在R语言中分组后查找两个数据框的重叠区间

我有两个大型数据框，结构如下：

df1 <- tibble(chrom=c(1,1,1,2,2,2),
              start=c(100,200,300,100,200,300),
              end=c(150,250,350,120,220,320))

df2 <- tibble(chrom=c(1,1,1,2,2,2),
              start2=c(100,50,280,100,10,200),
              end2=c(125,100,320,115,15,350))

需要找到df2中[start2-end2]区间与df1中同染色体的[start-end]区间的重叠部分，核心目标是获取重叠区间的坐标，理想输出示例如下：

#> # A tibble: 6 × 8
#>   chrom start   end start2  end2 overlap overlap_start overlap_end
#>   <dbl> <dbl> <dbl>  <dbl> <dbl> <chr>   <chr>         <chr>      
#> 1     1   100   150    100   125 yes     100           125        
#> 2     1   200   250     50   100 no      <NA>          <NA>       
#> 3     1   300   350    280   320 yes     300           320        
#> 4     2   100   120    100   115 yes     100           115        
#> 5     2   200   220     10    15 no      <NA>          <NA>       
#> 6     2   300   320    200   350 yes     200,300       220,320

注意：最后一行中，df2的200-350区间与df1中的两个区间[200-220, 300-320]存在重叠。

方法1：用tidyverse原生实现（适合中等规模数据） #

通过交叉连接匹配同染色体区间，计算重叠坐标后聚合结果：

library(tidyverse)

# 按染色体交叉连接df1和df2，计算每个区间对的重叠情况
cross_df <- df2 %>%
  inner_join(df1, by = "chrom") %>%
  mutate(
    overlap_start = pmax(start, start2),
    overlap_end = pmin(end, end2),
    has_overlap = overlap_start <= overlap_end
  ) %>%
  filter(has_overlap) %>%
  group_by(chrom, start2, end2) %>%
  summarise(
    overlap_start = str_c(overlap_start, collapse = ","),
    overlap_end = str_c(overlap_end, collapse = ","),
    .groups = "drop"
  )

# 合并回原始数据，补充重叠标记
final_df <- df1 %>%
  mutate(row_id = row_number()) %>%
  inner_join(df2 %>% mutate(row_id = row_number()), by = c("row_id", "chrom")) %>%
  left_join(cross_df, by = c("chrom", "start2", "end2")) %>%
  mutate(overlap = ifelse(is.na(overlap_start), "no", "yes")) %>%
  select(chrom, start, end, start2, end2, overlap, overlap_start, overlap_end) %>%
  arrange(chrom, start)

运行后即可得到符合需求的结果，核心逻辑是：

1. 交叉连接同染色体的所有区间对
2. 用pmax/pmin计算重叠区间的起止坐标
3. 过滤有效重叠并按df2的区间聚合多段重叠结果
4. 合并回原始行，补充是否重叠的标记

方法2：用GenomicRanges实现（适合超大规模数据） #

如果数据量达到百万级以上，推荐使用专门处理区间的GenomicRanges包，底层为C++实现，效率更高：

library(tidyverse)
library(GenomicRanges)

# 转换为GRanges对象
gr1 <- makeGRangesFromDataFrame(df1, seqnames.field = "chrom", start.field = "start", end.field = "end")
gr2 <- makeGRangesFromDataFrame(df2, seqnames.field = "chrom", start.field = "start2", end.field = "end2")

# 找到所有重叠对并计算重叠区间
hits <- findOverlaps(gr2, gr1)
overlap_ranges <- pintersect(gr2[queryHits(hits)], gr1[subjectHits(hits)])

# 整理聚合重叠结果
overlap_df <- as.data.frame(overlap_ranges) %>%
  mutate(df2_row = queryHits(hits)) %>%
  group_by(df2_row) %>%
  summarise(
    overlap_start = str_c(start, collapse = ","),
    overlap_end = str_c(end, collapse = ","),
    .groups = "drop"
  )

# 合并回原始数据
final_df <- df1 %>%
  mutate(row_id = row_number()) %>%
  bind_cols(df2) %>%
  left_join(overlap_df, by = c("row_id" = "df2_row")) %>%
  mutate(overlap = ifelse(is.na(overlap_start), "no", "yes")) %>%
  select(chrom, start, end, start2, end2, overlap, overlap_start, overlap_end)

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