Pandas quantile on a multi-level columns MultiIndex groupby object with a list of q's

user_8791366 0
Viewed 622

I have a Pandas df with MultiIndex column-labels like this:

in:

import pandas as pd  
import numpy as np

np.random.seed(123)
df = pd.DataFrame(np.random.randint(100,size=(3, 4)),columns = pd.MultiIndex.from_product([['exp0','exp1'],['rnd0','rnd1']],names=['experiments','rnd_runs']))

out:

experiments exp0      exp1     
rnd_runs    rnd0 rnd1 rnd0 rnd1
0             66   92   98   17
1             83   57   86   97
2             96   47   73   32

I would like to have multiple quantile computations (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.groupby.DataFrameGroupBy.quantile.html).

I can do it for a single quantile calculation:

in:

df.groupby(axis='columns',level='experiments').quantile(q=0.1)

out:

experiments  exp0  exp1
0            68.6  25.1
1            59.6  87.1
2            51.9  36.1

But using a list of q's upsets Pandas:

in:

df.groupby(axis='columns',level='experiments').quantile(q=[0.1,0.9])

out:

ValueError                                Traceback (most recent call last)
<ipython-input-54-86a5c92468f5> in <module>
----> 1 df.groupby(axis='columns',level='experiments').quantile(q=[0.1,0.9])

~\AppData\Local\Continuum\anaconda3\envs\suite2p\lib\site-packages\pandas\core\groupby\groupby.py in quantile(self, q, interpolation)
   1949 
   1950             # reorder rows to keep things sorted
-> 1951             indices = np.arange(len(result)).reshape([len(q), self.ngroups]).T.flatten()
   1952             return result.take(indices)
   1953 

ValueError: cannot reshape array of size 6 into shape (2,2)

Can you explain why and help me with the right syntax?

pandas-groupby multi-index pandas python
2 Answers

Using DataFrame.T transpose the dataframe and use DataFrame.groupby on level=0 then use groupby.quantile and specify the quantile values to calculate:

df1 = df.T.groupby(level=0).quantile([.1, .9]).T

Result:

print(df1)
experiments  exp0        exp1      
              0.1   0.9   0.1   0.9
0            68.6  89.4  25.1  89.9
1            59.6  80.4  87.1  95.9
2            51.9  91.1  36.1  68.9
user_128331660

Try stacking first and then find out the quantile.

df.stack().groupby(level=0).quantile(q=[0.1,0.9])

        exp0    exp1
0   0.1 80.5    53.7
    0.9 84.5    59.3
1   0.1 63.7    55.9
    0.9 85.3    87.1
2   0.1 5.7     33.2
    0.9 43.3    82.8

After stacking (using .stack()):

df.stack():

experiments exp0    exp1
rnd_runs        
0   rnd0    85  60
    rnd1    80  53
1   rnd0    88  91
    rnd1    61  52
2   rnd0    48  89
    rnd1    1   27

Then groupby level 0. and perform aggregation.

user_66603730
Related