Pandas基于关键词词频匹配，为数据行分配最高匹配得分对应的分类及子分类

需求说明 #

已通过主题建模提取客户评论对应的主题关键词，需基于Pandas实现跨数据框的分类关联，核心规则如下：

• 逐行处理目标数据框df的每条记录
• 以Topic_Keywords列为匹配基准，统计当前行与参考映射表df2每行的关键词匹配数量
• 匹配数计算得分：匹配数 * 10
• 选取得分最高的df2条目，将其主分类、子分类、匹配数、得分回填到目标行，生成指定格式的输出表

输入数据 #

目标表df #

存储贷款维度的客户评论及对应提取的主题关键词，列说明：

• Loan Number：贷款唯一编号
• Commetns：客户评论内容（保留原始拼写）
• Topic_Keywords：该评论对应的主题关键词，逗号分隔

import pandas as pd
df = pd.DataFrame([[121,'Customer Comments xxxx ttttt','loan, mortgage, payment, refinance, rate, new, time, credit, pay, current'],
[34,'Customer Comments xxxx','loan, mortgage, payment, refinance, rate, new, time, credit, pay, services'],
[356,'Customer Comments xxxx','loss, make, payment, refinance, rate, new, time, credit, pay, current'],
[908,'Customer Comments aaaaa','portal, improve, online, top, covid, web, deal, competitive, take, lost'],
[4356,'Customer Comments aaassds','portal, improve, website, top, covid, web, deal, competitive, take, care'],
[3333,'Customer Comments xxxx','communication, make, sure, process, rate, company, timely, interest, customer, know'],
[33456,'Customer Comments xxxx','communication, make, sure, process, rate, company, timely, interest, customer, lot']]
          , columns=['Loan Number','Commetns','Topic_Keywords'])

参考映射表df2 #

存储主题到业务分类的已标注映射关系，列说明：

• Dominant_Topic：主题唯一编号
• Topic_Keywords：对应主题的关键词列表，逗号分隔
• Cate：主题对应的主业务分类
• SubCategory：主题对应的子业务分类

df2 = pd.DataFrame([[0,'loan, mortgage, payment, refinance, rate, new, time, credit, pay, current','Servicing','Refinance'],
[5,'closing, survey, time, notary, company, date, title, day, close, cost','Origination','Loan closing'],
[9,'service, customer, keep, good, work, excellent, great, continue, job, company','Servicing','good service'],
[6,'loan, phone, call, process, person, email, contact, time, processor, communication','Servicing','phone call process'],
[4, 'loan, helpful, processor, officer, professional, staff, knowledgeable, hire, work, process','Servicing','Staff/Agent behaviour'],
[3, 'process, easy, nothing, refinance, entire, whole, experience, time, everything, start','Origination','OnBoarding'],
[8, 'great, experience, everything, job, overall, company, nothing, work, mortgage, everyone','Servicing','good service'],
[1, 'portal, improve, online, top, covid, web, deal, competitive, take, care','Servicing','websites'],
[2, 'communication, make, sure, process, rate, company, timely, interest, customer, know',  'Origination','OnBoarding'],
[7, 'process, anything, website, app, change, think, easy, thing, use, mobile', 'Servicing','websites']]
,columns=['Dominant_Topic','Topic_Keywords','Cate','SubCategory'])

预期输出 #

生成的outdf共7列，结构如下：

• 保留df原有的3列内容
• 新增Category（对应df2的Cate字段）、subCategory（对应df2的SubCategory字段）
• 新增String_match（关键词匹配数量）、match_score（匹配数*10的得分）

outdf=pd.DataFrame([[121,'Customer Comments xxxx ttttt','loan, mortgage, payment, refinance, rate, new, time, credit, pay, current','Servicing','Refinance',10,100],
[34,'Customer Comments xxxx','loan, mortgage, payment, refinance, rate, new, time, credit, pay, services','Servicing','Refinance',9,90],
[356,'Customer Comments xxxx','loss, make, payment, refinance, rate, new, time, credit, pay, current','Servicing','Refinance',8,80],
[908,'Customer Comments aaaaa','portal, improve, online, top, covid, web, deal, competitive, take, lost','Servicing','websites',9,90],
[4356,'Customer Comments aaassds','portal, improve, website, top, covid, web, deal, competitive, take, care','Servicing','websites',10,100],
[3333,'Customer Comments xxxx','communication, make, sure, process, rate, company, timely, interest, customer, know','Origination','OnBoarding',9,90],
[33456,'Customer Comments xxxx','communication, make, sure, process, rate, company, timely, interest, customer, lot','Origination','OnBoarding',9,90]],
columns=['Loan Number','Commetns','Topic_Keywords','Category','subCategory','String_match','match_score'])

实现代码 #

步骤1：预处理关键词为集合 #

将两个表的Topic_Keywords字段拆分为集合，简化交集计算：

# 预处理目标表关键词
df['keyword_set'] = df['Topic_Keywords'].apply(lambda x: set([i.strip() for i in x.split(',')]))
# 预处理映射表关键词
df2['keyword_set'] = df2['Topic_Keywords'].apply(lambda x: set([i.strip() for i in x.split(',')]))

步骤2：逐行匹配最优分类 #

遍历目标表每一行，匹配映射表中最优分类并组装结果：

res = []
for _, row in df.iterrows():
    # 计算当前行与映射表所有行的关键词匹配数
    df2['match_num'] = df2['keyword_set'].apply(lambda k: len(row['keyword_set'] & k))
    # 取匹配数最高的映射条目
    best_match = df2.sort_values('match_num', ascending=False).iloc[0]
    # 组装单行结果
    res.append({
        'Loan Number': row['Loan Number'],
        'Commetns': row['Commetns'],
        'Topic_Keywords': row['Topic_Keywords'],
        'Category': best_match['Cate'],
        'subCategory': best_match['SubCategory'],
        'String_match': best_match['match_num'],
        'match_score': best_match['match_num'] * 10
    })
# 生成最终输出表
outdf = pd.DataFrame(res)

大数据量优化版本 #

若目标表行数超过10万，可使用预加载列表的方式减少DataFrame重复运算开销：

# 预加载映射表的关键词与分类信息
df2_kw_list = df2['keyword_set'].tolist()
df2_cate_list = df2[['Cate', 'SubCategory']].values.tolist()
res = []
for kw_set in df['keyword_set']:
    # 批量计算所有匹配数
    match_nums = [len(kw_set & k) for k in df2_kw_list]
    best_idx = match_nums.index(max(match_nums))
    # 拼接结果
    res.append([*df2_cate_list[best_idx], match_nums[best_idx], match_nums[best_idx] * 10])
# 合并原有数据与匹配结果
outdf = pd.concat([
    df.drop('keyword_set', axis=1),
    pd.DataFrame(res, columns=['Category','subCategory','String_match','match_score'])
], axis=1)

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