read individual nodes from an xml url using pandas

user_9346438 0
Viewed 91

I am trying to read an XML file and access one specific attribute, in this case the DonorAdvisedFundInd attribute, and use it to create a dataframe in Pandas. So far I've tried the following code:

import xml.etree.ElementTree as et
import requests
 
xml_data = requests.get("https://s3.amazonaws.com/irs-form-990/201903199349320465_public.xml").content
 
xtree = et.parse(xml_data)
xroot = xtree.getroot()
 
df_cols = ["DAF"]
df_rows = []
for node in xroot:
    is_DAF = node.attrib.get("DonorAdvisedFundInd")
    df_rows.append({"DAF":is_DAF})
out_df = pd.DataFrame(df_rows, columns=df_cols)
out_df

but I'm getting this error message: Errno 36: file name too long

I appreciate any feedback and alternative suggestions anybody can provide. Thank you!

pandas dataframe xml python
1 Answers

Consider the new Pandas 1.3+ method, read_xml. In fact, in its IO tools docs, there is an example of retrieving an AWS S3 bucket IRS-990 XML forms, requiring the s3fs package. Otherwise pass URL directly without need of requests.

Importantly, since the IRS 990 form maintains a default namespace, use the namespaces argument for use in XPath query. Note: below xpath must be adjusted for the parent of DisplayName node, where DisplayName and its siblings migrate as columns in data frame.

S3 Path

df = pd.read_xml(
    "s3://irs-form-990/201903199349320465_public.xml",
    xpath=".//irs:Parent_of_DisplayName",
    namespaces={"irs": "http://www.irs.gov/efile"}
)

Https Path

df = pd.read_xml(
    "https://s3.amazonaws.com/irs-form-990/201903199349320465_public.xml",
    xpath=".//irs:Parent_of_DisplayName",
    namespaces={"doc": "http://s3.amazonaws.com/doc/2006-03-01/"}
)
user_14224510
Related