Нечёткая текстовая кластеризация
Представьте на секунду, что вам в качестве исходных данных достался вот такой список:
Если присмотреться, то легко уловить, что на самом деле в нём фигурируют всего 4 города (Москва, Санкт-Петербург, Новосибирск и Тамбов), написанные в разных вариантах - с ошибками и без, с "г" и без "г", с пробелом и без него и т.д. В реальной практике такой "разношёрстный зоопарк" встречается весьма часто, когда данные собираются из нескольких источников или в файл заносят информацию разные люди и делают это творчески.
Оставим пока за скобками глобальную неправильность этой ситуации и то, что её надо принципиально решать на другом, более высоком уровне, и сосредоточимся на следующей задаче: можно ли, имея вот такие корявые текстовые данные, автоматически сгруппировать их в наборы (т.е. 4 кучки в данном случае) по максимальной похожести? Чтобы получить на выходе упорядоченный список, с которым уже можно дальше хоть как-то работать? Примерно так:
Думаю, очевидно, что делать это вручную при большом количестве исходных данных - адская задача.
В науке анализа данных подобную процедуру называют нечёткой текстовой кластеризацией (fuzzy text clustering). В Microsoft Excel решить её можно с помощью Power Query - надстройки для импорта и очистки данных. Впервые она появилась для Excel 2010 (как отдельная вкладка), а начиная с версии Excel 2016 уже стала неотъемлемой его частью на вкладке Данные (Data). Под капотом у этой надстройки работает внутренний язык программирования "М", движок которого и реализует все преобразования данных.
В последних версиях Excel в этот язык добавили новую функцию Table.AddFuzzyClusterColumn, которая как раз и предназначена для решения нашей задачи - группировки неточно совпадающего текста в группы (кластеры). Нюанс же в том, что в интерфейсе Power Query пока нет кнопок или меню, где можно было бы вызвать эту функцию - применить её можно только введя соответствующий М-код вручную.
Давайте попробуем это сделать.
Конвертируем наш исходный "грязный" список в динамическую "умную" таблицу с помощью команды Главная - Форматировать как таблицу (Home - Format as Table) или сочетанием клавиш Ctrl+T. Затем загрузим её в Power Query стандартным образом - кнопкой Из таблицы/диапазона на вкладке Данные (Data - From Table/Range).
Нажмём на кнопку со значком fx, чтобы добавить новый шаг преобразований. В строке формул будет видно, что, по умолчанию, этот шаг просто ссылается на предыдущий (скорее всего он будет у вас называться "Измененный тип" или "Changed type" - это не принципиально).
Теперь главное - введём нашу функцию нечёткой текстовой кластеризации из языка М:
= Table.AddFuzzyClusterColumn(
#"Измененный тип",
"Город",
"Кластер",
[ Threshold=0.4, IgnoreCase=true, IgnoreSpace=true, SimilarityColumnName="Подобие" ] )
Первый её аргумент (#"Измененный тип") - исходная таблица, т.е. имя переменной, хранящей данные с предыдущего шага (если имя содержит пробелы, то оно по правилам синтаксиса языка М берётся в кавычки и перед ним ставится решётка).
Второй ("Город") - имя столбца с разношёрстными текстовыми значениями, который мы анализируем.
Третий аргумент ("Кластер") - имя создаваемого столбца, где будут храниться обобщенные значения.
Четвёртый аргумент представляет собой запись (record), где в квадратных скобках через запятую перечисляются параметры кластеризации:
- Threshold - порог подобия (похожести). Это дробное число от 0 до 1, определяющее требуемую похожесть текста внутри каждого кластера. Чем ближе это число к 1, тем меньше ошибок и различий эта функция будет "прощать".
- IgnoreCase - логический параметр (true или false), определяющий надо ли игнорировать регистр (обычно - да, надо).
- IgnoreSpace - логический параметр, определяющий надо ли игнорировать пробелы в тексте (если они расставлены как попало, то надо).
- SimilarityColumnName - имя дополнительного столбца, где будет выведено числовое значение коэффициента подобия (полезная штука для последующего анализа и уточнения). Если его не задать, то такой столбец создаваться не будет.
После нажатия на Enter в конце формулы (или галочки в строке формул рядом с fx) мы получим два новых столбца - с кластером, к которому Power Query отнёс каждое значение, и коэффициентом подобия исходного и обобщенного значений.
Само собой, имеет смысл отсортировать всю эту историю (с помощью кнопок фильтра в шапке таблицы) - сначала по кластерам, а потом по убыванию подобия внутри каждого кластера:
Останется выгрузить результаты в Excel стандартным образом - с помощью кнопки Закрыть и загрузить на вкладке Главная (Home - Close & Load).
Бонусом можно добавить разделительную линию между кластерами, чтобы их лучше было видно. Для этого выделяем всю полученную таблицу (кроме шапки) и используем команду Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - New rule - Use formula to detect which cells to format):
Логика тут простая: если значение в текущей строке столбца Кластер не равно значению в следующей, то мы добавляем ячейке нижнюю границу.
Ещё одно усовершенствование, которое можно сделать - это брать значение коэффициента подобия для параметра Threshold из какой-нибудь определённой ячейки на листе. Для удобства можно даже привязать к этой ячейке полосу прокрутки, чтобы динамически задавать коэффициент прямо мышью.
Чтобы реализовать такое, делаем следующее:
- Добавляем на лист полосу прокрутки, используя команду Вставить - Элементы управления формы - Полоса прокрутки на вкладке Разработчик (Developer - Insert):
- Щёлкаем по нарисованной полосе правой кнопкой мыши, выбираем команду Формат элемента управления (Format control) и задаем в качестве Связанной ячейки (Linked cell) любую подходящую пустую ячейку, например H2. В этой ячейке, при перемещении мышью ползунка полосы прокрутки, будет выводиться число от 0 до 100.
- В любой соседней ячейке (например, H3) вводим формулу для формирования коэффициента как дробного числа: =H2/100
- Превращаем эту ячейку в именованный диапазон с именем, например, Порог на вкладке Формулы - Диспетчер имён (Formulas - Name manager).
- Загружаем эту именованную ячейку в Power Query, используя команды Данные - Из таблицы/диапазона (Data - From Table/Range).
- Удаляем шаг Измененный тип (Changed type), если он есть, и проваливаемся внутрь единственной ячейки этой таблицы непосредственно к числовому значению коэффициента, щёлкая по ячейке правой кнопкой мыши - Детализация (Drill down).
- Выходим обратно в Excel, используя команду Главная - Закрыть и загрузить в - Только создать подключение (Home - Close & Load to - Only create connection).
Теперь можно прямо мышью динамически корректировать порог подобия на листе с помощью полосы прокрутки, нажимать кнопку Обновить всё на вкладке Данные (Data - Refresh all) - и тут же получать обновлённые сгруппированные результаты.
По сути, мы тут сделали универсальный инструмент для нечёткой текстовой кластеризации чего угодно. Удобная штука :)
P.S.
Если после вставки имени второго запроса "Порог" в код нашей функции вы получаете ошибку Formula.Firewall, то отключите проверку уровней конфиденциальности при слиянии запросов. Это можно сделать в окне Power Query командой Файл - Параметры и настройки - Параметры запроса - Конфиденциальность - Всегда игнорировать параметры уровней конфиденциальности (File - Options & Settings - Query settings - Privacy - Always ignore privacy levels).
Ссылки по теме
- Нечёткий текстовый поиск в Power Query
- Нечёткий текстовый поиск с надстройкой Fuzzy Lookup в Excel
- Поиск ближайшего похожего текста макросом
правда в том, сто при наличии таблиц из 100 млн строк например подбирать коэффициэнт при при помощи визуальнго контроля ещё та работа)))
Поиск с интернете не дает ничего вразумительного. Пишут, например, что как-то используется для сравнения коэф-т Жаккара. Но ведь он не учитывает порядок букв ("Токио" и "Киото" для него идентичны).