The order of the `.SD` columns in the j argument is different when using` get () `

Question

I very often convert subsets of data using the option .SDcolsin data.table. It makes sense that the columns .SDsent to jare in the same order as the original data.table .

EDITED to correctly identify the problem

It’s good that the columns .SDare in the same order as the argument .SDcols. This one does not work when getused in an argument j(at least inside the call lapply). In this case, the columns of the table .SDretain their original order.

Is it possible to override this behavior?

An example without getworks fine

# library(data.table)
dt = data.table(col1 = rep(LETTERS[1:3], 4), 
            b = rnorm(12),
            a = 1:12,
            c = LETTERS[1:12])

# columns I want to do something to
d.vars = c('a', 'b')  #' names in different order than names(dt)


# Generate columns of first differences by group
dt[, paste('d', d.vars, sep='.') := 
    lapply(.SD, function(L) L  - shift(L, n = 1, type='lag') ), 
      keyby = col1, .SDcols = d.vars]

"", (d.vars) , dt. :

, .SD , d.vars.

> dt
   col1           b  a c d.a         d.b
1:    A -0.28901751  1 A  NA          NA
2:    A  0.65746901  4 D   3  0.94648651
3:    A -0.10602462  7 G   3 -0.76349362
4:    A -0.38406252 10 J   3 -0.27803790
5:    B -1.06963450  2 B  NA          NA
6:    B  0.35137273  5 E   3  1.42100723
7:    B  0.43394046  8 H   3  0.08256772
8:    B  0.82525042 11 K   3  0.39130996
9:    C  0.50421710  3 C  NA          NA
10:   C -1.09493665  6 F   3 -1.59915375
11:   C -0.04858163  9 I   3  1.04635501
12:   C  0.45867279 12 L   3  0.50725443

, lapply j a b , dt.

get -

dt2 = data.table(col1 = rep(LETTERS[1:3], 4),
            b = rnorm(12),
            a = 1:12,
            neg = -1,
            c = LETTERS[1:12])

# columns I want to do something to
d.vars = c('a', 'b')  #' names in different order than names(dt)

# name of variable to be called in j.
negate <- 'neg'

dt2[, paste('d', d.vars, sep='.') :=
    lapply(.SD, function(L)  {(L  - shift(L, n = 1, type='lag') ) * get(negate) }),
       keyby = col1, .SDcols = d.vars]

d.vars:

 > dt2
    col1          b  a neg c         d.a d.b
 1:    A -0.3539066  1  -1 A          NA  NA
 2:    A  0.2702374  4  -1 D -0.62414408  -3
 3:    A -0.7834941  7  -1 G  1.05373150  -3
 4:    A -1.2765652 10  -1 J  0.49307118  -3
 5:    B -0.2936422  2  -1 B          NA  NA
 6:    B -0.2451996  5  -1 E -0.04844252  -3
 7:    B -1.6577614  8  -1 H  1.41256181  -3
 8:    B  1.0668059 11  -1 K -2.72456737  -3
 9:    C -0.1160938  3  -1 C          NA  NA
10:    C -0.7940771  6  -1 F  0.67798333  -3
11:    C  0.2951743  9  -1 I -1.08925140  -3
12:    C -0.4508854 12  -1 L  0.74605969  -3

b lapply d.a.

neg (.. get), , : lapply .SD , d.vars.

---

p.s. data.table! !

Answer 1

match "d.vars" "dt" ( "d.vars1" ),

d.vars1 <- d.vars[match(names(dt), d.vars, nomatch = 0)]
dt[, paste0("d.",d.vars1) := lapply(.SD, function(L)
        L  - shift(L, n = 1, type='lag') ), keyby = col1, .SDcols = d.vars1]
dt
#    col1           b  a c         d.b d.a
# 1:    A -0.28901751  1 A          NA  NA
# 2:    A  0.65746901  4 D  0.94648652   3
# 3:    A -0.10602462  7 G -0.76349363   3
# 4:    A -0.38406252 10 J -0.27803790   3
# 5:    B -1.06963450  2 B          NA  NA
# 6:    B  0.35137273  5 E  1.42100723   3
# 7:    B  0.43394046  8 H  0.08256773   3
# 8:    B  0.82525042 11 K  0.39130996   3
# 9:    C  0.50421710  3 C          NA  NA
#10:    C -1.09493665  6 F -1.59915375   3
#11:    C -0.04858163  9 I  1.04635502   3
#12:    C  0.45867279 12 L  0.50725442   3

Update

d.vars1 <- d.vars[match(names(dt2), d.vars, nomatch = 0)]
dt2[, paste0('d.', d.vars1) := lapply(.SD, function(L) 
    L  - shift(L, n = 1, type='lag') * get(negate) ), 
            keyby = col1, .SDcols = d.vars1]
dt2
#    col1          b  a neg c        d.b d.a
# 1:    A -0.3539066  1  -1 A         NA  NA
# 2:    A  0.2702374  4  -1 D -0.0836692   5
# 3:    A -0.7834941  7  -1 G -0.5132567  11
# 4:    A -1.2765652 10  -1 J -2.0600593  17
# 5:    B -0.2936422  2  -1 B         NA  NA
# 6:    B -0.2451996  5  -1 E -0.5388418   7
# 7:    B -1.6577614  8  -1 H -1.9029610  13
# 8:    B  1.0668059 11  -1 K -0.5909555  19
# 9:    C -0.1160938  3  -1 C         NA  NA
#10:    C -0.7940771  6  -1 F -0.9101709   9
#11:    C  0.2951743  9  -1 I -0.4989028  15
#12:    C -0.4508854 12  -1 L -0.1557111  21