Take the 2-minute tour ×

I am trying to figure out an elegant way to use := assignment to replace many columns at once in a data.table by applying a shared function. A typical use of this might be to apply a string function (e.g., gsub) to all character columns in a table. It is not difficult to extend the data.frame way of doing this to a data.table, but I'm looking for a method consistent with the data.table way of doing things.

For example:


m <- matrix(runif(10000), nrow = 100)
df <- df1 <- df2 <- df3 <- as.data.frame(m)
dt <- as.data.table(df)

## replace V20-V100 with sqrt

# data.frame approach
# by column numbers
df1[20:100] <- lapply(df1[20:100], sqrt)
# by reference to column numbers
v <- 20:100
df2[v] <- lapply(df2[v], sqrt)
# by reference to column names
n <- paste0("V", 20:100)
df3[n] <- lapply(df3[n], sqrt)

# data.table approach
# by reference to column names
dt[, n] <- lapply(dt[, n, with = FALSE], sqrt)

I understand it is more efficient to loop over a vector of column names using := to assign:

for (col in paste0("V", 20:100)) dt[, col := sqrt(dt[[col]]), with = FALSE]

I don't like this because I don't like reference the data.table in a j expression. I also know that I can use := to assign with lapply given that I know the column names:

dt[, c("V20", "V30", "V40", "V50", "V60") := lapply(list(V20, V30, V40, V50, V60), sqrt)]

(You could extend this by building an expression with unknown column names.)

Below are the ideas I tried on this, but I wasn't able to get them to work. Am I making a mistake, or is there another approach I'm missing?

# possible data.table approaches?
# by reference to column names; assignment works, but not lapply
dt[, n := lapply(n, sqrt), with = FALSE]
# by (smaller for example) list; lapply works, but not assignment
dt[, list(list(V20, V30, V40, V50, V60)) := lapply(list(V20, V30, V40, V50, V60), sqrt)]
# by reference to list; neither assignment nor lapply work
l <- parse(text = paste("list(", paste(paste0("V", 20:100), collapse = ", "), ")"))
dt[, eval(l) := lapply(eval(l), sqrt)]
share|improve this question

2 Answers 2

Is this what you are looking for?

dt[ , names(dt)[20:100] :=lapply(.SD, function(x) sqrt(x) ) , .SDcols=20:100]

I have heard tell that using .SD is not so efficient because it makes a copy of the table beforehand, but if your table isn't huge (obviously that's relative depending on your system specs) I doubt it will make much of a difference.

share|improve this answer
I've been told that set can also speed up operations like this. –  Frank Jun 5 '13 at 15:54
@Frank +1 for that answer, and I have book-marked for future reference. I wouldn't think to use a for loop here. Clever. –  Simon O'Hanlon Jun 5 '13 at 15:59
@SimonO101, I forgot about .SDcols. It works, but in a perfect world I don't want to use it. –  attitude_stool Jun 5 '13 at 16:17
@Frank, I didn't know about the for loop + set approach. I will have to think about using that in the future. –  attitude_stool Jun 5 '13 at 16:18
@attitude_stool read this comment by the author of data.table. Apparently this is an ideal use of .SD and what it was designed for. –  Simon O'Hanlon Jun 12 '13 at 19:26

These should work if you want to refer to the columns by string name:

n = paste0("V", 20:100)
dt[, (n) := lapply(n, function(x) {sqrt(get(x))})]


dt[, (n) := lapply(n, function(x) {sqrt(dt[[x]])}]
share|improve this answer
@SimonO101 - sure, but read the first line in the answer - "if ..." :) –  eddi Jun 5 '13 at 16:53
the difference is that you specify 20:100, so you're not searching for the columns by name anymore –  eddi Jun 5 '13 at 16:55
oh, I think I misunderstood you initially, you mean instead of paste0 not afterwards? yeah, you're right - what you do there doesn't matter; I had your answer in mind and was thinking about the assignment, so I thought you meant replace (n) there; tbh, I can't say I particularly care about how you get the n vector - that'll be very task specific –  eddi Jun 5 '13 at 17:21

Your Answer


