如何解决DataFrame shift操作后索引未扩展、缺失行的问题

现有如下DataFrame df：

x1        x2         y
year_week                              
2019-50   -0.090451  0.022198 -0.231182
2019-51    0.164583 -0.104173  0.016565
2019-52   -1.088672 -1.274486 -1.284807
2020-01    0.211166  0.130683 -0.350436
2020-02   -1.088672 -1.274486 -1.284807

执行以下代码生成领先/滞后指标：

df['x1_leading2'] = df['x1'].shift(2)
df['x2_lagging3'] = df['x2'].shift(-3)

得到的结果仅保留原索引行，无法扩展出2019-47、2019-48、2019-49、2020-03、2020-04这些行，无法达到预期输出：

x1        x2         y  x1_leading2  x2_lagging3
year_week                                                        
2019-47         NaN       NaN       NaN          NaN     0.022198
2019-48         NaN       NaN       NaN          NaN    -0.104173
2019-49         NaN       NaN       NaN          NaN    -1.274486
2019-50   -0.090451  0.022198 -0.231182          NaN     0.130683
2019-51    0.164583 -0.104173  0.016565          NaN    -1.274486
2019-52   -1.088672 -1.274486 -1.284807    -0.090451          NaN
2020-01    0.211166  0.130683 -0.350436     0.164583          NaN
2020-02   -1.088672 -1.274486 -1.284807    -1.088672          NaN
2020-03         NaN       NaN       NaN     0.211166          NaN
2020-04         NaN       NaN       NaN    -1.088672          NaN

核心思路是先构造包含所有目标周的完整索引，将原DataFrame对齐到该索引后，再执行shift操作生成指标。

步骤1：生成完整的目标周索引 #

调整generate_year_week的起始日期，确保覆盖2019-47到2020-04的所有周：

def generate_year_week(start_date, end_date):
    import datetime
    current_date = start_date
    year_weeks = []

    while current_date <= end_date:
        year = current_date.year
        week = current_date.isocalendar()[1]
        year_week = f"{year}-{week:02d}"
        year_weeks.append(year_week)

        current_date += datetime.timedelta(weeks=1)
    return year_weeks

import datetime
# 调整start_date到2019-11-24，对应2019-47周
start_date = datetime.date(2019, 11, 24)
end_date = datetime.date(2020, 1, 31)
full_year_weeks = generate_year_week(start_date, end_date)

此时full_year_weeks输出为：

['2019-47', '2019-48', '2019-49', '2019-50', '2019-51', '2019-52', '2020-01', '2020-02', '2020-03', '2020-04']

步骤2：将原DataFrame重新索引到完整索引 #

使用reindex方法扩展行，缺失值自动填充为NaN：

df_full = df.reindex(full_year_weeks)

步骤3：生成领先/滞后指标 #

在重新索引后的DataFrame上执行shift操作：

df_full['x1_leading2'] = df_full['x1'].shift(2)
df_full['x2_lagging3'] = df_full['x2'].shift(-3)

最终结果 #

执行上述代码后，df_full即为预期的结果：

x1        x2         y  x1_leading2  x2_lagging3
year_week                                                        
2019-47         NaN       NaN       NaN          NaN     0.022198
2019-48         NaN       NaN       NaN          NaN    -0.104173
2019-49         NaN       NaN       NaN          NaN    -1.274486
2019-50   -0.090451  0.022198 -0.231182          NaN     0.130683
2019-51    0.164583 -0.104173  0.016565          NaN    -1.274486
2019-52   -1.088672 -1.274486 -1.284807    -0.090451          NaN
2020-01    0.211166  0.130683 -0.350436     0.164583          NaN
2020-02   -1.088672 -1.274486 -1.284807    -1.088672          NaN
2020-03         NaN       NaN       NaN     0.211166          NaN
2020-04         NaN       NaN       NaN    -1.088672          NaN

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