Is there a way to find the second to last valid index in a rolling window?

user_277716 0
Viewed 287

"Second to last valid index" here simply means that we have a column of booleans, and we need to find the index of the previous before the last boolean that is True. Notice that it must happen in a rolling window and not for the entire dataframe.

This is remotely related to "Is there a way to do last_valid_index() in a rolling window?" which answers a similar question but finding the next to last instead of the last is a whole different beast.

For example:

d = {'col': [True, False, True, True, False, True, False]}

df = pd.DataFrame(data=d)

The expected outcome of a "second to last valid index" method for a rolling window of 3 is:

0    NaN
1    NaN
2    0.0
3    2.0
4    2.0
5    3.0
6    3.0

(because while the 5th index is true: index 3 is the second to last valid index)

If you're also drawing a blank see the URL above for a similar answer.

pandas dataframe numpy python
1 Answers

I think you need:

#shift Trues values and assign to new column
df['new'] = df.index.to_series()[df['col']].shift()

#get max per 3 window
df['new'] = df['new'].rolling(3, min_periods=1).max()
print (df)
     col  new
0   True  NaN
1  False  NaN
2   True  0.0
3   True  2.0
4  False  2.0
5   True  3.0
6  False  3.0
user_29010020
Related