Calculate the difference between consecutive, grouped columns in a data table.

Question

My data is structured as follows:

DT <- data.table(Id=c(1,2,3,4,5), Va1=c(3,13,NA,NA,NA), Va2=c(4,40,NA,NA,4), Va3=c(5,34,NA,7,84),
Va4=c(2,23,NA,63,9), Vb1=c(8,45,1,7,0), Vb2=c(0,35,0,7,6), Vb3=c(63,0,0,0,5), Vc1=c(2,5,0,0,4))
>DT
   Id Va1 Va2 Va3 Va4 Vb1 Vb2 Vb3 Vc1
1:  1   3   4   5   2   8   0  63   2
2:  2  13  40  34  23  45  35   0   5
3:  3  NA  NA  NA  NA   1   0   0   0
4:  4  NA  NA   7  63   7   7   0   0
5:  5  NA   4  84   9   0   6   5   4

In addition, I have a list of links that references all column groups:

reference <- list(g.1=c(2,3,4,5), g.2=c(6,7,8), g.3=c(9))

2,3,4,5 columns (variables Va1, Va2, Va3and Va4) belong to the same group of variables. Columns 6,7,8 (variables Vb1, Vb2, Vb3) belong to the second group. Column 9 (variable Vc1) belongs to the third group.

What I need to do is calculate the difference between consecutive columns within column groups.

those. I need to find the difference between Va2 and Va1, as well as between Va3 and Va2, etc., but not between Vb1 and Va4.

The result should look like this:

   Id Va1 Va2 Va3 Va4 Vb1 Vb2 Vb3 Vc1 D[Va1:Va2] D[Va2:Va3] D[Va3:Va4] D[Vb1:Vb2] D[Vb2:Vb3]
1:  1   3   4   5   2   8   0  63   2          1          1         -3         -8         63
2:  2  13  40  34  23  45  35   0   5         27         -6        -11        -10        -35
3:  3  NA  NA  NA  NA   1   0   0   0         NA         NA         NA         -1          0
4:  4  NA  NA   7  63   7   7   0   0         NA         NA         56          0         -7
5:  5  NA   4  84   9   0   6   5   4         NA         80        -75          6         -1

---

I am currently using the following loop:

  for(i in 1:(length(reference)-1)){
    tmp <- NULL
    tmp <- as.list(reference[[i]])
    tmp <- tmp[-length(tmp)]
    tmp <- mapply(c, lapply(tmp, FUN = function(x) x+1), tmp, SIMPLIFY=FALSE)
    for(j in 1:length(tmp)){
      data <- cbind(data, delta = data[, tmp[[j]][1], with = F] - data[, tmp[[j]][2], with = F])
    }
  }

300-500 + 1'000'000 .

?

Answer 1

, , , cbind := :

ref <- lapply(reference,function(x) names(DT)[x])

for (g in ref){
    if (length(g)==1) next
    gx   = tail(g,-1)
    gy   = head(g,-1)
    gn   = paste0("D[",gy,":",gx,"]")
    DT[,(gn) := mapply(function(x,y).SD[[x]]-.SD[[y]], gx, gy, SIMPLIFY=FALSE)]
}