Pandas - cumsum by month?

Question

I have a dataframe that looks like this:

Date          n
2014-02-27    4
2014-02-28    5
2014-03-01    1
2014-03-02    6
2014-03-03    7

I'm trying to get to one that looks like this

Date          n    csn
2014-02-27    4    4
2014-02-28    5    9
2014-03-01    1    1
2014-03-02    6    7
2014-03-03    7    14

...i.e. I want a column with the running total within the month and I want it to start over each month. How can I do this?

Answer 1

Use .groupby(), but don't just group by month, groupby year-month instead. Or else 2013-02 will be in the same group as 2014-02, etc.

In [96]:

df['Month']=df['Date'].apply(lambda x: x[:7])
In [97]:

df['csn']=df.groupby(['Month'])['n'].cumsum()
In [98]:

print df
         Date  n    Month  csn
0  2014-02-27  4  2014-02    4
1  2014-02-28  5  2014-02    9
2  2014-03-01  1  2014-03    1
3  2014-03-02  6  2014-03    7
4  2014-03-03  7  2014-03   14

[5 rows x 4 columns]