There may well be a more efficient way to do this, but here's one take.
# 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
# t1 and junk have a common key variable D, so the left join is
merge(t1, junk, by = 'D', all.x = TRUE)
# check against
On Sat, Nov 26, 2011 at 3:59 PM, ONKELINX, Thierry
<[hidden email]> 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.
> 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]
> Best regards,
> [[alternative HTML version deleted]]
> [hidden email] 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.