Best way to parse a XML and covert it into dataframe

Question

I have the following XML (it's a sample):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<HDR_DONNEES xmlns="http://ERABLE_HDR.com/ns1">
    <Dates>
        <Date valeur="14032019">
            <Depart ACR_DepartHTA="BDX" ACR_PosteSource="BDX" GdoDepart="V.LOTC0018" Nom_DepartHTA="BOURLANG" PS_DepartHTA="V.LOT" NomPosteSource="VILLELOT">
                <M H="1150" UTM="20850" ITM="94" IFg="0" UNB="1" INB="1"/>
            </Depart>
            <Depart ACR_DepartHTA="BDX" ACR_PosteSource="BDX" GdoDepart="V.LOTC0005" Nom_DepartHTA="MARCHE G" PS_DepartHTA="V.LOT" NomPosteSource="VILLELOT">
                <M H="1150" UTM="20850" ITM="41" IFg="0" UNB="1" INB="1"/>
            </Depart>
            <Depart ACR_DepartHTA="NTS" ACR_PosteSource="NTS" GdoDepart="PALLUC2703" Nom_DepartHTA="FROIDFON" PS_DepartHTA="PALLU" NomPosteSource="PALLUAU">
                <M H="1140" UTM="0" ITM="0" IFg="100" UNB="0" INB="1"/>
            </Depart>
        </Date>
    </Dates>
</HDR_DONNEES>

how could I parse this XML into a dataframe in order to have this structure?

|-- acrDeparthta: string (nullable = true)

|-- acrPostesource: string (nullable = true)

|-- gdodepart: string (nullable = true)

|-- nomDeparthta: string (nullable = true)

|-- psDeparthta: string (nullable = true)

|-- nompostesource: string (nullable = true)

|-- creationDate: string (nullable = true)

|-- m: array (nullable = true)

| |-- element: struct (containsNull = true)

| | |-- h: string (nullable = true)

| | |-- utm: string (nullable = true)

| | |-- ufg: string (nullable = true)

| | |-- itm: string (nullable = true)

| | |-- ifg: string (nullable = true)

| | |-- unb: string (nullable = true)

| | |-- inb: string (nullable = true)

Where any attribute below "M" is part of the "M" array.

Any help would be appreciated, thanks !

Edit :

I tried this :

import xml.etree.ElementTree as ET
tree = ET.parse('testtest.xml')
root = tree.getroot()

for child in root:
    print child.tag, child.attrib

but all I get is : {http://ERABLE_HDR.com/ns1}Dates {}

and if i go deeper in the same loop reusing it

for child in child:
    print child.tag, child.attrib

I get this : {http://ERABLE_HDR.com/ns1}Date {'valeur': '14032019'}

and it goes on and on..

Possible duplicate of [How to convert an XML file to nice pandas dataframe?](https://stackoverflow.com/questions/28259301/how-to-convert-an-xml-file-to-nice-pandas-dataframe) — G. Anderson, Apr 23 '19 at 17:32

score 1 · Accepted Answer · answered Apr 24 '19 at 06:47

I'd suggest to use BeautifulSoup with the lxml reader (if I understood your request correctly):

from bs4 import BeautifulSoup
import pandas as pd

xml=b"""\
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<HDR_DONNEES xmlns="http://ERABLE_HDR.com/ns1">
    <Dates>
        <Date valeur="14032019">
            <Depart ACR_DepartHTA="BDX" ACR_PosteSource="BDX" GdoDepart="V.LOTC0018" Nom_DepartHTA="BOURLANG" PS_DepartHTA="V.LOT" NomPosteSource="VILLELOT">
                <M H="1150" UTM="20850" ITM="94" IFg="0" UNB="1" INB="1"/>
            </Depart>
            <Depart ACR_DepartHTA="BDX" ACR_PosteSource="BDX" GdoDepart="V.LOTC0005" Nom_DepartHTA="MARCHE G" PS_DepartHTA="V.LOT" NomPosteSource="VILLELOT">
                <M H="1150" UTM="20850" ITM="41" IFg="0" UNB="1" INB="1"/>
            </Depart>
            <Depart ACR_DepartHTA="NTS" ACR_PosteSource="NTS" GdoDepart="PALLUC2703" Nom_DepartHTA="FROIDFON" PS_DepartHTA="PALLU" NomPosteSource="PALLUAU">
                <M H="1140" UTM="0" ITM="0" IFg="100" UNB="0" INB="1"/>
            </Depart>
        </Date>
    </Dates>
</HDR_DONNEES>"""


soup = BeautifulSoup(xml,features="lxml")

data={}
for i,depart in enumerate(soup.find_all('depart')):
    data[i]=depart.attrs
    for m in depart.findChildren():
        data[i]['m']=list(m.attrs.values())

df=pd.DataFrame.from_dict(data, orient='index')
print(df)

which returns:

  acr_departhta acr_postesource   gdodepart nom_departhta ps_departhta nompostesource                           m
0           BDX             BDX  V.LOTC0018      BOURLANG        V.LOT       VILLELOT  [1150, 20850, 94, 0, 1, 1]
1           BDX             BDX  V.LOTC0005      MARCHE G        V.LOT       VILLELOT  [1150, 20850, 41, 0, 1, 1]
2           NTS             NTS  PALLUC2703      FROIDFON        PALLU        PALLUAU     [1140, 0, 0, 100, 0, 1]

Best way to parse a XML and covert it into dataframe

1 Answers1