Conditionally Rollmean based on another column value

Question

I'm trying to take the moving average of a players fantasy points, based on their past performance against the same hand pitcher, over the past 3 games.

FP <- data.frame(player = c(rep("A",10), rep("B",10), rep("C",10)),
                pitcher_hand = rep(c("R","L"),15), 
                fantasy_points = runif(30, min = 0, max = 25))

I know i can use rollapplyr from (zoo) to take moving averages, but here I need the conditional moving average, based on another column. For instance, the new column moving average for row 7 would be the average fantasy points for row 5, 3, and 1, since it was against the same handed pitcher. I've tried:

FP <- FP %>%
 group_by(player) %>%
 mutate(FP_L3 = rollapplyr(.,list(-(3:1)),function(x) mean(x[x[['pitcher_hand']]==pitcher_hand]),fill=NA))

How can this be done? I can do this in a big loop, iterating through every row in my dataframe and searching for correct matches, however I want to avoid this since my dataframe is quite large.

score 0 · Accepted Answer · answered Jun 12 '21 at 01:08

Include pitcher_hand in group_by -

library(dplyr)

FP %>%
  group_by(player, pitcher_hand) %>%
  mutate(FP_L3 = lag(rollmeanr(fantasy_points, 3, fill = NA))) %>%
  ungroup

#  player pitcher_hand fantasy_points FP_L3
#   <chr>  <chr>                 <dbl> <dbl>
# 1 A      R                     12.7   NA  
# 2 A      L                      6.48  NA  
# 3 A      R                     10.7   NA  
# 4 A      L                     18.1   NA  
# 5 A      R                     16.3   NA  
# 6 A      L                      7.92  NA  
# 7 A      R                      5.62  13.2
# 8 A      L                     22.5   10.8
# 9 A      R                     14.8   10.9
#10 A      L                      5.32  16.2
# … with 20 more rows

Conditionally Rollmean based on another column value

1 Answers1