基于日期列表更新Pandas DataFrame的business_days列

需要针对银行节假日列表中有但Rocket节假日列表没有的每个节假日，为DataFrame的business_days列添加0.5个工作日。PredictionTargetDateEOM为当月最后一天，同一月份的business_days数值完全相同。

输入DataFrame（predicted_df） #

PredictionTargetDateEOM business_days
0       2022-06-30      22
1       2022-06-30      22
2       2022-06-30      22
3       2022-06-30      22
4       2022-06-30      22
        ... ... ...
172422  2022-11-30      21
172423  2022-11-30      21
172424  2022-11-30      21
172425  2022-11-30      21
172426  2022-11-30      21

节假日列表 #

Rocket节假日列表（含调休） #

rocket_holiday = ["New Year's Day", "Martin Luther King Jr. Day", "Memorial Day", "Independence Day",
                 "Labor Day", "Thanksgiving", "Christmas Day"]
rocket_holiday_including_observed = rocket_holiday + [item + ' (Observed)' for item in rocket_holiday]
print(rocket_holiday_including_observed)
# 输出：
["New Year's Day",
 'Martin Luther King Jr. Day',
 'Memorial Day',
 'Independence Day',
 'Labor Day',
 'Thanksgiving',
 'Christmas Day',
 "New Year's Day (Observed)",
 'Martin Luther King Jr. Day (Observed)',
 'Memorial Day (Observed)',
 'Independence Day (Observed)',
 'Labor Day (Observed)',
 'Thanksgiving (Observed)',
 'Christmas Day (Observed)']

美国银行节假日列表（2022年） #

由holidays库生成，原始字典格式如下：

import holidays
banker_hols_dict = holidays.US(years=2022)
# 输出字典：
{datetime.date(2022, 1, 1): "New Year's Day", 
 datetime.date(2022, 1, 17): 'Martin Luther King Jr. Day', 
 datetime.date(2022, 2, 21): "Washington's Birthday", 
 datetime.date(2022, 5, 30): 'Memorial Day', 
 datetime.date(2022, 6, 19): 'Juneteenth National Independence Day', 
 datetime.date(2022, 6, 20): 'Juneteenth National Independence Day (Observed)', 
 datetime.date(2022, 7, 4): 'Independence Day', 
 datetime.date(2022, 9, 5): 'Labor Day', 
 datetime.date(2022, 10, 10): 'Columbus Day', 
 datetime.date(2022, 11, 11): 'Veterans Day', 
 datetime.date(2022, 11, 24): 'Thanksgiving', 
 datetime.date(2022, 12, 25): 'Christmas Day', 
 datetime.date(2022, 12, 26): 'Christmas Day (Observed)'}

# 提取节假日名称列表：
banker_hols = [i for i in banker_hols_dict.values()]
print(banker_hols)
# 输出：
["New Year's Day",
 'Martin Luther King Jr. Day',
 "Washington's Birthday",
 'Memorial Day',
 'Juneteenth National Independence Day',
 'Juneteenth National Independence Day (Observed)',
 'Independence Day',
 'Labor Day',
 'Columbus Day',
 'Veterans Day',
 'Thanksgiving',
 'Christmas Day',
 'Christmas Day (Observed)']

期望输出 #

对于银行独有的节假日（如6月的Juneteenth、11月的Veterans Day）所在月份，所有行的business_days加0.5：

PredictionTargetDateEOM business_days
0       2022-06-30      22.5
1       2022-06-30      22.5
2       2022-06-30      22.5
3       2022-06-30      22.5
4       2022-06-30      22.5
        ... ... ...
172422  2022-11-30      21.5
172423  2022-11-30      21.5
172424  2022-11-30      21.5
172425  2022-11-30      21.5
172426  2022-11-30      21.5

尝试过的代码 #

已筛选出银行独有的节假日及其对应日期，但未完成DataFrame列的更新：

main_list = list(set(banker_hols) - set(rocket_holiday_including_observed))
print(main_list)
# 输出：
['Columbus Day',
 'Juneteenth National Independence Day',
 "Washington's Birthday",
 'Juneteenth National Independence Day (Observed)',
 'Veterans Day']

result = []
for key, value in holidays.US(years = 2022).items():
    if value in main_list:
        result.append(key)
print(result)
# 输出：
[datetime.date(2022, 2, 21),
 datetime.date(2022, 6, 19),
 datetime.date(2022, 6, 20),
 datetime.date(2022, 10, 10),
 datetime.date(2022, 11, 11)]

解决方案 #

使用Pandas的.loc()方法定位目标月份并更新business_days列：

# 1. 筛选银行独有的节假日名称
banker_hols = [i for i in holidays.US(years = 2022).values()]
hol_diffs = list(set(banker_hols) - set(rocket_holiday_including_observed))

# 2. 获取这些节假日的日期
dates_of_hols = []
for key, value in holidays.US(years = 2022).items():
    if value in hol_diffs:
        dates_of_hols.append(key)

# 3. 提取对应的月份（去重）
months = list(set([item.month for item in dates_of_hols]))

# 4. 为目标月份的business_days加0.5
predicted_df.loc[predicted_df['PredictionTargetDateEOM'].dt.month.isin(months), 'business_days'] += 0.5

内容的提问来源于stack exchange，提问作者Hefe