Let‘say I have a TableA:
Code Description
001 Apple
002 Banana
I also have TableB:
Description
001
002
003
I want to replace the values in Description of TableB with values from Description of TableA.
I understand that I can do a simple join. But, I would like to replace values. How can I do that?
My possible solution is the following, but it does not work properly:
TableB$Description <- TableA[TableA$Code %in% TableB$Description, Description]
This could be a merge/join operation,
merge(TableB, TableA, by.x = "Description", by.y = "Code", all.x = TRUE)
# Description Description.y
# 1 1 Apple
# 2 2 Banana
# 3 3 <NA>
(For more info on merge/join, see How to join (merge) data frames (inner, outer, left, right) and What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?)
Or it could be a simple lookup using match:
TableA$Description[ match(TableB$Description, TableA$Code) ]
# [1] "Apple" "Banana" NA
You could use the elucidate::translate() function, which provides a more readable syntax for the match() solution posted by r2evans.
tableA <- data.frame(code = c(001, 002),
Description = c("Apple", "Banana"))
tableB <- data.frame(Description = c(001, 002, 003))
tableB$Description <- translate(tableB$Description, old = tableA$code, new = tableA$Description)
tableB
> tableB
Description
1 Apple
2 Banana
3 <NA>
The elucidate package can be installed with:
remotes::install_github("bcgov/elucidate")
