I have a pandas dataframe consisting of a date column and a binary column named isHoliday, which simply consists of 1 if the date is a holiday and 1 if it is not. I want to create another two columns. One, a number of days to holiday column, which calculates the number of days left to the next holiday from both the date column and isHoliday column. Two, a number of days after holiday column, which calculates the number of days after to the last holiday from both the date column and isHoliday column.
1 Answers
Since you didn't share a reroducible example, here is a way (highly inspired by @jezrael solution here) to calculate the numbers of days since/till the last/next holiday based on two columns Date and isHoliday in a dataframe composed of ten consecutives dates.
import pandas as pd
# --- CREATING A SIMPLE DATASET
df = pd.DataFrame(pd.date_range("2022", periods=10), columns=['Date'])
df["isHoliday"] = [1, 0, 0, 0, 0, 0, 0, 1, 0, 0]
# --- CALCULATING THE TIME DELTAS
holidays = df['Date'].loc[df['isHoliday'] == 1].tolist()
holidays = pd.to_datetime(holidays, dayfirst=True)
df_hd = pd.DataFrame({'date1':holidays})
out = pd.merge_asof(df, df_hd, left_on='Date', right_on='date1', direction='forward')
out = pd.merge_asof(out, df_hd, left_on='Date', right_on='date1')
out['Days since the last holiday'] = out['Date'].sub(out.pop('date1_y')).dt.days
out['Days until the next holiday'] = out.pop('date1_x').sub(out['Date']).dt.days
>>> display(out)
Related