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']])