如何在其他数据文件的基础上添加新列？

Question

你好，我有两个数据格式，但两者不一样。我得根据二号更新一个。

示例: df1：

Region  Sub_Region  Run_Date    Status   Reason
ASPAC   CRM         2022-01-11  Success
ASPAC   Genesys     2022-01-11  Failed
LATAM   CRM         2022-01-11  Success

df2：

Region   Sub_Region   Max_Load_Date
NA       CRM          2021-08-12
ASPAC    CRM          2021-03-15
LATAM    CRM          2021-10-11
NA       Genesys      2021-12-18
ASPAC    Genesys      2021-11-18
LATAM    Genesys      2021-04-16

在最后产出中：

Region  Sub_Region  Run_Date    Status   Reason  Max_Load_Date
ASPAC   CRM         2022-01-11  Success          2021-03-15
ASPAC   Genesys     2022-01-11  Failed
LATAM   CRM         2022-01-11  Success          2021-10-11

当状态=“成功”时，df1将更新

Answer 1

在merge之后过滤掉数据

df1['Max_Load_Date'] = df1.merge(df2, on=['Region', 'Sub_Region'], how='left') \
                          .query("Status == 'Success'")['Max_Load_Date']
print(df)

# Output
  Region Sub_Region    Run_Date   Status  Reason Max_Load_Date
0  ASPAC        CRM  2022-01-11  Success     NaN    2021-03-15
1  ASPAC    Genesys  2022-01-11   Failed     NaN           NaN
2  LATAM        CRM  2022-01-11  Success     NaN    2021-10-11