[FIXED] how to get value 50 rows before condition is true

Issue

I am using this code , all other things are right but at the end condition is not true but still giving flag 1

df=pd.DataFrame({'A':[1,8.5,5.2,7,8,9,0,4,5,6],'B':[1,2,2,2,3.1,3.2,3,2,1,2]})
df['flag']=np.where((df['B']>3).shift(-2),1,0)

here are result at the end B not greater than 3 but still giving flag 1

   A    B  flag
0  1.0  1.0     0
1  8.5  2.0     0
2  5.2  2.0     1
3  7.0  2.0     1
4  8.0  3.1     0
5  9.0  3.2     0
6  0.0  3.0     0
7  4.0  2.0     0
8  5.0  1.0     1
9  6.0  2.0     1

desired output

   A    B  flag
0  1.0  1.0     0
1  8.5  2.0     0
2  5.2  2.0     1
3  7.0  2.0     1
4  8.0  3.1     0
5  9.0  3.2     0
6  0.0  3.0     0
7  4.0  2.0     0
8  5.0  1.0     0
9  6.0  2.0     0

Solution

try:

check if the value is greater then 3 after shifting :

df['flag']=np.where((df['B'].shift(-2).gt(3)),1,0)

OR

If you want to include shifted rows as well in your condition then use fillna():

df['flag']=np.where((df['B'].shift(-2).fillna(df['B'].iloc[-2:]).gt(3)),1,0)

Answered By - Anurag Dabas