如何在R的dplyr分组中合并指定列生成组合新列？

需要实现按ID分组折叠行，同时将每组内的agency与bucket按对应关系组合后合并为新列agency.bucket。之前尝试的dplyr代码只能分别对两列去重拼接，无法实现对应组合的效果：

df1 %>%
  dplyr::group_by(ID) %>%
  dplyr::mutate(
    agency = toString(unique(agency)),
    bucket = toString(unique(bucket))
  ) %>%
  dplyr::distinct()

输入数据 #

structure(list(ID = c(1, 1.1, 3.3, 3.3, 3.3, 3.3), time = c("M", 
"M", "M", "M", "M", "M"), restriction = c("18+", "18+", "N/A", 
"N/A", "N/A", "N/A"), county = c("Checked", "Checked", "Checked", 
"Checked", "Checked", "Checked"), zip_code = c("Checked", "Checked", 
NA, NA, NA, NA), age = c("Checked", "Checked", NA, NA, NA, NA
), delay = c("2 months", "2 months", "1 month", "1 month", "1 month", 
"1 month"), agency = c("SA", "Australia", "Africa", "Africa", 
"USA", "USA"), agency_response = c("Checked", "Checked", "Checked", 
"Checked", "Checked", "Checked"), bucket = c("DC", "DC", "Marvel", 
"Pixar", "Marvel", "Pixar"), bucket_response = c("Checked", "Checked", 
"Checked", "Checked", "Checked", "Checked")), class = "data.frame", row.names = c(NA, 
-6L)) 

期望输出 #

> df2
   ID time restriction  county zip_code     age    delay                agency.bucket
1 1.0    M         18+ Checked  Checked Checked 2 months                        SA:DC
2 1.1    M         18+ Checked  Checked Checked 2 months               Australia : DC
3 3.3    M         N/A Checked     <NA>    <NA>  1 month Africa: Marvel & USA : Pixar

解决方案 #

使用dplyr先对分组内的agency和bucket去重保留唯一配对，再拼接成指定格式：

library(dplyr)

df1 %>%
  group_by(ID) %>%
  # 保留每组内agency与bucket的唯一配对
  distinct(agency, bucket, .keep_all = FALSE) %>%
  # 将每对agency和bucket拼接为"XX: XX"格式
  mutate(pair = paste(agency, bucket, sep = ": ")) %>%
  # 合并所有配对并保留其他列的唯一值
  summarise(
    agency.bucket = paste(pair, collapse = " & "),
    across(c(time, restriction, county, zip_code, age, delay, agency_response, bucket_response), ~first(.x))
  ) %>%
  ungroup()

说明：

• distinct(agency, bucket)确保同一ID下只保留agency和bucket的唯一组合，避免重复配对
• paste(agency, bucket, sep = ": ")生成单个配对字符串
• paste(pair, collapse = " & ")将所有配对用" & "连接成最终的agency.bucket列
• across(..., ~first(.x))保留其他列的唯一值（同一ID下这些列值一致）

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