How to convert XML data as a pandas data frame?

Question

I'm trying to analysis XML file with python. I ned to get xml data as a pandas data frame.

import pandas as pd
import xml.etree.ElementTree as et
def parse_XML(xml_file, df_cols): 


 xtree = et.parse(xml_file)
 xroot = xtree.getroot()
 rows = []

 for node in xroot: 
    res = []
    res.append(node.attrib.get(df_cols[0]))
    for el in df_cols[1:]: 
        if node is not None and node.find(el) is not None:
            res.append(node.find(el).text)
        else: 
            res.append(None)
    rows.append({df_cols[i]: res[i] 
                 for i, _ in enumerate(df_cols)})

 out_df = pd.DataFrame(rows, columns=df_cols)
    
 return out_df

parse_XML('/Users/newuser/Desktop/TESTRATP/arrets.xml', ["Name","gml"])

But I'm getting below data frame.

    Name    gml

 0  None    None

 1  None    None

 2  None    None

My XML file is :

<?xml version="1.0" encoding="UTF-8"?>
<PublicationDelivery version="1.09:FR-NETEX_ARRET-2.1-1.0" xmlns="http://www.netex.org.uk/netex" xmlns:core="http://www.govtalk.gov.uk/core" xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:ifopt="http://www.ifopt.org.uk/ifopt" xmlns:siri="http://www.siri.org.uk/siri" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.netex.org.uk/netex">
    <PublicationTimestamp>2020-08-05T06:00:01+00:00</PublicationTimestamp>
    <ParticipantRef>transport.data.gouv.fr</ParticipantRef>
    <dataObjects>
        <GeneralFrame id="FR:GeneralFrame:NETEX_ARRET:" version="any">
            <members>
                <Quay id="FR:Quay:zenbus_StopPoint_SP_351400003_LOC:" version="any">
                    <Name>ST FELICIEN - Centre</Name>
                    <Centroid>
                        <Location>
                            <gml:pos srsName="EPSG:2154">828054.2068251468 6444393.512041969</gml:pos>
                        </Location>
                    </Centroid>
                    <TransportMode>bus</TransportMode>
                </Quay>
                <Quay id="FR:Quay:zenbus_StopPoint_SP_361350004_LOC:" version="any">
                    <Name>ST FELICIEN - Chemin de Juny</Name>
                    <Centroid>
                        <Location>
                            <gml:pos srsName="EPSG:2154">828747.3172982805 6445226.100290826</gml:pos>
                        </Location>
                    </Centroid>
                    <TransportMode>bus</TransportMode>
                </Quay>
                <Quay id="FR:Quay:zenbus_StopPoint_SP_343500005_LOC:" version="any">
                    <Name>ST FELICIEN - Darone</Name>
                    <Centroid>
                        <Location>
                            <gml:pos srsName="EPSG:2154">829036.2709757038 6444724.878001894</gml:pos>
                        </Location>
                    </Centroid>
                    <TransportMode>bus</TransportMode>
                </Quay>
                <Quay id="FR:Quay:zenbus_StopPoint_SP_359440004_LOC:" version="any">
                    <Name>ST FELICIEN - Col de Fontayes</Name>
                    <Centroid>
                        <Location>
                            <gml:pos srsName="EPSG:2154">829504.7993360173 6445490.57188837</gml:pos>
                        </Location>
                    </Centroid>
                    <TransportMode>bus</TransportMode>
                </Quay>
   </members>
        </GeneralFrame>
    </dataObjects>
</PublicationDelivery>
       

I gave you here little part of my xml file. I can't give you full XML file as it exceeding the character limits in stackoverflow. I'm wondering why I got above output and I don't know where the my error is. As I'm new to this please some one can help me? Thank you

Answer 1

Alternative solution using pandas-read-xml

pip install pandas-read-xml

import pandas_read_xml as pdx
from pandas_read_xml import fully_flatten

df = pdx.read_xml(xml, ['PublicationDelivery', 'dataObjects', 'GeneralFrame', 'members']).pipe(fully_flatten)

The list is just the tags that you want to navigate to as the "root". You many need to clean the column names afterwards.

Answer 2

My approach is avoid xml parsing and switch straight into pandas by using xmlplain to generate JSON from xml.

import xmlplain
with open("so_sample.xml") as f: js = xmlplain.xml_to_obj(f, strip_space=True, fold_dict=True)
df1 = pd.json_normalize(js).explode("PublicationDelivery.dataObjects.GeneralFrame.members")

# cleanup column names...
df1 = df1.rename(columns={c:c.replace("PublicationDelivery.", "").replace("dataObjects.GeneralFrame.","").strip() 
                          for c in df1.columns})
# drop spurious columns
df1 = df1.drop(columns=[c for c in df1.columns if c[0]=="@"])
# expand second level of dictionaries
df1 = pd.json_normalize(df1.to_dict(orient="records"))
# cleanup columns from second set of dictionaries
df1 = df1.rename(columns={c:c.replace("members.Quay.", "") for c in df1.columns})
# expand next list and dicts
df1 = pd.json_normalize(df1.explode("Centroid.Location.gml:pos").to_dict(orient="records"))
# there are some NaNs - dela with them
df1["Centroid.Location.gml:pos.@srsName"].fillna(method="ffill", inplace=True)
df1["Centroid.Location.gml:pos"].fillna(method="bfill", inplace=True)
# de-dup
df1 = df1.groupby("@id", as_index=False).first()

# more columns than requested... for SO output
print(df1.loc[:,["Name", "Centroid.Location.gml:pos.@srsName", "Centroid.Location.gml:pos"]].to_string(index=False))

output

                          Name Centroid.Location.gml:pos.@srsName            Centroid.Location.gml:pos
          ST FELICIEN - Darone                          EPSG:2154  829036.2709757038 6444724.878001894
          ST FELICIEN - Centre                          EPSG:2154  828054.2068251468 6444393.512041969
 ST FELICIEN - Col de Fontayes                          EPSG:2154   829504.7993360173 6445490.57188837
  ST FELICIEN - Chemin de Juny                          EPSG:2154  828747.3172982805 6445226.100290826