Select / assign data.table variables whose names are stored in a character vector

Question

How do you feel about variables in data.table if variable names are stored in a character vector? For example, this works for data.frame :

 df <- data.frame(col1 = 1:3) colname <- "col1" df[colname] <- 4:6 df # col1 # 1 4 # 2 5 # 3 6 

How can I perform the same operation for a data table with musical notation := or without it? The obvious thing is dt[ , list(colname)] does not work (and I did not expect this).

Answer 1

Try:

 DT = data.table(col1 = 1:3) colname = "col1" DT[, colname, with = FALSE] # select # col1 # 1: 1 # 2: 2 # 3: 3 DT[, (colname) := 4:6] # assign # col1 # 1: 4 # 2: 5 # 3: 6 

The latter is known as the plonk column, because you replace the entire column vector with a link. If there is a subset i , it will reassign by reference. Paranas around (colname) is a shorthand introduced in version v1.9.4 on CRAN Oct 2014. Here's the news:

 Using `with = FALSE` with `:=` is now deprecated in all cases, given that wrapping the LHS of `:=` with parentheses has been preferred for some time. colVar = "col1" DT[, colVar := 1, with = FALSE] # deprecated, still works silently DT[, (colVar) := 1] # please change to this DT[, c("col1", "col2") := 1] # no change DT[, 2:4 := 1] # no change DT[, c("col1","col2") := list(sum(a), mean(b)] # no change DT[, `:=`(...), by = ...] # no change 

See also the Details section in ?`:=` :

 DT[i, (colnamevector) := value] # [...] The parens are enough to stop the LHS being a symbol 

And to answer the next question in a comment, here is one way (as usual, there are many ways):

 DT[, colname := cumsum(get(colname)), with = FALSE] # col1 # 1: 4 # 2: 9 # 3: 15 

or it may be easier for you to read, write, and debug only up to eval a paste , like building a dynamic SQL statement to send to the server:

 expr = paste0("DT[,",colname,":=cumsum(",colname,")]") expr # [1] "DT[,col1:=cumsum(col1)]" eval(parse(text=expr)) # col1 # 1: 4 # 2: 13 # 3: 28 

If you do so much, you can define an auxiliary function eval :

 EVAL = function(...)eval(parse(text=paste0(...)),envir=parent.frame(2)) EVAL("DT[,",colname,":=cumsum(",colname,")]") # col1 # 1: 4 # 2: 17 # 3: 45 

Now that data.table 1.8.2 automatically optimizes j for efficiency, it may be preferable to use the eval method. get() in j prevents some optimizations, for example.

Or there is set() . Low service, functional form := , which would be nice. See ?set .

 set(DT, j = colname, value = cumsum(DT[[colname]])) DT # col1 # 1: 4 # 2: 21 # 3: 66 

Answer 2

* This is not an actual answer, but I do not have enough street loans for comment: /

In any case, for those who can try to create a new column in the data table with the name stored in the variable, I have the following: I do not mean that this is performance. Any suggestions for improvement? Can we assume that the nameless new column will always have the name V1?

 colname <- as.name("users") # Google Analytics query is run with chosen metric and resulting data is assigned to DT DT2 <- DT[, sum(eval(colname, .SD)), by = country] setnames(DT2, "V1", as.character(colname)) 

Notice that I can refer to it just fine in sum (), but it can't seem to assign it at the same step. BTW, the reason I need to do this is colname will be based on user input in the Shiny app.

Answer 3

For multiple columns and a function applied to column values.

When updating values ​​from a function, RHS should be an object of the list, so using a loop on .SD using lapply will do the trick.

The following example converts integer columns to numeric columns

 a1 <- data.table(a=1:5, b=6:10, c1=letters[1:5]) sapply(a1, class) # show classes of columns # ab c1 # "integer" "integer" "character" # column name character vector nm <- c("a", "b") # Convert columns a and b to numeric type a1[, j = (nm) := lapply(.SD, as.numeric ), .SDcols = nm ] sapply(a1, class) # ab c1 # "numeric" "numeric" "character"