如何高效获取长格式赛事数据中各队最近胜负场次的GAME_ID

我有一个长格式的赛事数据DataFrame，结构如下：

GAME_DATE_EST GAME_ID  TEAM_ID    WIN  
   <date>        <int>    <int>      <lgl>
 1 2015-06-16    41400406 1610612739 FALSE
 2 2015-06-16    41400406 1610612744 TRUE 
 3 2015-06-14    41400405 1610612744 TRUE 
 4 2015-06-14    41400405 1610612739 FALSE
 5 2015-06-11    41400404 1610612739 FALSE
 6 2015-06-11    41400404 1610612744 TRUE 
 7 2015-06-09    41400403 1610612739 TRUE 
 8 2015-06-09    41400403 1610612744 FALSE
 9 2015-06-07    41400402 1610612744 FALSE
10 2015-06-07    41400402 1610612739 TRUE 

需要为每行数据添加新列，分别存储对应球队最近一次获胜、失利的GAME_ID。目前通过自定义函数结合rowwise()实现，但执行效率极低：

get_most_recent_win = function(df, team_id, date) {
  temp = subset(df, WIN & TEAM_ID == team_id & GAME_DATE_EST < date,
                select = c("GAME_ID", "GAME_DATE_EST"))
  
  if (nrow(temp) > 0) {
    return(temp[which.max(temp$GAME_DATE_EST), "GAME_ID"])
  } else {
    return(NA)
  }
}

games_longer[1:50, ] %>%
  rowwise() %>% 
  mutate(most_recent_win = get_most_recent_win(., TEAM_ID, GAME_DATE_EST)) %>% 
  select(GAME_DATE_EST, GAME_ID, most_recent_win, TEAM_ID, WIN)

测试数据：

structure(list(GAME_DATE_EST = structure(c(16602, 16602, 16600, 
16600, 16597, 16597, 16595, 16595, 16593, 16593, 16590, 16590, 
16582, 16582, 16581, 16581, 16580, 16580, 16579, 16579), class = "Date"), 
    GAME_ID = c(41400406L, 41400406L, 41400405L, 41400405L, 41400404L, 
    41400404L, 41400403L, 41400403L, 41400402L, 41400402L, 41400401L, 
    41400401L, 41400315L, 41400315L, 41400304L, 41400304L, 41400314L, 
    41400314L, 41400303L, 41400303L), TEAM_ID = c(1610612739L, 
    1610612744L, 1610612744L, 1610612739L, 1610612739L, 1610612744L, 
    1610612739L, 1610612744L, 1610612744L, 1610612739L, 1610612744L, 
    1610612739L, 1610612744L, 1610612745L, 1610612739L, 1610612737L, 
    1610612745L, 1610612744L, 1610612739L, 1610612737L), WIN = c(FALSE, 
    TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, 
    TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, 
    FALSE)), row.names = c(NA, -20L), class = c("tbl_df", "tbl", 
"data.frame"))

核心思路是按球队分组+日期排序+向量化填充/窗口操作，彻底避免逐行循环，利用dplyr/tidyr的底层优化提升效率。

完整实现代码 #

library(dplyr)
library(tidyr)

# 加载测试数据（替换为你的实际数据）
games_longer <- structure(list(GAME_DATE_EST = structure(c(16602, 16602, 16600, 
16600, 16597, 16597, 16595, 16595, 16593, 16593, 16590, 16590, 
16582, 16582, 16581, 16581, 16580, 16580, 16579, 16579), class = "Date"), 
    GAME_ID = c(41400406L, 41400406L, 41400405L, 41400405L, 41400404L, 
    41400404L, 41400403L, 41400403L, 41400402L, 41400402L, 41400401L, 
    41400401L, 41400315L, 41400315L, 41400304L, 41400304L, 41400314L, 
    41400314L, 41400303L, 41400303L), TEAM_ID = c(1610612739L, 
    1610612744L, 1610612744L, 1610612739L, 1610612739L, 1610612744L, 
    1610612739L, 1610612744L, 1610612744L, 1610612739L, 1610612744L, 
    1610612739L, 1610612744L, 1610612745L, 1610612739L, 1610612737L, 
    1610612745L, 1610612744L, 1610612739L, 1610612737L), WIN = c(FALSE, 
    TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, 
    TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, 
    FALSE)), row.names = c(NA, -20L), class = c("tbl_df", "tbl", 
"data.frame"))

# 处理逻辑
result <- games_longer %>%
  # 按球队分组，按比赛日期升序排序
  group_by(TEAM_ID) %>%
  arrange(GAME_DATE_EST, .by_group = TRUE) %>%
  # 添加最近获胜的GAME_ID
  mutate(
    # 临时列：仅胜场保留当前GAME_ID，其余为NA
    temp_win = ifelse(WIN, GAME_ID, NA),
    # 向下填充，将最近的胜场ID传递给后续所有行
    temp_win_filled = fill(temp_win, .direction = "down") %>% pull(temp_win),
    # 后移一位，确保取的是当前比赛之前的最近胜场
    most_recent_win = lag(temp_win_filled)
  ) %>%
  # 添加最近失利的GAME_ID（逻辑与获胜一致，仅条件取反）
  mutate(
    temp_loss = ifelse(!WIN, GAME_ID, NA),
    temp_loss_filled = fill(temp_loss, .direction = "down") %>% pull(temp_loss),
    most_recent_loss = lag(temp_loss_filled)
  ) %>%
  # 删除临时列，取消分组
  select(-starts_with("temp_")) %>%
  ungroup() %>%
  # 恢复原数据的行顺序（可选，若不需要可删除）
  arrange(row.names(games_longer))

# 查看结果
print(result)

关键逻辑说明 #

1. 分组排序：按球队分组后按日期升序排列，确保同一球队的比赛按时间顺序处理，为后续的填充操作奠定基础。
2. 胜场/负场标记：用ifelse生成临时列，仅保留对应结果（胜/负）的GAME_ID，其余为NA。
3. 向下填充：fill()函数会将最近的非NA值向下传递，让每一行都能获取到截止到当前行的最近胜/负场ID。
4. 滞后偏移：lag()将填充后的结果后移一位，避免取到当前比赛的结果，确保获取的是当前比赛之前的最近记录。

效率优势 #

• 原方法用rowwise()逐行调用自定义函数，时间复杂度为O(n²)，数据量越大效率越低。
• 新方法采用向量化操作，时间复杂度为O(n log n)（主要来自排序），底层由C++实现，处理十万级以上数据时效率提升明显。

内容的提问来源于stack exchange，提问作者Max J.