Броени часове ни делят от началото на Европейското първенство по футбол. Тази година то ще се проведе в 11 различни града на Европа, за да се отбележи юбилеят от началото на европейските първенства. Нашият отбор не участва, но българските телевизии са в унисон с идеята – мачовете от първенството ще се излъчват по 5 (словом – пет) различни канала. И така пред феновете на играта възниква логичният въпрос – кой мач, кога ще дават и по коя телевизия?
Преди няколко дни сайтът dir.bg публикува статия с гръмкото заглавие Евро 2020 на длан: Кой срещу кого, къде, кога и по кой канал (Пълна програма). Всичко хубаво, но всъщност в нашата длан имаме един дълъг текст, който е трудно да се обхване. Видът на тази информация е далеч от удобното, ако желаем да разберем кога е мачът Англия-Шотландия и по кой канал го дават.
Съвсем друго би било програмата да е подредена в таблица, която може да се вкара в Ексел и с филтър да се търси определена среща.
Всъщност изправени сме пред една типична задача за „чистене на данни“, каквато се случва почти ежедневно на хората, занимаващи се с анализ на данни. Както може би се досещате, в тази статия ще покажа как може да стане това с помощта на Power Query. Но няма да се впускам в подробности за конкретните техники, а ще наблегна на основните стъпки и добри практики, които следвам в подобни случаи. Те са общовалидни и не зависят от инструмента, който се използва. Спокойно може да ги приложите и за да решите задачата с Python или SQL например.
1. Определяне на структурата
Това е много важна стъпка, в която отделяме време, за да разгледаме данните и да потърсим някакъв модел в подредбата им, което ще ни помогне да изградим заявката (кода), с който ще ги подредим. В нашия конкретен случай структурата се забелязва лесно и съм я илюстрирал на долната картинка:
Вдясно са описани колоните, които искаме да съдържа нашата таблица, а в ляво – къде в оригиналните данни (в случая в статията) се намират. Тоест сега вече знаем от къде да вземем данните за съответните колони, за да напълним данните. Разбира се, в практиката моделът може да е много по-сложен. Често се случва дори да имаме не един, а няколко модела. Всичко това трябва да открием и опишем на този етап, като първа стъпка за изготвяне на задание (спецификация) по което ще създадем съответния код.
Но преди спецификацията трябва да направим нещо много важно, а именно:
2. Откриване на грешки и проблеми
Тук трябва да разгледаме още един път данните, но този път не с идеята да открием структурата, а грешки (бъгове) в нея. Аз успях да открия три такива, които са описани по-долу:
След като сме ги открили, трябва да измислим и как да ги преодолеем, за да разполагаме с коректни данни. Обикновено това става чрез:
- Филтриране – така можем да премахнем излишния текст
- Заместване(Find & Replace). Така например можем да заменим думата „часа“ с „ч“ и ще имаме еднакво изписване
- Прилагане на логически формули (IF). Например – ако текстът на даден ред съдържа думата „Група“, значи на този ред се намират данните за часа, мача, градът, групата и телевизията
- Понякога може да е по-удобно, бързо и лесно да поправим нещо на ръка. Например, от всичките 50 мача единствено за първия часът е посочен в реда за деня и дата. Тогава вместо да мислим някаква сложна логика, която да утежни нашия код, можем просто да нанесем часа ръчно в таблицата, когато приключим с обработката
- В този случай не е приложимо, но в практиката грешки и бъгове често се отстраняват с прилагане на контроли и ограничения при въвеждането на данните. Например, ако забелязваме, че в списък с адреси градовете се изписват по най-различни начини ( „Велико Търново“, „В. Търново“ „Търново“, „Стара Загора“, „Ст. Загора“), можем да направим така, че те да се въвеждат от падащо меню
След като сме установили структурата и евентуалните грешки и проблеми, е време за следващата стъпка:
3. Насоки на логиката на обработката
Тук, въз основа на анализа в предните две точки, си набелязваме логиката, по която ще почистим и подредим данните. Не е задължително да разпишем точно и подробно как точно ще стане, а само да маркираме ключовите моменти. Това може да стане като запишем всяка една трансформация, която трябва да направим и до нея идеи как да се случи. Например:
„Отделяне на редовете с дните и датите –> тези редове съдържат „юни“ или „юли“
„Определяне на редовете с мачовете, часовете градовете –> съдържат името на града“
„Разделяне на данните в отделни колони –> разделени са със запетая“
Не бива да се забравят и грешките и проблемите с данните, които сме установили на предната стъпка. За всяка от тях също трябва да се разпише идея как да се отстрани:
„Уеднаквяване на изписването на часа –> да се замени „часа“ с „ч“
„Премахване на излишния текст –> използване на филтър – какъв да бъде критерият?“
Как точно ще направим всичко това – зависи от конкретната ситуация и от инструмента, който се използва за анализ, както и от това, което ще се случи на следващата стъпка. Обърнете внимание, че не всичко на този етап е ясно. Например, какъв ще бъде критерият, по който ще филтрираме излишния текст. Напълно е възможно такива детайли да се изчистят едва след като започнем обработката на данните. Идеята тук е да си съставим списък на проблеми за решаване с възможни техни решения.
4. Обработка на данните (в Power Query)
Как да заредим текстови данни от интернет в Power Query е подробно описано в тази статия. А ето така изглеждат данните в нашия случай (първите двайсетина реда)
Сега можем да използваме това, че в HTML всеки параграф с текст е ограден от таг <p>…</p> и да филтрираме всички редове, които съдържат текст по този начин:
Тук са филтрирани всички редове, които съдържат текста с програмата на мачовете
Сега вече може да започнем с трансформациите на този текст. Няма да ви отегчавам с детайлно описание на стъпките само ще дам жокер, че се използват техниките за:
Добавяне на колона под условие (add conditional column)
Разделяне на колони (Split columns)
Крайният резултат е точно това, което искаме – хубава и подредена таблица с всички мачове:
Но работата ни не е приключена. Има още една стъпка:
5. Проверка на резултата и корекции
Това, че имаме таблица, която изглежда добре, не означава, че сме свършили работата както трябва. Добра практика е да проверим таблицата, за да се уверим, че данните в нея са коректни. Има много методи за това и кои точно ще се приложат зависи от конкретния случай, но някои стандартни и общо валидни са:
- Проверка на тоталите. Например общия брой редове. На Евро 2020 ще се изиграят 51 мача, т.е. нашата таблица трябва да има 52 реда (заедно със заглавния ред). Един бърз преглед показва, че таблицата съдържа 51 реда. Губи ни се последният, най-важният мач – финалът на първенството. Защо – трябва да анализираме и да отстраним причината
- Друг вид проверка е на случаен принцип да проверим 3-4 реда дали съдържат правилната информация. Добре е те да са различни – примерно една среща от груповата фаза, една от осминафиналите, една от четвърт финали и т.н. Разбира се, ако данните са повече – няколко хиляди реда, ще трябва да увеличим размера на извадката
- Проверка за грешни стойности. Това особено често се случва с числа и дати, които могат да бъдат заредени като текст
- Проверка за невалидни стойности. Примерно, ако има мач преди 11 юни и след 11 юли или мач, който започва в 28 часа – нещо не е наред. Същото е, ако има мач в Ню Йорк (това е европейско първенство все пак)
- Логически проверки – те се отличават от проверката за невалидни стойности по това, че търсим валидни стойности, които обаче не изглеждат логични. Например: градовете, в които ще има мачове са по-малко от 11 (защото знаем, че 11 града ще бъдат домакини), или всички мачове започват в един и същи час, или имаме мач Шотландия-Шотландия
- Проверка за липсващи стойности – дали има ред/колона, в които липсват стойности. Например мач без дата или начален час.
Този списък не изчерпва всички проверки, които трябва да се направят. Отделете време да помислите върху конкретните данни и какво може да се обърка, за да го проверите.
Това бяха моите „опорни точки“ за изчистването на данни, т.е. превръщането им от текст в „насипен“ вид в структурирана таблица. Надявам се да са ви били полезни, а сега може да изтеглите файла с програмата на европейското първенство и да се насладите на играта. В него може да разгледате и заявката, която съм направил в Power Query. А ако желаете да се упражните, може да пробвате да обработите програмата, публикувана в gong.bg.