OpenData – як з ними бути? Частина перша

Як обробляти великі масиви транзакцій з порталу Є-Дата? Як можна полегшити роботу в середовищі мови програмування R? Як не загубитись у безлічі транзакцій місцевих бюджетних розпорядників?

Всі, хто працює з Офіційним порталом публічних фінансів, знають – наскільки швидко там накопичується інформація про платежі. Якщо у полі ваших інтересів знаходиться дві-три сільради, то щомісячний приріст масиву даних не такий вже й значний. Але якщо ви працюєте з даними розпорядників бюджетних коштів великих міст – щотижневий приріст даних про платежі може досягати декількох тисяч записів! Перша проблема – не проґавити нічого нового і не повторювати роботу над вже колись обробленими даними. Друга проблема – що ж робити з таким масивом інформації? Для розпорядників бюджетних коштів з Кривого Рогу за рік накопичилось майже 200 тис. платіжних транзакцій! І це за тієї умови, що я, мабуть, не всіх розпорядників знайшов! Звісно, що обробляти подібні масиви в редакторах електронних таблиць доволі проблематично. Також проблематичним є отримання таких масивів даних з порталу www.spending.gov.ua за умови користування лише веб-інтерфейсом. Нижче я розповім про свою методику роботи з порталом і про операції над отриманим масивом даних. Важливою умовою є правильний вибір “що ми хочемо отримати”. Якщо вам треба продивитись декілька ЄДРПОУ і знайти максимальні за сумою платежі – то без моєї методики вам буде простіше жити. Якщо ж вам необхідно постійно моніторити “що відбувається з бюджетом міста” і при цьому мати гарантований доступ до вашої накопиченої інформації, виробляти періодичні інформаційні та графічні матеріали – я сподіваюсь що описана нижче методика допоможе вам підняти швидкість і якість обробки даних з платіжних транзакцій.

Методика складається з наступних послідовних кроків:

  1. встановлення програмного забезпечення;
  2. отримання даних платіжних транзакцій з порталу spending.gov.ua за допомогою інтерфейсу прикладного програмування (API); створення локальної бази даних формату SQLite, до якої будемо заносити поточний і всі подальші отримані масиви даних; завантаження отриманого масиву платіжних транзакцій до локальної бази даних за допомогою скрипта, написаного мовою програмування PHP;
  3. підключення до бази даних з середовища RStudio мови програмування R;
  4. подальша робота з даними безпосередньо мовою програмування R.

У цій статті ми розглянемо перші два кроки. Робота з локальною базою транзакцій буде описана в наступній статті. А в подальших статтях ми розглянемо конкретні приклади практичного застосування зібраного набору даних: подивимось цікаві сюжети, які можуть розкопати в цих даних журналісти та активісти.

Для описаних вище кроків нам знадобиться декілька наборів програмного забезпечення. Але всі програмні пакети, які я використовую, – це вільне програмне забезпечення (OpenSource). Більше того – це кросплатформне програмне забезпечення. Вам не треба гаяти час на пошук альтернативних програм під інші платформи. Описані програмні пакети будуть працювати і на платформі Windows, і на MacOS, і на Linux/UNIX/FreeBSD та їм подібних системах. Я не буду описувати – як встановити це програмне забезпечення. В мережі Інтернет є безліч ресурсів та форумів, де все це неодноразово описано. Ми зосередимось власне на роботі з даними. Всі робочі команди я буду писати у синтаксисі командного рядка операційних систем Linux та Windows. Проте, я не думаю — що для інших операційних систем ці команди будуть суттєво відрізнятись. Набір скриптів для роботи в середовищі ОС Linux можна взяти тут. Набір скриптів та набір необхідного програмного забезпечення для роботи в середовищі ОС Windows можна взяти тут. Через те, що в архіві для Windows містяться ще й інсталятори програм – він значно відрізняється за розміром від лінуксового набору скриптів.
Тепер перелічимо, які програмні пакети необхідні нам для роботи:

  1. для завантаження даних за допомогою API-інтерфейсу я використовую утиліту cURL;
  2. для створення локальної бази даних SQLite нам знадобиться базовий пакет мови програмування PHP (поточна версія – php5) і додаткові пакети php5-json та php5-sqlite. Хоча, при установці нових версій PHP ці пакети можуть бути “вшитими” в базовий набір пакетів. Власне СУБД SQLite встановлювати не обов’язково;
  3. для завантаження отриманого масиву даних до локальної бази треба виконати скрипт, який перепише дані з JSON-файлів до бази даних;
  4. для подальшої обробки даних нам знадобляться програмні засоби, які створено для роботи з мовою програмування R. Зокрема: сама мова R (базовий набір пакетів) і пакети RSQLite та dplyr для підключення до бази даних SQLite та для вибирання і сортування даних. Додатково я використовую пакети tidyr та magrittr (зазвичай вони вони використовуються у зв’язці magrittr-dplyr-tidyr) для сортування, перекомпіляції, агрегації та іншими операціями над таблицями даних. І, нарешті, для створення інфографіки, я використовую пакет ggplot2. Всі ці пакети встановлюються безпосередньо виконаннями команд в середовищі мови програмування R.

Тепер давайте розпочнемо!

Отримання даних з порталу Є-Дата

Нагадаю вам, що для цього кроку нам необхідно встановити на комп’ютер утиліту cURL, за допомогою якої ми будемо зв’язуватись з API-інтерфейсом порталу Є-Дата.
Отримання даних відбувається за допомогою командних запитів до порталу публічних фінансів. Для створення командного запиту нам необхідно вказати часовий проміжок та ЄДРПОУ платників. Можна вказати ЄДРПОУ отримувачів платежів. Також можна вказати одночасно ЄДРПОУ і платників і отримувачів. Звертаю вашу увагу на те, що ми можемо завантажувати дані одночасно по декільком юридичним особам (вказавши в запиті до сервера декілька ЄДРПОУ). Для повноти картини нам треба знати скільки бюджетних коштів заплатили розпорядники та скільки вони отримали від інших розпорядників. Тому я зазвичай формую два запити. В одному вказую набір конкретних ЄДРПОУ в якості платників, в другому – ті самі ЄДРПОУ в якості отримувачів. Запити вводяться в інтерфейсі командного рядка. Утиліта cURL надсилає їх на сервер Є-Дата. На практиці це все виглядає отак:

В командному рядку ОС Linux:
curl -H “Content-Type:application/json” -X POST http://api.e-data.gov.ua:8080/api/rest/1.0/transactions -d ‘{ “startdate” : “14-09-2015”, “enddate” : “30-06-2016” , “payers_edrpous”:[“02124781”, “04544501”]}’ > post_e-data_osvita_out.json

В командному рядку ОС Windows:
curl -H “Content-Type:application/json” -X POST http://api.e-data.gov.ua:8080/api/rest/1.0/transactions -d “{“”startdate””: “”14-09-2015″”, “”enddate””: “”30-06-2016″”, “”payers_edrpous””: [“”02124781″”, “”04544501″”]}” > post_e-data_osvita_out.json

Ми просимо утиліту cURL зв’язатись з сервером порталу Є-Дата і витягти звідти всі вихідні транзакції (платежі) двох розпорядників, які вони здійснили за період з 14 вересня 2015 року до 30 червня 2016 року. Нас цікавлять ЄДРПОУ 02124781 (Саксаганський районний відділ освіти в Кривому Розі) та ЄДРПОУ 04544501 (Довгинцівський районний відділ освіти в Кривому Розі). Також ми просимо весь завантажений масив даних зберегти у файл post_e-data_osvita_out.json. При виконанні вищенаведеної команди ми отримаємо файл формату JSON – широко поширеного формату передачі табличних даних. В цьому файлі містяться дані про всі платежі цих двох розпорядників (за вказаний період), які були проведені через Державне казначейство. Нагадаю вам, що портал публічних фінансів розпочав роботу 15 вересня 2015 року. Саме з цієї дати почалась он-лайн публікація.
Тепер промоніторимо цих самих розпорядників на отримані ними платежі за той самий період:

В командному рядку ОС Linux:
curl -H “Content-Type:application/json” -X POST http://api.e-data.gov.ua:8080/api/rest/1.0/transactions -d ‘{ “startdate” : “14-09-2015”, “enddate” : “30-06-2016” , “recipt_edrpous”:[“02124781”, “04544501”]}’ > post_e-data_osvita_in.json

В командному рядку ОС Windows:
curl -H “Content-Type:application/json” -X POST http://api.e-data.gov.ua:8080/api/rest/1.0/transactions -d “{“”startdate””: “”14-09-2015″”, “”enddate””: “”30-06-2016″”, “”recipt_edrpous””: [“”02124781″”, “”04544501″”]}” > post_e-data_osvita_in.json

В принципі ми можемо вписати і три, і п’ять, і десять ЄДРПОУ одночасно. Але – майте совість! Не перевантажуйте сервер порталу! Особливо в будні дні, коли численні розпорядники бюджетних коштів вносять туди свої дані про укладені договори.
Що робити з двома отриманими файлами? В принципі – формат JSON розпізнається багатьма аналітичними програмними засобами. Це один із стандартних форматів передачі табличних даних. І ви можете напряму працювати з цими у багатьох програмних пакетах. Проте, ми підемо далі. Нам треба сформувати свою базу даних. Вона потрібна через те, що згодом у нас набереться така кількість даних про платежі, що постане питання сортування та фільтрування цих даних перед обробкою. Одним зі шляхів роботи з великими таблицями даних є формування бази даних та отримування необхідних “порцій” даних за допомогою запитів до цієї бази. Тепер приступимо до створення бази даних!

Створення локальної бази даних

Для створення і заповнення бази даних повинні бути встановлені на комп’ютері мова прогамування PHP (php5) із додатковими пакетами php5-json та php5-sqlite. Набір необхідних команд для ОС Linux міститься у цьому архіві, для ОС Windows — у цьому архіві. Завантажте цей архів і розпакуйте в потрібну вам директорію файлової системи. У вас з’явиться директорія spending, в якій будуть файли скриптів db_init.sh та import.php, а також субдиректорія db. В субдиректорії db міститься файл init.sql. Потім в цій субдиректорії буде розміщуватись і ваша локальна база платіжних транзакцій.
Тепер приступимо до створення бази даних. Для цього нам треба в інтерфейсі командного рядка перейти до директорії spending і виконати там команду:

В командному рядку ОС Linux:
sh db_init.sh

Дії для ОС Windows:
1. необхідно встановити тільки SQLite, для цього необхідно вміст архіву (sqlite-tools-win32-x86-3140100.zip) скопіювати у директорію DB;
2. Скопіювати у директорію DB скрипт init.sql;
3. виконати у командному рядку:
db\sqlite3.exe -init db\init.sql db\e_data.db .exit

Цією командою ми створимо порожню базу даних. У директорії db (DB для Windows версії) з’явиться файл data.sq3 (e_data.db для Windows-версії скрипта). Це база даних формату SQLite-3.
Створення бази даних відбувається одноразово. В цю базу даних ми потім будемо дописувати все нові і нові масиви даних, отримані нами з порталу Є-Дата. Якщо ви потім знову запустите команду створення бази даних, то затрете накопичену базу новим порожнім файлом.
Тепер переходимо до завантаження даних в локальну базу.

Завантаження даних транзакцій до локальної бази

Для цього кроку нам не потрібно встановлювати жодних програмних засобів. Все необхідні програми ми вже встановили.
Щоб завантажити дані платежів із файлів формату JSON нам необхідно перенести ці два файли (post_e-data_osvita_out.json та post_e-data_osvita_in.json) в директорію spending. Після цього ми викликаємо інтерфейс командного рядка, переходимо до директорії spending і послідовно виконуємо дві команди:

В командному рядку ОС Linux:
cat post_e-data_osvita_out.json | php import.php
cat post_e-data_osvita_in.json | php import.php

Дії для ОС Windows:
1. необхідно встановити РНР, для цього треба вміст архіву (php-5.6.24-nts-Win32-VC11-x86.zip) скопіювати в директорію PHP;
2. виконати у командному рядку:
D:\WORK\DataStory\TEST\PHP\php-win.exe import.php post_e-data_osvita_in.json
D:\WORK\DataStory\TEST\PHP\php-win.exe import.php post_e-data_osvita_out.json

Результатом виконання обох команд буде “бігання плюсиків” на екрані командного рядка. Кожний з плюсиків означає завантаження до бази однієї транзакції. Після цих команд у локальній базі даних data.sq3 заповниться таблиця transactions, в якій і будуть міститись дані про платежі.
В локальну базу даних можна записувати транзакції всіх інших цікавих вам розпорядників. При цьому при внесенні до бази будуть перевірятись значення ID транзакцій. Дублюватись транзакції в базі не будуть.

Послідовність роботи описаними вище інструментами показана на цьому графіку:

Робота з даними порталу Є-Дата. Кроки 1 та 2

Робота з даними порталу Є-Дата. Кроки 1 та 2

Тепер у нас є своя локальна база платіжних транзакцій. Ми можемо вносити до неї всі підряд платежі від цікавих нам розпорядників. Також будемо туди записувати і “вхідні” платежі на цих розпорядників. Звісно, що поки загальний розмір бази коливається в межах 2-3 тис. записів – виграш у порівнянні з іншими методами не дуже значний і, навіть, сумнівний. Проте, якщо ви націлені на використання мови R і ваші розпорядники вже здійснили 100-200-300 тис. платежів – описана методика дасть вам можливість створити впорядкований масив даних і працювати з ним безпосередньо з програмного середовища R. Також ви не будете залежати від наявності/швидкості мережі Інтернет.
У наступних статтях буде описано роботу зі створеною локальною базою даних транзакцій безпосередньо в середовищі мови програмування R. Також буде розглянуто декілька практичних прикладів для пошуку цікавих сюжетів у зібраному масиві даних.

І нагадую всім читачам, що викладена мною методика – це лише один із безлічі шляхів отримання та обробки даних. Існують багато інших варіантів завантаження та обробки даних транзакцій. Один з прикладів – подібні за функціональністю скрипти, але написані мовою програмування Python. Численні активісти пишуть багато власних схем отримання та обробки даних.

PS Вся робота виконується виключно у вільний час. Проте, ми не відмовимось від задоволення за філіжанкою кави обговорити доповнення та виправлення в написаному програмному коді… Подарувати трохи коштів на каву/чай можна грошовим переказом. Меню “Благодійність” розташовано зверху на бічній панелі цього сайту.

Leave a Comment