Home > front end >  Turning a .XML file to a data frame
Turning a .XML file to a data frame

Time:12-12

I have found a relevant data set about German politicians but I'm new to the format it comes in: XML with a belonging .DTD file. I'm used to working with data frames and I've tried different packages/libraries in R and python to convert it into a DF without any luck. Has anyone here worked with these formates before and can point me in the right direction? Thanks a million in advance!

The most promising solution I have found yet (in r) is:

# install.packages("xml2")
library(xml2)

x <- read_xml("MDB_STAMMDATEN.XML") # the xml file

xml_children(x)

It returns all of the variables divided into the correct sections, but I can't turn it into a working data frame...

Here is an extract from the data frame (below this is the data from the .DTD file):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE DOCUMENT SYSTEM "MDB_STAMMDATEN.DTD">
<!--Erstellt am: 04.11.2021 22:00:47--><DOCUMENT>
  <VERSION>1636087519</VERSION>
  <MDB>
    <ID>11000001</ID>
    <NAMEN>
      <NAME>
        <NACHNAME>Abelein</NACHNAME>
        <VORNAME>Manfred</VORNAME>
        <ORTSZUSATZ/>
        <ADEL/>
        <PRAEFIX/>
        <ANREDE_TITEL>Dr.</ANREDE_TITEL>
        <AKAD_TITEL>Prof. Dr.</AKAD_TITEL>
        <HISTORIE_VON>19.10.1965</HISTORIE_VON>
        <HISTORIE_BIS/>
      </NAME>
    </NAMEN>
    <BIOGRAFISCHE_ANGABEN>
      <GEBURTSDATUM>20.10.1930</GEBURTSDATUM>
      <GEBURTSORT>Stuttgart</GEBURTSORT>
      <GEBURTSLAND/>
      <STERBEDATUM>17.01.2008</STERBEDATUM>
      <GESCHLECHT>männlich</GESCHLECHT>
      <FAMILIENSTAND>keine Angaben</FAMILIENSTAND>
      <RELIGION>katholisch</RELIGION>
      <BERUF>Rechtsanwalt, Wirtschaftsprüfer, Universitätsprofessor</BERUF>
      <PARTEI_KURZ>CDU</PARTEI_KURZ>
      <VITA_KURZ/>
      <VEROEFFENTLICHUNGSPFLICHTIGES/>
    </BIOGRAFISCHE_ANGABEN>
    <WAHLPERIODEN>
      <WAHLPERIODE>
        <WP>5</WP>
        <MDBWP_VON>19.10.1965</MDBWP_VON>
        <MDBWP_BIS>19.10.1969</MDBWP_BIS>
        <WKR_NUMMER>174</WKR_NUMMER>
        <WKR_NAME/>
        <WKR_LAND>BWG</WKR_LAND>
        <LISTE/>
        <MANDATSART>Direktwahl</MANDATSART>
        <INSTITUTIONEN>
          <INSTITUTION>
            <INSART_LANG>Fraktion/Gruppe</INSART_LANG>
            <INS_LANG>Fraktion der Christlich Demokratischen Union/Christlich - Sozialen Union</INS_LANG>
            <MDBINS_VON/>
            <MDBINS_BIS/>
            <FKT_LANG/>
            <FKTINS_VON/>
            <FKTINS_BIS/>
          </INSTITUTION>
        </INSTITUTIONEN>
      </WAHLPERIODE>
      <WAHLPERIODE>
        <WP>6</WP>
        <MDBWP_VON>20.10.1969</MDBWP_VON>
        <MDBWP_BIS>22.09.1972</MDBWP_BIS>
        <WKR_NUMMER>174</WKR_NUMMER>
        <WKR_NAME/>
        <WKR_LAND>BWG</WKR_LAND>
        <LISTE/>
        <MANDATSART>Direktwahl</MANDATSART>
        <INSTITUTIONEN>
          <INSTITUTION>
            <INSART_LANG>Fraktion/Gruppe</INSART_LANG>
            <INS_LANG>Fraktion der Christlich Demokratischen Union/Christlich - Sozialen Union</INS_LANG>
            <MDBINS_VON/>
            <MDBINS_BIS/>
            <FKT_LANG/>
            <FKTINS_VON/>
            <FKTINS_BIS/>
          </INSTITUTION>
        </INSTITUTIONEN>
      </WAHLPERIODE>
      <WAHLPERIODE>
        <WP>7</WP>
        <MDBWP_VON>13.12.1972</MDBWP_VON>
        <MDBWP_BIS>13.12.1976</MDBWP_BIS>
        <WKR_NUMMER>174</WKR_NUMMER>
        <WKR_NAME/>
        <WKR_LAND>BWG</WKR_LAND>
        <LISTE/>
        <MANDATSART>Direktwahl</MANDATSART>
        <INSTITUTIONEN>
          <INSTITUTION>
            <INSART_LANG>Fraktion/Gruppe</INSART_LANG>
            <INS_LANG>Fraktion der Christlich Demokratischen Union/Christlich - Sozialen Union</INS_LANG>
            <MDBINS_VON/>
            <MDBINS_BIS/>
            <FKT_LANG/>
            <FKTINS_VON/>
            <FKTINS_BIS/>
          </INSTITUTION>
        </INSTITUTIONEN>
      </WAHLPERIODE>
      <WAHLPERIODE>
        <WP>8</WP>
        <MDBWP_VON>14.12.1976</MDBWP_VON>
        <MDBWP_BIS>04.11.1980</MDBWP_BIS>
        <WKR_NUMMER>174</WKR_NUMMER>
        <WKR_NAME/>
        <WKR_LAND>BWG</WKR_LAND>
        <LISTE/>
        <MANDATSART>Direktwahl</MANDATSART>
        <INSTITUTIONEN>
          <INSTITUTION>
            <INSART_LANG>Fraktion/Gruppe</INSART_LANG>
            <INS_LANG>Fraktion der Christlich Demokratischen Union/Christlich - Sozialen Union</INS_LANG>
            <MDBINS_VON/>
            <MDBINS_BIS/>
            <FKT_LANG/>
            <FKTINS_VON/>
            <FKTINS_BIS/>
          </INSTITUTION>
        </INSTITUTIONEN>
      </WAHLPERIODE>
      <WAHLPERIODE>
        <WP>9</WP>
        <MDBWP_VON>04.11.1980</MDBWP_VON>
        <MDBWP_BIS>29.03.1983</MDBWP_BIS>
        <WKR_NUMMER>174</WKR_NUMMER>
        <WKR_NAME/>
        <WKR_LAND>BWG</WKR_LAND>
        <LISTE/>
        <MANDATSART>Direktwahl</MANDATSART>
        <INSTITUTIONEN>
          <INSTITUTION>
            <INSART_LANG>Fraktion/Gruppe</INSART_LANG>
            <INS_LANG>Fraktion der Christlich Demokratischen Union/Christlich - Sozialen Union</INS_LANG>
            <MDBINS_VON/>
            <MDBINS_BIS/>
            <FKT_LANG/>
            <FKTINS_VON/>
            <FKTINS_BIS/>
          </INSTITUTION>
        </INSTITUTIONEN>
      </WAHLPERIODE>
      <WAHLPERIODE>
        <WP>10</WP>
        <MDBWP_VON>29.03.1983</MDBWP_VON>
        <MDBWP_BIS>18.02.1987</MDBWP_BIS>
        <WKR_NUMMER>174</WKR_NUMMER>
        <WKR_NAME/>
        <WKR_LAND>BWG</WKR_LAND>
        <LISTE/>
        <MANDATSART>Direktwahl</MANDATSART>
        <INSTITUTIONEN>
          <INSTITUTION>
            <INSART_LANG>Fraktion/Gruppe</INSART_LANG>
            <INS_LANG>Fraktion der Christlich Demokratischen Union/Christlich - Sozialen Union</INS_LANG>
            <MDBINS_VON/>
            <MDBINS_BIS/>
            <FKT_LANG/>
            <FKTINS_VON/>
            <FKTINS_BIS/>
          </INSTITUTION>
        </INSTITUTIONEN>
      </WAHLPERIODE>
      <WAHLPERIODE>
        <WP>11</WP>
        <MDBWP_VON>18.02.1987</MDBWP_VON>
        <MDBWP_BIS>20.12.1990</MDBWP_BIS>
        <WKR_NUMMER>174</WKR_NUMMER>
        <WKR_NAME/>
        <WKR_LAND>BWG</WKR_LAND>
        <LISTE/>
        <MANDATSART>Direktwahl</MANDATSART>
        <INSTITUTIONEN>
          <INSTITUTION>
            <INSART_LANG>Fraktion/Gruppe</INSART_LANG>
            <INS_LANG>Fraktion der Christlich Demokratischen Union/Christlich - Sozialen Union</INS_LANG>
            <MDBINS_VON>18.02.1987</MDBINS_VON>
            <MDBINS_BIS>20.12.1990</MDBINS_BIS>
            <FKT_LANG/>
            <FKTINS_VON/>
            <FKTINS_BIS/>
          </INSTITUTION>
        </INSTITUTIONEN>
      </WAHLPERIODE>
    </WAHLPERIODEN>
  </MDB>
</DOCUMENT>

Data from the .DTD file

<?xml version="1.0" encoding="UTF-8"?>
<!-- DTD für die Stammdaten der Abgeordneten des Deutschen Bundestages ab der 1. Wahlperiode -->
<!ELEMENT DOCUMENT (VERSION, MDB )>
    <!--DOCUMENT bestehend aus Dokumentenversion und Angaben zu Abgeordneten des Deutschen Bundestages
        Elemente, die mit einem   gekennzeichnet sind, können einmal oder mehrmals vorkommen.
    -->
<!ELEMENT VERSION (#PCDATA)>
    <!--Dokumentenversion
    -->
<!ELEMENT MDB (ID, NAMEN, BIOGRAFISCHE_ANGABEN, WAHLPERIODEN)>
    <!--Angaben zu Abgeordneten des Deutschen Bundestages
        -->
<!ELEMENT ID (#PCDATA)>
    <!--Identifikationsnummer des Abgeordneten
        Format: 8-stellig
    -->
<!ELEMENT NAMEN (NAME )>
    <!--Namensbestandteile zu Namen des Abgeordneten einschl. Namenshistorie
        Element kann einmal oder mehrmals vorkommen.
    -->
<!ELEMENT BIOGRAFISCHE_ANGABEN (GEBURTSDATUM?, GEBURTSORT?, GEBURTSLAND?, STERBEDATUM?, GESCHLECHT?, FAMILIENSTAND?, RELIGION?, BERUF?, PARTEI_KURZ?, VITA_KURZ?, VEROEFFENTLICHUNGSPFLICHTIGES?)>
    <!--Biografische Angaben des Abgeordneten
        Elemente, die mit einem ? gekennzeichnet sind, können keinmal oder genau einmal vorkommen.
        -->
<!ELEMENT WAHLPERIODEN (WAHLPERIODE )>
    <!--Angaben zur Wahlperiode 
        Element kann einmal oder mehrmals vorkommen.
    -->
<!ELEMENT NAME (NACHNAME, VORNAME, ORTSZUSATZ, ADEL, PRAEFIX, ANREDE_TITEL, AKAD_TITEL, HISTORIE_VON, HISTORIE_BIS)>
    <!--Namensbestandteile je Name des Abgeordneten einschl. Namenshistorie
    --> 
<!ELEMENT GEBURTSDATUM (#PCDATA)>
    <!--Geburtsdatum des Abgeordneten
        -->
<!ELEMENT GEBURTSORT (#PCDATA)>
    <!--Geburtsort des Abgeordneten
        -->
<!ELEMENT GEBURTSLAND (#PCDATA)>
    <!--Geburtsland des Abgeordneten
        -->
<!ELEMENT STERBEDATUM (#PCDATA)>
    <!--Sterbedatum des Abgeordneten
        -->
<!ELEMENT GESCHLECHT (#PCDATA)>
    <!--Geschlecht des Abgeordneten
        -->
<!ELEMENT FAMILIENSTAND (#PCDATA)>
    <!--Familienstand des Abgeordneten
        -->
<!ELEMENT RELIGION (#PCDATA)>
    <!--Religion des Abgeordneten
        -->
<!ELEMENT BERUF (#PCDATA)>
    <!--Beruf des Abgeordneten
        -->
<!ELEMENT PARTEI_KURZ (#PCDATA)>
    <!--Parteizugehörigkeit des Abgeordneten - Kurzform
        -->
<!ELEMENT VITA_KURZ (#PCDATA)>
    <!--Kurzbiografie des Abgeordneten (nur aktuelle Wahlperiode)
        -->
<!ELEMENT VEROEFFENTLICHUNGSPFLICHTIGES (#PCDATA)>
    <!--Veröffentlichungspflichtige Angaben des Abgeordneten (nur aktuelle Wahlperiode)
        Kategorien der Veröffentlichung
        1. Berufliche Tätigkeit vor der Mitgliedschaft im Deutschen Bundestag
          (§ 1 Abs. 1 Nr. 1 VR, Nr. 2 und 5 Ausführungsbestimmungen - AB)
        2. Entgeltliche Tätigkeiten neben dem Mandat
          (§ 1 Abs. 2 Nr. 1 VR, Nr. 3, 4 und 8 AB)
        3. Funktionen in Unternehmen
          (§ 1 Abs. 2 Nr. 2 VR, Nr. 3 AB)
        4. Funktionen in Körperschaften und Anstalten des öffentlichen Rechts
          (§ 1 Abs. 2 Nr. 3 VR, Nr. 3 AB)
        5. Funktionen in Vereinen, Verbänden und Stiftungen
          (§ 1 Abs. 2 Nr. 4 VR, Nr. 3 AB)
        6. Vereinbarungen über künftige Tätigkeiten oder Vermögensvorteile
          (§ 1 Abs. 2 Nr. 5 VR, Nr. 6 AB)
        7. Beteiligungen an Kapital- oder Personengesellschaften
          (§ 1 Abs. 2 Nr. 6 VR, Nr. 7 AB)
        8. Spenden
          (§ 4 VR, Nr. 10 AB)
        -->
<!ELEMENT WAHLPERIODE (WP, MDBWP_VON, MDBWP_BIS, WKR_NUMMER, WKR_NAME, WKR_LAND, LISTE, MANDATSART, INSTITUTIONEN)>
    <!--Angaben je Wahlperiode des Abgeordneten
        -->
<!ELEMENT NACHNAME (#PCDATA)>
    <!--Nachname des Abgeordneten
    -->
<!ELEMENT VORNAME (#PCDATA)>
    <!--VORNAME des Abgeordneten
    -->
<!ELEMENT ORTSZUSATZ (#PCDATA)>
    <!--Ortszusatz zu NACHNAME, zur Unterscheidung bei Namensgleichheit
        z.B. (Berlin)
    -->
<!ELEMENT ADEL (#PCDATA)>
    <!--Adelsprädikat (z.B. Freiherr, Baron u.ä.)
    -->
<!ELEMENT PRAEFIX (#PCDATA)>
    <!--Namenspräfix (z.B. von, van u.ä.)
    -->
<!ELEMENT ANREDE_TITEL (#PCDATA)>
    <!--Anrede-Titel des Abgeordneten (z.B. Dr., Prof. u.ä.)
        -->
<!ELEMENT AKAD_TITEL (#PCDATA)>
    <!--Akademischer Titel des Abgeordneten (z.B. Dr.-Ing., Prof. Dr. h. c. u.ä.)
    -->
<!ELEMENT HISTORIE_VON (#PCDATA)>
    <!--Historie zu den Namensbestandteilen des Abgeordneten - gültig von
        Format: TT.MM.JJJJ
        (ab Eintritt in den Bundestag oder ab Änderung der Namensbestandteile während des Mandates (z.B. durch Heirat))
    -->
<!ELEMENT HISTORIE_BIS (#PCDATA)>
    <!--Historie zu den Namensbestandteilen des Abgeordneten - gültig bis
        Format: TT.MM.JJJJ
        (bei Änderung der Namensbestandteile während des Mandates)
    -->
<!ELEMENT WP (#PCDATA)>
    <!--Nummer der Wahlperiode
        Format: 1 oder 2-stellig    
    -->
<!ELEMENT MDBWP_VON (#PCDATA)>
    <!--Beginn der Wahlperiodenzugehörigkeit des Abgeordneten
        Format: TT.MM.JJJJ
    -->
<!ELEMENT MDBWP_BIS (#PCDATA)>
    <!--Ende der Wahlperiodenzugehörigkeit des Abgeordneten
        Format: TT.MM.JJJJ
    -->
<!ELEMENT WKR_NUMMER (#PCDATA)>
    <!--Nummer des Wahlkreises, in dem der MDB kandidiert hat oder gewählt wurde.
        Format: 1 bis 3-stellig
    -->
<!ELEMENT WKR_NAME (#PCDATA)>
    <!--Wahlkreisname, in dem der MDB kandidiert hat oder gewählt wurde.
    -->
<!ELEMENT WKR_LAND (#PCDATA)>
    <!--Kurzbezeichnung des Bundeslandes, 
        in dem der Wahlkreis liegt, in dem der MDB kandidiert hat oder gewählt wurde.
    -->
<!ELEMENT LISTE (#PCDATA)>
    <!--Kurzbezeichnung der Liste, über die der MDB kandidiert hat oder gewählt wurde.
        Normalform: Bundeslandkürzel
        Ausnahmen: * Eingliederung Saarland, ** Berlin West Änderungsgesetz, *** von der Volkskammer gewählt
        Format: 1 bis 3-stellig
    -->
<!ELEMENT MANDATSART (#PCDATA)>
    <!--Art des Mandates (Direktmandat, Landesliste oder Volkskammer)
    -->
<!ELEMENT INSTITUTIONEN (INSTITUTION*)>
    <!--Angaben zu Institutionen (hier: nur Fraktion, außer aktuelle Wahlperiode)
        Element kann einmal oder mehrmals vorkommen.
    -->
<!ELEMENT INSTITUTION (INSART_LANG, INS_LANG, MDBINS_VON, MDBINS_BIS, FKT_LANG, FKTINS_VON, FKTINS_BIS)>
    <!--Angaben je Institution (hier: nur Fraktion, außer aktuelle Wahlperiode)
    -->
<!ELEMENT INSART_LANG (#PCDATA)>
    <!--Langbezeichnung der Institutionsart 
        (z.B. Fraktion, Ausschuss usw., hier: nur Fraktion, außer aktuelle Wahlperiode)
    -->
<!ELEMENT INS_LANG (#PCDATA)>
    <!--Langbezeichnung der Institution 
        (z.B. Fraktionsname, Ausschussname usw., hier: nur Fraktion, außer aktuelle Wahlperiode)
    -->
<!ELEMENT MDBINS_VON (#PCDATA)>
    <!--Beginn der Institutionszugehörigkeit des Abgeordneten
        Format: TT.MM.JJJJ
    -->
<!ELEMENT MDBINS_BIS (#PCDATA)>
    <!--Ende der Institutionszugehörigkeit des Abgeordneten
        Format: TT.MM.JJJJ
    -->
<!ELEMENT FKT_LANG (#PCDATA)>
    <!--Langbezeichnung der ausgeübten Funktion des Abgeordneten in einer Institution
        (z.B. Ordentliches Mitglied, Vorsitzender, Stellvertreter usw.)
    -->
<!ELEMENT FKTINS_VON (#PCDATA)>
    <!--Beginn der Funktionsausübung des Abgeordneten in einer Institution
        Format: TT.MM.JJJJ
    -->
<!ELEMENT FKTINS_BIS (#PCDATA)>
    <!--Ende der Funktionsausübung des Abgeordneten in einer Institution
        Format: TT.MM.JJJJ
    -->

CodePudding user response:

Okay, I found the solution myself using R (I know the code could be a little more neat):

library(tidyverse)
library(xml2)

x <- as_list(read_xml("/Users/WIBE/Downloads/MdB-Stammdaten-data/MDB_STAMMDATEN.XML"))


xml_df = tibble::as_tibble(x) %>%
  unnest_longer(DOCUMENT)

table(xml_df$DOCUMENT_id)


ID_wider = xml_df %>%
  dplyr::filter(DOCUMENT_id == "ID") %>%
  unnest_wider(DOCUMENT)

BIOGRAFISCHE_ANGABEN_wider = xml_df %>%
  dplyr::filter(DOCUMENT_id == "BIOGRAFISCHE_ANGABEN") %>%
  unnest_wider(DOCUMENT)

NAMEN_wider = xml_df %>%
  dplyr::filter(DOCUMENT_id == "NAMEN") %>%
  unnest_wider(DOCUMENT)

WAHLPERIODEN_wider = xml_df %>%
  dplyr::filter(DOCUMENT_id == "WAHLPERIODEN") %>%
  unnest_wider(DOCUMENT)


ID_df = ID_wider %>%
  # 1st time unnest to release the 2-dimension list?
  unnest(cols = names(.)) %>%
  # 2nd time to nest the single list in each cell?
  unnest(cols = names(.)) %>%
  # convert data type
  readr::type_convert()

BIOGRAFISCHE_ANGABEN_df = BIOGRAFISCHE_ANGABEN_wider %>%
  # 1st time unnest to release the 2-dimension list?
  unnest(cols = names(.)) %>%
  # 2nd time to nest the single list in each cell?
  unnest(cols = names(.)) %>%
  # convert data type
  readr::type_convert()

NAMEN_df = NAMEN_wider %>%
  # 1st time unnest to release the 2-dimension list?
  unnest(cols = names(.)) %>%
  # 2nd time to nest the single list in each cell?
  unnest(cols = names(.)) %>%
  # convert data type
  readr::type_convert()

WAHLPERIODEN_df = WAHLPERIODEN_wider %>%
  # 1st time unnest to release the 2-dimension list?
  unnest(cols = names(.)) %>%
  # 2nd time to nest the single list in each cell?
  unnest(cols = names(.)) %>%
  # convert data type
  readr::type_convert()

combined_df <- cbind(ID_df, BIOGRAFISCHE_ANGABEN_df, NAMEN_df, WAHLPERIODEN_df)

CodePudding user response:

Consider XSLT, the special-purpose language designed to transform XML files, in order to flatten your nested XML and migrate into the two-dimensions of an R data frame or Pandas DataFrame:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" omit-xml-declaration="no" indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/*">
     <DATA>
       <xsl:apply-templates select="descendant::WAHLPERIODE"/>
     </DATA>
    </xsl:template>
    
    <xsl:template match="WAHLPERIODE">
     <ROW>
       <xsl:copy-of select="ancestor::MDB/ID"/>
       <xsl:copy-of select="ancestor::MDB/NAMEN/NAME/*"/>
       <xsl:copy-of select="ancestor::MDB/BIOGRAFISCHE_ANGABEN/*"/>
       <xsl:copy-of select="*[name()!='INSTITUTIONEN']"/>
       <xsl:copy-of select="INSTITUTIONEN/INSTITUTION/*"/>
    </ROW>
    </xsl:template>
</xsl:stylesheet>

Online Demo

R (using xslt to run transformation and xml2 to parse)

library(xml2)
library(xslt)

# LOAD XML AND XSLT
doc <- read_xml("Input.xml")
style <- read_xml("Style.xsl", package = "xslt")

# RUN TRANSFORMATION AND SEE OUTPUT
flat_xml <- xml_xslt(doc, style)

# RETRIEVE data NODES
recs <- xml2::xml_find_all(flat_xml, "//ROW")

# BIND EACH CHILD TEXT AND NAME
df_list <- lapply(recs, function(r) {
  vals <- xml2::xml_children(r)
  
  df <- setNames(
    c(xml2::xml_text(vals)), 
    c(xml2::xml_name(vals))
  ) |> rbind() |> data.frame()
})

# COMBINE ALL DFS
final_df <- do.call(rbind.data.frame, df_list)

R (using Unix's command line xsltproc to run transformation and XML to parse)

library(XML)

system(paste(
  'cd /path/to/xml_and_xsl/files',
  'xsltproc -o Output.xml Style.xsl Input.xml', 
  sep=' && ')
)

final_df2 <- xmlToDataFrame('Output.xml')

Python (using lxml under the hood to run transformation)

import pandas as pd

doc = "Input.xml"
xsl = "Style.xsl"

final_df = pd.read_xml(doc, stylesheet = xsl)

Python (using Unix's command line xsltproc to run transformation)

from subprocess import Popen
import pandas as pd

cmds = ['xsltproc', '-o', 'Output.xml', 'Style.xsl', 'Input.xml']
result = Popen(cmds, cwd="/path/to/xml_and_xsl/files")

final_df2 = pd.read_xml("Output.xml")
  • Related