Cumulative Sum based on a Trigger

user_19837026 0
Viewed 41

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:

Index Value Signal
0 3 0
1 8 0
2 8 0
3 7 1
4 9 0
5 10 0
6 14 1
7 10 0
8 10 0
9 4 1
10 10 0
11 10 0

What would be a way to do it?

Expected Output:

Index Value Signal Cumsum_1 Cumsum_2 Cumsum_3
0 3 0 0 0 0
1 8 0 0 0 0
2 8 0 0 0 0
3 7 1 7 0 0
4 9 0 16 0 0
5 10 0 26 0 0
6 14 1 40 14 0
7 10 0 50 24 0
8 10 0 60 34 0
9 4 1 64 38 4
10 10 0 74 48 14
11 10 0 84 58 24
cumsum pandas iteration python
1 Answers

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
user_163434640
Related