按索引范围连接两表后行数异常增加，求排查与独热编码实现

一、行数从2832变为3298的原因排查 #

• 核心原因：基因区间存在重叠，单个pos匹配到多个基因区间，导致原df中的一条记录被拆分成多条（匹配到几个基因就生成几条）。比如示例里FAM151A的区间完全包含在ACOT11的区间内，落在该重叠区的pos会同时匹配两个基因，直接让总条数增加。

• 排查验证步骤：

  1. 找出匹配多个基因的pos记录：

     df_with_gene_name %>%
       group_by(rsid, pos) %>%
       summarise(gene_count = n(), .groups = "drop") %>%
       filter(gene_count > 1) %>%
       head(10)
     

     运行后会看到哪些rsid/pos对应多个基因，以及具体的匹配数量。

  2. 查看重叠区间的具体情况：
     取一个上述结果中的target_pos，查看对应的基因区间：

     target_pos <- 55075000  # 示例中落在ACOT11和FAM151A重叠区的pos
     gene_list_selected %>%
       filter(start_pos <= target_pos, end_pos >= target_pos)
     

     输出会显示该pos同时属于多个基因的区间，这就是行数增加的直接原因。

二、实现指定字段保留与基因独热编码 #

根据需求，分两种场景处理：

场景1：保留所有匹配的区间记录（允许一行变多行） #

每个pos匹配到几个基因就生成几条记录，同时生成独热编码列：

library(dplyr)
library(tidyr)

# 执行模糊左连接，保留df中所有行（包括未匹配到基因的）
df_joined <- df %>%
  fuzzy_left_join(gene_list_selected,
                  by = c("pos" = "start_pos", "pos" = "end_pos"),
                  match_fun = list(`>=`, `<=`))

# 生成独热编码并保留指定字段
df_result <- df_joined %>%
  mutate(value = 1) %>%
  pivot_wider(
    names_from = gene_name,
    values_from = value,
    values_fill = 0
  ) %>%
  select(rsid, pos, chr, start_pos, end_pos, all_of(unique(gene_list_selected$gene_name)))

场景2：合并重复的pos记录（同一pos只保留一行） #

将同一个pos匹配到的多个基因合并到一行，对应独热列标记为1：

library(dplyr)
library(tidyr)

df_joined <- df %>%
  fuzzy_left_join(gene_list_selected,
                  by = c("pos" = "start_pos", "pos" = "end_pos"),
                  match_fun = list(`>=`, `<=`))

df_result <- df_joined %>%
  # 为每个匹配的基因标记1
  mutate(!!gene_name := 1) %>%
  # 按rsid和pos分组，合并区间（这里取第一个匹配的区间，可按需调整）
  group_by(rsid, pos) %>%
  summarise(
    chr = first(chr),
    start_pos = first(start_pos),
    end_pos = first(end_pos),
    # 每个基因列取最大值（只要匹配过就为1）
    across(all_of(unique(gene_list_selected$gene_name)), ~max(., na.rm = TRUE)),
    .groups = "drop"
  ) %>%
  # 未匹配到基因的列填充为0
  mutate(across(all_of(unique(gene_list_selected$gene_name)), ~ifelse(is.na(.), 0, .)))

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