Всички знаем, че Ексел е чудесен инструмент за анализ на числови данни. С Power Query вече можем бързо и лесно да анализираме и текст. С тази четвърта по ред статия ще ви покажа как можем да изброим кои думи се срещат най-често в даден текст. Отново ще използвам текстовете на песните на Васил Найденов. Но техниката може с успех да се използва и при анализ на оплакванията в кол център, мнения в блог или отворени отговори на въпроси в социологическо проучване.
Започваме от там, където свършихме в 3 част – списък с песните и техните текстове. 
Списъкът е във вид на ексел таблица затова отиваме на Power Query > From Table.
Текстът, който ни интересува е в последната колона. В случая тя е маркирана и затова е със зелен фон. Другите две не ни трябват и можем да ги махнем като ги маркираме, натиснем десен бутон на мишката и изберем Remove. Но аз ще ги оставя, защото имам нещо на ум, което ще ви демонстрирам в петата част от серията.
Целта ни е да изброим кои думи колко често се срещат в текстовете, т.е. първо трябва да разбием всеки ред на отделни думи. Това става лесно, защото думите са разделени с интервали и можем да използваме Split column > By Delimiter
След това избираме за разделител интервал (Space) и като опция – At each occurrence of the delimiter.
На български казано – искаме Power Query да раздели колоната всеки път, когато срещне интервал.
Готово, всеки ред от таблицата е разделен на отделни колони.
Ако скролнем на дясно, ще видим, че те са 12 на брой. Как да ги подредим една под друга, за да можем да изброим думите по-лесно? С традиционните инструменти на Ексел това ставаше с 12 копи/пейст операции или с макрос. С Power Query това става с точно пет клика на мишката.
Първият е, за да маркираме най-лявата колона, като кликнем на нейното заглавие („Изпълнител“). След това натискаме CTRL или SHIFT и кликаме втори път върху заглавието на втората колона („Песен“). Така ги маркираме двете заедно както е на картинката.
След това отиваме на Transform > Unpivot Columns > Unpivot Other columns (още три клика, за да ги направим пет).
С тази операция казахме на Power Query, че искаме да подреди една под друга всички колони освен първите две. И речено-сторено. За секунда Power Query прави нещо, което би ни отнело поне 10 минути.
Нека да направим кратко отклонение и да разгледаме какво се получи. Първите две колони – „Изпълнител“ и „Песен“ си останаха. Изчезнаха 12-те с думите и на тяхно място се появиха две нови. Power Query им даде служебни заглавия – „Attribute“ и „Value“. „Attribute“ съдържа заглавията на старите 12 колони – Текст.1, Текст.2 и т.н. Тази колона в случая не ни трябва и можем да я махнем, предполагам вече знаете как – десен бутон върху заглавието и избираме Remove.
„Value“ съдържа съответните думи. Затова ще я оставим, но при желание можем да променим заглавието. Става с двоен клик върху него, то се маркира и записваме новото заглавие. В случая аз избрах „Думи“.
В общи линии това е – в 12 стъпки успяхме да подредим думите в една колона, сега като дадем Close&Load, таблицата ще се зареди в Ексел, където можем с пивот таблица да изброим думите.
Не би било зле обаче преди това да изчистим данните, за да получим по-добър анализ. Например, ако натиснем бутона за филтър на колона „Думи“, ще видим, че фигурират такива думи като „оие“, „аха“, „оу“. А също така препинателни знаци и цифри.
В случай, че те не ни интересуват, можем да се отървем от тях като махнем отметката пред всяка такава дума или символ.
Това е просто, но може да бъде много дълго. Което означава скучно и с риск да пропуснем нещо. От друга страна, ако решим да заредим някакъв друг текст. Примерно песните на Веселин Маринов. Там може да няма „оие“ и „оу“, но пък да има „ийе“ и „ау“, което означава, че филтърът ни просто няма да работи.
Затова аз бих ви препоръчал най-напред да изчистим текста от препинателни знаци. Те не са много. Броят се на пръстите на ръцете и присъстват във всякакъв текст. В случая ще ви покажа как става с точка (.). Десен бутон върху колоната и избираме Replace Values.
Във Value To Find пишем точка (.), а Replace with го оставяме празно. По този начин Power Query ще премахне всички точки в колоната.
Повтаряме операцията 15-на пъти за всички препинателни знаци – запетая, тире, двоеточие, лява и дясна скоба и т.н. Защо да го правим? Защото „красота!“, „красота“ и „красота.“ за Power Query и Ексел са три различни думи.
Сега нека да добавим една колона, с която да преброим дължината на всяка дума. Отиваме на Add Column > Add Custom Column
И записваме тази проста формула:
Text.Length е аналогът в Power Query на функцията LEN в ексел. Тя изброява дължината на даден стринг. В случая искаме да изброим дължината на текста на всеки ред от колона „Думи“. За име на новата колона съм избрал „Дължина на текста“, но може да бъде всичко по ваш вкус.
Тук е мястото да подчертая, че в Power Query, за разлика от Ексел е много важно да записваме функциите по правилния начин, т.е. Text.Length с главни и малки букви. Все още Power Query не ни подсказва синтаксиса и не отваря скоби за нас. Е, има някаква индикация дали формулата е грешна (ограденото в синьо на горната картинка), но не е толкова user friendly колкото в Ексел.
Ето я новата колона в таблицата. Тя ще ни даде възможност да филтрираме редовете, които са под определена дължина. Например, ако махнем отметките пред числата по-малки от четири, ще премахнем думи като „хей“, „оие“, „аз“, „ти“, „да“, „не“ и т.н. Стига да не виждаме смисъл от тях.
Сега можем да заредим нашата таблица в Ексел и да направим пивот таблица от нея:
Следващата стъпка е да сложим „Думи“ в Rows, „Дължина на текста“ във Filters и отново „Думи“ във Values като се уверим, че е избрана формулата за броене Count.
Сега нека да кликнем в лявата колона с десния бутон и да изберем Sort > More Sort Options
Избираме Descending и от падащото меню – Count of Думи.
Така Пивот таблицата ни подрежда думите в низходящ ред по тяхната честота.
Можем да махнем думите с дължина под 4 букви
и получаваме това:
Обърнете внимание, че колкото и да се постарахме, все пак не сме успели да изчистим всички излишни думи. Очевидно „Припев“ не е част от текстовете на Васил Найденов. Също така за нуждите на анализа „Обичам“, „обич“, „обичал“ може би трябва да се броят като едно. Както и „седем“ и „7“. Но да не навлизаме в такива подробности, които иначе са ежедневие за хората, занимаващи се с анализ на данни.
Статията е част от поредица за сваляне и анализ на данни от интернет, чрез Power Query. Ето и връзките към другите статии:
- Сваляне на данни от Интернет с Power Query
- Сваляне на данни от много линкове в Интернет
- Създаване на списък с линкове
- Анализ на текст чрез Ексел
- Word clouds в Excel