{"id":6356,"date":"2016-09-01T17:35:38","date_gmt":"2016-09-01T17:35:38","guid":{"rendered":"https:\/\/www.investintech.com\/resources\/blog\/?p=6356"},"modified":"2019-08-23T12:55:11","modified_gmt":"2019-08-23T12:55:11","slug":"analyze-open-data-able2extract-powerbi-datahero","status":"publish","type":"post","link":"https:\/\/www.investintech.com\/resources\/blog\/archives\/6356-analyze-open-data-able2extract-powerbi-datahero.html","title":{"rendered":"How To Analyze Open Data With Able2Extract, Power BI And DataHero"},"content":{"rendered":"

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. <\/span><\/p>\n

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. <\/span><\/p>\n

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: <\/span>www.data.gov<\/span><\/a>, <\/span>open.canada.ca<\/span><\/a>, <\/span>data.gov.uk<\/span><\/a> and many more. <\/span><\/p>\n

Open data portals contain thousands and thousands of data sets, related to various branches of government: education, business, economy, crime, justice, healthcare and more. <\/span><\/p>\n

Once you start exploring the online data, you will see that it usually comes in 3 main formats: HTML, XML and PDF.<\/span><\/p>\n

\"Common<\/p>\n

However, if you start investigating the data sets in more depth, you will quickly notice that there is only one format that\u2019s present in almost every data set \u2014 the PDF. So, the logic goes that if you know how to analyze data that\u2019s locked inside a PDF, you\u2019ll know how to analyze any. <\/span><\/p>\n

But what makes people want to store data in a non editable format?<\/span><\/p>\n

First of all, when you save a data set as a PDF you are reducing its size, so it\u2019s 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.<\/span><\/p>\n

So, once you find a PDF data set, where do you go next? <\/span><\/p>\n

You now basically have only one option \u2014 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.<\/span><\/p>\n

When it comes to exporting PDF data, the only tool in the market that has advanced enough PDF exporting capabilities is <\/span>Able2Extract<\/span><\/a>. 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. <\/span><\/p>\n

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. <\/span><\/p>\n

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 <\/span>here<\/span><\/a>. <\/span><\/p>\n

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 \u201cPreview conversion\u201d box. Once satisfied, hit the <\/span>convert<\/b> button to send the data to Excel.<\/span><\/p>\n

\"Able2Extract<\/p>\n

So, we got our data from PDF and into Excel. Great job! \u00a0<\/span><\/p>\n

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 <\/span>tabular<\/b> format, which means there is a separate row for each record. It should look something like this:<\/span><\/p>\n

\"PDF<\/p>\n

Make sure you don\u2019t 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\u2019s best to delete the whole row because it can mess up your end result and produce inaccurate results. <\/span><\/p>\n

Now that we have a clean and tidy data set, it\u2019s time to give life to these numbers and visualize them. Enter data visualization. <\/span><\/p>\n

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. <\/span><\/p>\n

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\u2019s free to use for datasets up to 1 GB. You can download it <\/span>here<\/span><\/a>. <\/span><\/p>\n

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:<\/span><\/p>\n