如何基于前后周最近日期分组？支持多年份分组重置

问题需求 #

• 处理含DATE和WEEK列的Pandas DataFrame，按以下规则自定义分组：
  • 相邻日期间隔≤3天（含周末间隔）的行归为同一分组；间隔>3天则触发新分组
  • 每个年份的分组编号从1开始重置
  • 每个年份中，最早的分组标记为TAIL（无前置分组），最晚的分组标记为HEAD（无后置分组），中间分组标记为NONE，且GROUP/MISSING组合唯一

输入示例 #

import pandas as pd
df = pd.DataFrame({'DATE': ['Tuesday, November 7, 2023', 'Wednesday, November 8, 2023', 'Thursday, November 9, 2023', 'Friday, November 10, 2023', 'Monday, November 13, 2023', 'Friday, November 17, 2023', 'Sunday, November 19, 2023', 'Monday, November 20, 2023', 'Thursday, November 23, 2023', 'Friday, November 24, 2023'], 'WEEK': [45, 45, 45, 45, 46, 46, 46, 47, 47, 47]})

期望输出 #

DATE  WEEK  GROUP MISSING
    Tuesday, November 7, 2023    45      1    TAIL
  Wednesday, November 8, 2023    45      1    TAIL
   Thursday, November 9, 2023    45      1    TAIL
    Friday, November 10, 2023    45      1    TAIL
    Monday, November 13, 2023    46      1    TAIL
    Friday, November 17, 2023    46      2    NONE
    Sunday, November 19, 2023    46      2    NONE
    Monday, November 20, 2023    47      2    NONE
  Thursday, November 23, 2023    47      3    HEAD
    Friday, November 24, 2023    47      3    HEAD

问题分析 #

原代码仅通过周数差判断分组，未考虑日期间隔的实际断点，导致分组偏移；同时未处理年份重置和MISSING列生成逻辑。

修正代码 #

import pandas as pd

# 输入数据
df = pd.DataFrame({'DATE': ['Tuesday, November 7, 2023', 'Wednesday, November 8, 2023', 'Thursday, November 9, 2023', 'Friday, November 10, 2023', 'Monday, November 13, 2023', 'Friday, November 17, 2023', 'Sunday, November 19, 2023', 'Monday, November 20, 2023', 'Thursday, November 23, 2023', 'Friday, November 24, 2023'], 'WEEK': [45, 45, 45, 45, 46, 46, 46, 47, 47, 47]})

# 1. 转换DATE列为datetime格式，提取年份用于分组重置
df['DATE'] = pd.to_datetime(df['DATE'])
df['YEAR'] = df['DATE'].dt.year

# 2. 计算相邻日期的天数差，标记分组断点
df['DATE_DIFF'] = df['DATE'].diff().dt.days
# 断点规则：第一行 或 日期间隔超过3天（超过常规周末间隔）
df['BREAK'] = ((df['DATE_DIFF'] > 3) | df['DATE_DIFF'].isna()).astype(int)

# 3. 按年份分组计算GROUP编号，每年从1开始累计
df['GROUP'] = df.groupby('YEAR')['BREAK'].cumsum()

# 4. 为每个年份的分组分配MISSING标记
def assign_missing_tag(group):
    min_group = group['GROUP'].min()
    max_group = group['GROUP'].max()
    group['MISSING'] = group['GROUP'].map(
        lambda x: 'TAIL' if x == min_group else ('HEAD' if x == max_group else 'NONE')
    )
    return group

df = df.groupby('YEAR').apply(assign_missing_tag).reset_index(drop=True)

# 清理临时列并格式化DATE为原字符串格式
df = df[['DATE', 'WEEK', 'GROUP', 'MISSING']]
df['DATE'] = df['DATE'].dt.strftime('%A, %B %d, %Y')

# 输出结果
print(df.to_string(index=False))

代码说明 #

1. 日期转换与年份提取：将DATE转为datetime类型，方便计算日期差和按年份重置分组。
2. 断点标记：通过相邻日期差判断是否触发新分组，符合实际业务中“连续日期”的定义（间隔≤3天包含周末）。
3. 年份内分组累计：使用groupby('YEAR')确保每个年份的分组从1开始计数。
4. MISSING列生成：针对每个年份的分组，为最早的分组标记TAIL，最晚的标记HEAD，中间分组标记NONE，保证GROUP/MISSING组合唯一。

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