如何判断DF2列表是否为DF1列表子集并为DF1新增MATCH列

需求说明 #

当DF2中LIST_2列表是DF1中LIST_1列表的子集时，为DF1（或新建DataFrame）新增MATCH列，存储所有匹配到的DF2的ID列表，支持多个匹配结果。

示例数据 #

DF1 #

NAME   LIST_1
BILL   ['ZF1', 'ZF2', 'ZF3', 'ZF9', 'ZF11']
PAUL   ['ZF1', 'ZF4', 'ZF5', 'ZF2', 'ZF3']
JOHN   ['ZF1', 'ZF2', 'ZF5', 'ZF6']

DF2 #

ID     LIST_2
ZB1    ['ZF1', 'ZF2', 'ZF3']
ZB2    ['ZF1', 'ZF4', 'ZF5']
ZB3    ['ZF2', 'ZF5', 'ZF6']

实现代码 #

1. 导入库并构建示例数据 #

import pandas as pd

# 构建DF1
df1 = pd.DataFrame({
    'NAME': ['BILL', 'PAUL', 'JOHN'],
    'LIST_1': [
        ['ZF1', 'ZF2', 'ZF3', 'ZF9', 'ZF11'],
        ['ZF1', 'ZF4', 'ZF5', 'ZF2', 'ZF3'],
        ['ZF1', 'ZF2', 'ZF5', 'ZF6']
    ]
})

# 构建DF2
df2 = pd.DataFrame({
    'ID': ['ZB1', 'ZB2', 'ZB3'],
    'LIST_2': [
        ['ZF1', 'ZF2', 'ZF3'],
        ['ZF1', 'ZF4', 'ZF5'],
        ['ZF2', 'ZF5', 'ZF6']
    ]
})

2. 预处理DF2（优化子集判断效率） #

将LIST_2转换为集合，利用集合的issubset方法快速判断子集关系：

# 为DF2添加集合列，后续用于子集判断
df2['SET_2'] = df2['LIST_2'].apply(set)
# 提前打包集合与ID对，避免重复索引DataFrame
df2_pairs = list(zip(df2['SET_2'], df2['ID']))

3. 生成MATCH列 #

定义函数遍历DF1每行，筛选出符合条件的DF2 ID：

def get_matching_ids(row):
    set1 = set(row['LIST_1'])
    # 筛选所有LIST_2是LIST_1子集的ID
    return [id for s, id in df2_pairs if s.issubset(set1)]

# 为DF1新增MATCH列
df1['MATCH'] = df1.apply(get_matching_ids, axis=1)

运行结果 #

NAME                                LIST_1       MATCH
0  BILL  ['ZF1', 'ZF2', 'ZF3', 'ZF9', 'ZF11']      ['ZB1']
1  PAUL   ['ZF1', 'ZF4', 'ZF5', 'ZF2', 'ZF3']  ['ZB1', 'ZB2']
2  JOHN        ['ZF1', 'ZF2', 'ZF5', 'ZF6']      ['ZB3']

补充说明 #

• 集合的issubset方法比遍历列表元素判断更高效，尤其适合数据量较大的场景
• 如果不需要保留DF2的原始结构，可以直接用df2_pairs完成判断，无需新增SET_2列

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