Calculate value-wise mean and value-wise sum among groups in Pandas groupby

user_14448109 0
Viewed 59

Let's say I have a dataframe like this:

cluster A     B     C
  a     1     2     3
  a     10    20    30
  a     100   200   300
  b     4     5     6
  b     40    50    60
  b     400   500   600
  c     7     8     9
  c     70    80    90
  c     700   800   900

I'd like to firstly group the dataframe by cluster, then calculate the value-wise mean for column A and B, and calculate the value-wise sum for column C among the groups.

Expected result:

cluster A     B     C
  d     4     5     18
  d     40    50    180
  d     400   500   1800

Thanks in advance.

pandas-groupby pandas python
1 Answers

you can groupby the cumcount of the cluster and then perform the aggregate functions:

s = df.groupby('cluster').cumcount()
df = df.groupby(s).agg({'A':'mean', 'B':'mean', 'C':'sum'})
df.insert(0,'cluster','d')
df
Out[1]: 
  cluster    A    B     C
0       d    4    5    18
1       d   40   50   180
2       d  400  500  1800
user_63667700
Related