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)

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. — smci
– smci, Commented Apr 27, 2018 at 6:31

mnel · Accepted Answer · 2013-05-28 04:22:31Z

48

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]

edited May 28, 2013 at 4:22

answered May 28, 2013 at 4:17

mnel

116k28 gold badges269 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bright-star Over a year ago

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

mnel Over a year ago

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

Christa Over a year ago

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

RolandASc Over a year ago

@Christa 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

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related