Convert API Reponse to Pandas DataFrame

Question

I making an API call with the following code:

req = urllib.request.Request(url, body, headers)

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

Which returns the following:

{"forecast": [17.588294043898163, 17.412641963452206], 
    "index": [
            {"SaleDate": 1629417600000, "Type": "Type 1"}, 
            {"SaleDate": 1629504000000, "Type": "Type 2"}
        ]
}

How can I convert this api response to a Panda DataFrame to convert the dict in the following format in pandas dataframe

Forecast                 SaleDate     Type
17.588294043898163       2021-08-16   Type 1
17.412641963452206       2021-08-17   Type 1

Answer 1

You can use the following. It uses pandas.Series to convert the dictionary to columns and pandas.to_datetime to map the correct date from the millisecond timestamp:

d = {"forecast": [17.588294043898163, 17.412641963452206], 
    "index": [
            {"SaleDate": 1629417600000, "Type": "Type 1"}, 
            {"SaleDate": 1629504000000, "Type": "Type 2"}
        ]
}

df = pd.DataFrame(d)
df = pd.concat([df['forecast'], df['index'].apply(pd.Series)], axis=1)
df['SaleDate'] = pd.to_datetime(df['SaleDate'], unit='ms')

output:

    forecast   SaleDate    Type
0  17.588294 2021-08-20  Type 1
1  17.412642 2021-08-21  Type 2