Pandas MultiIndexed DataFrame to nested dictionary-CodePudding

I'm running a function .agg([np.mean, np.sum]) that results in a DataFrame that looks like this:

HUC_8                         07110005    07110006    07110007
acute_human           mean    0.498878    0.491621    0.514938
                      sum   522.824218  799.375134  159.115824
chronic_human         mean    0.510916    0.490830    0.522305
                      sum   535.439663  798.090369  161.392326
overall_human         mean    0.488282    0.508226    0.498384
                      sum   521.824118  719.371134  119.115824

I'd like to convert this into a nested dictionary that looks like this:

     {'07110005': 
          {'acute_human': 
               {'mean': 0.498878,
                'sum': 522.824218},
           'chronic_human':
               {'mean': 0.510916,
                'sum': 535.439663},
           'overall_human':
               {'mean': 0.510916,
                'sum': 535.439663}},
      '0711006':
  ...

Simply using df.to_dict() gets me close, but not quite there:

    {'07110005': 
        {('acute_human', 'mean'): 0.4990235526721262, 
         ('acute_human', 'sum'): 522.9766832003883....

Is there a straightforward way to automatically nest the second level of the index without an expensive iteration?

CodePudding user response：

We can do with unstack then groupby to create the multi-layers dict

d = df.unstack().T.groupby(level=0).apply(lambda x: x.xs(x.name).to_dict()).to_dict()
Out[502]: 
{'07110005': {'acute_human': {'mean': 0.498878, 'sum': 522.824218},
  'chronic_human': {'mean': 0.510916, 'sum': 535.439663},
  'overall_human': {'mean': 0.488282, 'sum': 521.824118}},
 '07110006': {'acute_human': {'mean': 0.491621, 'sum': 799.375134},
  'chronic_human': {'mean': 0.49083, 'sum': 798.090369},
  'overall_human': {'mean': 0.508226, 'sum': 719.371134}},
 '07110007': {'acute_human': {'mean': 0.514938, 'sum': 159.115824},
  'chronic_human': {'mean': 0.522305, 'sum': 161.392326},
  'overall_human': {'mean': 0.498384, 'sum': 119.115824}}}