r data.table Join In Place Multiple Columns

Question

data.table is amazing.

I would like to do an in-place join but to keep all columns from both tables. This question demonstrates how to do it for a single column. How do I generalize this when I want all of the columns from my joined table to be in the final result and have it all done in one memory location.

library(data.table)
dt1 <- data.table(col1 = c("a", "b", "c"), 
                  col2 = 1:3, 
                  col3 = c(TRUE, FALSE, FALSE))

setkey(dt1, col1)

set.seed(1)
dt2 <- data.table(col1 = sample(c("a", "b", "c"), size = 10, replace = TRUE), 
                  another_col = sample(1:10, size = 10, replace = TRUE), 
                  and_anouther = sample(c(TRUE, FALSE), size = 10, replace = TRUE))

setkey(dt2, col1)

# I want to stick the columns from dt1 onto dt2

# this works
dt3 <- dt2[dt1]
dt3
    col1 another_col and_anouther col2  col3
 1:    a           9        FALSE    1  TRUE
 2:    b           2        FALSE    2 FALSE
 3:    b           9        FALSE    2 FALSE
 4:    b           6        FALSE    2 FALSE
 5:    b           5         TRUE    2 FALSE
 6:    b           8        FALSE    2 FALSE
 7:    c           9         TRUE    3 FALSE
 8:    c           5        FALSE    3 FALSE
 9:    c           7        FALSE    3 FALSE
10:    c           6        FALSE    3 FALSE

# but i want to do this by reference

# this works for one column
dt2[dt1, col2 := i.col2]
dt2

    col1 another_col and_anouther col2
 1:    a           3        FALSE    1
 2:    a           8         TRUE    1
 3:    a           8         TRUE    1
 4:    b           2         TRUE    2
 5:    b           7        FALSE    2
 6:    b          10         TRUE    2
 7:    b           4        FALSE    2
 8:    c           4         TRUE    3
 9:    c           5         TRUE    3
10:    c           8         TRUE    3

# ok, remove that column
dt2[, col2 := NULL]

# now try to join multiple columns 
# this doesn't work
dt2[dt1, (col2 := i.col2, 
          col3 := i.col3)]

# neither does this
dt2[dt1, .(col2 := i.col2, 
          col3 := i.col3)]

# this just give me to the two columns
dt2[dt1, .(col2 = i.col2, 
           col3 = i.col3)]
dt2
   col2  col3
 1:    1  TRUE
 2:    1  TRUE
 3:    1  TRUE
 4:    2 FALSE
 5:    2 FALSE
 6:    2 FALSE
 7:    2 FALSE
 8:    3 FALSE
 9:    3 FALSE
10:    3 FALSE  

                ^

^{Created on 2018-10-30 by the reprex package (v0.2.1)}

Pretty much, I want the result from dt3, but I would like for it to created in place by reference as dt2. Thanks!

score 9 · Accepted Answer · answered Oct 30 '18 at 19:01

9

I should have looked at one more questions which linked to this awesome reference.. All I needed to do was use the funcional form of the := operator.

dt2[dt1, `:=` (col2 = i.col2, 
          col3 = i.col3)]

dt2
    col1 another_col and_anouther col2  col3
 1:    a           3        FALSE    1  TRUE
 2:    a           8         TRUE    1  TRUE
 3:    a           8         TRUE    1  TRUE
 4:    b           2         TRUE    2 FALSE
 5:    b           7        FALSE    2 FALSE
 6:    b          10         TRUE    2 FALSE
 7:    b           4        FALSE    2 FALSE
 8:    c           4         TRUE    3 FALSE
 9:    c           5         TRUE    3 FALSE
10:    c           8         TRUE    3 FALSE

answered Oct 30 '18 at 19:01

Nick Criswell

1,733
2
16
32

2

The vignette is indeed good, but so is the help text, `?":="`, where `LHS := RHS ` form and Functional form are described. – Henrik Oct 30 '18 at 20:15
1

Is there a way that works without having to type all the column names? – groceryheist Sep 30 '19 at 20:45
A method of merging all columns without typing them out is explained in [this question](https://stackoverflow.com/questions/45043600/merging-all-column-by-reference-in-a-data-table) – Mxblsdl Dec 20 '19 at 23:04

score 4 · Answer 2 · answered Oct 30 '18 at 19:34

The functional syntax is cleaner than the standard way.

dt2[dt1, c("col2", "col3") := .(col2, col3), on = c(col1 = "col1")][order(col1)]

    col1 another_col and_anouther col2  col3
 1:    a           3        FALSE    1  TRUE
 2:    a           8         TRUE    1  TRUE
 3:    a           8         TRUE    1  TRUE
 4:    b           2         TRUE    2 FALSE
 5:    b           7        FALSE    2 FALSE
 6:    b          10         TRUE    2 FALSE
 7:    b           4        FALSE    2 FALSE
 8:    c           4         TRUE    3 FALSE
 9:    c           5         TRUE    3 FALSE
10:    c           8         TRUE    3 FALSE

r data.table Join In Place Multiple Columns

2 Answers2

Linked