如何用pivot_wider按分组添加含唯一值的列？

需要对数据框df按id分组，为每组内的唯一Team值生成两个新列Team_1和Team_2——每组的Team_1固定为该组第一个唯一队伍，Team_2固定为第二个唯一队伍，所有行对应填充。

原始数据 #

id <- c("1", "1", "1", "1", "2", "2", "2", "2")
Team <- c("Team_A", "Team_A", "Team_B", "Team_B", "Team_C", "Team_C", "Team_D", "Team_D")

df <- data.frame(id, Team)

当前输出 #

id   Team
1  1 Team_A
2  1 Team_A
3  1 Team_B
4  1 Team_B
5  2 Team_C
6  2 Team_C
7  2 Team_D
8  2 Team_D

期望输出 #

生成包含新列的df代码：

Team_1 <- c("Team_A", "Team_A", "Team_A", "Team_A", "Team_C", "Team_C", "Team_C", "Team_C")
Team_2 <- c("Team_B", "Team_B", "Team_B", "Team_B", "Team_D", "Team_D", "Team_D", "Team_D")

df <- data.frame(id, Team, Team_1, Team_2)

对应的数据框结果：

id   Team Team_1 Team_2
1  1 Team_A Team_A Team_B
2  1 Team_A Team_A Team_B
3  1 Team_B Team_A Team_B
4  1 Team_B Team_A Team_B
5  2 Team_C Team_C Team_D
6  2 Team_C Team_C Team_D
7  2 Team_D Team_C Team_D
8  2 Team_D Team_C Team_D

遇到的问题 #

尝试使用pivot_wider和spread函数时，出现报错：

Values from Team are not uniquely identified; output will contain list-cols.
中文翻译：Team的值无法唯一标识；输出将包含列表列。

解决方案 #

方法一：dplyr分组直接赋值 #

利用dplyr分组后，提取每组的唯一Team值，直接赋值给新列：

library(dplyr)

df <- df %>%
  group_by(id) %>%
  mutate(
    Team_1 = first(unique(Team)),
    Team_2 = last(unique(Team))
  ) %>%
  ungroup()

方法二：先做映射表再合并 #

先创建每个id对应的唯一Team映射表，再合并回原数据：

library(dplyr)
library(tidyr)

# 生成分组映射表
team_map <- df %>%
  distinct(id, Team) %>%
  group_by(id) %>%
  mutate(team_col = paste0("Team_", row_number())) %>%
  pivot_wider(names_from = team_col, values_from = Team)

# 合并到原数据框
df <- df %>% left_join(team_map, by = "id")

报错原因说明 #

之前用pivot_wider报错，是因为原数据中每个id对应多个重复的Team值，函数无法确定如何将重复值转换为宽格式列，因此提示生成列表列。只要先提取每组的唯一Team值，再进行宽格式转换或直接赋值，就能解决问题。

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