如何在R中生成同一id下name的全组合宽表（优先dplyr方案）

核心思路 #

针对同一id下的名称生成不重复的两两组合（排除反向重复），并关联对应月份信息，最终整理为目标格式。

代码实现 #

1. 准备原始数据 #

df <- data.frame(
  id = c("a", "a", "a", "a", "b", "b"),
  name = c("jane", "laura", "lauran", "lilly", "james", "jimmy"),
  month = c("april", "april", "may", "june", "june", "june")
)

2. 加载依赖包 #

library(dplyr)
library(tidyr)

3. 生成无重复组合并关联月份 #

result <- df %>%
  group_by(id) %>%
  # 生成同一id内所有名称的两两组合
  expand(name1 = name, name2 = name) %>%
  # 通过字符串排序过滤反向重复（仅保留name1字典序小于name2的组合）
  filter(name1 < name2) %>%
  # 关联name1对应的月份
  left_join(df, by = c("id", "name1" = "name")) %>%
  rename(month1 = month) %>%
  # 关联name2对应的月份
  left_join(df, by = c("id", "name2" = "name")) %>%
  rename(month2 = month) %>%
  ungroup()

4. 查看结果 #

print(result)

输出：

# A tibble: 7 × 5
  id    name1  name2  month1 month2
  <chr> <chr>  <chr>  <chr>  <chr>
1 a     jane   laura  april  april
2 a     jane   lauran april  may
3 a     jane   lilly  april  june
4 a     laura  lauran april  may
5 a     laura  lilly  april  june
6 a     lauran lilly  may    june
7 b     james  jimmy  june   june

可选：转换为紧凑宽表 #

如果需要将每个组合作为单独列展示，可进一步处理：

wide_result <- result %>%
  mutate(combination = paste(name1, name2, sep = "/")) %>%
  select(id, combination, month1, month2) %>%
  pivot_wider(
    id_cols = id,
    names_from = combination,
    values_from = c(month1, month2),
    names_sep = "_"
  )

print(wide_result)

输出：

# A tibble: 2 × 15
  id    month1_jane/laura month2_jane/laura month1_jane/lauran month2_jane/lauran month1_jane/lilly month2_jane/lilly month1_laura/lauran month2_laura/lauran month1_laura/lilly month2_laura/lilly month1_lauran/lilly month2_lauran/lilly month1_james/jimmy month2_james/jimmy
  <chr> <chr>             <chr>             <chr>              <chr>               <chr>             <chr>             <chr>               <chr>                <chr>              <chr>              <chr>               <chr>                <chr>              <chr>             
1 a     april             april             april              may                 april             june              april               may                 april              june              may                 june                NA                 NA                
2 b     NA                NA                NA                 NA                  NA                NA                NA                  NA                  NA                 NA                 NA                  NA                   june              june             

说明 #

• 该方案利用dplyr的分组和过滤逻辑，高效排除反向重复组合，适合数千行规模的数据集。
• 若数据量极大，可替换为data.table实现更优性能，但dplyr方案可读性更强，更易维护。

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