R - apply diff() function or equivalent self-defined function on multiple columns in a data.table

Question

currently have a data.table that looks roughly like this:

ID   Date         Var1   Var2   Var3   Var4
1    2020-03-01   AB     A33    250    12
1    2020-04-01   B      B25    NA     14
1    2020-05-01   AB     A44    270    20
1    2020-06-01   AC     C33    9     13
2    2019-09-01   X      C55    280    11
2    2019-10-01   K      C89    120    12
2    2019-11-01   A      C89    320    NA
2    2019-12-01   AB     A88    200    25

This data table stores the key ID and some corresponding variables. Some are type char and some type numeric. The table is sorted with setkey(dt, ID, Date) I want to compute the lagged difference for each numeric variable within each ID.

In my data I have the numeric columns extracted in vectors that look like this.

cols <- c("Var3", "Var4")
cols_indx <- c(5:6)

Then I want to add new columns with the lagged difference of the numeric variables Var5 and Var6 to my data.table dt.

I try:

# Doesn't work    
as.data.frame(lapply(dt[ , cols, with = FALSE], diff, lag = 1))
as.data.frame(lapply(dt[ , cols_indx, with = FALSE], diff, lag = 1))
as.data.frame(lapply(dt[ , .SD, .SDcols = cols], diff, lag = 1))
as.data.frame(lapply(dt[ , .SD, .SDcols = cols_indx], diff, lag = 1))

On my data none works and results in r[i1] - r[-length(r):-(length(r) - lag + 1L)]: non-numeric argument for binary operator. I can't seem to figure out what is causing this especially as I don't see a binary operator anywhere within this code.

However, once I excplicitly state either the colnames or the col indices, all works fine. Why is that? In my case I need to shift a long data.table with > 250 columns and then compute the lagged differences or all those columns and all that for multiple lag intervals. It is not manageable to define all selected columns by hand. What am I missing here?

# Works    
as.data.frame(lapply(dt[ , 5:6], diff, lag = 1))
as.data.frame(lapply(financials.dt[ , c("Var4", "Var5")], diff, lag = 1))

Additionally, one step is still missing. I want to compute the lagged differences within each group (defined by ID). When I try diff and a self-defined function both throw similar errors.

i <- 1
lag_names_diff <- paste(cols, "Lag", i, "d", sep = "_")

dt[ , (lag_names_diff) := lapply(.SD, function(x) x - shift(x, (i), type = "lag")),
       .SDcols = cols, by = ID] 
# Error 1:
# r[i1] - r[-length(r):-(length(r) - lag + 1L)] : non-numeric argument for binary operator

# or

dt[ , (lag_names_diff) := lapply(.SD, diff, x = cols, lag = i, differences = 1),
      .SDcols = cols, by = ID]
# Error 2:
# x - shift(x, (i), type = "lag") : non-numeric argument for binary operator

... everything breaks down with the error message. I cannot seem to figure out what is causing this. Highly appreciate any pointer.

AnilGoyal AnilGoyal · Accepted Answer · 2021-04-07T13:30:12

The error seems because diff(any_vector) returns a vector but length one shorter than any_vector. See this

diff(1:5)
[1] 1 1 1 1

So if diff is to be applied on any variable in a table, one element has to be added in the result either at end or at start. Although I am not sure of your expected outcome, still I presume this. (I am adding NA to the starting of resulting vector. You may add 0 as well, if so desired.

library(dplyr)
df %>% mutate(across(cols, ~c(NA, diff(.)), .names = "{.col}_diff"))

  ID       Date Var1 Var2 Var3 Var4 Var3_diff Var4_diff
1  1 2020-03-01   AB  A33  250   12        NA        NA
2  1 2020-04-01    B  B25   NA   14        NA         2
3  1 2020-05-01   AB  A44  270   20        NA         6
4  1 2020-06-01   AC  C33    9   13      -261        -7
5  2 2019-09-01    X  C55  280   11       271        -2
6  2 2019-10-01    K  C89  120   12      -160         1
7  2 2019-11-01    A  C89  320   NA       200        NA
8  2 2019-12-01   AB  A88  200   25      -120        NA

Or if grouped on ID is required

df %>% group_by(ID) %>%
  mutate(across(cols, ~c(NA, diff(.)), .names = "{.col}_diff"))

# A tibble: 8 x 8
# Groups:   ID [2]
     ID Date       Var1  Var2   Var3  Var4 Var3_diff Var4_diff
  <int> <chr>      <chr> <chr> <int> <int>     <int>     <int>
1     1 2020-03-01 AB    A33     250    12        NA        NA
2     1 2020-04-01 B     B25      NA    14        NA         2
3     1 2020-05-01 AB    A44     270    20        NA         6
4     1 2020-06-01 AC    C33       9    13      -261        -7
5     2 2019-09-01 X     C55     280    11        NA        NA
6     2 2019-10-01 K     C89     120    12      -160         1
7     2 2019-11-01 A     C89     320    NA       200        NA
8     2 2019-12-01 AB    A88     200    25      -120        NA

R - apply diff() function or equivalent self-defined function on multiple columns in a data.table

1 Answers