Groupby sum and average in pandas and make data frame

Question

I have a dataframe as shown below

ID     Score
A      20
B      60
A      40
C      50
B      100
C      60
C      40
A      10
A      10
A      70

From the above I would like to calculate the average score for each ID and total score.

Expected output:

ID    Average_score     Total_score
A     30                150
B     80                160
C     50                150

`df.groupby('ID').agg(['mean','sum'])` – anky Mar 26 '20 at 09:51 — anky, Mar 26 '20 at 09:51

score 1 · Accepted Answer · answered Mar 26 '20 at 09:52

Use named aggregation for custom columns names:

df1 = (df.groupby('ID').agg(Average_score=('Score','mean'), 
                            Total_score=('Score','sum'))
         .reset_index())
print (df1)
  ID  Average_score  Total_score
0  A             30          150
1  B             80          160
2  C             50          150

Groupby sum and average in pandas and make data frame

1 Answers1