Еще одна проблема, которую часто приходится решать при обработке данных для последующего анализа - это сопоставление информации из разных источников. В наиболее простом случае это может быть сопоставление двух таблиц, в которых один из столбцов полностью или частично совпадают. В случае, если обработка данных проходит в СУБД, то задача решается написанием простого SQL-запроса (при условии, что исходные таблицы правильно созданы и имеют идентификаторы). В Excel для решения подобных задач существует замечательная функция ВПР (VLOOKUP), которая берет значение в одном из столбцов таблицы и возвращает значение из другого столбца.
Однако практическое использование этой функции для обработки обычных данных, может оказать довольно проблематичным. Что делать, если значения "почти" совпадают, но не совсем точно? К примеру, в одной из таблиц сравниваемая ячейка имеет запятую, а в другой нет? Или в одной используется "-", а в другой "–" и так далее.
Я с подобными трудностями постоянно сталкиваюсь как минимум в двух случаях:
Однако практическое использование этой функции для обработки обычных данных, может оказать довольно проблематичным. Что делать, если значения "почти" совпадают, но не совсем точно? К примеру, в одной из таблиц сравниваемая ячейка имеет запятую, а в другой нет? Или в одной используется "-", а в другой "–" и так далее.
Я с подобными трудностями постоянно сталкиваюсь как минимум в двух случаях:
- При обработке данных по отдельным регионам РФ или странам мира. Проблема заключается в том, что разные страны или регионы могут иметь в разных источниках слегка отличающиеся названия. К примеру, " г. Москва" или "Москва (город)", "Белоруссия" или "Республика Беларусь", "Ханты-Мансийский автономный округ - Югра" или " Ханты-Мансийский авт. округ - Югра" и так далее. Примеров может быть множество, причем зачастую один и тот же источник (тот же Росстат) может использовать в разных публикациях или разных источниках немного отличающиеся названия регионов. Формально существует ГОСТ 7.67-2003, который определяет как должны называться страны и российские регионы "по стандарту", но на практике в точности ему никто не следует даже в официальных статистических публикациях. Да и ГОСТ сам себе уже старый (2003 год) и странный. к примеру, в нем определены "Башкирия (Республика Башкортостан)", но просто "Татарстан". Почему именно так понять решительно невозможно, запомнить - тем более. Названия стран в английском языке также могут именоваться слегка по разному. Классическое "USA", "U.S." или "United States"?
- Названия видов экономической деятельности ( поОКВЭД). Формально тоже существует официальный вариант названий, выложенный на сайте Росстата. Но и сам Росстат ему не следует. К примеру, в ЦБСД названия видов деятельности часто имеют такой вид "Предоставление усл. по добыче нефти и газа" вместо "Предоставление услуг по добыче нефти и газа" (11.2) или "Добыча и произ-ство соли" вместо "Добыча и производство соли" и так далее. ЦБСД при выдаче результатов по видам экономической деятельности не сохраняет код вида, поэтому идентификация возможно только по названию. Новая информационная система - "Новая межведомственная информационно-статистическая система"- имеет одинаковые названия видов в соответствии со стандартом и даже умеет выдавать результаты в формате SDMХ, в котором присутствуют коды видов деятельности, но она обновляется с большим опозданием.
Этими примерами все не ограничивается. Подобная проблема возникает постоянно, когда нужно объединить информацию из разных источников, относящихся к одной и той же сущности, которая не имеет однозначного идентификатора.
Что же делать?
Первый и достаточно очевидный ответ - сделать все вручную. Метод вполне хороший и оправдывает себя, если количество "сущностей" не слишком велико, и задача возникает лишь эпизодически.
Первый и достаточно очевидный ответ - сделать все вручную. Метод вполне хороший и оправдывает себя, если количество "сущностей" не слишком велико, и задача возникает лишь эпизодически.
Если же речь о других масштабах, к примеру, раскидать данные по всем более чем двум тысячам видам деятельности и имеет регулярный характер, можно подумать об автоматизации, по крайней мере частичной.
В терминах обработки текстов подобная задача носит стандартное название "fuzzy string match". Существует множество алгоритмов для решения задач. Хорошее их описание на русском языке есть на хабрахабре. Общая идея всех алгоритмов - разработка некоторой метрики оценки "схожести" строк. Полностью одинаковые строки имеют метрику 1.0, полностью не совпадающие строки - 0.0. Пользователь задает "точку отсечения" строк, которые будут считаться одинаковыми - к примеру, это может быть 0.9 и алгоритм считает, что строки которые значение метрики больше являются одинаковыми. Один из наиболее известных метрик - расстояние Левенштейна (по имени советского математика из Института им. Келдыша). Расстояние Левенштейна определяет минимальное количество вставок, замен или удалений символов, необходимое для того, чтобы превратить одну строку в другую.
Алгоритмы - это хорошо, но как воспользоваться их возможностями без необходимости того, чтобы программировать самому?
Я знаю два бесплатных инструмента, которые можно использовать прямо "с колес" без особых знаний тонкостей алгоритмов:
- Google Refine. Как называет его сама Google, " a power tool for working with messy data". Программа бесплатно скачивается и устанавливается на десктоп. Работает в окне браузера. Позволяет открыть локальный файл или документ Google Docs и сопоставить "похожие" значения. Для нечеткого сравнения программа предлагает несколько алгоритмов, подробно описанных в документации. Вообще программа умеет много чего, и имеет смысл поразбираться в ней, благо документация написана хорошо, есть даже видеоролики с иллюстрациями разных возможностей. Проблема для меня заключается в том, что передача файлов и обратно в Google Refine занимает время. Работа в веб-браузере понятна, но не очень удобна (для меня по крайней мере), поэтому я использовал несколько раз Google Refine для обработки бюджетной статистики и обработки большой таблицы со статистикой по странам по разным странам, но не нашел программу уж очень удобной для быстрой работы.
- Надстройка Fuzzy Lookup для Excel, написанная самой Microsoft. Программа также бесплатно скачивается и устанавливается в Excel. Внешний вид выглядит примерно так (обработка таблиц с видами деятельности):
В архиве есть файл с примером, который показывает как все работает. Важное, что нужно запомнить для того, чтобы сравнивать таблицы: нужно их создать в виде "таблицы" (то есть Вставка-Таблица). Первой выбирается таблица из которой берутся исходные значения, второй - которые сопоставляются с исходными. Можно выбрать несколько столбцов, по которым будет проводиться сопоставление столбцов, для этого надо добавить несколько столбцов в Match Columns. В Output Columns можно выводить не все имеющиеся столбцы, и лишь те, что нужно. Это довольно удобно. К примеру, стандартная функция ВПР ничего подобного не умеет, поэтому надстройкой Fuzzly Lookup можно пользоваться и для "четкого" сопоставления разных таблиц.
Алгоритм работы программы не описывается, но на практике он дает вполне нормальные результаты и для строк на русском языке. Главное не забыть, проверить результаты сопоставления и исправить возможные ошибки алгоритма вручную. Для этого надо обратить на строки, в которых Fuzzy.Lookup.Similarity отличается от 1 и удостовериться, что "автоматическое" сопоставление сработало правильно.
Алгоритм работы программы не описывается, но на практике он дает вполне нормальные результаты и для строк на русском языке. Главное не забыть, проверить результаты сопоставления и исправить возможные ошибки алгоритма вручную. Для этого надо обратить на строки, в которых Fuzzy.Lookup.Similarity отличается от 1 и удостовериться, что "автоматическое" сопоставление сработало правильно.
P.S. Функция ВПР имеет параметр "Интервальный просмотр", который можно поставить на значение "1". Это будет означать приблизительное соответствие . Но я не рекомендую его использовать ни при каких обстоятельствах, так как результаты этой "приблизительности" абсолютно непонятны. Можно легко получить неправильные значения. Ошибку же крайне сложно будет обнаружить пост-фактум.