Python Pandas实现DataFrame连续月度EffectiveDate及数据填充

问题需求 #

现有Pandas DataFrame，其中EffectiveDate列仅包含季度初日期，存在月度日期缺失。需要将EffectiveDate列补全为连续月度日期，其余列填充对应最近EffectiveDate的取值。例如Group=A时，缺失的2/1/2022、3/1/2022对应的所有列值需沿用1/1/2022的取值，以此类推。

输入DataFrame #

import pandas as pd
    
data = {
        'Group': ['A'] * 24,
        'EffectiveDate': [
            '1/1/2022', '1/1/2022', '1/1/2022', '1/1/2022', '1/1/2022', '1/1/2022',
            '4/1/2022', '4/1/2022', '4/1/2022', '4/1/2022', '4/1/2022', '4/1/2022',
            '7/1/2022', '7/1/2022', '7/1/2022', '7/1/2022', '7/1/2022', '7/1/2022',
            '10/1/2022', '10/1/2022', '10/1/2022', '10/1/2022', '10/1/2022', '10/1/2022'
        ],
        'ForecastDate': [
            '1/1/2022', '2/1/2022', '3/1/2022', '4/1/2022', '5/1/2022', '6/1/2022',
            '4/1/2022', '5/1/2022', '6/1/2022', '7/1/2022', '8/1/2022', '9/1/2022',
            '7/1/2022', '8/1/2022', '9/1/2022', '10/1/2022', '11/1/2022', '12/1/2022',
            '10/1/2022', '11/1/2022', '12/1/2022', '1/1/2023', '2/1/2023', '3/1/2023'
        ],
        'SKU': ['ABC12'] * 24,
        'Source': ['fdhh'] * 24
    }
    
df = pd.DataFrame(data)

期望输出DataFrame #

解决方案 #

步骤1：转换日期列类型 #

将日期列转换为datetime类型，便于后续日期操作：

df['EffectiveDate'] = pd.to_datetime(df['EffectiveDate'], format='%m/%d/%Y')
df['ForecastDate'] = pd.to_datetime(df['ForecastDate'], format='%m/%d/%Y')

步骤2：生成连续月度日期序列 #

提取数据中的分组信息，以及EffectiveDate的起止范围，生成完整的月度日期序列：

# 获取唯一分组
groups = df['Group'].unique()
# 获取EffectiveDate的起止日期
min_date = df['EffectiveDate'].min()
max_date = df['EffectiveDate'].max()
# 生成连续月度日期（每月第一天）
all_effective_dates = pd.date_range(start=min_date, end=max_date, freq='MS')

步骤3：构建全量分组-日期笛卡尔积 #

创建包含所有分组和所有连续月度日期的基础DataFrame：

# 生成分组与日期的笛卡尔积
full_df = pd.MultiIndex.from_product([groups, all_effective_dates], names=['Group', 'EffectiveDate']).to_frame(index=False)

步骤4：合并数据并填充缺失值 #

合并原数据与全量日期DataFrame，通过向前填充补全缺失的列值，并生成每个日期对应的ForecastDate序列：

# 原数据去重，保留每个Group+EffectiveDate对应的属性及ForecastDate列表
original_unique = df.groupby(['Group', 'EffectiveDate']).agg({
    'ForecastDate': list,
    'SKU': 'first',
    'Source': 'first'
}).reset_index()

# 展开ForecastDate列表
original_expanded = original_unique.explode('ForecastDate')

# 合并全量日期与原数据
merged = pd.merge(full_df, original_expanded, on=['Group', 'EffectiveDate'], how='left')

# 按分组向前填充SKU和Source
merged['SKU'] = merged.groupby('Group')['SKU'].ffill()
merged['Source'] = merged.groupby('Group')['Source'].ffill()

# 预存每个分组的所有ForecastDate（排序后）
group_forecasts = df.groupby('Group')['ForecastDate'].unique().apply(sorted).to_dict()

# 为每个EffectiveDate生成符合要求的ForecastDate列表（>=当前EffectiveDate）
def get_matching_forecasts(row):
    return [date for date in group_forecasts[row['Group']] if date >= row['EffectiveDate']]

merged['ForecastDate'] = merged.apply(get_matching_forecasts, axis=1)

# 展开ForecastDate得到最终结果
final_df = merged.explode('ForecastDate').reset_index(drop=True)

# 将日期格式转换回原格式（去掉前导零）
final_df['EffectiveDate'] = final_df['EffectiveDate'].dt.strftime('%m/%d/%Y').str.lstrip('0').replace('/0', '/', regex=True)
final_df['ForecastDate'] = final_df['ForecastDate'].dt.strftime('%m/%d/%Y').str.lstrip('0').replace('/0', '/', regex=True)

步骤5：验证结果 #

final_df即为符合需求的输出数据，可通过print(final_df.to_markdown(index=False))生成与期望输出一致的表格。

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