如何基于另一Pandas DataFrame的日期范围为df1分配周数？

需要为df1添加Week #列，依据df2中定义的日期范围，将每个WorkDate匹配到对应的周数。

原始数据 #

df1 #

import pandas as pd

df1 = pd.DataFrame({
    'WorkDate': ['2022-05-03', '2022-05-16', '2022-05-24']
})
# 转换为日期类型
df1['WorkDate'] = pd.to_datetime(df1['WorkDate'])

df2 #

df2 = pd.DataFrame({
    'Week #': [1,2,3,4,5],
    'Week Start': ['2022-05-01', '2022-05-08', '2022-05-15', '2022-05-22', '2022-05-29'],
    'Week End': ['2022-05-07', '2022-05-14', '2022-05-21', '2022-05-28', '2022-06-04']
})
# 转换为日期类型
df2['Week Start'] = pd.to_datetime(df2['Week Start'])
df2['Week End'] = pd.to_datetime(df2['Week End'])

解决方案 #

方法1：使用merge_asof（高效推荐） #

merge_asof适合按顺序匹配区间的场景，需先对df2按起始日期排序：

# 按Week Start排序df2
df2_sorted = df2.sort_values('Week Start')

# 执行asof合并
final_df = pd.merge_asof(df1.sort_values('WorkDate'), 
                         df2_sorted, 
                         left_on='WorkDate', 
                         right_on='Week Start',
                         direction='backward')

# 过滤并整理列
final_df = final_df[['WorkDate', 'Week #']].sort_values('WorkDate').reset_index(drop=True)

方法2：使用apply逐行判断 #

适合小数据量场景，直接判断日期所属区间：

def get_week_number(date):
    mask = (df2['Week Start'] <= date) & (df2['Week End'] >= date)
    return df2.loc[mask, 'Week #'].values[0]

df1['Week #'] = df1['WorkDate'].apply(get_week_number)
final_df = df1

方法3：使用pd.cut区间切割 #

将日期转为数值后，用区间规则匹配周数：

# 提取完整区间边界
bins = pd.concat([df2['Week Start'], df2['Week End'].iloc[-1:]]).sort_values()
labels = df2['Week #']

# 转换日期为数值并执行切割
df1['Week #'] = pd.cut(df1['WorkDate'].astype('int64'), 
                       bins=bins.astype('int64'), 
                       labels=labels, 
                       include_lowest=True)

final_df = df1

预期结果 #

print(final_df)
# 输出：
#    WorkDate  Week #
# 0 2022-05-03       1
# 1 2022-05-16       3
# 2 2022-05-24       4

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