XML data to Pandas dataframe

Question

https://drive.google.com/open?id=1M66WaMkwfkDoFW41MSwuKG4ZHyuSxFrO Guys its the first time working with XML. I've read many posts but still cant handle my data. In the link there is a part of my data (3 entities (mensaje) induced in the file. original one is about 35.000 entities). From those data I need to create a pandas dataframe .

Each line of the dt should be refer to one <mensaje> The first column has to be <numerosolicitud>********</numerosolicitud> second column <codigocliente>**********</codigocliente> and then i need one column for each <cuestionario><pregunta cod=***. There are 98 i think "cod" all same on all "mensajes". I need those "cod" as headers and Text if contained as value.

I believe that it is a basic task but after several days reading tutorials and posts still I need help. Any advice is highly appreciated.

min · Accepted Answer · 2020-08-25 17:52:28Z

2

I've made a package for similar use case. It could work here too.

pip install pandas_read_xml

you can do something like

import pandas_read_xml as pdx

df = pdx.read_xml('filename.xml', ['data', 'mansaje'])

To flatten, you could

df = pdx.flatten(df)

or

df = pdx.fully_flatten(df)

answered Aug 25, 2020 at 17:52

min

2412 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Poulos Spyros · Accepted Answer · 2020-05-25 07:36:18Z

I found the solution for my problem. Probably someone could make something more efficient but this code worked for me.

import xml.etree.ElementTree as ET
import pandas as pd
tree = ET.parse(r"C:\path\of\your\file")
root = tree.getroot()
df = pd.Dataframe()
counter=1
for mensaje in root.iter('mensaje'):
    df.loc[counter, 'numerosolicitud'] =  mensaje.find(".//numerosolicitud").text if not None else None
    df.loc[counter, 'codigocliente'] =  mensaje.find(".//codigocliente").text if not None else None
    df.loc[counter, 'riesgocb'] =  mensaje.find(".//riesgocb").text    if not None else None
    nodes = mensaje.findall(".//pregunta")
    for child in nodes:
        df.loc[counter, str(child.attrib["cod"] )] =  str((child.text if not None else None))
    print(counter)
    counter+=1

df.to_excel("output.xlsx")

Collectives™ on Stack Overflow

XML data to Pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related