[FIXED] How to keep pandas dataframe rows where column values appear in a given sequence?

Issue

I have a dataframe like this:

I only want to keep rows highlighted green where students status change from Fail to Pass in any 2 consecutive months. How can i drop the rows highlighted red with this condition? I can`t figure out how to do this in pandas.

In the second step where i will have a dataframe like this:

I only want to count passes which are preceded by a fail in previous month (as in red text). So like get unique count of passed students each month if they failed in the previous month? I am confused how to go about this approach using pandas.

Solution

I guess you would need to work with 'shift' to compare subsequent values

df['FailToPass'] = ((df == 'Pass') & ((df.shift(axis=1)) == 'Fail')).any(axis=1)

Answered By - Daniel Weigel