How to look into previous three row values to Current Row in Python after applying Group by

Question

How I can get the following expected output in python

Sample Input with Expected Output

ACTUAL_EXPECTED_OUTPUT is the expected output column Column.

The scenario is for each account we need to look into IS_DEFAULT COlumn prior three observations and if 1 is there in any of the previous three observation we need to get result as 1 else 0.

Group by the account id and if needed we can use order by MONTH_SINCE_DISB and then for each account id we need to look into prior three observations if 1 is there in any of the three observations for that account id then the new column label should be marked as 1 else 0. Iteratively the same logic should be applied for all accounts_id

Answer 1

Something like this should work

#Create temp column where when first 1 found, ffill the rest to 1 for that ACCT_ID 
df['ISDEFAULT_TEMP']=df.groupby('ACCT_ID')['IS_DEFAULT'].apply(lambda x: x.replace(to_replace=0,method='ffill'))

import numpy as np

#Create condition using that new column and if the cumsum >2 for an AcctID , then true 
# (.i.e. a IS_DEFAULT=1 has been seen 2 rows ago)
cond=df.groupby('ACCT_ID')['ISDEFAULT_TEMP'].transform('cumsum')>2

#Define that new column given the condition
df['ACTUAL_EXPECTED_OUTPUT']=np.where(cond,1,0)

df.drop('ISDEFAULT_TEMP',axis=1,inplace=True)
df