Як працювати з відкритими даними Верховної Ради України, використовуючи Excel

25 листопада 2016 року

Як працювати з відкритими даними Верховної Ради України, використовуючи Excel

Громадянська мережа ОПОРА в рамках реалізації проекту “Портал відкритих даних Верховної Ради України” за підтримки Програми розвитку ООН розробила відео-урок про роботу з відкритими парламентськими даними із використанням MS Excel. Активіст організації Григорій Сорочан поділився власним досвідом роботи з порталом відкритих даних ВРУ opendata.rada.gov.ua

Відео розраховане на початківців у роботі з відкритими даними та дає відповіді на питання:

Які дані та в якому форматі можна взяти з порталу відкритих даних ВРУ?
Як імпортувати дані в MS Excel та які несподіванки нас можуть очікувати?
Як працювати з даними в Excel, використовуючи функції та зведені таблиці?

Програмне забезпечення, яке використовувалося під час запису відео - це програма MS Excel 2010 (українська версія).

Під час уроку було розглянуто такі набори даних з порталу opendata.rada.gov.ua:

Законопроекти (Інформація про законопроекти 8 скликання). Набір даних містить інформацію про зареєстровані законопроекти, авторський колектив та ініціаторів, виконавців (комітети), супровідні документи та етапи проходження законопроектів. Оновлення набору здійснюється щодобово. Формат даних: CSV.

Народні депутати України (Інформація про народних депутатів України 8 скликання). Набір даних містить:

а) довідкову інформацію про народних депутатів, їх посади в фракціях, комітетах та інших об’єднаннях, про помічників депутатів

б) відомості з декларацій про доходи народних депутатів

Оновлення набору здійснюється щодобово. Формат даних: CSV.

Пленарні засідання (Інформація про пленарні засідання 8 скликання). Набір даних містить інформацію про результати реєстрації депутатів на пленарних засіданнях, результати голосувань (в тому числі й поіменних) за питання порядку денного та інформацію про виступи депутатів на засіданнях. Оновлення: щодобово

Оновлення набору здійснюється щодобово. Формат даних: CSV.

Формат СSV є основним форматом даних на порталі відкритих даних ВРУ

CSV (від англ. comma-separated values «значення, розділені комою», іноді character-separated values «значення, розділені символом») – файловий формат, який використовується для представлення табличних даних, у якому поля (стовпці таблиці) відокремлюються символом коми (або іншим роздільником)

Для того, щоб відкрити файл в форматі CSV в програмі MS Excel рекомендовано використовувати Майстер імпорту тексту, знайти його можна на вкладці Дані. Майстер складається з таких кроків:

обирається файл, з якого здійснюється імпорт;
вказується формат вихідних даних (з розділювачем чи фіксованої ширини), кодування та номер рядка з якого відбуватиметься імпорт;
задається роздільник (чи комбінація роздільників) стовпців, наприклад, це може бути символ «кома» - «,»;
вказуються формати даних стовпців у вихідній таблиці;
задається місце (лист, стартова комірка), куди буде імпортовано дані з файлу CSV.

Під час цієї операції користувач може зіштовхнутись із «негараздами імпорту». Так, часто виникають такі ситуації:

Excel не завжди якісно імпортує CSV файли, які представляють великі за розміром таблиці;
часом трапляються проблеми з «автоматичним» розпізнанням формату даних;
обмеження за кількістю рядків на листі Excel (1 048 576 рядків для версії 2010), тому, наприклад, набір даних, який надає інформацію про поіменні результати голосування, та який містить 2 906 794 записів, імпортувати повністю на один лист неможливо і в таких ситуаціях доцільно використовувати, наприклад, середовище R (за посиланням Ви знайдете урок щодо роботи в середовищі R).

За цим посиланням можна знайти імпортовані та почищені набори даних в MS Excel, а також детальні описи цих наборів даних: https://app.box.com/s/mg2p2cctygucds5bg7jpoptm5s1bfp33

Для того, щоб закріпити практично прийоми роботи в MS Excel, на основі імпортованих даних було вирішено два досить простих, але стандартних за технічними прийомами, практичних завдання.

Практичне завдання 1. Визначити у кого з офіційних ініціаторів законопроекти найчастіше стають нормативними актами (використовувалася група даних: Законопроекти – Інформація про законопроекти 8 скликання).

Практичне завдання 2. Підрахувати кількість помічників народних депутатів (використовувалася група даних: Народні депутати України – Інформація про народних депутатів України 8 скликання).

В обох завданнях реалізовується прийом об’єднання даних з різних листів Excel за ключовим полем.

Так, поняття «Ключове поле» (стовпчик таблиці) – це поле, яке є унікальним в одній таблиці, і за яким можна задати відповідне значення в іншій таблиці, де відповідне значення повторюється декілька разів. Для об’єднання даних з різних листів (таблиць) по ключовому полю можна використовувати комбінацію функцій Excel:

INDEX (), російський еквівалент ИНДЕКС();

MATCH(), російський еквівалент ПОИСКПОЗ().

Функція INDEX() повертає значення елемента таблиці (масиву), вибраного за індексами (номерами) рядків і стовпців.

Синтаксис: INDEX(масив; номер_рядка; [номер_стовпця])

масив (обов’язковий аргумент) - діапазон клітинок (таблиця), з яких повертатиметься значення;

номер_рядка (обов’язковий аргумент) - вибирає рядок у масиві, з якого повертається значення (ПРИМІТКА. Ми використовуватимемо для визначення номеру рядка функцію MATCH());

номер_стовпця (необов’язковий аргумент) - вибирає стовпець у масиві, з якого повертається значення.

Функція MATCH() шукає вказаний елемент у діапазоні клітинок і повертає відносне розташування цього елемента в діапазоні

Синтаксис: MATCH(шукане_значення; масив_перегляду; [тип_зіставлення])

шукане_значення (обов'язковий аргумент) – значення, яке потрібно знайти в масиві_перегляду;

масив_перегляду (обов'язковий аргумент) - діапазон клітинок, в якому шукають значення;

тип_зіставлення (необов’язковий аргумент) - число -1, 0 або 1 (в нашій ситуації ми використовуватимемо значення цього параметру «0», що відповідає пошуку «точного» значення).

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

Завдання 1 – https://app.box.com/s/nyjjqoi91vb7htprs28p57j8s8is39ml

Завдання 2 - https://app.box.com/s/c9yd0owafu9wa33c2vw667uorm98mv5j

Замість висновків що варто пам’ятати:

під час імпорту пам'ятайте про те, що Excel не завжди імпортує коректно;
перед тим, як серйозно працювати з наборами даних, вивчіть структуру усієї групи (на жаль, описів на сайті поки нема);
не забувайте, що потрібно після кожної дії перевіряти, наскільки коректно вона була виконана, помилки трапляються часто;
не слід боятися працювати в Excel з наборами з порталу відкритих даних, це реально;
для чистки даних можна використовувати OpenRefine;
альтернативою до Excel є Calc з LibreOffice, зокрема, він якісніше імпортує CSV;
портал відкритих даних Верховної Ради України працює все ще в тестовому режимі, проте, систематично надає якісну інформацію в форматі відкритих даних про народних депутатів, законопроекти та перебіг пленарних засідань.

Посилання на презентацію: https://app.box.com/s/88f88722jwi6l8qwdsacyuohcts6bquo