5.4 Working with XML/HTML and JSON

As we have seen in Chapter 3, our obtained data can come in a variety of formats. The most common ones are plain text, CSV, JSON, and HTML/XML. In this section we are going to demonstrate a couple of command-line tools that can convert our data from one format to another. There are two reasons to convert data.

First, oftentimes, the data needs to be in tabular form, just like a database table or a spreadsheet, because many visualization and machine learning algorithms depend on it. CSV is inherently in tabular form, but JSON and HTML/XML data can have a deeply nested structure.

Second, many command-line tools, especially the classic ones such as cut and grep, operate on plain text. This is because text is regarded as a universal interface between command-line tools. Moreover, the other formats are simply younger. Each of these formats can be treated as plain text, allowing us to apply such command-line tools to the other formats as well.

Sometimes we can get away with applying the classic tools to structured data. For example, by treating the JSON data below as plain text, we can change the attribute gender to sex using sed:

  1. $ sed -e 's/"gender":/"sex":/g' data/users.json | fold | head -n 3

Like many other command-line tools, sed does not make use of the structure of the data. Better is to either use a command-line tool that makes use of the structure of the data (such as jq which we discuss below), or first convert the data to a tabular format such as CSV and then apply the appropriate command-line tool.

We’re going to demonstrate converting XML/HTML and JSON to CSV through a real-world use case. The command-line tools that we’ll be using here are: curl, scrape (Janssens 2014g), xml2json (Parmentier 2014), jq (Dolan 2014), and json2csv (Czebotar 2014).

Wikpedia holds a wealth of information. Much of this information is ordered in tables, which can be regarded as data sets. For example, the page http://en.wikipedia.org/wiki/List_of_countries_and_territories_by_border/area_ratio contains a list of countries and territories together with their border length, their area, and the ration between the two.

Let’s imagine that we’re interested in analyzing this data. In this section, we’ll walk you through all the necessary steps and their corresponding commands. We won’t go into every little detail, so it could be that you won’t understand everything right away. Don’t worry, we’re confident that you’ll get the gist of it. Remember that the purpose of this section is to demonstrate the command line. All tools and concepts used in this section (and more) will be explained in the subsequent chapters.

The data set that we’re interested in, is embedded in HTML. Our goal is to end up with a representation of this data set that we can work with. The very first step is to download the HTML using curl:

  1. $ curl -sL 'http://en.wikipedia.org/wiki/List_of_countries_and_territories_'\
  2. > 'by_border/area_ratio' > data/wiki.html

The option -s causes curl to be silent and not output any other information but the actual HTML. The HTML is saved to a file named data/wiki.html. Let’s see how the first 10 lines look like:

  1. $ head -n 10 data/wiki.html | cut -c1-79
  2. <!DOCTYPE html>
  3. <html lang="en" dir="ltr" class="client-nojs">
  4. <head>
  5. <meta charset="UTF-8" /><title>List of countries and territories by border/area
  6. <meta http-equiv="X-UA-Compatible" content="IE=EDGE" /><meta name="generator" c
  7. <link rel="alternate" type="application/x-wiki" title="Edit this page" href="/w
  8. <link rel="edit" title="Edit this page" href="/w/index.php?title=List_of_countr
  9. <link rel="apple-touch-icon" href="//bits.wikimedia.org/apple-touch/wikipedia.p
  10. <link rel="shortcut icon" href="//bits.wikimedia.org/favicon/wikipedia.ico" />
  11. <link rel="search" type="application/opensearchdescription+xml" href="/w/opense

That seems to be in order. (Note that we’re only showing the first 79 characters of each line so that output fits on the page.)

Using the developer tools of our browser, we were able to determine that the root HTML element that we’re interested in is a <table> with the class wikitable. This allows us to look at the part that we’re interest in using grep (the -A command-line argument specifies the number of lines we want to see after the matching line):

  1. $ < data/wiki.html grep wikitable -A 21
  2. <table class="wikitable sortable">
  3. <tr>
  4. <th>Rank</th>
  5. <th>Country or territory</th>
  6. <th>Total length of land borders (km)</th>
  7. <th>Total surface area (km²)</th>
  8. <th>Border/area ratio (km/km²)</th>
  9. </tr>
  10. <tr>
  11. <td>1</td>
  12. <td>Vatican City</td>
  13. <td>3.2</td>
  14. <td>0.44</td>
  15. <td>7.2727273</td>
  16. </tr>
  17. <tr>
  18. <td>2</td>
  19. <td>Monaco</td>
  20. <td>4.4</td>
  21. <td>2</td>
  22. <td>2.2000000</td>
  23. </tr>

We now actually see the countries and their values that we first saw in the screenshot. The next step is to extract the necessary elements from the HTML file. For this we use the scrape tool:

  1. $ < data/wiki.html scrape -b -e 'table.wikitable > tr:not(:first-child)' \
  2. > > data/table.html
  3. $ head -n 21 data/table.html
  4. <!DOCTYPE html>
  5. <html>
  6. <body>
  7. <tr><td>1</td>
  8. <td>Vatican City</td>
  9. <td>3.2</td>
  10. <td>0.44</td>
  11. <td>7.2727273</td>
  12. </tr>
  13. <tr><td>2</td>
  14. <td>Monaco</td>
  15. <td>4.4</td>
  16. <td>2</td>
  17. <td>2.2000000</td>
  18. </tr>
  19. <tr><td>3</td>
  20. <td>San Marino</td>
  21. <td>39</td>
  22. <td>61</td>
  23. <td>0.6393443</td>
  24. </tr>

The value passed to argument -e, which stands for expression (also with many other command-line tools), is a so-called CSS-selector. The syntax is usually used to style web pages, but we can also use it to select certain elements from our HTML. In this case, we wish to select all <tr> elements or rows (except the first) that are part of a table which belongs to the wikitable class. This is precisely the table that we’re interested in. The reason that we don’t want the first row (specified by :not(first-child)) is that we don’t want the header of the table. This results in a data set where each row represents a country or territory. As you can see, we now have a <tr> elements that we’re looking for, encapsulated in <html>` and ’<body> elements (because we specified the -b argument). This ensures that our next tool, xml2json, can work with it.

As its name implies, xml2json converts XML (and HTML) to JSON.

  1. $ < data/table.html xml2json > data/table.json
  2. $ < data/table.json jq '.' | head -n 25
  3. {
  4.   "html": {
  5.     "body": {
  6.       "tr": [
  7.         {
  8.           "td": [
  9.             {
  10.               "$t": "1"
  11.             },
  12.             {
  13.               "$t": "Vatican City"
  14.             },
  15.             {
  16.               "$t": "3.2"
  17.             },
  18.             {
  19.               "$t": "0.44"
  20.             },
  21.             {
  22.               "$t": "7.2727273"
  23.             }
  24.           ]
  25.         },
  26.         {
  27.           "td": [

The reason we convert the HTML to JSON is because there is a very powerful tool called jq that operates on JSON data. The following command extracts certain parts of the JSON data and reshapes it into a form that we can work with:

  1. $ < data/table.json jq -c '.html.body.tr[] | {country: .td[1][],border:'\
  2. > '.td[2][], surface: .td[3][]}' > data/countries.json
  3. $ head -n 10 data/countries.json
  4. {"surface":"0.44","border":"3.2","country":"Vatican City"}
  5. {"surface":"2","border":"4.4","country":"Monaco"}
  6. {"surface":"61","border":"39","country":"San Marino"}
  7. {"surface":"160","border":"76","country":"Liechtenstein"}
  8. {"surface":"34","border":"10.2","country":"Sint Maarten (Netherlands)"}
  9. {"surface":"468","border":"120.3","country":"Andorra"}
  10. {"surface":"6","border":"1.2","country":"Gibraltar (United Kingdom)"}
  11. {"surface":"54","border":"10.2","country":"Saint Martin (France)"}
  12. {"surface":"2586","border":"359","country":"Luxembourg"}
  13. {"surface":"6220","border":"466","country":"Palestinian territories"}

Now we’re getting somewhere. JSON is a very popular data format, with many advantages, but for our purposes, we’re better off with having the data in CSV format. The tool json2csv is able to convert the data from JSON to CSV:

  1. $ < data/countries.json json2csv -p -k border,surface > data/countries.csv
  2. $ head -n 11 data/countries.csv | csvlook
  3. |---------+----------|
  4. |  border | surface  |
  5. |---------+----------|
  6. |  3.2    | 0.44     |
  7. |  4.4    | 2        |
  8. |  39     | 61       |
  9. |  76     | 160      |
  10. |  10.2   | 34       |
  11. |  120.3  | 468      |
  12. |  1.2    | 6        |
  13. |  10.2   | 54       |
  14. |  359    | 2586     |
  15. |  466    | 6220     |
  16. |---------+----------|

The data is now in a form that we can work with. Those were quite a few steps to get from a Wikipedia page to a CSV data set. However, when you combine all of the above commands into one, you will see that it’s actually really concise and expressive:

  1. $ curl -sL 'http://en.wikipedia.org/wiki/List_of_countries'\
  2. > '_and_territories_by_border/area_ratio' |
  3. > scrape -be 'table.wikitable > tr:not(:first-child)' |
  4. > xml2json | jq -c '.html.body.tr[] | {country: .td[1][],'\
  5. > 'border: .td[2][], surface: .td[3][], ratio: .td[4][]}' |
  6. > json2csv -p -k=border,surface | head -n 11 | csvlook
  7. |---------+----------|
  8. |  border | surface  |
  9. |---------+----------|
  10. |  3.2    | 0.44     |
  11. |  4.4    | 2        |
  12. |  39     | 61       |
  13. |  76     | 160      |
  14. |  10.2   | 34       |
  15. |  120.3  | 468      |
  16. |  1.2    | 6        |
  17. |  10.2   | 54       |
  18. |  359    | 2586     |
  19. |  466    | 6220     |
  20. |---------+----------|

That concludes the demonstration of conversion XML/HTML to JSON to CSV. While jq can perform much more operations, and while there exist specialized tools to work with XML data, in our experience, converting the data to CSV format as quickly as possible tends to work well. This way, you can spend more time becoming proficient at generic command-line tools, rather than very specific tools.