Create dataframe of certain XML element's text python pandas

Question

I am trying to create a dataframe out the XML code as shown below

<Structure>
    <Field>
        <Field_Name>GAMEREF</Field_Name>
        <Field_Type>Numeric</Field_Type>
        <Field_Len>4</Field_Len>
        <Field_Dec>0</Field_Dec>
    </Field>
    ...
    <Field>
        <Field_Name>WINLOSS</Field_Name>
        <Field_Type>Character</Field_Type>
        <Field_Len>1</Field_Len>
        <Field_Dec>0</Field_Dec>
    </Field>
</Structure>

<Records>
    <Record>
        <GAMEREF>1217</GAMEREF>
        <YEAR>2021</YEAR>
        (MORE ELEMENTS I DO NOT CARE ABOUT)
        <GAMENO>1</GAMENO>
        <WINLOSS>W</WINLOSS>
    </Record>
    ...
    <Record>
        <GAMEREF>1220</GAMEREF>
        <YEAR>2021</YEAR>
        (MORE ELEMENTS I DO NOT CARE ABOUT)
        <GAMENO>4</GAMENO>
        <WINLOSS>L</WINLOSS>
    </Record>
</Records>

The structure section of the XML code that is irrelevant to the dataframe I am trying to create.

I am trying to only use the XML elements of GAMEREF, YEAR, GAMENO, and WINLOSS as there are more in the XML for the Record elements.

I have tried using code as shown below to get this to work, but when I run the code I get the error of "AttributeError: 'NoneType' object has no attribute 'text'"

Code is below.

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

xtree = et.parse("gamedata.xml")
xroot = xtree.getroot()

df_cols = ["GAME REF","YEAR", "GAME NO", "WIN LOSS"]
rows = []

for child in xroot.iter():
     s_gameref = child.find('GAMEREF').text,
     s_year = child.find('YEAR').text,
     s_game_no = child.find('GAMENO').text,
     s_winloss = child.find('WINLOSS').text

     rows.append({"GAME REF": s_gameref,"YEAR": s_year, 
             "GAME NO": s_game_no, "WIN LOSS": s_winloss})

df = pd.DataFrame(rows, columns = df_cols)

The code is based off other stuff I have seen on the Stack and other sites, but nothing is working yet.

Ideal dataframe output is below

GAME REF	YEAR	GAME NO	WIN LOSS
1217	2021	1	W
1218	2021	2	W
1219	2021	3	L
1220	2021	4	L

Thanks

EDIT - NOT SURE WHAT IS GOING ON WITH MY TABLE, BUT IT SHOULD LOOK LIKE THIS

balderman · Accepted Answer · 2021-10-08 07:09:17Z

2

I think the below is what you are looking for. (Just loop over the "interesting" sub elements of Record). The logic of the code is in the line that starts with data = [.... The 2 loops can be found there.

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

xml = '''<r><Structure>
    <Field>
        <Field_Name>GAMEREF</Field_Name>
        <Field_Type>Numeric</Field_Type>
        <Field_Len>4</Field_Len>
        <Field_Dec>0</Field_Dec>
    </Field>
    ...
    <Field>
        <Field_Name>WINLOSS</Field_Name>
        <Field_Type>Character</Field_Type>
        <Field_Len>1</Field_Len>
        <Field_Dec>0</Field_Dec>
    </Field>
</Structure>

<Records>
    <Record>
        <GAMEREF>1217</GAMEREF>
        <YEAR>2021</YEAR>
        <GAMENO>1</GAMENO>
        <WINLOSS>W</WINLOSS>
    </Record>
    <Record>
        <GAMEREF>1220</GAMEREF>
        <YEAR>2021</YEAR>
        <GAMENO>4</GAMENO>
        <WINLOSS>L</WINLOSS>
    </Record>
</Records></r>'''
fields = {'GAMEREF':'GAME REF', 'YEAR':'YEAR', 'GAMENO':'GAME NO','WINLOSS':'WIN LOSS'}
root = ET.fromstring(xml)
data = [{display_name: rec.find(element_name).text for element_name,display_name in fields.items()} for rec in root.findall('.//Record')]
df = pd.DataFrame(data)
print(df)

output

  GAME REF  YEAR GAME NO WIN LOSS
0     1217  2021       1        W
1     1220  2021       4        L

edited Oct 8, 2021 at 7:09

answered Oct 8, 2021 at 6:56

balderman

24k8 gold badges39 silver badges60 bronze badges

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

3 Comments

DomCobb Over a year ago

Thanks for getting back to me. Is there a way to do this without directly adding the XML to the code? (first time working with XML). The root = et.fromstring(xml) portion is what I think is causing my question since I have been using xtree = et.parse("gamedata.xml") xroot = xtree.getroot().

balderman Over a year ago

Just replace fromstring with parse(filename) and it will work.

DomCobb Over a year ago

Thank you for the help!

User · Accepted Answer · 2021-10-08 06:51:12Z

0

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

xtree = et.parse("gamedata.xml")
xroot = xtree.getroot()

df_cols = ["GAME REF","YEAR", "GAME NO", "WIN LOSS"]
rows = []

for record in xroot:
     s_gameref = record.find('GAMEREF').text
     s_year = record.find('YEAR').text
     s_game_no = record.find('GAMENO').text
     s_winloss = record.find('WINLOSS').text

     rows.append({"GAME REF": s_gameref,"YEAR": s_year,
             "GAME NO": s_game_no, "WIN LOSS": s_winloss})

df = pd.DataFrame(rows, columns = df_cols)

Remove .iter()

answered Oct 8, 2021 at 6:51

User

8361 gold badge16 silver badges33 bronze badges

1 Comment

DomCobb Over a year ago

I still get the error of "AttributeError: 'NoneType' object has no attribute 'text'" with that code.

Collectives™ on Stack Overflow

Create dataframe of certain XML element's text python pandas

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related