How to compute avg of an index for x days before a date (if the day is not a holiday) and merge it t-CodePudding

I have a dataset in which I have Traffic indices for a location on a given date. For a given date, I want to compute the avg of all the traffic indices exactly 30 days before the given date and only consider the days in those 30 days subset if the day is not a holiday.

I want to do this computation using python. I have a screenshot below that visually represents my requirement.

Explanation of the screenshot

On April 1, 2019: 
I want to calculate the 30 Day Non-Holiday traffic Index Average,
for a given location and map it to a new column with a similar column name.

The column weekend_holiday is a boolean column that is true (1) for days that are public holidays or weekends. 
We must ignore such entries in the computation of Average Location's Traffic index.

Link to Sample Dataset:

Please suggest python pandas tricks to achieve this result.

CodePudding user response：

You can calculate a rolling mean using pandas' rolling, which accepts windows with time-based length.

The following code computes the averages you want for each row of the dataframe:

# Set date as index because it is needed if you want to do time-based rolling
df.Date = pd.to_datetime(df.Date)
df = df.set_index('Date')

# Drop weekends/holidays and then compute the average of the previous 30 days
df['DELHI'] = df.where(df.weekend_or_holiday == 0).rolling('30D').mean()['New Delhi']
df['MUMBAI'] = df.where(df.weekend_or_holiday == 0).rolling('30D').mean()['Mumbai']

# Get back Date column
df = df.reset_index()