R语言构造匹配game_id实现nflfastrpbp与routes.merging合并

第一步：构建队名全称与3字母缩写映射 #

nflfastr使用NFL官方标准3字母队缩写，你需要先准备队名全称到缩写的映射关系，R环境下可以直接调用nflreadr::load_teams()获取官方映射表，Python环境可以手动定义固定映射字典（NFL队名全称和缩写对应关系常年稳定，仅球队搬迁/更名时需要更新）。

第二步：ID拼接核心逻辑 #

你需要先统一两个数据集的匹配维度，避免主客顺序判断错误的坑：

• 统一周数格式：将routes.merging的Week字段格式化为2位字符串，不足2位前面补0，比如周1转为01，周11保持11
• 队名转缩写：将routes.merging的OffensiveTeam、DefensiveTeam字段通过映射表转为3字母缩写
• 生成无顺序对阵键：将单场比赛的两个队缩写按字母顺序拼接为统一键，同时对nflfastrpbp的game_id做拆分，提取赛季、周数、两个队缩写，生成同样规则的无顺序对阵键
• 关联匹配：按赛季+周数+无顺序对阵键合并两个数据集，将nflfastrpbp的game_id赋值给routes.merging的id.for.merging字段

第三步：代码实现示例 #

Python 版 #

import pandas as pd

# 1. 定义队名映射（仅示例部分，可补充完整32队映射）
team_name_map = {
    "Arizona Cardinals": "ARI",
    "San Francisco 49ers": "SF",
    # 补充其余30支球队的全称:缩写对应
}

# 2. 处理routes.merging生成匹配键
routes = routes.merging.copy()
routes["week_str"] = routes["Week"].astype(str).str.zfill(2)
routes["off_abbr"] = routes["OffensiveTeam"].map(team_name_map)
routes["def_abbr"] = routes["DefensiveTeam"].map(team_name_map)
# 生成无顺序对阵键，避免主客顺序问题
routes["match_key"] = routes.apply(
    lambda x: "_".join(sorted([x["off_abbr"], x["def_abbr"]])), axis=1
)
routes["join_key"] = routes["Season"].astype(str) + "_" + routes["week_str"] + "_" + routes["match_key"]

# 3. 处理nflfastrpbp生成匹配键
nfl = nflfastrpbp.copy()
nfl[["season", "week", "away_abbr", "home_abbr"]] = nfl["game_id"].str.split("_", expand=True)
nfl["match_key"] = nfl.apply(
    lambda x: "_".join(sorted([x["away_abbr"], x["home_abbr"]])), axis=1
)
nfl["join_key"] = nfl["season"] + "_" + nfl["week"] + "_" + nfl["match_key"]

# 4. 合并生成目标字段
routes = routes.merge(
    nfl[["join_key", "game_id", "pass_defense_player"]], # 按需补充需要关联的字段
    on="join_key",
    how="left"
).rename(columns={"game_id": "id.for.merging"})

R 版（适配nflfastR生态） #

library(tidyverse)
library(nflreadr)

# 1. 加载官方队名映射
team_map <- load_teams() %>% select(team_name, team_abbr)

# 2. 处理routes.merging生成匹配键
routes <- routes.merging %>%
  mutate(
    week_str = str_pad(Week, 2, side = "left", pad = "0"),
    off_abbr = left_join(tibble(OffensiveTeam), team_map, by = c("OffensiveTeam" = "team_name"))$team_abbr,
    def_abbr = left_join(tibble(DefensiveTeam), team_map, by = c("DefensiveTeam" = "team_name"))$team_abbr,
    match_key = map2_chr(off_abbr, def_abbr, ~str_c(sort(c(.x, .y)), collapse = "_")),
    join_key = str_c(Season, week_str, match_key, sep = "_")
  )

# 3. 处理nflfastrpbp生成匹配键
nfl <- nflfastrpbp %>%
  separate(game_id, into = c("season", "week", "away_abbr", "home_abbr"), sep = "_", remove = FALSE) %>%
  mutate(
    match_key = map2_chr(away_abbr, home_abbr, ~str_c(sort(c(.x, .y)), collapse = "_")),
    join_key = str_c(season, week, match_key, sep = "_")
  )

# 4. 合并生成目标字段
routes <- routes %>%
  left_join(nfl %>% select(join_key, game_id, pass_defense_player), by = "join_key") %>%
  rename(id.for.merging = game_id)

验证建议 #

合并完成后随机抽取10~20条数据，对比对阵双方、赛季、周数是否匹配，确认队名映射没有错误后即可使用id.for.merging完成后续全量关联。

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