python pandas simple pivot table sum count

Question

I'm trying to identify the best way to make a simple pivot on my data:

import pandas    
dfn = pandas.DataFrame({
    "A" : [ 'aaa', 'bbb', 'aaa', 'bbb' ],
    "B" : [     1,    10,     2,   30  ],
    "C" : [     2,     0,     3,   20  ] })

The output I would like to have is a dataframe, grouped by A, that sum and count values of B and C, and names have to be exactly (Sum_B, Sum_C, Count), as following:

A   Sum_B  Sum_C  Count
aaa    3      5       2
bbb   50     20       2

What is the fastest way to do this?

Answer 1

you can use .agg() function:

In [227]: dfn.groupby('A').agg({'B':sum, 'C':sum, 'A':'count'}).rename(columns={'A':'count'})
Out[227]:
      B  count   C
A
aaa   3      2   5
bbb  40      2  20

or with reset_index():

In [239]: dfn.groupby('A').agg({'B':sum, 'C':sum, 'A':'count'}).rename(columns={'A':'count'}).reset_index()
Out[239]:
     A   B  count   C
0  aaa   3      2   5
1  bbb  40      2  20

PS Here is a link to examples provided by @evan54