如何在R中实现列间字符串模糊匹配（排除子串嵌入匹配）

Your goal is to match market names from vendor_list to firm_list only when the name appears as a standalone word block (not a substring of another word). The key here is using regular expression word boundaries to enforce this rule, rather than string distance algorithms which don't account for word structure.

Step 1: Define Your DataFrames #

First, let's recap your input data for clarity:

# Build vendor_list
MARKET_ID <- c(1,2,3,4,5)
MARKET_NAME <- c("DELHI","MUMBAI","BANGALORE","KOLKATA","CHENNAI")
vendor_list <- data.frame(MARKET_ID,MARKET_NAME)

# Build firm_list
MARKET_NAME <- c("DELHI MUNICIPAL CORP","DELHI","MUMBAI","BENGALURU","BANGALORES","CITYKOLKATA")
POPULATION <- c(1000,2000,3000,4000,5000,6000)
firm_list <- data.frame(MARKET_NAME,POPULATION)

Step 2: Use Regular Expression Word Boundaries for Matching #

We'll leverage \\b (word boundary) in regex to ensure we only match full, standalone words. A word boundary matches the position between a word character (letters/numbers/underscores) and a non-word character (spaces, punctuation, start/end of string).

Option 1: Tidyverse + Purrr Approach

This method gives you explicit control over the matching process:

library(tidyverse)

final_market_info <- vendor_list %>%
  # Create a regex pattern for each market name to match standalone words
  mutate(match_pattern = str_c("\\b", MARKET_NAME, "\\b")) %>%
  # Iterate over each row to find matching entries in firm_list
  pmap_dfr(function(MARKET_ID, MARKET_NAME, match_pattern) {
    # Filter firm_list for entries that match the standalone word pattern
    matching_firms <- firm_list %>%
      filter(str_detect(MARKET_NAME, regex(match_pattern, ignore_case = FALSE))) %>%
      rename(MARKET_NAME.y = MARKET_NAME)
    
    # Only return rows if there's a match
    if (nrow(matching_firms) > 0) {
      tibble(
        MARKET_ID = MARKET_ID,
        MARKET_NAME.x = MARKET_NAME,
        matching_firms
      )
    } else {
      tibble() # Return empty if no matches
    }
  })

# View the result
print(final_market_info)

Option 2: Fuzzyjoin Regex Join (Shorter Version)

If you prefer a more concise approach, use the fuzzyjoin package's regex_join to directly connect the dataframes using your regex pattern:

library(fuzzyjoin)
library(stringr)

final_market_info <- regex_join(
  vendor_list,
  firm_list,
  by = "MARKET_NAME",
  # Create standalone word patterns for each vendor market name
  pattern = str_c("\\b", vendor_list$MARKET_NAME, "\\b"),
  mode = "inner" # Only keep rows with matches
) %>%
  # Rename columns to match your desired output
  rename(MARKET_NAME.x = MARKET_NAME.x, MARKET_NAME.y = MARKET_NAME.y)

Step 3: Verify the Result #

Both methods will produce your desired output:

# A tibble: 3 × 4
  MARKET_ID MARKET_NAME.x MARKET_NAME.y      POPULATION
      <dbl> <chr>         <chr>                   <dbl>
1         1 DELHI         DELHI MUNICIPAL CORP      1000
2         1 DELHI         DELHI                     2000
3         2 MUMBAI        MUMBAI                    3000

Why Stringdist_join Didn't Work #

The stringdist_join function uses algorithms like LCS or Jaro-Winkler to measure string similarity, not word structure. It would incorrectly match BANGALORE to BANGALORES (since they're highly similar) or KOLKATA to CITYKOLKATA, which violates your "standalone word" rule. The regex approach explicitly enforces that the market name is a separate word block.

内容的提问来源于stack exchange，提问作者avishkar683