Python for Data Science – Importing XML to Pandas DataFrame

In my previous post, I showed how easy to import data from CSV, JSON, Excel files using Pandas package. Another popular format to exchange data is XML. Unfortunately Pandas package does not have a function to import data from XML so we need to use standard XML package and do some extra work to convert the data to Pandas DataFrames.

Here’s a sample XML file (save it as test.xml):

We want to convert his to a dataframe which contains customer name, email, phone and street:

As you can see, we need to read attribute of an XML tag (customer name), text value of sub elements (address/street), so although we will use a very simple method, it will show you how to parse even complex XML files using Python.

Python provides a comprehensive XML package which provides different APIs to parse XML. Unfortunately none of them are secure against erroneous or maliciously constructed data. Please do not forget to check XML vulnerabilities. Anyway, I prefer the ElementTree XML API which is a simple and lightweight XML processor.

To read and parse an XML file, all we need is to call the parse method:

The above code will return an ElementTree object, then we can use “iter()” method to generate an iterator (for specific XML elements) or “getroot()” to get the root element for this tree, and then iterate all elements.

It’s almost done. We get customer data (name, email, phone and street). To be able to add these data to a DataFrame, we need to define a DataFrame before we iterate elements, then for each customer, we build a Pandas.Series object (an array), and append this Series object to the DataFrame. While doing this, we should also be ready to handle “null” values because some XML paths might be missing on our XML file.

Here’s the full script:

And the output:

As you can see, I defined a function to handle missing paths, and also gave the same column names while appending the series to the DataFrame (otherwise they would be added as additional columns). Please do not hesitate to ask questions! See you on next blog post!

Please share this post Share on Facebook0Share on Google+0Share on LinkedIn3Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

Leave Comment

Your email address will not be published. Required fields are marked *