adding a new column derived from a different table without using the "merge" function

Question

Suppose these two tables - A and B

table A is

id v1  
1  10
3  20
5  20
6  15

table B is

id v2  
1  200
2  300
3  200
4  250
5  190
6  290

I want to add "v2" of B to A with the same "id" as follows:

table A should be

id v1 v2
1  10 200
3  20 200
5  20 190
6  15 290

I appreciate your help!

Answer 1

Using data.table which is based on X[Y] syntax:

require(data.table)
dt1 <- data.table(df1)
dt2 <- data.table(df2)
setkey(dt1, "id")
setkey(dt2, "id")
> dt2[dt1]

#    id  v2 v1
# 1:  1 200 10
# 2:  3 200 20
# 3:  5 190 20
# 4:  6 290 15

Answer 2

You can use sqldf

library(sqldf)
sqldf('SELECT dat.*,dat1.v2
       FROM dat,dat1
       WHERE dat.id = dat1.id')
  id v1  v2
1  1 10 200
2  3 20 200
3  5 20 190
4  6 15 290

But using merge we get the same result

merge(dat,dat1)
  id v1  v2
1  1 10 200
2  3 20 200
3  5 20 190
4  6 15 290

Answer 3

You can use an environment lookup via:

library(qdap)

A$v2 <- lookup(A$id, B)
A

> A
  id v1  v2
1  1 10 200
2  3 20 200
3  5 20 190
4  6 15 290

This is pretty fast on large data sets.