how to sort a group by with aggregate in pandas

user_19690879 0
Viewed 52

I have a dataset of books, i am grouping it and using aggregations

df_books.groupby('Author').agg({'Reviews':['min','max'], 'User Rating':'sum' })

giving me this output

Reviews     min max     User Rating sum
Author          
Abraham Verghese    4866    4866    9.2
Adam Gasiewski  3113    3113    4.4
Adam Mansbach   9568    9568    4.8
Adir Levy   8170    8170    4.8
Admiral William H. McRaven  10199   10199   4.7
... ... ... ...
Walter Isaacson 3014    7827    13.7
William Davis   7497    7497    8.8
William P. Young    19720   19720   9.2
Wizards RPG Team    16990   16990   14.4
Zhi Gang Sha    37  220 9.2

I need order by 'User Rating' sum

pandas dataframe python
1 Answers

I assume your original table looks something like this:

                      Author Reviews UserRating
0           Abraham_Verghese    4866        9.2
1             Adam_Gasiewski    3113        4.4
2              Adam_Mansbach    9568        4.8
3                  Adir_Levy    8170        4.8
4  Admiral_William_H.McRaven   10199        4.7
5            Walter_Isaacson    3014       13.7
6              William_Davis    7497        8.8
7            William_P.Young   19720        9.2
8           Wizards_RPG_Team   16990       14.4
9               Zhi_Gang_Sha      37        9.2

Then I will do the groupby(), followed by droplevel() and reset_index() to level out the column headers, then sort_values() by the column you want

df = df.groupby('Author')\
       .agg({'Reviews':['min','max'], 'UserRating':'sum' })\
       .droplevel(1,1)\
       .reset_index()\
       .sort_values(by='UserRating', ascending=False)
df.columns = ['Author', 'ReviewsMin', 'ReviewsMax', 'UserRatingSum']
print(df)

Output:

                      Author ReviewsMin ReviewsMax  UserRatingSum
8           Wizards_RPG_Team      16990      16990           14.4
5            Walter_Isaacson       3014       3014           13.7
0           Abraham_Verghese       4866       4866            9.2
7            William_P.Young      19720      19720            9.2
9               Zhi_Gang_Sha         37         37            9.2
6              William_Davis       7497       7497            8.8
2              Adam_Mansbach       9568       9568            4.8
3                  Adir_Levy       8170       8170            4.8
4  Admiral_William_H.McRaven      10199      10199            4.7
1             Adam_Gasiewski       3113       3113            4.4
user_123706870
Related