如何用Pandas为SKU-Location组合生成连续月度数据并向前填充

问题背景 #

需求说明：由于SKU1、Location1缺失2023年2月、3月的提交数据，需为其向前填充2023年1月至3月的数据。具体需完成两步：1. 为每个SKU、Location组合生成连续月份；2. 对NA值执行ffill（向前填充）操作。预期结果需确保月度数据连续，蓝色高亮部分为向前填充的数据。

用户提供的输入DataFrame代码：

import pandas as pd

# Sample data
data = {
    'submissionmonth': ['1/1/2023', '1/1/2023', '1/1/2023', '1/1/2023', '1/1/2023', '1/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023'],
    'sku': ['SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2'],
    'location': ['Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2'],
    'forecastmonth': ['1/1/2023', '2/1/2023', '3/1/2023', '4/1/2023', '5/1/2023', '6/1/2023', '4/1/2023', '5/1/2023', '6/1/2023', '7/1/2023', '8/1/2023', '9/1/2023', '4/1/2023', '5/1/2023', '6/1/2023', '7/1/2023', '8/1/2023', '9/1/2023', '11/1/2023', '12/1/2023', '1/1/2024', '2/1/2024', '3/1/2024', '4/1/2024', '4/1/2024', '4/1/2024'],
    'cost': [100, 100, 100, 100, 100, 100, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200]
}

# Create DataFrame
df = pd.DataFrame(data)

# Convert to datetime
df['submissionmonth'] = pd.to_datetime(df['submissionmonth'], format='%m/%d/%Y')
df['forecastmonth'] = pd.to_datetime(df['forecastmonth'], format='%m/%d/%Y')

print(df.shape)
print(df)

用户尝试分组并使用ffill方法时出现形状错误，核心问题是没有先为每个SKU-Location组合生成完整的连续月度序列，且未处理每个提交月份对应多个预测月份的一对多关系，直接填充会导致维度不匹配。

解决方案 #

核心思路 #

1. 为每个sku+location组合生成从最早到最晚提交月份的连续月度序列，补全缺失的2023年2、3月。
2. 将原数据与完整序列合并，按分组向前填充缺失的forecastmonth和cost数据，最后展开恢复原有的行结构。

完整实现代码 #

import pandas as pd

# 原数据初始化（保留用户代码）
data = {
    'submissionmonth': ['1/1/2023', '1/1/2023', '1/1/2023', '1/1/2023', '1/1/2023', '1/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '4/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023', '10/1/2023'],
    'sku': ['SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU1', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2', 'SKU2'],
    'location': ['Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location1', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2', 'Location2'],
    'forecastmonth': ['1/1/2023', '2/1/2023', '3/1/2023', '4/1/2023', '5/1/2023', '6/1/2023', '4/1/2023', '5/1/2023', '6/1/2023', '7/1/2023', '8/1/2023', '9/1/2023', '4/1/2023', '5/1/2023', '6/1/2023', '7/1/2023', '8/1/2023', '9/1/2023', '11/1/2023', '12/1/2023', '1/1/2024', '2/1/2024', '3/1/2024', '4/1/2024', '4/1/2024', '4/1/2024'],
    'cost': [100, 100, 100, 100, 100, 100, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200, 200]
}

df = pd.DataFrame(data)
df['submissionmonth'] = pd.to_datetime(df['submissionmonth'], format='%m/%d/%Y')
df['forecastmonth'] = pd.to_datetime(df['forecastmonth'], format='%m/%d/%Y')

# 1. 生成每个SKU-Location的连续月度序列
grouped = df.groupby(['sku', 'location'])
date_ranges = grouped['submissionmonth'].agg(['min', 'max'])

full_dates = []
for (sku, loc), (min_date, max_date) in date_ranges.iterrows():
    # 生成从最小到最大日期的所有月初日期（freq='MS'表示每月第一天）
    months = pd.date_range(start=min_date, end=max_date, freq='MS')
    for month in months:
        full_dates.append({'sku': sku, 'location': loc, 'submissionmonth': month})

full_df = pd.DataFrame(full_dates)

# 2. 合并原数据并向前填充缺失值
# 先将原数据按sku、location、submissionmonth分组，把对应的forecastmonth和cost存为列表
original_groups = df.groupby(['sku', 'location', 'submissionmonth']).agg(list).reset_index()

# 合并完整日期序列与原分组数据
merged = pd.merge(full_df, original_groups, on=['sku', 'location', 'submissionmonth'], how='left')

# 按sku和location分组，向前填充缺失的forecastmonth和cost列表
merged['forecastmonth'] = merged.groupby(['sku', 'location'])['forecastmonth'].ffill()
merged['cost'] = merged.groupby(['sku', 'location'])['cost'].ffill()

# 展开列表，恢复原有的多行结构
final_df = merged.explode(['forecastmonth', 'cost']).reset_index(drop=True)

# 查看结果
print(final_df.shape)
print(final_df)

代码说明 #

• 生成连续月度序列：通过pd.date_range为每个SKU-Location组合生成完整的月份，确保没有缺失的提交月份。
• 分组存储原数据：将每个提交月份对应的多个预测月份和成本值存为列表，避免合并时的维度冲突。
• 向前填充：按SKU和Location分组，对缺失的列表数据执行ffill，复用最近的有效数据。
• 展开列表：通过explode将列表还原为多行，恢复原数据的行结构。

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