Groupby, Sum And Count To One Table

I have a dataframe below df=pd.DataFrame({'A':np.random.randint(1,10,9),'B':np.random.randint(1,10,9),'C':list('abbcacded')}) A B C 0 9 6 a 1 2 2 b 2 1 9 b 3 8 2

Solution 1:

You can do this using a single groupby with

res = df.groupby(df.C).agg({'A': 'sum', 'B': {'sum': 'sum', 'count': 'count'}})

res.columns = ['A_sum', 'B_sum', 'count']

Solution 2:

One option is to count the size and sum the columns for each group separately and then join them by index:

df.groupby("C")['A'].agg({"number": 'size'}).join(df.groupby('C').sum())

    number  A   B
# C         
# a     2   11  8
# b     2   14  12
# c     2   8   5
# d     2   11  12
# e     1   7   2

You can also do df.groupby('C').agg(["sum", "size"]) which gives an extra duplicated size column, but if you are fine with that, it should also work.