如何在Pandas中基于包含关系合并两个DataFrame？

构造示例数据 #

先还原你的两个DataFrame：

import pandas as pd

# 构造df_1
df_1 = pd.DataFrame({
    "Nº.do Incidente": ["IN6948271", "IN6948304", "IN6948307", "IN6948309", "IN6948310", "IN6948311"],
    "Status": ["ENCERRADO"]*6,
    "Description": [
        "GR26 D.I.T.I. >>> ABEND NO JOB PP_SASG_GD9822...",
        "GR26 D.I.T.I. >>> ABEND NO JOB PP_AACE_R4539 ...",
        "GR26 D.I.T.I. >>> ABEND NO JOB PP_ADAT_SPRK_EX...",
        "GR26 D.I.T.I. >>> ABEND NO JOB PP_ADAT_SPRK_EX...",
        "GR26 D.I.T.I. >>> ABEND NO JOB PP_ADAT_SPRK_EX...",
        "GR26 D.I.T.I. >>> ABEND NO JOB PP_ADAT_SPRK_EX..."
    ],
    "Per_Extracao": ["DE : 2022/01/05 ATÉ : 2022/12/08"]*6
})

# 构造df_2
df_2 = pd.DataFrame({
    "JOB_NAME": [
        "PP_AACD_NR_D8706_TIHIBRIDA_PROC_EXCUC_D",
        "PP_SASG_GD9822",
        "PP_AACE_R4539",
        "PP_AACE_R4539_CONS_JUNC",
        "PP_AACE_R4539_FMRC_TD_01",
        "PP_AACE_R4539_FMRC_TD_02"
    ],
    "JOB_STREAM_NAME": [
        "P26_AACD_FAC_TOD",
        "P26_AACE_U08",
        "P26_AACE_U09",
        "P26_AACE_U08",
        "P26_AACE_U08",
        "P26_AACE_U08"
    ]
})

解决方案 #

方法一：遍历匹配关联 #

这种方法适用于无固定格式的JOB_NAME，只要JOB_NAME完整出现在Description中即可匹配：

# 为每个JOB_NAME找到对应的df_1行索引
matches = df_2.apply(
    lambda row: df_1[df_1["Description"].str.contains(row["JOB_NAME"], na=False)].index,
    axis=1
)

# 展开匹配关系，生成关联表
match_df = pd.DataFrame({
    "df1_index": [idx for sublist in matches for idx in sublist],
    "df2_index": [i for i, sublist in enumerate(matches) for _ in sublist]
})

# 合并得到结果
merged_df = df_1.merge(
    match_df, left_index=True, right_on="df1_index"
).merge(
    df_2, left_on="df2_index", right_index=True
).drop(columns=["df1_index", "df2_index"]).reset_index(drop=True)

方法二：正则提取后精确合并 #

如果你的JOB_NAME有固定格式（比如都是PP_开头，后续无空格），可以用正则从Description中提取出JOB_NAME，再进行精确合并，效率更高：

# 从Description中提取JOB_NAME（匹配PP_开头直到非空白字符的内容）
df_1["extracted_job"] = df_1["Description"].str.extract(r"(PP_\S+)")

# 和df_2精确合并
merged_df = df_1.merge(df_2, left_on="extracted_job", right_on="JOB_NAME", how="inner").drop(columns=["extracted_job"]).reset_index(drop=True)

结果展示 #

运行代码后得到的merged_df如下：

Nº.do Incidente     Status                                        Description                          Per_Extracao       JOB_NAME JOB_STREAM_NAME
0       IN6948271  ENCERRADO  GR26 D.I.T.I. >>> ABEND NO JOB PP_SASG_GD9822...  DE : 2022/01/05 ATÉ : 2022/12/08  PP_SASG_GD9822     P26_AACE_U08
1       IN6948304  ENCERRADO  GR26 D.I.T.I. >>> ABEND NO JOB PP_AACE_R4539 ...  DE : 2022/01/05 ATÉ : 2022/12/08   PP_AACE_R4539     P26_AACE_U09

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