Is your data readable?

In talking with clients and colleagues about open data and open government this is the one question that comes up over and over again. The word “data” means a collection or body of facts that represent the qualitative or quantitative attributes of a variable or set of variables but what does “open data” mean?

To answer this question I like to look at what we are trying to achieve by opening data. The promise of open data is that if we make government administrative data available to the public value will be created in ways that we may or may not be able to imagine. The value will be created by using the data. So, what is open data? Ultimately, it’s data you can use. In this series of blog posts I will explore the various ways data can be made more usable.

What makes data usable?

In a previous post I proposed some dimensions that move toward a usability scale. In this post I propose a minimum standard of usability. In other words, what are the absolute minimum requirements that must be satisfied in order to consider something open data? To answer this question one could look at the dimensions of usability individually and decide for each one, what would be the minimum level of usability below which data is not usable.

One of the main measures of usability is readability.  In other words, how easy is it to read?

For example, this list of cities with their geographic areas and populations is data:

Data collected into rows and columns in this way is typically called a data set (or dataset). By putting this dataset in my blog post in a table I have made it available to you but the fact that I made it available to you as a screenshot of my spreadsheet means to read it would be difficult, error prone and would require expensive software or scripting. Which makes it pretty much unusable by you.

Another method in use by governments today is is to publish data as a PDF formatted document. This is marginally better than posting as an image. It’s technically possible to extract the data from PDF files as I have demonstrated in a previous post, but it’s still expensive, time consuming and error prone.

What I could do instead is make that same data available as an HTML table in this blog post, like this:

City	Area	Population
Victoria	19.68	78057
Vancouver	114.67	578041
Kelowna	211.69	120812

Technically, this is a level better than both images and PDF files but it will still get me low points on the usability scale because in order to read it a programmer still has to write a script specifically for reading this data from my blog post, a time consuming and wasteful process. If you’re unfortunate enough to need to read data from an HTML page, another previous blog post describes how to do this.

To really improve the usability of this software it makes sense to publish it in a format that represents data in a form that makes the data easily accessible. Many people are familiar with spreadsheets, which are a popular tool for reading and manipulation of tabular data so making data available in spreadsheet format makes it more usable in the sense that people can obtain spreadsheet programs to read the tabular data. For example, here is the same data published in the open .ODS format supported by a wide variety of spreadsheet software providers, and here it is published in the XLS format a proprietary format controlled by the Microsoft corporation.

The advantage to publishing in spreadsheet format is that while still requiring specialized scripts and software to read, at least the rows and columns are well defined which translates into fewer errors.  This is what I would consider the minimum bar for usable open data.  It's not as usable as I would like, but it is usable without too much risk.  In other words, if you have data in this format already and you don't have the budget to reformat it before publishing it, don't delay the release, just publish it as is.

Ideally though data is published in formats specifically designed for the purpose of information sharing, and that’s where the CSV, XML and JSON formats come in.

The CSV version of my dataset looks like this:

"City","Area","Population"
"Victoria",19.68,78057
"Vancouver",114.67,578041
"Kelowna",211.69,120812

The XML version looks like this:

<dataset>
 <data>
  <row><city>Victoria</city>19.69<population>78057</population></row>
  <row><city>Vancouver</city>114.67<population>578041</population></row>
  <row><city>Kelowna</city>211.69<population>120812</population></row>
 </data>
</dataset>

and the JSON version looks like this:

[
 {"city": "Victoria", "population": 78057, "area": 19.690000000000001},
 {"city": "Vancouver", "population": 578041, "area": 114.67},
 {"city": "Kelowna", "population": 120812, "area": 211.69}
]

While not quite pretty as the other human readable formats CSV, XML and JSON are open formats that provide structure making it very easy for programs to read the data. They are also well supported in almost all modern programming languages so that any programmer who wants to use your data can do so easily and accurately with free software and very little programming. And as a side benefit, its very easy and inexpensive to publish your administrative data into these formats using free software.

Publishing data in these open formats makes it easy for people to use open data. While publishing in HTML format is readable and is what I would consider the bare minimum for usability, depending on how it is done, other formats can make it much easier. And if your organization is serious about engaging people to collaborate and create value from the data they will want to make the data as usable as possible and making the data readable is one part of doing that.

OpenDataBC: Extracting Data from A4CA PDFs

In this OpenDataBC series of posts, I describe how to use some of the data that is being made available by the government of British Columbia on http://data.gov.bc.ca and related web sites. In the first article of this series, I described how to write a script to scrape catalog data from web pages. In the second article I described how to write a program to transform the data. In this article, I describe how to convert a PDF document into useable data.

As part of the Apps for Climate Action Contest, the Province of BC released over 500 datasets in the Climate Action Data Catalogue. It's an impressive amount of data pulled from an array of sources both within BC and elsewhere.

In an ideal “open data” world, all of that data would be in an easily machine readable format that we could use to write programs directly. While that would be great, the reality today is a bit different. Much of the data that is made publicly available these days is in formats that are harder to use. For example, some of the data in the Climate Change Data Catalogue was released in PDF format. PDF is a proprietary format, meaning the format is controlled exclusively by one party, in this case the Adobe corporation.

An interesting fact is that it takes extra effort to get data from its raw form into PDF format. In other words, to publish data in an open format rather than in PDF format actually saves time, effort and money – up front. However, PDF became well established in the pre-open world, so a lot of data is already published using it. To switch existing software to publish in an open format might take time. As a result, at least temporarily, we need to find ways to get at the data in the PDF files.

In this post I describe how to do that. Looking through some of the available datasets in the catalogue, one that I find interesting is “Transit Ridership in Metro Vancouver”. The data is produced by Translink and is in a PDF format and looks like this:

What I am interested in is the number of passenger trips by year for the past few years. I am going to leave out the Seabus and the West Coast Express as I am mostly interested in the buses and the Skytrain.

What I would like is a dataset, in a CSV file. The way this program will work is essentially as follows:

• read the data from the source database
• extract the data from the PDF file into a list in memory
• write the list in memory out to a CSV file

Prerequisites
The following code requires the Python programming language, which comes pre-installed on all Linux and modern Mac machines and can be easily installed on Windows.

The Code
The first thing we need to do is to read the PDF file into memory. The simple way to do that in Python is to use the urllib2 library and read the entire PDF from the original web site. Tying the script to the actual location of the file means we don't manually store the orginal file anywhere. If the City of Vancouver decided to move the URL we would have to adjust our code, but we're probably only going to run this code once so it's not a big deal. To read the PDF file into a memory variable we do this:

import urllib2 
    url = 'http://www.metrovancouver.org/about/publications/Publications/KeyFacts-TransitRidership1989-2008.pdf'
    pdf = urllib2.urlopen(url).read() 

Now that we have the PDF file in memory, I want to parse the PDF file and turn it into raw text. To do this I use a free open source Python library called pdfminer. I have created a function called pdf_2_text for this purpose. Here's the function:

def pdf_to_text(data): 
    from pdfminer.pdfinterp import PDFResourceManager, process_pdf 
    from pdfminer.pdfdevice import PDFDevice 
    from pdfminer.converter import TextConverter 
    from pdfminer.layout import LAParams 

    import StringIO 
    fp = StringIO.StringIO() 
    fp.write(data) 
    fp.seek(0) 
    outfp = StringIO.StringIO() 
    
    rsrcmgr = PDFResourceManager() 
    device = TextConverter(rsrcmgr, outfp, laparams=LAParams()) 
    process_pdf(rsrcmgr, device, fp) 
    device.close() 
    
    t = outfp.getvalue() 
    outfp.close() 
    fp.close() 
    return t

The pdf_to_text function starts by importing the components required to do the conversion. The pdfminer library provides a lot of functionality. In this example we are using a small fraction of its functionality to do what we need, which is to get at the content in the PDF. The main function that actually does the work is called process_pdf. It takes a PDFResourceManager object, a TextConverter object and a file object as parameters so the code before that call is setting up those parameters properly. I use a StringIO object rather than just passing the urllib2 object in because the PDF converter needs to use the seek method for random access which is not supported in urllib2. To gain this ability I put the data into a StringIO object, which supports seek.

When the pdf_to_text function is called with the contents of a PDF file it returns a string containing lines of text with each line containing one of the elements (numbers or labels) of the PDF file. Here's what it looks like on my system:

Now that we have the data in text format, we want to pull out the numbers that we are interested in. I am interested in the labels on the left, which start on line 6, the first numeric column (BUS), which starts on line 75 and the second numeric column (SKYTRAIN), which starts on line 144.

To start the process of extracting rows of data from the text file, I first split lines of the text file into a list like this:

lines = text.splitlines() 

Then I create a simple function called grab_one_row which besides having a very clever name, knows the relative placement of the three columns, and pulls one whole row at a time from the text file and returns it as a tuple. Here is the function:

def grab_one_row(lines,n): 
    return (lines[n],long(lines[n+69].replace(',','')),long(lines[n+138].replace(',',''))) 

Armed with that function, I can now collect most of the rows I am interested in with a simple generator line:

rows = [grab_one_row(lines,i) for i in range(6,26)] 

In the original PDF, the data for 2008 is placed further down the page so the last line needs to be added with a separate line of code like this:

rows.append(grab_one_row(lines,39)) 

now the rows array contains all of the data we are interested in, in an array that we can easily deal with. We just need to write them out to a CSV file to complete our work. To do that I created the rows_to_csv function. Here it is:

def rows_to_csv(rows,filename): 
    # write the clean data out to a file 
    import csv 
    f = open(filename,'w') 
    writer = csv.writer(f,delimiter=',', quotechar='"', quoting=csv.QUOTE_NONNUMERIC) 
    writer.writerow(rows[0]) 
    for row in rows[1:]: 
        writer.writerow((row[0],long(row[1].replace(',','')),long(row[2].replace(',','')))) 

I wanted the resulting CSV file to have numbers rather than strings containing numbers for the numeric values. The last line of this function strips out the commas that were in the numbers in the PDF file and then converts the text to a long integer to be written the CSV file.

The resulting CSV file now looks like this:

This result is a lot easier to deal with than the original PDF file. Arguably, a small file such as this could also be converted with Open Office Spreadsheet by cutting from the PDF and pasting to the spreadsheet. The nice thing about doing this as a script as above is that we can use this same technique for very large PDF files that would be too onerous to do manually.

Here is the entire program with all of the code together at once:

def pdf_to_text(data): 
    from pdfminer.pdfinterp import PDFResourceManager, process_pdf 
    from pdfminer.pdfdevice import PDFDevice 
    from pdfminer.converter import TextConverter 
    from pdfminer.layout import LAParams 

    import StringIO 
    fp = StringIO.StringIO() 
    fp.write(data) 
    fp.seek(0) 
    outfp = StringIO.StringIO() 
    
    rsrcmgr = PDFResourceManager() 
    device = TextConverter(rsrcmgr, outfp, laparams=LAParams()) 
    process_pdf(rsrcmgr, device, fp) 
    device.close() 
    
    t = outfp.getvalue() 
    outfp.close() 
    fp.close() 
    return t 
    
def grab_one_row(lines,n): 
    return (lines[n],lines[n+69],lines[n+138]) 

def rows_to_csv(rows,filename): 
    # write the clean data out to a file 
    import csv 
    f = open(filename,'w') 
    writer = csv.writer(f,delimiter=',', quotechar='"', quoting=csv.QUOTE_NONNUMERIC) 
    writer.writerow(rows[0]) 
    for row in rows[1:]: 
        writer.writerow((row[0],long(row[1].replace(',','')),long(row[2].replace(',','')))) 

def run(): 
    import urllib2 
    url         = 'http://www.metrovancouver.org/about/publications/Publications/KeyFacts-TransitRidership1989-2008.pdf' 
    outfilename = 'translink_bus_skytrain_trips_1989_2008.csv' 
    
    pdf = urllib2.urlopen(url).read() 
    text = pdf_to_text(pdf) 
    
    lines = text.splitlines() 
    rows = [grab_one_row(lines,i) for i in range(6,26)] 
    rows.append(grab_one_row(lines,39)) 

    rows_to_csv(rows,outfilename) 
    
if __name__ == '__main__': 
    run() 

and you can find the resulting CSV file here.

Once again, Python comes through for us. Clearly it's not as easy to convert a PDF file as it is to rip a table out of an HTML file, but being possible at all makes it something we can work with. And part of the beauty of “Open” is that now that I have done it, others don't have to. And I in turn will benefit from other contributors to the open ecosystem. If we all do a bit, it's an “everyone wins” scenario.

OpenDataBC #2: The A4CA Data Catalogue Transformer

In the OpenDataBC series of posts I describe how to use some of the data that is being made available by the government of British Columbia on http://data.gov.bc.ca and related web sites.  My goal is to encourage people that might not otherwise consider interacting directly with data, to give it a try and see for themselves that it's easy, and maybe even fun.  :-)

Three weeks ago I sat down to have a closer look at the datasets that were released as part of the Province of BC's Apps 4 Climate Action (A4CA) contest.  I am very excited about the prospect of open data in BC and wanted to see what was available that might be interesting to use for various projects.  When I started to look through the datasets, I realized I was going to need to download it into either a spreadsheet or database to be able to really look at what was available.

In the previous article of this series, I described how to write a script to scrape the catalog data from the web page that contains it.  In this article I describe how to write a program to transform the data.

The goal of this program is to read the live catalog from the A4CA site and make it available in a useable form.  The program will do some data cleaning and write the data out to a file that can be read by a spreadsheet or database program.  I have decided to output the catalog data in a comma delimited form otherwise known as a csv file.

While writing the last article, I noticed that when the data was downloaded the data had some broken lines, extra spaces and some of the data was encoded with HTML encoding (for example: '≥').  I want to be able to view the data in a plain text editor and in a simple spreadsheet program.  This means cleaning all of these extra lines and codes out so the data is easy to work with and understand during analysis.

The way this program will work is basically as follows:

• read the data from the catalog page
• clean the data
• write the clean data out to a csv file

Prerequisites
The following code requires the Python programming language, which comes pre-installed on all Linux and modern Mac machines and can be easily installed on Windows.

The Code
One of the first things we are going to need to do is grab the catalog data.  I saved my code from the last article in a module called read_a4ca_catalog. To make that module available I just import it.  We are also going to need a few other modules in our program so I import those at the same time.  The string module will help us when we want to clean the data.  We'll use the csv module when we want to write our output to a file.

import read_a4ca_catalog
import string
import csv

Now that we have all of the basic modules loaded we are set to start coding.

Reading the dataset is ridiculously easy now that we have a module specifically written to do that.  It's a one-liner that uses our module from last time:

raw_data = read_a4ca_catalog.read_a4ca_catalog()

Now we have our data in a list variable and we need to clean it.  For that we're going to go through the data row by row, cleaning each row and placing it into a new array called clean_data.

clean_data = []
for row in raw_data:
    clean_data.append( cleanup_row(row) )

Notice that the code calls a function called cleanup_row.  That function basically takes one row from the raw catalog data and goes through it cell by cell and cleans the data in each cell.  Cleaning the data consists of replacing the encoded tags with readable alternatives, replacing duplicate spaces with single spaces, and removing all characters that we don't want.  We create a string called legal_chars ahead of time, to specify which characters we consider legal for the output cells.  Here's the code:

legal_chars  = string.digits + string.ascii_letters + string.punctuation + ' '
def cleanup_row(row):
    def cleanup_element(element):
        t1 = element.replace('  ',' ').replace('&amp;','&').replace('&#8804;','<=').replace('&#8805;','>=').replace('&lt;','<').replace('&gt;','>')
        return ''.join([c for c in t1 if c in legal_chars])
    return [cleanup_element(element) for element in row]

We need to place this function above the code that uses it. In my program I have placed it right below the import statements.

Finally, we want to write the clean data out to a file.  For that I create an output file and call upon the Python csv module to write each row out to the file.   Just before I write out the rows of data, I write a row of labels so that the csv file has headings.

f = open('a4ca_catalog.csv','w')
writer = csv.writer(f,delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)
writer.writerow(['category','subtopic','title','link','agency','agencylink','format'])
for row in clean_data:
    writer.writerow(row)

That's it. We now have a .csv file that can be easily read by pretty much any spreadsheet or database program.

Here's the entire program with all the bits in order:

import read_a4ca_catalog
import string
import csv

legal_chars  = string.digits + string.ascii_letters + string.punctuation + ' '
def cleanup_row(row):
    def cleanup_element(element):
        t1 = element.replace('  ',' ').replace('&amp;','&').replace('&#8804;','<=').replace('&#8805;','&gt;=').replace('&lt;','<').replace('&gt;','>')
        return ''.join([c for c in t1 if c in legal_chars])
    return [cleanup_element(element) for element in row]

# read the a4ca catalog from the live web site
raw_data = read_a4ca_catalog.read_a4ca_catalog()

# clean the data
clean_data = []
for row in raw_data:
    clean_data.append( cleanup_row(row) )

# write the clean data out to a file
f = open('a4ca_catalog.csv','w')
writer = csv.writer(f,delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)
writer.writerow(['category','subtopic','title','link','agency','agencylink','format'])
for row in clean_data:
    writer.writerow(row)

And you can find the resulting csv file here.

The nice thing about doing this with a program rather than manually is that if the catalog is updated from time to time, all we have to do is run our program and we have a nice clean spreadsheet to work with that has the latest and greatest data.

Python comes with a lot of tools built in for manipulating data and it make short work of jobs like this. Now that the data is in spreadsheet form, we can look at it to see what different types of data there are, what format it's in and where it comes from, and we can do cross tabs and graphs to help us visualize those aspects.

OpenDataBC: Accessing the A4CA Data Catalogue

The OpenDataBC Series: In this series of posts I will describe how to use some of the data that is being made available by the government of British Columbia on http://data.gov.bc.ca and related web sites.  My goal is to encourage people that might not otherwise consider interacting directly with data, to give it a try and see for themselves that it's easy, and maybe even fun.  :-)

Last week I sat down to have a closer look at the datasets that were released as part of the Province of BC's Apps 4 Climate Action (A4CA) contest.  I am very excited about the prospect of open data in BC and wanted to see what was available that might be interesting to use for various projects.

The A4CA data listed in the catalogue includes a range of formats and technologies.  Some are easier to work with than others.  Being able to browse the catalogue online is great but I really want to have a closer look and maybe do some analysis to find the data that is easy to work with.  For that I need to download the data so I can work with in it spreadsheet and / or database form.  In the perfect world, this data would be available on the site as a downloadable feed, with its own URL so programmers could simply point at the URL and get the data in XML format that goes with it.

The A4CA catalog provides a download to .CSV form, which is easy to work with but unfortunately, the link to that data is hidden behind a flash widget, so there is no way to download the data directly.  The page itself however does provide the data to the browser in the form of a table.  The table shows only 100 records at a time; however, there is another flash widget that allows the user to page through the data 100 records at a time, without a screen refresh.  That means the data is already in the browser, all 540 rows of it.  It's just a matter of scraping it out using a bit of code.

How it's done:
First, checking the robots.txt file for the site (www.gov.bc.ca) file reveals that the site allows for almost any type of program including this one, so that's great.

Prerequisites
My personal language of choice for this sort of task is Python, which comes pre-installed with all Linux and modern Mac machines and can be easily installed on Windows.  In my case I am using Python 2.6 under Ubuntu 9.10.  In addition to Python I am using Beautiful Soup which is an excellent library for scraping web sites.

The Code
The first thing we need to do in our program is to import the modules we need.  We are going to need urllib2 to grab the page and BeautifulSoup to parse it.  That's just one line:

import urllib2, BeautifulSoup

Next, we need to go out to the web and grab the HTML page and store it as a string called page:

page = urllib2.urlopen('http://data.gov.bc.ca/data.html')

Next, we create an object called soup using the BeautifulSoup library.

soup = BeautifulSoup.BeautifulSoup(page)

At this point we have the page loaded and we can read whatever parts of it we want using the methods provided by the soup object.

I am particularly interested in the table in the middle of the page, that contains the data I am after.  Looking at the raw HTML code from inside my browser I see that there is only one table on this page and that it's ID is set to 'example', so that makes it pretty easy to find using the find method provided by the soup object.

data_table = soup.find('table',id='example')

We also need a place to store our results.  I'll use an array for that.

records = []

Now that we have the table, we just want to cycle through the rows of the table and pull the data out.  For that we can use the Python for statement with the method findAll provided by the data_table object that we created.  With each row that we iterate through, we want to grab the text that is stored in each table cell.  This is easily accomplished by creating an array containing all of the cells in the row and then taking the parts we want to work with from that array.  Here's the code:

for row in data_table.findAll('tr'):
    if row.find('td'):
        cols = [a for a in row.findAll('td')]
        records.append([
            cols[0].text,
            cols[1].text,
            cols[2].text,
            cols[2].a['href'],
            cols[3].text,
            cols[3].a['href'],
            cols[4].text,
            ])

Pulling the text out of the table cells is as easy as accessing the .text member.  Two of the cells in each row have links, which I wanted to capture as well, so I accessed those by using the .a member and then access the href attribute which is where links are stored in HTML.

Now, each row in our records array contains one row from the table with the cell contents and links separated out.  This is a good start to making this data more usable for my purposes. 

Next, I plan to do some data cleaning and then start to do some analysis on it to get a feel for what's available in the A4CA catalogue.

And finally, here is the entire program:

def read_a4ca_catalog():
    import urllib2, BeautifulSoup

    page = urllib2.urlopen('http://data.gov.bc.ca/data.html')
    soup = BeautifulSoup.BeautifulSoup(page)

    records = []

    data_table = soup.find('table',id='example')
    for row in data_table.findAll('tr'):

        if row.find('td'):
            cols = [a for a in row.findAll('td')]
            records.append([
               cols[0].text,
               cols[1].text,
               cols[2].text,
               cols[2].a['href'],
               cols[3].text,
               cols[3].a['href'],
               cols[4].text,
               ])
    return records

if __name__ == '__main__':
    for record in read_a4ca_catalog():
        print record

With Python and BeautifulSoup it's easy to extract data from a site and I would encourage anyone to give it a try.  It's easier than you might think.

Now that we have the data in a form we can work with, how do we clean it up and make it more useful?  I'll cover that in the next article of this series.