如何对分组数据子集执行向前填充以生成连续月度数据？

假设当前月份为2023年12月，现有如下Pandas DataFrame，需按以下规则完成数据处理：

• 对于A1-sup1分组，2023年1月存在提交记录，下一次提交在2023年4月，需将1月的所有数据向前填充至2023年2月、3月；
• A1-sup1分组2023年4月的提交记录，需向前填充至2023年5月至12月；
• A2-sup2分组2023年10月的提交记录，需向前填充至2023年11月、12月。

输入DataFrame代码 #

import pandas as pd

input_data = [
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Jan-2023","cost": 100},
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Feb-2023","cost": 105},
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Mar-2023","cost": 108},
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 106},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Oct-2023","cost": 101},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Nov-2023","cost": 102},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Dec-2023","cost": 109},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Jan-2024","cost": 104},
]

# 创建DataFrame
input_data = pd.DataFrame(input_data)

# 打印DataFrame（可选）
print(input_data.shape)
input_data

预期输出DataFrame代码 #

import pandas as pd

output_data = [
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Jan-2023","cost": 100},
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Feb-2023","cost": 105},
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Mar-2023","cost": 108},
{"submissionmonth": "Jan-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 106},
{"submissionmonth": "Feb-2023", "sku": "A1","location": "sup1","forecastmonth": "Jan-2023","cost": 100},
{"submissionmonth": "Feb-2023", "sku": "A1","location": "sup1","forecastmonth": "Feb-2023","cost": 105},
{"submissionmonth": "Feb-2023", "sku": "A1","location": "sup1","forecastmonth": "Mar-2023","cost": 108},
{"submissionmonth": "Feb-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 106},
{"submissionmonth": "Mar-2023", "sku": "A1","location": "sup1","forecastmonth": "Jan-2023","cost": 100},
{"submissionmonth": "Mar-2023", "sku": "A1","location": "sup1","forecastmonth": "Feb-2023","cost": 105},
{"submissionmonth": "Mar-2023", "sku": "A1","location": "sup1","forecastmonth": "Mar-2023","cost": 108},
{"submissionmonth": "Mar-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 106},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Apr-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "May-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "May-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "May-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "May-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Jun-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Jun-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Jun-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Jun-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Jul-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Jul-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Jul-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Jul-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Aug-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Aug-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Aug-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Aug-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Sept-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Sept-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Sept-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Sept-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Oct-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Oct-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Oct-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Oct-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Oct-2023","cost": 101},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Nov-2023","cost": 102},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Dec-2023","cost": 109},
{"submissionmonth": "Oct-2023", "sku": "A2","location": "sup2","forecastmonth": "Jan-2024","cost": 104},
{"submissionmonth": "Nov-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Nov-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Nov-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Nov-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Nov-2023", "sku": "A2","location": "sup2","forecastmonth": "Oct-2023","cost": 101},
{"submissionmonth": "Nov-2023", "sku": "A2","location": "sup2","forecastmonth": "Nov-2023","cost": 102},
{"submissionmonth": "Nov-2023", "sku": "A2","location": "sup2","forecastmonth": "Dec-2023","cost": 109},
{"submissionmonth": "Nov-2023", "sku": "A2","location": "sup2","forecastmonth": "Jan-2024","cost": 104},
{"submissionmonth": "Dec-2023", "sku": "A1","location": "sup1","forecastmonth": "Apr-2023","cost": 101},
{"submissionmonth": "Dec-2023", "sku": "A1","location": "sup1","forecastmonth": "May-2023","cost": 102},
{"submissionmonth": "Dec-2023", "sku": "A1","location": "sup1","forecastmonth": "Jun-2023","cost": 109},
{"submissionmonth": "Dec-2023", "sku": "A1","location": "sup1","forecastmonth": "Jul-2023","cost": 104},
{"submissionmonth": "Dec-2023", "sku": "A2","location": "sup2","forecastmonth": "Oct-2023","cost": 101},
{"submissionmonth": "Dec-2023", "sku": "A2","location": "sup2","forecastmonth": "Nov-2023","cost": 102},
{"submissionmonth": "Dec-2023", "sku": "A2","location": "sup2","forecastmonth": "Dec-2023","cost": 109},
{"submissionmonth": "Dec-2023", "sku": "A2","location": "sup2","forecastmonth": "Jan-2024","cost": 104},
]

# 创建DataFrame
output_data = pd.DataFrame(output_data)

# 打印DataFrame（可选）
print(output_data.shape)
output_data

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