A Python guide for open data file formats

Question

If you are an open data researcher you will need to handle a lot of different file formats from datasets. Sadly, most of the time, you don’t have the opportunity to choose which file format is the best for your project, but you have to comport with all of them to be sure that you won’t find a dead end.

There’s always someone who knows the solution to your problem, but that doesn’t mean that answers come easy. Is there a guide or tutorial for all the different file formats?

Answer 1

Here is a guide for each file format from the Open data handbook and a suggestion with a Python library to use.

JSON is a simple file format that is very easy for any programming language to read. Its simplicity means that it is generally easier for computers to process than others, such as XML. Working with JSON in Python is almost the same such as working with a Python dictionary. You will need the JSON library, but it is preinstalled to every Python 2.6 and after.

import json
json_data = open("file root")
data = json.load(json_data)

Then data["key"] prints the data for the JSON.

XML is a widely used format for data exchange, because it gives good opportunities to keep the structure in the data and the way files are built on and allows developers to write parts of the documentation in with the data without interfering with the reading of them. This is pretty easy in Python as well. You will need the MiniDom library. It is also preinstalled.

from xml.dom import minidom
xmldoc = minidom.parse("file root")
itemlist = xmldoc.getElementsByTagName("name")

This prints the data for the "name" tag.

RDF is a W3C-recommended format and makes it possible to represent data in a form that makes it easier to combine data from multiple sources. RDF data can be stored in XML and JSON, among other serializations. RDF encourages the use of URLs as identifiers, which provides a convenient way to directly interconnect existing open data initiatives on the Web. RDF is still not widespread, but it has been a trend among Open Government initiatives, including the British and Spanish Government Linked Open Data projects. The inventor of the Web, Tim Berners-Lee, has recently proposed a five-star scheme that includes linked RDF data as a goal to be sought for open data initiatives I use rdflib for this file format. Here is an example.

from rdflib.graph import Graph
g = Graph()
g.parse("<file root>", format="<format>")
for stmt in g:
   print(stmt)

In RDF you can run queries too and return only the data you want. But this isn't easy as parsing it. You can find a tutorial here.

Spreadsheets. Many authorities have information left spreadsheet documents, for example Microsoft Excel. This data can often be used immediately with the correct descriptions of what the different columns mean. However, in some cases there can be macros and formulas in spreadsheets, which may be somewhat more cumbersome to handle. It is therefore advisable to document such calculations next to the spreadsheet, since it is generally more accessible for users to read. I prefer to use a tool like xls2csv and then use the output file as a CSV file. But if you want for any reason to work with an .xls file, www.python-excel.org is the best source I had. The most populars are xlrd and xlwt. There is also another library, openpyxl, where you can work with .xlsx files.

Comma Separated Files (CSV) files can be a very useful format, because it is compact and thus suitable to transfer large sets of data with the same structure. However, the format is so spartan that data are often useless without documentation since it can be almost impossible to guess the significance of the different columns. It is therefore particularly important for the comma-separated formats that documentation of the individual fields are accurate. Furthermore, it is essential that the structure of the file is respected, as a single omission of a field may disturb the reading of all remaining data in the file without any real opportunity to rectify it, because it cannot be determined how the remaining data should be interpreted. You can use the CSV Python library. Here is an example:

import csv
with open('eggs.csv', 'rb') as csvfile:
    file = csv.reader(<file root>, delimiter=' ', quotechar='|')
    for row in file:
        print ', '.join(row)</pre>

Plain Text (txt) are very easy for computers to read. They generally exclude structural metadata from inside the document however, meaning that developers will need to create a parser that can interpret each document as it appears. Some problems can be caused by switching plain text files between operating systems. MS Windows, Mac OS X and other Unix variants have their own way of telling the computer that they have reached the end of the line. You can load the txt file, but how you will use it after that depends on the data format.

text_file = open("<file root>", "r")
lines = text_file.read()</pre>

This example will return the whole txt.

PDF Here is the biggest problem in open data file formats. Many datasets have their data in PDF, and unfortunately it isn't easy to read and then edit them. PDF is really presentation oriented and not content oriented. But you can use PDFMiner to work with it. I won't include any example here since it isn't a trivial one, but you can find anything you want in their documentation.

HTML. Nowadays much data is available in HTML format on various sites. This may well be sufficient if the data is very stable and limited in scope. In some cases, it could be preferable to have data in a form easier to download and manipulate, but as it is cheap and easy to refer to a page on a website, it might be a good starting point in the display of data. Typically, it would be most appropriate to use tables in HTML documents to hold data, and then it is important that the various data fields are displayed and are given IDs which make it easy to find and manipulate data. Yahoo has developed a tool, YQL that can extract structured information from a website, and such tools can do much more with the data if it is carefully tagged. I have used a Python library many times called Beautiful Soup for my projects.

from bs4 import BeautifulSoup
soup = BeautifulSoup(html_file)
soup.title
soup.title.name
soup.title.string
soup.title.parent.name
soup.p
soup.p['class']
soup.a
soup.find_all('a')
soup.find(id="link3")

Those are only a few of what you can do with this library. By calling the tag, it will return the content. You can find more in their documentation.

Scanned image. Yes. It is true. Probably the least suitable form for most data, but both TIFF and JPEG-2000 can at least mark them with documentation of what is in the picture - right up to mark up an image of a document with full text content of the document. If images are clean, containing only text and without any noise, you can use a library called pytesser. You will need the Python Imaging Library (PIL) library to use it. Here is an example:

from pytesser import *
image = Image.open('fnord.tif')  # Open image object using PIL
print image_to_string(image)</pre>

Proprietary formats. Last but not least, some dedicated systems, etc. have their own data formats that they can save or export data in. It can sometimes be enough to expose data in such a format - especially if it is expected that further use would be in a similar system as they came from. Where further information on these proprietary formats can be found should always be indicated, for example by providing a link to the supplier’s website. Generally it is recommended to display data in non-proprietary formats where feasible.. I suggest to google if there is any library specific for this dataset.

Tab Separated Values (TSV). A tab-separated values file is a simple text format for storing data in a tabular structure (for example, database or spreadsheet data). Each record in the table is one line of the text file. Each field value of a record is separated from the next by a tab stop character – it is a form of the more general delimiter-separated values format. Unfortunately, I haven't found any good working Python library only for TSV. Until now, I have worked with CSV library like the following example:

import csv
with open("tab-separated-values") as tsv:
    for line in csv.reader(tsv, dialect="excel-tab"): #You can also use delimiter="\t"

Shapefiles are files used to represent spatial data such as polygons that define a city, a neighborhood etc. You can use the libraries fiona and shapely (pip install fiona shapely) to help with this job. For example, if you want to load a shapefile, simplify its polygons (to reduce size) and then export to GeoJSON (so you can plot in your Web browser using JavaScript libraries such as LeafLet), you can use this code:

import json

import fiona
import shapely.geometry


shapefile = fiona.open('my_shapefile.shp')
shapes = shapely.geometry.shape(shapefile['geometry'])
simplified_shapes = shapes.simplify(0.01) # 0.01 is the simplification factor
geodict = {'type': 'FeatureCollection',
           'features': shapely.geometry.mapping(simplified_shapes)}
with open('my_geojson.json') as fobj:
    fobj.write(json.dumps(geodict))

VOTables is a mix of HTML and XML used most of the time in astronomy. This kind of data contains metadata that it is vital for you. It is pretty simple to extract them with Python using the following library.

from astropy.io.votable import parse
votable = parse("votable.xml")

Additional Information. Maybe you will find the Pandas library useful, whose I/O capabilities integrate and unify access from/to most of the formats: CSV, Excel, HDF, SQL, JSON, HTML, and Pickle.

Answer 2

I surprised no-one mentioned HDF5 yet. This is a documented format with freely available C libraries for using it. It has traditionally been used for high-performance persistence of numerical scientific data. HDF5 files are self-describing and very easy to work with using python (using either PyTables or H5Py). More importantly, HDF5 is platform agnostic and bindings are available for C++, Java and C# (not sure about JS Ruby etc. but anything with a foreign-function interface can be made to work). Think of HDF5 as like XML but for binary data.

Answer 3

Once you've decided on a data format, you will need to decide the following additional issues before publishing your open dataset:

1. Data Layout
   
2. Name/Data type (Vocabulary) of data fields.
   
3. Language and Language Script.

Data Layout

Tabular Data Format (TDF) - http://dataprotocols.org/tabular-data-package/

The key features of this format are the following:
•CSV (comma separated variables) for data
•Single JSON file (datapackage.json) to describe the dataset including a schema for data files
•Reuse wherever possible of existing work including other Data Protocols specifications

Data Packages (DP) - http://dataprotocols.org/data-packages/

A data package consists of:
•Data package metadata that describes the structure and contents of the package
•Optionally, additional resources, including data files, that make up the package

Dataset Publishing Language (DSPL) - https://developers.google.com/public-data/

This is a Google standard that does not appear to have gained much traction.

DSPL is a data and metadata format designed from the ground up to support powerful, interactive visualizations like those in the Google Public Data Explorer.

Name / Data Type

OgenGeoCode Linked CSV Vocabulary - http://www.opengeocode.org/cude1.1/LinkedCSV-Vocab.php

This is our OpenGeoCode.Org's published standard (disclaimer: which I am a co-founder).

This document describes the field namimg convention and data types ('the vocabulary') used in the Linked CSV generated datasets. For more information on Linked CSV, see Linked CSV, ODI

Common Core Metadata Schema - http://project-open-data.github.io/schema/

This section contains guidance to support the use of the common core metadata to list agency datasets and application programming interfaces (APIs) as hosted at agency.gov/data. Updates to the metadata schema can be found in the changelog. Current metadata version: 1.0 FINAL as of 9/20/13. Standard Metadata Vocabulary Metadata is structured information that describes, explains, locates, or otherwise makes it easier to retrieve, use, or manage an information resource (NISO 2004, ISBN: 1-880124-62-9). The challenge is to define and name standard metadata fields so that a data consumer has sufficient information to process and understand the described data. The more information that can be conveyed in a standardized regular format, the more valuable data becomes.

Data Catalog Vocabulary (DCAT) (W3C std) - http://www.w3.org/TR/vocab-dcat/

DCAT is an RDF vocabulary designed to facilitate interoperability between data catalogs published on the Web. This document defines the schema and provides examples for its use. By using DCAT to describe datasets in data catalogs, publishers increase discoverability and enable applications easily to consume metadata from multiple catalogs. It further enables decentralized publishing of catalogs and facilitates federated dataset search across sites. Aggregated DCAT metadata can serve as a manifest file to facilitate digital preservation.*

Linked Open Vocabularies (LOV) - http://lov.okfn.org/dataset/lov/

Welcome to LOV, your entry point to the growing ecosystem of linked open vocabularies (RDFS or OWL ontologies) used in the Linked Data Cloud. Here you will find vocabularies listed and individually described by metadata, classified by vocabulary spaces, interlinked using the dedicated vocabulary VOAF. You will enjoy querying the LOV dataset either at vocabulary level or at element level, exploring the vocabulary content using full-text faceted search, and finding metrics about the use of vocabularies in the Semantic Web. Not finding your favourite one? Suggest a new vocabulary to add to LOV!

Linked Open Data (LOD) [Getty Vocabulary] - http://www.getty.edu/research/tools/vocabularies/lod/

The Getty vocabularies are constructed to allow their use in linked data. A project to publish AAT, TGN, ULAN, and CONA to the LOD (Linked Open Data) cloud is underway. The documents on this page contain news and presentations about releasing the Getty vocabularies as LOD. These materials are subject to frequent modification and addition.

US Government's Vocab - http://vocab.data.gov/ A metamodel for government data ... Government Data Vocabulary ... Schema that defines concepts and relationships common to all Open Government Data. **

Language and Script

ISO 639-1 - http://www.loc.gov/standards/iso639-2/php/code_list.php

Codes for the Representation of Names of Languages

IANA Character Set Identifiers - http://www.iana.org/assignments/character-sets/character-sets.xhtml

These are the official names for character sets that may be used in the Internet and may be referred to in Internet documentation. These names are expressed in ANSI_X3.4-1968 which is commonly called US-ASCII or simply ASCII. The character set most commonly use in the Internet and used especially in protocol standards is US-ASCII, this is strongly encouraged. The use of the name US-ASCII is also encouraged.

Answer 4

As said in the question, the format used depends mainly on which project/domain the data comes from. But most of the time the data is useless without metadata information, and when the dataset starts to get big you need a way to extract the metadata from it automatically.

In astronomy, where most of the data has been open for decades, the International Virtual Observatory Alliance (IVOA) created the specifications of a format that is somehow a mix of html tables and xml, it's called VOTables and it contains information as where it comes from, what are the names of the columns, the units and other descriptors (base on a set of standards).

This fileformat, besides being compatible with a lot of tools used in astronomy can be also read and written in python using the astropy package. A simple votable can be read by just:

from astropy.io.votable import parse
votable = parse("votable.xml")

Answer 5

Downloading and Uncompressing Archive (.zip) Files and Folders:

Often data is downloaded from the web in a compressed form, or data folders are joined in one zip file. Before processing, the files must be uncompressed. If already working in a Python environment, it's useful to be able to download the .zip file and also unzip it, all in the same piece of code.

The most commonly used url-retrieving library is requests, and I prefer this package for all my scraping needs. But the requests package is not suggested for downloading large non-HTML files (source). Instead, urllib2.urlopen can be used. In case .zip files contain multiple files or folders, it's best to unzip to a new folder. The following code is OS-agnostic (and adapted from here):

# set remote and local file location
url = 'http://www.colorado.edu/conflict/peace/download/peace_essay.ZIP'
filename = 'data.zip'
folderpath = 'data'

def download(url,filename):
    import urllib2
    page=urllib2.urlopen(url)
    open(filename,'wb').write(page.read())

def unzip(source_filename, dest_dir):
    import zipfile,os.path
    with zipfile.ZipFile(source_filename) as zf:
        for member in zf.infolist():
            words = member.filename.split('/')
            path = dest_dir
            for word in words[:-1]:
                drive, word = os.path.splitdrive(word)
                head, word = os.path.split(word)
                if word in (os.curdir, os.pardir, ''): continue
                path = os.path.join(path, word)
            zf.extract(member, path)

download(url,filename)
unzip(filename,folderpath)

If you want to check if the file exists on the remote server (more details):

import urllib2
page=urllib2.urlopen(url)
if page.code == 200:
    print "Exists!"

If you want to check if the .zip file is updated since the last download, you can first read the headers (more details). The 'last-modified' date and 'content-length' can tell you if it has been updated:

print page.headers.items()
>> [('content-length', '39600'), ('set-cookie', 'f5_persistence=1729145024.20480.0000; path=/'), ('accept-ranges', 'bytes'), ('server', 'Apache'), ('last-modified', 'Fri, 18 Dec 1998 23:27:52 GMT'), ('connection', 'close'), ('etag', '"a0a66ce5-9ab0-33f4cb8492e00"'), ('date', 'Tue, 15 Apr 2014 07:27:54 GMT'), ('content-type', 'application/zip')]

For simple .zip files with single .txt contents, a much simpler piece of code can be used. .zip file

import zipfile
with ZipFile('spam.zip', 'w') as myzip:
    myzip.write('eggs.txt')

For .gzip files, the process is similar. .gzip:

import gzip
f = gzip.open('file.txt.gz', 'rb')
file_content = f.read()
f.close()

Other formats: .tar.gz, .7z and .bz2 files can also be processed.

Answer 6

.mbox mailbox format: This data format is a standard when exporting mailboxes (wikipedia). Besides personal emails, one source of .mbox files is mailing lists, which can be found with a search engine: example link. Mbox files are very powerful data sets for text-mining and sentiment analysis. Individual .mbox files can be concatenated (i.e. with cat in linux). Gmail users can download their emails with this tool from google.

With python there is a library to open and read the .mbox format. I'll post a snippet of python 2.7 code for the usage of the mailbox library: read more by typing help(mailbox.mbox).

import mailbox
m = mailbox.mbox('C:\\data\\sample.maxima.mbox')
print m[0].keys() # print typical dict keys (first message only)
total_emails = 0
for message in m:
    total_emails += 1
    # can access keys here, for example the subject of each message
print total_emails,'total_emails'

Output:

['Received', 'Received', 'Received', 'Date', 'Message-Id', 'User-Agent', 'From', 'To', 'References', 'Subject', 'Sender', 'Errors-To', 'X-BeenThere', 'X-Mailman-Version', 'Precedence', 'List-Help', 'List-Post', 'List-Subscribe', 'List-Id', 'List-Unsubscribe', 'List-Archive', 'Status']
620 total_emails

More examples of functionality.

I shared a working code as a Gist as well as the sample data file, which was publicly shared and shortened for testing. There is also multithreading, which speeds up the code for large .mbox files. The goal of this code is to count the different spellings in my email Sent folder: Advisor or Adviser (the 'the' is just a reference to compare to).