如何按±5范围匹配两数值列合并两个R数据框？

问题背景 #

现有两个R数据框：

df1 <- data.frame(chrom = c(1,1,3,6,6),
                  chromStart = c(15433, 1959,34205,35043, 77456),
                  chromEnd = c(15700, 2001,36245,36245,78469), 
                  id = c('aaad', 'dfk', 'bb', 'llk', 'ie9o'))

df2 <- data.frame(chrom = c(1,1,5,1,6),
                  chromStart2 = c(15433, 1961,34205,1962, 77456),
                  chromEnd2 = c(15700, 2002,36245,1999,78480))

需要按以下条件合并两个数据框：

• chrom 列完全匹配
• df1$chromStart 处于 df2$chromStart2 ±5 范围内
• df1$chromEnd 处于 df2$chromEnd2 ±5 范围内

原尝试用dplyr::inner_join仅能精确匹配，只得到aaad一行，无法匹配dfk这类符合范围条件的行。由于实际数据量分别为26万行和17.9万行，需要内存高效的实现方式，期望输出如下：

data.frame(chrom = c(1,1,1),
           chromStart = c(15433, 1959,1959),
           chromEnd = c(15700, 2001,2001), 
           id = c('aaad', 'dfk', 'dfk'),
           chromStart2 = c(15433, 1961,1962),
           chromEnd2 = c(15700, 2002,1999))

内存高效解决方案：使用data.table非等连接 #

data.table的非等连接是处理大数据量范围匹配的最优选择之一，内存占用低、运算速度快。具体实现步骤如下：

1. 加载并转换数据格式

library(data.table)

# 将data.frame转换为data.table
setDT(df1)
setDT(df2)

2. 执行范围条件合并

merged_df <- df1[df2, 
                 on = .(chrom == chrom, 
                         chromStart >= chromStart2 - 5, 
                         chromStart <= chromStart2 + 5,
                         chromEnd >= chromEnd2 - 5,
                         chromEnd <= chromEnd2 + 5),
                 nomatch = 0,
                 .(chrom = x.chrom, 
                   chromStart = x.chromStart, 
                   chromEnd = x.chromEnd, 
                   id, 
                   chromStart2 = i.chromStart2, 
                   chromEnd2 = i.chromEnd2)]

代码说明 #

• on = .(...)：设置匹配条件，其中chrom == chrom保证染色体完全匹配，后续四个条件定义chromStart和chromEnd的±5范围
• nomatch = 0：只保留匹配成功的行，对应inner_join的效果
• .()：指定输出列，用x.前缀表示来自df1的列，i.前缀表示来自df2的列，避免列名冲突

运行上述代码后，得到的merged_df即为符合要求的结果：

> merged_df
   chrom chromStart chromEnd   id chromStart2 chromEnd2
1:     1       15433    15700 aaad       15433     15700
2:     1        1959     2001  dfk        1961      2002
3:     1        1959     2001  dfk        1962      1999

备选方案：fuzzyjoin（适合中小数据量） #

如果习惯使用dplyr语法，也可以用fuzzyjoin包，但对于20万级别的数据，速度和内存占用不如data.table：

library(fuzzyjoin)
library(dplyr)

merged_df <- fuzzy_inner_join(df1, df2,
                              by = c("chrom" = "chrom",
                                     "chromStart" = "chromStart2",
                                     "chromEnd" = "chromEnd2"),
                              match_fun = list(`==`,
                                               function(x, y) abs(x - y) <= 5,
                                               function(x, y) abs(x - y) <= 5)) %>%
  select(chrom = chrom.x, chromStart, chromEnd, id, chromStart2, chromEnd2)

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