基于多子串匹配规则实现两个Pandas DataFrame的合并

实现代码 #

完整可运行代码如下：

import pandas as pd

# 构造示例数据
substring_ref = pd.DataFrame({
    "keyword_1": ["Amazon Web Service -", "Virtual Apps"],
    "keyword_2": ["", "Service"],
    "type": ["IaaS", "PaaS"]
})

sales = pd.DataFrame({
    "product_name": ["Amazon Web Service - Automation", "Web", "Citrix Virtual Apps Premium OnPrem", "Citrix Virtual Apps Premium Service"],
    "Value": [10, 90, 500, 340]
})

# 处理逻辑
# 1. 生成笛卡尔积获取两个表所有行的组合
substring_ref['tmp_key'] = 1
sales['tmp_key'] = 1
all_comb = substring_ref.merge(sales, on='tmp_key').drop('tmp_key', axis=1)

# 2. 按规则过滤匹配成功的行
match_mask = (
    # keyword_2为空时仅需包含keyword_1即可匹配
    ((all_comb['keyword_2'] == '') & all_comb.apply(lambda row: row['keyword_1'] in row['product_name'], axis=1))
    |
    # keyword_2不为空时需要同时包含两个关键词
    ((all_comb['keyword_2'] != '') & all_comb.apply(lambda row: row['keyword_1'] in row['product_name'] and row['keyword_2'] in row['product_name'], axis=1))
)
matched_rows = all_comb[match_mask]

# 3. 合并所有sales数据，未匹配行的关联字段填空
final_result = sales.drop('tmp_key', axis=1).merge(matched_rows, on=['product_name', 'Value'], how='left').fillna('')

# 打印结果
print(final_result)

输出结果 #

运行后得到的final_result和预期完全一致：

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