Extracting data from XML tree into pandas/csv with Python

Question

I have an issue with some XML files. I cannot say a lot about data, because it is for work and I don't want to be in trouble! From a huge XML file, 123091 lines of code, I only need data from 7 tags(if that makes sense). I am trying to extract that specific data, but I am having a bit of a situation when trying to store into pandas or csv. I have found a method to take some information out, like:

for info in root.iter('ArtistName'):
   print(info.text)

The code above will give me the artists in the data from that XML tag. Here is a little part of my Jupyter Notebook, with the output of the above lines of code:

Various Artists
Various Artists
Various Artists
Various Artists
Various Artists
Cream
Various Artists
Various Artists
Various Artists
Various Artists
Various Artists
Fleetwood Mac
Fleetwood Mac
Linkin Park
Lynyrd Skynyrd
Fleetwood Mac
Eric Clapton
The Black Keys
Tegan And Sara

And then, I have run into the problem, because in the below code, I cannot or better said, I don't know how to loop over each tag from XML to extract the data. Below is an attempt:

import xml.etree.ElementTree as ET
import pandas as pd

tree = ET.parse("filename.xml")
root = tree.getroot()
dfcols = ['IRC', 'IRC2', 'Artist', 'Song', 'Units', 'PPD', 'TerritoryCode']
df_xml = pd.DataFrame(columns = dfcols)

for i in root.iter(tree):
   df_xml = df_xml.append(pd.Series(index=dfcols), ignore_index=True)

df_xml.head()

The result of the above code is:

 IRC IRC2 Artist Song Units PPD TerritoryCode

Which is the header of the file that I want to create. I cannot find a way to bring the information I need into these columns.

I have also tried this:

def getValOfNode(node):
    return node.text if node is not None else None


def main():

    dfcols = ['IRC', 'IRC2', 'Artist', 'Song', 'Units', 'PPD', 'TerritoryCode']
    df_xml = pd.DataFrame(columns = dfcols)

    for node in tree:
        IRC = node.find('IRC')
        IRC2 = node.find('ICPN')
        Artist = node.find('rtistName')
        Song = node.find('Title')
        Units = node.find('ConsumerSales')
        PPD = node.find('Amount')
        TerritoryCode = node.find('TerritoryCode')

        df_xml = df_xml.append(
            pd.Series([getValOfNode(IRC), getValOfNode(IRC2), getValOfNode(Artist), getValOfNode(Song), getValOfNode(Units), getValOfNode(PPD), getValOfNode(TerritoryCode)], index=dfcols), ignore_index=True)

    print(df_xml)


main()

And I get this error:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-5-1f816143f9e4> in <module>()
     23 
     24 
---> 25 main()

<ipython-input-5-1f816143f9e4> in main()
      8     df_xml = pd.DataFrame(columns = dfcols)
      9 
---> 10     for node in tree:
     11         IRC = node.find('IRC')
     12         IRC2 = node.find('ICPN')

TypeError: 'ElementTree' object is not iterable

There is also an issue with the territory code, when I run:

for info in root.iter('TerritoryCode'):
   print(info.text)

it prints the territories but, in order, because they are duplicates(I don't know how to explain), I really need all of them and not just one of each. If that makes sense. This is what I get:

AE
AR
AT
AU
AW
BE
BG
BO
BR
BY
CA
CH
CL
CN
CO
CR
CY
CZ
DE
DK
DO
DZ
EC
EE
EG
ES
FI
FR
GB
GL
GR
GT
HK
HN

This is what I need:

AD
AD
AE
AE
AE
AE
AE
AE,

and so forth.

Can anyone help me with this? Much appreciated.

Have a great day :)

Answer 1

As mentioned, your needed nodes are at different levels of the XML and hence path expressions will be different for each data item. Additionally you need to traverse between two repeating levels: SalesToRecordCompanyByTerritory and ReleaseTransactionsToRecordCompany.

Therefore, consider parsing in nested for loops. And rather than growing a data frame inside a loop, build a list of dictionaries that you can pass into pandas' DataFrame() constructor outside of the loop. With this approach, you migrate dictionary keys as columns and elements as data.

Below uses chained find() calls, long relative, or short absolute paths to navigate down the nested levels and retrieve corresponding element text values. Notice all parsing are relative to looped nodes with parent terr and child rls objects.

import xml.etree.ElementTree as ET
import pandas as pd

tree = ET.parse("file.xml")

data = []
for terr in tree.findall('.//SalesToRecordCompanyByTerritory'):

    for rls in terr.findall('.//ReleaseTransactionsToRecordCompany'):

        inner = {}

        # DESCENDANTS
        inner['IRC'] = rls.find('./ReleaseId/ISRC').text    
        inner['IRC2'] = rls.find('./ReleaseId/ICPN').text

        # CHILDREN
        inner['Artist'] = rls.find('WMGArtistName').text
        inner['Song'] = rls.find('WMGTitle').text

        # DESCENDANTS
        inner['Units'] = rls.find('./SalesTransactionToRecordCompany/SalesDataToRecordCompany/GrossNumberOfConsumerSales').text    
        inner['PPD'] = rls.find('Deal').find('AmountPayableInCurrencyOfAccounting').text

        # PARENT
        inner['TerritoryCode'] = terr.find('./TerritoryCode').text

        data.append(inner)

df = pd.DataFrame(data)

You can shorten the find() chains and long relative paths with absolute paths using .//:

inner['IRC'] = rls.find('.//ISRC').text    
inner['IRC2'] = rls.find('.//ICPN').text

inner['PPD'] = rls.find('.//AmountPayableInCurrencyOfAccounting').text
inner['Units'] = rls.find('.//GrossNumberOfConsumerSales').text