使用Pandas灵活计算周累计(WTD)值的问题排查与实现

每日需生成包含昨日指标、周六预测指标及周累计(WTD)值的自动化报表，目前周累计值始终显示为0，无法正确计算。需求是代码能自适应运行日期：

• 周二运行：计算周日至周一的累计值
• 周五运行：计算周日至周四的累计值

原始数据包含Location、Metric列及多个日期列（如2024-09-04），期望输出表格包含Location、Metric、Yesterday、Saturday、Week to Date列，但当前Week to Date列全为0。

现有代码如下：

df = pd.read_excel("Data.xlsx")
date_col = df.columns[3:]
df.columns = df.columns[:3].tolist() + pd.to_datetime(date_col, errors='coerce').tolist()
df.columns = df.columns[:3].tolist() + [d.date() for d in df.columns[3:]]
today = datetime.today().date()
yesterday = (datetime.today() - timedelta(days=1)).date()
week_start = today - timedelta(days=today.weekday())
saturday = week_start + timedelta(days=5)

if yesterday in df.columns:
    df_yesterday = df[['Location','Metric', yesterday]]
if saturday in df.columns:
    df_saturday = df[['Location','Metric',saturday]]
sunday = yesterday - timedelta(days=yesterday.weekday() + 1)
wtd = df.columns[(df.columns >= pd.Timestamp(sunday)) & (df.columns <= pd.Timesstamp(yesterday))]

1. 核心问题点 #

• 类型不匹配：日期列被转为datetime.date类型，但筛选时用了pd.Timestamp（datetime.datetime），导致筛选出的日期列为空，累计计算自然为0。
• 缺少累计计算逻辑：原代码仅筛选了日期列，未对这些列的值执行求和操作。
• 周日日期计算错误：原逻辑在周一运行时（昨日为周日），会错误计算出上上周日，导致WTD范围偏差。
• 未合并输出数据：分别提取了昨日、周六数据，但未与周累计值合并成最终报表。

import pandas as pd
from datetime import datetime, timedelta

# 读取原始数据
df = pd.read_excel("Data.xlsx")

# 统一日期列类型为datetime.date
date_cols = df.columns[3:]
df.columns = df.columns[:3].tolist() + [pd.to_datetime(col).date() for col in date_cols]

# 计算关键日期
today = datetime.today().date()
yesterday = today - timedelta(days=1)

# 自适应计算本周日（周起始，周日为一周第一天）
week_sunday = yesterday if yesterday.weekday() == 6 else yesterday - timedelta(days=yesterday.weekday() + 1)
# 本周六日期
week_saturday = week_sunday + timedelta(days=5)

# 提取昨日数据，缺失则填充空值
df_yesterday = df[['Location', 'Metric', yesterday]].rename(columns={yesterday: 'Yesterday'}) if yesterday in df.columns else df[['Location', 'Metric']].assign(Yesterday=pd.NA)

# 提取周六预测数据，缺失则填充空值
df_saturday = df[['Location', 'Metric', week_saturday]].rename(columns={week_saturday: 'Saturday'}) if week_saturday in df.columns else df[['Location', 'Metric']].assign(Saturday=pd.NA)

# 筛选WTD日期范围：周日至昨日
wtd_date_cols = [col for col in df.columns[3:] if week_sunday <= col <= yesterday]

# 计算周累计值
df['Week to Date'] = df[wtd_date_cols].sum(axis=1)

# 合并所有数据生成最终报表
final_df = df_yesterday.merge(df_saturday, on=['Location', 'Metric'], how='left')
final_df = final_df.merge(df[['Location', 'Metric', 'Week to Date']], on=['Location', 'Metric'], how='left')

# 输出或保存结果
print(final_df)
# final_df.to_excel("Daily_Report.xlsx", index=False)

代码说明 #

• 日期类型统一：所有日期相关变量和列都使用datetime.date，避免类型不匹配问题。
• 自适应周范围：根据昨日日期动态调整周起始（周日），确保不同运行日的WTD范围正确。
• 缺失数据兼容：若昨日或周六数据不存在，自动填充空值，避免程序报错。
• 明确累计逻辑：通过横向求和sum(axis=1)计算指定日期范围内的周累计值。
• 结果合并：将各部分数据合并为期望的输出表格格式。

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