How to convert multilevel json/dict into python dataframe?

Question

I am getting output web api response as below.

d = { 'L1': [{'att_1': 10, 'att_2': 'whatever',
                  'att_4': 214, 'att_5': 'YES'}],
  'L2': [{'att_1': 11, 'att_2': 'whatever News',
                  'att_4': 230, 'att_5': 'another'}],
  'L3': [{'att_1': 12, 'att_2': 'whatever says',
                  'att_4': 213, 'att_5': 'YES'}]}

from above multilevel json/dict i need pandas data frame as below.

      att_1    att_2           att_4     att_5
L1    10       whatever        214       YES
L2    11       whatever  News  230       another
L3    12       whatever says   213       YES

I tried as below

for k, v in d.items():
   print(k)
   print(v)

pd.DataFrame.from_dict({k:(v,orient='index') for k, v in d.items()}axis=1)

I am getting error. could you pls provide the solution?

Does this answer your question? [Construct pandas DataFrame from items in nested dictionary](https://stackoverflow.com/questions/13575090/construct-pandas-dataframe-from-items-in-nested-dictionary) — bonCodigo, May 19 '23 at 11:33

score 1 · Answer 1 · answered May 19 '23 at 11:35

After fixing the typos in your d ('Lx' instead of Lx), you can re-arange/fix your code this way :

df = pd.DataFrame.from_dict({k: v[0] for k, v in d.items()}, orient="index")

Output :

print(df)

    att_1          att_2  att_4    att_5
L1     10       whatever    214      YES
L2     11  whatever News    230  another
L3     12  whatever says    213      YES

score 0 · Accepted Answer · answered May 19 '23 at 11:35

For general solution create DataFrames in dictionary comprehension with concat, last remove second level of MultiIndex by DataFrame.droplevel:

df = pd.concat({k: pd.DataFrame(v) for k, v in d.items()}).droplevel(1)
print (df)
    att_1          att_2  att_4    att_5
L1     10       whatever    214      YES
L2     11  whatever News    230  another
L3     12  whatever says    213      YES

If there are one element lists is possible use:

df = pd.DataFrame.from_dict({k: v[0] for k, v in d.items()}, orient='index')
print (df)
    att_1          att_2  att_4    att_5
L1     10       whatever    214      YES
L2     11  whatever News    230  another
L3     12  whatever says    213      YES

What's the difference between OP's question and the one in my comment? — bonCodigo, May 20 '23 at 02:49

score 0 · Answer 3 · answered May 19 '23 at 11:38

You can do like this:

df = pd.DataFrame.from_records((v[0] for v in d.values()), index=d.keys())

+--+-----+-------------+-----+-------+
|  |att_1|att_2        |att_4|att_5  |
+--+-----+-------------+-----+-------+
|L1|10   |whatever     |214  |YES    |
|L2|11   |whatever News|230  |another|
|L3|12   |whatever says|213  |YES    |
+--+-----+-------------+-----+-------+

How to convert multilevel json/dict into python dataframe?

3 Answers3