I am trying to track cumulative sums of the 'Value' column that should begin every time I get 1 in the 'Signal' column.

So in the table below I need to obtain 3 cumulative sums starting at values 3, 6, and 9 of the index, and each sum ending at value 11 of the index:

What would be a way to do it?

Expected Output:

CodePudding user response：

You can pivot, bfill, then cumsum:

df.merge(df.assign(id=df['Signal'].cumsum().add(1))
           .pivot(index='Index', columns='id', values='Value')
           .bfill(axis=1).fillna(0, downcast='infer')
           .cumsum()
           .add_prefix('cumsum'),
         left_on='Index', right_index=True
         )

output:

    Index  Value  Signal  cumsum1  cumsum2  cumsum3  cumsum4
0       0      3       0        3        0        0        0
1       1      8       0       11        0        0        0
2       2      8       0       19        0        0        0
3       3      7       1       26        7        0        0
4       4      9       0       35       16        0        0
5       5     10       0       45       26        0        0
6       6     14       1       59       40       14        0
7       7     10       0       69       50       24        0
8       8     10       0       79       60       34        0
9       9      4       1       83       64       38        4
10     10     10       0       93       74       48       14
11     11     10       0      103       84       58       24

older answer

IIUC, you can use groupby.cumsum:

df['cumsum'] = df.groupby(df['Signal'].cumsum())['Value'].cumsum()

output:

    Index  Value  Signal  cumsum
0       0      3       0       3
1       1      8       0      11
2       2      8       0      19
3       3      7       1       7
4       4      9       0      16
5       5     10       0      26
6       6     14       1      14
7       7     10       0      24
8       8     10       0      34
9       9      4       1       4
10     10     10       0      14
11     11     10       0      24