pandas dataframe from nested JSON with lists

user_11545694 0
Viewed 1293

Although a few other examples of nested JSON to pandas dataframe can be found, this one I cannot find and hence not succeed. I have a nested JSON as follows:

{'x':
     {'1':[2,5,6],'2':[7,6]},
 'y':
     {'1':[0,4,8],'2':[8,1]},
 'z':
     {'1':[8,0,9],'2':[2,2]}}

and I would like a dataframe as:

   1_0 1_1 1_2 2_0 2_1
x   2   5   6   7   6
y   0   4   8   8   1
z   8   0   9   2   2

the labelling of the columns do not necessarily have to be exactly this as long as I can infer the data correctly.

I have tried this:

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

with gzip.open('example.json') as f:    
    d = json.load(f)  

df = pd.json_normalize(d)
df

resulting in this:

enter image description here

pandas python json
1 Answers

One way is to first use DataFrame.from_dict to load the values as list, and then concat them:

d = {'x':
     {'1':[2,5,6],'2':[7,6]},
 'y':
     {'1':[0,4,8],'2':[8,1]},
 'z':
     {'1':[8,0,9],'2':[2,2]}}

df = pd.DataFrame.from_dict(d, orient="index")
df2 = pd.concat([pd.DataFrame(df[i].values.tolist(),
                              columns=[f"{i}_{num}" for num in range(len(df[i].iat[0]))]
                              ) for i in df.columns],axis=1)

print (df2)

   1_0  1_1  1_2  2_0  2_1
0    2    5    6    7    6
1    0    4    8    8    1
2    8    0    9    2    2

Alternatively using chain.from_iterable to flatten the lists first:

from itertools import chain

print (pd.DataFrame([chain.from_iterable(i.values()) for i in d.values()],
                    index=d.keys(),
                    columns=[f"{k}_{num}" for k, v in list(d.values())[0].items()
                             for num in range(len(v))]))

   1_0  1_1  1_2  2_0  2_1
x    2    5    6    7    6
y    0    4    8    8    1
z    8    0    9    2    2
user_92844230
Related