106

I have a data.table with which I'd like to perform the same operation on certain columns. The names of these columns are given in a character vector. In this particular example, I'd like to multiply all of these columns by -1.

Some toy data and a vector specifying relevant columns: 

library(data.table)
dt <- data.table(a = 1:3, b = 1:3, d = 1:3)
cols <- c("a", "b")

Right now I'm doing it this way, looping over the character vector:

for (col in 1:length(cols)) {
   dt[ , eval(parse(text = paste0(cols[col], ":=-1*", cols[col])))]
}

Is there a way to do this directly without the for loop?

Henrik
  • 65,555
  • 14
  • 143
  • 159
Dean MacGregor
  • 11,847
  • 9
  • 34
  • 72

7 Answers7

177

This seems to work:

dt[ , (cols) := lapply(.SD, "*", -1), .SDcols = cols]

The result is

    a  b d
1: -1 -1 1
2: -2 -2 2
3: -3 -3 3

There are a few tricks here:

  • Because there are parentheses in (cols) :=, the result is assigned to the columns specified in cols, instead of to some new variable named "cols".
  • .SDcols tells the call that we're only looking at those columns, and allows us to use .SD, the Subset of the Data associated with those columns.
  • lapply(.SD, ...) operates on .SD, which is a list of columns (like all data.frames and data.tables). lapply returns a list, so in the end j looks like cols := list(...).

EDIT: Here's another way that is probably faster, as @Arun mentioned:

for (j in cols) set(dt, j = j, value = -dt[[j]])
Henrik
  • 65,555
  • 14
  • 143
  • 159
Frank
  • 66,179
  • 8
  • 96
  • 180
  • 27
    another way is to use `set` with a `for-loop`. I suspect it'll be faster. – Arun May 30 '13 at 22:33
  • 3
    @Arun I've made an edit. Is that what you meant? I haven't used `set` before. – Frank May 30 '13 at 23:17
  • @Frank, Very much! It avoids `.SD` creation. Even though, for this particular operation, .SD shouldn't cost much as it's not created for every `by`. But still, I prefer `set` for this question. – Arun May 31 '13 at 06:50
  • 9
    +1 Great answer. Yes I prefer a `for` loop with `set` for cases like this, too. – Matt Dowle May 31 '13 at 14:28
  • 4
    Yes, using `set()` seems faster, ~4 times faster for my dataset! Amazing. – Konstantinos Feb 24 '16 at 12:16
  • Here is a variant without `.SD`, but I don't think it is as good as @Frank 's answer: `dt[, (cols) := lapply(dt[, cols, with = F], "*", -1)]` – James Hirschorn Nov 18 '16 at 15:05
  • 2
    Thanks, @JamesHirschorn . I'm not sure, but I suspect there's more overhead to subsetting columns that way rather than using .SD, which is the standard idiom anyway, appearing in the intro vignette https://github.com/Rdatatable/data.table/wiki/Getting-started Part of the reason for the idiom, I think, is to avoid typing the table name twice. – Frank Nov 18 '16 at 15:41
  • does the set for version work with the by= argument? – Jakob Apr 04 '17 at 15:09
  • @PeterPan No. It's just a utility to do simple operations very quickly. – Frank Apr 04 '17 at 15:10
  • @Frank thanks. I'm also not able to adapt the first code to a case with the by argument and followed up here: http://stackoverflow.com/questions/43211628/run-a-function-in-data-table-over-many-columns-by-indicator – Jakob Apr 04 '17 at 15:34
  • @Arun can one use the set syntax if one did not want to replace the columns but rather add columns? For instance say you wanted to add columns whose names were cols.yes.no = paste0(cols,".yn") – Farrel Aug 05 '19 at 22:32
  • @Farrel It seems to work: `d = data.table(x = 1:2); set(d, j="x2", v = 3:4); d` – Frank Aug 06 '19 at 01:03
  • @Frank yes, indeed j ="x2" might work but Arun's syntax was marching through a list of column names. j was each step. So each column had a different name. Would it have to be j=paste0(j,".yn". I think I tried that and it did not work. – Farrel Aug 07 '19 at 02:58
  • @Farrel, not entirely sure I follow.. but something like this? `d <- data.table(a=1, b=2, c=3); set(d, j=c("d", "e", "f"), value=lapply(d, \`*\`, 2))`? – Arun Sep 26 '19 at 18:09
  • 1
    Warning: The method using .SDcols can unintentionally switch the order of your columns if you use the get() function within lapply(). See an example here: https://stackoverflow.com/questions/50878299/data-table-column-order-when-using-lapply-and-get. – JS1204 Dec 13 '20 at 00:43
23

I would like to add an answer, when you would like to change the name of the columns as well. This comes in quite handy if you want to calculate the logarithm of multiple columns, which is often the case in empirical work.

cols <- c("a", "b")
out_cols = paste("log", cols, sep = ".")
dt[, c(out_cols) := lapply(.SD, function(x){log(x = x, base = exp(1))}), .SDcols = cols]
hannes101
  • 2,410
  • 1
  • 17
  • 40
  • 1
    Is there a way to change the names based on a rule? In dplyr, for example, you can do iris %>% mutate_at(vars(matches("Sepal")), list(times_two = ~.*2)) and it will append "_times_two" to the new names. – kennyB Apr 16 '19 at 05:58
  • 1
    I don't think that's possible, but not really sure about it. – hannes101 Apr 16 '19 at 07:22
  • this would add columns with the names of `out_cols`, while still leaving `cols` in place. So, you'd need to eliminate those by either explicitly 1) asking for only log.a and log.b: chain a `[,.(outcols)]` to the end and re-store to `dt` via `<-`. 2) remove the old columns with a chained `[,c(cols):=NULL]`. A non-chaining solution 3) is`dt[,c(cols):=...]` followed by `setnames(dt, cols, newcols)` – mpag Jul 15 '19 at 22:34
  • @mpag, yes that's true, but for my use case of empirical research I most of the time need both series in the data set. – hannes101 Jul 16 '19 at 07:07
13

UPDATE: Following is a neat way to do it without for loop

dt[,(cols):= - dt[,..cols]]

It is a neat way for easy code readability. But as for performance it stays behind Frank's solution according to below microbenchmark result

mbm = microbenchmark(
  base = for (col in 1:length(cols)) {
    dt[ , eval(parse(text = paste0(cols[col], ":=-1*", cols[col])))]
  },
  franks_solution1 = dt[ , (cols) := lapply(.SD, "*", -1), .SDcols = cols],
  franks_solution2 =  for (j in cols) set(dt, j = j, value = -dt[[j]]),
  hannes_solution = dt[, c(out_cols) := lapply(.SD, function(x){log(x = x, base = exp(1))}), .SDcols = cols],
  orhans_solution = for (j in cols) dt[,(j):= -1 * dt[,  ..j]],
  orhans_solution2 = dt[,(cols):= - dt[,..cols]],
  times=1000
)
mbm

Unit: microseconds
expr                  min        lq      mean    median       uq       max neval
base_solution    3874.048 4184.4070 5205.8782 4452.5090 5127.586 69641.789  1000  
franks_solution1  313.846  349.1285  448.4770  379.8970  447.384  5654.149  1000    
franks_solution2 1500.306 1667.6910 2041.6134 1774.3580 1961.229  9723.070  1000    
hannes_solution   326.154  405.5385  561.8263  495.1795  576.000 12432.400  1000
orhans_solution  3747.690 4008.8175 5029.8333 4299.4840 4933.739 35025.202  1000  
orhans_solution2  752.000  831.5900 1061.6974  897.6405 1026.872  9913.018  1000

as shown in below chart

performance_comparison_chart

My Previous Answer: The following also works

for (j in cols)
  dt[,(j):= -1 * dt[,  ..j]]
Orhan Celik
  • 1,513
  • 15
  • 12
  • This is essentially the same thing as Frank's answer from a year and a half ago. – Dean MacGregor Apr 02 '18 at 13:00
  • 1
    Thanks, Frank's answer was using set. When I work with large data.table's with millions of rows, I see := operator outperforms functions – Orhan Celik Apr 02 '18 at 13:17
  • I'm skeptical, if you look at the comments of Frank's answer you'll see the creators of data table say set is better and others find it performs better. I wouldn't give you a hard time about except this question is a year and a half old. – Dean MacGregor Apr 02 '18 at 14:45
  • 2
    The reason I added an answer to an old question is as follows: I also had a similar issue, I came across this post with google search. Afterwards I found a solution to my issue, and I see it applies to here as well. Actually my suggestion uses a new function of data.table that is available in new versions of the library, which did not exist in the time of the question. I thought it is a good idea to share, thinking others with similar problem will end up here with google search. – Orhan Celik Apr 03 '18 at 07:24
  • The main difference in my suggestion is that it uses ..j notion which must be a newer option of data.table – Orhan Celik Apr 03 '18 at 07:31
  • 2
    Are you benchmarking with `dt` consisting of 3 rows? – Uwe Apr 04 '18 at 12:38
  • Yes. I also tried 100 and 1000 rows, sequence didn't change. – Orhan Celik Apr 04 '18 at 13:38
  • any benchmark with timings in microseconds is suspect. see the vignette: https://cran.r-project.org/web/packages/data.table/vignettes/datatable-benchmarking.html – MichaelChirico Jun 05 '18 at 11:33
  • 3
    Hannes' answer is doing a different computation and so shouldn't be compared with the others, right? – Frank Jun 29 '18 at 14:42
2

None of above solutions seems to work with calculation by group. Following is the best I got:

for(col in cols)
{
    DT[, (col) := scale(.SD[[col]], center = TRUE, scale = TRUE), g]
}
Feng Jiang
  • 1,776
  • 19
  • 25
2

dplyr functions work on data.tables, so here's a dplyr solution that also "avoids the for-loop" :)

dt %>% mutate(across(all_of(cols), ~ -1 * .))

I benchmarked it using orhan's code (adding rows and columns) and you'll see dplyr::mutate with across mostly executes faster than most of the other solutions and slower than the data.table solution using lapply.

library(data.table); library(dplyr)
dt <- data.table(a = 1:100000, b = 1:100000, d = 1:100000) %>% 
  mutate(a2 = a, a3 = a, a4 = a, a5 = a, a6 = a)
cols <- c("a", "b", "a2", "a3", "a4", "a5", "a6")

dt %>% mutate(across(all_of(cols), ~ -1 * .))
#>               a       b      d      a2      a3      a4      a5      a6
#>      1:      -1      -1      1      -1      -1      -1      -1      -1
#>      2:      -2      -2      2      -2      -2      -2      -2      -2
#>      3:      -3      -3      3      -3      -3      -3      -3      -3
#>      4:      -4      -4      4      -4      -4      -4      -4      -4
#>      5:      -5      -5      5      -5      -5      -5      -5      -5
#>     ---                                                               
#>  99996:  -99996  -99996  99996  -99996  -99996  -99996  -99996  -99996
#>  99997:  -99997  -99997  99997  -99997  -99997  -99997  -99997  -99997
#>  99998:  -99998  -99998  99998  -99998  -99998  -99998  -99998  -99998
#>  99999:  -99999  -99999  99999  -99999  -99999  -99999  -99999  -99999
#> 100000: -100000 -100000 100000 -100000 -100000 -100000 -100000 -100000

library(microbenchmark)
mbm = microbenchmark(
  base_with_forloop = for (col in 1:length(cols)) {
    dt[ , eval(parse(text = paste0(cols[col], ":=-1*", cols[col])))]
  },
  franks_soln1_w_lapply = dt[ , (cols) := lapply(.SD, "*", -1), .SDcols = cols],
  franks_soln2_w_forloop =  for (j in cols) set(dt, j = j, value = -dt[[j]]),
  orhans_soln_w_forloop = for (j in cols) dt[,(j):= -1 * dt[,  ..j]],
  orhans_soln2 = dt[,(cols):= - dt[,..cols]],
  dplyr_soln = (dt %>% mutate(across(all_of(cols), ~ -1 * .))),
  times=1000
)

library(ggplot2)
ggplot(mbm) +
  geom_violin(aes(x = expr, y = time)) +
  coord_flip()

Created on 2020-10-16 by the reprex package (v0.3.0)

Arthur Yip
  • 5,810
  • 2
  • 31
  • 50
1

To add example to create new columns based on a string vector of columns. Based on Jfly answer:

dt <- data.table(a = rnorm(1:100), b = rnorm(1:100), c = rnorm(1:100), g = c(rep(1:10, 10)))

col0 <- c("a", "b", "c")
col1 <- paste0("max.", col0)  

for(i in seq_along(col0)) {
  dt[, (col1[i]) := max(get(col0[i])), g]
}

dt[,.N, c("g", col1)]
Dorian Grv
  • 421
  • 5
  • 9
0
library(data.table)
(dt <- data.table(a = 1:3, b = 1:3, d = 1:3))

Hence:

   a b d
1: 1 1 1
2: 2 2 2
3: 3 3 3

Whereas (dt*(-1)) yields:

    a  b  d
1: -1 -1 -1
2: -2 -2 -2
3: -3 -3 -3
amonk
  • 1,769
  • 2
  • 18
  • 27