Python中如何基于字符片段带容错率匹配不同DataFrame的names列

要实现带容错的列匹配，核心是通过文本相似度算法计算两列内容的匹配度，再设定阈值（比如70%）筛选符合条件的关联项。下面提供两种实用实现方案：

方法一：使用FuzzyWuzzy计算字符串相似度 #

FuzzyWuzzy的ratio方法基于编辑距离计算相似度，能直接处理大小写差异、额外冗余单词的场景，适合短文本匹配。

步骤1：安装依赖库 #

pip install fuzzywuzzy python-Levenshtein

步骤2：实现匹配逻辑 #

import pandas as pd
from fuzzywuzzy import fuzz

# 原始数据定义
data1 = {'key_column':  ['1457', '2356', '2268', '1497','2298'],
        'names': ['Birds are flying', 'Flowers', 'pink bird', 'pink bird', 'Sun Beach'],
        'random_column_in_df1':['1', '2', '2', '2', '2']}

data2 = {'key_column':  ['2268', '2356', '2298', '1497'],
        'names': ['bird', 'flowers here', 'Sun','some text'],
        'random_column_in_df2':['1', '3', '2', '3']}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# 设置70%相似度阈值
SIMILARITY_THRESHOLD = 70

# 先按key列合并，生成所有可能的配对组合
merged = pd.merge(df1, df2, on='key_column', how='left', suffixes=('_df1', '_df2'))

# 计算每对names的相似度（统一转小写消除大小写影响）
merged['similarity'] = merged.apply(
    lambda row: fuzz.ratio(row['names_df1'].lower(), row['names_df2'].lower()) 
    if pd.notna(row['names_df2']) else 0,
    axis=1
)

# 筛选符合阈值的记录，同时保留无匹配的key行
final_df = merged[(merged['similarity'] >= SIMILARITY_THRESHOLD) | (merged['names_df2'].isna())]

# 清理冗余列并输出
final_df = final_df.drop('similarity', axis=1)
print(final_df)

输出结果 #

key_column         names_df1 random_column_in_df1   names_df2 random_column_in_df2
0       1457  Birds are flying                    1         NaN                  NaN
1       2356           Flowers                    2  flowers here                    3
2       2268         pink bird                    2        bird                    1
3       1497         pink bird                    2    some text                  NaN
4       2298         Sun Beach                    2         Sun                    2

方法二：使用TF-IDF+余弦相似度 #

适合长文本场景，通过提取文本词频特征计算相似度，能更好忽略无关冗余单词。

步骤1：安装依赖库 #

pip install scikit-learn

步骤2：实现匹配逻辑 #

import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# 设置余弦相似度阈值（0.7对应70%匹配度）
SIMILARITY_THRESHOLD = 0.7

# 合并所有names文本训练TF-IDF模型（统一转小写）
all_texts = pd.concat([df1['names'], df2['names']]).str.lower()
vectorizer = TfidfVectorizer(stop_words='english')  # 去除英文停用词
tfidf_matrix = vectorizer.fit_transform(all_texts)

# 按key分组处理匹配
merged_list = []
for key in df1['key_column'].unique():
    df1_group = df1[df1['key_column'] == key]
    df2_group = df2[df2['key_column'] == key]
    
    # 处理无匹配的key
    if df2_group.empty:
        merged_list.extend(df1_group.assign(**{col: None for col in df2.columns if col != 'key_column'}))
        continue
    
    # 提取当前key对应的TF-IDF向量
    df1_vec = vectorizer.transform(df1_group['names'].str.lower())
    df2_vec = vectorizer.transform(df2_group['names'].str.lower())
    
    # 计算余弦相似度矩阵
    sim_matrix = cosine_similarity(df1_vec, df2_vec)
    
    # 筛选符合阈值的配对
    for idx, df1_row in df1_group.iterrows():
        group_idx = idx - df1_group.index[0]
        max_sim = sim_matrix[group_idx].max()
        if max_sim >= SIMILARITY_THRESHOLD:
            matched_df2_row = df2_group.iloc[sim_matrix[group_idx].argmax()]
            merged_row = pd.concat([df1_row, matched_df2_row.drop('key_column')])
            merged_list.append(merged_row)
        else:
            merged_row = df1_row.assign(**{col: None for col in df2.columns if col != 'key_column'})
            merged_list.append(merged_row)

final_df = pd.DataFrame(merged_list).reset_index(drop=True)
print(final_df)

说明 #

• 两种方法都支持自定义阈值，可根据实际场景灵活调整
• FuzzyWuzzy计算速度快，适合短文本；TF-IDF对长文本的冗余单词过滤效果更好
• 可额外添加文本预处理步骤（如去除标点、分词）进一步提升匹配准确性

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