Applying Cumulative Average Function to a Grouped Object

Question

I have a DataFrame df where each record is a football game. Teams will appear more than once. I need to calculate some kind of moving average for each team (well, not exactly the moving average of the letter).

  date home away score_h score_a 166 2013-09-01 Fulham Chelsea 0 0 167 2013-09-03 Arsenal Everton 0 2 164 2013-09-05 Arsenal Swansea 5 1 165 2013-09-06 Fulham Norwich 0 1 163 2013-09-18 Arsenal Swansea 0 0 

What I need to calculate is the average score for each team (at home and away).

For brevity, let's just make a home column:

 grouped = df.groupby('home') grouped = grouped.sort_index(by='date') # rows inside groups must be in asc order 

This leads to:

  date home away score_h score_a home Arsenal 167 2013-09-03 Arsenal Everton 0 2 164 2013-09-05 Arsenal Swansea 5 1 163 2013-09-18 Arsenal Swansea 0 0 Fulham 166 2013-09-01 Fulham Chelsea 0 0 165 2013-09-06 Fulham Norwich 0 1 

The question starts here.

Now I need to calculate the moving average for the teams. Let me do this manually for a group named Arsenal . At the end of this, we must complete with two additional columns, let me name them: rmean_h and rmean_a . The first entry in group ( 167 ) has grades 0 and 2 . rmean of them are just 0 and 2 respectively. For the second record in group ( 164 ), rmeans will be (0+5)/2 = 2.5 and (2+1) / 2 = 1.5 , and for the third record, (0+5+0)/3 = 1.66 and (2+1+0)/3 = 1 .

Now our DataFrame will look like this:

  date home away score_h score_a rmean_h rmean_a home Arsenal 167 2013-09-03 Arsenal Everton 0 2 0 2 164 2013-09-05 Arsenal Swansea 5 1 2.5 1.5 163 2013-09-18 Arsenal Swansea 0 0 1.66 1 Fulham 166 2013-09-01 Fulham Chelsea 0 0 165 2013-09-06 Fulham Norwich 0 1 

I want to do these calculations for my data, do you have any suggestions?

Answer 1

You can apply expanding_mean (see docs ) for each group:

 grouped = df.sort(columns='date').groupby('home') grouped['score_h'].apply(pd.expanding_mean)