Parse XML data into data frame

Question

I want to extract the data in XML into dictionary and then pass that into the list to get it in the form of data frame.

Below is the code for the same:

import pandas as pd
import requests,sys
from xml.etree import ElementTree

response = requests.get("http://mapiuat.vwbeatroute.com/v1/customer/index?key=aQeZx9WpppnbyJmar2ry4Ah0_2WdzWo4")

tree = ElementTree.fromstring(response.content)

for child in tree[1][0][:]:
    print(child)
    for i in child[:]:
        print(i)
        thisdict = {
                i.tag : i.text
                        }
        print(thisdict)
        #dicts=[]
        #dicts.append(thisdict)
        #print(dicts)
        
        for x, y in thisdict.items():
              print(x, y)

For [reproducible example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples), please post sample XML including root and enough nodes to understand structure (redact as necessary). And show desired results. — Parfait, Jul 09 '20 at 14:26

score 0 · Answer 1 · answered Jul 10 '20 at 22:15

Another method.

from simplified_scrapy import SimplifiedDoc, utils, req
xml = req.get(
    'http://mapiuat.vwbeatroute.com/v1/customer/index?key=aQeZx9WpppnbyJmar2ry4Ah0_2WdzWo4'
)
doc = SimplifiedDoc(xml)
items = doc.selects('data>item')
for item in items:
    children = item.children
    dicts = []
    for child in children:
        if child.tag == 'customFields' or child.tag == 'statutoryFields':
            custom = child.selects('item')
            for i in custom:
                thisdict = {}
                for kv in i.children:
                    thisdict[kv.tag] = kv.text
                dicts.append(thisdict)
        else:
            thisdict = {child.tag: child.text}
            dicts.append(thisdict)
    print(dicts)

Result:

[{'distributor_id': '502914'}, {'distributor_external_id': '1200012'}, {'distributor_name': 'LIVGUARD ENERGY TECHNOLOGIES PVT. LTD.'}, {'id': '724342'}, {'city': 'Mohanlal'}, {'chain_id': ''}, {'district': ''}, {'unique_id': ''}, {'contact_person': 'Mr. Mansha Ram'}, {'country': 'India'}, {'created_by': '12216'}, {'created_date': '2016-10-07 15:50:36'}, {'deleted_by': ''}, {'deleted_date': ''}, {'email': ''}, {'fax': ''}, {'fssai_number': ''}, {'group': '0'}, {'is_available': '1'}, {'landline': ''}, {'longitude': '0.000000000000'}, {'latitude': '0.000000000000'}, {'locality': ''}, {'mobile': '9453705181'}, {'modified_by': '12216'}, {'modified_date': '2019-07-12 13:24:47'}, {'name': 'SHAM ENTERPRISES'}, {'pan': ''}, {'pincode': '227305'}, {'retailer_subtype': '0'}, {'retailer_type': 'MT'}, {'status': '1'}, {'street': 'MOHAN LAL GANJ'}, {'sync_time': '2019-04-12 18:03:15'}, {'route_id': '11573'}, {'route_order': '0'}, {'stage': ''}, {'tsm': '12731'}, {'sm': '12217'}, {'route': 'LUCKNOW ROUTE - 1'}, {'sales_rep_id': '12732'}, {'state_id': '689'}, {'state': 'Haryana'}, {'external_id': '724342'}, {'labelId': '371', 'labelName': 'BPM Unique Id', 'value': ''}, {'labelId': '376', 'labelName': 'Test', 'value': ''}, {'id': '10', 'name': 'GSTIN Number'}]
......

Parse XML data into data frame

1 Answers1