如何分组拼接字符串并排除当前用户，优化亿级数据处理？

现有数据 #

person_id <- c("A1", "A1", "A1", "A1", "A2", "A2", "A3", "A3", "B1", "B1", "C1", "C1", "C2", "C2")
year <- c(2015, 2016, 2015, 2016, 2015, 2016, 2015, 2016, 2015, 2016, 2015, 2016, 2015, 2016)
group_id <- c("abc", "abc", "cdz", "cdz", "abc", "abc", "ghe", "ghe", "abc", "fjx", "ghe", "ghe", "cdz", "cdz")
example <- data.frame(person_id, group_id, year)

当前实现的问题 #

• 拼接结果包含用户自身：比如A1的结果里不该出现A1，应只保留同组其他用户
• 无同组用户时错误显示自身ID：比如2016年的B1所在组只有自己，应显示空字符串
• 两次分组操作效率极低，无法处理10亿行规模的数据（R版本3.4.3，禁止使用tidyr）

高效解决方案（优先推荐data.table） #

针对大数据量，data.table是最优选择——其分组操作基于C实现，内存效率和运行速度远高于base R或dplyr。

# 安装兼容R3.4.3的data.table版本（如1.10.4-3）
# install.packages("data.table", version = "1.10.4-3")
library(data.table)

# 转换为data.table格式
setDT(example)

# 1. 按group_id+year分组，预存每组的唯一成员列表
group_members <- example[, .(members = list(unique(person_id))), by = .(group_id, year)]

# 2. 关联回原表，为每个用户生成排除自身的同组用户拼接串
example <- merge(example, group_members, by = c("group_id", "year"))
example[, connections := {
  sapply(person_id, function(x) paste(setdiff(members[[1]], x), collapse = ", "))
}, by = .(group_id, year)]

# 3. 按person_id+year去重聚合，得到最终结果
res <- example[, .(joint = paste(unique(connections), collapse = ", ")), by = .(person_id, year)]

# 确保无同组用户时显示空字符串
res[joint == "", joint := ""]

base R替代方案 #

若无法使用data.table，可使用以下优化后的base R代码（注意：10亿行数据可能存在内存压力）：

# 1. 按group_id+year分组，获取每组的唯一成员列表
group_list <- with(example, tapply(person_id, list(group_id, year), function(x) unique(x)))

# 2. 转换为数据框并关联回原表
group_df <- expand.grid(group_id = dimnames(group_list)[[1]], year = dimnames(group_list)[[2]])
group_df$members <- as.vector(group_list)
example <- merge(example, group_df, by = c("group_id", "year"))

# 3. 生成排除自身的拼接串
example$connections <- mapply(function(mems, self) {
  paste(setdiff(mems, self), collapse = ", ")
}, example$members, example$person_id)

# 4. 按person_id+year聚合去重
res <- aggregate(connections ~ person_id + year, data = example, function(x) paste(unique(x), collapse = ", "))
names(res)[3] <- "joint"

# 替换空值场景
res$joint[res$joint == ""] <- ""

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