OpenData та міські бюджети: як це робилось

Як писалась стаття “OpenData та міські бюджети”: методика, аналітика, програмні скрипти. Дивіться, вчіться, повторюйте!

Ця стаття є описанням методики створення статті “OpenData та міські бюджети“, в якій розглядались та аналізувались витрати районних відділів освіти Кривого Рогу. Тут детально покроково висвітлюється методика отримання даних, чищення та збагачення даних, аналітична обробка та створення графіки. Я оприлюднюю всі програмні скрипти, які я використовував при створенні згаданої вище статті.

Інструменти

Для роботи з первинними даними я використовував програмний пакет LibreOffice Calc. За допомогою цієї програми здійснено збирання даних до єдиної таблиці, чищення та збагачення даних.

Аналітична обробка та побудова графіки виконувалась в програмному середовищі R за допомогою графічного інтерфейсу RStudio. Для обробки даних та побудови графіків використано додаткові пакети dplyr, magrittr та ggplot2.

Вся робота здійснювалась на ноутбуці з операційною системою Debian GNU/Linux 8 (“Jessie”).

Отримання даних

Первинні дані транзакцій криворізьких районних відділів освіти я отримував з порталу spending.gov.ua з розділу “Пошук за трансакціями“. Вводячи ЄДРПОУ платника (конкретного відділу освіти) я скачував файли в форматі CSV. В той час, коли я збирав дані (січень 2016 року), одним файлом скачати можна було лише дані за місяць. Первинні дані за всі часові проміжки і по всіх відділах освіти просто збирались до єдиної зведеної таблиці.

Звертаю вашу увагу на те, що файли мають доволі складну структуру. Для того, щоб не перемішались рядки в первинних таблицях – треба використовувати параметри імпорту файлів:

  • кодування: кирилиця Windows-1251
  • розділювач: крапка з комою
  • розділювач тексту: лапки
  • додаткові опції: поля в лапках розпізнавати як текст

Після завантаження та об’єднання всіх цікавих нам таблиць – приступаємо до чищення та збагачення даних.

Чищення та збагачення даних

Чищення даних полягає в приведенні всіх записів до єдиної форми. Наприклад – в таблицях транзакцій від різних платників однакові підприємства називаються по різному. Самі платники також в одній таблиці можуть писатись із відмінами. Все це треба привести до єдиного вигляду. Це дозволить потім використовувати фільтри, групування, пошук за маскою та інше. Заразом я виконую чищення дубльованих пробілів, видаляю лапки (з ними потім в R бувають проблеми).

Для фізичних осіб-підприємців я видаляю всілякі дописи: ФОП, СПД-ФО, СПД та інші подібні. І призвище-ім’я-по_батькові фізичних осіб копіюю до поля “КОД ОДЕРЖУВАЧА” (там, де записані ЄДРПОУ юридичних осіб). Зверніть особливу увагу на перевірку правильності написання фізосіб. Трапляються помилки в прізвищах-іменах-по_батькові.

Після “наведення лоску” первинні дані можна трохи збагатити. Наприклад – додати поле “ТИП ОДЕРЖУВАЧА” і розділити одержувачів платежів на “фізичні” та “юридичні”.

Зведену таблицю по всіх періодах та всіх платниках це я зберігаю в електронну таблицю формату ODS (для подальшої роботи). Потім саме з цієї таблиці експортую в формат CSV для завантаження в програмне середовище R. Використання циклу ODS-CSV автоматично змінює кодування первинних даних з Windows-1251 на UTF-8. Саме юнікод є “рідним” кодуванням середовища R.

При імпорті даних в R в полях “КОД ПЛАТНИК” та “КОД ОДЕРЖУВАЧ” “з’їдаються” перші нулі в ЄДРПОУ. Проте – це не критично. Навіть без перших нулів ЄДРПОУ-коди все-одно залишаються унікальними ідентифікаторами.

Не забувайте – нормальна підготовка даних часто займає часу більше, ніж аналітична обробка. Але це найважливіший етап! Якщо буде сміття на вході – то буде і сміття на виході!

Аналітика та створення графіки

Для прикладів, які тут розглядаються, я створив “учбову” таблицю. Вона нічим не відрізняється від тієї таблиці, на основі якої писалась стаття “OpenData та міські бюджети“. Взяти учбову таблицю можна тут. Файл зі скриптами можна взяти тут. Кодування символів в обох файлах – UTF-8.

Для роботи з даними я використовую “тунельний інтерфейс” (pipeline). Він поєднує вихід-вхід окремих функцій. Це дозволяє відмовитись від створення великої кількості проміжних таблиць. Тунель викликається командою %>%. Реалізований він в пакеті magrittr.

Для початку завантажемо потрібні пакети та таблицю з даними в R:

#ПІДГОТОВКА ДО РОБОТИ
#завантажуємо потрібні пакети:
library(magrittr)
library(dplyr)
library(ggplot2)
#завантажуємо таблицю з даними:
trans.ucheb <- read.csv("trans_ucheb.csv", header = T, sep = ";")

Перша таблиця в описуваній статті – “Розподіл платежів по районних відділах освіти“. За допомогою R я робив обчислення і отримував чернетки таблиць. Остаточно оформлював ці таблиці вже в процесі публікації на сайті: писав “людські” заголовки, додавав назви підприємств, установ та організацій. Розрахунок чернетки цієї таблиці такий:

#РОЗРАХУНОК СУМАРНИХ ПЛАТЕЖІВ ПО РАЙОННИХ ВІДДІЛАХ ОСВІТИ
#запускаємо таблицю з даними в "тунель":
tbl_df(trans.ucheb) %>%
#вибраємо потрібні поля таблиці:
select(НАЗВА.ПЛАТНИКА, СУМА) %>%
#групуємо дані по районним відділам освіти:
group_by(НАЗВА.ПЛАТНИКА) %>%
#підсумовуємо дані:
summarise(
#рахуємо загальні кількості платежів:
all.count = n(),
#рахуємо загальні суми платежів:
suma = sum(СУМА, na.rm = TRUE),
#рахуємо середні значення платежів:
aver = mean(СУМА, na.rm = TRUE),
#рахуємо медіани платежів, завершуємо підсумовування
#та записуємо дані в окрему таблицю:
med = median(СУМА, na.rm = TRUE)) -> ucheb_RVO
#записуємо таблицю в окремий CSV-файл:
write.table(ucheb_RVO, "ucheb_RVO.csv", sep = ";", row.names = F)

Тепер нас цікавить розподіл платежів сумою менше 150 тис.грн. Це було  оформлено у вигляді графіку. Скрипт для побудови цього графіку перед Вами:

#ПОБУДОВА ГРАФІКУ РОЗПОДІЛУ ПЛАТІЖНИХ ТРАНЗАКЦІЙ
#одразу запускаємо експорт до PNG-файла:
png("trans_hist_RVO_150.png", width = 297, height = 210, units = "mm", res = 150)
#запускаємо таблицю з даними в "тунель":
tbl_df(trans.ucheb) %>%
#вибираємо потрібні поля таблиці:
select(НАЗВА.ПЛАТНИКА, СУМА) %>%
#приводимо суми платежів до формату "тис.грн.":
mutate(СУМА = СУМА/1000) %>%
#запускаємо побудову графіків:
ggplot(aes(x = СУМА, fill = НАЗВА.ПЛАТНИКА))+
#вибираємо тип графіку (гістограма) та параметри побудови:
geom_histogram(binwidth = 5, position = "identity") +
#задаємо границю по осі X (150 тис.грн.):
scale_x_continuous(limits=c(0, 150)) +
#розбиваємо на окремі під-графіки для кожного платника окремо:
facet_wrap(~НАЗВА.ПЛАТНИКА, nrow = 2) +
#задаємо "графічні фічі" - підписи, шрифти, кольори:
labs(y = "кількість транзакцій", x = "сума транзакції, тис. грн.", title = "Розподіл платіжних транзакцій сумою до 150 тис. грн.\nпо районних відділах освіти Кривого Рогу (15.09 - 31.12 2015 р.)") +
theme(text = element_text(family = "Droid Serif", face = "italic", size = 16, colour = "firebrick4", lineheight = 0.9), legend.position = "none", legend.text = element_text(family = "Droid Serif", face = "italic", size = 14, colour = "firebrick4", lineheight = 0.8, angle = 0), axis.text.x = element_text(colour = "gray15", face="bold", size= 12, angle = 0, vjust = 1, hjust = 0.5), axis.text.y = element_text(colour = "gray15", face="bold", size= 12, angle = 0, vjust = 0.5, hjust = 1), axis.ticks = element_line(colour = "orange", size = 0.2), plot.background = element_rect(fill = "gray95"), panel.grid.major = element_line(colour = "orange", size = 0.2), panel.grid.minor = element_line(colour = "gray90"), panel.background = element_rect(fill = "gray95"))
#закриваємо графічний процес та записуємо дані в PNG-файл:
dev.off()

Результат виконання скрипту виглядає ось так:

Розподіл платіжних транзакцій сумою до 150 тис. грн. по районних відділах освіти Кривого Рогу

Розподіл платіжних транзакцій сумою до 150 тис. грн. по районних відділах освіти Кривого Рогу

Для побудови таблиці “ТОП-25 отримувачів платежів від районних відділів освіти Кривого Рогу” я побудував окрему таблицю із загальними отриманими сумами, а потім при оформленні цієї таблиці для публікації на сайті – просто вибрав 25 найбільших отриманих сум. Тільки не забудьте дописати відсутні нулі в коди ЄДРПОУ при оформленні таблиці “до друку”! Скрипт для цього розрахунку виглядає ось так:

#РОЗРАХУНОК СУМАРНИХ ПЛАТЕЖІВ ПО ОДЕРЖУВАЧАМ
#запускаємо таблицю з даними в "тунель":
tbl_df(trans.ucheb) %>%
#вибраємо потрібні поля таблиці:
select(КОД.ОДЕРЖУВАЧ, СУМА) %>%
#групуємо дані по коду одержувачів платежів:
group_by(КОД.ОДЕРЖУВАЧ) %>%
#підсумовуємо дані:
summarise(
#рахуємо загальні кількості платежів:
all.count = n(),
#знаходимо значення максимального платежу:
maximum = max(СУМА, na.rm = TRUE),
#рахуємо загальні суми отриманих платежів:
suma = sum(СУМА, na.rm = TRUE),
#рахуємо середні значення отриманих платежів:
aver = mean(СУМА, na.rm = TRUE),
#рахуємо медіани отриманих платежів, завершуємо підсумовування
#та записуємо дані в окрему таблицю:
med = median(СУМА, na.rm = TRUE)) -> ucheb_ODERZH
#записуємо таблицю в окремий CSV-файл:
write.table(ucheb_ODERZH, "ucheb_ODERZH.csv", sep = ";", row.names = F)

Наступним кроком була побудова графіку із порівнянням отриманих платежів фізичними та юридичними суб’єктами. Для побудови цього графіку я використав такий скрипт:

#ПОБУДОВА ГРАФІКУ ОТРИМАНИХ ПЛАТЕЖІВ ФІЗИЧНИМИ ТА ЮРИДИЧНИМИ СУБ’ЄКТАМИ
#одразу запускаємо експорт до PNG-файла:
png("trans_bar_typ_RVO.png", width = 297, height = 210, units = "mm", res = 150)
#запускаємо таблицю з даними в "тунель":
tbl_df(trans.ucheb) %>%
#вибираємо потрібні поля таблиці:
select(НАЗВА.ПЛАТНИКА, ТИП.ОДЕРЖУВАЧ, СУМА) %>%
#приводимо суми платежів до формату "тис.грн.":
mutate(СУМА = СУМА/1000) %>%
#групуємо дані по платникам та типам одержувачів:
group_by(НАЗВА.ПЛАТНИКА, ТИП.ОДЕРЖУВАЧ) %>%
#підсумовуємо загальні суми одержаних платежів:
summarise(total.sum = sum(СУМА, na.rm = TRUE)) %>%
#запускаємо побудову графіків:
ggplot(aes(x = ТИП.ОДЕРЖУВАЧ, y = total.sum, fill = ТИП.ОДЕРЖУВАЧ)) +
#вибираємо тип графіку (стовпчаста діаграма) та параметри побудови:
geom_bar(stat = "identity") +
#розбиваємо на окремі під-графіки для кожного платника окремо:
facet_wrap(~НАЗВА.ПЛАТНИКА, nrow = 2) +
#задаємо "графічні фічі" - підписи, шрифти, кольори:
labs(y = "сума транзакцій, тис. грн.\n", x = "\nтип суб’єктів", title = "Розподіл платіжних транзакцій між типами суб’єктів\nпо районних відділах освіти Кривого Рогу (15.09 - 31.12 2015 р.)" +
theme(text = element_text(family = "Droid Serif", face = "italic", size = 16, colour = "firebrick4", lineheight = 0.9), legend.position = "none", legend.text = element_text(family = "Droid Serif", face = "italic", size = 14, colour = "firebrick4", lineheight = 0.8, angle = 0), axis.text.x = element_text(colour = "gray15", face="bold", size= 12, angle = 0, vjust = 1, hjust = 0.5), axis.text.y = element_text(colour = "gray15", face="bold", size= 12, angle = 0, vjust = 0.5, hjust = 1), axis.ticks = element_line(colour = "orange", size = 0.2), plot.background = element_rect(fill = "gray95"), panel.grid.major = element_line(colour = "orange", size = 0.2), panel.grid.minor = element_line(colour = "gray90"), panel.background = element_rect(fill = "gray95"))
#закриваємо графічний процес та записуємо дані в PNG-файл:
dev.off()

Результат цього скрипту такий:

Розподіл платіжних транзакцій між типами суб’єктів по районних відділах освіти Кривого Рогу

Розподіл платіжних транзакцій між типами суб’єктів по районних відділах освіти Кривого Рогу

Після побудови графіку отриманих фізичними та юридичними особами загальних сум я обчислив таблицю “ТОП-25 фізичних осіб-отримувачів платежів від районних відділів освіти Кривого Рогу“. Цю таблицю я будував так само, як і попередні: робив в R таблицю-чернетку, а потім доводив її до нормального вигляду вже в процесі публікації. Скрипт для розрахунку виглядає наступним чином:

#РОЗРАХУНОК СУМАРНИХ ПЛАТЕЖІВ ПО ОДЕРЖУВАЧАМ
#запускаємо таблицю з даними в "тунель":
tbl_df(trans.ucheb) %>%
#вибраємо потрібні поля таблиці:
select(КОД.ОДЕРЖУВАЧ, СУМА, ТИП.ОДЕРЖУВАЧ) %>%
#вибираємо лише платежі фізичним особам:
filter(ТИП.ОДЕРЖУВАЧ == "фізичні") %>%
#групуємо дані по коду одержувачів платежів:
group_by(КОД.ОДЕРЖУВАЧ) %>%
#підсумовуємо дані:
summarise(
#рахуємо загальні кількості платежів:
all.count = n(),
#знаходимо значення максимального платежу:
maximum = max(СУМА, na.rm = TRUE),
#рахуємо загальні суми отриманих платежів:
suma = sum(СУМА, na.rm = TRUE),
#рахуємо середні значення отриманих платежів:
aver = mean(СУМА, na.rm = TRUE),
#рахуємо медіани отриманих платежів, завершуємо підсумовування
#та записуємо дані в окрему таблицю:
med = median(СУМА, na.rm = TRUE)) -> ucheb_ODERZH_fizychni
#записуємо таблицю в окремий CSV-файл:
write.table(ucheb_ODERZH_fizychni, "ucheb_ODERZH_fizychni.csv", sep = ";", row.names = F)

Останнім кроком дослідження була побудова графіку одержаних фізичними особами сум від різних відділів освіти. Цей графік побудовано за допомогою ось цього скрипту:

#ПОБУДОВА ГРАФІКУ ОТРИМАНИХ ФІЗИЧНИМИ СУБ’ЄКТАМИ ПЛАТЕЖІВ
#одразу запускаємо експорт до PNG-файла:
png("trans_bar_fizychni_RVO.png", width = 297, height = 210, units = "mm", res = 150)
#запускаємо таблицю з даними в "тунель":
tbl_df(trans.ucheb) %>%
#вибираємо потрібні поля таблиці:
select(НАЗВА.ПЛАТНИКА, ТИП.ОДЕРЖУВАЧ, СУМА) %>%
#приводимо суми платежів до формату "тис.грн.":
mutate(СУМА = СУМА/1000) %>%
#вибираємо лише платежі фізичним особам:
filter(ТИП.ОДЕРЖУВАЧ == "фізичні") %>%
#групуємо дані по платникам:
group_by(НАЗВА.ПЛАТНИКА) %>%
#підсумовуємо дані:
summarise(total.sum = sum(СУМА, na.rm = TRUE)) %>%
#запускаємо побудову графіків:
ggplot(aes(x = НАЗВА.ПЛАТНИКА, y = total.sum)) +
#вибираємо тип графіку (стовпчаста діаграма) та параметри побудови:
geom_bar(stat = "identity", fill = "gold2") +
#задаємо "графічні фічі" - підписи, шрифти, кольори:
labs(y = "сума транзакцій, тис. грн.\n", x = "\nназва платника", title = "Розподіл платіжних транзакцій фізичним особам\nпо районних відділах освіти Кривого Рогу (15.09 - 31.12 2015 р.)") +
theme(text = element_text(family = "Droid Serif", face = "italic", size = 16, colour = "firebrick4", lineheight = 0.9), legend.position = "none", legend.text = element_text(family = "Droid Serif", face = "italic", size = 14, colour = "firebrick4", lineheight = 0.8, angle = 0), axis.text.x = element_text(colour = "gray15", face="bold", size= 9, angle = 0, vjust = 1, hjust = 0.5), axis.text.y = element_text(colour = "gray15", face="bold", size= 12, angle = 0, vjust = 0.5, hjust = 1), axis.ticks = element_line(colour = "orange", size = 0.2), plot.background = element_rect(fill = "gray95"), panel.grid.major = element_line(colour = "orange", size = 0.2), panel.grid.minor = element_line(colour = "gray90"), panel.background = element_rect(fill = "gray95"))
#закриваємо графічний процес та записуємо дані в PNG-файл:
dev.off()

Після виконання скрипту ми отримуємо графік загальних сум, одержаних фізичними особами-підприємцями від різних районних відділів освіти Кривого Рогу:

Розподіл платіжних транзакцій фізичним особам по районних відділах освіти Кривого Рогу

Розподіл платіжних транзакцій фізичним особам по районних відділах освіти Кривого Рогу

Як бачите – нічого складного в обробці подібних даних немає! Більшість подібної “аналітики” являє собою примітивне перетасовування таблиць та обчислення усереднених показників. Сучасні аналітичні інструменти дозволяють Вам одразу поєднати аналітику з графічним дизайном. Проте – шрифти, заливки та інші “фічі” неможливі без первинних даних. Я раджу Вам більше зосереджуватись на підготовці даних та продумувати – що саме Ви хочете показати читачам!

Leave a Comment