r语言 - 从数据之间的多对多匹配中选择一个.表dt1$id和dt2$id，使dt1$id和dt2$id在结果集中是唯一的.&

Question

我有两个数据。表的dt1和dt2，其中id1和id2作为唯一键列，并且我与匹配的id1和id2执行不等多对多连接结果集dtR。

library(data.table)
dt1 = data.table(id1 = c(1:12),a = c(1,1,1,1,2,2,2,2,3,3,3,3),key='id1')
dt2 = data.table(id2 = c(13:24), b = c(0,0,1,1,2,2,3,3,4,4,5,5),key='id2')
dtR <- dt2[dt1,.(id1,id2,a,b),on=.(b < a)]

我如何过滤dtR，使id1在dtR$id1列中唯一，id2在dtR$id2中唯一:

data.table(
id1=c(1,2,5,6,9,10),id2=c(13,14,15,16,17,18))

注意，dt1和dt2涉及数百万行和高内存，因此它应该是高效的，并且应该避免复制或循环。谢谢!

我尝试迭代id1，创建一个与id2匹配的额外列，并检查现有的id2，添加下一个。非常耗时。SQL解决方案也可以接受，因为dt1和dt2驻留在DuckDb中。

Answer 1

data.table分组解决方案将非常快。

system.time({
u2 <- unique(sort(dtR$id2))
avail <- rep(TRUE, length(u2))
x <- dtR[, m2 := match(id2, u2)][
, .(
id2 = {
m <- m2[which.max(avail[m2])]
if (avail[m]) {
avail[m] <- FALSE
u2[m]
}  else NULL
}
), id1
]
})
#>    user  system elapsed 
#>       0       0       0
x
#>       id1  id2
#>   1:    1   77
#>   2:    2  188
#>   3:    3  109
#>   4:    4   14
#>   5:    5   47
#>  ---          
#> 983:  996 9472
#> 984:  997 9036
#> 985:  998 9688
#> 986:  999 8646
#> 987: 1000 9066
uniqueN(x[[1]])
#> [1] 987
uniqueN(x[[2]])
#> [1] 987

数据:

library(data.table)
set.seed(42)
dt1 = data.table(
id1 = c(1:1e3),
a = sample(1:1e2,1e3,replace = T),key='id1')
dt1[,`:=`(alo=a-5,ahi=a+5)]
dt2 = data.table(
id2 = c(1:1e4), 
b = sample(1:1e3,1e4,replace = T),key='id2')
dtR <- dt2[dt1,.(id1,id2,a,b),on=.(b < ahi, b>alo)]

Answer 2

很酷的问题。我自己也不知道没有环圈该怎么做。可能想要检查以下内容有多慢:

x <- vector("list",nrow(dtR))
last_unique_id1 <- dtR[1,id1]
last_unique_id2 <- dtR[1,id2]
counter = 1
x[[counter]] <- dtR[1,.(id1,id2)]
for(i in 2:nrow(dtR)){
if(dtR[i,id1] > last_unique_id1 &  dtR[i,id2] > last_unique_id2){
last_unique_id1 <- dtR[i,id1]
last_unique_id2 <- dtR[i,id2]
counter = counter + 1
x[[counter]] <- dtR[i,.(id1,id2)]
}
}  
x <- rbindlist(x)
x
#   id1 id2
#1:   1  13
#2:   2  14
#3:   5  15
#4:   6  16
#5:   9  17
#6:  10  18

Answer 3

感谢@Evan_Friedland的建议。下面是你的代码的一个应用:

library(data.table)
set.seed(42)
dt1 = data.table(
id1 = c(1:1e3),
a = sample(1:1e2,1e3,replace = T),key='id1')
dt1[,`:=`(alo=a-5,ahi=a+5)]
dt2 = data.table(
id2 = c(1:1e4), 
b = sample(1:1e3,1e4,replace = T),key='id2')
dtR <- dt2[dt1,.(id1,id2,a,b),on=.(b < ahi, b>alo)]
system.time({
x <- vector("list",nrow(dtR))
last_unique_id1 <- dtR[1,id1]
last_unique_id2 <- dtR[1,id2]
counter = 1
x[[counter]] <- dtR[1,.(id1,id2)]
for(i in 2:nrow(dtR)){
if(dtR[i,id1] > last_unique_id1 &  
dtR[i,id2] > last_unique_id2){
last_unique_id1 <- dtR[i,id1]
last_unique_id2 <- dtR[i,id2]
counter = counter + 1
x[[counter]] <- dtR[i,.(id1,id2)]
}

}  
x <- rbindlist(x)
})

这个代码占用了38"得到83个唯一的id1和id2。

我尝试的代码，下面，4"得到987个id1和id2，其余13个id1只与重复id2匹配。但是对于大1000到10000倍的表，这仍然很慢。

dtc <- copy(dtR)
system.time({
setkey(dtR,id1)
for(i in unique(dtR$id1)){
try({
uId <- dtR[id1==i,first(id2)]
dtR[id1==i & id2==uId,keep:=1,mult='first']
dtR <- dtR[(id1!=i & id2 != uId) | keep==1]
})
}})