python-3.x - cumsum() 按月计算，但如果该月没有数据，则重复这些值

Question

我有数据：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

有了cumsum()我可以获得值的累积总和。但如果某个月份没有值，则该值不会重复。

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

但我想得到以下结果：col1即使在那个特定月份没有数据，也要重复值的累积和。

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

在此先感谢您的帮助。

标签： python-3.xpandaspandas-groupbycumsum

Answer 1

利用：

#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

如有必要，最后将日期时间转换为自定义字符串：

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