如何基于多行将tibble转换为指定宽格式？pivot_wider尝试失败

解决方案 #

你需要将每个比赛的两队数据合并为一行，用**自连接（inner_join）**的方式比pivot_wider更直接，步骤如下：

1. 加载tidyverse工具包，同时将steals和assists列转换为数值类型（原始数据为字符型）：

library(tidyverse)

# 假设原始数据框名为df
df <- df %>%
  mutate(across(c(steals, assists), as.integer))

2. 对数据进行自连接，匹配同一比赛的两队数据，筛选正确配对关系后整理列名：

result <- df %>%
  # 自连接，用后缀区分两队数据
  inner_join(df, by = "game_id", suffix = c("_team", "_opp")) %>%
  # 筛选：当前队的team等于对手队的opponent，确保配对逻辑正确
  filter(team_team == opponent_opp) %>%
  # 选择并重命名目标列
  select(
    game_id,
    team = team_team,
    opponent = opponent_team,
    team_steals = steals_team,
    opp_assists = assists_opp,
    team_assists = assists_team,
    opp_steals = steals_opp
  ) %>%
  # 每个比赛只保留一行数据
  distinct(game_id, .keep_all = TRUE)

3. 最终输出的result即为目标格式：

> result
# A tibble: 5 × 7
  game_id team     opponent team_steals opp_assists team_assists opp_steals
    <int> <chr>    <chr>          <int>       <int>        <int>      <int>
1 401468360 Spurs   Warriors          6           9           18         35
2 401468358 Clippers Rockets          6          11           25         20
3 401468359 Hawks   Bucks             4           4           23          4
4 401468356 Thunder Celtics           4          15           21         25
5 401468357 Suns    Heat              9           6           22          6

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