I have data: df
date col1 col2
0 1/16/2016 apple 20
1 2/1/2016 apple 40
2 2/2/2016 pear 60
3 3/13/2016 apple 10
4 5/4/2016 apple 50
5 6/15/2016 pear 5
With cumsum() I can get cumulative sum of the values.
But if there is no value in a certain month, the value is not repeated.
df.set_index('date', inplace=True)
df = df.groupby([df.index.month, 'col1']).sum()
df['cumsum'] = df.groupby('col1')['cumsum'].cumsum()
date col1 cumsum
Jan-16 apple 20
Feb-16 apple 60
Feb-16 pear 60
Mar-16 apple 70
May-16 apple 120
Jun-16 pear 65
But I'd like to get the following result: To repeat the cumsum of col1 values even if there is no data in that particular month.
date col1 cumsum
Jan-16 apple 20
Feb-16 apple 60
Feb-16 pear 60
Mar-16 apple 70
Mar-16 pear 60
Apr-16 apple 70
Apr-16 pear 60
May-16 apple 120
May-16 pear 60
Jun-16 apple 120
Jun-16 pear 65
Thanks in advance for your help.
Use:
#create month period column for correct ordering
df['months'] = df['date'].dt.to_period('m')
#aggregate month
df1 = df.groupby(['months', 'col1'])['col2'].sum()
#MultiIndex with all possible combinations
mux = pd.MultiIndex.from_product([pd.period_range(df['months'].min(),
df['months'].max(), freq='M'),
df['col1'].unique()], names=df1.index.names)
#add missing values with reindex reshape, cumulative sum
#forward fill missing values and reshape back
df2 = (df1.reindex(mux)
.unstack()
.cumsum()
.ffill()
.stack()
.astype(int)
.reset_index(name='cumsum')
)
print (df2)
months col1 cumsum
0 2016-01 apple 20
1 2016-02 apple 60
2 2016-02 pear 60
3 2016-03 apple 70
4 2016-03 pear 60
5 2016-04 apple 70
6 2016-04 pear 60
7 2016-05 apple 120
8 2016-05 pear 60
9 2016-06 apple 120
10 2016-06 pear 65
Last if necessary convert datetimes to custom strings:
df2['months'] = df2['months'].dt.strftime('%b-%y')
print (df2)
months col1 cumsum
0 Jan-16 apple 20
1 Feb-16 apple 60
2 Feb-16 pear 60
3 Mar-16 apple 70
4 Mar-16 pear 60
5 Apr-16 apple 70
6 Apr-16 pear 60
7 May-16 apple 120
8 May-16 pear 60
9 Jun-16 apple 120
10 Jun-16 pear 65