如何基于字符串包含条件合并两个Pandas DataFrame？

场景与需求 #

现有两个Pandas DataFrame：

import pandas as pd
  
df1 = pd.DataFrame({'col_name':['12','13','14','15','16','17','18','19','20','21','22','23']})

df2 = pd.DataFrame({'col_name_aggr':['12|13|14', '10|21', '12|15|23'],
                    'color':['Blue', 'Red', 'Green']})

需求：保留df1的全部数据，新增color列——当col_name的值存在于df2的col_name_aggr（竖线分隔的字符串）中时，填入对应的color；无匹配项则为None。

对应的SQL逻辑是：

SELECT
 df1.*,
 df2.color
FROM df1
left join df2 on CHARINDEX(df1.col_name,df2.col_name_aggr)<>0

Pandas的pd.merge()只能指定列匹配，无法直接设置这种字符串包含条件，以下是几种可行的实现方法：

方法一：拆分聚合列后常规合并（推荐） #

这种方法逻辑清晰，效率较高，适合大部分场景：

1. 将df2的col_name_aggr按竖线拆分成多行，保留对应color
2. 用df1和拆分后的df2做左连接
3. 按需处理重复匹配（比如某值同时匹配多个color）

代码实现：

# 拆分df2的聚合列
df2_exploded = df2.assign(col_name=df2['col_name_aggr'].str.split('|')).explode('col_name')

# 左连接获取匹配的color
result = df1.merge(df2_exploded[['col_name', 'color']], on='col_name', how='left')

# 若需去重（每个col_name保留第一个匹配的color）
result = result.groupby('col_name').first().reset_index()

方法二：交叉连接+条件筛选 #

先生成两个DataFrame的笛卡尔积，再筛选符合包含条件的行，最后合并回df1：

# 添加临时键实现交叉连接
df1['tmp_key'] = 1
df2['tmp_key'] = 1
cross_join = df1.merge(df2, on='tmp_key', how='left')

# 判断col_name是否在col_name_aggr中
cross_join['match'] = cross_join.apply(lambda x: x['col_name'] in x['col_name_aggr'], axis=1)

# 筛选匹配行并合并回df1
matched = cross_join[cross_join['match']][['col_name', 'color']]
result = df1.drop('tmp_key', axis=1).merge(matched, on='col_name', how='left')

# 按需去重
result = result.groupby('col_name').first().reset_index()

方法三：逐行匹配（简洁但效率较低） #

对df1的每一行，在df2中查找符合条件的color：

def get_color(row):
    # 找到所有包含当前col_name的df2行，提取color
    matches = df2[df2['col_name_aggr'].str.contains(row['col_name'])]['color']
    # 有匹配则返回第一个，无匹配返回None
    return matches.iloc[0] if not matches.empty else None

df1['color'] = df1.apply(get_color, axis=1)

这种写法代码简洁，但数据量大时效率较低，因为apply是逐行处理。

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