How to efficiently aggregate multiple data.table columns by groups, N-at-a-time, where N is variable

Question

The dataset I am using is structured as follows:

library("data.table")

   dt <- data.table(Id = c(1,2,3,4,5,6,7,8), "01.01.2005" = c(10,0,0,0,5,7,7,0),
 "02.01.2005" = c(4,5,7,0,0,0,9,9), "03.01.2005" = c(2,3,3,6,7,77,45,0), 
"04.01.2005" = c(0,0,0,0,0,0,0,1), "05.01.2005" = c(0,1,1,1,1,0,0,2), 
"06.01.2005" = c(45,15,45,54,5,76,67,10), "07.01.2005" = c(0,0,0,0,466,21,832,54), 
"08.01.2005" = c(2,3,734,2,3,4,5,6))

>dt
       Id 01.01.2005 02.01.2005 03.01.2005 04.01.2005 05.01.2005 06.01.2005 07.01.2005 08.01.2005
    1:  1         10          4          2          0          0         45          0          2
    2:  2          0          5          3          0          1         15          0          3
    3:  3          0          7          3          0          1         45          0        734
    4:  4          0          0          6          0          1         54          0          2
    5:  5          5          0          7          0          1          5        466          3
    6:  6          7          0         77          0          0         76         21          4
    7:  7          7          9         45          0          0         67        832          5
    8:  8          0          9          0          1          2         10         54          6

I need to sum the an amount of columns by a a certain step. I.e. if the step = 2 then column (2,3) are aggregated, (4,5), (6,7), and (8,9) as well. The output needs to look as follows:

>output
       Id 01.01.2005-02.01.2005 03.01.2005-04.01.2005 05.01.2005-06.01.2005 07.01.2005-08.01.2005
    1:  1                    14                     2                    45                     2
    2:  2                     5                     3                    16                     3
    3:  3                     7                     3                    46                   734
    4:  4                     0                     6                    55                     2
    5:  5                     5                     7                     6                   469
    6:  6                     7                    77                    76                    25
    7:  7                    16                    45                    67                   837
    8:  8                     9                     1                    12                    60

To achieve this I am using a loop:

output <- dt[, list(Id)]
step = 2
for(i in seq(nrow(dt), 2, by = -step)){
     output <- cbind(output, temp.col = rowSums(dt[, i:(i-step+1), with = F], 
                      na.rm = FALSE, dims = 1))
     setnames(output, "temp.col", "new.name...")
}

but for large datasets this approach is very slow. Is there a function that exists that does what I need without a loop?

Furthermore: "step" needs to be a variable input.

thanks in advance

Answer 1

Here's another possible approach

step <- 2
temp <- melt(dt, "Id")[, indx := rep(seq_len((ncol(dt)-1L)/step), each = nrow(dt)*step)]
dcast(temp, Id ~ indx, sum, value.var = "value")
#    Id  1  2  3   4
# 1:  1 14  2 45   2
# 2:  2  5  3 16   3
# 3:  3  7  3 46 734
# 4:  4  0  6 55   2
# 5:  5  5  7  6 469
# 6:  6  7 77 76  25
# 7:  7 16 45 67 837
# 8:  8  9  1 12  60