Converting large XML file to DataFrame

Question

def intr_docs(xml_doc):
    
    attr = xml_doc.attrib
    for chld in root:
        
        for value in chld:
                    
            for xml in xml_doc.iter('timestep'):
                doc_dict = attr.copy()
                doc_dict.update(xml.attrib)
                doc_dict['time'] = chld.attrib['time']             
                doc_dict['id'] = value.attrib['id']
                doc_dict['speed'] = value.attrib['speed']
                doc_dict['lane'] = value.attrib['lane']
            yield doc_dict

doc_df = pd.DataFrame(list(intr_docs(root)))

I am new to python. I have XML file (attached image)1 and want to convert it into the dataframe (attached image)2. I got the code and converted the file (13 MB) as I wanted but in the case of a large file (more than 500 MB) it is taking a lot of time. I am wondering it is due to code or my PC capacity. So how can I do it.

user_14168623 · Answer

Probably the default read_xml() method will perform better:

import pandas as pd

df = pd.read_xml(xml, xpath="//vehicle", attrs_only=True)

Using xpath parameter tells pandas to use each vehicle element as a dataframe row, attrs_only parameter tells pandas to use attributes as row fields. This approach will take all attributes of vehicle element, unnecessary fields can be dropped after dataframe creation.

Solution using SAX parser:

import xml.sax
from collections import defaultdict
import pandas as pd

# class handling SAX events
class TimestepHandler(xml.sax.ContentHandler):

  # dictionary containing data for dataframe
  data = defaultdict(list)

  def __init__(self):
    self.data = defaultdict(list)
    self.time = None

  # start element event handler
  def startElement(self, tag, attributes):
    # on 'timestep' element save time value
    if tag == 'timestep':
      self.time = attributes['time']
    # on 'vehicle' element add attribute values to the dictionary
    # reusing 'time' value from last 'timestep' element
    if tag == 'vehicle':
      self.data['time'].append(self.time)
      self.data['id'].append(attributes['id'])
      self.data['x'].append(attributes['x'])
      self.data['y'].append(attributes['y'])
      self.data['angle'].append(attributes['angle'])
      self.data['type'].append(attributes['type'])
      self.data['speed'].append(attributes['speed'])
      self.data['pos'].append(attributes['pos'])
      self.data['lane'].append(attributes['lane'])
      self.data['slope'].append(attributes['slope'])

# create SAX parser with custom handler   
parser = xml.sax.make_parser()
handler = TimestepHandler()
parser.setContentHandler(handler)

# parse XML file 
parser.parse("file.xml")

# create pandas dataframe from dictionary
df = pd.DataFrame(handler.data)
df

Solution using lxml:

import pandas as pd
from lxml import etree
from collections import defaultdict

d = defaultdict(list)

doc = etree.parse('file.xml')

# retrieve 'vehicle' elements using xpath
vehicles = doc.xpath('.//vehicle')

# iterate over 'vehicle' elements
for v in vehicles:
  d['id'].append(v.get('id'))
  d['time'].append(v.xpath('../@time')[0])
  d['x'].append(v.get('x'))
  d['y'].append(v.get('y'))
  d['angle'].append(v.get('angle'))
  d['type'].append(v.get('type'))
  d['speed'].append(v.get('speed'))
  d['pos'].append(v.get('pos'))
  d['lane'].append(v.get('lane'))
  d['slope'].append(v.get('slope'))

# create pandas dataframe from dictionary
df = pd.DataFrame(d)
df

Converting large XML file to DataFrame

1 Answers