两个数据帧，连接ID和从1 df开始的位置(如果值在第二df范围内) [英] Two data frames, join ID's and position from one df if value is in the range of 2nd df
问题描述
从昨天开始，我一直在努力解决我的问题... 我认为，最好的解释是当我向您展示示例时，所以我有两个df的x和y，它们看起来像这样:
Since yesterday I've been trying to solve my problem... I think, the best explanation will be when I show you the example, so I have two df's x and y and they look like this:
ID_x <- c('x1','x2','x3','x4','x5','x6','x7','x8','x9','x10')
chr_x <- c('chr1','chr1','chr1','chr1','chr1','chr1','chr1','chr1','chrX','chrY')
start <- c(1,2,3,4,50,60,70,80,9,100)
stop <- c(10,20,30,40,500,600,700,800,95,1000)
x <- data.frame(ID_x,chr_x,start,stop)
> x
ID_x chr_x start stop
1 x1 chr1 1 10
2 x2 chr1 2 20
3 x3 chr1 3 30
4 x4 chr1 4 40
5 x5 chr1 50 500
6 x6 chr1 60 600
7 x7 chr1 70 700
8 x8 chr1 80 800
9 x9 chrX 9 90
10 x10 chrY 100 1000
第二个:
ID_y <- c('y1','y2','y3','y4','y5','y6','y7','y8','y9','y10')
chr_y <- c('chr1','chr1','chr1','chr1','chr2','chr2','chr3','chr3','chrX','chrY')
pos_y <- c(8,9,15,31,41,2,81,8,91,8)
y <- data.frame(ID_y,chr_y,pos_y)
> y
ID_y chr_y pos_y
1 y1 chr1 8
2 y2 chr1 9
3 y3 chr1 15
4 y4 chr1 31
5 y5 chr2 41
6 y6 chr2 2
7 y7 chr3 81
8 y8 chr3 8
9 y9 chrX 91
10 y10 chrY 8
现在，我想根据范围(开始，停止)和chr将第二个df(y)的行映射到第一个df(x). 我想列出从y df(如果它们满足条件)到df x的每一行的所有id. 没有这样的id_y，然后是NA. 所以预期的结果:
Now I'd like to map rows from 2nd df (y) to first df (x) based on range (start, stop) and chr. I'd like to list all id's from y df (if they fulfill the criteria) to each row of df x. Of there's no such id_y, then NA. So the expectd result:
ID_x chr_x start stop ID_y pos_y
1 x1 chr1 1 10 y1,y2 8,9
2 x2 chr1 2 20 y1,y2,y3 8,9,15
3 x3 chr1 3 30 y1,y2,y3,y4 8,9,15,31
4 x4 chr1 4 40 y1,y2,y3,y4 8,9,15,31
5 x5 chr1 50 500 NA NA
6 x6 chr1 60 600 NA NA
7 x7 chr1 70 700 NA NA
8 x8 chr1 80 800 NA NA
9 x9 chrX 9 95 y9 91
10 x10 chrY 100 1000 NA NA
乍一看，我认为这只是小菜一碟，但是当我尝试解决此问题时，我尝试了几种方法...
与ifelse()嵌套的apply()给了我巨大的矩阵，上面有很多NA ...
我发现了类似的问题和解决方法 ，但是我的原始数据集很大，df x有134748行，df y有865859行.我遇到了内存分配错误，即使我尝试用一个染色体执行此操作也是如此.
At first glance I thought that it's piece of cake, but when i tried to solve this... I tried several approaches...
Nested apply() with ifelse() was giving me a huge matrix with a lots of NA's...
I found similar problem and solution here but my original data set is huge, df x has 134748 rows and df y has 865859 rows. I had the memory allocation error, even when I tried to do this with one chromosome.
您能给我一个提示怎么做吗?
Could you please give me a hint how to do this?
谢谢你， 亚当
推荐答案
使用data.table联接应该对此有更有效的解决方案，但这是使用dplyr的一种方法.我们首先用"chr"列对x和y进行left_join，然后在start和stop之间的filter行，为每个chr_x，start和stop，最后是right_join和x，以得到NA的缺失范围.
There should be a more efficient solution to this using data.table joins but here is one way to do it using dplyr. We first do a left_join of x and y by "chr" columns, then filter rows which is between start and stop, create a comma-separated string for each chr_x, start and stop and finally right_join with x to get missing range with NA.
library(dplyr)
left_join(x, y, by = c("chr_x" = "chr_y")) %>%
filter(data.table::between(pos_y, start, stop)) %>%
group_by(chr_x, start, stop) %>%
summarise(ID_y = toString(ID_y), pos_y = toString(pos_y)) %>%
right_join(x)
# chr_x start stop ID_y pos_y ID_x
# <chr> <dbl> <dbl> <chr> <chr> <fct>
# 1 chr1 1 10 y1, y2 8, 9 x1
# 2 chr1 2 20 y1, y2, y3 8, 9, 15 x2
# 3 chr1 3 30 y1, y2, y3 8, 9, 15 x3
# 4 chr1 4 40 y1, y2, y3, y4 8, 9, 15, 31 x4
# 5 chr1 50 500 NA NA x5
# 6 chr1 60 600 NA NA x6
# 7 chr1 70 700 NA NA x7
# 8 chr1 80 800 NA NA x8
# 9 chrX 9 95 y9 91 x9
#10 chrY 100 1000 NA NA x10
这篇关于两个数据帧，连接ID和从1 df开始的位置(如果值在第二df范围内)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
