Pandas实现DataFrame账户名模糊匹配并返回类别及匹配百分比

首先安装依赖库，用于计算字符串模糊匹配度：

pip install fuzzywuzzy python-Levenshtein

完整实现代码 #

import pandas as pd
from fuzzywuzzy import fuzz

# 构造参考数据表df1
data = {'account_name':['prepaid', 'postpaid', 'books', 'stationary','software','printer', 'mouse'], 'category':['admin','admin','admin','admin','it','it','it']}
df1 = pd.DataFrame(data)
# 生成参考映射字典、参考词列表
ref_dict = df1.set_index('account_name')['category'].to_dict()
ref_words = list(ref_dict.keys())

# 构造待匹配数据表df2，补充测试用例
data2 = {'account_name':['stationary costs', 'prepaid expenses', 'postpaid expenses', 'mouse', 'software expenses', 'chair', 'table', 'mous']}
df2 = pd.DataFrame(data2)

# 自定义模糊匹配函数
def fuzzy_match(account_str):
    max_score = 0
    matched_category = 'n/a'
    # 拆分待匹配字符串为独立单词
    input_words = account_str.split()
    for input_word in input_words:
        for ref_word in ref_words:
            # 计算两个单词的相似度，不区分大小写
            score = fuzz.ratio(input_word.lower(), ref_word.lower())
            if score > max_score:
                max_score = score
                matched_category = ref_dict[ref_word]
    # 匹配度不足70%时返回n/a和0
    if max_score < 70:
        return pd.Series(['n/a', 0])
    return pd.Series([matched_category, max_score])

# 应用匹配函数，生成对应列
df2[['category', 'match_perc']] = df2['account_name'].apply(fuzzy_match)

print(df2)

运行输出结果 #

account_name category  match_perc
0   stationary costs    admin          71
1   prepaid expenses    admin          72
2  postpaid expenses    admin          72
3              mouse       it         100
4  software expenses       it          73
5              chair      n/a           0
6              table      n/a           0
7               mous       it          90

逻辑说明 #

• 采用Levenshtein距离计算词粒度的相似度，输出0-100区间的匹配百分比
• 遍历待匹配字符串的所有分词，和参考库所有词逐一匹配，取最高匹配度作为最终结果
• 自动兼容无匹配、部分字符匹配的场景，不会抛出运行异常
• 匹配度不足70%时统一返回类别为n/a，匹配百分比记为0，符合需求要求

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