如何用Python Pandas将各市场渠道的月度目标拆解为每日目标？

背景 #

长期维护各市场、渠道目标进度报表，原依赖Google Sheets的split/flatten函数拆分月度预算为每日目标，再结合其他数据生成统计后在Tableau聚合。但新增市场/渠道操作繁琐，且Google Sheets文件过大无法连接Tableau，故改用Python实现。

已用Pandas的pd.melt将宽表（每行含KPI、市场、渠道，列对应各月目标）转为长表，但尚未实现：将每个月扩展为对应天数，保留KPI/市场/渠道信息，且每日目标按当月天数占比分配。

现有代码 #

import pandas as pd

df = pd.DataFrame([['New', 'Albuquerque', 'Marketing', 34, 34, 34, 35, 35, 36, 36, 36, 37, 40, 40, 40],
                   ['New', 'Boston', 'Marketing', 12, 12, 12, 12, 12, 13, 13, 14, 14, 15, 16, 17],
                   ['Converted', 'Albuquerque', 'Marketing', 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5],
                   ['Converted', 'Boston', 'Marketing', 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2]],
                  columns=['KPI',
                           'Market',
                           'Channel',
                           '2022-01-01',
                           '2022-02-01',
                           '2022-03-01',
                           '2022-04-01',
                           '2022-05-01',
                           '2022-06-01',
                           '2022-07-01',
                           '2022-08-01',
                           '2022-09-01',
                           '2022-10-01',
                           '2022-11-01',
                           '2022-12-01'])

# Set up variables for the melt
index_vars = ['KPI','Market','Channel']
val_vars = df.set_index(index_vars).columns.tolist()

# Unpivot months
df = pd.melt(df,
             id_vars=index_vars,
             value_vars=val_vars,
             var_name='Date',
             value_name='Goal',
             ignore_index=False)

# Force dates to datetime, sort and reset index for a clean view
df['Date'] = pd.to_datetime(df['Date'], errors='coerce')
df = df.sort_values(by=['KPI','Market','Channel','Date']).reset_index(drop=True)

print(df)

当前输出 #

KPI       Market    Channel       Date  Goal
0   Converted  Albuquerque  Marketing 2022-01-01     5
1   Converted  Albuquerque  Marketing 2022-02-01     5
2   Converted  Albuquerque  Marketing 2022-03-01     5
3   Converted  Albuquerque  Marketing 2022-04-01     5
4   Converted  Albuquerque  Marketing 2022-05-01     5
5   Converted  Albuquerque  Marketing 2022-06-01     5
6   Converted  Albuquerque  Marketing 2022-07-01     5
7   Converted  Albuquerque  Marketing 2022-08-01     5
8   Converted  Albuquerque  Marketing 2022-09-01     5
9   Converted  Albuquerque  Marketing 2022-10-01     5
10  Converted  Albuquerque  Marketing 2022-11-01     5
11  Converted  Albuquerque  Marketing 2022-12-01     5
12  Converted       Boston  Marketing 2022-01-01     2
13  Converted       Boston  Marketing 2022-02-01     2
...

期望输出 #

KPI       Market    Channel       Date      Goal
0   Converted  Albuquerque  Marketing 2022-01-01  0.161290
1   Converted  Albuquerque  Marketing 2022-01-02  0.161290
2   Converted  Albuquerque  Marketing 2022-01-03  0.161290
3   Converted  Albuquerque  Marketing 2022-01-04  0.161290
4   Converted  Albuquerque  Marketing 2022-01-05  0.161290
5   Converted  Albuquerque  Marketing 2022-01-06  0.161290
6   Converted  Albuquerque  Marketing 2022-01-07  0.161290
7   Converted  Albuquerque  Marketing 2022-01-08  0.161290
8   Converted  Albuquerque  Marketing 2022-01-09  0.161290
9   Converted  Albuquerque  Marketing 2022-01-10  0.161290
10  Converted  Albuquerque  Marketing 2022-01-11  0.161290
11  Converted  Albuquerque  Marketing 2022-01-12  0.161290
12  Converted       Boston  Marketing 2022-01-01  0.064516
13  Converted       Boston  Marketing 2022-01-02  0.064516
...

尝试过的方法及问题 #

• 尝试用reindex+日期范围扩展，报错TypeError: Cannot compare dtypes int64 and datetime64[ns]，原因是该方法仅适用于以日期为唯一索引的DataFrame，无法处理多维度（KPI/Market/Channel）的扩展需求。
• 尝试转换Date列为周期再转回时间戳，仍出现相同类型错误。

解决方案 #

在现有melt后的代码基础上，添加以下步骤实现日期扩展和每日目标计算：

# 计算当月天数和每日目标
df['days_in_month'] = df['Date'].dt.days_in_month
df['daily_goal'] = df['Goal'] / df['days_in_month']

# 生成当月所有日期序列
df['daily_dates'] = df['Date'].apply(
    lambda x: pd.date_range(start=x, end=x + pd.offsets.MonthEnd(0), freq='D')
)

# 展开日期序列为多行，保留所有维度信息
df_daily = df.explode('daily_dates').drop(columns=['Date', 'Goal', 'days_in_month'])
df_daily = df_daily.rename(columns={'daily_dates': 'Date', 'daily_goal': 'Goal'})

# 排序并重置索引
df_daily = df_daily.sort_values(by=['KPI', 'Market', 'Channel', 'Date']).reset_index(drop=True)

print(df_daily)

步骤说明 #

1. 计算每日目标：利用dt.days_in_month获取当月天数，将月度目标除以天数得到每日目标值。
2. 生成月度日期序列：对每个月度日期，用pd.date_range生成该月的所有日期（从当月第一天到最后一天）。
3. 展开日期序列：使用explode将每个日期序列拆分为单独的行，同时保留KPI、Market、Channel等维度信息。
4. 整理结果：删除冗余列，重命名列名并排序，得到符合需求的每日目标报表。

内容的提问来源于stack exchange，提问作者a guy i kno