Lagging in a data.table in R which is time series panel data

Question

I was trying to use lag and data table notation to lag the variables, so that it is fastest. This is how I was trying to do it,

head(DT)
setkey(DT,code,year)

The output is

     code year pt_N_1y ws_country close is_msci
1: 130104 2003       0     ISRAEL     0       0     
2: 130104 2004       0     ISRAEL     0       0   
3: 130104 2005       0     ISRAEL     0       0    
4: 130104 2006       0     ISRAEL     0       0    
5: 130104 2007       0     ISRAEL     0       0    
6: 130104 2008       0     ISRAEL     0       0

DT[,L1_is_msci:=.SD[lag(is_msci,1)],by=code]

This gives 50 warnings and gives all NA's. Isn't .SD supposed to subset the data by "code" and apply the function lag(is_msci, 1). I would ideally like a 1 line function to do the lags and would like to work with base functions and data table notation as it is the most optimal while dealing with huge datasets without installing many packages. Is it possible?

What I want to achieve is

     code year pt_N_1y ws_country close is_msci L1_is_msci
1: 130104 2003       0     ISRAEL     0       0    NA 
2: 130104 2004       0     ISRAEL     0       0    0
3: 130104 2005       0     ISRAEL     0       0    0
4: 130104 2006       0     ISRAEL     0       0    0
5: 130104 2007       0     ISRAEL     0       0    0
6: 130104 2008       0     ISRAEL     0       0    0

No, this does not lag the series, I want NA's to appear at the first lag when there is no previous observation. This done by groups. — Rads, Jul 01 '14 at 21:35
No, lag gives a 0 to the missing variable, I want an NA. How can I do so? — Rads, Jul 02 '14 at 23:27
@Arun, I have updated the question, when I did what eddi asked me to do, it just produced the same column as is_msci — Rads, Jul 03 '14 at 16:36

score 1 · Accepted Answer · edited May 23 '17 at 12:07

In the future, please provide a reproducible example - one from which we can just copy/paste the code to get the data.

I'm not sure which function you're using for lag - you don't show it. The base function lag (from stats) seems to do something different. If you're using that, no wonder your results aren't what you expect. It's quite easy to test the function you're using:

# base function from package stats
lag(1:5, k=1L)
# [1] 1 2 3 4 5
# attr(,"tsp")
# [1] 0 4 1

Obviously not what we want. So, let's write our own function:

lag <- function(x, n)
    if (n == 0) x else c(rep.int(NA, n), head(x, -n))

# test it
lag(1:5, 0)
# [1] 1 2 3 4 5

lag(1:5, 2)
# [1] NA NA  1  2  3

Now, you an apply @eddi's code:

DT[, L1_is_msci := lag(is_msci, 1L), by = code]
#      code year pt_N_1y ws_country close is_msci L1_is_msci
# 1: 130104 2003       0     ISRAEL     0       0         NA
# 2: 130104 2004       0     ISRAEL     0       0          0
# 3: 130104 2005       0     ISRAEL     0       0          0
# 4: 130104 2006       0     ISRAEL     0       0          0
# 5: 130104 2007       0     ISRAEL     0       0          0
# 6: 130104 2008       0     ISRAEL     0       0          0

HTH

Lagging in a data.table in R which is time series panel data

1 Answers1