A reshape puzzle in data.table

data.table, r, reshape

Solution

Try this:

cumsum0 <- function(x) { x <- cumsum(x); ifelse(x == 0, NA, x) }
DT2 <- DT[, {SUM.<-y; lapply(data.table(model.matrix(~ SUM.:x + SUM.:v + 0)), cumsum0)}]
setnames(DT2, sub("(.):(.)", "\\2.\\1", names(DT2)))

Simplifications:

1) If using `0` in place of `NA` is ok then it can be simplified by omitting the first line which defines `cumsum0` and replacing `cumsum0` in the next line with `cumsum`.

2) The result of the second line has these names:

> names(DT2)
[1] "SUM.A:x" "SUM.B:x" "SUM.A:v" "SUM.B:v"

so if that is sufficient the last line can be dropped since its only purpose is to make the names exactly the same as in the question.

The result (without the simplifications) is:

> DT2
    SUM.x.A SUM.x.B SUM.v.A SUM.v.B
 1:       1      NA      12      NA
 2:       1       1      12      62
 3:       2       1      72      62
 4:       2       2      72     123
 5:       4       2     155     123
 6:       4       4     155     220
 7:       6       4     156     220
 8:       6       6     156     242
 9:       9       6     255     242
10:       9       9     255     289
11:      12       9     318     289
12:      12      12     318     338

Problem

Yet another reshape problem in `data.table` ``` set.seed(1234) DT <- data.table(x=rep(c(1,2,3),each=4), y=c("A","B"), v=sample(1:100,12)) # x y v # 1: 1 A 12 # 2: 1 B 62 ... #11: 3 A 63 #12: 3 B 49 ``` I would like to do a cumulative sum of `x` and `v` by `y` but the result to be presented as: The number of lines always stays the same, and when `y==A` the `SUM.*.A` is incremented, same when `y==B`. (As usual `y` could have many factors, 2 in this example) ``` # SUM.x.A SUM.x.B SUM.v.A SUM.v.B # 1: 1 NA 12 NA # 2: 1 1 12 62 ... #11: 12 9 318 289 #12: 12 12 318 338 ``` EDIT: Here is my poor solution clearly overly complicated ``` #first step is to create cumsum columns colNames <- c("x","v"); newColNames <- paste0("SUM.",colNames) DT[, newColNames:=lapply(.SD,cumsum) ,by=y, .SDcols=colNames, with=F]; #now we need to reshape each SUM.* to SUM.*.{yvalue} DT[,N:=.I]; setattr(DT,"sorted","N") g <- function(DT,SD){ cols <- c('N',grep('SUM',colnames(SD), value=T)); Yval <- unique(SD[,y]); merge(DT, SD[,cols, with=F], suffixe=c('',paste0('.',Yval)), all.x=T); } DT <- Reduce(f=g,init=DT,x=split(DT,DT$y)); locf = function(x) { ind = which(!is.na(x)) if(is.na(x[1])) ind = c(1,ind) rep(x[ind], times = diff( c(ind, length(x) + 1) )) } newColNames <- grep('SUM',colnames(DT),value=T); DT <- DT[, (newColNames):=lapply(.SD, locf), .SDcols=newColNames] ```

Original source