Problem: Lets suppose that we supply robots to a factory. Each of these robots is programmed to switch into the work mode after 3 days (e.g. if it arrives on day 1, it starts working on day 3), and then they work for 5 days. after that, the battery runs out and they stop working. The number of robots supplied each day varies. The following code is the supplies for the first 15 days like so:
import pandas as pd
df = pd.DataFrame({
'date': ['01','02', '03', '04', '05','06', \
'07','08','09','10', '11', '12', '13', '14', '15'],
'value': [10,20,20,30,20,10,30,20,10,20,30,40,20,20,20]
})
df.set_index('date',inplace=True)
df
Let's now estimate the number of working robots on each of these days like so ( we move two days back and sum up only the numbers within the past 5 days):
04 10
05 20+10 = 30
06 20+20 = 40
07 30+20 = 50
08 20+30 = 50
09 10+20 = 30
10 30+10 = 40
11 20+30 = 50
12 10+20 = 30
13 20+10 = 30
14 30+20 = 50
15 40+30 = 70
Is it possible to model this in python? I have tried this - not quite but close.
df_p = (((df.rolling(2)).sum())).shift(5).rolling(1).mean().shift(-3)
p.s. if you dont think its complicated enough then I also need to include the last 7-day average for each of these numbers for my real problem.
Let's try shift forward first the window (5) less the rolling window length (2) and taking rolling sum with min periods set to 1:
shift_window = 5
rolling_window = 2
df['new_col'] = (
df['value'].shift(shift_window - rolling_window)
.rolling(rolling_window, min_periods=1).sum()
)
Or with hard coded values:
df['new_col'] = df['value'].shift(3).rolling(2, min_periods=1).sum()
df:
value new_col
date
01 10 NaN
02 20 NaN
03 20 NaN
04 30 10.0
05 20 30.0
06 10 40.0
07 30 50.0
08 20 50.0
09 10 30.0
10 20 40.0
11 30 50.0
12 40 30.0
13 20 30.0
14 20 50.0
15 20 70.0