如何查找两个不同dataframe的重叠区间并写入对应bin

工具适配性判断 #

• GenomicRanges包的findOverlaps是最适配该需求的方案，原生支持区间重叠查询、重叠长度计算，无需手动写循环，性能远高于自行遍历或拆分bin的方案。
• 纯dplyr方案也可实现，但需要做笛卡尔积关联再过滤重叠区间，数据量较大时性能会明显下降，仅适合小数据集场景。

具体实现代码示例 #

基于GenomicRanges的最优方案 #

步骤1：构造GRanges对象

library(GenomicRanges)
library(dplyr)

# 第一个存储子集区间的df记为df_region，第二个存储bin的df记为df_bin
# 构造区域GRanges对象
gr_region <- GRanges(
  seqnames = df_region$Chromosome,
  ranges = IRanges(start = df_region$Start, end = df_region$End)
)

# 构造bin的GRanges对象
gr_bin <- GRanges(
  seqnames = df_bin$Chromosome,
  ranges = IRanges(start = df_bin$BinStart, end = df_bin$BinEnd),
  bin = df_bin$bin
)

步骤2：查询重叠并计算总重叠长度

# 查找所有重叠的区间对
overlap_hits <- findOverlaps(gr_bin, gr_region)
# 计算每对重叠区间的重叠长度
overlap_width <- width(pintersect(gr_bin[queryHits(overlap_hits)], gr_region[subjectHits(overlap_hits)]))
# 按bin分组汇总总重叠长度、重叠次数
bin_stats <- aggregate(
  overlap_width, 
  by = list(bin_idx = queryHits(overlap_hits)), 
  FUN = function(x) c(total_length = sum(x), overlap_count = length(x))
)

步骤3：合并结果回原bin表

# 初始化默认值为0
df_bin <- df_bin %>%
  mutate(
    overlap_length = 0,
    `Number of Overlaps` = 0
  )
# 填充有重叠的bin的统计值
df_bin$overlap_length[bin_stats$bin_idx] <- bin_stats$x[, "total_length"]
df_bin$`Number of Overlaps`[bin_stats$bin_idx] <- bin_stats$x[, "overlap_count"]

纯dplyr实现（仅适合小数据量场景） #

如果不想引入Bioconductor依赖，也可以用dplyr实现：

df_bin <- df_bin %>%
  # 关联两个表的同染色体区间
  left_join(df_region, by = "Chromosome") %>%
  # 过滤出重叠的区间对
  filter(Start <= BinEnd, End >= BinStart) %>%
  # 计算单组重叠长度
  mutate(single_overlap = pmin(End, BinEnd) - pmax(Start, BinStart) + 1) %>%
  # 按bin汇总统计
  group_by(bin, BinStart, BinEnd) %>%
  summarise(
    overlap_length = sum(single_overlap),
    `Number of Overlaps` = n(),
    .groups = "drop"
  ) %>%
  # 补全无重叠的bin，赋值为0
  right_join(df_bin %>% select(-`Number of Overlaps`), by = c("bin", "BinStart", "BinEnd")) %>%
  mutate(
    overlap_length = coalesce(overlap_length, 0),
    `Number of Overlaps` = coalesce(`Number of Overlaps`, 0)
  )

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