Python as Glue

Python is a versatile and powerful language that can be used for a wide variety of tasks. One of the most common use cases for Python is as a “glue” language: it helps us combine skills and programs we already know how to use by allowing us to easily convert data from one format to another. This means that we can take data in one format that we don’t have tools to manipulate and change it into data for tools that we’re comfortable with. Whether we need to process a CSV, web page, or JSON file, Python can help us get the data into a format we can use.

For example, we might use Python to pull data from a web page and put it into Excel, where we already know how to manipulate it. We might also read a CSV file downloaded from a website, calculate the totals from it, and then output the data in JSON format.

Who This Series is For

In this series of tutorials, we’re not looking at data science. That is, this isn’t about doing heavy statistical or numerical calculations on data we’ve received. Python is one of the industry-standard tools for doing calculations like these—using libraries such as NumPy and pandas —and there are plenty of resources available for learning data science (such as Become a Python Data Scientist).

In this series, we’ll looking at how to convert data from one form to another so that we can then go on to manipulate it.

We’re also going to assume a little knowledge of Python and programming already—such as what a variable is, what a dictionary is, and how to import a module. To start learning Python (or any other programming language) from scratch, check out SitePoint’s programming tutorials. The Python wiki also has a list of Python programming tutorials for programmers.

Getting Started

We’ll start this tutorial by looking at how to read data and then how to write it to a different format.

Reading Data

Let’s try an example. Let’s imagine we’re an author on a tour of local libraries, talking to people about our books. We’ve been given plymouth-libraries.json —a JSON file of all the public libraries in the town of Plymouth in the UK—and we want to explore this dataset a little and convert it into something we can read in Excel or Google Sheets, because we know about Excel.

First, let’s read the contents of the JSON file into a Python data structure:

Now let’s explore this data a little in Python code to see what it contains:

This will print dict_keys(['type', 'name', 'crs', 'features']), which are the top-level keys from this file.

Similarly:

This will print Central Library, which is the LibraryName value in properties for the first entry in the features list in the JSON file.

This is the most basic, and most common, use of Python’s built-in json module: to load some existing JSON data into a Python data structure (usually a Python dictionary, or nested set of Python dictionaries).

Bear in mind that, to keep these examples simple, this code contains no error checking. (Check out A Guide to Python Exception Handling for more on that.) But handling errors is important. For example, what would happen if the plymouth-libraries.json file didn’t exist? What we do in that situation depends on how we should react for errors. If we’re running this script by hand, Python will display the exception that occurs—in this case, a FileNotFoundError exception. Simply seeing that exception may be enough; we may not want to “handle” this in code at all:

If we’d like to do something more than have our program terminate with an error, we can use Python’s try and except keywords (as the exception handling article above describes) to do something else of our choosing. In this case, we display a more friendly error message and then exit (because the rest of the program won’t run without the list of libraries!):

Writing

Now we want to write that data from its Python dictionary into a different format on disk, so we can open it in Excel. For now, let’s use CSV format, which is a very simple file format that Excel understands. (If you’re thinking, “Hey, why don’t we make it a full Excel file!” … then read on. CSV is simpler, so we’ll do that first.) This process of taking Python data structures and writing them out as some file format is called serialization. So we’re going to serialize the data we read as JSON into CSV format.

The image below demonstrates the stages involved in serialization.

A CSV file is a text file of tabular data. Each row of the table is one line in the CSV file, with the entries in the row separated by commas. The first line of the file is a list of column headings.

Consider a set of data like this:

This data could look like this as a CSV file:

To write out a CSV file, we need a list of column header names. Fortunately, these will be the keys of the properties of the first entry in "features", since all libraries have the same keys:

Given those names, we use the built-in csv module to write the header, and then write one row per library—to a file we open called plymouth-libraries.csv —like this:

This is the core principle behind using Python as a file format converter to glue together two things:

1. Read data in whatever format it’s currently in, which gives us that data as Python dictionaries.
2. Then serialize those Python dictionaries into the file format we actually want.

That’s all there is to it. Now we have a CSV file that we can open and look through as we choose.

The script and input files referenced above can be downloaded from GitHub.

Reading Data with Code

A second example will further demonstrate this principle of format conversion. Let’s resume our fictional library tour. The following week, we’re planning to continue our tour by visiting libraries in north Somerset, and the list of those libraries is in a format called XML. Opening the XML file and looking at its contents suggests that an individual library is listed in this file as something like this:

So we’re going to read that file with Python’s built-in XML module. Again, the goal here is to make a convenient Python dictionary and then serialize it. But this time, we’re also going to alter the data a little along the way: we only care about libraries where the postcode (the British version of a zip code) is BS40 or less. (So we want to keep BS21 6NN as a postcode, but ignore a postcode such as BS49 1AH.) This lets us use our knowledge of Python strings and numbers to discard some of the data.

First, let’s read in the XML file. The ElementTree module is traditionally imported with the name ET, for brevity, and can read a file with .parse(). We’ll also need the json module later to write out a JSON version of this data:

ElementTree reads in an XML file and forms a tree structure out of it. At the top of the tree is the root, which is the first element in the XML. An ET element has properties relating to its content. An XML element like <MyElement>content here</MyElement> would have a tag property of "MyElement", the tag name, and a text property of "content here", containing the text in the element. The element also presents as a list that can be iterated over with a Python for loop, which will yield all the element’s direct child elements.

We obtain a reference to the root element of the tree:

In this XML file in particular, this element is actually <Root>, but the name is a coincidence; it could be called anything. We can then iterate over all child elements of the root. These child elements are the <Row> elements in the XML, each of which defines one library. Our plan here is to turn the above XML for a <Row> into a Python dictionary, with one entry per child of the <Row>. That is, the above XML should become this Python dictionary:

To do this, we’ll iterate over each of the <Row> elements in the root, and then, for each <Row>, iterate over each of its child elements and add them to a dictionary for that row. This will give us one dictionary per library, which we’ll store in a list called libraries:

At this point, we’ve successfully parsed the input XML data into a list of Python dictionaries, meaning that we can discard the XML and work simply with the Python data structures. The first goal is to remove all the libraries with postcode BS40 or greater, which can be done by removing items from the list that don’t match:

The final step is to serialize the Python data into the destination format we want, which in this case is JSON. This is done with the json module’s dump function:

The outputted JSON data will look something like this:

The script and input files can be downloaded from GitHub.

General Principles

The goal here is to take three steps:

1. Load the data we want into a set of Python data structures.
2. Manipulate the data however we want—which is convenient, because it’s now a set of Python data structures.
3. Serialize the data into whatever output format we want.

Sometimes, the steps might require a little programming, as above. JSON data, as we saw in the first example, has a built-in Python library to load it (or “de-serialize” it) directly into a Python dictionary, ready for us to manipulate. XML data doesn’t have such a library, so a little delving into the Python docs is required if we’re to understand exactly how to read it. Many more esoteric formats also have Python libraries available to read or to write them, which can be installed from PyPI, Python’s suite of third-party libraries.

Parsing Complicated Formats

A useful guide to parsing some more complicated formats using the Python pandas module is available in Using Python to Parse Spreadsheet Data.

Understanding All the Formats

One example of such a format is KML, which was originally developed for Google Earth to allow a number of points on the globe to be specified and then loaded onto a map. It might be useful to plot our list of Plymouth libraries on a map so we can see where they all are in relation to one another and plan the best route for our tour. Let’s write a script that reads in that data and outputs it as KML so we can do that!

A little searching reveals that Python doesn’t have a built-in module for reading KML, but there are quite a few available for download. The simplest seems to be simplekml (truth in advertising!), and this is available from PyPI. Installing modules from PyPI involves a little setup, which is documented in Virtual Environments in Python Made Easy and also in the Python Packaging Guide, but once that’s done (and it only needs to be done once and will work for ever more), we should be able to pip install simplekml to get the simplekml module.

Embracing All the Formats

At this point, the script we need to write follows our standard three steps: load the data from the format it’s in (in this case, we’ll use the CSV file we created earlier), manipulate it if we wish (we don’t need to this time), and then serialize it to a file in the new format (which the simplekml documentation shows us how to do for KML).

First, let’s read in our input data. The Python csv module knows how to read the CSV file of libraries that we created previously:

Now we have a Python list of dictionaries. One of those dictionaries looks like this:

To create a KML file, we can follow the simplekml module’s documentation. Make a Kml() object, then call its newpoint() function with name, latitude, and longitude values for each library:

Finally, save the file as KML:

Now we can import that KML file to Google Earth and … this is what we see.

The script and input files can be downloaded from GitHub.

Reading HTML

While we’re in Plymouth, we notice that the skyline is dominated by Beckley Point, the tallest building in the southwest of England, and that gets us wondering about tall buildings generally. Wikipedia, as usual, provides a page about the tallest buildings in the world, and we decide we’d like to explore that list a little more in Excel. Of course, we already know that we can copy a table from a web page and paste it into Excel and it does a reasonable job, but that’s not very exciting. We’re all Python all the time now! What if we wanted to look at, say, all the tall buildings without an E in their name? Or to get that list every day by running one command? So let’s break out the text editor again, with the intention of getting the data from that Wikipedia page and then putting it into Excel with a script.

Extracting data from an HTML page is the task of screen scraping, and much has been written about it. One useful guide is Web Scraping for Beginners. There are dedicated screen scraping tools to do the work, but Python can do it too, and then we have all the power of code at our disposal.

First, let’s look at how to fetch and read an HTML page with Python to extract data from it.

Requesting data from the Web can be done with the built-in urllib.request module, but the requests module is easier to use, so it’s worth installing that. We’ll also need Beautiful Soup (BS) for parsing HTML. (Again, this can be done with built-in modules, but BS is more pleasant.) So pip install requests beautifulsoup4 should get those modules ready for use. After that, the content of a web page can be fetched with requests.get() and the response’s content property:

At this point, html is a long string variable of HTML. Beautiful Soup knows how to parse that into a useful data structure that we can explore:

Extracting data from HTML is one of the tasks that requires some programming—as well as some knowledge of the structure of the HTML in question—to make best use of the data. Beautiful Soup is the most-used Python module for this, and it has a deep well of functionality to help with advanced screen scraping.

For most examples, though, it will suffice to load the HTML into a “soup” of parsed data, and then use the .select() function to extract HTML elements by CSS selector, iterate through them, and read the .text within them. This is somewhat similar to using JavaScript methods to identify HTML elements in the DOM in a web page, which may be familiar to web programmers. The BS4 documentation explains some of Beautiful Soup’s more advanced features in detail, but much of the work can be done with select(), which takes a CSS selector as a parameter and returns a list of elements matching that selector, similarly to JavaScript’s querySelectorAll().

The HTML table that we want—of the tallest buildings in the world by height to pinnacle—is the fifth <table> element on the page. If the Wikipedia HTML had an ID attribute on that table, we could use that to find it directly, but it doesn’t. So we’ll use the knowledge that it’s the fifth table to find it with the .select() function:

As can be seen from the table, we can get the title of each column by reading the first row. The table actually has two header rows, because there are separate columns for both height in meters and height in feet, and so we handle this a little differently by adding two columns to the list of column titles:

Once the column titles have been extracted—so that they can be used to make a dictionary for each building—it’s time to actually extract the building data. Each row in the table contains the values for a building. This means that, for each row, we need to combine the column titles and the column data to make a dictionary. Fortunately, Python provides the zip() function, which makes this easy.

zip() takes two lists— [a, b, c, d] and [w, x, y, z] —and returns one list: [(a,w), (b,x), (c,y), (d,z)]. We can then iterate through this combined list.

This means that, for each table row (or <tr>), we can retrieve the cells (<td>) in that row with tr.select("td"), and then zip the column titles together with them. Each cell returned from the <td> is a soup object, so we must then use the .text property of each <td> to retrieve the text in it:

More Data Fiddling

Now that we have the data in a convenient Python data structure and no longer have to worry about HTML (or however else we obtained it), the next step is to manipulate the data. Here, it will be useful to convert the numeric heights of the buildings to be actual numbers. Since HTML data is all textual, this means that all the “height” values in our data are strings of digits rather than actual numbers. We can see this with a quick test, using Python’s built-in pprint module to “pretty-print” the first building in the list:

Let’s convert the “Height (ft)”, “Height (m)” and “Floors” values from strings to numbers. These data may contain characters that aren’t numbers, so a simple call to float() may not work and we’ll need to be a little more clever.

The most basic way to convert string data like this to a floating point number or integer is to walk through each character in the string and only keep it if it’s a digit or a decimal point, and then to call float() on the kept characters only. This removes any commas, carriage returns, spaces, or other extraneous data in a string like "2,345.6 m", and would look like this, for converting the “Height (m)” data for each building:

A second but slightly more advanced way is to use a list comprehension to extract these digits, which will convert "2,345.6 m" into ["2", "3", "4", "5", ".", "6"] —which can then be combined back into a string with .join() and then to a number with float():

And a third way that’s shorter still is to use regular expressions, which are more powerful but also a more complex subject:

Once the data is converted to numbers for each building, if we pretty-print the first building, the data is more properly numeric:

Writing for Excel for Real

The final step is to convert this data to our preferred destination format.

It’s sometimes useful to be able to create nicer Excel output than CSV can provide. In particular, a CSV file looks very plain when loaded into a spreadsheet, and has no formatting at all. If we’re producing a sheet for our own data exploration, then it may not matter how pretty it is. But a little effort put into making data look more readable can help a lot, especially if it’s destined for someone else. For this, we’ll use Python’s XlsxWriter, which can be installed with pip install Xlsxwriter. We can then pay some attention to the examples given in XlsxWriter’s tutorial.

First, create a new Excel spreadsheet file, and add a worksheet to it:

To add formatting to Excel cells using XlsxWriter, it’s easiest to define a particular format style, which can then be applied later. Let’s do that for some of the cell data up front:

The first row of the spreadsheet will be the column titles. We can fetch these by using the dictionary keys from the first building in the list:

We now need to walk through this list of titles and write each into its corresponding column in row 0. To do this, we use worksheet.write with a row number (always 0), column number (starting at 0 and increasing by one for each column title), cell content (the column title string itself), and a cell format (bold, as defined above). Python provides the useful enumerate() function to iterate the list while getting the index position for each item. enumerate([a, b, c, ...]) will return a list ([(0, a), (1, b), (2, c), ...]) which can be iterated over to write out our column titles at the top of each column:

Then, to write out all the data, we do exactly the same thing: iterate through the column titles list once for each building, and write out the value of that field in the building’s dictionary into the appropriate row and column number:

Finally, a call to worksheet.autofit() adjusts all the cell widths in the spreadsheet so they fit the data contained with them, similar to double-clicking on a column border in Excel itself. Then, closing the worksheet ensures it’s saved:

The script and input files can be downloaded from GitHub.

And then we have some more nicely formatted Excel data to look at, as pictured below.

Summary

And that’s all we need! Whether we’re using code libraries to find real libraries, or trying to extract sense from a mess of reports or published data, Python’s there for us.

For each file or set of data we want to work with, we can take these three steps:

1. Load the data we want into a set of Python data structures, using Python’s built-in modules or others that we find from PyPI which can read that format.
2. Manipulate the data however we want—which is convenient, because it’s now a set of Python data structures.
3. Serialize the data into whatever output format we want, again using built-in or third-party modules.