When working with messy text data or unstructured metadata in R, you often need to perform joins that standard functions like dplyr::left_join() cannot handle out of the box. A classic example is matching a reference table to a dataset where the key is buried inside a descriptive sentence or string.

If you have hundreds of potential keywords to search across, chaining nested ifelse() or grepl() calls quickly becomes unmaintainable and computationally inefficient. Here are the two best, idiomatic ways to solve this problem in R using modern packages: regex extraction with dplyr and declarative matching with fuzzyjoin.

The Sample Data

Let's use the reproducible data provided in the scenario:

specimen_origin <- data.frame(
  specimen = c("Specimen 1", "Specimen 2", "Specimen 3", "Specimen 4"),
  origin   = c("Collected on trip to Denver, Colorado", 
               "Found in wild at Ohio Botanical Gardens", 
               "Cliffside during Yunnan 2002 expedition", 
               "Unknown"),
  stringsAsFactors = FALSE
)

country_origin_index <- data.frame(
  region  = c("Colorado", "Ohio", "Missouri", "Texas", "Yunnan", "Chongqing"),
  country = c("USA", "USA", "USA", "USA", "China", "China"),
  stringsAsFactors = FALSE
)

Method 1: Regex Extraction + Standard Join (Fastest & Most Scalable)

If you have thousands of rows and a long list of keywords, compiling your search terms into a single regular expression pattern and extracting the match before joining is the most scalable approach.

We can use stringr::str_extract() together with word boundaries (\b) to avoid false matches (e.g., ensuring "Ind" doesn't accidentally match "India"):

library(dplyr)
library(stringr)
library(tidyr)

# 1. Create a regex alternation pattern using word boundaries
pattern <- paste0("\\b(", paste(country_origin_index$region, collapse = "|"), ")\\b")

# 2. Extract the matching region and perform a standard left_join
result <- specimen_origin %>%
  mutate(region = str_extract(origin, pattern)) %>%
  left_join(country_origin_index, by = "region") %>%
  mutate(across(c(region, country), ~ replace_na(.x, "")))

print(result)

Output:

    specimen                                  origin   region country
1 Specimen 1   Collected on trip to Denver, Colorado Colorado     USA
2 Specimen 2 Found in wild at Ohio Botanical Gardens     Ohio     USA
3 Specimen 3 Cliffside during Yunnan 2002 expedition   Yunnan   China
4 Specimen 4                                 Unknown

Why this works well:

  • Performance: The extraction runs in vectorised C code under the hood (via stringi), which is significantly faster than performing a full cross-join with regex evaluations.
  • Safety: Wrapping keywords in \b ensures you match full words rather than partial character sequences inside unrelated words.

Method 2: Using the fuzzyjoin Package

If you prefer a declarative syntax without constructing regex strings manually, the fuzzyjoin package provides a dedicated helper function called regex_left_join().

Install the package if you haven't already:

install.packages("fuzzyjoin")

Then join the two tables directly by evaluating the regex relationship:

library(dplyr)
library(fuzzyjoin)
library(tidyr)

result <- regex_left_join(
  specimen_origin,
  country_origin_index,
  by = c(origin = "region"),
  ignore_case = FALSE
) %>%
  mutate(across(c(region, country), ~ replace_na(.x, "")))

print(result)

Important Considerations with fuzzyjoin

  • Multiple Matches: If an origin string contains more than one region (for example, "Specimen from Colorado and Ohio border"), fuzzyjoin will duplicate rows for each match, just like a standard SQL join.
  • Substrings: By default, regex_left_join() treats the values in country_origin_index$region as regular expressions. If your regions contain special regex characters (such as parentheses or periods), you should escape them beforehand using stringr::str_escape().

Summary: Which Approach Should You Use?

  • Use Method 1 (str_extract + left_join) if you expect at most one region per specimen description, need maximum performance across large datasets, or want strict word-boundary checks.
  • Use Method 2 (fuzzyjoin::regex_left_join) if you want concise code or genuinely need to retain duplicate rows when multiple keywords match the same string.