R语言中高效筛选满足数值和条件的comboindex组合

需要筛选出comboindex列的所有两两组合，满足以下条件：

• 两行的indexa、indexb、indexc三个索引列取值完全相同
• 两行的number1之和>0
• 两行的number2之和>0

示例数据 #

# 创建示例数据
example_df <- data.frame(
  comboindex=c(LETTERS[1:4],LETTERS[1:6]),
  indexa=c(rep("A",4),rep("CCC",6)),
  indexb=c(rep("B",4),rep("DDD",6)),
  indexc=c(rep("C",4),rep("EEE",6)),
  number1=c(290, 340, -200, -108, 150, -190, 500, 1000, -300, 128),
  number2=c(-120, 100, -400, 180, -140, 200, -155, -900, 22000, 175),
  stringsAsFactors = F
)

期望输出 #

# 包含求和计算的目标结果
desired_df <- data.frame(
  indexcombo=c("A_D","B_D","B_C","D_E","C_F","A_F","C_E"),
  indexa=c("A","A","CCC","CCC","CCC","CCC","CCC"),
  indexb=c("B","B","DDD","DDD","DDD","DDD","DDD"),
  indexc=c("C","C","EEE","EEE","EEE","EEE","EEE"),
  number1a=c(290,340,340,1000,-300,150,500),
  number1b=c(-108,-108,-200,1000,128,128,-300),
  sumnumber1=c(sum(290,-108),sum(340,-108),sum(340,-200),sum(1000,-300),sum(500,128),sum(150,128),sum(500,-300)),
  number2a=c(-120,100,200,-900,-155,-140,-155),
  number2b=c(180,180,-155,22000,175,175,22000),
  sumnumber2=c(sum(-120,180),sum(100,180),sum(200,-155),sum(-900,22000),sum(-155,175),sum(-140,175),sum(-155,22000)),
  stringsAsFactors = F
)

高效解决方案 #

1. 使用data.table实现 #

data.table的分组自连接操作效率极高，适合处理数千行的数据集：

library(data.table)

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

# 分组自连接并筛选符合条件的组合
result_dt <- example_df[example_df, 
                        on = .(indexa, indexb, indexc),
                        allow.cartesian = TRUE,
                        .(indexcombo = paste(comboindex, i.comboindex, sep = "_"),
                          indexa, indexb, indexc,
                          number1a = number1, number1b = i.number1,
                          sumnumber1 = number1 + i.number1,
                          number2a = number2, number2b = i.number2,
                          sumnumber2 = number2 + i.number2)
                        ][comboindex != i.comboindex # 排除自身组合
                          & sumnumber1 > 0 
                          & sumnumber2 > 0]

# 查看结果
result_dt

2. 使用dplyr/tidyverse实现 #

通过分组交叉连接生成组合，再筛选条件：

library(dplyr)
library(tidyr)

result_dplyr <- example_df %>%
  group_by(indexa, indexb, indexc) %>%
  # 组内生成所有两两组合
  crossing(a = cur_data(), b = cur_data()) %>%
  # 排除自身组合
  filter(a$comboindex != b$comboindex) %>%
  # 计算求和列并筛选符合条件的组合
  mutate(sumnumber1 = a$number1 + b$number1,
         sumnumber2 = a$number2 + b$number2) %>%
  filter(sumnumber1 > 0, sumnumber2 > 0) %>%
  # 整理为目标格式
  transmute(indexcombo = paste(a$comboindex, b$comboindex, sep = "_"),
            indexa, indexb, indexc,
            number1a = a$number1, number1b = b$number1, sumnumber1,
            number2a = a$number2, number2b = b$number2, sumnumber2) %>%
  ungroup()

# 查看结果
result_dplyr

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