Flatten a nested xml with pandas-CodePudding

I have the follow xml in this format:

<?xml version='1.0' encoding='UTF-8'?>
<ettevotjad>
  <ettevotja>
    <nimi>000 Holdings OÜ</nimi>
    <ariregistri_kood>16372442</ariregistri_kood>
    <ettevotja_oiguslik_vorm>Osaühing</ettevotja_oiguslik_vorm>
    <ettevotja_oigusliku_vormi_alaliik/>
    <kmkr_nr/>
    <ettevotja_staatus>R</ettevotja_staatus>
    <ettevotja_staatus_tekstina>Registrisse kantud</ettevotja_staatus_tekstina>
    <ettevotja_esmakande_kpv>23.11.2021</ettevotja_esmakande_kpv>
    <ettevotja_aadress>
      <asukoht_ettevotja_aadressis/>
      <asukoha_ehak_kood/>
      <asukoha_ehak_tekstina></asukoha_ehak_tekstina>
      <indeks_ettevotja_aadressis/>
      <ads_adr_id></ads_adr_id>
      <ads_ads_oid></ads_ads_oid>
      <ads_normaliseeritud_taisaadress/>
    </ettevotja_aadress>
    <teabesysteemi_link>https://ariregister.rik.ee/est/company/16372442</teabesysteemi_link>
  </ettevotja>
  <ettevotja>
    <nimi>001 group OÜ</nimi>
    <ariregistri_kood>12754230</ariregistri_kood>
    <ettevotja_oiguslik_vorm>Osaühing</ettevotja_oiguslik_vorm>
    <ettevotja_oigusliku_vormi_alaliik/>
    <kmkr_nr/>
    <ettevotja_staatus>R</ettevotja_staatus>
    <ettevotja_staatus_tekstina>Registrisse kantud</ettevotja_staatus_tekstina>
    <ettevotja_esmakande_kpv>17.11.2014</ettevotja_esmakande_kpv>
    <ettevotja_aadress>
      <asukoht_ettevotja_aadressis>Õismäe tee 78-9</asukoht_ettevotja_aadressis>
      <asukoha_ehak_kood>0176</asukoha_ehak_kood>
      <asukoha_ehak_tekstina>Haabersti linnaosa, Tallinn, Harju maakond</asukoha_ehak_tekstina>
      <indeks_ettevotja_aadressis>13513</indeks_ettevotja_aadressis>
      <ads_adr_id>2182337</ads_adr_id>
      <ads_ads_oid></ads_ads_oid>
      <ads_normaliseeritud_taisaadress>Harju maakond, Tallinn, Haabersti linnaosa, Õismäe tee 78-9</ads_normaliseeritud_taisaadress>
    </ettevotja_aadress>
    <teabesysteemi_link>https://ariregister.rik.ee/est/company/12754230</teabesysteemi_link>
  </ettevotja>
</ettevotjad>

Using pandas' .read_xml() yields:

 import pandas as pd

 data = pd.read_xml('test_file.xml')

 print(data.head(2).to_string())
              nimi  ariregistri_kood ettevotja_oiguslik_vorm  ettevotja_oigusliku_vormi_alaliik kmkr_nr ettevotja_staatus ettevotja_staatus_tekstina ettevotja_esmakande_kpv  ettevotja_aadress                               teabesysteemi_link
0  000 Holdings OÜ          16372442                Osaühing                                NaN    None                 R         Registrisse kantud              23.11.2021                NaN  https://ariregister.rik.ee/est/company/16372442
1     001 group OÜ          12754230                Osaühing                                NaN    None                 R         Registrisse kantud              17.11.2014                NaN  https://ariregister.rik.ee/est/company/12754230

Notice in the dataframe 'ettevotja_aadress' is NaN, but in fact if you look at the xml structure, it's nested with those sub columns/headers. How do I expand out those nested columns into the dataframe?

I thought one way to do it was to simply read in the file, remove the <ettevotja_aadress> and <ettevotja_aadress> tags, then read into pandas, but it seems like there should be direct way to do this, similar to pandas' .json_normalize().

CodePudding user response：

So one work around is you can clarify a path to the node, in this case the ettevotja_aadress. Then can merge/concat to the original dataframe. If anyone has a solution to do this directly with the .read_xml(), please do share:

data = pd.read_xml(xml_data_source)
dataAddress = pd.read_xml(xml_data_source, xpath=".//ettevotja_aadress")
data = pd.concat([data, dataAddress], axis=1)
data = data.drop('ettevotja_aadress', axis=1)

Output:

print(data.head(2).to_string())
              nimi  ariregistri_kood ettevotja_oiguslik_vorm  ettevotja_oigusliku_vormi_alaliik kmkr_nr ettevotja_staatus ettevotja_staatus_tekstina ettevotja_esmakande_kpv  ettevotja_aadress                               teabesysteemi_link asukoht_ettevotja_aadressis  asukoha_ehak_kood                       asukoha_ehak_tekstina  indeks_ettevotja_aadressis  ads_adr_id  ads_ads_oid                              ads_normaliseeritud_taisaadress
0  000 Holdings OÜ          16372442                Osaühing                                NaN    None                 R         Registrisse kantud              23.11.2021                NaN  https://ariregister.rik.ee/est/company/16372442                        None                NaN                                        None                         NaN         NaN          NaN                                                         None
1     001 group OÜ          12754230                Osaühing                                NaN    None                 R         Registrisse kantud              17.11.2014                NaN  https://ariregister.rik.ee/est/company/12754230             Õismäe tee 78-9              176.0  Haabersti linnaosa, Tallinn, Harju maakond                     13513.0   2182337.0          NaN  Harju maakond, Tallinn, Haabersti linnaosa, Õismäe tee 78-9

CodePudding user response：

This could be done by providing an XSL stylesheet to flatten the original XML. Code will look like following:

xml = '''<?xml version='1.0' encoding='UTF-8'?>
<ettevotjad>
  <ettevotja>
    <nimi>000 Holdings OÜ</nimi>
    <ariregistri_kood>16372442</ariregistri_kood>
    <ettevotja_oiguslik_vorm>Osaühing</ettevotja_oiguslik_vorm>
    <ettevotja_oigusliku_vormi_alaliik/>
    <kmkr_nr/>
    <ettevotja_staatus>R</ettevotja_staatus>
    <ettevotja_staatus_tekstina>Registrisse kantud</ettevotja_staatus_tekstina>
    <ettevotja_esmakande_kpv>23.11.2021</ettevotja_esmakande_kpv>
    <ettevotja_aadress>
      <asukoht_ettevotja_aadressis/>
      <asukoha_ehak_kood/>
      <asukoha_ehak_tekstina></asukoha_ehak_tekstina>
      <indeks_ettevotja_aadressis/>
      <ads_adr_id></ads_adr_id>
      <ads_ads_oid></ads_ads_oid>
      <ads_normaliseeritud_taisaadress/>
    </ettevotja_aadress>
    <teabesysteemi_link>https://ariregister.rik.ee/est/company/16372442</teabesysteemi_link>
  </ettevotja>
  <ettevotja>
    <nimi>001 group OÜ</nimi>
    <ariregistri_kood>12754230</ariregistri_kood>
    <ettevotja_oiguslik_vorm>Osaühing</ettevotja_oiguslik_vorm>
    <ettevotja_oigusliku_vormi_alaliik/>
    <kmkr_nr/>
    <ettevotja_staatus>R</ettevotja_staatus>
    <ettevotja_staatus_tekstina>Registrisse kantud</ettevotja_staatus_tekstina>
    <ettevotja_esmakande_kpv>17.11.2014</ettevotja_esmakande_kpv>
    <ettevotja_aadress>
      <asukoht_ettevotja_aadressis>Õismäe tee 78-9</asukoht_ettevotja_aadressis>
      <asukoha_ehak_kood>0176</asukoha_ehak_kood>
      <asukoha_ehak_tekstina>Haabersti linnaosa, Tallinn, Harju maakond</asukoha_ehak_tekstina>
      <indeks_ettevotja_aadressis>13513</indeks_ettevotja_aadressis>
      <ads_adr_id>2182337</ads_adr_id>
      <ads_ads_oid></ads_ads_oid>
      <ads_normaliseeritud_taisaadress>Harju maakond, Tallinn, Haabersti linnaosa, Õismäe tee 78-9</ads_normaliseeritud_taisaadress>
    </ettevotja_aadress>
    <teabesysteemi_link>https://ariregister.rik.ee/est/company/12754230</teabesysteemi_link>
  </ettevotja>
</ettevotjad>
'''

import pandas as pd

stylesheet = '''<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes"/>
<xsl:template match="/">
    <ettevotjad>
        <xsl:apply-templates select="//ettevotja"/>
    </ettevotjad>
</xsl:template>
<xsl:template match="ettevotja">
    <ettevotja>
      <xsl:copy-of select="node()[not(self::ettevotja_aadress)]"/>
      <xsl:apply-templates select="./ettevotja_aadress"/>
    </ettevotja>
 </xsl:template>
 <xsl:template match="ettevotja_aadress">
    <xsl:copy-of select="node()"/>
  </xsl:template>
 </xsl:stylesheet>
'''

df = pd.read_xml(xml, xpath="//ettevotja", stylesheet = stylesheet)

df.head()

The idea behind the code is that XSL transformation is applied to the XML document, flattening its structure. The result of the transformation will be:

<?xml version="1.0" encoding="UTF-8"?>
<ettevotjad>
   <ettevotja>
      <nimi>000 Holdings OÜ</nimi>
      <ariregistri_kood>16372442</ariregistri_kood>
      <ettevotja_oiguslik_vorm>Osaühing</ettevotja_oiguslik_vorm>
      <ettevotja_oigusliku_vormi_alaliik/>
      <kmkr_nr/>
      <ettevotja_staatus>R</ettevotja_staatus>
      <ettevotja_staatus_tekstina>Registrisse kantud</ettevotja_staatus_tekstina>
      <ettevotja_esmakande_kpv>23.11.2021</ettevotja_esmakande_kpv>
      <teabesysteemi_link>https://ariregister.rik.ee/est/company/16372442</teabesysteemi_link>
  
      <asukoht_ettevotja_aadressis/>
      <asukoha_ehak_kood/>
      <asukoha_ehak_tekstina/>
      <indeks_ettevotja_aadressis/>
      <ads_adr_id/>
      <ads_ads_oid/>
      <ads_normaliseeritud_taisaadress/>
    </ettevotja>
   <ettevotja>
      <nimi>001 group OÜ</nimi>
      <ariregistri_kood>12754230</ariregistri_kood>
      <ettevotja_oiguslik_vorm>Osaühing</ettevotja_oiguslik_vorm>
      <ettevotja_oigusliku_vormi_alaliik/>
      <kmkr_nr/>
      <ettevotja_staatus>R</ettevotja_staatus>
      <ettevotja_staatus_tekstina>Registrisse kantud</ettevotja_staatus_tekstina>
      <ettevotja_esmakande_kpv>17.11.2014</ettevotja_esmakande_kpv>
      <teabesysteemi_link>https://ariregister.rik.ee/est/company/12754230</teabesysteemi_link>
  
      <asukoht_ettevotja_aadressis>Õismäe tee 78-9</asukoht_ettevotja_aadressis>
      <asukoha_ehak_kood>0176</asukoha_ehak_kood>
      <asukoha_ehak_tekstina>Haabersti linnaosa, Tallinn, Harju maakond</asukoha_ehak_tekstina>
      <indeks_ettevotja_aadressis>13513</indeks_ettevotja_aadressis>
      <ads_adr_id>2182337</ads_adr_id>
      <ads_ads_oid/>
      <ads_normaliseeritud_taisaadress>Harju maakond, Tallinn, Haabersti linnaosa, Õismäe tee 78-9</ads_normaliseeritud_taisaadress>
    </ettevotja>
</ettevotjad>

After the transformation an XPath //ettevotja is applied to the transformation result, taking children of ettevotja elements as dataframe row fields.