12

How can I specify a function in the aggfunc=[] using pandas.pivot_table so that I get the first observation of every group just like the result I get running groupby().first()?

Zhongjie Zhang
  • 123
  • 1
  • 1
  • 7

1 Answers1

14

You can use aggfunc='first':

In [11]: df = pd.DataFrame([[1, 2, "A"], [1, 4, "A"], [5, 6, "B"]])

In [12]: df
Out[12]:
   0  1  2
0  1  2  A
1  1  4  A
2  5  6  B

In [13]: df.pivot_table(index=0, values=1, columns=2)  # default aggfunc is 'mean'
Out[13]:
2   A   B
0
1   3 NaN
5 NaN   6

In [14]: df.pivot_table(index=0, values=1, columns=2, aggfunc='first')
Out[14]:
2   A   B
0
1   2 NaN
5 NaN   6

I'm not sure if there's a complete list of these strings for aggfunctions in the docs (they also work for groupbys), I will take a look...

Andy Hayden
  • 359,921
  • 101
  • 625
  • 535
  • I thought there was a dictionary in the source mapping 'mean' to `np.mean` etc, although in retrospect I think that aggfunc="x" simply calls .x() on each subgroup.... Hmm. – Andy Hayden Feb 06 '15 at 08:05
  • Hi, thanks for the answer. Your code works. However, it works the same way as using groupby().first(). My objective is to have 'first' and other aggfunc like numpy.mean() so that I don't have to do it twice and merge the tables. Any ideas how to do that? – Zhongjie Zhang Feb 06 '15 at 08:23
  • You can pass a dictionary to `aggfunc` with what functions you want to apply for each column like this: `df.pivot_table(index=0, aggfunc={0:'mean', 2:'first'})` – Primer Feb 06 '15 at 13:53
  • @ZhongjieZhang Are you talking to me or Primer? In either case these both work, I've tested myself using pandas 0.15.2 - it works with the outputs above. What have you tried and what's failing? – Andy Hayden Feb 10 '15 at 07:03
  • 1
    Hi Andy, was talking to Primer. Really? You also tried df.pivot_table(index=0, aggfunc={0:'mean', 2:'first'})? I got some errors running it. – Zhongjie Zhang Feb 10 '15 at 08:40
  • @ZhongjieZhang That works with the example above, maybe it won't with different data or an older pandas? p.s. please consider accepting this answer if it answered your question: http://meta.stackexchange.com/a/5235 – Andy Hayden Feb 10 '15 at 09:26