Calculating cumulative mean of recent observations

Question

My dataset has as features: players IDs, weeks and points.

I want to calculate the mean of points for previous weeks, but not all past weeks, just to the last 5 or less (if the current week is smaller than 5).

Example: For player_id = 5, week = 7, the result will be the average of POINTS for player_id = 5 and weeks 2, 3, 4, 5 and 6.

The following code already does the average for all previous week, so I need an adaptation to make it for just 5 previous week.

player_id<-c(rep(1,30),rep(2,30),rep(3,30),rep(4,30),rep(5,30))
week<-1:30
points<-round(runif(150,1,10),0) 
mydata<- data.frame(player_id=player_id,week=rep(week,5),points)


mydata<-mydata %>% 
        group_by(player_id) %>%    # the group to perform the stat on
        arrange(week) %>%          # order the weeks within each group
        mutate(previous_mean = cummean(points) ) %>% # for each week get the 
cumulative mean
        mutate(previous_mean = lag(previous_mean) ) %>% # shift cumulative 
mean back one week
        arrange(player_id) # sort by player_id

Answer 1

You can use slice to select just the last 5 weeks for each group. Try this:

player_id<-c(rep(1,30),rep(2,30),rep(3,30),rep(4,30),rep(5,30))
week<-1:30
points<-round(runif(150,1,10),0) 
mydata<- data.frame(player_id=player_id,week=rep(week,5),points)

library(dplyr)

mydata <- mydata %>% 
    group_by(player_id) %>%    # the group to perform the stat on
    arrange(week) %>% # order the weeks within each group
    slice( (n()-4):n() ) %>%  # "slice" the last 5 rows (weeks) of every group
    mutate(previous_mean = cummean(points) ) %>% # for each week get the cumulative mean
mutate(previous_mean = lag(previous_mean) ) %>% # shift cumulative mean back one week
arrange(player_id) # sort by player_id

The line

slice( (n()-4):n() )

selects rows within the range [(last row - 4) : last row], for each group

EDIT: To avoid trouble when the current week is less than 5, use an ifelse statement to validate:

mydata %>% 
    group_by(player_id) %>%    # the group to perform the stat on
    arrange(week) %>% # order the weeks within each group
    slice(ifelse(n() < 5, 1:n(), n()-4):n()) %>%  # "slice" the last 5 rows (weeks) of every group
    mutate(previous_mean = cummean(points) ) %>% # for each week get the cumulative mean
    mutate(previous_mean = lag(previous_mean) ) %>% # shift cumulative mean back one week
    arrange(player_id) # sort by player_id

Answer 2

HAVB's approach is great, but depending on what you want, here is another. This approach is adapted from this answer to a different question, but changed for your circumstances:

library(dplyr)
library(zoo)
# set the seed for reproducibility
set.seed(123)
player_id<-c(rep(1,30),rep(2,30),rep(3,30),rep(4,30),rep(5,30))
week<-1:30
points<-round(runif(150,1,10),0) 
mydata<- data.frame(player_id=player_id,week=rep(week,5),points)

roll_mean <- function(x, k) {
    result <- rollapplyr(x, k, mean, partial=TRUE, na.rm=TRUE)
    result[is.nan(result)] <- NA
    return( result )
}

mydata<- data.frame(player_id=player_id,week=rep(week,5),points)

mydata<-mydata %>% 
    group_by(player_id) %>%
    arrange(week) %>%
    mutate(rolling_mean = roll_mean(x=lag(points), k=5) ) %>%
    arrange(player_id)

Then we can look at a subset to show it worked:

mydata[mydata$player_id %in% 1:2 & mydata$week %in% 1:6, ]
# A tibble: 12 x 4
# Groups:   player_id [2]
   player_id  week points rolling_mean
       <dbl> <int>  <dbl>        <dbl>
 1         1     1      4           NA
 2         1     2      8     4.000000
 3         1     3      5     6.000000
 4         1     4      9     5.666667
 5         1     5      9     6.500000
 6         1     6      1     7.000000
 7         2     1     10           NA
 8         2     2      9    10.000000
 9         2     3      7     9.500000
10         2     4      8     8.666667
11         2     5      1     8.500000
12         2     6      5     7.000000

So we can see at each time t, rolling_mean for player i will be the mean of the points observations for player i at times {t - 1, ..., min(1, t - 5)}.