CODEWITHSUNDEEP
rdata.table
Shawn Brar
Shawn Brar

Reputation: 1420

Merge 2 data.tables with more than one column the data.table way

I have two data.tables as following:-

a <- data.table(id = 1:10, val = 2010:2019)
b <- data.table(id = c(1, 2, 4, 6), year = 1:4)

Now if I merged b and a as following:-

b[a, val := i.val, on = "id"]

This will make an extra column in b called val. This will also not reassign the memory for b data.table.

I wanted to know if a more than 2 columns and was as following:-

    a <- data.table(id = 1:10, val = 2010:2019,
                    twr = c(10, 13, 22 ,43, 23, 23, -4, 33, -54, 34))

how to merge the two data.tables (b and a), the data.table way, i.e. not using merge or any of the join function.

But using [, , on = "id"] syntax.

I want to know this because using any of the join functions or merge makes a whole new object, whereas the data.table way only creates the new columns and not a whole new object.

Thanks in advance.

Upvotes: 1

Views: 99

Answers (2)

Uwe
Uwe

Reputation: 42544

With development version 1.14.1, has gained the env parameter which is meant for programming on data.table:

cols <- setdiff(names(a), "id")
b[a, on = "id", (cols) := acols, env = list(acols = as.list(cols))][]

   id year  val twr
1:  1    1 2010  10
2:  2    2 2011  13
3:  4    3 2013  43
4:  6    4 2015  23

This will work in many cases where there no duplicate column names in a and b except those to join on. However, we can explicitely refer to columns of a by using the prefix i.:

b[a, on = "id", (cols) := acols, env = list(acols = as.list(paste0("i.", cols)))][]

Upvotes: 0

akrun
akrun

Reputation: 886938

If there are only two columns to be returned, just wrap then in a list (or short form .() after joining on by 'id', and assign :=) those columns to 'b'

b[a, names(a)[-1] := .(i.val, i.twr), on = .(id)]

If there are many columns to be returned

nm1 <- names(a)[-1]
b[a, (nm1) := mget(paste0("i.", nm1)), on = .(id)]

-ouput

b
   id year  val twr
1:  1    1 2010  10
2:  2    2 2011  13
3:  4    3 2013  43
4:  6    4 2015  23

Upvotes: 2

Related Questions