如何基于路径/文件名尾部部分合并两个pandas DataFrame

我有如下两个DataFrame：

import pandas as pd

df1 = pd.DataFrame({'PATH':[r'C:\FODLER\Test1.jpg',
                            r'C:\A\FODLER\Test2.jpg',
                            r'C:\A\FODLER\Test3.jpg',
                            r'C:\A\FODLER\Test4.jpg'],
                    'VALUE':[45,23,45,2]})

df2 = pd.DataFrame({'F_NAME': [r'FODLER\Test1.jpg',
                               r'FODLER\Test2.jpg',
                               r'FODLER\Test6.jpg',
                               r'FODLER\Test3.jpg',
                               r'FODLER\Test4.jpg',
                               r'FODLER\Test9.jpg'],
                    'VALUE_X': ['12', '25', '97', '33', '123', '0'],
                    'CORDS': ['1', '2', '3', '4', '5', '6']})

我希望在PATH包含F_NAME的条件下，将df2合并到df1中，得到如下结果DataFrame：

df3 = pd.DataFrame({'PATH':[r'C:\FODLER\Test1.jpg',
                            r'C:\A\FODLER\Test2.jpg',
                            r'C:\A\FODLER\Test3.jpg',
                            r'C:\A\FODLER\Test4.jpg'],
                    'F_NAME': [r'FODLER\Test1.jpg',
                               r'FODLER\Test2.jpg',
                               r'FODLER\Test3.jpg',
                               r'FODLER\Test4.jpg'],
                    'VALUE_X': ['12', '25', '33', '123'],
                    'CORDS': ['1', '2', '4', '5'],
                    'VALUE':[45,23,45,2]})

请问如何编写pandas的merge语句来实现该合并操作？

直接使用merge无法直接基于"包含"条件匹配，需要先给df1生成一个能和df2的F_NAME精确匹配的列，再执行合并：

步骤1：给df1提取匹配用的F_NAME列 #

因为F_NAME是PATH的尾部子串，我们可以用字符串匹配提取出PATH中对应F_NAME的部分：

# 生成匹配模式，匹配df2中所有F_NAME的结尾
pattern = '|'.join(df2['F_NAME'])
df1['F_NAME'] = df1['PATH'].str.extract(f'({pattern})$', expand=False)

如果df2的F_NAME数量较多，也可以用更高效的遍历匹配方式：

df1['F_NAME'] = df1['PATH'].apply(lambda x: next((fn for fn in df2['F_NAME'] if fn in x), None))

步骤2：执行左连接合并 #

用merge以F_NAME为键做左连接，保留df1的所有行：

df3 = pd.merge(df1, df2, on='F_NAME', how='left')

完整代码示例 #

import pandas as pd

df1 = pd.DataFrame({'PATH':[r'C:\FODLER\Test1.jpg',
                            r'C:\A\FODLER\Test2.jpg',
                            r'C:\A\FODLER\Test3.jpg',
                            r'C:\A\FODLER\Test4.jpg'],
                    'VALUE':[45,23,45,2]})

df2 = pd.DataFrame({'F_NAME': [r'FODLER\Test1.jpg',
                               r'FODLER\Test2.jpg',
                               r'FODLER\Test6.jpg',
                               r'FODLER\Test3.jpg',
                               r'FODLER\Test4.jpg',
                               r'FODLER\Test9.jpg'],
                    'VALUE_X': ['12', '25', '97', '33', '123', '0'],
                    'CORDS': ['1', '2', '3', '4', '5', '6']})

# 提取匹配列
df1['F_NAME'] = df1['PATH'].str.extract(f"({'|'.join(df2['F_NAME'])})$", expand=False)
# 合并
df3 = pd.merge(df1, df2, on='F_NAME', how='left')

print(df3)

运行后即可得到目标结果。

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