How can I fill a column with values that are computed between two dates in pandas, with a delay of one row, respecting certain conditions?

I prefer to add a temporary column to calculate the winning probability.

In my environment, the following code works fine.

Code

import pandas as pd
from pandas import Timestamp

# create a sample dataframe (a markdown table is shown below as input dataframe)
df = pd.DataFrame({'Date': {0: Timestamp('2017-09-08 00:00:00'), 1: Timestamp('2017-09-05 00:00:00'), 2: Timestamp('2017-09-04 00:00:00'), 3: Timestamp('2017-09-15 00:00:00'), 4: Timestamp('2017-09-05 00:00:00'), 5: Timestamp('2017-09-11 00:00:00'), 6: Timestamp('2017-09-03 00:00:00'), 7: Timestamp('2017-09-03 00:00:00'), 8: Timestamp('2017-09-07 00:00:00'), 9: Timestamp('2017-09-07 00:00:00'), 10: Timestamp('2017-09-10 00:00:00'), 11: Timestamp('2017-09-14 00:00:00'), 12: Timestamp('2017-09-09 00:00:00'), 13: Timestamp('2017-09-11 00:00:00'), 14: Timestamp('2017-09-12 00:00:00'), 15: Timestamp('2017-09-01 00:00:00'), 16: Timestamp('2017-09-16 00:00:00'), 17: Timestamp('2017-09-15 00:00:00'), 18: Timestamp('2017-09-03 00:00:00'), 19: Timestamp('2017-09-02 00:00:00'), 20: Timestamp('2017-09-06 00:00:00')}, 'Distance': {0: 1000, 1: 1600, 2: 1000, 3: 1600, 4: 1200, 5: 1600, 6: 1600, 7: 1000, 8: 1200, 9: 1000, 10: 1600, 11: 1600, 12: 1000, 13: 1000, 14: 1600, 15: 1200, 16: 1000, 17: 1200, 18: 1200, 19: 1600, 20: 1200}, 'Position': {0: 1, 1: 1, 2: 1, 3: 2, 4: 3, 5: 1, 6: 4, 7: 2, 8: 1, 9: 2, 10: 9, 11: 9, 12: 1, 13: 1, 14: 9, 15: 10, 16: 2, 17: 2, 18: 3, 19: 9, 20: 13}, 'TrainerID': {0: 6529, 1: 1234, 2: 1234, 3: 6529, 4: 1234, 5: 1234, 6: 6529, 7: 6529, 8: 1234, 9: 9999, 10: 6529, 11: 9999, 12: 9999, 13: 9999, 14: 1234, 15: 9999, 16: 1234, 17: 9999, 18: 6529, 19: 9999, 20: 6529}})

# ensure that rows of the same Trainer ID stack in a single block, not seperately.
df = df.sort_values(['TrainerID', 'Date'], kind='stable')

# determine a window size for time series
no_days = '1000D'

# create a mask
mask = (df.Distance == 1000) | (df.Distance == 1200)

# add a temporary column for calculation
df = df.assign(Position_for_calc=df.loc[mask, 'Position'])

# use Date as index
df = df.set_index('Date')

# define calculation of win
calc = lambda s: round(100*s.eq(1).sum()/s.notnull().sum())

# obtain pd.series of calculated winning probability
ser_win = df.groupby('TrainerID').rolling(no_days)['Position_for_calc'].apply(calc).groupby('TrainerID').shift().fillna(0).values

# add Win column and drop the temporary column (a markdown table is shown below as output dataframe)
df = df.assign(Win=ser_win).drop('Position_for_calc', axis=1)

If you want to squeeze everything into a few lines,

df = df.sort_values(['TrainerID', 'Date'], kind='stable')
df = df.assign(Win=df.assign(Position_for_calc=df.loc[(df.Distance == 1000) | (df.Distance == 1200), 'Position']).set_index('Date').groupby('TrainerID').rolling('1000D')['Position_for_calc'].apply(lambda s: round(100*s.eq(1).sum()/s.notnull().sum())).groupby('TrainerID').shift().fillna(0).values)

Python Version

3.9.10

Pandas Version

1.3.5

Input Dataframe

Output Dataframe