R Data.Table: In-memory left join multiple columns from left and right side

Question

I want to merge two data tables on multiple columns using data table.

## The two tables
A <- data.table(a = 1:4, b = 12:15, c = 10:13)
   a  b  c
1: 1 12 10
2: 2 13 11
3: 3 14 12
4: 4 15 13

B <- data.table(a = 1:3, b = 12:14, d = 10:12, e = 10:12)
   a  b  d  e
1: 1 12 10 10
2: 2 13 11 11
3: 3 14 12 12

I would like to get this result:

A <- merge(A, B, by = c('a', 'b'), all.x = T)
   a  b  c  d  e
1: 1 12 10 10 10
2: 2 13 11 11 11
3: 3 14 12 12 12
4: 4 15 13 NA NA

This answer showed that data table can edit the A data frame in-memory, so it's more efficient when using large data sets. The code suggested is

A[B, bb:=i.b, on='a']

How can I alter this code so the A data frame is merged in-memory, and the result will be the same as above?

See stackoverflow.com/questions/41439976/…

David Arenburg
– David Arenburg

2017-08-14 15:11:56 +00:00
Commented Aug 14, 2017 at 15:11 — David Arenburg
– David Arenburg, Commented Aug 14, 2017 at 15:11

akrun · Accepted Answer · 2017-08-14 15:11:30Z

2

If we meant to do a join. Here the i.d or i.e is not needed as the columns are unique in the 'B'

nm1 <- setdiff(names(B), names(A)) 
A[B, (nm1) :=  mget(nm1), on = .(a, b)]
A
#   a  b  c  d  e
#1: 1 12 10 10 10
#2: 2 13 11 11 11
#3: 3 14 12 12 12
#4: 4 15 13 NA NA

edited Aug 14, 2017 at 15:11

answered Aug 14, 2017 at 15:04

akrun

891k38 gold badges590 silver badges700 bronze badges

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

1 Comment

Jelger van Zaane Over a year ago

Thanks! Because I use two data sets with many more columns, is it possible to make this more flexible ? for example using setdiff(names(B), names(A)) in the j section of data table?

Collectives™ on Stack Overflow

R Data.Table: In-memory left join multiple columns from left and right side

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related