Python converting URL JSON response to pandas dataframe

Question

Hi I am making a call to a web service from Python with the following code:

response = urllib.request.urlopen(req)
string = response.read().decode('utf-8')
json_obj = json.loads(string)

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

The result of this is:

                                                               Results
forecast                                          [2.1632421537363355]
index                [{'SaleDate': 1644278400000, 'OfferingGroupId': 0...
prediction_interval         [[-114.9747272420262, 119.30121154949884]]

What I am trying to do now is to have data in DataFrame as:

Forecast              SaleDate      OfferingGroupId
2.1632421537363355    2022-02-08    0

I have tried so many different things that have lost the count.

Could you please help me with this?

Answer 1

You could first convert the json string to a dictionary (thanks @JonSG):

import json
response = urllib.request.urlopen(req)
string = response.read().decode('utf-8')
data = json.loads(string)

or use the json method of response:

data = response.json()

then use pandas.json_normalize where you can directly pass in the record and meta paths of your data to convert the dictionary to a pandas DataFrame object:

import pandas as pd
out = pd.json_normalize(data['Results'], record_path = ['index'], meta = ['forecast'])

Output:

        SaleDate  OfferingGroupId  forecast
0  1644278400000                0  2.163242