如何用Python合并列格式部分相似的DataFrame并实现多格式姓名匹配？

Hey there, this is a super common pain point when reconciling external vendor data with structured internal HR records—let’s walk through a robust solution to match those messy name formats and merge your DataFrames effectively using Python and pandas.

The core idea here is to:

1. Generate all possible standardized name formats from your HR data (since it's structured with separate first/last names)
2. Clean up the vendor's name column to remove inconsistencies
3. Use exact matching first for clean, straightforward matches
4. Fall back to fuzzy matching for partial/abbreviated names that don't hit exact matches

1. Prep Your HR DataFrame #

Since your HR data has separate first_name and last_name columns, we can generate all the name variations that the vendor data might use. This gives us multiple target keys to match against.

import pandas as pd

# Example HR DataFrame (replace with your actual data)
hr_df = pd.DataFrame({
    "employee_id": [101, 102, 103],
    "first_name": ["John", "Emily", "Michael"],
    "last_name": ["Smith", "Johnson", "Williams"]
})

# Generate all possible name formats from HR data
hr_df["full_first_last"] = hr_df["first_name"] + " " + hr_df["last_name"]
hr_df["full_last_first"] = hr_df["last_name"] + " " + hr_df["first_name"]
hr_df["init_dot_last"] = hr_df["first_name"].str[0] + ". " + hr_df["last_name"]
hr_df["init_no_dot_last"] = hr_df["first_name"].str[0] + " " + hr_df["last_name"]

2. Clean Up the Vendor DataFrame #

First, we'll standardize the vendor's name column to fix trivial inconsistencies like extra spaces, case mismatches, and odd capitalization.

# Example Vendor DataFrame (replace with your actual data)
vendor_df = pd.DataFrame({
    "vendor_record_id": [201, 202, 203],
    "employee_name": ["John Smith", "J Johnson", "Williams Michael"]
})

# Standardize: trim spaces, fix case to Title Case
vendor_df["cleaned_name"] = vendor_df["employee_name"].str.strip().str.title()

3. Match Names: Exact First, Fuzzy Second #

Exact Matching (Low-Hanging Fruit) #

We'll merge the vendor data against all the name formats we generated for HR data. This catches all perfect matches without any guesswork.

# Start with first-last name match
merged = vendor_df.merge(
    hr_df,
    left_on="cleaned_name",
    right_on="full_first_last",
    how="left"
)

# If no match, try last-first format
merged = merged.merge(
    hr_df,
    left_on="cleaned_name",
    right_on="full_last_first",
    how="left",
    suffixes=("", "_last_first")
)

# Try initial + last name (with and without dot)
merged = merged.merge(
    hr_df,
    left_on="cleaned_name",
    right_on="init_dot_last",
    how="left",
    suffixes=("", "_init_dot")
)

merged = merged.merge(
    hr_df,
    left_on="cleaned_name",
    right_on="init_no_dot_last",
    how="left",
    suffixes=("", "_init_no_dot")
)

# Combine matching HR columns (fill missing values from alternate matches)
merged["employee_id"] = merged["employee_id"].fillna(merged["employee_id_last_first"]).fillna(merged["employee_id_init_dot"]).fillna(merged["employee_id_init_no_dot"])
merged["first_name"] = merged["first_name"].fillna(merged["first_name_last_first"]).fillna(merged["first_name_init_dot"]).fillna(merged["first_name_init_no_dot"])
merged["last_name"] = merged["last_name"].fillna(merged["last_name_last_first"]).fillna(merged["last_name_init_dot"]).fillna(merged["last_name_init_no_dot"])

# Drop duplicate columns from multiple merges
merged = merged.drop([col for col in merged.columns if "_last_first" in col or "_init_" in col], axis=1)

Fuzzy Matching for Tricky Cases #

For rows still without a match (like typos or non-standard abbreviations), use rapidfuzz (a faster, maintained alternative to fuzzywuzzy) to find the closest match with a confidence score.

from rapidfuzz import process, fuzz

# Get rows that didn't get an exact match
unmatched = merged[merged["employee_id"].isna()]

# Function to find the best HR name match for a given vendor name
def get_best_match(vendor_name, hr_name_list, score_cutoff=80):
    # Use token_sort_ratio to handle name order differences automatically
    match_result = process.extractOne(vendor_name, hr_name_list, scorer=fuzz.token_sort_ratio)
    if match_result and match_result[1] >= score_cutoff:
        return match_result[0]
    return None

# Compile all possible HR name variations into a single list
all_hr_names = pd.concat([
    hr_df["full_first_last"],
    hr_df["full_last_first"],
    hr_df["init_dot_last"],
    hr_df["init_no_dot_last"]
]).unique()

# Find best matches for unmatched rows
unmatched["matched_hr_name"] = unmatched["cleaned_name"].apply(lambda x: get_best_match(x, all_hr_names))

# Merge back with HR data to get employee details
unmatched_merged = unmatched.merge(
    hr_df.melt(id_vars=["employee_id", "first_name", "last_name"], value_name="hr_name"),
    left_on="matched_hr_name",
    right_on="hr_name",
    how="left"
)

# Combine exact and fuzzy matches into the final DataFrame
final_merged = pd.concat([
    merged[~merged["employee_id"].isna()],
    unmatched_merged.drop(["matched_hr_name", "variable", "hr_name"], axis=1)
])

4. Validate & Refine #

Always manually review the fuzzy matches to catch false positives. You can adjust the score_cutoff (we used 80 here) to be stricter/harder based on your data quality:

• Higher cutoff (e.g., 90) = fewer false matches but may miss some valid ones
• Lower cutoff (e.g., 70) = catches more matches but increases risk of errors

Optional: Add a match score column to track confidence:

unmatched["match_confidence_score"] = unmatched["cleaned_name"].apply(
    lambda x: process.extractOne(x, all_hr_names, scorer=fuzz.token_sort_ratio)[1] if get_best_match(x, all_hr_names) else None
)

Pro Tips: #

• Normalize first: Always handle whitespace, case, and special characters before matching—this eliminates 90% of avoidable mismatches.
• Prioritize exact matches: They're faster and more reliable than fuzzy matches, so always do those first.
• Test with a sample: Run this on a small subset of your data first to tweak the cutoff and matching logic before scaling.

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