How to create a multi-index from a triple-nested dictionary

November 15, 2021

{
  0 : {'acc507' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc522' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc551' : {'max' : 1, 'mean' : 2, 'min': 3} },
  
  1 : {'acc507' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc522' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc551' : {'max' : 1, 'mean' : 2, 'min': 3} },
  
  2 : {'acc507' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc522' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc551' : {'max' : 1, 'mean' : 2, 'min': 3} },
  
  3 : {'acc507' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc522' : {'max' : 1, 'mean' : 2, 'min': 3} ,
       'acc551' : {'max' : 1, 'mean' : 2, 'min': 3} }
 }

This is my data.
I would like to create a dataframe with the following multi index:

indexML = pd.MultiIndex.from_arrays(arrays=[level1,level2],names=['K-Value','Model'])


MultiIndex([(0, 'acc507'),
            (0, 'acc522'),
            (0, 'acc551'),
            (1, 'acc507'),
            (1, 'acc522'),
            (1, 'acc551'),
            (2, 'acc507'),
            (2, 'acc522'),
            (2, 'acc551'),
            (3, 'acc507'),
            (3, 'acc522'),
            (3, 'acc551')],
           names=['K-Value', 'Model'])

I now would like to add 3 columns, ‘max’, ‘mean’, ‘min’. How do I access those last-level values? Do I need to iterate through each dictionary or is there a way of accessing the last nest directly?

This seems like a messy/hard to work with data structure. Is there a better way of storing this type of information?

>Solution :

Try:

srs = pd.DataFrame(data).stack()
output = (pd.DataFrame(srs.tolist(), index=srs.index)
          .swaplevel()
          .rename_axis(['K-Value', 'Model']))
>>> output

                max  mean  min
K-Value Model                 
0       acc507    1     2    3
1       acc507    1     2    3
2       acc507    1     2    3
3       acc507    1     2    3
0       acc522    1     2    3
1       acc522    1     2    3
2       acc522    1     2    3
3       acc522    1     2    3
0       acc551    1     2    3
1       acc551    1     2    3
2       acc551    1     2    3
3       acc551    1     2    3