如何在R中依据Cross列值处理行以实现正确分组求和

原始数据 #

data <- data.table(
  Name = c("orc1", "orc1", "orc1", "orc2", "orc2", "orc4", "orc4", "orc4"),
  Subject = c("apple1", "apple2", "pear", "apple1", "apple2", "ban1", "ban2", "ban3"),
  Cross = c("apple1, apple2", "apple1, apple2", "", "", "", "ban1, ban2, ban3", "ban1, ban2, ban3", "ban1, ban2, ban3"),
  Count = c(3, 3, 3, 3, 3, 3, 3, 3)
)

需求说明 #

需要按Name分组对Count求和，但Cross列标记了需要合并计数的Subject组：同一Cross组内的Subject仅需贡献一次Count值。例如：

• orc1的apple1和apple2同属一组，合计仅算3而非6，最终总和为6
• orc4的三个ban类Subject同属一组，合计仅算3
• orc2的Cross为空，两个Subject各自贡献3，总和为6

最终期望结果：

Name Count
1: orc1     6
2: orc2     6
3: orc4     3

方案1：删除重复组内的多余行 #

通过解析Cross列的关联Subject集合，每个集合仅保留一行，其余行删除后再分组求和。

# 按Name分组筛选需要保留的行
data_processed <- data[, {
  # 提取当前组非空的Cross内容（同一组内Cross值一致）
  cross_str <- unique(Cross[Cross != ""])
  if (length(cross_str) > 0) {
    # 拆分Cross字符串为Subject列表
    cross_subjects <- strsplit(cross_str, ", ")[[1]]
    # 保留规则：不在关联组内的行，或者关联组内的第一行
    keep <- !(Subject %in% cross_subjects) | (Subject == cross_subjects[1])
  } else {
    # Cross为空时保留所有行
    keep <- rep(TRUE, .N)
  }
  .SD[keep]
}, by = Name]

# 分组计算Count总和
result1 <- data_processed[, .(Count = sum(Count)), by = Name]
print(result1)

方案2：将重复组内的多余行Count设为0 #

解析Cross列的关联Subject集合，将集合内除第一个Subject外的其他行Count设为0，再分组求和。

# 按Name分组调整Count值
data_processed2 <- data[, {
  cross_str <- unique(Cross[Cross != ""])
  if (length(cross_str) > 0) {
    cross_subjects <- strsplit(cross_str, ", ")[[1]]
    # 标记需要置0的行：属于关联组但不是组内第一个Subject
    set_zero <- (Subject %in% cross_subjects) & (Subject != cross_subjects[1])
    Count[set_zero] <- 0
  }
  .SD
}, by = Name]

# 分组计算Count总和
result2 <- data_processed2[, .(Count = sum(Count)), by = Name]
print(result2)

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