cumsum() by month but repeat the values if there is no data in that month

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.

1 answer

  • answered 2019-02-10 12:42 jezrael

    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