R语言如何合并主客场NBA数据生成按赛季分组的球队总数据集

优先推荐方案：从原始games数据直接生成统一数据集 #

这个方案避免了合并两个聚合表的字段对齐、加权误差问题，逻辑更简单也更不容易出错：

• 第一步把单场比赛的宽格式数据拆为长格式：每条比赛拆成2行，分别对应主队视角、客队视角
• 第二步统一标记每场比赛球队是否获胜、是否主场，提取对应球队的技术统计
• 第三步按赛季+球队ID分组聚合得到所需指标

示例代码如下：

library(dplyr)
library(tidyr)

# 读取原始数据
games <- read.csv("games.csv")

unified_season_data <- games %>%
  # 拆分主客场为独立行
  pivot_longer(
    cols = c(home_team_id, away_team_id),
    names_to = "role",
    values_to = "team_id"
  ) %>%
  # 统一生成胜负、技术统计字段
  mutate(
    is_win = case_when(
      role == "home_team_id" & home_team_wins == 1 ~ 1,
      role == "away_team_id" & home_team_wins == 0 ~ 1,
      TRUE ~ 0
    ),
    fg_pct = ifelse(role == "home_team_id", home_fg_pct, away_fg_pct),
    three_p_pct = ifelse(role == "home_team_id", home_3p_pct, away_3p_pct),
    reb = ifelse(role == "home_team_id", home_reb, away_reb),
    # 其余技术统计按照相同逻辑补充即可
  ) %>%
  # 按赛季+球队ID聚合
  group_by(season, team_id) %>%
  summarise(
    total_win = sum(is_win),
    total_lose = n() - total_win,
    win_rate = total_win / n(),
    avg_fg_pct = mean(fg_pct, na.rm = TRUE),
    avg_three_p_pct = mean(three_p_pct, na.rm = TRUE),
    avg_reb = mean(reb, na.rm = TRUE),
    # 其余技术统计均值按照相同逻辑补充即可
    .groups = "drop"
  )

备选方案：合并你已有的主客场聚合表 #

如果你已经完成了主客场的单独聚合，也可以按以下逻辑合并：

• 第一步统一两个聚合表的字段名，去掉home_/away_前缀，保留每个聚合分组的出场数字段
• 第二步行绑定两个表后按赛季+球队ID重新分组，用出场数加权计算整体均值

示例代码如下：

# 假设你的主场聚合表名为home_agg，字段为season, home_team_id, home_games, home_win, avg_home_fg_pct...
# 客场聚合表名为away_agg，字段为season, away_team_id, away_games, away_win, avg_away_fg_pct...

# 清洗主场表字段
home_clean <- home_agg %>%
  rename(team_id = home_team_id) %>%
  rename_with(~ gsub("home_", "", .x), everything())

# 清洗客场表字段
away_clean <- away_agg %>%
  rename(team_id = away_team_id) %>%
  rename_with(~ gsub("away_", "", .x), everything())

# 合并聚合
unified_season_data <- bind_rows(home_clean, away_clean) %>%
  group_by(season, team_id) %>%
  summarise(
    total_win = sum(win),
    total_lose = sum(games) - total_win,
    win_rate = total_win / sum(games),
    # 按出场数加权计算均值，避免主客场场次不均的误差
    avg_fg_pct = weighted.mean(avg_fg_pct, games, na.rm = TRUE),
    avg_three_p_pct = weighted.mean(avg_three_p_pct, games, na.rm = TRUE),
    avg_reb = weighted.mean(avg_reb, games, na.rm = TRUE),
    .groups = "drop"
  )

注意事项 #

• 所有均值计算都建议加na.rm = TRUE，避免单场数据缺失导致整个球队赛季指标为NA
• 如果要关联球队名称，直接将结果和球队维度表左连匹配team_id即可

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