I have two datasets, one a detailed dataset of
weight's and another that's supposed to be a summary dataset. I am trying to create the summary dataset by joining the detail dataset and aggregating, but it isn't working as expected.
Here's a sample code.
mytesta <- data.table(cola = c("a","b"), groupa = c(1,2)) # summary mytestb <- data.table(groupa = c(1,1,1,1,2,2,2), weighta = c(10,20,30,25,15,30,10)) #detail
And this is my desired output.
cola groupa weighta 1: a 1 85 2: b 2 55
What I tried to do is,
mytesta[mytestb, on = "groupa", weight_summary := sum(i.weighta), by = "groupa"]
The problem is that when
by is used, the columns of the inner data.table disappear (for instance,
mytesta[mytestb, on = "groupa", .SD, by = "groupa"]). Is there a way around this?