How to convert ordered collections to data frame with the proper key value pairs in Python?

user_13270019 0
Viewed 83

I have to get the content from a page that is in XML format into a data frame.

My code to read the file is:


from urllib.request import urlopen
import re
import pandas as pd
import xmltodict
from collections import OrderedDict

x= urlopen('https://mpr.datamart.ams.usda.gov/ws/report/v1/cattle/LM_CT138?filter=%7B%22filters%22:%5B%7B%22fieldName%22:%22Report%20date%22,%22operatorType%22:%22EQUAL%22,%22values%22:%5B%224/1/2022%22%5D%7D%5D%7D').read().decode('utf-8')

data = xmltodict.parse(x)

print(data)

The output is in ordered Dictionary and I tried number of ways to convert it to the required data frame.

Example


keys = data.keys()
values = data.values()

print ("keys : ", str(keys))
print ("values : ", str(values))

pd.DataFrame.from_dict(values)

I am getting the entire dictionary in one column. I want them split with according to the key values in different coulms.

pandas dataframe dictionary python
2 Answers

Try this:

import requests
import xml.etree.ElementTree as ET

r = requests.get('https://mpr.datamart.ams.usda.gov/ws/report/v1/cattle/LM_CT138?filter=%7B%22filters%22:%5B%7B%22fieldName%22:%22Report%20date%22,%22operatorType%22:%22EQUAL%22,%22values%22:%5B%224/1/2022%22%5D%7D%5D%7D')

root = ET.fromstring(r.text)
records = [record.attrib for record in root.iterfind("report/record/report/record")]
df = pd.DataFrame(records)

Every column comes out as string. You may convert it to other data types as needed.

user_25389390

The data is stored in a nested dict as you see on the screenshotoriginal data screenshot You are trying to create a dataframe with column names being "first level keys". Obviously, you are obtaining "first level values" in the "results" column and the whole dict inside the "report" cell. You need to call the correct level of the nested dictionary to get the correct dataframe.

import pandas as pd
import requests
import xmltodict

url = 'https://mpr.datamart.ams.usda.gov/ws/report/v1/cattle/LM_CT138?filter=%7B"filters":%5B%7B"fieldName":"Report%20date","operatorType":"EQUAL","values":%5B"4/1/2022"%5D%7D%5D%7D'
r = requests.get(url)
data = xmltodict.parse(r.text)
data_ = data['results']['report']['record']['report']['record']  # returns the list of "OrderedDict"-s with keys = column names
pd.DataFrame(data_)

Sorry for using requests, I'm not really familiar with a urllib library

user_183748980
Related