在R中对两个数据框进行分组后找到重叠的范围。

huangapple
go评论104阅读模式
英文:

Find overlapping ranges between two data frames after grouping in R

问题

我有两个类似这样的大型数据框：

  1. df1 <- tibble(chrom=c(1,1,1,2,2,2),
  2.               start=c(100,200,300,100,200,300),
  3.               end=c(150,250,350,120,220,320))
  5. df2 <- tibble(chrom=c(1,1,1,2,2,2),
  6.               start2=c(100,50,280,100,10,200),
  7.               end2=c(125,100,320,115,15,350))
  9. df1
  10. #> # A tibble: 6 × 3
  11. #>   chrom start   end
  12. #>   <dbl> <dbl> <dbl>
  13. #> 1     1   100   150
  14. #> 2     1   200   250
  15. #> 3     1   300   350
  16. #> 4     2   100   120
  17. #> 5     2   200   220
  18. #> 6     2   300   320
  19. df2
  20. #> # A tibble: 6 × 3
  21. #>   chrom start2  end2
  22. #>   <dbl>  <dbl> <dbl>
  23. #> 1     1    100   125
  24. #> 2     1     50   100
  25. #> 3     1    280   320
  26. #> 4     2    100   115
  27. #> 5     2     10    15
  28. #> 6     2    200   350

我想要找到df2的范围[start2-end2]与df1的范围[start-end]重叠的部分。理想的输出可能类似于以下内容，但不一定需要。主要是我想要重叠范围的坐标。

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

请注意，在最后一行中，范围200-350已与df1中的两个范围[200-220，300-320]重叠。

英文:

I have two large data frames that look like this:

  1. df1 &lt;- tibble(chrom=c(1,1,1,2,2,2),
  2.               start=c(100,200,300,100,200,300),
  3.               end=c(150,250,350,120,220,320))
  5. df2 &lt;- tibble(chrom=c(1,1,1,2,2,2),
  6.               start2=c(100,50,280,100,10,200),
  7.               end2=c(125,100,320,115,15,350))
  9. df1
  10. #&gt; # A tibble: 6 &#215; 3
  11. #&gt;   chrom start   end
  12. #&gt;   &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
  13. #&gt; 1     1   100   150
  14. #&gt; 2     1   200   250
  15. #&gt; 3     1   300   350
  16. #&gt; 4     2   100   120
  17. #&gt; 5     2   200   220
  18. #&gt; 6     2   300   320
  19. df2
  20. #&gt; # A tibble: 6 &#215; 3
  21. #&gt;   chrom start2  end2
  22. #&gt;   &lt;dbl&gt;  &lt;dbl&gt; &lt;dbl&gt;
  23. #&gt; 1     1    100   125
  24. #&gt; 2     1     50   100
  25. #&gt; 3     1    280   320
  26. #&gt; 4     2    100   115
  27. #&gt; 5     2     10    15
  28. #&gt; 6     2    200   350

<sup>Created on 2023-01-09 with reprex v2.0.2</sup>

I want to find which range[start2-end2] of df2 overlaps with the range[start-end] of df1.
An ideal output would be something like this, but it's not necessary. Mostly I want the coordinates of the overlapping ranges.

  2. #&gt; # A tibble: 6 &#215; 8
  3. #&gt;   chrom start   end start2  end2 overlap overlap_start overlap_end
  4. #&gt;   &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;  &lt;dbl&gt; &lt;dbl&gt; &lt;chr&gt;   &lt;chr&gt;         &lt;chr&gt;      
  5. #&gt; 1     1   100   150    100   125 yes     100           125        
  6. #&gt; 2     1   200   250     50   100 no      &lt;NA&gt;          &lt;NA&gt;       
  7. #&gt; 3     1   300   350    280   320 yes     300           320        
  8. #&gt; 4     2   100   120    100   115 yes     100           115        
  9. #&gt; 5     2   200   220     10    15 no      &lt;NA&gt;          &lt;NA&gt;       
  10. #&gt; 6     2   300   320    200   350 yes     200,220       300,320

<sup>Created on 2023-01-09 with reprex v2.0.2</sup>

!Note that on the last line, the range 200-350 overlaps already with two ranges from df1[200-220, 300-320].

答案1

得分: 2

I believe you are looking for something like this?

我相信你在寻找类似这样的内容：

I see no need to summarize here, so you'll get two results for the df2-range 200-350.

我在这里不需要总结，所以你将得到df2范围为200-350的两个结果。

  1. library(data.table)
  2. library(matrixStats)
  3. # set to data.table format
  4. setDT(df1); setDT(df2)
  5. # perform join
  6. ans <- df1[df2, .(chrom, 
  7.                   start = x.start, end = x.end, 
  8.                   start2 = i.start2, end2 = i.end2), 
  9.            on = .(chrom, start < end2, end > start2),
  10.            nomatch = NA]
  12. # calculate new columns
  13. ans[, overlap_start := rowMaxs(as.matrix(.SD)), .SDcols = c("start", "start2")]
  14. ans[, overlap_end := rowMins(as.matrix(.SD)), .SDcols = c("end", "end2")]
  15. #    chrom start end start2 end2 overlap_start overlap_end
  16. # 1:     1   100 150    100  125           100         125
  17. # 2:     1    NA  NA     50  100            NA          NA
  18. # 3:     1   300 350    280  320           280         320
  19. # 4:     2   100 120    100  115           100         115
  20. # 5:     2    NA  NA     10   15            NA          NA
  21. # 6:     2   200 220    200  350           200         220
  22. # 7:     2   300 320    200  350           200         320

以上是翻译好的代码部分。

英文:

I believe you are looking for sometehing like this?

I see no need to summarise here, so you'll get two results for the df2-range 200-350.

  1. library(data.table)
  2. library(matrixStats)
  3. # set to data.table format
  4. setDT(df1); setDT(df2)
  5. # perform join
  6. ans &lt;- df1[df2, .(chrom, 
  7.                   start = x.start, end = x.end, 
  8.                   start2 = i.start2, end2 = i.end2), 
  9.            on = .(chrom, start &lt; end2, end &gt; start2),
  10.            nomatch = NA]
  12. # calculate new columns
  13. ans[, overlap_start := rowMaxs(as.matrix(.SD)), .SDcols = c(&quot;start&quot;, &quot;start2&quot;)]
  14. ans[, overlap_end := rowMins(as.matrix(.SD)), .SDcols = c(&quot;end&quot;, &quot;end2&quot;)]
  16. #    chrom start end start2 end2 overlap_start overlap_end
  17. # 1:     1   100 150    100  125           100         125
  18. # 2:     1    NA  NA     50  100            NA          NA
  19. # 3:     1   300 350    280  320           280         320
  20. # 4:     2   100 120    100  115           100         115
  21. # 5:     2    NA  NA     10   15            NA          NA
  22. # 6:     2   200 220    200  350           200         220
  23. # 7:     2   300 320    200  350           200         320

答案2

得分: 2

我的建议是使用Bioconductor包GenomicRanges，它可以使用最优的数据结构来查找区间重叠。

  1. library(GenomicRanges)
  3. df1 <- tibble(chrom=c(1,1,1,2,2,2),
  4.               start=c(100,200,300,100,200,300),
  5.               end=c(150,250,350,120,220,320))
  7. df2 <- tibble(chrom=c(1,1,1,2,2,2),
  8.               start2=c(100,50,280,100,10,200),
  9.               end2=c(125,100,320,115,15,350))
  11. overlaps <- findOverlapPairs(makeGRangesFromDataFrame(df1),
  12.                              makeGRangesFromDataFrame(df2,
  13.                                                       end.field = "end2",
  14.                                                       start.field = "start2"))
  16. > overlaps
  17. Pairs object with 6 pairs and 0 metadata columns:
  18.           first    second
  19.       <GRanges> <GRanges>
  20.   [1] 1:100-150  1:50-100
  21.   [2] 1:100-150 1:100-125
  22.   [3] 1:300-350 1:280-320
  23.   [4] 2:100-120 2:100-115
  24.   [5] 2:200-220 2:200-350
  25.   [6] 2:300-320 2:200-350
  27. mapply(as.data.frame,
  28.        list(S4Vectors::first(overlaps),
  29.             S4Vectors::second(overlaps)),
  30.        SIMPLIFY = FALSE) |
  31.     do.call(what = `cbind`)
  33.   seqnames start end width strand seqnames start end width strand
  34. 1        1   100 150    51      *        1    50 100    51      *
  35. 2        1   100 150    51      *        1   100 125    26      *
  36. 3        1   300 350    51      *        1   280 320    41      *
  37. 4        2   100 120    21      *        2   100 115    16      *
  38. 5        2   200 220    21      *        2   200 350   151      *
  39. 6        2   300 320    21      *        2   200 350   151      *
英文:

My advise is to use the Bioconductor package GenomicRanges, which can use optimal data structures for finding interval overlaps.

  1. library(GenomicRanges)
  3. df1 &lt;- tibble(chrom=c(1,1,1,2,2,2),
  4.               start=c(100,200,300,100,200,300),
  5.               end=c(150,250,350,120,220,320))
  7. df2 &lt;- tibble(chrom=c(1,1,1,2,2,2),
  8.               start2=c(100,50,280,100,10,200),
  9.               end2=c(125,100,320,115,15,350))
  12. overlaps &lt;- findOverlapPairs(makeGRangesFromDataFrame(df1),
  13.                              makeGRangesFromDataFrame(df2,
  14.                                                       end.field = &quot;end2&quot;,
  15.                                                       start.field = &quot;start2&quot;))
  18. &gt; overlaps
  19. Pairs object with 6 pairs and 0 metadata columns:
  20.           first    second
  21.       &lt;GRanges&gt; &lt;GRanges&gt;
  22.   [1] 1:100-150  1:50-100
  23.   [2] 1:100-150 1:100-125
  24.   [3] 1:300-350 1:280-320
  25.   [4] 2:100-120 2:100-115
  26.   [5] 2:200-220 2:200-350
  27.   [6] 2:300-320 2:200-350
  29. mapply(as.data.frame,
  30.        list(S4Vectors::first(overlaps),
  31.             S4Vectors::second(overlaps)),
  32.        SIMPLIFY = FALSE) |&gt;
  33.     do.call(what = `cbind`)
  35.   seqnames start end width strand seqnames start end width strand
  36. 1        1   100 150    51      *        1    50 100    51      *
  37. 2        1   100 150    51      *        1   100 125    26      *
  38. 3        1   300 350    51      *        1   280 320    41      *
  39. 4        2   100 120    21      *        2   100 115    16      *
  40. 5        2   200 220    21      *        2   200 350   151      *
  41. 6        2   300 320    21      *        2   200 350   151      *

答案3

得分: 0

以下是翻译好的部分：

  1. # 一个更长的“整洁风格”版本：
  2. ```R
  3. library(dplyr)
  5. df1 |&gt;
  6.   left_join(df2, by = 'chrom') |&gt;
  7.   rowwise() |&gt;
  8.   mutate(range1 = list(start:end),
  9.          range2 = list(start2:end2),
  10.          intersect = list(intersect(start:end, start2:end2)),
  11.          overlap = c('no', 'yes')[1 + sign(length(intersect))],
  12.          overlap_start = ifelse(length(intersect), min(intersect), NA),
  13.          overlap_end = ifelse(length(intersect), max(intersect), NA),
  14.          ) |&gt;
  15.   group_by(paste(start2, end2)) |&gt;
  16.   summarise(across(chrom : end2),
  17.             overlap,
  18.             across(starts_with('overlap_'),
  19.                    ~ paste(na.omit(.x), collapse = ','))
  20.             ) |&gt;
  21.   ungroup() |&gt;
  22.   select(chrom:overlap_end)
  1. # 一个数据框：18 x 8
  2.    chrom start   end start2  end2 overlap overlap_start overlap_end
  3.    <dbl> <dbl> <dbl>  <dbl> <dbl> <chr>   <chr>         <chr>      
  4.  1     2   100   120     10    15 no      ""            ""         
  5.  2     2   200   220     10    15 no      ""            ""         
  6.  3     2   300   320     10    15 no      ""            ""         
  7.  4     2   100   120    100   115 yes     "100"         "115"      
  8.  5     2   200   220    100   115 no      "100"         "115"      
  9.  6     2   300   320    100   115 no      "100"         "115"      
  10.  7     1   100   150    100   125 yes     "100"         "125"      
  11.  8     1   200   250    100   125 no      "100"         "125"      
  12.  9     1   300   350    100   125 no      "100"         "125"      
  13. 10     2   100   120    200   350 no      "200,300"     "220,320" 
  14. # ...

要获取数值向量而不是多个重叠的逗号分隔字符串，请使用以下代码片段进行总结：

  1. ## ...
  2.     across(starts_with('overlap_'),
  3.            ~ list(c(na.omit(.x)))
  4.            )
英文:

A lengthier "tidy-style" version:

  1. library(dplyr)
  2. df1 |&gt;
  3. left_join(df2, by = &#39;chrom&#39;) |&gt;
  4. rowwise() |&gt;
  5. mutate(range1 = list(start:end),
  6. range2 = list(start2:end2),
  7. intersect = list(intersect(start:end, start2:end2)),
  8. overlap = c(&#39;no&#39;, &#39;yes&#39;)[1 + sign(length(intersect))],
  9. overlap_start = ifelse(length(intersect), min(intersect), NA),
  10. overlap_end = ifelse(length(intersect), max(intersect), NA),
  11. ) |&gt;
  12. group_by(paste(start2, end2)) |&gt;
  13. summarise(across(chrom : end2),
  14. overlap,
  15. across(starts_with(&#39;overlap_&#39;),
  16. ~ paste(na.omit(.x), collapse = &#39;,&#39;))
  17. ) |&gt;
  18. ungroup() |&gt;
  19. select(chrom:overlap_end)
  1. # A tibble: 18 x 8
  2. chrom start   end start2  end2 overlap overlap_start overlap_end
  3. &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;  &lt;dbl&gt; &lt;dbl&gt; &lt;chr&gt;   &lt;chr&gt;         &lt;chr&gt;      
  4. 1     2   100   120     10    15 no      &quot;&quot;            &quot;&quot;         
  5. 2     2   200   220     10    15 no      &quot;&quot;            &quot;&quot;         
  6. 3     2   300   320     10    15 no      &quot;&quot;            &quot;&quot;         
  7. 4     2   100   120    100   115 yes     &quot;100&quot;         &quot;115&quot;      
  8. 5     2   200   220    100   115 no      &quot;100&quot;         &quot;115&quot;      
  9. 6     2   300   320    100   115 no      &quot;100&quot;         &quot;115&quot;      
  10. 7     1   100   150    100   125 yes     &quot;100&quot;         &quot;125&quot;      
  11. 8     1   200   250    100   125 no      &quot;100&quot;         &quot;125&quot;      
  12. 9     1   300   350    100   125 no      &quot;100&quot;         &quot;125&quot;      
  13. 10     2   100   120    200   350 no      &quot;200,300&quot;     &quot;220,320&quot; 
  14. # ...

to obtain numeric vectors instead of comma-separated strings for multiple overlaps, summarize with the following fragment instead:

  1. ## ...
  2. across(starts_with(&#39;overlap_&#39;),
  3. ~ list(c(na.omit(.x)))
  4. )

huangapple
  • 本文由 发表于 2023年1月9日 18:46:37
  • 转载请务必保留本文链接：https://go.coder-hub.com/75056152.html
  • data.table
  • dplyr
  • r
  • tidyverse
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定