如何在R中将足球比赛DataFrame转换为分主客场的长格式

方法1：拆分主客队后合并（逻辑清晰） #

这种方法分别构造主队和客队的独立记录，再合并，逻辑直观不易出错：

library(dplyr)

# 构造主队记录
home_records <- df %>%
  mutate(
    team_api_id = home_team_api_id,
    opponent_team_api_id = away_team_api_id,
    goals = home_team_goal,
    goals_conceded = away_team_goal,
    is_home = TRUE
  ) %>%
  select(season, stage, match_api_id, team_api_id, opponent_team_api_id, goals, goals_conceded, is_home)

# 构造客队记录
away_records <- df %>%
  mutate(
    team_api_id = away_team_api_id,
    opponent_team_api_id = home_team_api_id,
    goals = away_team_goal,
    goals_conceded = home_team_goal,
    is_home = FALSE
  ) %>%
  select(season, stage, match_api_id, team_api_id, opponent_team_api_id, goals, goals_conceded, is_home)

# 合并并排序得到最终结果
df_long <- bind_rows(home_records, away_records) %>%
  arrange(match_api_id, desc(is_home))

运行后输出完全符合预期：

season stage match_api_id team_api_id opponent_team_api_id goals goals_conceded is_home
1 2015/2016     1           101           1                   2     3              1    TRUE
2 2015/2016     1           101           2                   1     1              3   FALSE
3 2015/2016     1           102           2                   1     2              3    TRUE
4 2015/2016     1           102           1                   2     3              1   FALSE

方法2：用tidyr::pivot_longer一次性转换（更简洁） #

利用pivot_longer的names_pattern参数，匹配列名中的home/away前缀，同时处理球队ID和进球数据：

library(dplyr)
library(tidyr)

df_long <- df %>%
  pivot_longer(
    cols = starts_with(c("home_", "away_")),
    names_to = c("is_home", ".value"),
    names_pattern = "(home|away)_(.*)"
  ) %>%
  mutate(
    is_home = is_home == "home",
    opponent_team_api_id = ifelse(is_home, away_team_api_id, home_team_api_id)
  ) %>%
  rename(team_api_id = team_api_id, goals = goal) %>%
  select(season, stage, match_api_id, team_api_id, opponent_team_api_id, goals, goals_conceded, is_home) %>%
  arrange(match_api_id, desc(is_home))

原代码问题分析 #

你之前的代码错误在于：pivot_longer仅拆分了球队ID列，但home_team_goal和away_team_goal仍保留原行的两个值，导致用ifelse赋值时，进球数据没有对应到当前的球队类型（主/客），最终出现匹配错位。

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