How to Read XML File with Python and Pandas   

In this quick tutorial, we'll cover how to read or convert XML file to Pandas DataFrame or Python data structure.

Since version 1.3 Pandas offers an elegant solution for reading XML files: pd.read_xml().

The short solutions is:

df = pd.read_xml('sitemap.xml')

With the single line above we can read XML file to Pandas DataFrame or Python structure.

Below we will cover multiple examples in greater detail by using two ways:

• pd.read_xml()
• xmltodict - Python library

Setup

Suppose we have simple XML file with the following structure:

<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
   <url>
      <loc>https://example.com/item-1</loc>
      <lastmod>2022-06-02T00:00:00Z</lastmod>
      <changefreq>weekly</changefreq>
   </url>
   <url>
      <loc>https://example.com/item-2</loc>
      <lastmod>2022-06-02T11:34:37Z</lastmod>
      <changefreq>weekly</changefreq>
   </url>
   <url>
      <loc>https://example.com/item-3</loc>
      <lastmod>2022-06-03T19:24:47Z</lastmod>
      <changefreq>weekly</changefreq>
   </url>
</urlset>

which we would like to read as Pandas DataFrame like shown below:

or getting the links as Python list:

['https://example.com/item-1',  'https://example.com/item-2',  'https://example.com/item-3']

Step 1: Read XML File with read_xml()

The official documentation of method read_xml() is placed on this link:

pandas.read_xml.

To read the local XML file in Python we can give the absolute path of the file:

import pandas as pd

df = pd.read_xml('sitemap.xml')

The result will be:

The method has several useful parameters:

• xpath - The XPath to parse the required set of nodes for migration to DataFrame.
• elems_only - Parse only the child elements at the specified xpath. By default, all child elements and non-empty text nodes are returned.
• names - Column names for DataFrame of parsed XML data.
• encoding - Encoding of XML document.
• namespaces - The namespaces defined in XML document as dicts with key being namespace prefix and value the URI.

Step 2: Read XML File with read_xml() - remote

Now let's use Pandas to read XML from a remote location.

The first parameter of read_xml() is: path_or_buffer described as:

String, path object (implementing os.PathLike[str]), or file-like object implementing a read() function. The string can be any valid XML string or a path. The string can further be a URL. Valid URL schemes include http, ftp, s3, and file.

So we can read remote files the same way:

import pandas as pd

df = pd.read_xml( f'https://s3.example.com/sitemap.xml.gz')

The final output will be exactly the same as before - DataFrame which has all values from the XML data.

Step 3: Read XML File as Python list or dict

Now suppose you need to convert XML file to Python list or dictionary.

We need to read the XML file first, then to convert the file to DataFrame and finally to get the values from this DataFrame by:

Example 1: List

df['loc'].to_list()

result:

['https://example.com/item-1', 'https://example.com/item-2', 'https://example.com/item-3']

Example 2: Dictionary

df['loc'].to_dict()

result:

{0: 'https://example.com/item-1',
 1: 'https://example.com/item-2',
 2: 'https://example.com/item-3'}

Example 3: Dictionary - orient index

df[['loc', 'changefreq']].to_dict(orient='index')

result:

{0: {'loc': 'https://example.com/item-1', 'changefreq': 'weekly'},
 1: {'loc': 'https://example.com/item-2', 'changefreq': 'weekly'},
 2: {'loc': 'https://example.com/item-3', 'changefreq': 'weekly'}}

Step 4: Read multiple XML Files in Python

Finally let's see how to read multiple identical XML files with Python and Pandas.

Suppose that files are identical with the following format:

https://s3.example.com/sitemap{i}.xml.gz

So:

• https://s3.example.com/sitemap1.xml.gz
• https://s3.example.com/sitemap2.xml.gz

We can use the following code to read all files in a given range and concatenate them into a single DataFrame:

import pandas as pd

df_temp = []

for i in (range(1, 10)):
    s = f'https://s3.example.com/sitemap{i}.xml.gz'
    
    df_site = pd.read_xml(s)
    df_temp.append(df_site)

The result is a list of DataFrames which can be concatenated into a single one by:

df_all = pd.concat(df_temp)

Now we have information from all XML files into df_all.

Step 5: Read XML File - xmltodict

There is an alternative solution for reading XML file in Python by using the library: xmltodict.

It can be installed by:

pip install xmltodict

To read XML file we can do:

import xmltodict

with open('sitemap.xml') as fd:
    doc = xmltodict.parse(fd.read())

The result is a dict:

{'urlset': {'@xmlns': 'http://www.sitemaps.org/schemas/sitemap/0.9',
  'url': [{'loc': 'https://example.com/item-1',
    'lastmod': '2022-06-02T00:00:00Z',
    'changefreq': 'weekly'},
   {'loc': 'https://example.com/item-2',
    'lastmod': '2022-06-02T11:34:37Z',
    'changefreq': 'weekly'},
   {'loc': 'https://example.com/item-3',
    'lastmod': '2022-06-03T19:24:47Z',
    'changefreq': 'weekly'}]}}

Accessing elements can be done by:

doc['urlset']['url'][0]

the result is:

    {'loc': 'https://example.com/item-1',
 'lastmod': '2022-06-02T00:00:00Z',
 'changefreq': 'weekly'}

Conclusion

In this article, we covered several ways to read XML file with Python and Pandas. Now we know how to read local or remote XML files, using two Python libraries.

Different options and parameters make the XML conversion with Python - easy and flexible.