There is a general sense of helplessness when it comes to analyzing public data, especially as people think it involves insane amounts of statistical mastery and in-depth knowledge of complicated statistical software.
This is especially nerve wracking for data journalists, who are keen on using data to write stories that can actually influence a certain aspect of our society, such as healthcare or education. Truth be told, analyzing data and storytelling actually go hand in hand.
Since the Open Data initiative started, more and more data sets have seen the light of the day on various data-related portals. The most interesting data sets for journalists are the ones who are publicly available, simply because they are free to use and analyze. Those data sets are available on a variety of online sources, such as: www.data.gov, open.canada.ca, data.gov.uk and many more.
Open data portals contain thousands and thousands of data sets, related to various branches of government: education, business, economy, crime, justice, healthcare and more.
Once you start exploring the online data, you will see that it usually comes in 3 main formats: HTML, XML and PDF.
However, if you start investigating the data sets in more depth, you will quickly notice that there is only one format that’s present in almost every data set — the PDF. So, the logic goes that if you know how to analyze data that’s locked inside a PDF, you’ll know how to analyze any.
But what makes people want to store data in a non editable format?
First of all, when you save a data set as a PDF you are reducing its size, so it’s easier to store and upload to online databases. Secondly, since the PDF is not editable by default, you are making sure that no one tampers with your data and changes any of the ever-so-important numerical values. Remember, people spend countless hours gathering data and they are keen on protecting their hard work as much as possible.
So, once you find a PDF data set, where do you go next?
You now basically have only one option — you need to get that data into an Excel or CSV file format, while preserving source document accuracy as much as possible. After you do that, the next step would be to import that converted file into a data visualization tool of your choice, which we will cover later in this tutorial.
When it comes to exporting PDF data, the only tool in the market that has advanced enough PDF exporting capabilities is Able2Extract. That is because Able2Extract is not just a regular PDF converter. See, most (if not all) PDF converters on the market only convert PDF to Excel automatically, leaving you with a messy data set. The automatic conversion works good for one page invoices but converting a 1,000 page data set takes a lot more than that.
Able2Extract is the only converter that lets you fully customize your conversion by manually setting up row and column structure, prior to conversion. In addition it lets you preview the conversion results from within the software, which lets you export your data set as accurately as possible.
First, find your PDF data set. For this tutorial, we are going to use a practice data set containing all funded projects from Canadian Environmental Damages Fund. You can download it here.
Open the data set in Able2Extract and use custom PDF to Excel conversion to convert it to an Excel file. Set up row and column structure using the right side panel and make sure to check the “Preview conversion” box. Once satisfied, hit the convert button to send the data to Excel.
So, we got our data from PDF and into Excel. Great job!
The next step is to go to Excel and clean the data. This will take 15 minutes to 2 hours, depending on the data set, but the thing you are looking for in the end is to end up with data in the tabular format, which means there is a separate row for each record. It should look something like this:
Make sure you don’t have any empty rows or blank cells and that all text is formatted in the same way. If there is a row with 3 cells missing it’s best to delete the whole row because it can mess up your end result and produce inaccurate results.
Now that we have a clean and tidy data set, it’s time to give life to these numbers and visualize them. Enter data visualization.
Data visualization simply means to create interesting charts from just plain data, which makes it easier to understand and present to your readers. When it comes to visualizing data you have an option between a desktop dataviz tool and a cloud dataviz tool. We will explore one example of both.
Our recommended desktop software for visualizing complex data is Power BI. We are recommending it because of its compatibility with Excel and the fact that it’s free to use for datasets up to 1 GB. You can download it here.
Before we start with Power BI, you will need to know that analyzing data starts by asking questions and then using data to answer them. For example, you can ask questions regarding our practice data set before we even upload it to the dataviz tool:
- What was the EDF funding per region?
- Which group received the biggest funding?
Depending on the data set, you can ask a 1000 questions and, make no mistake, you will get a 1000 answers. OK, let’s move on to more serious stuff. Power BI.
Power BI
Power BI is a Business Intelligence tool created for monitoring business performance and discovering market opportunities. Today we will use it as a data journalism tool in order to answer the two questions above.
Once you open Power BI you first click on Get Data > Excel > Connect > Your file.
Choose a sheet where data is located and press Load. Alternatively, you can press Edit if you’d like to check your data set for mistakes once again.
Once you do so, you will find a blank canvas and your data values on the right sidebar panel.
These are the values we are going to slice and dice. Let’s try to answer our first question. If you remember, we wanted to know what was the EDF funding per region.
The basic data field there is EDF Funding so we’ll drag it into the “Values” box. The canvas immediately changes and it is now showing us the total EDF funding:
Let’s now introduce another data field. Select the “Pie chart”.
Drag the “Region” field into the “Legend” box. Congrats, you made your first data visualization! We now have an overview of the funding per region and we can already start answering some questions.
However, if you pay close attention you can see that we still don’t know the exact funding for each region. To show the exact values of data fields, go to “Format” panel:
Expand the “Detail Labels” category, find the Label Style and select “Both” from the drop down menu.
Our pie chart is now showing us the specific monetary values for each segment. Great, first question answered.
OK, next up is to see which Group received the biggest funding. We’ll repeat the process but we’ll use a different chart, just to demonstrate different features of Power BI.
First, find and click on the Clustered Bar Chart.
Drag the EDF Funding into the Values box and drag the Group into the Axis box. Turn on the data labels and you’ll quickly see that the University of Waterloo received the biggest funding — almost $320,000
Now that you know how to ask questions and visualize public data, we will now quickly go over another tool that can help you visualize your data in the Cloud. Have in mind that the Cloud tools only support lower file sizes, which means you’re best off using them for 10-20 page data sets. Luckily, the data set from our example is actually pretty small.
DataHero
DataHero is a cloud solution for Business Intelligence and data visualization. It allows you to connect files from numerous online and offline sources and it even has an integrated data cleaning tool, which is nice, but I do not recommend relying solely on it.
You can use DataHero for free, for files up to 2 MB in size. Anything larger than that, and you’ll probably have to pay a monthly subscription which is between $60 and $90. For this purpose, we are going to use a free plan.
Create an account, click on the Data tab and click on Import Data.
Find your Excel file, select the sheet and upload it:
On the next screen, check formatting and proceed.
What’s cool about DataHero is that it automatically suggests data visualizations:
I was originally interested in EDF Funding by project category so I’ll just create a brand new chart. DataHero uses the same drag & drop interface so it’s really easy to start using it.
First, drag the EDF Funding field onto the canvas.
Next, drag & drop the Project Category field.
As you can see, we received our answer. Most of the funding money (35%) went into Restoration projects and the rest was dispersed equally between other three categories.
There are other, more complex, data visualization tools but we will stick with DataHero and Power BI for the time being as they offer the most features in their free plans.
Let’s recap the entire process of analyzing public data that’s archived in PDF:
- Find a relevant data set
- Use Able2Extract’s Custom PDF to Excel feature to convert it to Excel or CSV
- Clean the data in Excel and remove blank rows and cells
- Visualize the data using a tool like Power BI or DataHero
By now you should have a clear understanding of the entire process of analyzing public data and should be well on your way to using it to shape the future of journalism. The strategy is simple — just upload clean, high quality data and play around with it until you get what you are looking for.
