如何高效对两个大型Pandas DataFrame进行模糊合并？

问题背景 #

有两个包含公司名称的Pandas DataFrame，其中一个有500万行数据，另一个约1万行，需要基于公司名称进行模糊合并（存在拼写错误、简写等差异），但当前使用的fuzzywuzzy匹配代码运行耗时极长，需要更高效的实现方法。

当前使用的代码 #

def fuzzy_merge(df_1, df_2, key1, key2, threshold=90, limit=2):
    """
    :param df_1: the left table to join
    :param df_2: the right table to join
    :param key1: key column of the left table
    :param key2: key column of the right table
    :param threshold: how close the matches should be to return a match, based on Levenshtein distance
    :param limit: the amount of matches that will get returned, these are sorted high to low
    :return: dataframe with boths keys and matches
    """
    s = df_2[key2].tolist()

    m = df_1[key1].apply(lambda x: process.extract(x, s, limit=limit))    
    df_1['matches'] = m

    m2 = df_1['matches'].apply(lambda x: ', '.join([i[0] for i in x if i[1] >= threshold]))
    df_1['matches'] = m2

    return df_1

样本数据 #

df1

df2

优化方案 #

1. 先标准化公司名称（核心优化步骤） #

通过预处理减少字符串差异，降低后续匹配的计算量：

• 统一转为小写，避免大小写干扰
• 去除特殊字符、多余空格
• 移除通用前缀/后缀（如"The"、"Ltd."、"Inc."）
• 提取词干（可选），统一相似词汇的形态

import pandas as pd
import re
from nltk.stem import PorterStemmer

stemmer = PorterStemmer()

def clean_company_name(name):
    if pd.isna(name):
        return ""
    # 转小写
    name = str(name).lower()
    # 移除特殊字符
    name = re.sub(r'[^\w\s]', '', name)
    # 移除常见前缀
    common_prefixes = ["the ", "inc ", "ltd ", "corp ", "co "]
    for prefix in common_prefixes:
        if name.startswith(prefix):
            name = name[len(prefix):].strip()
    # 提取词干
    words = name.split()
    stemmed_words = [stemmer.stem(word) for word in words]
    return ' '.join(stemmed_words)

# 对两个DataFrame的公司名称做预处理
df1['Cleaned_Name'] = df1['Company Name'].apply(clean_company_name)
df2['Cleaned_Name'] = df2['Company Name'].apply(clean_company_name)

2. 用RapidFuzz替代FuzzyWuzzy（提速10-100倍） #

FuzzyWuzzy是纯Python实现，速度极慢；RapidFuzz是其C语言重写版，性能提升显著，用法基本兼容。

from rapidfuzz import process, fuzz

def fuzzy_merge_rapid(df_left, df_right, key_left, key_right, threshold=90, limit=2):
    right_names = df_right[key_right].tolist()
    # 用RapidFuzz的process.extract批量匹配
    df_left['matches'] = df_left[key_left].apply(
        lambda x: process.extract(x, right_names, scorer=fuzz.WRatio, limit=limit)
    )
    # 过滤符合阈值的结果并格式化
    df_left['matches'] = df_left['matches'].apply(
        lambda x: ', '.join([match[0] for match in x if match[1] >= threshold])
    )
    return df_left

# 使用预处理后的列进行匹配
result_df = fuzzy_merge_rapid(df1, df2, 'Cleaned_Name', 'Cleaned_Name')

3. 分块匹配进一步减少计算量 #

将左表（500万行）按名称首字母/长度分块，右表同步分块，仅在同块内进行匹配，避免全量遍历：

def block_based_merge(df_left, df_right, key_left, key_right, threshold=90, limit=2):
    # 按名称首字母分块（也可以按字符串长度分块）
    df_left['block'] = df_left[key_left].str[0].str.lower()
    df_right['block'] = df_right[key_right].str[0].str.lower()
    
    final_result = pd.DataFrame()
    # 遍历每个块，仅在同块内执行匹配
    for block in df_left['block'].unique():
        left_block = df_left[df_left['block'] == block].copy()
        right_block = df_right[df_right['block'] == block]
        
        if len(right_block) == 0:
            left_block['matches'] = ""
            final_result = pd.concat([final_result, left_block])
            continue
        
        # 块内用RapidFuzz匹配
        left_block['matches'] = left_block[key_left].apply(
            lambda x: process.extract(x, right_block[key_right].tolist(), scorer=fuzz.WRatio, limit=limit)
        )
        left_block['matches'] = left_block['matches'].apply(
            lambda x: ', '.join([match[0] for match in x if match[1] >= threshold])
        )
        final_result = pd.concat([final_result, left_block])
    
    final_result.drop('block', axis=1, inplace=True)
    return final_result

4. 并行化处理超大数据集 #

用swifter库自动实现并行化apply，充分利用多核CPU：

import swifter

# 用swifter加速apply操作
df1['matches'] = df1['Cleaned_Name'].swifter.apply(
    lambda x: process.extract(x, df2['Cleaned_Name'].tolist(), scorer=fuzz.WRatio, limit=2)
)
df1['matches'] = df1['matches'].swifter.apply(
    lambda x: ', '.join([match[0] for match in x if match[1] >= threshold])
)

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