R 中带有条件语句的聚合滚动平均值

Question

我有一个遵循以下格式的数据框。

match  team1 team2 winningTeam
1      A     D     A
2      B     E     E
3      C     F     C
4      D     C     C
5      E     B     B
6      F     A     A
7      A     D     D
8      D     A     A

我想做的是创建变量，以计算过去 x 场比赛中球队 1 和 2 的形式。例如，我想创建一个名为 team1_form_last3_matches 的变量，对于第 8 场比赛，该变量为 0.33(因为他们在过去 3 场比赛中赢了 1 场)，并且还会有一个名为 team2_form_last3_matches 的变量，在第 8 场比赛中为 0.66(因为他们赢得了过去 2 场比赛中的 3 场)。理想情况下，我希望能够指定在计算团队x_form_lasty变量和要自动创建的变量时要考虑的先前比赛数量。我已经尝试了很多使用 dplyr、动物园滚动平均函数和大量嵌套的 for/if 语句的方法。但是，我还没有完全破解它，当然也不是以一种优雅的方式。我觉得我错过了这个通用问题的简单解决方案。任何帮助将不胜感激！

干杯

千斤顶

Answer 1

这适用于 t1l3，您需要为 t2 复制它。

dat <- data.frame(match = c(1:8), team1 = c("A","B","C","D","E","F","A","D"), team2 = c("D","E","F","C","B","A","D","A"), winningTeam = c("A","E","C","C","B","A","D","A"),stringsAsFactors = FALSE)
dat$t1l3 <- c(NA,sapply(2:nrow(dat),function(i) {
df <- dat[1:(i-1),] #just previous games, i.e. excludes current game
df <- df[df$team1==dat$team1[i] | df$team2==dat$team1[i],] #just those containing T1
df <- tail(df,3) #just the last three (or fewer if there aren't three previous games)
return(sum(df$winningTeam==dat$team1[i])/nrow(df)) #total wins/total games (up to three)
}))

Answer 2

像这样的事情怎么样：

dat <- data.frame(match = c(1:8), team1 = c("A","B","C","D","E","F","A","D"), team2 = c("D","E","F","C","B","A","D","A"), winningTeam = c("A","E","C","C","B","A","D","A"))
match team1 team2 winningTeam
1     1     A     D           A
2     2     B     E           E
3     3     C     F           C
4     4     D     C           C
5     5     E     B           B
6     6     F     A           A
7     7     A     D           D
8     8     D     A           A
Allteams <- c("A","B","C","D","E","F")
# A vectorized function for you to use to do as you ask:
teamX_form_lastY <- function(teams, games, dat){
sapply(teams, function(x) {
games_info <- rowSums(dat[,c("team1","team2")] == x) + (dat[,"winningTeam"] == x)
lookup <- ifelse(rev(games_info[games_info != 0])==2,1,0)
games_won <- sum(lookup[1:games])
if(length(lookup) < games) warning(paste("maximum games for team",x,"should be",length(lookup)))
games_won/games
})
}
teamX_form_lastY("A", 4, dat)
A 
0.75 
# Has a warning for the number of games you should be using
teamX_form_lastY("A", 5, dat)
A 
NA 
Warning message:
In FUN(X[[i]], ...) : maximum games for team A should be 4
# vectorized input
teamX_form_lastY(teams = c("A","B"), games = 2, dat = dat)
A   B 
0.5 0.5 
# so you ca do all teams
teamX_form_lastY(teams = Allteams, 2, dat)
A   B   C   D   E   F 
0.5 0.5 1.0 0.5 0.5 0.0