Convert JSON nested list of dict to DataFrame

Question

How can I convert the following list of dicts (json output) to a pandas DataFrame. I tried

res = {} 
for d in list_of_dict: 
    res.update(d)

It gives me the error:

ValueError: dictionary update sequence element #0 has length 33; 2 is required

Example JSON output, needed converted to DataFrame.

{
    "PlanCoverages": [
        {
            "PlanId": 65860,
            "FormularyId": 61855,
            "PlanName": "CVS Caremark Performance Standard Control w/Advanced Specialty Control",
            # :
            "OverTheCounter": false
        },
        {
            "PlanId": 69549,
            "FormularyId": 63811,
            "PlanName": "CVS Caremark Performance Standard Opt-Out w/ Advanced Specialty Control ",
            # :
            "OverTheCounter": false
        } ]
}

Here is my full code. It connects to an API, and scraps information on pharmaceuticals. I need the PlanCoverages of 1330 plans.

import requests
import pandas as pd
from pandas.io.json import json_normalize 
import json

headers = {
    'Accept': '*/*',
    'X-Requested-With': 'XMLHttpRequest',
    'Access-Token': 'H-oa4ULGls2Cpu8U6hX4myixRoFIPxfj',
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.87 Safari/537.36',
    'Is-Session-Expired': 'false',
    'Referer': 'https://formularylookup.com/',
}

response = requests.get('https://formularylookup.com/Formulary/Coverage/Controller?ProductId=237171&ProductName=Rybelsus&ControllerId=884&ChannelId=1&StateId=all&DrugTypeId=3&Options=PlanCoverages', headers=headers)
df = response.json()

df_normal =  json_normalize(df)["PlanCoverages"]#["ControllerCoverages"]
#dff = pd.DataFrame(df_normal)

#dff = json.dumps(df, indent=4, sort_keys=False)

res = {} 
for d in df_normal: 
    res.update(d)

print(res)

Ideal output is, 1 row per plan. So a total of 1330 rows.

Daniel · Accepted Answer · 2020-02-01T17:08:14.943

2

Something like this will work:

I have assumed your json object is one large string named 'data'.

import pandas as pd    
import json

# json object:
json_string = """ { "PlanCoverages": [ { "PlanId": 65860, ... """

# 1) load json object as python variable:
data = json.loads(json_string)

# 2) convert to dataframe:
plan_coverages = pd.DataFrame(data['PlanCoverages'])

edited Feb 01 '20 at 17:08

answered Feb 01 '20 at 16:57

Daniel

3,228
1
7
23

score 0 · Answer 2 · answered Feb 01 '20 at 17:30

I think you need something like this

import pandas as pd    
import json


convert json to python dictionary:
my_dict = json.loads(json_string)

convert a dictionary my_dict to dataframe df:
df = pd.concat({k: pd.DataFrame(v).T for k, v in my_dict.items()}, axis=0)

#reset the index
df = df.reset_index()

Convert JSON nested list of dict to DataFrame

2 Answers2

Linked