Check if status changed in comparison to yesterday if yes display it in a new Dataframe

Question

Every day I run my code and its test if it was successful or fail. I put it in a database and the database looks like this:

         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

Now I want to display the only rows where it failed in comparison to yesterday

What I have now when I run my code is this output:

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

I've tried to find the duplicate and save it in a new Dataframe

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

This is the output I want:

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

Answer 1

Try first filter by boolean indexing:

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

Or by DataFrame.query:

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

Thank you @piRSquared for suggestion -

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'")

EDIT:

If need another condition for test last value of 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

Or:

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

If need condition for test today datetime:

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

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

Or:

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