finding highest goals scoring teams from each season through groupby pandas

user_17108206 0
Viewed 85

i have data like this:

    id  season      team                 wins   losses  goals
0   0   2006-2007   Manchester United    28     5       83
1   1   2006-2007   Chelsea              24     3       64  
2   2   2006-2007   Liverpool            20     10      57
.
.
238 238 2017-2018   Stoke City           7      19      35  
239 239 2017-2018   West Bromwich Albion 6      19      31

i am trying to find highest goals scoring teams from each season.

target output:

        team                season              
        
        Manchester United   2006-2007           83
        
        Manchester United   2007-2008           80
        
        Liverpool           2008-2009           77
        .
        .
        Manchester City     2017-2018           106

I have tried,

df.groupby(['team','season'])['goals'].sum().sort_values(ascending = False).head(10)

team               season   
Manchester City    2017-2018    106
Chelsea            2009-2010    103
Manchester City    2013-2014    102
Liverpool          2013-2014    101
Manchester City    2011-2012     93
Manchester United  2011-2012     89
Tottenham Hotspur  2016-2017     86
Manchester United  2012-2013     86
                   2009-2010     86
Chelsea            2016-2017     85
Name: goals, dtype: int64

this gives me the overall highest goals scored by a team in all seasons. Year repeats here, I don't want years to be repeated.

pandas-groupby pandas dataframe python
1 Answers

If you add more data, this can be verified. This won't work if two teams in one season score equally high goals.

df.sort_values('goals', ascending = False).groupby('season').first()

            id               team  wins  losses  goals
season
2006-2007    0  Manchester United    28       5   83.0
2017-2018  238         Stoke City     7      19   35.0
user_121334340
Related