Get max count of Pandas Groupby objects

user_11519317 0
Viewed 228

This line:

gf = df.groupby(['weakness','type']).size()

gives the following output:

weakness  type    

fighting  normal       6
fire      bug          1
          flying       1
          grass       11
flying    fighting     3
grass     rock         1
          water        1
ground    electric     1
poison    fairy        3
rock      fire         1
water     fire         9
          ground       4
          rock         4
dtype: int64

But how to get only the max 'type' of a certain 'weakness' as below. For counts having the same value, alphabetical order will be considered.

weakness type count

fighting normal   6
fire     grass    11
flying   fighting 3
grass    rock     1 ......etc

What I tried

First I tried to convert the groupobject to a dataframe by:

gdf = gf.groupby(['weakness','type']).size().reset_index(name='count')

Then I tried:

gdf.groupby(["weakness","type"]).count().sort_values("count").groupby(level=0).tail(1)

which also didn't give the correct output. Any help will be appreciated.

pandas-groupby pandas dataframe python
1 Answers

You can sort the dataframe by count and then remove any duplicates, this will keep the first (and thus maximum value) for each weakness.

df.sort_values('count', ascending=False).drop_duplicates(['weakness'])

To make sure that the types are in alphabetical order, you can add type to the sort as well:

df.sort_values(['count', 'type'], ascending=[False, True]).drop_duplicates(['weakness'])

Result:

    weakness      type  count
3       fire     grass     11
10     water      fire      9
0   fighting    normal      6
8     poison     fairy      3
4     flying  fighting      3
7     ground  electric      1
9       rock      fire      1
5      grass      rock      1
user_75795470
Related