1

I am doing rolling computations on a column,using the below code

dt <- data.table(x1=1:8,x2=2:10,x3=4:11,x4=6:12)
N = 3L
dt[, y1 := (2*dt$x1[.I] -dt$x1[(.I+N-1L)]), by=1:nrow(dt)]
dt


     x1 x2 x3 x4 y1
1:  1  2  4  6 -1
2:  2  3  5  7  0
3:  3  4  6  8  1
4:  4  5  7  9  2
5:  5  6  8 10  3
6:  6  7  9 11  4
7:  7  8 10 12 13
8:  8  9 11  6 NA
9:  1 10  4  7 NA

sdcols=paste0("x",1:4)

how does one use sdcols to achieve the same result for columns x1 through x4, creating new columns y1 to y4

ashleych
  • 1,042
  • 8
  • 25

1 Answers1

2

Perhaps we don't need a group by operation

nm1 <- names(dt)
dt[,  paste0('y', seq_along(nm1)) := lapply(.SD, 
    function(x) c((2*shift(x)- shift(x, type = 'lead'))[-1], NA)), .SDcols = nm1]
dt
#   x1 x2 x3 x4 y1 y2 y3 y4
#1:  1  2  4  6 -1  0  2  4
#2:  2  3  5  7  0  1  3  5
#3:  3  4  6  8  1  2  4  6
#4:  4  5  7  9  2  3  5  7
#5:  5  6  8 10  3  4  6  8
#6:  6  7  9 11  4  5  7 16
#7:  7  8 10 12 13  6 16 17
#8:  8  9 11  6 NA NA NA NA
#9:  1 10  4  7 NA NA NA NA
akrun
  • 874,273
  • 37
  • 540
  • 662
  • the solution works and is elegant. I am having trouble decoding this piece c((2*shift(x)- shift(x, type = 'lead'))[-1], NA)). Why use c, why [-1] and why NA? Could you help clarify? – ashleych Oct 03 '17 at 07:07
  • @ashleych I used that to remove the first observation and then append with NA at the end to get the expenceted output – akrun Oct 03 '17 at 09:00
  • I don't really get how this would translate for other values of the OP's variable `N = 3`. – Frank Oct 03 '17 at 11:12
  • @Frank I didn't test it with other values except the ones in the example provided – akrun Oct 03 '17 at 11:25