8
I am looking for the best alternative to the not yet implemented (to my knowledge) assignment by reference in a data.table by groups. Using the data.table example,
DT = data.table(x=rep(c("a","b","c"),each=3), y=c(1,3,6), v=1:9)
x y v
[1,] a 1 1
[2,] a 3 2
[3,] a 6 3
[4,] b 1 4
[5,] b 3 5
[6,] b 6 6
[7,] c 1 7
[8,] c 3 8
[9,] c 6 9
I want to add a new column z, containing f(y,v) grouped by values of x (lets take f(y,v)=mean(y)+v). Note that I do not want to print or store the result of this computation as in
DT[,mean(y)+v,by=x]
x V1
[1,] a 4.333333
[2,] a 5.333333
[3,] a 6.333333
[4,] b 7.333333
[5,] b 8.333333
[6,] b 9.333333
[7,] c 10.333333
[8,] c 11.333333
[9,] c 12.333333
but rather I want to add the result to DT:
x y v V1
[1,] a 1 1 4.333333
[2,] a 3 2 5.333333
[3,] a 6 3 6.333333
[4,] b 1 4 7.333333
[5,] b 3 5 8.333333
[6,] b 6 6 9.333333
[7,] c 1 7 10.333333
[8,] c 3 8 11.333333
[9,] c 6 9 12.333333
my data.table has 262 MB, such that
DT <- DT[,transform(.SD,mean(y)+v),by=x]
is not an option, since I cannot fit DT twice in memory (which is implied by the copy operation, I think). Fact is I've never seen that operation finish.
What alternatives do I have (until data.table comes with DT[,z:=mean(y)+v,by=x])?
I just read about DT[newDT]. What's wrong here?
newDT <- DT[,mean(y)+v,by=x]
x V1
[1,] a 4.333333
[2,] a 5.333333
[3,] a 6.333333
[4,] b 7.333333
[5,] b 8.333333
[6,] b 9.333333
[7,] c 10.333333
[8,] c 11.333333
[9,] c 12.333333
(which is doable memory wise.) then:
> DT[newDT]
setkey(DT,x)
setkey(newDT,x)
x y v V1
a 1 1 4.333333
a 3 2 4.333333
a 6 3 4.333333
a 1 1 5.333333
a 3 2 5.333333
a 6 3 5.333333
a 1 1 6.333333
a 3 2 6.333333
a 6 3 6.333333
b 1