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 convert any XML file to Pandas 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:

loc lastmod changefreq
0 https://example.com/item-1 2022-06-02T00:00:00Z weekly
1 https://example.com/item-2 2022-06-02T11:34:37Z weekly
2 https://example.com/item-3 2022-06-03T19:24:47Z weekly

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 local 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:

loc lastmod changefreq
0 https://example.com/item-1 2022-06-02T00:00:00Z weekly
1 https://example.com/item-2 2022-06-02T11:34:37Z weekly
2 https://example.com/item-3 2022-06-03T19:24:47Z weekly

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 remote XML File with read_xml()

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 to Python list or dict

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

We can first convert the file to DataFrame and then get the values from this DataFrame by:

Example 1:

df['loc'].to_list()

result:

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

Example 2:

df['loc'].to_list()

result:

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

Example 3:

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 remote 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:

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 with Python library - xmltodict

There is an alternative solution for reading XML files 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 files 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.