5 мая 2012 г.

Получение подробных данных по бюджетам субъектов РФ

Большую часть экономики составляет бюджетный сектор. В прошлом году расходы консолидированного бюджета РФ составили чуть больше 20 трлн рублей (около 37% ВВП), из них на федеральный бюджет пришлось почти 11 трлн рублей, остальное - внебюджетные фонды, бюджеты субъектов РФ и муниципальные бюджеты.
Не так давно встала задача с получением и анализом довольно подробных данных по бюджетам отдельных субъектов РФ. Основным источником информации является отчетность Федерального Казначейства, который ежемесячно предоставляет данные об исполнении бюджетов. В случае федерального бюджета за каждый месяц - это стандартный набор файлов Excel.
Для региональных бюджетов - все гораздо сложнее. Казначейство выкладывает огромный архив (данные за каждый месяц в архиве занимают более 10 мегабайт) html-файлов. Структура данных простая: "регион-бюджетная форма отчетности" - отдельный файл.
Министерство финансов проводит некоторую аналитическую работу и предоставляет те же данные в более удобоваримом виде. Однако в случае данных Минфина возникает две проблемы:

  1. Набор показателей включает в себя базовые вещи вроде общих расходов и доходов регионального бюджета, но набор специфических показателей достаточно скуден. 
  2. Данные начинаются с февраля 2011 года, хотя Казначейство дает информацию аж с 2000 года. 

Что делать, если нужны подробные данные и длинная история? Как обычно, если цифр немного, то проще всего собрать цифры ручками. В противном случае, приходится "изобретать велосипед" и пытаться автоматизировать процесс, так как источники информации мало заботятся о нуждах простых аналитиков.
Когда возникла подобная необходимость, я написал набор скриптов на Python, которые обрабатывали предварительно сохраненные на локальном диске папки с исходными файлами. На каждый год я создавал отдельный скрипт для того, чтобы можно было посмотреть исходные данные. К  сожалению, из-за того, что коды бюджетной классификации регулярно меняются, как и формат предоставления данных. Поэтому скрипт, написанный для данных 2010 года, потребует, незначительной "подкрутки" для того, чтобы работать на данных 2006 года.
Для обработки html файлов я использовал отличную библиотеку Beautiful Soup. Парсинг файлов занимает относительно много времени (около 30-40 секунд для отдельного файла), поэтому скрипт работает довольно долго (около 10-15 минут на моем компьютере). Но так как он работает сам по себе, в это время можно заниматься другими полезными вещами.
  
Исходные коды, если кому-то пригодятся (так как я не программист, то код, видимо, не самый оптимальный): 

2 мая 2012 г.

Нечеткое сравнение строк (fuzzy string match) в Excel с помощью Fuzzy Lookup

Еще одна проблема, которую часто приходится решать при обработке данных для последующего анализа - это сопоставление информации из разных источников. В наиболее простом случае это может быть сопоставление двух таблиц, в которых один из столбцов полностью или частично совпадают. В случае, если обработка данных проходит в СУБД, то задача решается написанием простого SQL-запроса (при условии, что исходные таблицы правильно созданы и имеют идентификаторы). В Excel для решения подобных задач существует замечательная функция ВПР (VLOOKUP), которая  берет значение в одном из столбцов таблицы и возвращает значение из другого столбца.
Однако практическое использование этой функции для обработки обычных данных, может оказать довольно проблематичным. Что делать, если значения "почти" совпадают, но не совсем точно? К примеру, в одной из таблиц сравниваемая ячейка имеет запятую, а в другой нет? Или в одной используется "-", а  в другой "–" и так далее. 
Я с подобными трудностями  постоянно сталкиваюсь как минимум в двух случаях:

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

В архиве есть файл с примером, который показывает как все работает. Важное, что нужно запомнить для  того, чтобы сравнивать таблицы: нужно их создать в виде "таблицы" (то есть Вставка-Таблица). Первой выбирается таблица из которой берутся исходные значения, второй - которые сопоставляются с исходными. Можно выбрать несколько столбцов, по которым будет проводиться сопоставление столбцов, для этого надо добавить несколько столбцов в Match Columns. В Output Columns можно выводить не все имеющиеся столбцы, и лишь те, что нужно. Это довольно удобно. К примеру, стандартная функция ВПР ничего подобного не умеет, поэтому надстройкой Fuzzly Lookup можно пользоваться и для "четкого" сопоставления разных таблиц.
Алгоритм работы программы не описывается, но на практике он дает вполне нормальные результаты и для строк на русском языке. Главное не забыть, проверить результаты сопоставления и исправить возможные ошибки алгоритма вручную. Для этого надо обратить на строки, в которых Fuzzy.Lookup.Similarity отличается от 1 и удостовериться, что "автоматическое" сопоставление сработало правильно. 


P.S. Функция ВПР имеет параметр "Интервальный просмотр", который можно поставить на значение "1". Это будет означать приблизительное соответствие . Но я не рекомендую его использовать ни при каких обстоятельствах, так как результаты этой "приблизительности" абсолютно непонятны. Можно легко получить неправильные значения. Ошибку же крайне сложно будет обнаружить пост-фактум.