Update columns using other columns of same data.table, using only their string name

Question

I have a lot of columns that I want to update using values of other columns in the same data.table. I need to use their string names. Indeed, I have very large vector of variable names of the form:

cols.x <- c('name1.x', 'name2.x', 'name3.x', ...)

that I want to update using other columns:

cols.y <- c('name1.y', 'name2.y', 'name3.y', ...)

However, I could not find a way to make data.table understand I have also columns in the right hand side of the :=, and not the string.

Here is the working example:

library(data.table)

# Setting the data.table
DT <- data.table(V1.x=c(NA,c(1:5)), V2.x=c(NA,LETTERS[1:5]), V1.y=6:1, V2.y=LETTERS[1:6])

# Preparing the columns
cols.x <- c('V1.x','V2.x')
cols.y <- c('V1.y','V2.y')

# Assign cols.y to cols.x when cols.x is NA
DT[is.na(V1.x) | is.na(V2.x), cols.x := cols.y, with = F]

print(DT)

# Output
    V1.x V2.x V1.y V2.y
1:   NA  V1.y   6    A
2:    1    A    5    B
3:    2    B    4    C
4:    3    C    3    D
5:    4    D    2    E
6:    5    E    1    F

# Desired Output
    V1.x V2.x V1.y V2.y
1:    6    A    6    A
2:    1    A    5    B
3:    2    B    4    C
4:    3    C    3    D
5:    4    D    2    E
6:    5    E    1    F

I tried many other solution such as:

DT[is.na(V1.x) | is.na(V2.x), cols.x := get(cols.y)]
# or
DT[is.na(V1.x) | is.na(V2.x), cols.x := as.list(cols.y)]

but nothing gives me the desired output.

Adrien · Accepted Answer · 2017-07-20 15:44:49Z

1

I just found this solution that works

DT[is.na(V1.x) | is.na(V2.x), (cols.x) := DT[is.na(V1.x) | is.na(V2.x),cols.y, with = F]]

edited Jul 20, 2017 at 15:44

answered Jul 20, 2017 at 15:37

Adrien

4716 silver badges19 bronze badges

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

3 Comments

lmo Over a year ago

As of data.table 1.10.4, you can use the prepend .. to reference vectors of variable names that were created outside of data.table, so the following tweek to your answer DT[is.na(V1.x) | is.na(V2.x), (cols.x) := DT[is.na(V1.x) | is.na(V2.x), ..cols.y]].

Adrien Over a year ago

Thanks ! So this is the new version of "with = FALSE" ?

lmo Over a year ago

it does replace some of the functionality of with=FALSE in some instances, but it functions similar to the reserved words like .N or .grp. It basically says look up one level for objects with given name and use these as the variables. As you saw with the previous warning DT[i, (colvector) := val] is another instance of the replacement of with=FALSE. See here for the posting on using .. prefixes: github.com/Rdatatable/data.table/blob/master/NEWS.md for version 1.10.2

Collectives™ on Stack Overflow

Update columns using other columns of same data.table, using only their string name

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related