3 level nested dictionary to Pandas Dataframe

user_9731167 0
Viewed 66

I have a dictionary in the form:

dict = {'A1' : {'B1' : {'Average' : 0 , 'Max' : 0, 'Min' : 0},
                'B2' : {'Average' : 0 , 'Max' : 0, 'Min' : 0},
                'B3' : {'Average' : 0 , 'Max' : 0, 'Min' : 0}},
        'A2' : {'B1' : {'Average' : 0 , 'Max' : 0, 'Min' : 0},
                'B2' : {'Average' : 0 , 'Max' : 0, 'Min' : 0},
                'B3' : {'Average' : 0 , 'Max' : 0, 'Min' : 0}}}

and I would like to get a data frame that looks like this:

               B1               B2               B3
        Average Max Min  Average Max Min  Average Max Min
Region
  A1       0     0   0      0     0   0      0     0   0 
  A2       0     0   0      0     0   0      0     0   0

I have tried several answers but none of them give me the data frame the way I want it. Can anybody please help? I'm quite stuck. Thanks.

pandas python
1 Answers

Just another way of doing it:

df = pd.DataFrame(d).T #choosing d as a name for dict because dict is a keyword
Bcols = df.columns
df = pd.concat([df[col].apply(pd.Series) for col in df.columns], axis=1)
df.columns = pd.MultiIndex.from_product([Bcols, df.columns]).drop_duplicates()
df.rename_axis('Region', axis=0, inplace=True)

print(df):

              B1              B2              B3        
       Average Max Min Average Max Min Average Max Min
Region                                                
A1           0   0   0       0   0   0       0   0   0
A2           0   0   0       0   0   0       0   0   0
user_14103030
Related