Using agg with multi index in pandas

user_662770 0
Viewed 1399

I have a multi index table in pandas that is divided by columns, like in the example below:

import pandas as pd
header = pd.MultiIndex.from_product([['loc1','loc2'],
                                     ['S1','S2']],
                                    names=['loc','S'])
df = pd.DataFrame(np.random.randint(1, high=5, size=(5,4)), 
                  index=['a','b','c','d','e'], 
                  columns=header)
print(df)

with output:

loc loc1    loc2   
S     S1 S2   S1 S2
a      4  2    2  5
b      1  4    2  4
c      2  4    2  3
d      3  4    1  2
e      4  1    3  1

I'm trying to perform actions on "loc1" and "loc2", for which I use 

df.agg({'loc1':sum, 'loc2':np.mean})

but I'm getting an error: "SpecificationError: nested renamer is not supported".

The expected output is a row with the sum of (loc1, S1) and (loc1, S2), and the mean of (loc2, S1) and (loc2, S2),in my case

  loc1    loc2  
  sum     mean    
  S1 S2   S1 S2  
  14 15    2  3

How can I do this calculation with the "agg" function? Is there any work around?

p.s. I know I can do it like in the answer Pivot table with multiple aggfunc sum and normalize one column, but it seems somehow "non-pythonic".

multi-index pandas
6 Answers

Unfortunately support for agg on columns (axis=1) is not not as complete as for rows. The solution is to do it in two agg calls.

Create the aggregates

o1 = df['loc1'].agg('sum').rename('sum').to_frame()
o2 = df['loc2'].agg('mean').rename('mean').to_frame()

Combine the aggregates

result = pd.concat([o1,o2],axis=1, keys=['loc1', 'loc2'])

Finally do some wrangling to get the data into the required format

result = result.unstack().to_frame().T

Result

  loc1       loc2     
   sum       mean     
S   S1    S2   S1   S2
0  9.0  10.0  2.4  2.4
user_22403260

You can use dictionaty comprehension with DataFrame.agg and DataFrame.stack, last concat for MultiIndex Series with Series.to_frame and transpose for one row DataFrame:

d = {'loc1':'sum','loc2':'mean'}

df1 = pd.concat({k: df[k].agg([v]).stack() for k, v in d.items()}).to_frame().T
print (df1)
  loc1       loc2     
    sum       mean     
     S1    S2   S1   S2
0  15.0  14.0  2.6  2.0
user_29010020

If I understand you correctly, you want to the sum over each row per loc. We need to specify a level and axis in our groupby:

df.groupby(level=0, axis=1).sum(axis=1)

loc      loc1      loc2
a   -0.159510  0.669699
b    0.406272  2.258626
c   -0.703832  0.274719
d   -1.453601 -0.480166
e    1.128587  0.504887

To assign it back, we can use join, since the indices stay the same:

dfn = df.join(df.groupby(level=0, axis=1).sum(axis=1))

   (loc1, S1)  (loc1, S2)  (loc2, S1)  (loc2, S2)      loc1      loc2
a   -0.540104    0.380594    0.591548    0.078151 -0.159510  0.669699
b   -0.161479    0.567751    1.392222    0.866404  0.406272  2.258626
c   -0.549657   -0.154175    0.447627   -0.172908 -0.703832  0.274719
d   -1.811309    0.357709    0.124907   -0.605073 -1.453601 -0.480166
e    2.274189   -1.145603    0.458101    0.046786  1.128587  0.504887

Note that your MultiIndex columns got flattened.

To keep your MultiIndex levels, we have to create an artificial level called sum:

dfg = df.groupby(level=0, axis=1).sum(axis=1)
dfg.columns = pd.MultiIndex.from_product([dfg.columns, ['sum']])

dfn = df.join(dfg)

loc      loc1                loc2                loc1      loc2
S          S1        S2        S1        S2       sum       sum
a   -0.540104  0.380594  0.591548  0.078151 -0.159510  0.669699
b   -0.161479  0.567751  1.392222  0.866404  0.406272  2.258626
c   -0.549657 -0.154175  0.447627 -0.172908 -0.703832  0.274719
d   -1.811309  0.357709  0.124907 -0.605073 -1.453601 -0.480166
e    2.274189 -1.145603  0.458101  0.046786  1.128587  0.504887

Finally, if you want so sort your columns by loc, use sort_index:

dfn.sort_index(axis=1)

loc      loc1                          loc2                    
S          S1        S2       sum        S1        S2       sum
a   -0.540104  0.380594 -0.159510  0.591548  0.078151  0.669699
b   -0.161479  0.567751  0.406272  1.392222  0.866404  2.258626
c   -0.549657 -0.154175 -0.703832  0.447627 -0.172908  0.274719
d   -1.811309  0.357709 -1.453601  0.124907 -0.605073 -0.480166
e    2.274189 -1.145603  1.128587  0.458101  0.046786  0.504887
user_90812670

Yes, it appears that dataframes with pd.MultiIndex and using dictionary in agg is not supported, however, here is a work around that will generate your desired ouput.

df_sum = df.agg('sum')[['loc1']].rename('sum').to_frame().stack().reorder_levels([0,2,1])
df_avg = df.agg('mean')[['loc2']].rename('mean').to_frame().stack().reorder_levels([0,2,1])
pd.concat([df_sum, df_avg]).to_frame().T

Output:

loc  loc1       loc2     
      sum       mean     
S      S1    S2   S1   S2
0    11.0  15.0  3.4  2.6
user_63615310

There is no direct way to achieve your desired output. One of an indirect way is using dict comprehension to construct an agg dictionary. After that, using this dictionary to agg and switching around columns and indices to get the desired output:

ops_dict = {'loc1':'sum', 'loc2':'mean'}
agg_dict = {(x,y): [ops_dict[x]] for x,y in df.columns}
df_agg = df.agg(agg_dict).stack([0,1]).swaplevel(0,1).sort_index(0).to_frame().T

Out[65]:
   loc1       loc2
    sum       mean
     S1    S2   S1   S2
0  14.0  15.0  2.0  3.0

Or use pd.concat with parameter keys on direct sum and mean of each locX 

df_agg = pd.concat([df[['loc1']].sum(), df[['loc2']].mean()], 
                   keys=['sum','mean']).swaplevel(0,1).to_frame().T

or

df_agg = pd.concat([df['loc1'].sum(), df['loc2'].mean()], 
                   keys=[('loc1','sum'), ('loc2','mean')]).to_frame().T

Out[67]:
loc  loc1       loc2
      sum       mean
S      S1    S2   S1   S2
0    14.0  15.0  2.0  3.0
user_101892140

We can do 

new_df = (df.stack('S')
            .groupby(level='S')
            .agg(loc1_sum = ('loc1', 'sum'), loc2_mean = ('loc2', 'mean')))
print(new_df)
    loc1_sum  loc2_mean
S                      
S1        11        2.0
S2        10        1.8

new_df.columns = pd.MultiIndex.from_tuples(map(tuple, new_df.columns.str.split('_')))
result = new_df.unstack().to_frame().T
print(result)

   loc1       loc2     
    sum       mean     
S    S1    S2   S1   S2
0  11.0  10.0  2.0  1.8
user_118842370
Related