基于日期与多列分组，计算球员最近2场每5分钟平均FGM

需求说明 #

针对数据框中的每个PLAYER_NAME，计算其最近2个game_date内的每5分钟平均FGM（投篮命中数）值。

原始数据 #

df <- structure(list(game_date = structure(c(19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19153, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19156, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159, 19159), class = "Date"), MIN = c(28.083, 40.683, 9, 40.823, 32.96, 14.5, 39.95, 43.427, 28.16, 39.7, 9.9, 42.667, 35.55, 31.45, 20.547, 12.533, 10.067, 3.933, 32.847, 37.13, 4.72, 39.625, 34.983, 14.617, 1.317, 39.703, 42.533, 16.75, 44.05, 26.155, 1.317, 44.417, 21.413, 1.317, 30.237, 1.317, 14.287, 16.067, 1.317, 1.317, 4.683, 1.317, 1.317, 1.317, 1.017, 39.215, 39.918, 41.302, 41.817, 13.05, 1.05, 38.483, 43.682, 21.667, 44, 19.767, 40.21, 16.452, 1.05, 32.623, 17.782, 15.85, 1.017, 1.05, 7.95, 1.05), FGM = c(2, 14, 0, 7, 1, 0, 7, 7, 3, 9, 2, 8, 4, 3, 6, 1, 0, 0, 3, 7, 0, 7, 3, 1, 0, 7, 12, 1, 5, 6, 0, 10, 0, 1, 4, 0, 4, 1, 0, 0, 0, 0, 0, 0, 0, 6, 12, 5, 5, 2, 0, 4, 7, 0, 12, 2, 6, 1, 0, 4, 5, 1, 0, 0, 0, 0), PLAYER_NAME = c("Al Horford", "Stephen Curry", "Nemanja Bjelica", "Klay Thompson", "Draymond Green", "Otto Porter Jr.", "Marcus Smart", "Andrew Wiggins", "Kevon Looney", "Jaylen Brown", "Gary Payton II", "Jayson Tatum", "Derrick White", "Robert Williams III", "Jordan Poole", "Grant Williams", "Payton Pritchard", "Andre Iguodala", "Al Horford", "Stephen Curry", "Nemanja Bjelica", "Klay Thompson", "Draymond Green", "Otto Porter Jr.", "Nik Stauskas", "Marcus Smart", "Andrew Wiggins", "Kevon Looney", "Jaylen Brown", "Gary Payton II", "Damion Lee", "Jayson Tatum", "Derrick White", "Luke Kornet", "Robert Williams III", "Juan Toscano-Anderson", "Jordan Poole", "Grant Williams", "Juwan Morgan", "Aaron Nesmith", "Payton Pritchard", "Jonathan Kuminga", "Moses Moody", "Sam Hauser", "Andre Iguodala", "Al Horford", "Stephen Curry", "Klay Thompson", "Draymond Green", "Otto Porter Jr.", "Nik Stauskas", "Marcus Smart", "Andrew Wiggins", "Kevon Looney", "Jaylen Brown", "Gary Payton II", "Jayson Tatum", "Derrick White", "Luke Kornet", "Robert Williams III", "Jordan Poole", "Grant Williams", "Juwan Morgan", "Aaron Nesmith", "Payton Pritchard", "Sam Hauser")), row.names = c(NA, -66L), class = c("tbl_df", "tbl", "data.frame"))

解决方案 #

使用dplyr包实现需求，步骤如下：

• 按球员分组，对比赛日期降序排序，筛选出每位球员的最近2场比赛
• 汇总这2场比赛的总出场时间和总投篮命中数
• 计算每5分钟的平均命中数，同时处理总出场时间为0的异常情况

library(dplyr)

# 计算每位球员最近2场的每5分钟平均FGM
result <- df %>%
  group_by(PLAYER_NAME) %>%
  arrange(desc(game_date)) %>%
  slice_head(n = 2) %>%
  summarize(
    total_minutes = sum(MIN),
    total_fgm = sum(FGM),
    avg_fgm_per_5min = ifelse(total_minutes == 0, 0, (total_fgm / total_minutes) * 5)
  )

# 查看结果示例
head(result)

结果说明 #

最终结果包含四列：

• PLAYER_NAME：球员姓名
• total_minutes：最近2场的总出场时间
• total_fgm：最近2场的总投篮命中数
• avg_fgm_per_5min：最近2场的每5分钟平均投篮命中数

内容的提问来源于stack exchange，提问作者firmo23