Extracting subset of data from multilevel XML when variables have the same name

Question

I have a large amount of xml data which looks like this (only a fraction of data shown):

I want to extract the environmental data and place it in a pandas dataframe. I can do this using the following method:

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

tree = et.parse(data.xml) #load in the data
root = tree.getroot() # get the element tree root

celsius = []
for x in root.iter('temperature'):
    value = x.attrib.get('value')
    celsius.append(value)

tempdf = pd.DataFrame(celsius,columns=['Temperature (C)'])        
tempdf

This gives me the following dataframe with 114 columns:

I can then repeat this for all the other interesting variables, and use pd.concat to join them together. The problem is that there are two 'time' variables for each of the 114 blocks of data, as 'precipitation' has a separate timestamp. When I try to parse the time data like so:

time = []
for x in root.iter('time'):
    value = x.attrib.get('to')
    time.append(value)

timedf = pd.DataFrame(time,columns=['Date & Time'])        
timedf

This gives double the amount of rows of data (228 instead of 114):

I can't join the time dataframe together with the others as there are double the amount of time rows than other variables. I would like to only select the first time variable from each of the 114 instances i.e. I want to keep time datatype="forecast" from="2020-07-06T15:00:00Z" to="2020-07-06T15:00:00Z" and skip the second one which is for precipitation time datatype="forecast" from="2020-07-06T14:00:00Z" to="2020-07-06T15:00:00Z". I have tried:

time = []
for x in root.iter('time')[0]:
    value = x.attrib.get('to')
    time.append(value)

But this doesn't work and I am not sure how I can do this when the variable names are identical within each hour of data. I would greatly appreciate any help with this.

user_1422451 · Answer

Consider separately building a temperature data frame and precipitation data frame with concat and then merge concatenated versions together by common values in time and location nodes. And consider binding all attribute values together using list/dict comprehension.

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

tree = et.parse('Input.xml')     # load in the data
root = tree.getroot()            # get the element tree root

temp_list = []; precip_list = []

for n, x in enumerate(root.iter('time')):
    # GET LIST OF DICTIONARIES OF ALL ATTRIBUTES
    x_list = [{i.tag+'_'+k:v for k,v in i.attrib.items()} for i in x.iter('*')] 

    # COMBINE INTO SINGLE DICTIONARY    
    x_dict = {k:v for d in x_list for k,v in d.items()}

    # BUILD DATA FRAME
    df = pd.DataFrame(x_dict, index=[0])
    
    # SEPARATELY SAVE TO LIST OF DATA FRAMES
    if 'temperature_unit' in df.columns: temp_list.append(df)
    if 'precipitation_unit' in df.columns: precip_list.append(df)
    

# MERGE CONCATENATED SETS BY COMMON VARS
df = pd.merge(pd.concat(temp_list),
              pd.concat(precip_list),
              on=['time_to', 'time_datatype',
                  'location_altitude', 'location_latitude',
                  'location_longitude'],
              suffixes=['_t','_p'])

Extracting subset of data from multilevel XML when variables have the same name

1 Answers