r - 如何对范围内的行进行分组并考虑第三列？

Question

我有一个遗传数据集，我想对基因组中物理上靠近的遗传变异/行进行分组。我想对每个染色体( chrom )中基因组中某些点范围内的基因进行分组。
我的 'spots' 数据集是变体/行需要在一个范围内的位置，看起来像:

 chrom      low       high
   1        500       1700
   1        19500     20600
   5        400       1500

我的 low和 high列是我想查看下一个数据集中是否有任何行落入的范围，同时考虑到染色体( chrom )也必须匹配。具有唯一范围和色度组合的每一行都是它自己的组，我希望查看我的其他数据集中是否有任何内容。
我的另一个数据集有一个位置值，我想看看它是否符合上述任何范围并匹配 chrom ，为了将其标记为对应于该范围，然后我可以将同一范围内的位置和 chrom 组合在一起:

Gene   chrom position 
Gene1   1    1200          
Gene2   1    10000        
Gene3   5    500 
Gene4   5    560
Gene5   1    20100           

我试过使用 group_by()和 between()设置范围，因为看到其他与日期/时间范围类似的问题，但我正在努力考虑在搜索范围之前匹配数据集之间的染色体( chrom )的需要。
输出看起来像:

Gene   chrom position   Group 
Gene1   1    1200          1  #position is in one of the ranges and matches the chrom so is in a group    
Gene2   1    10000        NA  #does not fit into any range on chrom 2 (no matches)
Gene3   5    500           2  #position is in one of the ranges and matches the chrom so is in a group
Gene4   5    560           2  #position is in the same range and chrom as above so joins that group
Gene5   1    20100         3  #position matches a chrom and range and so gets a group corresponding to that particular chrom and range

Gene3 和 Gene4 不在组 1 中，因为它们位于不同的 chrom ，但它们确实与 chrom 匹配，并且在我的第一个数据集的第 3 行的范围内 - 所以它们会在对应于该范围和 chrom 的组中。

Gene5 与 Gene1 不在同一组中，因为它们匹配 chrom它们在 low 的不同范围内和 high ，因此为独特的范围获取自己的组。

所以我正在创建一个 Group low 之间相同范围内所有行的共享编号的列和 high同上 chrom , 或者 NA 如果它们的位置在第一个数据集中的任何范围和 chrom 中都不匹配。
输入数据:

df1 <- 
structure(list(chrom = c(1L, 1L, 5L), 
   low = c(500L, 19500L, 400L), high = c(1700L, 20600L, 1500L
    )), row.names = c(NA, -3L), class = c("data.table", "data.frame"))

df2 <- 
structure(list(Gene = c("Gene1", "Gene2", "Gene3", "Gene4", "Gene5"
), chrom = c(1L, 1L, 5L, 5L, 1L), position = c(1200L, 10000L, 
500L, 560L, 20100L)), row.names = c(NA, -5L), class = c("data.table", 
"data.frame"))

我还在考虑为每个唯一的范围和色度组合提供我的第一个数据集唯一标识符，然后将该标识符分配给数据集 2 中也匹配该组合的任何行，以便该标识符创建我的组号列。虽然我的真实数据是 2.3k 行范围和 82k 行匹配到共享组，所以我在运行 dplyr 选项时也遇到了问题，我通常会尝试。

Answer 1

如果你知道 sql那么这可以在sql + R中快速解决:

df1$group_id <- seq(nrow(df1)) #This creates the unique groups for each interval

sqldf::sqldf('
    SELECT df2.*, df1.group_id 
    FROM df2 
    LEFT JOIN df1 
    ON df2.chrom = df1.chrom AND position between low AND high')

  Gene chrom position group_id
1 Gene1     1     1200        1
2 Gene2     1    10000       NA
3 Gene3     5      500        3
4 Gene4     5      560        3
5 Gene5     1    20100        2