Take the 2-minute tour ×
Open Data Stack Exchange is a question and answer site for developers and researchers interested in open data. It's 100% free, no registration required.

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?

share|improve this question
Any reason you specifically said 'python'? It might be worth describing the advantages / disadvantages of the file formats (some of which you've done), and then use the answers for major languages w/ info on how to deal with them. –  Joe Oct 16 '13 at 21:23
I wrote about python, because I used it on every datasets I have worked before. This guide is for those that want to use the datasets. So, they won't have the privillage to choose the file format but they will have to work with what others gave to them. I couldn't wrote about other languages since python is the only one I use :) –  Anastasios Ventouris Oct 16 '13 at 22:33
You might want to add openpyxl, which offers XLSX support, and Pandas, whose I/O capabilities integrate and unify access from/to most of the formats: CSV, Excel, HDF, SQL, JSON, HTML, Pickle. –  ojdo Oct 17 '13 at 8:46
@ojdo Great. I have already added on the post! Thank you –  Anastasios Ventouris Oct 17 '13 at 9:11
Perhaps you can formulate a question, and write the post as an answer to your own question; fits better in the Q&A format. –  gerrit Oct 17 '13 at 11:25
show 5 more comments

5 Answers

up vote 24 down vote accepted

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:

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)

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:

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.

share|improve this answer
Great post!! Please add TSV as the preferred tabular-textual format vs CSV. TSV is a better format than CSV in almost every circumstance. –  nicerobot Oct 20 '13 at 18:46
I haven't worked with TSV before, but I promise I will search for it, practice a little and find the best library I can for python. I will updated it in the next few days :) –  Anastasios Ventouris Oct 20 '13 at 19:08
Also lxml for XML and HTML. –  dAnjou Jan 13 at 20:48
Any links to examples where you've used RDF effectively? I've only seen it mentioned as a file format, but I've never seen it used in a real way. –  Jeff Tratner Jan 18 at 17:25
@JeffTratner I don't have a link for an example, but I can tell you one. If you want to download the LOD Cloud, you will have to deal with RDF. At least, it was a few months before like this. –  Anastasios Ventouris Jan 18 at 17:38
show 1 more comment

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.

share|improve this answer
Can you recommend some sites with more information or tutorials on this? –  Jeanne Holm Jan 14 at 6:09
add comment

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")
share|improve this answer
Great post. May I have your permission to include it on the initial post? –  Anastasios Ventouris Nov 12 '13 at 21:53
Thanks! please do :) –  dvdgc13 Nov 12 '13 at 21:56
oh, sure, you mention VOTable, but no love for FITS? PyFITS merged into AstroPy, so you can use astropy.io.fits. I believe that SunPy (python library for solar physics) uses it, too. –  Joe Nov 18 '13 at 20:18
@joe, you are right! fits are an option too! and they are indeed accessible from SunPy too. –  dvdgc13 Nov 29 '13 at 17:37
add comment

.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'


['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).

share|improve this answer
add comment

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 donwload(url,filename):
    import urllib2

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)


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

import urllib2
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:

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

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

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

share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.