Getting the max/mode of groupby.size() results

Question

I have a dataset containing "Season - League name - home team winner"

I want to get the most winning team in each league separately for every season; So I did a group by and applied the size function and got this dataset which have the number of each teams home wins per league per season.

When I applied the max function, I managed to get the number of home wins for the most winning team per season per league indeed.

However, I want to fetch the name of the team with the most wins per season per league, not its number of wins.

Any smarter, different way to address this ?

Answer 1

This question is really a duplicate of this Stack Overflow post. The most intuitive solution is to use pd.Series.mode in groupby but the fastest solution is to drop duplicates of a value_counts result.

counts = df.value_counts(['season', 'league_name', 'home_winner'])
result = counts[~counts.droplevel('home_winner').index.duplicated()]

A toy example / output:

df = pd.DataFrame({
    'season': [1, 1, 1, 1, 1, 1], 
    'league_name': [*'AAABBB'], 
    'home_winner':[*'abaded']
})

counts = df.value_counts(['season', 'league_name', 'home_winner'])
result = counts[~counts.droplevel('home_winner').index.duplicated()]
print(result)

season  league_name  home_winner
1       A            a              2
        B            d              2

Answer 2

def get_max(g):
    g.count().sort_values(by='count', ascending =False).loc[0,:]

Groupby().apply(get_max)