Python：按频率循环生成至月末的日程及单元格显示优化问询

现有表格如下：

需要新增next_schedule列，根据frequency列的频率，从start_execution_date开始生成所有符合条件的日程，直到end_month对应的月末。当前代码仅能生成单次日期，无法循环生成后续日程（例如Weekly频率应生成2022-11-20、2022-11-27等），且生成的日期挤在一个单元格中，无法单独成行。

当前代码如下：

from calendar import mdays, calendar
from datetime import datetime as dt, timedelta
from datetime import date
from dateutil.relativedelta import relativedelta
from dateutil.rrule import rrule, DAILY

predict = []

for frequency in df1['frequency']:
  if frequency == 'Daily':
    next= df1['start_execution_date'] + pd.Timedelta(days=1)
    predict.append(next)
  elif frequency == 'Weekly':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=1)
    predict.append(next)
  elif frequency == 'Every 2 Weeks':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=2)
    predict.append(next)
  elif frequency == 'Monthly':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=4)
    predict.append(next)
  elif frequency == 'Every 1 Months':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=4)
    predict.append(next)
  elif frequency == 'Every 3 Months':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=12)
    predict.append(next)
  elif frequency == 'Every 6 Months':
    next= df1['start_execution_date'] + pd.Timedelta(weeks=24)
    predict.append(next)
  else:
    next= df1['start_execution_date']
    predict.append(next)

df1.insert(4, "next_schedule", predict, True)
df1

核心思路 #

• 用dateutil.rrule循环生成符合频率的所有日期，替代单次时间增量计算
• 生成日期列表后，将每行数据按日期拆分为多行，实现每个日期单独成行

完整代码 #

import pandas as pd
from datetime import datetime
from dateutil.relativedelta import relativedelta
from dateutil.rrule import rrule, DAILY, WEEKLY, MONTHLY

# 处理日期格式，确保start_execution_date和end_month是datetime类型
df1['start_execution_date'] = pd.to_datetime(df1['start_execution_date'])
# 将end_month转为对应月份的最后一天
df1['end_date'] = pd.to_datetime(df1['end_month'], format='%d-%m-%Y') + relativedelta(day=31)
df1['end_date'] = df1['end_date'].dt.normalize()

# 定义生成日程的函数
def generate_schedules(row):
    start = row['start_execution_date']
    end = row['end_date']
    freq = row['frequency']
    
    # 根据不同频率生成所有符合条件的日期
    if freq == 'Daily':
        dates = list(rrule(DAILY, dtstart=start, until=end))
    elif freq == 'Weekly':
        dates = list(rrule(WEEKLY, dtstart=start, until=end))
    elif freq == 'Every 2 weeks':
        dates = list(rrule(WEEKLY, interval=2, dtstart=start, until=end))
    elif freq in ['Monthly', 'Every 1 months']:
        dates = list(rrule(MONTHLY, dtstart=start, until=end))
    elif freq == 'Every 3 months':
        dates = list(rrule(MONTHLY, interval=3, dtstart=start, until=end))
    elif freq == 'Every 6 months':
        dates = list(rrule(MONTHLY, interval=6, dtstart=start, until=end))
    elif freq == 'Every 12 months':
        dates = list(rrule(MONTHLY, interval=12, dtstart=start, until=end))
    elif freq == 'Once':
        dates = [start] if start <= end else []
    else:  # 处理未知频率（如??、SQL Startup）
        dates = [start]
    
    return dates

# 生成每行的日程列表
df1['next_schedule_list'] = df1.apply(generate_schedules, axis=1)

# 将日期列表拆分为单独行
df_result = df1.explode('next_schedule_list').rename(columns={'next_schedule_list': 'next_schedule'})

# 清理临时列
df_result = df_result.drop(columns=['end_date'])

print(df_result)

代码说明 #

1. 日期格式统一：把start_execution_date转为datetime类型，将end_month解析为对应月份的最后一天，确保生成的日程不会超出截止时间。
2. 批量日程生成：借助dateutil.rrule根据频率规则生成从开始到结束的所有日期，支持自定义间隔（如每2周、每3个月）。
3. 拆分多行：使用explode方法将每行的日期列表拆分为独立行，实现每个日期单独占一行的需求。
4. 特殊情况兼容：对Once和未知频率单独处理，仅保留符合时间范围的起始日期。

内容的提问来源于stack exchange，提问作者Kitty.Cattie