Flatten XML structure to pandas dataframe-CodePudding

I am having trouble extracting data from an xml file into a pandas data frame. The xml-file in question has the following structure:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Beleg_Exchange>

  <Beleg_List>
    <Beleg_Rec>
      <ID>16092</ID>
      <Adresse_ID>3127</Adresse_ID>
      <Belegzeile_List>
        <Belegzeile_Rec>
          <ID>59122</ID>
          <Zeilennummer>1</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>Importabwicklung</Belegzeilentext>
          <Preis>100</Preis>
        </Belegzeile_Rec>
        <Belegzeile_Rec>
          <ID>59123</ID>
          <Zeilennummer>2</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>Exportabwicklung</Belegzeilentext>
          <Preis>200</Preis>
        </Belegzeile_Rec>
      </Belegzeile_List>
    </Beleg_Rec>

    <Beleg_Rec>
      <ID>16093</ID>
      <Adresse_ID>3128</Adresse_ID>
      <Belegzeile_List>
        <Belegzeile_Rec>
          <ID>59125</ID>
          <Zeilennummer>1</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>Importabwicklung</Belegzeilentext>
          <Preis>100</Preis>
        </Belegzeile_Rec>
        <Belegzeile_Rec>
          <ID>59126</ID>
          <Zeilennummer>2</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>Exportabwicklung</Belegzeilentext>
          <Preis>200</Preis>
        </Belegzeile_Rec>
      </Belegzeile_List>
    </Beleg_Rec>
  </Beleg_List>
</Beleg_Exchange>

I would like to import it into pandas either as a flat table as follows, duplicating the entries under <Beleg_Rec> for every <Belegzeile_Rec>.

    ID      Adresse_ID  ID_inner    Zeilennummer    Menge   Einheit Belegzeilentext     Preis
0   16092   3127        59122       1               1       Stk     Importabwicklung    100
1   16092   3127        59123       2               1       Stk     Exportabwicklung    200
2   16093   3128        59125       1               1       Stk     Importabwicklung    100
3   16093   3128        59126       2               1       Stk     Exportabwicklung    200

Alternatively two dataframes which can be merged would also work well. For that I would need the ID of <Beleg_Rec> somehow connected to each line item.

I can read each portion using pandas.read_xml with the specific xpath, but I cannot get a way to connect the two:

df_outer = pd.read_xml(f, xpath="//Beleg_Exchange/Beleg_List/Beleg_Rec")

      ID  Adresse_ID  Belegzeile_List
0  16092        3127              NaN
1  16093        3128              NaN

df_inner = pd.read_xml(f, xpath="//Beleg_Exchange/Beleg_List/Beleg_Rec/Belegzeile_List/Belegzeile_Rec")

      ID  Zeilennummer  Menge Einheit   Belegzeilentext  Preis
0  59122             1      1     Stk  Importabwicklung    100
1  59123             2      1     Stk  Exportabwicklung    200
2  59125             1      1     Stk  Importabwicklung    100
3  59126             2      1     Stk  Exportabwicklung    200

The way forward I see would be to use xml2dict and then walk through the dictionary, appending the to dataframe. Is there a more efficient way I am not aware of?

CodePudding user response：

Try the below

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


xml = '''<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Beleg_Exchange>

  <Beleg_List>
    <Beleg_Rec>
      <ID>16092</ID>
      <Adresse_ID>3127</Adresse_ID>
      <Belegzeile_List>
        <Belegzeile_Rec>
          <ID>59122</ID>
          <Zeilennummer>1</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>rtabwicklung1</Belegzeilentext>
          <Preis>100</Preis>
        </Belegzeile_Rec>
        <Belegzeile_Rec>
          <ID>59123</ID>
          <Zeilennummer>2</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>abwicklung2</Belegzeilentext>
          <Preis>200</Preis>
        </Belegzeile_Rec>
      </Belegzeile_List>
    </Beleg_Rec>

    <Beleg_Rec>
      <ID>16093</ID>
      <Adresse_ID>3128</Adresse_ID>
      <Belegzeile_List>
        <Belegzeile_Rec>
          <ID>59125</ID>
          <Zeilennummer>1</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>bwicklung3</Belegzeilentext>
          <Preis>101</Preis>
        </Belegzeile_Rec>
        <Belegzeile_Rec>
          <ID>59126</ID>
          <Zeilennummer>2</Zeilennummer>
          <Menge>1</Menge>
          <Einheit>Stk</Einheit>
          <Belegzeilentext>abwicklung4</Belegzeilentext>
          <Preis>201</Preis>
        </Belegzeile_Rec>
      </Belegzeile_List>
    </Beleg_Rec>
  </Beleg_List>
</Beleg_Exchange>'''

data = []
root = ET.fromstring(xml)
records = root.findall('.//Beleg_Rec')
for record in records:
  temp = {c.tag:c.text for c in record if not len(c)}
  sub_records = record.findall('.//Belegzeile_Rec')
  for sub in sub_records:
    entry = {c.tag if c.tag != 'ID' else 'ID_inner':c.text for c in sub}
    entry.update(temp)
    data.append(entry)
df = pd.DataFrame(data)
print(df)

output

  ID_inner Zeilennummer Menge Einheit Belegzeilentext Preis     ID Adresse_ID
0    59122            1     1     Stk   rtabwicklung1   100  16092       3127
1    59123            2     1     Stk     abwicklung2   200  16092       3127
2    59125            1     1     Stk      bwicklung3   101  16093       3128
3    59126            2     1     Stk     abwicklung4   201  16093       3128