如何基于多值键合并Pandas DataFrame并按规则聚合列？

问题说明 #

需要将两个DataFrame按指定键做左连接，其中df1的List列包含分号分隔的多值，要与df2的Cod列匹配，最终将匹配到的Animal、Letter按List内的多值顺序用|拼接，未匹配项用-填充。

原始数据 #

df1代码：

import pandas as pd

df1 = pd.DataFrame({
    "List": ["P111", "P999", "P111;P999;P777", "P555", "P666;P111;P333"],
    "Color": ["red", "red", "blue","yellow", "red"]
})

df2代码：

df2 = pd.DataFrame({
    "Cod": ["P111", "P222", "P333", "P444", "P555", "P666", "P777"],
    "Animal": ["DOG", "CAT", "BUG","SNAKE,DOG", "CAT,BUG", "DOG", "SNAKE"],
    "Letter": ["A,F", "C", "S,M", "F,L", "C,A","M,C", "Z,L"]
})

期望结果 #

List           Color     Animal             Letter
0  P111              red      DOG,FROG           A,F
1  P999              red      -                  -
2  P111;P999;P777    blue     DOG,FROG|-|SNAKE   A,F|-|Z,L
3  P555              yellow   CAT,BUG            C,A 
4  P666;P111;P333    red      DOG|DOG,FROG|BUG   M,C|A,F|S,M

（注：期望结果中P111对应的Animal为DOG,FROG，与df2中P111的DOG不符，以下代码按通用逻辑实现，若需特定值可自行调整df2数据）

实现步骤与代码 #

步骤1：拆分多值列并保留原始索引 #

把df1的List列按分号拆分，同时保留原来的行索引，方便后续分组聚合：

# 拆分List列，展开为单行单值，保留原索引
df1_expanded = df1.assign(List=df1['List'].str.split(';')).explode('List').reset_index()

步骤2：左连接匹配数据 #

将展开后的df1_expanded与df2按List（对应df2的Cod）做左连接：

# 左连接，匹配Cod列
merged = pd.merge(df1_expanded, df2, left_on='List', right_on='Cod', how='left')

步骤3：填充未匹配的空值 #

用-替换Animal和Letter列的空值：

# 填充空值为'-'
merged[['Animal', 'Letter']] = merged[['Animal', 'Letter']].fillna('-')

步骤4：按原始索引分组聚合 #

按原始行索引分组，将Animal和Letter按顺序用|拼接，同时恢复原List和Color列：

# 分组聚合，拼接结果
result = merged.groupby('index').agg(
    List=('List', lambda x: ';'.join(x)),
    Color=('Color', 'first'),
    Animal=('Animal', '|'.join),
    Letter=('Letter', '|'.join)
).reset_index(drop=True)

完整代码 #

import pandas as pd

# 初始化原始数据
df1 = pd.DataFrame({
    "List": ["P111", "P999", "P111;P999;P777", "P555", "P666;P111;P333"],
    "Color": ["red", "red", "blue","yellow", "red"]
})

df2 = pd.DataFrame({
    "Cod": ["P111", "P222", "P333", "P444", "P555", "P666", "P777"],
    "Animal": ["DOG", "CAT", "BUG","SNAKE,DOG", "CAT,BUG", "DOG", "SNAKE"],
    "Letter": ["A,F", "C", "S,M", "F,L", "C,A","M,C", "Z,L"]
})

# 拆分多值列并保留原索引
df1_expanded = df1.assign(List=df1['List'].str.split(';')).explode('List').reset_index()

# 左连接匹配
merged = pd.merge(df1_expanded, df2, left_on='List', right_on='Cod', how='left')

# 填充空值
merged[['Animal', 'Letter']] = merged[['Animal', 'Letter']].fillna('-')

# 分组聚合得到最终结果
result = merged.groupby('index').agg(
    List=('List', lambda x: ';'.join(x)),
    Color=('Color', 'first'),
    Animal=('Animal', '|'.join),
    Letter=('Letter', '|'.join)
).reset_index(drop=True)

print(result)

输出结果 #

运行后得到的结果（对应df2原始数据）：

List   Color               Animal               Letter
0              P111     red                  DOG                 A,F
1              P999     red                    -                    -
2  P111;P999;P777    blue        DOG|-|SNAKE        A,F|-|Z,L
3              P555  yellow              CAT,BUG              C,A
4  P666;P111;P333    red        DOG|DOG|BUG        M,C|A,F|S,M

若要得到期望结果中的DOG,FROG，只需修改df2中P111对应的Animal值即可。

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