How to transform a nested dictionary to pandas DataFrame

Question

I have the following nested dictionary dict:

{
 'x1': 
   {
    'k1': 
      {
        'first_col': 'Date',
        'col_type': pandas._libs.tslibs.timestamps.Timestamp,
        'col_min_val': Timestamp('2017-12-04 00:00:00') 
      },
     'k2': 
      {
        'first_col': 'Date',
        'col_type': pandas._libs.tslibs.timestamps.Timestamp,
        'col_min_val': Timestamp('2018-02-02 00:00:00')
      }
    }
 'x2': 
   {
     'k1': 
       {
         'first_col': 'Date',
         'col_type': pandas._libs.tslibs.timestamps.Timestamp,
         'col_min_val': Timestamp('2017-12-04 05:00:00') 
       }
   }
}

I need to get this pandas DataFrame:

col1   col2   first_col   col_type                                   col_min_val
x1     k1     Date        pandas._libs.tslibs.timestamps.Timestamp   Timestamp('2017-12-04 00:00:00')
x1     k2     Date        pandas._libs.tslibs.timestamps.Timestamp   Timestamp('2018-02-02 00:00:00')
x2     k1     Date        pandas._libs.tslibs.timestamps.Timestamp   Timestamp('2017-12-04 05:00:00')

This is what I tried, but the result does not coincide with the expected one:

pd.DataFrame(dict).stack().reset_index()

jezrael · Accepted Answer · 2019-04-03T09:20:18.397

Create list of dictionaries with append keys for both levels and call DataFrame constructor:

EDIT:

Dont use dict for variable name, because python code word (builtin).

#https://stackoverflow.com/a/34757497
L = [dict(d[i][j], **{'col1': i,'col2': j})
       for i in d.keys() 
       for j in d[i].keys()]

df = pd.DataFrame(L)
print (df)

  col1 col2         col_min_val                                  col_type  \
0   x1   k1 2017-12-04 00:00:00  pandas._libs.tslibs.timestamps.Timestamp   
1   x1   k2 2018-02-02 00:00:00  pandas._libs.tslibs.timestamps.Timestamp   
2   x2   k1 2017-12-04 05:00:00  pandas._libs.tslibs.timestamps.Timestamp   

  first_col  
0      Date  
1      Date  
2      Date

How to transform a nested dictionary to pandas DataFrame

1 Answers1