get cumsum data base on week data for each day in pandas?-CodePudding

Goal

I want to get cumsum of vol based on week data like below, I refer this [post][1] but it returns dataframe based on week not on day.

df:

df = pd.DataFrame({'ts_code': ['A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B'],
 'date': ['2021/1/11',  '2021/1/12',  '2021/1/13',  '2021/1/14',  '2021/1/17',  '2021/1/18',
  '2021/1/11',  '2021/1/12',  '2021/1/13',  '2021/1/14',  '2021/1/17',  '2021/1/18'],
 'vol': [10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10]})

which looks like:

  ts_code   date    vol
    A   2021/1/11   10
    A   2021/1/12   10
    A   2021/1/13   10
    A   2021/1/14   10
    A   2021/1/17   10
    A   2021/1/18   10
    B   2021/1/11   10
    B   2021/1/12   10
    B   2021/1/13   10
    B   2021/1/14   10
    B   2021/1/17   10
    B   2021/1/18   10

Expected

    ts_code date    vol week_vol
    A   2021/1/11   10  10
    A   2021/1/12   10  20
    A   2021/1/13   10  30
    A   2021/1/14   10  40
    A   2021/1/17   10  10
    A   2021/1/18   10  20
    B   2021/1/11   10  10
    B   2021/1/12   10  20
    B   2021/1/13   10  30
    B   2021/1/14   10  40
    B   2021/1/17   10  10
    B   2021/1/18   10  20

notice

the dates is trade dates, which ranges between Monday to Friday.It starts from Money at least. [1]: Pandas Group by date weekly

CodePudding user response：

You can create a groupby object on 'ts_code' column and pd.Grouper(key='date', freq='W-SAT')] and use the cumsum method (used freq='W_SAT' because the weekdays in the sample data seemed to start on Saturdays):

df['week_vol'] = df.groupby(['ts_code', pd.Grouper(key='date', freq='W-SAT')]).cumsum()

Output:

   ts_code       date  vol  week_vol
0        A 2021-01-11   10        10
1        A 2021-01-12   10        20
2        A 2021-01-13   10        30
3        A 2021-01-14   10        40
4        A 2021-01-17   10        10
5        A 2021-01-18   10        20
6        B 2021-01-11   10        10
7        B 2021-01-12   10        20
8        B 2021-01-13   10        30
9        B 2021-01-14   10        40
10       B 2021-01-17   10        10
11       B 2021-01-18   10        20