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

1 answer

  • answered 2018-02-13 03:39 Scott Boston

    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