Use `.` syntax and `with = FALSE` in one data.table call

user_862288 0
Viewed 113

Suppose I want to have a data.table that contains user supplied columns and programmer specified renaming, for example, the following code:

iris <- data.table::data.table(iris)
# details varies, users are smart and don't specify columns that will be renamed.
details <- c('Sepal.Length', 'Sepal.Width')
cbind(iris[, .(Type = Species)], iris[, details, with = FALSE])

I'm interested in the most efficient and elegant method to select and rename the columns. What is it? We haven't been able to do better than the cbind() above. data.table is of a reasonable size, say 500.000 rows by 8 columns.

Addendum

I've ran the solutions provided by Akrun with microbenchmark, they perform quite well:

> microbenchmark(
  cbind1 = cbind(iris3000[, .(Type = Species)], iris3000[, details, with = FALSE]),
  cbind2 = cbind(iris3000[, .(Type = Species)], iris3000[, ..details]),
  assignment = iris3000[, ..details][, Type := iris3000$Species][],
  setnames1 = setnames(iris3000[, .SD, .SDcols = c("Species", details)],  "Species", "Type")[],
  setnames2 = setnames(iris3000[,  c("Species", ..details)], 1, "Type")[],
  times = 500L
)
Unit: milliseconds
       expr      min       lq     mean   median       uq       max neval
     cbind1 4.900362 5.304078 6.306950 5.414371 5.558222  21.77936   500
     cbind2 4.910121 5.296428 6.720642 5.411720 5.514940 221.38992   500
 assignment 2.108325 2.284778 2.738970 2.333464 2.394961  18.55119   500
  setnames1 2.155007 2.372021 2.857554 2.423696 2.483803  20.26001   500
  setnames2 1.832926 2.048634 2.960401 2.095429 2.145081 204.90523   500

The case details <- NULL also works and is faster.

data.table r
1 Answers

We can specify the columns to select in .SDcols, subset the .SD and change the specific column name with setnames

setnames(iris[, .SD, .SDcols = c("Species", details)], "Species", "Type")[]

The OP's code using with = FALSE can be shortened to ..

cbind(iris[, .(Type = Species)], iris[, ..details])

Or instead of cbind, can do an assignment

iris[, ..details][, Type := iris$Species][]

Or another option is

setnames(iris[,  c("Species", ..details)], 1, "Type")[]
#          Type Sepal.Length Sepal.Width
#  1:    setosa          5.1         3.5
#  2:    setosa          4.9         3.0
#  3:    setosa          4.7         3.2
#  4:    setosa          4.6         3.1
#  5:    setosa          5.0         3.6
# ---                                   
#146: virginica          6.7         3.0
#147: virginica          6.3         2.5
#148: virginica          6.5         3.0
#149: virginica          6.2         3.4
#150: virginica          5.9         3.0
user_37322710
Related