如何基于两侧最近可用日期生成重叠周窗口？

需求说明 #

核心需求是：传入YEAR/WEEK参数时，返回对应的日期窗口。具体示例如下：

• 调用some_window_function(2022, 5)，返回结果：

DATE
YEAR WEEK                             
2020 30          Friday, July 24, 2020
2022 5     Wednesday, February 2, 2022
     5      Thursday, February 3, 2022
     5        Friday, February 4, 2022
     7      Tuesday, February 15, 2022

• 调用some_window_function(2022, 7)，返回结果：

DATE
YEAR WEEK                                
2022 5         Friday, February 4, 2022
2022 7       Tuesday, February 15, 2022
     7     Wednesday, February 16, 2022
     7      Thursday, February 17, 2022
2023 44       Tuesday, October 31, 2023

原始数据 #

使用的DataFrame如下：

import pandas as pd

df = pd.DataFrame({'YEAR': [2020, 2020, 2020, 2020, 2020, 2020, 2020, 2022, 2022, 2022, 2022, 2022, 2022, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023], 'WEEK': [29, 29, 29, 30, 30, 30, 30, 5, 5, 5, 7, 7, 7, 44, 44, 44, 44, 45, 45, 45, 46, 46, 46, 46], 'DATE': ['Monday, July 13, 2020', 'Thursday, July 16, 2020', 'Friday, July 17, 2020', 'Monday, July 20, 2020', 'Tuesday, July 21, 2020', 'Thursday, July 23, 2020', 'Friday, July 24, 2020', 'Wednesday, February 2, 2022', 'Thursday, February 3, 2022', 'Friday, February 4, 2022', 'Tuesday, February 15, 2022', 'Wednesday, February 16, 2022', 'Thursday, February 17, 2022', 'Tuesday, October 31, 2023', 'Wednesday, November 02, 2023', 'Friday, November 03, 2023', 'Sunday, November 05, 2023', 'Monday, November 06, 2023', 'Tuesday, November 07, 2023', 'Wednesday, November 08, 2023', 'Monday, November 13, 2023', 'Tuesday, November 14, 2023', 'Wednesday, November 15, 2023', 'Thursday, November 16, 2023']})

问题分析 #

你之前的代码通过按YEAR分组处理，但逻辑没有准确匹配需求：既没有定位到目标YEAR/WEEK的前后边界，也未正确筛选出对应窗口的日期数据。

解决方案 #

我们可以先将DATE列转换为datetime类型，按YEAR和WEEK分组后定位目标组，再收集目标组的所有数据、前一组的最后一条数据、后一组的第一条数据，合并后返回结果。

实现代码如下：

def some_window_function(target_year, target_week):
    # 将DATE转为datetime类型，方便排序和筛选
    df['DATE'] = pd.to_datetime(df['DATE'])
    
    # 按YEAR和WEEK分组，记录每组的首尾日期及所有日期
    grouped = df.groupby(['YEAR', 'WEEK']).agg(
        first_date=('DATE', 'min'),
        last_date=('DATE', 'max')
    ).reset_index()
    
    # 找到目标组的索引位置
    target_idx = grouped[(grouped['YEAR'] == target_year) & (grouped['WEEK'] == target_week)].index[0]
    
    # 收集结果数据
    result_parts = []
    
    # 添加前一组的最后一条数据（如果存在）
    if target_idx > 0:
        prev_group = grouped.iloc[target_idx - 1]
        prev_row = df[(df['YEAR'] == prev_group['YEAR']) & (df['DATE'] == prev_group['last_date'])]
        result_parts.append(prev_row)
    
    # 添加目标组的所有数据
    target_rows = df[(df['YEAR'] == target_year) & (df['WEEK'] == target_week)]
    result_parts.append(target_rows)
    
    # 添加后一组的第一条数据（如果存在）
    if target_idx < len(grouped) - 1:
        next_group = grouped.iloc[target_idx + 1]
        next_row = df[(df['YEAR'] == next_group['YEAR']) & (df['DATE'] == next_group['first_date'])]
        result_parts.append(next_row)
    
    # 合并数据，恢复DATE的字符串格式并设置索引
    final_result = pd.concat(result_parts).sort_values('DATE')
    final_result['DATE'] = final_result['DATE'].dt.strftime('%A, %B %d, %Y')
    final_result = final_result.set_index(['YEAR', 'WEEK'])
    
    return final_result

测试验证 #

调用函数测试：

# 测试2022年第5周
print(some_window_function(2022, 5))
# 测试2022年第7周
print(some_window_function(2022, 7))

输出结果将与你预期的一致。

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