Apply a function to a subset of data.table columns, by column-indices instead of name

Question

I'm trying to apply a function to a group of columns in a large data.table without referring to each one individually.

a <- data.table(
  a=as.character(rnorm(5)),
  b=as.character(rnorm(5)),
  c=as.character(rnorm(5)),
  d=as.character(rnorm(5))
)
b <- c('a','b','c','d')

with the MWE above, this:

a[,b=as.numeric(b),with=F]

works, but this:

a[,b[2:3]:=data.table(as.numeric(b[2:3])),with=F]

doesn't work. What is the correct way to apply the as.numeric function to just columns 2 and 3 of a without referring to them individually.

(In the actual data set there are tens of columns so it would be impractical)

Rosannarosanne

Also, if you just want to reference multiple columns by indices, ,with=F] allows j to be column-indices e.g. dt[, 2:3, with =F. But applying a function to each is more complicated, as per @mnel's answer. – Clathrate

Answer 1

The idiomatic approach is to use .SD and .SDcols

You can force the RHS to be evaluated in the parent frame by wrapping in ()

a[, (b) := lapply(.SD, as.numeric), .SDcols = b]

For columns 2:3

a[, 2:3 := lapply(.SD, as.numeric), .SDcols = 2:3]

or

mysubset <- 2:3
a[, (mysubset) := lapply(.SD, as.numeric), .SDcols = mysubset]

Ungava

If you want to use the "by" grouping here, does that have to be included in advance, in mysubset? – Libration

@TrevorAlexander - No, the By columns are not in .SD, they exist as single values in the environment in which .SD is created. – Ungava

Hi how do i use this if I want to apply the function on all columns but 'b'? Thanks! – Decease

@Decease You could still use a[, (b[b != 'b']) := lapply(.SD, as.numeric), .SDcols = b[b != 'b']] then. But mySubset <- setdiff(b, 'b') followed by a[, (mySubset) := lapply(.SD, as.numeric), .SDcols = mySubset] is more readable and seems straight-forward – Bipack