python xml file to pandas dataframe

Question

I have an xml file in the following format:

<table name="ecat">

  <tuple>
    <atom name="TaxAccNo1">test</atom>
    <atom name="TaxAccNo2">00005504</atom>
    <atom name="TaxAccNo3">1</atom>
    <atom name="irn">100107</atom>
  </tuple>

  <tuple>
    <atom name="TaxAccNo1">test</atom>
    <atom name="TaxAccNo2">00005505</atom>
    <atom name="TaxAccNo3">2</atom>
    <atom name="irn">100108</atom>
  </tuple>

  <tuple>
    <atom name="TaxAccNo1">test</atom>
    <atom name="TaxAccNo2">00005506</atom>
    <atom name="TaxAccNo3">3</atom>
    <atom name="irn">100109</atom>
  </tuple>

</table>

From this, I want to be able to generate a pandas dataframe that would look like this:

    TaxAccNo1   TaxAccNo2   TaxAccNo3   irn 
    test        00005504    1           100107
    test        00005505    2           100108
    test        00005505    3           100109

How would I do this?

check this might help : stackoverflow.com/questions/28259301/… — anky
– anky, Commented Jan 31, 2019 at 5:53
I surprised there's no read_xml (the read_html isn't as feature rich as i remember) — Andy Hayden
– Andy Hayden, Commented Jan 31, 2019 at 6:10

Andy Hayden · Accepted Answer · 2019-01-31 06:10:30Z

3

You can use python's xml and a list/dict comprehension:

In [11]: import xml

In [12]: e = xml.etree.ElementTree.parse(open("foo.xml"))

In [13]: pd.DataFrame([{a.get('name'): a.text for a in t} for t in e.findall("tuple")])
Out[13]:
  TaxAccNo1 TaxAccNo2 TaxAccNo3     irn
0      test  00005504         1  100107
1      test  00005505         2  100108
2      test  00005506         3  100109

answered Jan 31, 2019 at 6:10

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

Collectives™ on Stack Overflow

python xml file to pandas dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related