如何基于双条件匹配提取两个DataFrame行？代码重复值问题求助

问题背景 #

我有两个DataFrame：snp_region和hic_region，数据结构如下：

hic_region 示例数据 #

dput(hic_region[1:15,1:4])
structure(list(rf1 = c(57584944, 57584944, 57584944, 57584944, 
57584944, 57584944, 57584944, 57584944, 50263463, 5e+07, 50263463, 
50263463, 35172197, 35172197, 57584944), rt1 = c(57624944, 57624944, 
57624944, 57624944, 57624944, 57624944, 57624944, 57624944, 50303463, 
51423463, 50303463, 50303463, 35212197, 35212197, 57624944), 
    rf2 = c(1899354, 1899354, 1899354, 1899354, 1899354, 1579354, 
    1899354, 1899354, 1899354, 1779354, 1899354, 1899354, 1899354, 
    1899354, 1499354), rt2 = c(1939354, 1939354, 1939354, 1939354, 
    1939354, 1619354, 1939354, 1939354, 1939354, 1819354, 1939354, 
    1939354, 1939354, 1939354, 1539354)), row.names = c(NA, -15L
), class = c("tbl_df", "tbl", "data.frame"))

snp_region 示例数据 #

dput(snp_region[1:10,1:6])
structure(list(Gene = c("ENSG00000132819", "ENSG00000101162", 
"ENSG00000132819", "ENSG00000101162", "ENSG00000101162", "ENSG00000101162", 
"ENSG00000101162", "ENSG00000101162", "ENSG00000101162", "ENSG00000101162"
), `Gene-Chr` = c(20, 20, 20, 20, 20, 20, 20, 20, 20, 20), `Gene-Pos` = c(55975426, 
57598009, 55975426, 57598009, 57598009, 57598009, 57598009, 57598009, 
57598009, 57598009), RsId = c("rs6084653", "rs156356", "rs1741314", 
"rs6136489", "rs4814776", "rs13042885", "rs4814779", "rs6045615", 
"rs11696739", "rs4618126"), `SNP-Chr` = c(20, 20, 20, 20, 20, 
20, 20, 20, 20, 20), `SNP-Pos` = c(4157072, 1819280, 4155193, 
1923734, 1921523, 1924707, 1923271, 1931582, 1600925, 1930885
)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
))

需要筛选满足以下两个条件的snp_region行：

• snp_region$Gene-Pos处于hic_region某一行的rf1到rt1区间内；
• 对应同一行hic_region，snp_region$SNP-Pos处于rf2到rt2区间内。

编写的循环代码出现重复值问题，代码及结果示例如下：

snp <- data.frame()
for(i in 1:dim(snp_region)[1]){
  snp_pos <- snp_region$`SNP-Pos`[i]
  for(j in 1:dim(hic_region)[1]){
  if(snp_region$`Gene-Pos`[i] %in% seq(hic_region$rf1[j],hic_region$rt1[j],1)){
   hic_region1 <- hic_region[j,]
    if(snp_pos %in% seq(hic_region1$rf2,hic_region1$rt2,1)){
      preset <<- snp_region[i,]
    } 
  }
  }
  snp <- rbind(snp,preset)
}

# 结果示例
dput(snp[1:5,1:6])
structure(list(Gene = c("ENSG00000131069", "ENSG00000131069", 
"ENSG00000131069", "ENSG00000101162", "ENSG00000101162"), `Gene-Chr` = c(20, 
20, 20, 20, 20), `Gene-Pos` = c(33487859, 33487859, 33487859, 
57598009, 57598009), RsId = c("rs4142441", "rs4142441", "rs4142441", 
"rs6136489", "rs4814776"), `SNP-Chr` = c(20, 20, 20, 20, 20), 
    `SNP-Pos` = c(42839620, 42839620, 42839620, 1923734, 1921523
    )), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame"
))

原代码问题分析 #

1. 重复行根源：内层循环中，每次找到匹配的hic_region行就会覆盖全局变量preset，外层循环无论匹配次数多少，都会将preset绑定到结果中。若一个snp_region行匹配多个hic_region行，最终会重复添加同一行（或因覆盖导致保留最后一次匹配，但逻辑混乱）。
2. 效率低下：用seq()生成区间内所有整数再用%in%判断，当区间范围大时，会生成海量序列，占用内存且拖慢运行速度。
3. 全局变量风险：使用<<-修改全局变量，容易引发意外的变量覆盖，导致逻辑错误。

优化方案 #

方案1：dplyr交叉连接+过滤（中小数据集适用） #

通过生成两个表的所有组合，过滤符合双区间条件的行，最后去重保留snp_region的唯一行：

library(dplyr)

result <- snp_region %>%
  cross_join(hic_region) %>%
  filter(`Gene-Pos` >= rf1 & `Gene-Pos` <= rt1,
         `SNP-Pos` >= rf2 & `SNP-Pos` <= rt2) %>%
  distinct(across(all_of(colnames(snp_region))))

方案2：data.table非等连接（大数据集适用） #

data.table的非等连接效率远高于嵌套循环，适合数据量较大的场景：

library(data.table)

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

# 非等连接并筛选条件，最后去重
result <- snp_region[hic_region, 
                     on = .(`Gene-Pos` >= rf1, `Gene-Pos` <= rt1,
                            `SNP-Pos` >= rf2, `SNP-Pos` <= rt2),
                     nomatch = 0] %>%
  unique(by = colnames(snp_region))

方案3：修复循环逻辑（仅作参考，不推荐） #

若坚持使用循环，需避免全局变量，且找到匹配后及时处理：

snp <- data.frame()
for(i in 1:nrow(snp_region)){
  gene_pos <- snp_region$`Gene-Pos`[i]
  snp_pos <- snp_region$`SNP-Pos`[i]
  matched <- FALSE
  for(j in 1:nrow(hic_region)){
    # 直接用区间判断，无需生成seq
    if(gene_pos >= hic_region$rf1[j] && gene_pos <= hic_region$rt1[j] &&
       snp_pos >= hic_region$rf2[j] && snp_pos <= hic_region$rt2[j]){
      snp <- rbind(snp, snp_region[i,])
      matched <- TRUE
      break  # 找到一个匹配即跳出，避免重复添加
    }
  }
}
# 最终去重，确保无重复行
snp <- distinct(snp)

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