Converting a XML-file with data saved as vectors to a pandas dataframe

Question

I have been trying to convert a nested XML-file to a dataframe in Python using Pandas and xml.etree.ElementTree.

The XML-looks like this:


    
        
            
                 3; 5; 1; 2; 6; 9; 8  
            
        
        
            
                 5; 0; 12; 8; 4; 5; 7
            
        
        
            
                 4.54; 5.71; nan; 7.12; 8.75; 2.99; 4.94

My main problem is extracting the data in this format where it is seperated by a semicolon. I have looked all over stackoverflow without finding any similar posts.

Using

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

tree = et.parse("Hospital.xml")
root = tree.getroot()

for child in root.iter():
    print(child.tag, child.attrib)

You can access the informations stored as tags and attributes, but I am not sure how to access the values stored in the Data-field.

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

tree = et.parse("Hospital.xml")
root = tree.getroot()

df_cols = ["numOperated", "antibioticsUsed", "numHospilaized"]
rows = []

for node in root:
    numOperated = node.attrib.get("numOperated")
    antibioticsUsed = node.attrib.get("antibioticsUsed")
    numHospilaized = node.attrib.get("numHospilaized")

    rows.append({"numOperated": numOperated, "antibioticsUsed " : antibioticsUsed,
    "numHospilaized ": numHospilaized  })
df = pd.DataFrame(rows,colums = df_cols)
print(df)

Is a solution I have tried, but this only prints None and Nan as the only values. I have also tried to add the directory of the XML-tree in the code above, but I still end up with None values.

The table I ultimatly want in a pandas dataframe converted from the XML-file is:

	numOperated	antibioticsUsed	numHospilaized
1	5	4.54	3
2	0	5.71	5
3	12	nan	1
4	8	7.12	2
5	4	8.75	6
6	5	2.99	9
7	7	4.94	8

Do anybody know how you can solve this problem? I really appreciate any help!

user_7175713 · Answer

The key is to split the text -> this converts it into a list, that Pandas can internally convert to a Series/Column.

data = """
     ...:     
     ...:         
     ...:             
     ...:                  3; 5; 1; 2; 6; 9; 8  
     ...:             
     ...:         
     ...:         
     ...:             
     ...:                  5; 0; 12; 8; 4; 5; 7
     ...:             
     ...:         
     ...:         
     ...:             
     ...:                  4.54; 5.71; nan; 7.12; 8.75; 2.99; 4.94
     ...:             
     ...:         
     ...:     
     ...: """

import xml.etree.ElementTree as ET
root = ET.fromstring(data)

Collect data into a dictionary:

collection = {}
for entry in root.findall(".//UnitVector"):
    key = entry.attrib['name']
    values = entry.find(".Data").text.split(";")
    collection[key] = values

Create the dataframe:

pd.DataFrame(collection)
 
  numHospitalized numOperated antibioticsUsed
0               3           5            4.54
1               5           0            5.71
2               1          12             nan
3               2           8            7.12
4               6           4            8.75
5               9           5            2.99
6               8           7            4.94

Your data is going to be strings -> you can convert to int/float with astype

Converting a XML-file with data saved as vectors to a pandas dataframe

1 Answers