Canonical tidyverse method to update some values of a vector from a look-up table

A direct data.table solution, without %in%.
Depending on the length of the lookup / data tables, adding keys could improve performance substantially, but this isn't the case on this simple example.

library(data.table)

setDT(data)
setDT(lookup)

## If needed
# setkey(data,x)
# setkey(lookup,old)

data[lookup, x:=new, on=.(x=old)]
data 

   id  x
1:  1  a
2:  2  a
3:  3  B
4:  4  C
5:  5  d
6:  6 AA
7:  7  !

Benchmarking

Expanding the original dataset to 10M rows, 15 runs using microbenchmark gave the follow results on my computer:

Note that forcats::fct_recode and dplyr::recode solutions mentioned by the OP have also been included. Neither works with the updated data because the named vector that resolves to . = ! will throw an error, which is why results are tested on the original dataset.

data = data.frame(
  id = 1:5,
  x = c("A", "A", "B", "C", "D")
)

lookup = data.frame(
  old = c("A", "D"),
  new = c("a", "d")
)

set.seed(1)
data <- data[sample(1:5, 1E7, replace = T),]

dt_lookup <- data.table::copy(lookup)

dplyr_coalesce <- function(){
  library(dplyr)
  lookupV <- setNames(lookup$new, lookup$old)
  data %>% 
    dplyr::mutate(x = coalesce(lookupV[ x ], x))
}

datatable_in <- function(){
  library(data.table)
  lookupV <- setNames(lookup$new, lookup$old)
  setDT(dt_data)
  dt_data[ x %in% names(lookupV), x := lookupV[ x ] ]
}

datatable <- function(){
  library(data.table)
  
  setDT(dt_data)
  setDT(dt_lookup)
  
  ## If needed
  # setkey(data,x)
  # setkey(lookup,old)
  
  dt_data[dt_lookup, x:=new, on =.(x=old)]
}

purrr_modify_if <- function(){
  library(dplyr)
  library(purrr)
  lookupV <- setNames(lookup$new, lookup$old)
  data %>% 
    dplyr::mutate(x = modify_if(x, x %in% lookup$old, ~ lookupV[.x]))
}

stringr_str_replace_all_update <- function(){
  library(dplyr)
  library(stringr)
  lookupV <- setNames(lookup$new, do.call(sprintf, list("^\\Q%s\\E$", lookup$old)))
  
  data %>% 
    dplyr::mutate(x = str_replace_all(x, lookupV))
}

base_named_vector <- function(){
  lookupV <- c(with(lookup, setNames(new, old)), rlang::set_names(setdiff(unique(data$x), lookup$old)))
  lookupV[data$x]
}

base_ifelse <- function(){
  lookupV <- setNames(lookup$new, lookup$old)
  with(data, ifelse(x %in% lookup$old, lookup$new, x))
}

plyr_mapvalues <- function(){
  library(plyr)
  data %>% 
    dplyr::mutate(x = plyr::mapvalues(x, lookup$old, lookup$new, warn_missing = F))
}

base_match <- function(){
  tochange <- match(data$x, lookup$old, nomatch = 0)
  data$x[tochange > 0] <- lookup$new[tochange]
}

base_local_safe_lookup <- function(){
  lv <- structure(lookup$new, names = lookup$old)
  
  safe_lookup <- function(val) {
    new_val <- lv[val]
    unname(ifelse(is.na(new_val), val, new_val))
  }
  
  safe_lookup(data$x)
}

dplyr_recode <- function(){
  library(dplyr)
  lookupV <- setNames(lookup$new, lookup$old)
  
  data %>%
    dplyr::mutate(x = recode(x, !!!lookupV))
}

base_for <- function(){
  for (i in seq_len(nrow(lookup))) {
    data$x[data$x == lookup$old[i]] = lookup$new[i]
  }
}

datatable_for <- function(){
  library(data.table)
  setDT(dt_data)
  
  for (i in seq_len(nrow(lookup))) {
    dt_data[x == lookup$old[i], x := lookup$new[i]]
  }
}

forcats_fct_recode <- function(){
  library(dplyr)
  library(forcats)
  lookupV <- setNames(lookup$new, lookup$old)
  
  data %>% 
    dplyr::mutate(x = as.character(fct_recode(x, !!!lookupV)))
  
}

datatable_set <- function(){
  library(data.table)
  setDT(dt_data)
  
  tochange <- dt_data[, chmatch(x, lookup$old, nomatch = 0)]
  set(dt_data, i = which(tochange > 0), j = "x", value = lookup$new[tochange])
}

library(microbenchmark)
bench <- microbenchmark(dplyr_coalesce(),
                        datatable(),
                        datatable_in(),
                        datatable_for(),
                        base_for(),
                        purrr_modify_if(),
                        stringr_str_replace_all_update(),
                        base_named_vector(),
                        base_ifelse(),
                        plyr_mapvalues(),
                        base_match(),
                        base_local_safe_lookup(),
                        dplyr_recode(),
                        forcats_fct_recode(),
                        datatable_set(),
                        times = 15L,
                        setup = dt_data <- data.table::copy(data))

bench$expr <- forcats::fct_rev(forcats::fct_reorder(bench$expr, bench$time, mean))
ggplot2::autoplot(bench)

Thanks to @Waldi and @nicola for advice implementing data.table solutions in the benchmark.

Combination of a named vector and coalesce:

# make lookup vector
lookupV <- setNames(lookup$new, lookup$old)

data %>% 
  mutate(x = coalesce(lookupV[ x ], x))
#   id x
# 1  1 a
# 2  2 a
# 3  3 B
# 4  4 C
# 5  5 d

Or data.table:

library(data.table)

setDT(data)
data[ x %in% names(lookupV), x := lookupV[ x ] ]

This post might have a better solution for data.table - "update on merge":

• R data table: update join

A base R option using %in% and match - thanks to @LMc & @nicola

tochange <- match(data$x, lookup$old, nomatch = 0)
data$x[tochange > 0] <- lookup$new[tochange]

One more data.table option using set() and chmatch

library(data.table)
setDT(data)

tochange <- data[, chmatch(x, lookup$old, nomatch = 0)]
set(data, i = which(tochange > 0), j = "x", value = lookup$new[tochange])

Result

data
#  id  x
#1  1  a
#2  2  a
#3  3  B
#4  4  C
#5  5  d
#6  6 AA
#7  7  !

modify_if

You could use purrr::modify_if to only apply the named vector to values that exist in it. Though not a specified requirement, it has the benefit of the .else argument, which allows you to apply a different function to values not in your lookup.

I also wanted to include the use of tibble::deframe here to create the named vector. It is slower than setNames, though.

lookupV <- deframe(lookup)

data %>% 
  mutate(x = modify_if(x, x %in% lookup$old, ~ lookupV[.x]))

str_replace_all

Alternatively, you could use stringr::str_replace_all, which can take a named vector for the replacement argument.

data %>% 
  mutate(x = str_replace_all(x, lookupV))

Update

To accommodate the change to your edited example, the named vector used in str_replace_all needs to be modified. In this way, the entire literal string needs to be match so that "A" does not get substituted in "AA", or "." does not replace everything:

lookupV <- setNames(lookup$new, do.call(sprintf, list("^\\Q%s\\E$", lookup$old)))

data %>% 
  mutate(x = str_replace_all(x, lookupV))

left_join

Using dplyr::left_join this is very similar to OP solution, but uses .keep argument of mutate so it has less steps. This argument is currently in the experimental lifecycle and so it is not included in the benchmark (though it is around the middle of posted solutions).

left_join(data, lookup, by = c("x" = "old")) %>% 
    mutate(x = coalesce(new, x) , .keep = "unused")

Base R

Named Vector

Create a substitution value for every unique value in your dataframe.

lookupV <- c(with(lookup, setNames(new, old)), setNames(nm = setdiff(unique(data$x), lookup$old)))

data$x <- lookupV[data$x]

ifelse

with(data, ifelse(x %in% lookup$old, lookupV[x], x))

Another option that is clear is to use a for-loop with subsetting to loop through the rows of the lookup table. This will almost always be quicker with data.table because of auto indexing, or if you set the key (i.e., ?data.table::setkey()) ahead of time. Also, it will--of course--get slower as the lookup table gets longer. I would guess an update-join would be preferred if there is a long lookup table.

Base R:

for (i in seq_len(nrow(lookup))) {
  data$x[data$x == lookup$old[i]] <- lookup$new[i]
}

data$x
# [1] "a"  "a"  "B"  "C"  "d"  "AA" "!" 

Or the same logic with data.table:

library(data.table)
setDT(data)

for (i in seq_len(nrow(lookup))) {
  data[x == lookup$old[i], x := lookup$new[i]]
}

data$x
# [1] "a"  "a"  "B"  "C"  "d"  "AA" "!" 

Data:

data = data.frame(
  id = 1:7,
  x = c("A", "A", "B", "C", "D", "AA", ".")
)

lookup = data.frame(
  old = c("A", "D", "."),
  new = c("a", "d", "!")
)

Another base solution, with a lookup vector:

## Toy example
data = data.frame(
  id = 1:5,
  x = c("A", "A", "B", "C", "D"),
  stringsAsFactors = F
)

lookup = data.frame(
  old = c("A", "D"),
  new = c("a", "d"),
  stringsAsFactors = F
)

lv <- structure(lookup$new, names = lookup$old)

safe_lookup <- function(val) {
  new_val <- lv[val]
  unname(ifelse(is.na(new_val), val, new_val))
}

data$x <- safe_lookup(data$x)

dplyr+plyr solution that is in order with all ur bulletpoints (if u consider plyr in the the tidyverse):

data <- data %>% 
  dplyr::mutate(
    x = plyr::mapvalues(x, lookup$old, lookup$new) #Can add , F to remove warnings
  )

I basically share the same problem. Although dplyr::recode is in the "questioning" life cycle I don't expect it to become deprecated. At some point it might be superseded, but even in this case it should still be usable. Therefore I'm using a wrapper around dplyr::recode which allows the use of named vectors and or two vectors (which could be a lookup table).

library(dplyr)
library(rlang)

recode2 <- function(x, new, old = NULL, .default = NULL, .missing = NULL) {
  
  if (!rlang::is_named(new) && !is.null(old)) {
    new <- setNames(new, old)
  }
  
  do.call(dplyr::recode,
          c(.x = list(x),
            .default = list(.default),
            .missing = list(.missing),
            as.list(new)))
  
}

data = data.frame(
  id = 1:7,
  x = c("A", "A", "B", "C", "D", "AA", ".")
)

lookup = data.frame(
  old = c("A", "D", "."),
  new = c("a", "d", "!")
)

# two vectors new / old
data %>% 
  mutate(x = recode2(x, lookup$new, lookup$old))
#>   id  x
#> 1  1  a
#> 2  2  a
#> 3  3  B
#> 4  4  C
#> 5  5  d
#> 6  6 AA
#> 7  7  !

# named vector
data %>% 
  mutate(x = recode2(x, c("A" = "a",
                          "D" = "d",
                          "." = "!")))
#>   id  x
#> 1  1  a
#> 2  2  a
#> 3  3  B
#> 4  4  C
#> 5  5  d
#> 6  6 AA
#> 7  7  !

^{Created on 2021-04-21 by the reprex package (v0.3.0)}