CODEWITHSUNDEEP
pythonxml
Bob Wakefield
Bob Wakefield

Reputation: 4009

Parsing metadata properties from ADO.Net Data Services XML in Python

I want to put some XML into a pandas dataframe before I stuff it into a database table. I've taken a look at Element Tree and lxml but the examples are really simple and I can't seem to extrapolate them to something this complex. I understand XML I'm just not sure how to drill down to what I need. A sample is below.

I'm after the stuff in <m:properties>. So NEW_DATE = 1997-01-02T00:00:00, BC_1YEAR = 5.630000114440918 etc. is what goes in the database. Notice how BC_1MONTH = NULL and is not like the other nodes.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="http://data.treasury.gov/Feed.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">DailyTreasuryYieldCurveRateData</title>
  <id>http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData</id>
  <updated>2017-10-30T20:31:53Z</updated>
  <link rel="self" title="DailyTreasuryYieldCurveRateData" href="DailyTreasuryYieldCurveRateData" />
  <entry>
    <id>http://data.treasury.gov/Feed.svc/DailyTreasuryYieldCurveRateData(1)</id>
    <title type="text"></title>
    <updated>2017-10-30T20:31:53Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="DailyTreasuryYieldCurveRateDatum" href="DailyTreasuryYieldCurveRateData(1)" />
    <category term="TreasuryDataWarehouseModel.DailyTreasuryYieldCurveRateDatum" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Id m:type="Edm.Int32">1</d:Id>
        <d:NEW_DATE m:type="Edm.DateTime">1997-01-02T00:00:00</d:NEW_DATE>
        <d:BC_1MONTH m:type="Edm.Double" m:null="true" />
        <d:BC_3MONTH m:type="Edm.Double">5.190000057220459</d:BC_3MONTH>
        <d:BC_6MONTH m:type="Edm.Double">5.3499999046325684</d:BC_6MONTH>
        <d:BC_1YEAR m:type="Edm.Double">5.630000114440918</d:BC_1YEAR>
        <d:BC_2YEAR m:type="Edm.Double">5.96999979019165</d:BC_2YEAR>
        <d:BC_3YEAR m:type="Edm.Double">6.130000114440918</d:BC_3YEAR>
        <d:BC_5YEAR m:type="Edm.Double">6.3000001907348633</d:BC_5YEAR>
        <d:BC_7YEAR m:type="Edm.Double">6.4499998092651367</d:BC_7YEAR>
        <d:BC_10YEAR m:type="Edm.Double">6.5399999618530273</d:BC_10YEAR>
        <d:BC_20YEAR m:type="Edm.Double">6.8499999046325684</d:BC_20YEAR>
        <d:BC_30YEAR m:type="Edm.Double">6.75</d:BC_30YEAR>
        <d:BC_30YEARDISPLAY m:type="Edm.Double">0</d:BC_30YEARDISPLAY>
      </m:properties>
    </content>
  </entry>
</feed>

If you have links to a good article that talks about this, that would be appreciated too.

Below is the code I'm working with:

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

xml_data = open('/path/user_agents.xml').read()

def xml2df(xml_data):
    root = ET.XML(xml_data) # element tree
    all_records = []
    for i, child in enumerate(root):
        record = {}
        for subchild in child:
            record[subchild.tag] = subchild.text
            all_records.append(record)
    return pd.DataFrame(all_records)

Error message received from Duffy's code:

Traceback (most recent call last):
  File "C:/Users/Bob/Desktop/temp/yield curve script.py", line 25, in <module>
    xml2dict(xml_data)
  File "C:/Users/Bob/Desktop/temp/yield curve script.py", line 13, in xml2dict
    root = lxml.etree.parse(xml_file)
  File "src\lxml\lxml.etree.pyx", line 3427, in lxml.etree.parse (src\lxml\lxml.etree.c:81100)
  File "src\lxml\parser.pxi", line 1811, in lxml.etree._parseDocument (src\lxml\lxml.etree.c:117831)
  File "src\lxml\parser.pxi", line 1837, in lxml.etree._parseDocumentFromURL (src\lxml\lxml.etree.c:118178)
  File "src\lxml\parser.pxi", line 1741, in lxml.etree._parseDocFromFile (src\lxml\lxml.etree.c:117090)
  File "src\lxml\parser.pxi", line 1138, in lxml.etree._BaseParser._parseDocFromFile (src\lxml\lxml.etree.c:111636)
  File "src\lxml\parser.pxi", line 595, in lxml.etree._ParserContext._handleParseResultDoc (src\lxml\lxml.etree.c:105092)
  File "src\lxml\parser.pxi", line 706, in lxml.etree._handleParseResult (src\lxml\lxml.etree.c:106800)
  File "src\lxml\parser.pxi", line 633, in lxml.etree._raiseParseError (src\lxml\lxml.etree.c:105611)
OSError: Error reading file '<?xml version="1.0" encoding="utf-8" standalone="yes"?>

Upvotes: 0

Views: 700

Answers (1)

Charles Duffy
Charles Duffy

Reputation: 295363

import lxml.etree
import datetime

nsmap = {
  'm': 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata',
  'd': 'http://schemas.microsoft.com/ado/2007/08/dataservices'
}
m_null = ('{%s}null' % nsmap['m'])
m_type = ('{%s}type' % nsmap['m'])

type_handlers = {
    'Edm.Double': float,
    'Edm.Int32': int,
    'Edm.DateTime': lambda s: datetime.datetime.strptime(s.translate(None, ':-'), "%Y%m%dT%H%M%S"),
}

def xml2dict(xml_file):
    root = lxml.etree.parse(xml_file)
    result = {}
    for properties_el in root.xpath('//m:properties', namespaces=nsmap):
        for child in properties_el.getchildren():
            tag = child.tag.split('}',1)[-1]  # split the namespace off the tag
            if child.attrib.get(m_null):
                value = None
            else:
                value = child.text
                type_handler = type_handlers.get(child.attrib.get(m_type))
                if type_handler is not None:
                    value = type_handler(value)
            result[tag] = value
    return result

...properly returns, for your data:

{'BC_10YEAR': 6.539999961853027,
 'BC_1MONTH': None,
 'BC_1YEAR': 5.630000114440918,
 'BC_20YEAR': 6.849999904632568,
 'BC_2YEAR': 5.96999979019165,
 'BC_30YEAR': 6.75,
 'BC_30YEARDISPLAY': 0.0,
 'BC_3MONTH': 5.190000057220459,
 'BC_3YEAR': 6.130000114440918,
 'BC_5YEAR': 6.300000190734863,
 'BC_6MONTH': 5.349999904632568,
 'BC_7YEAR': 6.449999809265137,
 'Id': 1,
 'NEW_DATE': datetime.datetime(1997, 1, 2, 0, 0)}

Upvotes: 1

Related Questions