Rolling sum for a window of 2 days (Pandas) - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: Rolling sum for a window of 2 days (Pandas) (/thread-7956.html) |
Rolling sum for a window of 2 days (Pandas) - klllmmm - Feb-01-2018 I'm trying to calculate rolling sum for a winows of 2 days for the Income column considering client ID & Category column wise. import pandas as pd import datetime as dt table = pd.DataFrame(data = {'ClientID':[100,100,100,200,100,200,100,100,100,100], 'Date':['2017-10-01 13:11:24','2017-10-01 15:11:24','2017-10-02 09:11:24','2017-10-04 11:11:24','2017-10-04 13:11:24','2017-10-06 15:11:24','2017-10-06 13:11:24','2017-10-08 11:11:24','2017-10-08 13:11:24','2017-10-08 13:11:24'], 'Category':['A','A','A','B','A','B','A','A','A','B'], 'Income':[800,900,1000,900,1000,800,400,400,900,1000],},) table['Date'] = pd.to_datetime(table['Date'])So far i managed to calculate only a cumulative daily total of Income for the Client ID & Category grops. # Client ID & Category wise daily cumulative total of Income table['Cum_Income'] = table.groupby(by = [table['ClientID'],table['Category'],table['Date'].dt.date]) ['Income'].cumsum()Highly apreciate if someone can help me to calculate rolling sum of Income column for a window of 2 days for the Client ID & Category groups. RE: Rolling sum for a window of 2 days (Pandas) - klllmmm - Feb-02-2018 # Create a date only column table['DateOnly'] = table['Date'].dt.date# Set the created date only column as Datetime Index table1 = table.set_index(pd.DatetimeIndex(table['DateOnly']))# Create a new table that shows rolling sum of Income column for a window of 2 days for the Client ID & Category groups table2 = table1.groupby(by = [table1['ClientID'],table1['Category']]).resample("1d").sum().fillna(0).rolling(window=2, min_periods=1)['Income'].sum() |