CODEWITHSUNDEEP
rdata.table
JerryN
JerryN

Reputation: 2506

Construct a column with lagged values

Suppose I have the following script that creates a data table

library(data.table)
dt <- data.table(a = c(1,2,3,4,5), b = c(6,7,8,9,10), c = c(22,0,0,0,0))

I want to replace the values of c in rows 2 to 5 as follows

   a  b  c
1: 1  6 22
2: 2  7 31
3: 3  8 42
4: 4  9 55
5: 5 10 70

which is something like

c = a + b + shift(c, n = 1, fill = 0, type="lag")

In other words, keep row 1 the same and apply the formula to rows 2 to 5.

I have tried 

dt[2:nrow(dt), c := a + b + shift(c, n = 1, fill = 0, type="lag")]

but that gives

   a  b  c
1: 1  6 22
2: 2  7  9
3: 3  8 11
4: 4  9 13
5: 5 10 15

Any suggestions?

Upvotes: 3

Views: 91

Answers (2)

akrun
akrun

Reputation: 887911

We could also do (a variation of @Frank's suggestion)

dt[, c:= c[1L]][-1L, c := cumsum(a+b)+c]
dt
#   a  b  c
#1: 1  6 22
#2: 2  7 31
#3: 3  8 42
#4: 4  9 55
#5: 5 10 70

Upvotes: 4

akuiper
akuiper

Reputation: 215117

A data.table way to do it, considering the fact that add a + b + c[i-1] is actually doing a cumulative sum of a + b:

dt[, c := shift(cumsum(shift(a+b, n = 1, type = "lead")) + c[1], 
                n = 1, type = "lag", fill = c[1])]
dt
   a  b  c
1: 1  6 22
2: 2  7 31
3: 3  8 42
4: 4  9 55
5: 5 10 70

Upvotes: 4

Related Questions