具有多个条件的内部联接r数据表 [英] inner join with multiple conditions r data table
本文介绍了具有多个条件的内部联接r数据表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用具有多个相当动态条件的数据表进行内部联接。我被语法绊倒了。首先,我创建两个对象,我想使用它们进行内部联接。 x
和 x2
。
I am trying to do an inner join using data table that has multiple, fairly dynamic conditions. I am getting tripped up on the syntax. First, I create two objects, x
and x2
that I want to do an inner join with.
set.seed(1)
#generate data
x = data.table(CJ(t=1:10, d=1:3,p1s=seq(1,3,by=0.1),p1sLAST=seq(1,3,by=0.1)))
x[d==1,p1sLAST:=3]
x=x[p1s<=p1sLAST]
x2 = data.table(CJ(tprime=1:10, p1sLASTprm=seq(1,3,by=0.1)))
对象:
> x
t d p1s p1sLAST
1: 1 1 1.0 3.0
2: 1 1 1.0 3.0
3: 1 1 1.0 3.0
4: 1 1 1.0 3.0
5: 1 1 1.0 3.0
---
9026: 10 3 2.8 2.9
9027: 10 3 2.8 3.0
9028: 10 3 2.9 2.9
9029: 10 3 2.9 3.0
9030: 10 3 3.0 3.0
> x2
tprime p1sLASTprm
1: 1 1.0
2: 1 1.1
3: 1 1.2
4: 1 1.3
5: 1 1.4
---
206: 10 2.6
207: 10 2.7
208: 10 2.8
209: 10 2.9
210: 10 3.0
现在,我想在单个内部联接中完成最后三个步骤。
Now, I want to do these last three steps in a single inner join.
joined = x[,x2[],by=names(x)]
joined=joined[p1sLASTprm==p1s & d!=3 | d==3 & p1sLASTprm==3]
joined=joined[tprime==t+1]
结果在最终输出中:
> joined
t d p1s p1sLAST tprime p1sLASTprm
1: 1 1 1.0 3.0 2 1.0
2: 1 1 1.1 3.0 2 1.1
3: 1 1 1.2 3.0 2 1.2
4: 1 1 1.3 3.0 2 1.3
5: 1 1 1.4 3.0 2 1.4
---
4343: 9 3 2.8 2.9 10 3.0
4344: 9 3 2.8 3.0 10 3.0
4345: 9 3 2.9 2.9 10 3.0
4346: 9 3 2.9 3.0 10 3.0
4347: 9 3 3.0 3.0 10 3.0
推荐答案
我不认为单个内部联接可以完成这3个步骤,因为存在 |
,并且很可能需要结果的并集。
I do not think a single inner join can accomplish those 3 steps since there is a |
and most likely a union of results will be required.
一种更有效利用内存的方法可能是:
A more memory efficient approach could be:
ux <- unique(x)[, upt := t+1]
rbindlist(list(
ux[d!=3][x2,
c(mget(names(ux)), mget(names(x2))),
on=c("p1s"="p1sLASTprm", "upt"="tprime"),
nomatch=0L],
ux[d==3][x2[p1sLASTprm==3],
c(mget(names(ux)), mget(names(x2))),
on=c("upt"="tprime"),
nomatch=0L]
))
这篇关于具有多个条件的内部联接r数据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文