# How to do accumulative calculation in data frame in Python?

I have a dataframe like this,

``````import pandas as pd
d = {'col1': ["2004-02-26", "2004-02-27", "2004-03-01",
"2004-03-02", "2004-03-03", "2004-03-04",
"2004-03-05", "2004-03-08", "2004-03-09",
"2004-03-10", "2004-03-11", "2004-03-12"],
'col2': [3, 4, 5, 3, 1, 11, 123, 43, 5, 3, 4, 7],
'col3': [-1, 1, -1, -1, 1, 1, 1, 1, -1, 1, 1, -1]}
df = pd.DataFrame(data=d)

col1  col2  col3
0   2004-02-26     3    -1
1   2004-02-27     4     1
2   2004-03-01     5    -1
3   2004-03-02     3    -1
4   2004-03-03     1     1
5   2004-03-04    11     1
6   2004-03-05   123     1
7   2004-03-08    43     1
8   2004-03-09     5    -1
9   2004-03-10     3     1
10  2004-03-11     4     1
11  2004-03-12     7    -1
``````

Suppose now I have 1000 cash from start. When `col3` is negative I hold my money. When it turns to positive I throw my money in and multiply the `col2` value until it meets the next `-1` I take all the money back again.

All the calculated values store in new column 'col4'. Finally the dataframe looks like,

``````       col1      col2  col3   col4
0   2004-02-26     3    -1    1000
1   2004-02-27     4     1    4000
2   2004-03-01     5    -1    4000
3   2004-03-02     3    -1    4000
4   2004-03-03     1     1    4000
5   2004-03-04    11     1    44000
6   2004-03-05   123     1    492000
7   2004-03-08    43     1    172000
8   2004-03-09     5    -1    172000
9   2004-03-10     3     1    516000
10  2004-03-11     4     1    688000
11  2004-03-12     7    -1    688000
``````

• answered 2018-02-13 03:39

Use, `cumprod` with `where` and `fillna`:

``````df['col4'] = (((df.col3.where(df.col3.gt(0))*df.col2)
.fillna(1)
.cumprod())
.astype(int).mul(1000))
``````

Output:

``````          col1  col2  col3        col4
0   2004-02-26     3    -1        1000
1   2004-02-27     4     1        4000
2   2004-03-01     5    -1        4000
3   2004-03-02     3    -1        4000
4   2004-03-03     1     1        4000
5   2004-03-04    11     1       44000
6   2004-03-05   123     1     5412000
7   2004-03-08    43     1   232716000
8   2004-03-09     5    -1   232716000
9   2004-03-10     3     1   698148000
10  2004-03-11     4     1  2792592000
11  2004-03-12     7    -1  2792592000
``````