如何实现不同长度DataFrame列之间的模糊匹配？

Great question! When dealing with DataFrames of different lengths, the core challenge is moving beyond 1:1 row matches to evaluating all plausible candidate pairs, then narrowing down to the best matches. Let’s walk through a practical, actionable approach using Python’s pandas and fuzzy matching tools:

First, let’s set up our tools and sample data. I’ll use rapidfuzz (a faster, more efficient alternative to fuzzywuzzy) for scoring matches:

import pandas as pd
from rapidfuzz import process, fuzz

# Sample DataFrames of different lengths
df_left = pd.DataFrame({"id_left": [1, 2, 3], "name": ["Apple Inc.", "Microsoft Corp", "Google LLC"]})
df_right = pd.DataFrame({"id_right": [101, 102, 103, 104], "company_name": ["Apple Incorporated", "Microsoft", "Google Inc", "Amazon.com"]})

Step 1: Generate All Candidate Pairs #

We need to create every possible combination of rows from the two DataFrames. A cross join (Cartesian product) is the simplest way to do this:

# Add a dummy column to enable cross join
df_left["dummy"] = 1
df_right["dummy"] = 1

# Perform cross join to get all candidate pairs
candidates = df_left.merge(df_right, on="dummy").drop("dummy", axis=1)

Step 2: Calculate Fuzzy Matching Scores #

Next, apply a fuzzy scoring metric to compare your target columns. token_set_ratio is ideal for strings like company names—it ignores word order and handles partial matches gracefully:

candidates["fuzzy_score"] = candidates.apply(
    lambda row: fuzz.token_set_ratio(row["name"], row["company_name"]),
    axis=1
)

Step 3: Filter to Best Matches #

Choose an approach based on your use case:

Option 1: Keep the highest-scoring match per row in the left DataFrame #

Use this if you want one best match for each entry in your smaller/primary DataFrame:

# Get the maximum score for each row in df_left
max_scores = candidates.groupby("id_left")["fuzzy_score"].max().reset_index()

# Merge back to retrieve full match details
best_matches = candidates.merge(max_scores, on=["id_left", "fuzzy_score"])

# Optional: Filter out low-quality matches with a threshold (e.g., 80/100)
best_matches = best_matches[best_matches["fuzzy_score"] >= 80]

Option 2: Keep all matches above a quality threshold #

Use this if you want to see all plausible matches, not just the top one:

# Keep only pairs with a score above your chosen threshold
threshold_matches = candidates[candidates["fuzzy_score"] >= 70]

Step 4: Optimizations for Large DataFrames #

If your DataFrames are large (10k+ rows), a cross join will become slow and memory-heavy. Try these optimizations:

• Use process.extractOne to get only the top match for each row in the smaller DataFrame:

  def get_best_match(name, choices, threshold=80):
      match = process.extractOne(name, choices, scorer=fuzz.token_set_ratio)
      return match[0] if match[1] >= threshold else None
  
  df_left["best_match"] = df_left["name"].apply(
      lambda x: get_best_match(x, df_right["company_name"])
  )
  

• Use the recordlinkage library, which includes blocking (grouping similar rows first) to drastically reduce candidate pairs:

  from recordlinkage import Index, Compare
  
  # Block on the first letter of the name to reduce unnecessary comparisons
  indexer = Index()
  indexer.block(left_on=lambda x: x["name"][0], right_on=lambda x: x["company_name"][0])
  candidate_pairs = indexer.index(df_left, df_right)
  
  # Calculate matching scores
  comparer = Compare()
  comparer.string("name", "company_name", method="jarowinkler", label="name_score")
  
  features = comparer.compute(candidate_pairs, df_left, df_right)
  
  # Filter pairs with high enough similarity
  matches = features[features["name_score"] >= 0.8]
  

Key Tips #

• Pick the right scorer for your data: token_set_ratio for flexible name matches, partial_ratio for partial string matches (e.g., "Apple" vs "Apple Inc."), and jarowinkler for shorter strings like addresses.
• Always spot-check matches manually—fuzzy logic isn’t perfect, so setting a threshold and validating a sample ensures accuracy.

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