使用stringdist进行企业名称模糊匹配效果不佳及大数据匹配咨询

First, let's address the two big issues here: using expand.grid with 5 million records is computationally impossible (you'd end up with trillions of pairs), and subpar matching is almost always tied to missing name standardization or misconfigured distance parameters. Let's walk through step-by-step fixes:

1. Standardize Company Names First #

Most fuzzy matching failures start here—variations like uppercase/lowercase, special characters, or common abbreviations (e.g., "Ltd" vs "Limited") throw off distance calculations. Build a standardization function to normalize all names:

standardize_name <- function(name) {
  # Convert to lowercase
  name <- tolower(name)
  # Remove non-alphanumeric characters (keep spaces)
  name <- gsub("[^a-z0-9 ]", "", name)
  # Collapse multiple spaces to one, trim edges
  name <- gsub("\\s+", " ", name) %>% trimws()
  # Replace common business suffix abbreviations
  name <- gsub("\\bco\\b", "company", name)
  name <- gsub("\\bltd\\b", "limited", name)
  name <- gsub("\\binc\\b", "incorporated", name)
  name <- gsub("\\bcorp\\b", "corporation", name)
  return(name)
}

# Apply to your data frames
Companylist$standardized <- standardize_name(Companylist$Companies)
Biglist$standardized <- standardize_name(Biglist$name)

This alone will drastically improve matching accuracy by eliminating trivial variations.

2. Ditch expand.grid—Use Efficient Fuzzy Joins #

Generating every possible pair is never going to work for 5M records. Instead, use fuzzyjoin's stringdist_join—it only computes distances for plausible matches and avoids the combinatorial explosion.

For your "Amminex" example, here's how to find matches with a reasonable edit distance threshold:

library(fuzzyjoin)
library(stringdist)

# Match standardized names with a maximum Levenshtein distance of 2
# (adjust threshold based on how tolerant you need to be of typos)
matches <- stringdist_join(
  Companylist,
  Biglist,
  by = "standardized",
  max_dist = 2,
  method = "lv",  # Levenshtein distance works best for typo fixes
  mode = "left",  # Keep all entries from Companylist, match to Biglist
  ignore_case = FALSE  # We already standardized case, so skip this
)

# Inspect the results
head(matches[, c("Companies", "name", "standardized.x", "standardized.y")])

Optimize for 5M Records #

Even stringdist_join can slow down with 5M rows—try these tweaks:

• Group by first character: Only match names that start with the same character (most typos don't change the first letter). Add a first_char column to both data frames and filter matches to same-group pairs.
• Use data.table: Convert your data frames to data.table for faster grouping and joins—fuzzyjoin works with it, or you can use data.table's own subsetting logic to reduce the pool of candidates.
• Filter by name length: Skip pairs where the standardized names differ in length by more than your max_dist + 1 (e.g., if max_dist=2, don't match a 6-character name to a 10-character name).

3. Tune stringdist Parameters for Better Accuracy #

Not all distance methods work the same for company names:

• Levenshtein ("lv"): Best for common typos (missing/extra characters, letter swaps).
• Jaro-Winkler ("jw"): Great for short names like "Amminex"—it prioritizes matching prefixes, which is how most people misspell brand names.
• Cosine ("cosine"): Useful for longer company names with word order variations.

For "Amminex", try switching to Jaro-Winkler with a prefix weight (adjust p to prioritize early characters):

matches_jw <- stringdist_join(
  Companylist,
  Biglist,
  by = "standardized",
  max_dist = 0.1,  # Jaro-Winkler uses 0 (exact) to 1 (no match)
  method = "jw",
  p = 0.1,  # Prefix weight—higher values prioritize first characters
  mode = "left"
)

4. Cluster Duplicate Names in Biglist #

If your goal is to group all similar names in Biglist into a single entity (rather than just matching to Companylist), use hierarchical clustering on standardized names:

library(dplyr)
library(stringdist)

# Group by first character to reduce computation
Biglist <- Biglist %>%
  mutate(first_char = substr(standardized, 1, 1))

# Cluster within each first-character group
clustered_names <- Biglist %>%
  group_by(first_char) %>%
  group_modify(function(group, key) {
    if (nrow(group) <= 1) {
      group$cluster_id <- 1
      return(group)
    }
    # Calculate Jaro-Winkler distance matrix
    dist_mat <- stringdistmatrix(group$standardized, method = "jw", p = 0.1)
    # Cluster with hierarchical clustering
    hc <- hclust(as.dist(dist_mat), method = "ward.D")
    # Assign cluster IDs (adjust h to control cluster tightness)
    group$cluster_id <- cutree(hc, h = 0.1)
    return(group)
  }) %>%
  ungroup()

# Assign a representative name to each cluster (most frequent entry)
clustered_names <- clustered_names %>%
  group_by(cluster_id) %>%
  mutate(representative_name = names(which.max(table(standardized)))) %>%
  ungroup()

This will group all "Amminex" variations (like "AMMINEX", "Aminex", "Amminexx") into the same cluster with a single representative name.

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