Росстат ежегодно публикует около 4 тысяч показателей государственной статистики.
Они доступны каждому без каких-либо ограничений по статусу, правам доступа и т. д. Но Росстат при публикации данных в первую очередь ориентируется на то, что пользователи будут работать с ними вручную (глазами и руками), хотя за последние 20 лет мягко говоря, это не совсем тренд. Меня зовут Максим Веденков, я работаю в ЦПУР (Центр передовых управленческих решений) — некоммерческая организация, которая проводит исследования государственных данных с целью повышения осведомленности общественности о процессах, происходящих в стране.
Мы также собираем, обогащаем и публикуем наборы данных с государственными данными, как ранее опубликованными, так и теми, которые ранее не публиковались.
В этой статье я хочу поговорить об одном из таких наборов данных.
Большая, сложная, важная, но в то же время доступная в крайне неудобном для исследователей формате - база данных показателей муниципальных образований (БД БДМ).
Программистам часто хочется найти элегантное решение, которое может быть сложным, трудоемким, но универсальным.Но прежде чем начать говорить о программной базе данных, стоит рассказать, зачем нужно дополнительно обрабатывать открытые данные.Однако это не работает с правительственными данными.
Проходящей через всю статью нитью не столько то, насколько мы молодцы (хотя мы, конечно, молодцы), сколько то, что иногда нужно просто запутаться, да еще и прийти к какому-то согласию с самим собой( когда тебя заставляют выполнять не особо квалифицированную работу) для достижения цели.
Дело в том, что исследователи — не программисты; у них другой набор навыков и инструментов.
Исследователи хотят получать необработанные данные в удобном виде, чтобы им не нужно было предпринимать никаких дополнительных действий и можно было сразу приступить к анализу и исследованию.
Вот пример исследования моих коллег - «От выборов до назначения.
Оценка эффекта от изменения модели управления муниципальными образованиями в России» .
Контекст был такой: по всей России выборы мэров отменялись или уже были отменены, но к чему это приведет на практике, сказать никто не мог.
Это невозможно оценить, поскольку для такой оценки необходимы данные о том, что произошло до и что произошло после отмены, с разбивкой по муниципалитетам.
И в течение длительного периода, чтобы можно было проследить историю.
Вот показатели, в частности, используемые при оценке:
- Население
- Средняя заработная плата
- Расходы и доходы местных бюджетов
- Возрастная структура населения
Вывод исследования: Отмена выборов имела негативные последствия, муниципалитеты стали беднее.В приведенном выше примере данные фактически имеют вид т.н.Текст исследования содержит аргументацию.
А выводы и аргументация являются основой для принятия решений.
Панельные данные.
Что это значит?
- Измерения охватывают достаточный период времени
- Разные объекты можно сравнивать друг с другом.
- Все необходимые переменные собраны в одной таблице
Что такое база данных PMO
База данных PMO представляет собой базу данных муниципальных показателей, поддерживаемую Росстат .Муниципалитеты являются элементами административного деления страны.
Вот примеры показателей, которые они отслеживают:
- Общая площадь отселенного аварийного жилищного фонда
- Расходы местного бюджета фактически исполнены
- Количество лечебно-профилактических организаций
- Посевная площадь сельскохозяйственных культур
- Численность населения на 1 января текущего года
Такая информация полезна социологам, экономистам, политологам, журналистам и другим исследователям.
А их исследования, в свою очередь, полезны обществу и государству.
Кстати, показатели, по которым оценивается эффект от изменения модели муниципального управления, также можно найти в базе данных ОУП.
Какая была задача
Соберите 85 региональных баз данных в одну.Потому что нет «базы» базы данных PMO как единого объекта данных.
Каждый регион имеет свою базу данных, практически не связанную с другими.
Структурно они должны быть идентичными, но на практике это не так, ведь сотни людей заполняют их вручную.
Теперь представьте нашу проблему на абстрактном уровне.
У вас есть базы данных из 85 регионов, все об одном и том же, но с микроотличиями.
И нужно объединить все основы так, чтобы исключить эти микроотличия, и сделать это правильно.
Начинаем с этого момента и идем дальше.
Как структурирована база данных PMO
Мы решили не парсить данные, а обратиться к первоисточнику.Росстат пошел нам навстречу и передал дампы баз данных, значения из которых теперь можно выгружать в конструктор на сайте Росстата, но в отдельных таблицах.
То есть на вход мы получили дампы баз данных по всем предметам в формате .
bak (MS SQL Server) и один файл Word с документацией.
В развернутом виде они занимали ~200 ГБ и содержали около 200 миллионов строк наблюдений.
В среднем в каждой базе данных имеется 780 таблиц.
Всего мы получили 82 базы данных – меньше, чем субъектов (85), поскольку в базу данных своих «материнских» субъектов включены четыре автономных округа (Ненецкий, Ханты-Мансийский, Чукотский, Ямало-Ненецкий), два из которых расположены в Тюменской области.Вот как выглядит одна база данных:Те.
данные по Ямало-Ненецкому и Ханты-Мансийскому автономному округу хранятся в базе данных по Тюменской области.
Каждая база данных содержит два типа таблиц: таблицы с данными и справочники.
Одна таблица данных — это один показатель.
Вот, например, данные по показателю «Количество людей, проживающих в небезопасных жилых домах» (код 8008022): 
Первые шесть атрибутов присутствуют всегда, по ним мы узнаем год, период записи и коды муниципалитетов.
Поле zn8008022 — поле для значения индикатора, записанное с использованием маски zn[код индикатора].
Но эта структура не постоянна; Индикатор может иметь от одного до трех дополнительных атрибутов, которые мы называем группами категорий.
Вот пример: 
В этой таблице не семь, а восемь атрибутов.
Еще одним дополнительным является поле «stdohod», которое содержит значения из группы категорий «Статьи доходов местного бюджета».
И значение этого кода хранится в отдельной справочной таблице для этой группы категорий.
А вот и он: 
Если объединить таблицу данных со справочниками ее атрибутов, то мы получим полноценные панельные данные, т.е.
заменим коды категорий на сами эти категории.
В каждом регионе своя версия каталога.
Те.
один условный показатель — это 82 таблицы плюс 82 справочника по каждому входящему в него признаку.
О документации
Вместе с базой данных мы получили внутреннюю документацию.В документации не было ни иллюстраций, ни ER-диаграмм, ни каких-либо других графических диаграмм, но было 26 страниц избранного официального текста.
Например, можете ли вы сразу понять, что содержат такие типы каталогов: «Справочники вхождений значений признаков» и «Справочники секционирования значений признаков».
Из названия смутно понятно, что здесь есть определенные знаки и разрезы.
А если мы посмотрим документацию, скажем, второго каталога, то обнаружим следующее: «Имя таблицы формируется путем присоединения префикса «p_» и окончания «_raz» к содержимому поля nameprav фичи каталог».
Те.
Никаких пояснений, что это такое, нет, есть сугубо техническая информация.
И вот первый вывод. Если вы встретите в документации официальный язык, то перепишите его под свои нужды, введите новую терминологию, стандартизируйте названия, упростите формулировки, классифицируйте объекты данных, визуализируйте.
Работайте с тем, что вы понимаете, а не с тем, кто это за вас написал.
Мы не сделали это сразу (это заняло бы много времени), и это была огромная ошибка.
Любой контакт (встреча, статус, мозговой штурм) за пределами нашей небольшой команды наталкивался на стену непонимания со стороны коллег, пока мы не ввели новую терминологию.
Каждая минута, потраченная на переписывание документации, окупится десятикратно, если вам не придется отвечать на вопросы «Что это такоеЭ» снова и снова.
В чем разница? Как они связаны друг с другом? Это окупится каждый раз, когда ваш мозг не потратит пару секунд на то, чтобы вспомнить, чем «Справочник значений признаков» принципиально отличается от «Справочника числовых значений признаков» и в каком из них содержится то, что вам нужно.
Сложность в том, что нет ни точки опоры, ничего.
Допустим, вы получили такую задачу, какое первое нативное решение? Приводим справочники к стандарту, очищаем данные от аномалий.
Все логично.
Но этот способ не работает с базой данных PMO. Напомним, что данные в базе данных программного обеспечения являются статистическими показателями.
Вот, вроде бы, показатель, простая вещь, например, «Количество муниципальных спортивных объектов».
Но может иметь от 4 до 7 групп категорий (атрибуты в таблице).
Подайте заявку в 10 или 85 регионах.
Его можно заполнять раз в квартал, год или 10 лет. По состоянию на 30 декабря текущего года или 30 января года, следующего за отчетным.
Он может иметь одну из 27 единиц измерения.
Физические показатели могут измеряться в единицах и кг, денежные показатели – в суммах от тысяч рублей до миллиардов.
И конечно, все эти цифры разные: разные по годам, разные по регионам, разные по муниципалитетам одного и того же региона.
И границы регионов меняются, меняется ОКТМО мун.
образования, названия меняются.
Код ОКТМО — восьмизначный код вида 14701000, который кодирует мун.Базы данных отличаются друг от друга, поскольку это разные регионы.образование.
В данном примере 14 – Белгородская область, 701 – город Белгород, 000 ничего не значит. Если бы в составе Белгорода были другие муниципалитеты, то вместо 000 были бы цифры.
Сам термин «ОКТМО» расшифровывается как «Общероссийский классификатор муниципальных территорий».
Ни один из них невозможно принять за эталон.
В Краснодарском крае выращивают пшеницы в 60 тысяч раз больше, чем в Якутии.
И Якутия от этого не становится аномалией.
Другими словами, у вас нет диапазона допустимых значений метрик.
Кроме того, показатель для одного региона может быть неприменим к другому.
А еще есть человеческий фактор: у каждого показателя есть человек, ответственный за его заполнение, и когда его нет или его Windows 98 просит обновиться, данных нет. Показатели вводились не сразу, а на протяжении многих лет. И нет никакой истории этих изменений.
Некоторые старые были удалены, некоторые заменены.
Такие классификаторы, как ОКВЭД и ОКОГУ (Классификатор видов экономической деятельности и органов государственной власти и управления), являются федеральными, а не Росстатом.
Следовательно, когда они изменились, Росстат был вынужден проводить перекодировки параллельно во всех 82 базах данных.
И конечно, не обошлось без человеческих ошибок, которые, например, проявляются в том, что одновременно используются два разных кода.
Другой пример.Также непросто формализовать и рассчитать в цифрах такие метрики качества данных, как полнота, непрерывность и целостность, чтобы опираться на них в дальнейшей работе.Показатели и категории также не статичны.
Они обновляются с некоторой регулярностью.
Например, потому, что принят закон об обновлении кодов федерального классификатора (ОКОГУ).
И делают они это тоже по-разному.
В некоторых регионах просто меняют название категории под определенный год, в других вводят новое, добавляя что-то вроде «с 2014 года» в новом и «до 2014 года» в старом.
В результате получаем дубль (два разных названия категории для одного кода).
Например, в таком-то районе в таком-то сельском поселении заполнялись данные не по каждому селу, а по всему сельсовету.
Формально они вроде бы существуют, но уже агрегированы.
Такая особенность агрегирования данных в базе данных ПМО является следствием ее концепции, ведь одно наблюдение – это значение показателя для одного ОКТМО.
Проблема в том, что код ОКТМО сам по себе является структурной единицей, поэтому заполняемые для него данные не являются атомарными (неделимыми).
Поэтому, например, если посчитать сумму для всех мун.
формирования Омской области, получится 43 млн человек: 
Происходит это потому, что лунные образования, словно матрешка, вложены одно в другое.
А вложенные они потому, что база не в третьей нормальной форме, т.к.
поле ОКТМО содержит структурную информацию.
А ОКТМО содержит структурную информацию, потому что это федеральный справочник и именно в связи с ОКТМО сам Росстат получает данные.
Вот матрешка.
На практике эти проблемы проявляются в том, что у вас нет и не может быть единых схем поиска аномалий, не говоря уже об их устранении.
И любая обнаруженная аномалия с равной вероятностью может оказаться артефактом или даже нормой для данного региона.
И четких критериев (кроме природных показателей, если, например, длина трубопровода за год увеличилась на 1 млн км) как для определения аномалий, так и для отличия аномалий от артефактов нет. План «находим аномалии, понимаем природу, находим универсальное решение» не работает в таком масштабе и изменчивости, он слишком трудозатратен.
Это может занять месяцы работы команды людей.
В итоге мы пришли к следующему выводу: базу данных собираем, справочники нормализуем, а поиск и устранение аномалий оставляем исследователям.
Это имеет смысл по двум причинам:
- Во-первых, исследователи работают с гораздо меньшим количеством показателей по той или иной теме и гораздо более погружены в нее, чем мы.
- Во-вторых, мы слишком сильно рискуем, если очищаем данные, удаляя по-настоящему ценные артефакты, поскольку они попали под шаблон аномалии.
Нормализация каталогов
Со справочниками, в отличие от данных, было проще.Итак, у вас есть 82 базы данных, а это значит 82 справочника.
Например, культура, «Сельскохозяйственные культуры».
Напомню, что каталог подключается к данным так: 
Это таблица показателя «Посевная площадь сельскохозяйственных культур» в Алтайском крае, в дополнительном признаке (культура) которой прописан код 1010500. В справочнике этого атрибута код 1010500 кодирует категорию «Пшеница яровая».
Если в каждой базе данных записана одна сущность под кодом 1010500, то проблем нет. Это идеально.
Но на самом деле мы имеем несколько вариантов написания одной и той же сущности.
Вот простейший пример: одна сущность, но разное написание: 
В этом проблема справочников.
Они не нормированы между базами, и не существует стандарта, к которому их можно было бы привести.
Как их нормализовать и привести к стандарту? Здесь работает нативное решение: создаем стандарт из всех справочников по принципу «какая категория чаще всего используется с кодом, тот и является стандартом».
Здесь возникли некоторые трудности:
- Во-первых, как посчитать метрику «разницы» между категориями и какой мусорный контейнер выбрать для этой метрики.
Например, некие условные «категория 1» и «категория 2» различаются только словами «с 2014 года», но дают разницу в 86 (по Левенштейну).
А «прочие категории 1» и «прочие категории 2» дают мусорную корзину 80, но явно фундаментально отличаются друг от друга.
- Во-вторых, если у вас пропорция 81/1, где в 81-й базе названия категорий одинаковы, а в одной они принципиально разные, то где стандарт, понятно.
Что делать, если у вас соотношение 42/28? Это уже не так очевидно.
- В-третьих, мы нашли такие проблемы (их, кстати, было более 2000), что с ними делать?
Второй – с помощью здравого смысла.
И третий – с помощью Росстата.
Помимо Левенштейна мы также пробовали посимвольную матрицу TF-IDF, используя разные алгоритмы.
Технически вам нужно было сравнить одно имя категории с любым другим именем из всех каталогов и получить корзину.
На практике мусоропровод Левенштейна оказался самым понятным и точным из всех, поэтому остался именно он.
И мы подтвердили результаты собственными глазами.
Природа пропорций типа 42/28 оказалась во всех случаях одинаковой: сосуществование в справочниках как старых, так и новых кодов.
В некоторых регионах старые коды остались, в других их удалили.
Чем нам помог Росстат? Он взял на себя задачу решения «сложных дел».
Мы составили для них полный отчет, по каким предметам, по каким показателям и категориям есть проблемы, и отправили им.
И их отправляют в региональные управления для детальной проверки.
План действий готов
Теперь я вам все это описал, и вроде понятно, в чем проблема.
Примерно наш путь выглядел так: 
Фактически, каждый из этих выводов, как и общий результат, не был очевиден с самого начала.
80% времени при работе с базой данных ПО тратилось на устранение неопределенностей и поиск решений, а не на исполнение самого решения (кодов, алгоритмов).
Наш реальный путь выглядел примерно так: 
Много раз мы двигались в определенном направлении, понимали, что это движение не решает нашей проблемы, и откатывались назад. Мы искренне пытались найти какой-то универсальный путь, но его не было.
В итоге мы пришли к компромиссу.
Вот как сейчас выглядит наш компромиссный пайплайн:
- Устанавливаем связь.
Переписываем документацию под себя.
Упростите и сократите.
- Выбросьте лишнее.
Классифицируем таблицы по принципу «если есть значение — оставляем, если значения нет — удаляем».
Например, каталог «Типы доступа пользователей» или пустая таблица «TestTable» не представляли для нас никакой ценности.
- Давайте уточним конечную цель.
Цель — получение панельных данных с заданным уровнем качества за ограниченный период времени.
- Уточняем критерии качества.
Мы ставим на первое место полноту и надежность.
- Введем критерий затрат труда.
Мы понимаем, что можем сделать больше, но это иррационально.
Мы делаем то, что рационально.
- Выделяем проблемные места.
Конкретные показатели или категории, вызывающие сомнения в их достоверности.
- Мы находим пути решения проблем.
О них ниже.
- Давайте решим проблему.
Например, если в Алтайском крае под кодом 100 записана не категория «ячмень», как положено, а «голоса, отданные за бывшего мэра», то проблемной зоной является: показатель «Выборы мэра и ячмень».
», регион Алтайский край, все строки с кодом категории 100. Выбрасываем ненужные вещи – что именно? Сервисные таблицы (список пользователей базы данных).
Таблицы, информация из которых не имеет прямого отношения к данным, например, вложенная группировка внутри группы категорий (есть такое).
Таблицы с неверным именем, например, имя индикатора «fd_8001002_1» вместо «fd_8001002» (это копии таблицы данных в какой-то момент времени).
Таблицы типа TestTable. Старые версии справочников и т. д. Можно сказать, что мы вводим формальные критерии оценки ценности данных, критерии, которые «дешевы» для оценки с точки зрения времени.
Некоторые таблицы данных, возможно, представляли интерес, но восстанавливать информацию из них посчитали нерациональным.
И то, и другое из-за небольшого количества данных (< 1% of all rows), and because neither we nor Rosstat could eliminate some of the uncertainties. Какие решения мы использовали? «Я принимаю реальность, а не сопротивляюсь ей».
Или, другими словами, работали руки и глаза.
Печень тоже.
По каждому показателю собирался подробный отчет о полноте, периоде наблюдения и т.д. Исследователи нашей команды и сотрудники Росстата проверили показатели.
Напомню, особенностью показателей базы данных ПМО является их изменчивость и достаточно длительный период наблюдения.
Индикаторы «в данный момент» практически бесполезны; они имеют ценность только при сравнении между регионами или внутри региона на протяжении длительного временного ряда (тенденций).
Поэтому из массива данных были исключены те показатели из базы данных, которые имели очень короткий период наблюдения, прерывистость и низкую полноту по регионам.
Из 608 показателей в окончательных данных остались 355. Из более чем 2 тысяч проблемных случаев в справочниках все, кроме 187, нормируются путем расчета «подобия» по Левенштейну.
Эти 187 «сложных» дел были отправлены в Росстат для проверки на месте.
На момент написания этой статьи мы ожидали этих изменений.
В панельных данных, которые мы предоставляем исследователям, данные из категории «сомнительные» заменяются на «Нан».
В результате получаем 79Гб панельных данных и новую базу данных.
База для нас, Розетки - для исследователей, выводы - для всех.
Панели выглядят следующим образом: 
Показатели сгруппированы по заголовкам (в примере выше заголовок «Спорт»).
Коды категорий заменены их значениями.
В каждую таблицу добавлены единицы измерения и точное местоположение.
Оказалось удобно, не нужно ничего совмещать.
С этим могут работать даже те, кто хочет «просто посмотреть распределение в Excel» (а таких немало).
Цифры: 25 заголовков, 355 индикаторов, 211 миллионов строк, общий размер CSV 79 ГБ.
Перед загрузкой в формат панели данные сохраняются в нашей базе данных PostgreSQL. По сравнению с оригинальным, он был почищен и собран заново.
Мы упростили структуру до трёх типов сущностей:
- Заголовки — это таблицы данных, содержащие несколько показателей, объединенных одной темой (например, спорт, народонаселение, здравоохранение и т. д.).
- Каталоги групп категорий, содержащие два поля — идентификатор категории и название категории.
Используя идентификатор категории, мы можем сопоставить каталог с определенным атрибутом в таблице.
- Мета-каталоги — это каталоги, связанные со всеми категориями.
К ним относятся: справочник показателей, их единицы измерения, кодировка рубрик, ОКТМО.
Каждый атрибут в категории связан со своим справочником (кроме поля со значением показателя и года).
Это почти 3НФ (код ОКТМО еще содержит структурную информацию), в нем легко разобраться, его легко загрузить в панельные данные.
А как насчет выводов из этой работы?
Есть выводы.Вот процессы нашего конвейера:
- Командное общение.
В этом вся суть Scrum.
- Выбросьте лишнее.
Это история про MVP и минимальную достаточность.
- Конкретные цели и критерии.
В целом это касается здравого смысла.
- Выявляем проблемные места, ищем решения и решаем их.
Речь идет о MVP, здравом смысле и итеративном подходе.
Несмотря на нестандартность задачи, все столь любимые в ИТ методологии и здесь работают. Допустим, я их знаю, применяю, этого достаточно? Не совсем, давайте добавим значение «Я их адаптирую».
Как я писал выше, 80% времени при работе с базой данных ПО было потрачено на устранение неопределенностей и поиск решений.
Наверное, каждый из нас способен принять лучшее решение, если у него есть две вещи: знания и полнота информации.
Что делать, если полной информации нет? И устранение неопределенностей не приближает вас к цели? Может быть, стоит более системно устранять неопределенность? В такой момент я вспоминаю цикл НОРД , состоящий из четырех повторяющихся процессов: наблюдение, ориентировка, решение, действие.
Эту мысленную стратегию разработал стратег и полковник ВВС США Джон Бойд. Раз он полковник, значит, он придумал это не для того, чтобы купить молоко, а, скажем, для чего-то типа победы на войне.
В этой стратегии есть два пути достижения целей: быстрее завершить цикл НОРД или повысить качество принимаемых решений за счет удлинения процессов наблюдения и ориентации.
И сейчас я бы сказал, что первый способ более выгоден.
Мы потратили очень много времени на «понимание» базы данных программного обеспечения.
Мы поняли больше, но к готовому продукту это нас не приблизило.
Прогресс начался, когда мы начали отсекать ненужное, отсекать непонятное и делать некий ограниченный продукт на основе известных и понятных данных — где у нас было достаточно знаний и понимания вопроса, чтобы создать четкую, применимую ценность.
Да, это Agile, говорите вы? Да, точно.
Адаптирован под задачу.
Адаптация заключается в том, что каждый трек начинается от устранения неопределенности в некоторых локальная область задач (когда нет технического задания, когда план пути невозможно написать, не устранив эту неопределенность), постановку целей и, наконец, принятие решений.
Теги: #Читальный зал #Хранение данных #государство #Открытые данные #sql #госуслуги #правительство и Интернет
-
Новый Macbook От Apple
19 Oct, 24 -
Особенности Удаленного Груминга
19 Oct, 24 -
Рубиновый Подкаст Noname S04E15
19 Oct, 24 -
Sharpdevelop 3.0, Версия-Кандидат 1
19 Oct, 24 -
Утка И Крышка
19 Oct, 24
