python - 在交叉评估 2 个数据集时根据条件应用列中的值

Question

我有 2 个 DataFrame:

项目1

  key   name   deadline     delivered
0 AA1   Tom    01/05/2018   02/05/2018
1 AA2   Sue    01/05/2018   30/04/2018
2 AA4   Jack   01/05/2018   04/05/2018

项目2

  key   name   deadline     delivered
0 AA1   Tom    01/05/2018   30/04/2018
1 AA2   Sue    01/05/2018   30/04/2018
2 AA3   Jim    01/05/2018   03/05/2018

可以在 PROJECT2 中创建一个名为'In PROJECT1'的列，并应用如下条件:

伪代码

for row in PROJECT2: 
    if in the same row based on key column PROJECT1['delivered'] >= PROJECT2['deadline']:
        PROJECT2['In PROJECT1'] = 'project delivered before deadline'
    else: 
        'Project delayed'

预期结果

  key   name   deadline     delivered    In PROJECT1
0 AA1   Tom    01/05/2018   30/04/2018   Project delayed
1 AA2   Sue    01/05/2018   30/04/2018   project delivered before deadline
2 AA3   Jim    01/05/2018   03/05/2018   NaN

不确定如何处理它(iterrows()、for循环、df.loc[条件]、np.where()，或者也许我需要定义某种在 df.apply() 中使用的函数)，非常感谢任何帮助。

Answer 1

您可以使用numpy.select添加包含条件和值列表的系列。

请注意，我相信您的期望标准相反，即在截止日期之前交付应该给出“项目在截止日期之前交付”，而不是反之亦然。

import numpy as np

# convert series to datetime if necessary
for col in ['deadline', 'delivered']:
    df1[col] = pd.to_datetime(df1[col], dayfirst=True)

for col in ['deadline', 'delivered']:
    df2[col] = pd.to_datetime(df2[col], dayfirst=True)

# create series mapping key to delivered date in df1
s = df1.set_index('key')['delivered']

# define conditions and values
conditions = [~df2['key'].isin(s.index), df2['key'].map(s) <= df2['deadline']]
values = [np.nan, 'project delivered before deadline']

# apply conditions and values, with fallback value
df2['In Project1'] = np.select(conditions, values, 'Project delayed')

print(df2)

   key name   deadline  delivered                        In Project1
0  AA1  Tom 2018-05-01 2018-04-30                    Project delayed
1  AA2  Sue 2018-05-01 2018-04-30  project delivered before deadline
2  AA3  Jim 2018-05-01 2018-05-03                                nan