Faster approach to calculate consecutive number of rows in pandas dataframe, where value is lower than in current row while iterating

The answer provided by @benjwadams on the Q that you link to actually works on your problem. It could be optimized, however, as we will see below.

Here's the solution by @benjwadams. I leave out original comments for brevity, but they are well worth the read to grasp the logic):

import pandas as pd
from operator import gt, lt

def consecutive_run(op, ser, i):
    thresh_all = op(ser[:i], ser[i])
    non_passing = thresh_all[~thresh_all]
    start_idx = 0
    if not non_passing.empty:
        start_idx = non_passing.index[-1]
    return thresh_all[start_idx:].sum()

Applied to your case:

df_old = pd.DataFrame({'Spam': [10, 1, 2, 3, 4, 5, 6, 8, 3, 4, 7, 8, 9, 1, 2, 7, 11]})
s = df_old.Spam

# original design is to capture both `gt` and `lt`, the OP needs `lt` here.
res = s.index.to_series().map(lambda i: consecutive_run(lt, s, i))

print(res.to_numpy().tolist())
[0, 0, 1, 2, 3, 4, 5, 6, 0, 1, 2, 3, 11, 0, 1, 2, 16] # correct

So, this works, but we can (potentially) improve performance by first cutting out all the index values where we can easily check that the needed value is going to be zero. That way we reduce the number of index values that need to go into the lambda function. So:

def wrapper(s):
    # each cell where the diff with previous cell <= 0 should get 0 always,
    # so we cut out the associated index values, and only feed the rest
    # to the `map(lamda i: ...)`
    idx = s[s.diff().ge(0)].index # cutting [0, 1, 8, 13] in OP's case
    t = s.iloc[idx].index.to_series().map(lambda i: consecutive_run(lt, s, i))
    
    # afterwards, we reindex on the original index, and apply `fillna(0)`,
    # i.e. replacing all `NaN`s for index values that were skipped with zeros
    res = t.reindex(s.index).fillna(0)
    return res

^{N.B.: if people want to use consecutive_run(gt, s, i), they should of course change s[s.diff().ge(0)].index to the le(0)-variant!}

Check for equality:

print(wrapper(s).equals(
    s.index.to_series().map(
        lambda i: consecutive_run(lt, s, i)).astype(float)))
# True

Of course, the actual performance improvement will depend on the amount of index values we were able to cut out.

Finally, I've came up with a different solution as well. It is (potentially) faster than the original version of the solution by @benjwadams, but only because it uses the same trick. Otherwise, it seems to be slightly slower. Just adding it here; maybe someone else might see a way to improve it a little more.

import numpy as np

def consecutive_run_alt(s):
    # same idea as in `wrapper()` above
    idx = s[s.diff().ge(0)].index
    
    # for each i, get `s` through to `i` and get the diff;
    # now flip the sequence (`[::-1]` or `np.flip()` as here);
    # for the flipped sequence, get `cumsum()` and ask `> 0` (`gt(0)`);
    # finally, apply `.argmin()` to get the length of the first sequence of
    # `True`'s. 
    t = s.iloc[idx].index.to_series().map(
        lambda i: np.flip(s.iloc[:i+1].diff()).cumsum().gt(0).argmin())
    
    # same idea as in `wrapper()` above
    res = t.reindex(s.index).fillna(0)
    return res

# equality check:
print(wrapper(s).equals(consecutive_run_alt(s)))
# True

Here's a small performance comparison:

df_old = pd.DataFrame({'Spam': [10, 1, 2, 3, 4, 5, 6, 8, 3, 4, 7, 8, 9, 1, 2, 7, 11]})
s = pd.Series(np.tile(df_old.Spam,1000))

print('consecutive_run:', end=' ')
%timeit s.index.to_series().map(lambda i: consecutive_run(lt, s, i))
print('wrapper:', end=' ')
%timeit wrapper(s)
print('consecutive_run_alt:', end=' ')
%timeit consecutive_run_alt(s)

consecutive_run: 5.26 s ± 279 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
wrapper: 4.05 s ± 67.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
consecutive_run_alt: 4.3 s ± 180 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)