How to Import CSV Data

Now that we have learned a little bit about the data, let’s open the file in Python and convert the data into a form Python can understand. This only takes a couple of lines of code:

import csv

csvfile = open('data-text.csv', 'rb')
reader = csv.reader(csvfile)

for row in reader:
    print row

Let’s go through each line. In the previous chapter, we did all of our coding in the Python interpreter, but as the code gets longer and more complicated, it is easier to write and run code from a file. After we walk through this bit of code, we will save the code to a .py file, which is a Python file, and run the file from the command line.

The first line of the script imports a library called csv:

import csv

A Python library is a package of code that provides functionality you can use in your Python programs. The csv library we are importing comes with your Python installation as part of the standard library (or stdlib). Once we import a library into our file, we can use it. If we did not have this library, this script would be a lot longer—the csv library gives us helper functions so we don’t have to write as much code to perform a more complex task.

The second line of code takes our data-text.csv file, which should be located in the same folder as the script, and passes it to the open function:

csvfile = open('data-text.csv', 'rb')

open is a built-in function in Python, meaning the act of opening a file is so common that the core Python contributors felt it should be added to every Python installation. When we use the open function, we pass a filename as the first argument (here we used 'data-text.csv') and then we optionally specify which mode this file should be opened in (we used 'rb'). If you visit the docs for the open function, you will find that the argument 'rb' means that we open the file as read-only and in binary mode. Opening the file in binary mode allows our code to run on both Windows and Unix-based operating systems. The other common mode is write ('w', or 'wb' for write in binary mode).

We store the output of this function in the variable csvfile. csvfile now holds an open file as its value.

In the next line, we pass csvfile to the reader function in the csv module. This function tells the csv module to read the open file as a CSV:

reader = csv.reader(csvfile)

The output of the function csv.reader(csvfile) is stored in the reader variable. The reader variable now holds a Python CSV reader with the opened file. This CSV reader allows us to easily view data from our file using simple Python commands. In the last piece of code, we have something called a for loop.

A for loop is a way of iterating over Python objects, commonly used with lists. A for loop tells Python code, “For each thing in this list of things, do something.” The first word used after for in a for loop is the variable that will hold each object in the list (or other iterable object). The code below the for loop uses that variable to perform more functions or calculations on the item. For that reason, it is best to use a word which conveys meaning, so you and others can easily read and understand the code.

Try running the following example in your Python interpreter:

dogs = ['Joker', 'Simon', 'Ellie', 'Lishka', 'Fido']
for dog in dogs:
    print dog

With this for loop, we store each dog name in the for loop variable dog. For each of the iterations of our for loop, we print out the dog’s name (held in the variable dog). When the for loop has gone through each dog’s name (or item in the list), the code is finished running.

In the case of the code we are using to read our CSV, our reader object is a Python container holding the data rows. In our reader for loop, we hold each row of data in the variable row. The next line states that for each row, we want Python to print the row:

for row in reader:
    print row

Now that we can import and loop through our data, we can really begin to explore it.

Saving the Code to a File; Running from Command Line

As you are working on code as a developer, you will want to save even partial bits of in-progress code to review and use later. If you are interrupted, being organized and saving your code means you can seamlessly pick up where you left off.

Let’s save the file that has our code up to this point, and run it. The code should look like this (if you haven’t already, open up your text editor, create a new file, and type this code into it):

import csv

csvfile = open('data-text.csv', 'rb')
reader = csv.reader(csvfile)

for row in reader:
    print row

Save the code using your text editor as a .py (Python) file. The full filename should be something like import_csv_data.py.

Put the data file data-text.csv in the same folder where you just saved your Python file. If you want to put the file in another location, you will need to update the code appropriately for your new file location.

After you save the file, you can run it using the command line. If you don’t already have it open, open your command line (Terminal or cmd), and navigate to where the file is located. Let’s assume that you put the file in ~/Projects/data_wrangling/code. To navigate there using the Mac command line, you would use the change directory or folder command (cd):

cd ~/Projects/data_wrangling/code

After you get to the right location, you can run the Python file. Up until this point, we were running our code in the Python interpreter. We saved the file as import_csv_data.py. To run a Python file from the command line, you simply type python, a space, and then the name of the file. Let’s try running our import file:

python import_csv_data.py

Your output should look like a bunch of lists—something like the data shown here, but with many more records:

['Healthy life expectancy (HALE) at birth (years)', 'Published', '2012',
 'Western Pacific', 'Lower-middle-income', 'Samoa', 'Female', '66',
 '66.00000', '', '', '']
['Healthy life expectancy (HALE) at birth (years)', 'Published', '2012',
 'Eastern Mediterranean', 'Low-income', 'Yemen', 'Both sexes', '54',
 '54.00000', '', '', '']
['Healthy life expectancy (HALE) at birth (years)', 'Published', '2000',
 'Africa', 'Upper-middle-income', 'South Africa', 'Male', '49', '49.00000',
 '', '', '']
['Healthy life expectancy (HALE) at birth (years)', 'Published', '2000',
 'Africa', 'Low-income', 'Zambia', 'Both sexes', '36', '36.00000', '', '', '']
['Healthy life expectancy (HALE) at birth (years)', 'Published', '2012',
 'Africa', 'Low-income', 'Zimbabwe', 'Female', '51', '51.00000', '', '', '']

Did you get this output? If not, stop for a minute to read the error you received. What does it tell you about where you might have gone wrong? Take time to search for the error and read a few ways people have fixed the same error. If you need extra help on troubleshooting how to get past the error, take a look at Appendix E.

With the current code we are writing, along with many other solutions we’ll write, there are often many ways to solve a problem. csv.reader() returns each new line of your file as a list of data and is an easy-to-understand solution when you begin. We are going to modify our script slightly to make our list rows into dictionary rows. This will make our data a little easier to read, compare, and understand as we explore our dataset.

In your text editor, take line 4, reader = csv.reader(csvfile), and update it to read reader = csv.DictReader(csvfile). Your code should now look like this:

import csv

csvfile = open('data-text.csv', 'rb')
reader = csv.DictReader(csvfile)

for row in reader:
    print row

When you run the file again after saving it, each record will be a dictionary. The keys in the dictionary come from the first row of the CSV file. All the subsequent rows are values. Here is an example row of output:

{
    'Indicator': 'Healthy life expectancy (HALE) at birth (years)',
    'Country': 'Zimbabwe',
    'Comments': '',
    'Display Value': '49',
    'World Bank income group': 'Low-income',
    'Numeric': '49.00000',
    'Sex': 'Female',
    'High': '',
    'Low': '',
    'Year': '2012',
    'WHO region': 'Africa',
    'PUBLISH STATES': 'Published'
}

At this point, we have successfully imported the CSV data into Python, meaning we were able to get the data from the file into a usable format Python can understand (dictionaries). Using a for loop helped us see the data so we could visually review it. We were able to use two different readers from the csv library to see the data in both a list and a dictionary form. We will be using this library again as we start exploring and analyzing datasets. For now, let’s move on to importing JSON.

How to Import JSON Data

Importing a JSON file is even easier than importing a CSV file in Python. The following code will open, load, import, and print a JSON data file:

import json                                 

json_data = open('data-text.json').read()   

data = json.loads(json_data)                

for item in data:                           
    print item

Imports the json Python library, which we will use to process the JSON.

Uses the built-in Python function open to open the file. The filename is data-text.json (and this is the first argument for our open function). This line of code calls the open file’s read method, which will read the file and store it in the json_data variable.

Uses json.loads() to load JSON data into Python. The data variable catches the output.

Iterates over the data using a for loop and prints each item, which will be the output in this example.

If you run python import_json_data.py from the command line, the output will show a dictionary for each record in the JSON file. It should look almost exactly like the final CSV output. Remember to copy the data file into the folder or to change the file path to reflect it’s location.

At the end of the CSV section, we learned how to save a file and run it from the command line. With this example, let’s start with a blank file and build up to that.

For a quick overview:

1. Create a new file in your code editor.

2. Save it as import_json_data.py in the folder holding your code.

3. Move (or save) the data file to the folder where you are storing your code. (Make sure to rename the file so it matches the name you use in the code. The book uses data-text.json.)

4. Go back to your code editor, which should still have the import_json_data.py file open.

Let’s read through the code and compare it to the CSV import file. First, we import Python’s built-in json library:

import json

Then we use the open function we are already familiar with to open the file data-text.json and call the read method on the open file:

json_data = open('data-text.json').read()

In the CSV file, we did not call read. What is the difference? In the CSV example, we opened the file in read-only mode, but in the JSON example, we are reading the contents of the file into our json_data variable. In the CSV example, the open function returns an object that is a file, but in the JSON example, we first open and then read the file, so we have a str (string). This difference is based on the fact that the Python json and csv libraries handle input in different ways. If you tried to pass a string into the CSV reader, it would throw an error, and the JSON loads function would throw an error if you passed it a file.

The good news is that Python makes it easy to write a string to a file (e.g., if you needed to use the CSV reader but only had a string), and it’s also easy to read a file into a string. To Python, a closed file is just a filename string waiting to be opened and read. Getting data from a file, reading it into a string, and passing that string to a function requires just a few lines of Python code.

filename = 'data-text.json'
type(open(filename, 'rb'))      # similar to csv code
type(open(filename).read())     # similar to json code

The Python json library’s loads function expects a string, not a file. The Python csv library’s reader function expects an open file. In the next line of our script, we will use the loads function, which loads a JSON string into Python. The output of this function is assigned to a variable called data:

data = json.loads(json_data)

To preview our data, we iterate over each item and print it out. This isn’t necessary for our code, but it helps us preview the data and make sure it’s in the proper form:

for item in data:
    print item

Once you are done writing your file you can save and run it. As you can see, opening and converting a JSON file to a list of dictionaries in Python is quite easy. In the next section, we will explore more customized file handling.

How to Import XML Data

Because we have an understanding of the data, let’s import the file into a usable form for Python. To get the data out of XML form and into Python, we will write the following code:

from xml.etree import ElementTree as ET

tree = ET.parse('data-text.xml')
root = tree.getroot()

data = root.find('Data')

all_data = []

for observation in data:
    record = {}
    for item in observation:

        lookup_key = item.attrib.keys()[0]

        if lookup_key == 'Numeric':
            rec_key = 'NUMERIC'
            rec_value = item.attrib['Numeric']
        else:
            rec_key = item.attrib[lookup_key]
            rec_value = item.attrib['Code']

        record[rec_key] = rec_value

    all_data.append(record)

print all_data

As you can see, this is a little more complicated than the CSV and JSON examples.

Let’s take a closer look. Create a new file in your code editor and save it to the folder where you have been putting your code. Name it import_xml_data.py. Also, if you downloaded the data directly from the WHO site and not the book’s repository, rename the saved XML file data-text.xml and put it in the same folder as your new code.

First, let’s import ElementTree, part of the built-in library we are going to use to parse the XML:

from xml.etree import ElementTree as ET

This import statement has an extra component we did not have last time: as ET. We are importing ElementTree, but will refer to it as ET. Why? Because we are lazy and do not want to type out ElementTree every time we want to use the library. This is a common practice when importing classes or functions with long names, but it is not mandatory. The as tells Python we want to use ET to represent ElementTree.

Next, we call the parse method on the ET class, which will parse data from the filename we pass. Because we are parsing a file located in the same folder, the filename needs no file path:

tree = ET.parse('data-text.xml')

The parse method returns a Python object people normally store in a variable tree. When talking about XML, the tree is the whole XML object stored in a way Python can understand and parse.

To understand how to traverse the tree (and data contained therein), we begin at the root of the tree. The root is the first XML tag. To start at the root of the tree, we call the getroot function:

root = tree.getroot()

If you were to take a moment to print out root by adding print root after the previous statement, you would find it prints out the Python representation of the root element in the XML tree (it should look like <Element 'GHO' at 0x1079e79d0>²). From this representation, we can quickly tell ElementTree identified the root or outermost tag of the XML document as an XML node with a tag name GHO.

Now that we’ve identified the root, we need to figure out how to access the data we want. After analyzing the data in this chapter’s CSV and JSON sections, we know what data we’d like to review. We need to traverse the XML tree and extract that same data. In order to understand what we’re looking for, we need to understand the overall structure and format of the XML tree. Here, we’ve slimmed down the XML file we are using and removed the data, so we can view just the core structure:

<GHO>
    <Data>
        <Observation>
            <Dim />
            <Dim />
            <Dim />
            <Dim />
            <Dim />
            <Dim />
            <Value>
                <Display>
                </Display>
            </Value>
        </Observation>
        <Observation>
            <Dim />
            <Dim />
            <Dim />
            <Dim />
            <Dim />
            <Dim />
            <Value>
                <Display>
                </Display>
            </Value>
        </Observation>
    </Data>
</GHO>

In reviewing this structure, we can see each “row” of data is held in an Observation tag. The data for each of these rows is then held in the Dim, Value, and Display nodes within each Observation node.

So far we have three lines of code. To investigate how we can use Python to extract these nodes, let’s add print dir(root) to the end of our current code, then save the file and run it on the command line:

python import_xml_data.py

You will see all the methods and properties of the root variable. Your code should look like this:

from xml.etree import ElementTree as ET

tree = ET.parse('data-text.xml')
root = tree.getroot()

print dir(root)

When you run the file, you should see this output:

['__class__', '__delattr__', '__delitem__', '__dict__', '__doc__',
'__format__', '__getattribute__', '__getitem__', '__hash__', '__init__',
'__len__', '__module__', '__new__', '__nonzero__', '__reduce__',
'__reduce_ex__', '__repr__', '__setattr__', '__setitem__', '__sizeof__',
'__str__', '__subclasshook__', '__weakref__', '_children', 'append', 'attrib',
'clear', 'copy', 'extend', 'find', 'findall', 'findtext', 'get',
'getchildren', 'getiterator', 'insert', 'items', 'iter', 'iterfind',
'itertext', 'keys', 'makeelement', 'remove', 'set', 'tag', 'tail', 'text']

Let’s assume our file is too large for us to open, and that we don’t know the structure of the file. This is often the case with larger XML datasets. What can we do? Let’s start by calling dir(root) to review the root object’s methods. We notice the getchildren method as a potential solution for seeing children in the Observation nodes. After reading the latest documentation and a question on Stack Overflow, we find the getchildren method will return the subelements, but the method has been deprecated. When a method you want to use is or will soon become deprecated, you should attempt to use what the authors of the library suggest as a replacement.

Based on what the documentation is recommending, if we want to view subelements for the root tree, we should use list(root). If we have a very large file, returning direct subelements will give us a view of the data and its structure, without overwhelming us with too many lines of output. Let’s try that.

Replace this line:

print dir(root)

with this line:

print list(root)

Run the file again from your command line. You should end up with the following output, which is a list of Element objects (for our purposes, elements are XML nodes):

[<Element 'QueryParameter' at 0x101bfd290>,
<Element 'QueryParameter' at 0x101bfd350>,
<Element 'QueryParameter' at 0x101bfd410>,
<Element 'QueryParameter' at 0x101bfd590>,
<Element 'QueryParameter' at 0x101bfd610>,
<Element 'QueryParameter' at 0x101bfd650>,
<Element 'Copyright' at 0x101bfd690>,
<Element 'Disclaimer' at 0x101bfd710>,
<Element 'Metadata' at 0x101bfd790>,
<Element 'Data' at 0x102540250>]

The list contains Element objects called QueryParameter, Copyright, Disclaimer, Metadata, and Data. We can traverse these elements and explore the contents so we better understand how to extract the data we are after.

When searching for data stored in an XML tree, the Data element is likely a good place to start. Now we have found the Data element, and we can focus on that subelement. There are a couple of ways to get the Data element, but we will use the find method. The root element’s find method allows us to search for a subelement using the tag name. Then, we will get the Element’s children and see what we should do next.

Replace this line:

print list(root)

with this:

data = root.find('Data')

print list(data)

When you rerun your file with the new lines of code, you will see a dizzying output of a list of Observation elements. These are our data points. Although there is a lot of information output, you can tell it is a list because the last character is a ], which symbolizes the end of a list.

Let’s iterate over the list of data. Each Observation represents a row of data, so they should have more information contained inside. We can iterate over those elements individually to see what subelements exist. With a Python Element objects, we can iterate over all of the subelements similar to how we would a list. Therefore, we can iterate over each Observation and iterate over each subelement in the Observation elements. This is our first time using a loop within a loop, and it should show us whether there are more subelements we can use to extract data.

Replace this line:

print list(data)

with these lines:

for observation in data:
    for item in observation:
        print item

and rerun the file.

The output is a bunch of Dim and Value objects. Let’s try a couple of different ways to explore what might be contained in these elements. There are a few ways you can view data within Python’s Element object. One of the attributes of every Element node is text, which displays the text contained inside the node.

Replace this line:

print item

with this line:

print item.text

and rerun the file.

What happened? You should have gotten a lot of None values back. This is because item.text does not exist for those elements because there is no text between the elements’ tags. Look at how <Dim /> is structured in the data samples. For example:

<Dim Category="YEAR" Code="2000"/>

In Python, item.text is only useful if your elements are structured with text in the nodes, like the following:

<Dim Category="YEAR">2000</Dim>

For the second example, item.text returns 2000.

XML data can be structured in many different ways. The information we need is located in the XML; it just wasn’t in the first place we looked. Let’s continue to explore.

Another place to look is in child elements. Child elements are subelements of a parent element. Let’s check if we have any child elements. Replace this line:

print item.text

with this line:

print list(item)

When you rerun the code with that change, the output shows that some (but not all) elements have children. That’s interesting! These are actually Value elements. Look at how these are structured in the data sample:

<Value>
    <Display>
    </Display>
</Value>

If we want to explore those child elements, we’ll need another loop similar to the loop we wrote to go through the items in each Observation.

There is another method we can call for each Element object in Python, called attrib, which returns the attributes for each node. As we know from reviewing XML structure, if nodes don’t have values between the tags, they usually have attributes within the tags.

To see what attributes we have in our nodes, replace:

print list(item)

with this line:

print item.attrib

When we rerun the code, we see the data contained in the attributes output as a bunch of dictionaries. Rather than storing each element and the attributes in separate dictionaries, we want each full row of data in a dictionary together. Here is one record from our attrib output:

{'Category': 'PUBLISHSTATE', 'Code': 'PUBLISHED'}
{'Category': 'COUNTRY', 'Code': 'ZWE'}
{'Category': 'WORLDBANKINCOMEGROUP', 'Code': 'WB_LI'}
{'Category': 'YEAR', 'Code': '2012'}
{'Category': 'SEX', 'Code': 'BTSX'}
{'Category': 'GHO', 'Code': 'WHOSIS_000002'}
{'Category': 'REGION', 'Code': 'AFR'}
{'Numeric': '49.00000'}

Because we ended up with a dictionary for every record in our CSV example, let’s try to put this output in a similar form. The keys in our XML data dictionary will be slightly different, because the WHO does not provide the same data in the XML dataset as in the CSV dataset. We will work to get our data in the following form, but ignore the difference in key names. Ultimately, this will not affect how we use the data.

Just to remind you, here is a sample record from the CSV reader:

{
    'Indicator': 'Healthy life expectancy (HALE) at birth (years)',
    'Country': 'Zimbabwe',
    'Comments': '',
    'Display Value': '51',
    'World Bank income group': 'Low-income',
    'Numeric': '51.00000',
    'Sex': 'Female',
    'High': '',
    'Low': '',
    'Year': '2012',
    'WHO region': 'Africa',
    'PUBLISH STATES': 'Published'
}

Here is the goal for a sample record using our XML data. We aim to have it in this format by the time we are done parsing our XML tree:

{
    'COUNTRY': 'ZWE',
    'GHO': 'WHOSIS_000002',
    'Numeric': '49.00000',
    'PUBLISHSTATE': 'PUBLISHED',
    'REGION': 'AFR',
    'SEX': 'BTSX',
    'WORLDBANKINCOMEGROUP': 'WB_LI',
    'YEAR': '2012'
 }

Notice the High and Low fields are missing. If they existed in our XML dataset, we would add them to the keys of our new dictionary. The Display Value is also missing. We decided not to include it, as it’s the same as the Numeric value.

Currently, your code should look like this:

from xml.etree import ElementTree as ET

tree = ET.parse('data-text.xml')
root = tree.getroot()

data = root.find('Data')

for observation in data:
    for item in observation:
        print item.attrib

To create the data structure, we need to first create an empty dictionary for each record. We will use this dictionary to add keys and values, then we will append each record to a list, so we have a final list of all of our records (similar to our CSV data).

Let’s add our empty dictionary and an empty list to hold our data in. Add all_data = [] on a new line above the outer for loop and record = {} as the first line in the for loop, like so:

all_data = []

for observation in data:
    record = {}
    for item in observation:
        print item.attrib

Now we need to figure out what our key and values are for each line and add them to our record’s dictionary. For each attrib call, we get a dictionary with one or more value and key combinations returned, like this:

{'Category': 'YEAR', 'Code': '2012'}

It looks like the value of the Category key (here, YEAR) should be the key for our dictionary, and the value of Code (here, 2012) should be set as the value for that key. As you’ll recall from Chapter 2, a dictionary key should be easily used for lookup (like YEAR) and dictionary value should contain the value associated with that key (like 2012). With that knowledge, the preceding line would become:

'YEAR': '2012'

Update print item.attrib to print item.attrib.keys() in your code, then rerun it:

for item in observation:
    print item.attrib.keys()

It will output the keys of each attribute dictionary. We want to check the keys so we can form the keys and values of the new item dictionary. We end up with two different outputs: ['Category', 'Code'] and ['Numeric']. Let’s tackle one at a time. Based on our initial investigation, for the elements with both Category and Code, we know we need to use the Category values as keys and the Code values as values.

To do this, add [0] to the end of item.attrib.keys():

for item in observation:
    lookup_key = item.attrib.keys()[0]
    print lookup_key

This is called indexing. It will return the first item in the list.

If we rerun the code and look at the output, we now have the following:

Category
Category
Category
Category
Category
Category
Category
Numeric

Now that we have the key names, we can look up the values. We need the values of the Category key to use as the keys in our new dictionary. Update the inner for loop by creating a new variable, rec_key, which stores the value returned from item.attrib[lookup_key]:

for item in observation:
    lookup_key = item.attrib.keys()[0]
    rec_key = item.attrib[lookup_key]
    print rec_key

With these updates, rerun the code from your command line. For each record, we get the following values:

PUBLISHSTATE
COUNTRY
WORLDBANKINCOMEGROUP
YEAR
SEX
GHO
REGION
49.00000

These all look like great keys for our new dictionary, except for the last one. This is because the last element is a Numeric dictionary instead of the Category ones we have been working with. If we want to retain that data for our use, we need to set up a special case for those numeric elements using an if statement.

We want to see when lookup_key is equal to Numeric, and use Numeric as the key instead of the value (like we did with the Category keys). Update your code with the following:

for item in observation:

    lookup_key = item.attrib.keys()[0]

    if lookup_key == 'Numeric':
        rec_key = 'NUMERIC'
    else:
        rec_key = item.attrib[lookup_key]

    print rec_key

If you run your updated code, all of your keys should now look like keys. Now, let’s pull out the values we want to store in our new dictionary and associate them with those keys. In the case of Numeric, it’s simple, because we just want the Numeric key’s value. Make the following changes to your code:

    if lookup_key == 'Numeric':
        rec_key = 'NUMERIC'
        rec_value = item.attrib['Numeric']
    else:
        rec_key = item.attrib[lookup_key]
        rec_value = None

    print rec_key, rec_value

If you run the updated code, you will see the rec_value for Numeric is properly matched. For example:

NUMERIC 49.00000

For all other values, we set the rec_value to None. In Python, None is used to represent a null value. Let’s populate those with real values. Remember each record has a Category and a Code key, like so: {'Category': 'YEAR', 'Code': '2012'}. For these elements, we want to store the Code value as the rec_value. Update the line rec_value = None, so your if-else statement looks like the one shown here:

    if lookup_key == 'Numeric':
        rec_key = 'NUMERIC'
        rec_value = item.attrib['Numeric']
    else:
        rec_key = item.attrib[lookup_key]
        rec_value = item.attrib['Code']

    print rec_key, rec_value

Rerun the code, and you should now see that we have values for our rec_key and our rec_value. Let’s build the dictionary:

    if lookup_key == 'Numeric':
        rec_key = 'NUMERIC'
        rec_value = item.attrib['Numeric']
    else:
        rec_key = item.attrib[lookup_key]
        rec_value = item.attrib['Code']

    record[rec_key] = rec_value 

Adds each key and value to the record dictionary.

We also need to add each record to our all_data list. As we saw in “List Methods: Things Lists Can Do”, we can use the list’s append method to add values to our list. We need to append each record at the end of the outer for loop, as that is when it will have all of the keys for each of the subelements. Finally, we will add a print at the end of the file, to show our data.

Your full code to transform the XML tree to a dictionary should look like this:

from xml.etree import ElementTree as ET

tree = ET.parse('data-text.xml')
root = tree.getroot()

data = root.find('Data')

all_data = []

for observation in data:
    record = {}
    for item in observation:

        lookup_key = item.attrib.keys()[0]

        if lookup_key == 'Numeric':
            rec_key = 'NUMERIC'
            rec_value = item.attrib['Numeric']
        else:
            rec_key = item.attrib[lookup_key]
            rec_value = item.attrib['Code']

        record[rec_key] = rec_value

    all_data.append(record)

print all_data

Once you run the code, you will see a long list with a dictionary for each record, just like in the CSV example:

{'COUNTRY': 'ZWE', 'REGION': 'AFR', 'WORLDBANKINCOMEGROUP': 'WB_LI',
'NUMERIC': '49.00000', 'SEX': 'BTSX', 'YEAR': '2012',
'PUBLISHSTATE': 'PUBLISHED', 'GHO': 'WHOSIS_000002'}

As you can see, extracting data from the XML was a little more complicated. Sometimes CSV and JSON files won’t be as easy to process as they were in this chapter, but they are usually more easily processed than XML files. However, looking at the XML data allowed you to explore and grow as a Python developer, giving you a chance to create empty lists and dictionaries and populate them with data. You also honed your debugging skills as you explored how to extract data from the XML tree structure. These are valuable lessons in your quest to become a better data wrangler.