Convert json response with nested dictionaries to pandas dataframe [duplicate]

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
– dawg, Commented Aug 25, 2020 at 17:01
@dawg hi, sorry, I missed out on the closing brackets there, fixed it now. — Nirbhay Tandon
– Nirbhay Tandon, Commented Aug 25, 2020 at 17:10

Rob Raymond · Accepted Answer · 2020-08-25 17:17:52Z

1

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

answered Aug 25, 2020 at 17:17

Rob Raymond

31.5k3 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nirbhay Tandon Over a year ago

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.

Rob Raymond Over a year ago

I've wrestled with it a few times and realised keep it simple...

Collectives™ on Stack Overflow

Convert json response with nested dictionaries to pandas dataframe [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related