Change column values in Pandas DataFrame with cumsum that resets yearly back to daily/monthly values

Viewed 26

I have dataframe df1 shown below. Its a cumulative sum which resets every year.

values  values1 values2
date            
2010-01-01  1   1   4
2010-02-01  3   2   8
2010-03-01  3   3   10
2010-04-01  5   4   13
2010-05-01  9   5   14
2010-06-01  12  5   15
2010-07-01  15  7   17
2010-08-01  16  11  18
2010-09-01  16  13  20
2010-10-01  18  13  20
2010-11-01  22  13  21
2010-12-01  26  15  21
2011-01-01  2   0   0
2011-02-01  5   2   4
2011-03-01  6   4   4
2011-04-01  6   6   6
2011-05-01  10  10  8
2011-06-01  13  10  9
2011-07-01  14  13  9
2011-08-01  15  16  11
2011-09-01  17  19  13
2011-10-01  17  19  16
2011-11-01  20  22  16
2011-12-01  22  23  19

I am trying to get dataframe df2 as follows, which is just the monthly data without any cumsum whatsover.

    test    test1   test2
date            
2010-01-01  1   1   4
2010-02-01  2   1   4
2010-03-01  0   1   2
2010-04-01  2   1   3
2010-05-01  4   1   1
2010-06-01  3   0   1
2010-07-01  3   2   2
2010-08-01  1   4   1
2010-09-01  0   2   2
2010-10-01  2   0   0
2010-11-01  4   0   1
2010-12-01  4   2   0
2011-01-01  2   0   0
2011-02-01  3   2   4
2011-03-01  1   2   0
2011-04-01  0   2   2
2011-05-01  4   4   2
2011-06-01  3   0   1
2011-07-01  1   3   0
2011-08-01  1   3   2
2011-09-01  2   3   2
2011-10-01  0   0   3
2011-11-01  3   3   0
2011-12-01  2   1   3

The following is the code to generate df1 and df2. I'm trying to reverse the groupby and cumsum process. I followed the groupby and cumsum shown here Cumulative monthly sum with reset to zero at the beginning of each new month in pandas

import pandas as pd
from datetime import datetime, timedelta

date_today = datetime.now()
ndays=pd.date_range(start='2010', end='2012', freq='M').shape[0]
test_df = pd.DataFrame(
    {'date': pd.date_range(start='2010', end='2012', freq='M').strftime('%Y-%m'), 
    'test': pd.Series(np.random.randint(5,size=ndays)),
    'test1': pd.Series(np.random.randint(5,size=ndays)),
    'test2': pd.Series(np.random.randint(5,size=ndays))
    }
)
test_df = test_df.set_index('date')
test_df.index = pd.to_datetime(test_df.index)
test_df[['values','values1','values2']] = test_df.groupby(test_df.index.year)[['test','test1','test2']].cumsum()

df1 = pd.DataFrame(test_df[['values','values1','values2']])
df2 = pd.DataFrame(test_df[['test','test1','test2']])
1 Answers

Use diff after resample:

df.resample('Y')[['values', 'values1', 'values2']].\
         diff().fillna(df[['values', 'values1', 'values2']])
Related