XML to Pandas DF with namespaces - Python

user_12623018 0
Viewed 106

I need to convert an XML download from Wordpress to a Pandas DF to do some data analysis.

The XML is automatically generated and has a few namespaces:

<rss version="2.0"
    xmlns:excerpt="http://wordpress.org/export/1.2/excerpt/"
    xmlns:content="http://purl.org/rss/1.0/modules/content/"
    xmlns:wfw="http://wellformedweb.org/CommentAPI/"
    xmlns:dc="http://purl.org/dc/elements/1.1/"
    xmlns:wp="http://wordpress.org/export/1.2/"
>

And the file has hundreds of <item>s, each one of them with hundreds of fields. Example of one :

<item>
        <title>MockTitle</title>
        <link>https://mock.url.io/?post_type=mock&#038;p=992</link>
        <pubDate>Fri, 30 Oct 2020 20:47:53 +0000</pubDate>

        (...)

        <wp:postmeta>
            <wp:meta_key><![CDATA[_customer_user]]></wp:meta_key>
            <wp:meta_value><![CDATA[0]]></wp:meta_value>
        </wp:postmeta>
        
        <wp:postmeta>
            <wp:meta_key><![CDATA[_payment_method]]></wp:meta_key>
            <wp:meta_value><![CDATA[paypal]]></wp:meta_value>
        </wp:postmeta>

        <wp:postmeta>
            <wp:meta_key><![CDATA[_payment_method_title]]></wp:meta_key>
            <wp:meta_value><![CDATA[PayPal]]></wp:meta_value>
        </wp:postmeta>

        <wp:postmeta>
            <wp:meta_key><![CDATA[_customer_ip_address]]></wp:meta_key>
            <wp:meta_value><![CDATA[1.1.1.15]]></wp:meta_value>
        </wp:postmeta>

        <wp:postmeta>
            <wp:meta_key><![CDATA[_customer_user_agent]]></wp:meta_key>
            <wp:meta_value><![CDATA[Mock Info about the agent]]></wp:meta_value>
        </wp:postmeta>

        (...)
</item>
<item>
...

My code can easily pull elements like title and pubDate, but I don't know how I could do it to search by wp:meta_key and actually pull out the wp:meta_value corresponding to that key. I am missing that in order to be able to create data frames with the specific information that I want.

Here is my code so far:

import pandas as pd
import xml.etree.ElementTree as et 

xtree = et.parse("data.xml")
xroot = xtree.getroot()

for item in xroot: 
    order_number = item.find("./title").text
    date = item.find("./pubDate").text

#Don't know how to access the different wp:postmeta's and search by wp:meta_key...
pandas wordpress xml python
1 Answers

Using a simplified version of your xml, and lxml with xpath:

rss = """<rss version="2.0"
    xmlns:excerpt="http://wordpress.org/export/1.2/excerpt/"
    xmlns:content="http://purl.org/rss/1.0/modules/content/"
    xmlns:wfw="http://wellformedweb.org/CommentAPI/"
    xmlns:dc="http://purl.org/dc/elements/1.1/"
    xmlns:wp="http://wordpress.org/export/1.2/"
>
<items>
<item>
        <title>MockTitle</title>
        <link>https://mock.url.io/?post_type=mock&#038;p=992</link>
        <pubDate>Fri, 30 Oct 2020 20:47:53 +0000</pubDate>
        <wp:postmeta>
            <wp:meta_key><![CDATA[_customer_user]]></wp:meta_key>
            <wp:meta_value><![CDATA[0]]></wp:meta_value>
        </wp:postmeta>        
        <wp:postmeta>
            <wp:meta_key><![CDATA[_payment_method]]></wp:meta_key>
            <wp:meta_value><![CDATA[paypal]]></wp:meta_value>
        </wp:postmeta>
</item>
<item>
        <title>MockTitle2</title>
        <link>https://mock2.url.io/?post_type=mock&#038;p=992</link>
        <pubDate>Fri, 30 Oct 2220 20:47:53 +0000</pubDate>
        <wp:postmeta>
            <wp:meta_key><![CDATA[_customer_user]]></wp:meta_key>
            <wp:meta_value><![CDATA[02]]></wp:meta_value>
        </wp:postmeta>        
        <wp:postmeta>
            <wp:meta_key><![CDATA[_payment_method]]></wp:meta_key>
            <wp:meta_value><![CDATA[paypal2]]></wp:meta_value>
        </wp:postmeta>
</item>
</items>
</rss>"""

from lxml import etree
doc = etree.XML(rss)
ns = { (k if k else "xx"):(v) for k, v in doc.xpath('//namespace::*') }

#searching, for example, for "_payment_method"

for company in doc.xpath("//wp:meta_key[.='_payment_method']/following-sibling::wp:meta_value/text()", namespaces=ns):
    print(company)

Output:

paypal
paypal2
user_94480900
Related