Getting stories from spreadsheets
How can you extract data that may not be in the format you want in order to use it for your stories?
How do you find stories within data? How do you extract this data in the first place? This was the topic of discussion at the July Nairobi Hacks/Hackers meetup, which featured a Data Extraction masterclass led by Prestone Adie, a data scientist, storyteller, open data analyst, and podcaster, who explained how to use a variety of tools to get the most out of datasets that may contain volumes of information.
Spreadsheets are a useful source of information for journalists, because they often contain information that could be used to tell and add depth to stories. The data is often collected from public sources, and it may not be immediately usable in the format it is presented in.
To make the data more usable, you may have to extract it, clean it, and put it in a machine-readable format.
Many government documents are shared as PDFs, which are handy for maintaining the security of the information they contain, but this limits how the information they contain can be extracted. To dig this information out, especially information in the form of tables, you can use Tabula, a tool that identifies tables based on their formatting, and allows you to save them as spreadsheets.
Once you extract data in the form of tables from PDFs, for example, you may have to look through the data to find errors. Zeros are sometimes read as letter o’s, the data may all end up in one row instead of multiple rows, and the numbers could even be read as words.
For the demonstration, Prestone used Tabula to extract a table from the 2018 Kenya Economic Survey.
Tabula can auto-detect tables within documents that you have uploaded, and you can also select tables directly within the document for extraction. Once you find the data you need within the document, Tabula exports it as a CSV (comma-separated value) file, which you can open in Excel, Google Sheets, or any other spreadsheet program.
Once you extract the spreadsheet from your document, you may need to check it for errors, and that is where Open Refine comes in. Open Refine, formerly known as Google Refine, is a powerful tool that cleans ‘messy’ data, allowing you to transform it from one format into another and explore large data sets with ease.
Prestone demonstrated how to use the ‘Split’ function on Open Refine to separate data that got lumped into one row when extracted using Tabula. By repeating the process over and over again, he was able to separate one row into six rows. While this process can be a little cumbersome, Prestone was able to demonstrate the versatility of the tool by using functions and scripts that rely on the tool’s Java backend to work.
Other tools that Prestone demonstrated were Tableau Public for data visualization, and Grafana for analytics and monitoring.
When choosing a tool for your data analysis, Prestone explained that you need to understand what tasks you need it to do, and whether your audience would be able to understand the information you are putting across.
There you go! Now you can try to extract the data you need from documents directly rather than having to copy it out yourself. Hopefully these tools should help. If you need to learn more about data extraction and cleaning, as well as other tools that you may need in your storytelling, check out the Data Journalism course on academy.AFRICA.
You can watch a replay of the livestream from the July Hacks/Hackers Nairobi session here.
The worlds of hackers and journalists are coming together, as reporting goes digital and Internet companies become media empires.
Journalists call themselves “hacks,” someone who can churn out words in any situation. Hackers use the digital equivalent of duct tape to whip out code.
Hacker-journalists try and bridge the two worlds. Hacks/Hackers Africa aims to bring all these people together — those who are working to help people make sense of our world. It’s for hackers exploring technologies to filter and visualize information, and for journalists who use technology to find and tell stories. In the age of information overload and collapse of traditional business models for legacy media, their work has become even more crucial.
Code for Africa, the continent’s largest #OpenData and civic technology initiative, recognises this and is spearheading the establishment of a network of HacksHackers chapters across Africa to help bring together pioneers for collaborative projects and new ventures.
Follow Hacks/Hackers Africa on Twitter and Facebook and join the Hacks/Hackers community group today.
