quick SQL Join-like adding columns to a data.table in R -
what i'm trying achieve similar sql
select * t left join t2 on t1.key2 = t2.key2 left join t3 on t1.key3 = t3.key3
but in r using data.table
. specifically, 'append' two columns t2 , one column t3 first table.
current solution looks like
setkey(t1,key2) t1 <- t2[t1] setkey(t1,key3) t1 <- t3[t1] setkey(t1,key1) # restore original key
this copies entire t1 twice , not quick. on 'inside' t1 quite large dataset ~ 1mln rows , t2 , t3 small 'mapping' tables.
think using j(..)
help, i'm new data.tables , can't understand how apply here.
please suggest faster solution join? thanks
since devel version of data.table, v1.9.5 implemented feature of being able join data.tables directly without having set keys using new on
argument, can provide quality answer question:
t1[t2, colt2 := i.colt2, on = c(key2="key2") ][t3, colt3 := i.colt3, on = c(key3="key3") ][]
it should preserve original key on t1
.
Comments
Post a Comment