Applying different aggregate functions when using pivot_table

Question

I have this sample:

import pandas as pd
import numpy as np
dic = {'name':
       ['j','c','q','j','c','q','j','c','q'],
       'foo or bar':['foo','bar','bar','bar','foo','foo','bar','foo','foo'], 
       'amount':[10,20,30, 20,30,40, 200,300,400]}
x = pd.DataFrame(dic)
x
pd.pivot_table(x, 
               values='amount', 
               index='name', 
               columns='foo or bar', 
               aggfunc=[np.mean, np.sum])

It returns this:

I'd like to just have the highlighted columns. Why can I not specify tuples in the aggfunc argument like this?

pd.pivot_table(x, 
               values='amount', 
               index='name', 
               columns='foo or bar', 
               aggfunc=[(np.mean, 'bar'), (np.sum, 'foo')])

Is using .ix like here (define aggfunc for each values column in pandas pivot table) the only option?

this question is related: stackoverflow.com/questions/20119414/… — whytheq
– whytheq, Commented Mar 20, 2016 at 13:58

MaxU - stand with Ukraine · Accepted Answer · 2016-03-20 18:03:44Z

4

i think you can't specify tuples for the aggfunc parameter, but you can do something like this:

In [259]: p = pd.pivot_table(x,
   .....:                values='amount',
   .....:                index='name',
   .....:                columns='foo or bar',
   .....:                aggfunc=[np.mean, np.sum])

In [260]: p
Out[260]:
           mean       sum
foo or bar  bar  foo  bar  foo
name
c            20  165   20  330
j           110   10  220   10
q            30  220   30  440

In [261]: p.columns = ['{0[0]}_{0[1]}'.format(col) if col[1] else col[0] for col in p.columns.tolist()]

In [262]: p.columns
Out[262]: Index(['mean_bar', 'mean_foo', 'sum_bar', 'sum_foo'], dtype='object')

In [264]: p[['mean_bar','sum_foo']]
Out[264]:
      mean_bar  sum_foo
name
c           20      330
j          110       10
q           30      440

edited Mar 20, 2016 at 18:03

answered Mar 20, 2016 at 16:53

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anton Protopopov · Accepted Answer · 2016-03-20 18:59:33Z

To be able to do that as in the answer you provided you need to create appropriate columns for that. You could do that with:

x['foo'] = x.loc[x['foo or bar'] == 'foo', 'amount']
x['bar'] = x.loc[x['foo or bar'] == 'bar', 'amount']

In [81]: x
Out[81]: 
   amount foo or bar name    foo    bar
0      10        foo    j   10.0    NaN
1      20        bar    c    NaN   20.0
2      30        bar    q    NaN   30.0
3      20        bar    j    NaN   20.0
4      30        foo    c   30.0    NaN
5      40        foo    q   40.0    NaN
6     200        bar    j    NaN  200.0
7     300        foo    c  300.0    NaN
8     400        foo    q  400.0    NaN

And then you could use following:

In [82]: x.pivot_table(values=['foo','bar'], index='name', aggfunc={'bar':np.mean, 'foo':sum})
Out[82]: 
        bar    foo
name              
c      20.0  330.0
j     110.0   10.0
q      30.0  440.0

Collectives™ on Stack Overflow

Applying different aggregate functions when using pivot_table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related