CODEWITHSUNDEEP
pythonpandasdataframe
Mike Hunt
Mike Hunt

Reputation: 23

Rolling average Pandas for 3 week period for specific column values

I have a dataframe 'qbPast' which contains nfl player data for a season.

P   Player  Week    Team    Opp Opp Rank    Points  Def TD  Def INT Def Yds/att Year
2   QB  Kyler Murray    2   ARI MIN 14  38.10   1.8125  1.0000  6.9 2021
3   QB  Lamar Jackson   2   BAL KC  6   37.26   1.6875  0.9375  7   2021
5   QB  Tom Brady   2   TB  ATL 28  30.64   1.9375  0.7500  6.8 2021

I am attempting to create a new rolling average based on the "Points" column for each individual player for each 3 week period, for the first two weeks it should just return the points for that week and after that it should return the average for the 3 week moving period e,g Player A scores 20,30,40,30,40 the average should return 20,30,30,33.3 etc.

My attempt # qbPast['Avg'] = qbPast.groupby('Player')['Points'].rolling(3).mean().reset_index(drop=True)

The problem is it is only returning the 3 week average for all players I need it to filter by player so that it returns the rolling average for each player, the other players should not affect the rolling average.

Upvotes: 1

Views: 151

Answers (3)

pieterbons
pieterbons

Reputation: 1724

A nice way of avoiding the index problem is using groupby - transform:

qbPast['avg'] = (qbPast.
             sort_values('Week').
             groupby('Player')['Points'].
             transform(lambda g: g.rolling(3).mean()))

Upvotes: 0

luca
luca

Reputation: 146

You have to change the .reset_index(drop=True) into .reset_index(0, drop=True) so it is not mixing the players indices together.

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71687

The problem is index alignment. Don't use reset_index instead drop the zeroth index level then assign the values to a column

qbPast['Avg'] = qbPast.groupby('Player').rolling(3)['Points'].mean().droplevel(0)

Upvotes: 1

Related Questions