Відео
Як працювати з відкритими даними Верховної Ради України, використовуючи 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