如何在R中基于ID匹配或区间条件实现DataFrame的full_join？

需要将多个DataFrame（如示例中的data1、data2）进行全连接（full_join），连接条件满足以下任一即可：

• 两个DataFrame的ID完全匹配（id1 == id2）
• data2的location2值处于data1的min1与max1构成的区间范围内（location2 >= min1 & location2 <= max1）

示例数据 #

data1 <- data.frame(id1 = c("A","B","C","D"),                  
                    location1 = c(123,247,335,454),
                    min1 = c(100,200,300,400),
                    max1 = c(199,299,399,499))

data2 <- data.frame(id2 = c("A","E","F"),                  
                    location2 = c(123,221,522),
                    min2 = c(100,212,500),
                    max2 = c(199,221,599))

期望结果 #

id1 location1 min1 max1 id2 location2 min2 max2
1    A       123  100  199    A       123  100  199
2    B       247  200  299    E       221  212  221
3    C       335  300  399   NA        NA   NA   NA
4    D       454  400  499   NA        NA   NA   NA
5   NA        NA   NA   NA    F       522  500  599

各行说明 #

• 第1行：ID完全匹配，直接关联
• 第2行：ID不匹配，但location2处于data1的区间范围内，因此关联
• 第3/4行：data1中无匹配项，保留原数据
• 第5行：data2中无匹配项，保留原数据

实现代码 #

使用dplyr包可以完成该需求，步骤如下：

1. 先基于ID匹配做全连接，得到初步结果
2. 筛选出data2中未匹配到ID的行，再基于区间条件与data1做连接
3. 合并两次连接的结果，去除重复行，最后补全未匹配的行

library(dplyr)

# 第一步：基于ID匹配的全连接
id_join <- full_join(data1, data2, by = c("id1" = "id2"))

# 第二步：处理data2中未通过ID匹配的行，基于区间匹配
unmatched_data2 <- data2 %>% filter(!id2 %in% id_join$id1[!is.na(id_join$id1)])
interval_join <- unmatched_data2 %>%
  cross_join(data1) %>%
  filter(location2 >= min1 & location2 <= max1) %>%
  select(names(id_join))

# 第三步：合并结果，去重，补全未匹配的行
final_result <- bind_rows(id_join, interval_join) %>%
  distinct() %>%
  bind_rows(data2 %>% filter(!id2 %in% final_result$id2[!is.na(final_result$id2)]) %>% mutate(across(names(data1), ~NA))) %>%
  arrange(ifelse(is.na(id1), 100, match(id1, c("A","B","C","D"))))

# 查看结果
print(final_result)

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