Filtering a section in json data using Python

mbjeter
June 3, 2023
248 views
0 votes
2 Answers

Need assistance only pulling / filtering "results" from json data as shown below

[
{
    "reportSection": "Report Detail",
    "reportSections": [
        "Report Header",
        "Report Detail"
    ],
    "stats": {
        "returnedRows": 22,
        "userAllowedRows": 100000,
        "totalRows": 22
    },
    "results": [
        {
            "report_date": "05/31/2023",
            "report_begin_date": "05/31/2023",
            "report_end_date": "05/31/2023",
            "published_date": "05/31/2023 13:55:06",
            "office_name": "Minneapolis, MN",
            "office_city": "Minneapolis",
            "office_state": "MN",
            "market_type": "Point of Sale - Grain",
            "market_location_name": "Grain - South Dakota",
            "market_location_city": "Sioux Falls",
            "market_location_state": "SD",
            "slug_id": 3186,
            "slug_name": "AMS_3186",
            "report_title": "South Dakota Daily Grain Bids",
            "grp": "Grains",
            "cat": "Coarse",
            "commodity": "Soybeans",
            "location_State": null,
            "location_City": null,
            "trade_loc": "Central",
            "class": null,
            "grade": "US #1",
            "protein": null,
            "application": null,
            "pkg": "bulk",
            "desc": null,
            "quote_type": "Basis",
            "delivery_point": "Country Elevators",
            "conventional": "Conventional",
            "basis_unit": "¢/Bu",
            "sale Type": "Bid",
            "basis Min": 20,
            "basis Min Futures Month": "August (Q)",
            "basis Max": 30,
            "basis Max Futures Month": "August (Q)",
            "basis Min Change": 0,
            "basis Min Direction": "UNCH",
            "basis Max Change": 0,
            "basis Max Direction": "UNCH",
            "price_unit": "$ Per Bushel",
            "price Min": 12.3775,
            "price Max": 12.4775,
            "price Min Change": 0.0175,
            "price Min Direction": "DN",
            "price Max Change": 0.0175,
            "price Max Direction": "DN",
            "avg_price": 12.4108,
            "avg_price_year_ago": 16.3867,
            "freight": "Delivered",
            "trans_mode": "Truck",
            "current": "Yes",
            "delivery_start": null,
            "delivery Start Half": null,
            "delivery_end": null,
            "delivery End Half": null
        }
    ]
}
]

Only been successful at querying API and pulling back json data file in Python, but trying to learn how to pull out only a specific section of data and read to a data frame. Example, data where Report Section = ‘Report Detail’ and bring back all ‘results’ rows

Below is python code attempted so far:

datapull = response_API.text
data = pd.read_json(datapull)

def section_results(record):
    section = record.get('reportSection', 'Report Header')
    result = section.get('results', None)

print([section_results(r) for r in data])

#data = pd.read_json(datapull)
#df = pd.Series(validate_record_schema(data)).to_frame()
#print (df)

Error I am getting:
enter image description here

Answers

Chosen as BEST ANSWER

Thanks for the help and I now have it working:

response_API = 
requests.get("https://marsapi.ams.usda.gov/services/v1.2/reports/3186? 
q=report_date=2023-05-31&allSections=true", 
auth=HTTPBasicAuth(api_key_string, ''))

print(response_API.status_code)

datapull = response_API.text
print (datapull)

data = json.loads(datapull)

filtered_data = []
for d in data:
    if d.get('reportSection') == 'Report Detail':
        filtered_data.extend(d['results'])

df = pd.DataFrame(filtered_data)
print(df)
df.to_excel("json_data_2.xlsx")

How would one suggest best way to read the list of 'reportSections' so that I can filter data for each section?

(Edit)

What you have is string with Json data. You can use json module to parse it and then filter it to pandas DataFrame:

import json
import pandas as pd


json_string = '''
[
{
    "reportSection": "Report Detail",
    "reportSections": [
        "Report Header",
        "Report Detail"
    ],
    "stats": {
        "returnedRows": 22,
        "userAllowedRows": 100000,
        "totalRows": 22
    },
    "results": [
        {
            "report_date": "05/31/2023",
            "report_begin_date": "05/31/2023",
            "report_end_date": "05/31/2023",
            "published_date": "05/31/2023 13:55:06",
            "office_name": "Minneapolis, MN",
            "office_city": "Minneapolis",
            "office_state": "MN",
            "market_type": "Point of Sale - Grain",
            "market_location_name": "Grain - South Dakota",
            "market_location_city": "Sioux Falls",
            "market_location_state": "SD",
            "slug_id": 3186,
            "slug_name": "AMS_3186",
            "report_title": "South Dakota Daily Grain Bids",
            "grp": "Grains",
            "cat": "Coarse",
            "commodity": "Soybeans",
            "location_State": null,
            "location_City": null,
            "trade_loc": "Central",
            "class": null,
            "grade": "US #1",
            "protein": null,
            "application": null,
            "pkg": "bulk",
            "desc": null,
            "quote_type": "Basis",
            "delivery_point": "Country Elevators",
            "conventional": "Conventional",
            "basis_unit": "¢/Bu",
            "sale Type": "Bid",
            "basis Min": 20,
            "basis Min Futures Month": "August (Q)",
            "basis Max": 30,
            "basis Max Futures Month": "August (Q)",
            "basis Min Change": 0,
            "basis Min Direction": "UNCH",
            "basis Max Change": 0,
            "basis Max Direction": "UNCH",
            "price_unit": "$ Per Bushel",
            "price Min": 12.3775,
            "price Max": 12.4775,
            "price Min Change": 0.0175,
            "price Min Direction": "DN",
            "price Max Change": 0.0175,
            "price Max Direction": "DN",
            "avg_price": 12.4108,
            "avg_price_year_ago": 16.3867,
            "freight": "Delivered",
            "trans_mode": "Truck",
            "current": "Yes",
            "delivery_start": null,
            "delivery Start Half": null,
            "delivery_end": null,
            "delivery End Half": null
        }
    ]
}
]
'''

data = json.loads(json_string)

filtered_data = []
for d in data:
    if d.get('reportSection') == 'Report Detail':
        filtered_data.extend(d['results'])

df = pd.DataFrame(filtered_data)
print(df)

Prints:

  report_date report_begin_date report_end_date       published_date      office_name  office_city office_state            market_type  market_location_name market_location_city market_location_state  slug_id slug_name                   report_title     grp     cat commodity location_State location_City trade_loc class  grade protein application   pkg  desc quote_type     delivery_point  conventional basis_unit sale Type  basis Min basis Min Futures Month  basis Max basis Max Futures Month  basis Min Change basis Min Direction  basis Max Change basis Max Direction    price_unit  price Min  price Max  price Min Change price Min Direction  price Max Change price Max Direction  avg_price  avg_price_year_ago    freight trans_mode current delivery_start delivery Start Half delivery_end delivery End Half
0  05/31/2023        05/31/2023      05/31/2023  05/31/2023 13:55:06  Minneapolis, MN  Minneapolis           MN  Point of Sale - Grain  Grain - South Dakota          Sioux Falls                    SD     3186  AMS_3186  South Dakota Daily Grain Bids  Grains  Coarse  Soybeans           None          None   Central  None  US #1    None        None  bulk  None      Basis  Country Elevators  Conventional       ¢/Bu       Bid         20              August (Q)         30              August (Q)                 0                UNCH                 0                UNCH  $ Per Bushel    12.3775    12.4775            0.0175                  DN            0.0175                  DN    12.4108             16.3867  Delivered      Truck     Yes           None                None         None              None

Please signup or login to give your own answer.

Click here to cancel reply.