Convert json response with nested dictionaries to pandas dataframe

Question

I have a json response which looks something like:

d={'results':[
    {'key1':'1','key2':'item1'},
    {'key1':'1','key2':{
            'subkey20':[
                    {'subkey201':'val', 
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'},
                    {'subkey201':'val',
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'},
                    {'subkey201':'val',
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'},
                    {'subkey201':'val',
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'}]}},
    {'key1':'1','key2':'item1'},
    {'key1':'1','key2':{
            'subkey20':[
                    {'subkey201':'val', 
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'},
                    {'subkey201':'val',
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'},
                    {'subkey201':'val',
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'},
                    {'subkey201':'val',
                    'subkey202':val,
                    'subkey203':'val',
                    'subkey204':'value'}]}},]}

I am currently working on processing this into a pandas dataframe with each key starting from key1 representing a column in a pandas dataframe. So far I have been able to only do:

    df = pd.concat([pd.DataFrame(v) for k,v in d.items()], keys=d)
    print (df)

which does not yield the result i desire. Can i get assistance as to how I should navigate through this and obtain a pandas dataframe with all the keys as columns and values filled in?

I did try using json_normalize but that processes the subkeys into the key2 column as a value, not as individual columns.

Your example is not a complete data structure. If you are asking for free help, at least make it easy for someone... — dawg, Aug 25 '20 at 17:01
Check [here](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_json.html) — Carlo Zanocco, Aug 25 '20 at 17:08
@dawg hi, sorry, I missed out on the closing brackets there, fixed it now. — Nirbhay Tandon, Aug 25 '20 at 17:10

score 1 · Accepted Answer · answered Aug 25 '20 at 17:17

json_normalize() is the tool. Just use it twice along with an explode()

pd.json_normalize(pd.json_normalize(d["results"]).explode("key2.subkey20").to_dict(orient="records"))

output

key1   key2  key2.subkey20 key2.subkey20.subkey201 key2.subkey20.subkey202 key2.subkey20.subkey203 key2.subkey20.subkey204
   1  item1            NaN                     NaN                     NaN                     NaN                     NaN
   1    NaN            NaN                     val                     val                     val                   value
   1    NaN            NaN                     val                     val                     val                   value
   1    NaN            NaN                     val                     val                     val                   value
   1    NaN            NaN                     val                     val                     val                   value
   1  item1            NaN                     NaN                     NaN                     NaN                     NaN
   1    NaN            NaN                     val                     val                     val                   value
   1    NaN            NaN                     val                     val                     val                   value
   1    NaN            NaN                     val                     val                     val                   value
   1    NaN            NaN                     val                     val                     val                   value

thanks! This worked! I believe I was on the right track with json_normalize but it hadn't occurred to me to use it twice. — Nirbhay Tandon, Aug 25 '20 at 17:33
I've wrestled with it a few times and realised keep it simple... — Rob Raymond, Aug 25 '20 at 17:41

Convert json response with nested dictionaries to pandas dataframe

1 Answers1