Sum of column values based on a condition in pandas

Since pandas 1.1 you can create a forward rolling window and select the rows you want to include in your dataframe. With different arguments my notebook kernel got terminated: use with caution.

indexer = pd.api.indexers.FixedForwardWindowIndexer(window_size=5)
df['total'] = df.daychange.rolling(indexer, min_periods=1).sum()[df.SS == 100]
df

Out:

   daychange   SS     total
0   0.017065    0       NaN
1  -0.009259  100  0.023432
2   0.031542    0       NaN
3  -0.004530    0       NaN
4   0.000709    0       NaN
5   0.004970  100 -0.013319
6  -0.021900    0       NaN
7   0.003611    0       NaN

Exclude the starting row with SS == 100 from the sum

That would be the next row after rows with SS == 100. As all rows are computed you can use

df['total'] = df.daychange.rolling(indexer, min_periods=1).sum().shift(-1)[df.SS == 100]
df

Out:

   daychange   SS     total
0   0.017065    0       NaN
1  -0.009259  100  0.010791
2   0.031542    0       NaN
3  -0.004530    0       NaN
4   0.000709    0       NaN
5   0.004970  100 -0.018289
6  -0.021900    0       NaN
7   0.003611    0       NaN

---

Slow hacky solution using indices of selected rows

This feels like a hack, but works and avoids computing unnecessary rolling values

df['next5sum'] = df[df.SS == 100].index.to_series().apply(lambda x: df.daychange.iloc[x: x + 5].sum())
df

Out:

   daychange   SS  next5sum
0   0.017065    0       NaN
1  -0.009259  100  0.023432
2   0.031542    0       NaN
3  -0.004530    0       NaN
4   0.000709    0       NaN
5   0.004970  100 -0.013319
6  -0.021900    0       NaN
7   0.003611    0       NaN

For the sum of the next five rows excluding the rows with SS == 100 you can adjust the slices or shift the series

df['next5sum'] = df[df.SS == 100].index.to_series().apply(lambda x: df.daychange.iloc[x + 1: x + 6].sum())
# df['next5sum'] = df[df.SS == 100].index.to_series().apply(lambda x: df.daychange.shift(-1).iloc[x: x + 5].sum())

df

Out:

   daychange   SS  next5sum
0   0.017065    0       NaN
1  -0.009259  100  0.010791
2   0.031542    0       NaN
3  -0.004530    0       NaN
4   0.000709    0       NaN
5   0.004970  100 -0.018289
6  -0.021900    0       NaN
7   0.003611    0       NaN
7   0.003611    0       NaN