[R] data.table merge equivalent for all.x

[Dennis Murphy]

Hi:

There may well be a more efficient way to do this, but here's one take.

library('data.table')
# Want to merge by D in the end, so set D as part of the key:
t1 <- data.table(ID = 11:20, A = rnorm(10), D = 1:10, key = "ID, D")
t2 <- data.table(ID2 = 1:10, D = rep(1:5, 2), B = rnorm(10), key = "ID2, D")

# The J expression produces sums of B (the non-key variable) for each D group
# .SD denotes 'sub-data'.  The result 'junk' is a data table.
junk <- t2[, lapply(.SD, sum), by = D]

tables()   # junk has no key
# set a key for junk so that it can be merged
setkey(junk, 'D')
# t1 and junk have a common key variable D, so the left join is
merge(t1, junk, by = 'D', all.x = TRUE)

# check against
t1
junk

HTH,
Dennis


On Sat, Nov 26, 2011 at 3:59 PM, ONKELINX, Thierry
<Thierry.ONKELINX at inbo.be> wrote:
> Dear all,
>
> I'm trying to use data.table to summarise a table and merge it to another table. Here is what I would like to do, but by using data.table() in a proper way.
>
> library(data.table)
> tab1 <- data.table(ID = 11:20, A = rnorm(10), D = 1:10, key = "ID")
> tab2 <- data.table(ID2 = 1:10, D = rep(1:5, 2), B = rnorm(10), key = "ID2")
> junk <- aggregate(tab2[, B], by = list(D = tab2[, D]), FUN = sum)
> merge(tab1, junk, by = "D", all.x = TRUE)
>
> This my attempt using data.table()
>
> junk <- tab2[, mean(B), by = D]
> tab1[junk]
>
> Best regards,
>
> Thierry
>
>
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>