Parse nested XML into DataFrame

Question

I get back a response from an API in XML form. It looks like:

<result_set>
    <status> ...
    </status>
    <copyright> ...
    </copyright>
    <results>
       <congress> ...
       </congress>
       <chamber> ...
       </chamber>
       <num_results> ...
       </num_results>
       <offset> ...
       </offset>
       <members>
         <member>
           <id> ... </id>
           <name> ... </name>
           ...
           ...
           ...
         </member>
         <member> ... </member>
         ...
         ... 
         ...
       </members>
    </results>
</result_set>

I need the data under each tag into a pandas dataframe of the form:

id name ... ... ...
1  JOHN ... ... ...
2  DOE  ... ... ...

I have tried ElementTree, but I have failed in all attempts so far.

ElementTree is a good approach. Show us what you've tried so far even if it's not working then we can help — Chris
– Chris, Commented Oct 15, 2019 at 18:22

balderman · Accepted Answer · 2019-10-15 19:08:48Z

1

Below

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

xml = '''<r> 
         <members>
         <member>
           <id>1</id>
           <name>jack</name>
         </member>
         <member>
           <id>5</id>
           <name>dan</name>
         </member>

       </members>
       </r>'''

root = ET.fromstring(xml)
data = []
members = root.findall('.//member')
for member in members:
    data.append({c.tag:c.text for c in list(member)})
df = pd.DataFrame(data)
print(df.head())

output

  id  name
0  1  jack
1  5   dan

answered Oct 15, 2019 at 19:08

balderman

24k8 gold badges39 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

Parse nested XML into DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related