Replace column values in table with values from lookup based on matches in R using data.table

Question

I'd like to replace values in the table column with corresponding values of matches in the lookup column. I achieved this with data.table package "assign by reference" taking one value (and it's replacement) after the other, but I thought I could do something more modular.

In summary, I do this:

# Build a table and a lookup
code<- c("ABC","EBC","ABC","EBC","OOO","PPP","ABC")
sn <- c(1:7)
old<- c("ABC","EBC")
new<- c("CBa","CBe")

lookup <- data.frame(old,new)
table <-data.frame(code,sn)

# Set data.table as TRUE for both
setDT(table)
setDT(lookup)

# Attempt reassignment
table[code %in% lookup$old, code := lookup$new[which(lookup$old==code)]]
table

..and I get this:

> table
   code sn
1:  CBa  1
2:  CBe  2
3: <NA>  3
4: <NA>  4
5:  OOO  5
6:  PPP  6
7: <NA>  7

However, what I really hope for is this results:

> table
   code sn
1:  CBa  1
2:  CBe  2
3:  CBa  3
4:  CBe  4
5:  OOO  5
6:  PPP  6
7:  CBa  7

In essence: the replacement is made based on the lookup with all values assigned (not just the first two). What am I missing? I have tried looking for other solutions around but suggestions don't quite seem to be what I am looking for. Grateful for any answers.

Answer 1

We can do a join on the 'code' and 'old' from table and lookup respectively

table[lookup, code := new, on = .(code = old)]

-output

 table
   code sn
1:  CBa  1
2:  CBe  2
3:  CBa  3
4:  CBe  4
5:  OOO  5
6:  PPP  6
7:  CBa  7