Get max count of Pandas Groupby objects-CodePudding

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.

CodePudding user response：

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, False]).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