Convert a dictionary of dictionary into a row wise dataframe in pandas

Question

I have dictionary as shown below

d1:

{'teachers': 49, 
      'students': 289,  
      'R': 3.7, 
      'holidays': 165, 
      'E': {'from': '2020-02-29T20:00:00.000Z', 'to': '2020-03-20T20:00:00.000Z', 
                  'F': 3, 'C': 2},
      'OS':18 
      'sC': {'from': '2020-03-31T20:00:00.000Z', 'to': '2020-05-29T20:00:00.000Z', 
                  'F': 25, 'C': 31}}

I would like to convert above dictionary as a dataframe as shown below in pandas.

Params     Value
teachers   49
students   289
R          3.7
holidays   165
E_from     2020-02-29
E_to       2020-03-20
E_F        3
E_C        2
OS         18
sC_from    2020-03-31
sC_to      2020-05-29
sC_F       25
sC_C       31

The df should have two columns with heading 'params' and 'value'

`pd.io.json.json_normalize(d, sep='_').loc[0] `, and the question is almost the same , I will close it by the same question asked before ~ — BENY, Aug 11 '20 at 19:24

MrNobody33 · Accepted Answer · 2020-08-11T19:39:29.360

You can use json_normalize and then tranpose the dataframe:

d = {'teachers': 49,
      'students': 289,
      'R': 3.7,
      'holidays': 165,
      'Em': {'from': '2020-02-29T20:00:00.000Z', 'to': '2020-03-20T20:00:00.000Z',
                  'F': 3, 'C': 2},
      'OS':18,
      'sC': {'from': '2020-03-31T20:00:00.000Z', 'to': '2020-05-29T20:00:00.000Z',
                  'F': 25, 'C': 31}}


df=pd.json_normalize(d, sep='_').T.reset_index().rename(columns={'index':'Params',0:'Value'})

Output:

df
Params                       Value
teachers                        49
students                       289
R                              3.7
holidays                       165
OS                              18
Em_from   2020-02-29T20:00:00.000Z
Em_to     2020-03-20T20:00:00.000Z
Em_F                             3
Em_C                             2
sC_from   2020-03-31T20:00:00.000Z
sC_to     2020-05-29T20:00:00.000Z
sC_F                            25
sC_C                            31

edited the question, pls modify your answer – Danish Aug 11 '20 at 19:35 — Danish, Aug 11 '20 at 19:35
@Danish - You're welcome! Happy coding! – MrNobody33 Aug 11 '20 at 19:42 — MrNobody33, Aug 11 '20 at 19:42

Convert a dictionary of dictionary into a row wise dataframe in pandas

1 Answers1

Linked