Rolling sum for a window of 2 days (Pandas)
 Rolling sum for a window of 2 days (Pandas) klllmmm Wafer-Thin Wafer Posts: 75 Threads: 28 Joined: Oct 2016 Reputation: 1 Feb-01-2018, 12:27 AM (This post was last modified: Feb-01-2018, 12:27 AM by klllmmm.) 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. Reply klllmmm Wafer-Thin Wafer Posts: 75 Threads: 28 Joined: Oct 2016 Reputation: 1 Feb-02-2018, 03:24 PM (This post was last modified: Feb-02-2018, 03:24 PM by klllmmm.) # 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()` Reply

 Possibly Related Threads… Thread Author Replies Views Last Post Calculating Beta over Rolling Periods illmattic 2 282 Sep-27-2020, 11:27 PM Last Post: Larz60+ Apply rolling window function over time dimension of 3D data Staph 0 583 Jan-01-2020, 08:31 AM Last Post: Staph Grouping data based on rolling conditions kapilan15 0 578 Jun-05-2019, 01:07 PM Last Post: kapilan15 Pandas .rolling() with some calculations inside irmscher 5 2,177 Apr-04-2019, 11:55 AM Last Post: scidam How to use pandas.rolling mean for 3D input array? Prv_Yadv 1 1,246 Mar-26-2019, 11:49 AM Last Post: scidam Numpy Rolling mean window Thunberd 1 2,541 Jun-14-2018, 01:37 AM Last Post: Larz60+ Creating a matrix of rolling variances vvvcvvcv 1 1,041 May-26-2018, 12:51 PM Last Post: killerrex

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020