req = requests.get("https://data.cityofnewyork.us/api/views/25th-nujf/rows.xml?accessType=DOWNLOAD",proxies=proxies)
doc=etree.fromstring(req.content)
lst = doc.findall('row/row')
print(lst[0])
<Element row at 0x26953028fc8>
How can I make the list into a dataframe such that I can easily access the data instead of getting Element row at 0x26953028fc8?
Would like to format the dataframe in with the following column headers:
_id brth_yr gndr ethncty nm cnt rnk
Thanks in advance!
What's happening in your code above is that you're printing out the type of the first 'row' in lst.
To answer your question you can load XML directly into a DataFrame without using a list.
Im my code below I'm using urllib to download the XML.
Then I'm creating an instance of an etree Element called xml_doc and populating that with the XML downloaded above.
Next I'm defining my data frame column index as a list using the first 2 column headers you requested above.
And then iterating through my XML element xml_doc appending each row to the data frame as I go. Finally the call to dframe.head() will display the first 5 rows by default, if you call dframe.head(10) it will display the first ten rows.
You'll have to expand dfcols to include all the columns headers you want, and do the same to the call pd.Series() in dframe.append().
import xml.etree.ElementTree as ET
import pandas as pd
import urllib.request as request
response = request.urlopen('https://data.cityofnewyork.us/api/views/25th- nujf/rows.xml?accessType=DOWNLOAD')
xml_string = response.read()
xml_doc = ET.fromstring(xml_string)
dfcols = ['_id', 'brth_yr']
dframe = pd.DataFrame(columns=dfcols)
for i in xml_doc.iter(tag='row'):
dframe = dframe.append(pd.Series([i.get('_id'),i.get('brth_yr')], index=dfcols),ignore_index=True)
dframe.head()
