How to convert an XML file to nice pandas dataframe?

by

You can easily use xml (from the Python standard library) to convert to a pandas.DataFrame. Here’s what I would do (when reading from a file replace xml_data with the name of your file or file object):

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

def iter_docs(author):
    author_attr = author.attrib
    for doc in author.iter('document'):
        doc_dict = author_attr.copy()
        doc_dict.update(doc.attrib)
        doc_dict['data'] = doc.text
        yield doc_dict

xml_data = io.StringIO(u'''YOUR XML STRING HERE''')

etree = ET.parse(xml_data) #create an ElementTree object 
doc_df = pd.DataFrame(list(iter_docs(etree.getroot())))

If there are multiple authors in your original document or the root of your XML is not an author, then I would add the following generator:

def iter_author(etree):
    for author in etree.iter('author'):
        for row in iter_docs(author):
            yield row

and change doc_df = pd.DataFrame(list(iter_docs(etree.getroot()))) to doc_df = pd.DataFrame(list(iter_author(etree)))

Have a look at the ElementTree tutorial provided in the xml library documentation.

More Related Contents:

  1. Pandas read_csv low_memory and dtype options
  2. How to extract multiple JSON objects from one file?
  3. How do convert a pandas dataframe to XML?
  4. error when applying function to a series object
  5. How to deal with SettingWithCopyWarning in Pandas
  6. Groupby value counts on the dataframe pandas
  7. Python: pandas merge multiple dataframes
  8. Find the column name which has the maximum value for each row
  9. Python: get a frequency count based on two columns (variables) in pandas dataframe some row appers
  10. Find the max of two or more columns with pandas
  11. What is the difference between using squared brackets or dot to access a column?
  12. Creating an empty Pandas DataFrame, then filling it?
  13. What does axis in pandas mean?
  14. How to map numeric data into categories / bins in Pandas dataframe
  15. How to loop over grouped Pandas dataframe?
  16. replace column values in one dataframe by values of another dataframe
  17. using lxml and iterparse() to parse a big (+- 1Gb) XML file
  18. Pandas expand rows from list data available in column
  19. How to concatenate multiple column values into a single column in Pandas dataframe
  20. How can I use cumsum within a group in Pandas?
  21. Selecting/excluding sets of columns in pandas [duplicate]
  22. Why does df.apply(tuple) work but not df.apply(list)?
  23. Merge a list of pandas dataframes
  24. Checking if particular value (in cell) is NaN in pandas DataFrame not working using ix or iloc
  25. Updating a value in a pandas dataframe in an iterrows loop
  26. How to repeat a Pandas DataFrame?
  27. Get column name where value is something in pandas dataframe
  28. How to use str.contains() with multiple expressions in pandas dataframes
  29. How to return a csv file/Pandas DataFrame in JSON format using FastAPI?
  30. How to get number of groups in a groupby object in pandas?

Leave a Comment