检查状态是否与昨天相比发生了变化，如果是，则在新的数据框中显示它

Question

每天我都运行我的代码和它的测试，看它是成功的还是失败的。 我把它放在一个数据库里，数据库看起来是这样的：

         Date      Name Color
0  2019-07-18  Project1  blue
1  2019-07-18  Project2  blue
2  2019-07-18  Project3   red
3  2019-07-19  Project1  blue
4  2019-07-19  Project2   red
5  2019-07-19  Project3   red

现在我想显示与昨天相比它失败的唯一行

我现在运行代码时得到的是以下输出：

         Date      Name Color
4  2019-07-19  Project2   red
5  2019-07-19  Project3   red

我试着找到重复的，并保存在一个新的数据帧

dfnewfails = df.drop_duplicates(subset='Name', keep='last').query("Color == 'red'")

这是我想要的输出：

print(dfnewfails)
>>> 0    2019-07-19    Project2    red

Answer 1

您还可以执行以下操作：

df.drop_duplicates(subset='Name',keep='last').loc[df['Status'] == 'Fail']

Answer 2

尝试按^{}进行第一次筛选：

df[df['Status'].eq('FAIL')].drop_duplicates(subset='Name',keep='last')

或通过^{}：

df.query("Status == 'FAIL'").drop_duplicates(subset='Name', keep='last')

谢谢@piRSquared的建议-

If there was a prior failure for a project but a subsequent success, Querying first would return the failure rather than nothing.

df.drop_duplicates(subset='Name', keep='last').query("Status == 'FAIL'")

编辑：

如果需要另一个条件来测试Date的最后一个值：

last = df['Date'].iloc[-1]
df1 = df.query("Color == 'red' & Date == @last")
print (df1)
         Date      Name Color
4  2019-07-19  Project2   red
5  2019-07-19  Project3   red

或：

last = df['Date'].iloc[-1]
df1 = df[df['Color'].eq('red') & df['Date'].eq(df['Date'].iloc[-1])]
print (df1)
         Date      Name Color
4  2019-07-19  Project2   red
5  2019-07-19  Project3   red

如果今天需要测试条件日期时间：

df['Date'] = pd.to_datetime(df['Date'])
today = pd.Timestamp.today().floor('d')

df1 = df.query("Color == 'red' & Date == @today")

或：

df1 = df[df['Color'].eq('red') & df['Date'].eq(today)]
print (df)
        Date      Name Color
4 2019-07-19  Project2   red
5 2019-07-19  Project3   red

Answer 3

我也找到了一种新的方法。你知道吗

print(df)
>>>         
       Date      Name   Color
0  2019-07-18  Project1  blue
1  2019-07-18  Project2  blue
2  2019-07-18  Project3   red
3  2019-07-19  Project1  blue
4  2019-07-19  Project2   red
5  2019-07-19  Project3   red

首先确定日期：

today = datetime.now()
yesterday = datetime.today() - timedelta(days=1)
Date = today.strftime("%Y-%m-%d")
yesterdayDate = yesterday.strftime("%Y-%m-%d")

然后查询：

dfred = df.query("Color == 'red'")
dftoday = dfred.query("Date == @Date")
dfyesterday = dfred.query("Date == @yesterdayDate")
dfcombined = pd.concat([dfyesterday,dftoday])
dfnewfails = dfcombined.drop_duplicates(subset='Name', keep=False).query("Date == @Date")

结果

print(dfnewfails)
>>>
         Date      Name Color
4  2019-07-19  Project2   red