根据工作日和节假日删除/保留行。Python 3.8/Spyder/W10

Question

在数据框中，我希望删除所有日期不在本月最后一个星期五的行

但如果周五（假期）没有数据，则保留前一天的行，即周四

如果星期五和星期四都是假日（无数据），则保留星期三，依此类推

基本上，如果星期五是假日，那就把一天追溯到星期四，如果星期四是假日，那就追溯到星期三，依此类推

数据：

[['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Turnover', 'weekday', 'weekday_name'], ['30-10-2020', 0.5, 0.4, 0.89, 0.21, 0.09, 0.82, 4, 'Friday'], ['31-10-2020', 0.49, 0.58, 0.24, 0.81, 0.76, 0.86, 5, 'Saturday'], ['02-11-2020', 0.21, 0.31, 0.92, 0.7, 0.07, 0.45, 0, 'Monday'], ['03-11-2020', 0.2, 0.09, 0.25, 0.58, 0.61, 0.36, 1, 'Tuesday'], ['04-11-2020', 0.93, 0.61, 0.89, 0.31, 0.01, 0.04, 2, 'Wednesday'], ['05-11-2020', 0.14, 0.52, 0.79, 0.44, 0.46, 0.15, 3, 'Thursday'], ['18-11-2020', 0.75, 0.36, 0.41, 0.73, 0.48, 0.83, 2, 'Wednesday'], ['19-11-2020', 0.08, 0.86, 0.56, 0.13, 0.15, 0.28, 3, 'Thursday'], ['20-11-2020', 0.99, 0.23, 0.47, 0.87, 0.51, 0.59, 4, 'Friday'], ['23-11-2020', 0.84, 0.67, 0.83, 0.3, 0.34, 0.98, 0, 'Monday'], ['24-11-2020', 0.46, 0.49, 0.1, 0.36, 0.07, 0.6, 1, 'Tuesday'], ['29-11-2020', 0.45, 0.45, 0.98, 0.93, 0.73, 0.08, 6, 'Sunday'], ['01-12-2020', 0.17, 0.89, 0.63, 0.05, 0.04, 0.57, 1, 'Tuesday'], ['02-12-2020', 0.17, 0.22, 0.71, 0.72, 0.13, 0.69, 2, 'Wednesday'], ['03-12-2020', 0.45, 0.69, 0.69, 0.41, 0.39, 0.1, 3, 'Thursday'], ['17-12-2020', 0.15, 0.99, 0.58, 0.42, 0.97, 0.74, 3, 'Thursday'], ['18-12-2020', 0.22, 0.89, 0.76, 0.25, 0.96, 0.56, 4, 'Friday'], ['21-12-2020', 0.92, 0.98, 0.1, 0.22, 0.01, 0.51, 0, 'Monday'], ['22-12-2020', 0.65, 0.42, 0.7, 0.81, 0.92, 0.64, 1, 'Tuesday'], ['23-12-2020', 0.8, 0.46, 0.91, 0.38, 0.15, 0.12, 2, 'Wednesday'], ['24-12-2020', 0.71, 0.77, 0.42, 0.42, 0.49, 0.49, 3, 'Thursday'], ['28-12-2020', 0.6, 0.79, 0.72, 0.0, 0.01, 0.54, 0, 'Monday'], ['29-12-2020', 0.1, 0.71, 0.06, 0.28, 0.43, 0.55, 1, 'Tuesday'], ['30-12-2020', 0.35, 0.53, 0.75, 0.64, 0.46, 0.27, 2, 'Wednesday'], ['31-12-2020', 0.58, 0.67, 0.4, 0.81, 0.34, 0.84, 3, 'Thursday'], ['11-01-2021', 0.31, 0.02, 0.67, 0.19, 0.8, 0.36, 0, 'Monday'], ['13-01-2021', 0.12, 0.22, 0.76, 0.03, 0.52, 0.54, 2, 'Wednesday'], ['15-01-2021', 0.95, 0.62, 0.41, 0.35, 0.76, 0.23, 4, 'Friday'], ['18-01-2021', 0.89, 0.03, 0.63, 0.6, 0.89, 0.89, 0, 'Monday'], ['19-01-2021', 0.01, 0.37, 0.26, 0.85, 0.31, 0.41, 1, 'Tuesday'], ['21-01-2021', 0.47, 0.03, 0.84, 0.7, 0.94, 0.31, 3, 'Thursday'], ['22-01-2021', 0.36, 0.41, 0.14, 0.82, 0.55, 0.78, 4, 'Friday'], ['25-01-2021', 0.42, 0.76, 0.0, 0.33, 0.22, 0.54, 0, 'Monday'], ['27-01-2021', 0.68, 0.93, 0.85, 0.74, 0.27, 0.99, 2, 'Wednesday'], ['30-01-2021', 0.07, 0.2, 0.8, 0.42, 0.75, 0.71, 5, 'Saturday'], ['01-02-2021', 0.19, 0.24, 0.78, 0.19, 0.55, 0.03, 0, 'Monday'], ['02-02-2021', 0.32, 0.49, 0.1, 0.29, 0.07, 0.67, 1, 'Tuesday'], ['04-02-2021', 0.6, 0.16, 0.28, 0.22, 0.43, 0.81, 3, 'Thursday'], ['05-02-2021', 0.78, 0.9, 0.72, 0.88, 0.6, 0.65, 4, 'Friday'], ['15-02-2021', 0.65, 0.98, 0.58, 0.14, 0.91, 0.88, 0, 'Monday'], ['17-02-2021', 0.82, 0.89, 0.45, 0.71, 0.7, 0.18, 2, 'Wednesday'], ['18-02-2021', 0.99, 0.79, 0.67, 0.52, 0.95, 0.01, 3, 'Thursday'], ['19-02-2021', 0.95, 0.6, 0.18, 0.01, 0.72, 0.72, 4, 'Friday'], ['22-02-2021', 0.73, 0.21, 0.88, 0.86, 0.54, 0.69, 0, 'Monday'], ['24-02-2021', 0.7, 0.38, 0.39, 0.52, 0.51, 0.13, 2, 'Wednesday'], ['25-02-2021', 0.17, 0.66, 0.73, 0.01, 0.38, 0.95, 3, 'Thursday'], ['26-02-2021', 0.03, 0.62, 0.48, 0.85, 0.36, 0.06, 4, 'Friday'], ['01-03-2021', 0.13, 0.38, 0.09, 0.37, 0.49, 0.83, 0, 'Monday'], ['02-03-2021', 0.07, 0.26, 0.44, 0.18, 0.88, 0.66, 1, 'Tuesday'], ['03-03-2021', 0.05, 0.0, 0.19, 0.59, 0.01, 0.4, 2, 'Wednesday'], ['10-03-2021', 0.27, 0.71, 0.24, 0.29, 0.16, 0.64, 2, 'Wednesday'], ['15-03-2021', 0.09, 0.1, 0.38, 0.54, 0.42, 0.38, 0, 'Monday'], ['16-03-2021', 0.87, 0.13, 0.54, 0.49, 0.49, 0.83, 1, 'Tuesday'], ['17-03-2021', 0.95, 0.33, 0.66, 0.97, 0.11, 0.91, 2, 'Wednesday'], ['18-03-2021', 0.26, 0.57, 0.52, 0.94, 0.44, 0.3, 3, 'Thursday'], ['23-03-2021', 0.76, 0.83, 0.04, 0.43, 0.44, 0.98, 1, 'Tuesday'], ['24-03-2021', 0.51, 0.79, 0.18, 0.26, 0.98, 0.27, 2, 'Wednesday'], ['25-03-2021', 0.3, 0.12, 0.17, 0.76, 0.14, 0.63, 3, 'Thursday'], ['30-03-2021', 0.77, 0.42, 0.1, 0.87, 0.46, 0.82, 1, 'Tuesday'], ['31-03-2021', 0.76, 0.16, 0.0, 0.38, 0.07, 0.69, 2, 'Wednesday'], ['01-04-2021', 0.43, 0.78, 0.09, 0.91, 0.21, 0.29, 3, 'Thursday'], ['05-04-2021', 0.63, 0.48, 0.61, 0.27, 0.33, 0.93, 0, 'Monday'], ['13-04-2021', 0.07, 0.94, 0.89, 0.8, 0.79, 0.68, 1, 'Tuesday'], ['20-04-2021', 0.54, 0.61, 0.11, 0.01, 0.74, 0.96, 1, 'Tuesday'], ['23-04-2021', 0.8, 0.56, 0.97, 0.68, 0.13, 0.06, 4, 'Friday'], ['26-04-2021', 0.64, 0.02, 0.45, 0.87, 0.08, 0.75, 0, 'Monday'], ['27-04-2021', 0.04, 0.91, 0.42, 0.61, 0.38, 0.95, 1, 'Tuesday'], ['28-04-2021', 0.61, 0.32, 0.43, 0.37, 0.53, 0.24, 2, 'Wednesday'], ['29-04-2021', 0.44, 0.26, 0.65, 0.39, 0.44, 0.9, 3, 'Thursday'], ['30-04-2021', 0.18, 0.89, 0.2, 0.33, 0.99, 0.15, 4, 'Friday']]

Answer 1

使用假日，您可以递归地调整以达到所需的日期。它应该是有效的，因为它是矢量化的

import pandas as pd
import numpy as np
from pandas.tseries.holiday import USFederalHolidayCalendar

df = pd.DataFrame({"date":pd.date_range("1-jan-2020", "31-dec-2021")})

# select holidays you want...
holidays = USFederalHolidayCalendar().holidays(start=df["date"].min(), end=df["date"].max())

# get last Friday of every month
df = df.loc[df["date"].dt.dayofweek.eq(4)].groupby([df["date"].dt.month, df["date"].dt.year], as_index=False).max()

# use recursion to adjust dates if instance of a holiday or weekend
def adjusthol(df, holidays):
    if df["date"].isin(holidays).any() or df["date"].dt.dayofweek.isin([5,6]).any():
        df["date"] = np.where(df["date"].isin(holidays), df["date"] - pd.Timedelta(days=1), df["date"])
        df = adjusthol(df, holidays)
    return df


adjusthol(df, holidays)

输出

      date
2020-01-31
2021-01-29
2020-02-28
2021-02-26
2020-03-27
2021-03-26
2020-04-24
2021-04-30
2020-05-29
2021-05-28
2020-06-26
2021-06-25
2020-07-31
2021-07-30
2020-08-28
2021-08-27
2020-09-25
2021-09-24
2020-10-30
2021-10-29
2020-11-27
2021-11-26
2020-12-24
2021-12-30