如何基于多列及区间匹配合并R语言中的两个数据框？

需求说明 #

需要将数据框df2中的基因符号映射到df1，匹配规则为：

• 两者染色体（chr）相同
• df2的起始位置（startpos）和终止位置（endpos）完全落在df1对应的区间内（即df1$startpos ≤ df2$startpos且df1$endpos ≥ df2$endpos）

数据结构 #

df1结构 #

structure(list(chr = c("1", "1", "1", "2", "2", "2", "2", "2", "3", "3", "3", "3", "3", "3", "4", "4", "4", "4", "4", "5", "5", "5", "5", "5", "5", "5", "5", "5", "6", "6", "6", "6", "6", "6", "6", "6", "7", "7", "7", "7", "7", "8", "8", "8", "8", "9", "9", "9", "9", "10", "10", "10", "11", "11", "11", "11", "11", "12", "12", "12", "12", "13", "13", "13", "13", "14", "14", "14", "14", "15", "16", "16", "16", "16", "17", "17", "17", "17", "17", "18", "18", "18", "18", "19", "19", "20", "20", "20", "21", "22", "X", "X"), startpos = c(3763769L, 30204151L, 145574212L, 41404L, 79025902L, 84425655L, 97207752L, 195771938L, 319825L, 53724022L, 81670925L, 84760199L, 130389220L, 167473864L, 4166887L, 9755086L, 36316146L, 51848345L, 181522885L, 2788095L, 21585311L, 29848748L, 50371143L, 72115891L, 94628989L, 107861719L, 142773060L, 167755050L, 549364L, 8054180L, 36024843L, 44302628L, 63211948L, 93358143L, 106544755L, 122454050L, 2712235L, 63876731L, 77122341L, 116695594L, 122013344L, 219366L, 4787599L, 46635389L, 116942766L, 407227L, 61665918L, 68540505L, 131604834L, 972645L, 42785641L, 58400552L, 4367675L, 26537294L, 54591798L, 69295669L, 100356152L, 140964L, 38670828L, 92531096L, 123835317L, 23009501L, 58528741L, 67228207L, 89361193L, 20002158L, 42528760L, 85298658L, 106377432L, 19964897L, 1586202L, 46618297L, 64982005L, 71230496L, 156366L, 27079757L, 29571810L, 34959645L, 55315183L, 196829L, 20979714L, 42004300L, 67512592L, 7117415L, 29606361L, 96321L, 31760029L, 46583816L, 14163568L, 17424460L, 312451L, 155774775L), endpos = c(29516595L, 119471365L, 248917151L, 75517955L, 80604356L, 89027732L, 191836667L, 239800120L, 46460352L, 77635071L, 81670925L, 126852836L, 164163609L, 193626229L, 5640434L, 32409374L, 48832879L, 177353681L, 186709835L, 16689924L, 25911075L, 43609241L, 68226894L, 91476629L, 103201499L, 137946019L, 163475701L, 175509230L, 8015924L, 31288236L, 41282856L, 56607197L, 90312920L, 106234115L, 119269213L, 167948331L, 57200843L, 72629090L, 113084740L, 118192084L, 159138060L, 3950145L, 42318418L, 112643523L, 140300545L, 38615782L, 61666267L, 127344499L, 133402909L, 38378075L, 54527809L, 131956064L, 23404921L, 50416083L, 63408895L, 96373497L, 134381883L, 33426473L, 89523741L, 119857421L, 130036761L, 52820957L, 61414933L, 85795917L, 110503806L, 39399442L, 81916397L, 100984697L, 106874951L, 101828980L, 27483556L, 58920764L, 71203799L, 89562821L, 21210841L, 29565997L, 34636381L, 51633681L, 81715184L, 13884567L, 36653268L, 64232128L, 77436580L, 19679350L, 58478128L, 25320386L, 46048325L, 64219694L, 42904255L, 49657199L, 2720458L, 155774775L)), class = "data.frame", row.names = c(NA, -92L))

df2结构 #

structure(list(hgnc_symbol = c("ERBB2", "PAK1"), chr = c("17", "11"), startpos = c(39687914L, 77322017L), endpos = c(39730426L, 77474635L)), row.names = c(NA, -2L), class = "data.frame")

问题分析 #

使用merge(df1, df2, by = c('chr', 'startpos', 'endpos'))返回0行，因为merge是精确匹配，要求三个列的值完全相等，但你的需求是区间包含匹配，两者逻辑不同，所以无法得到结果。

解决方案 #

方法一：使用dplyr包实现区间匹配 #

先按染色体连接两个数据框，再过滤区间条件：

library(dplyr)

# 执行匹配
result <- df1 %>%
  inner_join(df2, by = "chr") %>%  # 先按chr连接
  filter(startpos.y >= startpos.x, endpos.y <= endpos.x) %>%  # 过滤区间包含条件
  select(chr, startpos = startpos.x, endpos = endpos.x, hgnc_symbol)  # 整理输出列

# 查看结果
print(result)

方法二：使用data.table包实现高效非等连接 #

适用于大数据集，非等连接效率更高：

library(data.table)

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

# 执行非等连接匹配
result <- df2[df1, on = .(chr, startpos >= startpos, endpos <= endpos), 
              .(chr, startpos = i.startpos, endpos = i.endpos, hgnc_symbol)]

# 去除NA行（无匹配的结果）
result <- na.omit(result)

# 查看结果
print(result)

方法三：基础R实现（适合小数据集） #

通过循环遍历df2的每一行，在df1中筛选符合条件的行：

# 初始化空列表存储匹配结果
match_list <- list()

# 遍历df2的每个基因
for (i in 1:nrow(df2)) {
  gene <- df2[i, ]
  # 筛选df1中符合条件的行
  matched_rows <- subset(df1, 
                         chr == gene$chr & 
                           startpos <= gene$startpos & 
                           endpos >= gene$endpos)
  if (nrow(matched_rows) > 0) {
    matched_rows$hgnc_symbol <- gene$hgnc_symbol
    match_list[[i]] <- matched_rows
  }
}

# 合并结果
result <- do.call(rbind, match_list)

# 查看结果
print(result)

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