CODEWITHSUNDEEP
pythonpandasdataframecumsum
M_S_N
M_S_N

Reputation: 2810

pandas calculate 3 months cummulative sum on monthly basis at each row

I am trying to calculate cumsum for last 3 months for each row level. So, my main data frame looks like this

ID Month Level_1
1 AUG_15 1
1 SEP_15 0
1 OCT_15 1
1 NOV_15 1
1 DEC_15 0
1 JAN_16 1
1 FEB_16 1
1 MAR_16 1
2 AUG_15 1
2 SEP_15 1
2 OCT_15 1
2 NOV_15 1
2 DEC_15 1
2 JAN_16 1
2 FEB_16 1
2 MAR_16 1

and my resultant desired output is

ID Month Level_1 Level_1_m3
1 AUG_15 1 1
1 SEP_15 0 1
1 OCT_15 1 2
1 NOV_15 1 2
1 DEC_15 0 2
1 JAN_16 1 2
1 FEB_16 1 2
1 MAR_16 1 3
2 AUG_15 1 1
2 SEP_15 1 2
2 OCT_15 1 3
2 NOV_15 1 3
2 DEC_15 1 3
2 JAN_16 1 3
2 FEB_16 1 3
2 MAR_16 1 3

so, basically the m3 columns looks at last three months from a particular and calculate cumsum. e.g. for Id 1 and month Mar_16, cumsum value is 3 as it is calculated using values of Mar_16, Feb_16 and Jan_16.

Is there are builtin method that can help achieve this in pandas?

Upvotes: 1

Views: 1236

Answers (2)

d_frEak
d_frEak

Reputation: 470

Another way is using shift, if your data is already sorted

df["Level_1_m3"]=df["Level_1"]+df["Level_1"].shift(1).fillna(0)+df["Level_1"].shift(2).fillna(0)

Upvotes: 0

M_S_N
M_S_N

Reputation: 2810

So, someone did posted an answer(got removed later) that I should explore pandas rolling method and it did the job.

Here is my current solution:-

df.groupby('ID')['Level_1'].rolling(3, min_periods=1).sum()

Upvotes: 1

Related Questions