pandas按年统计数据时如何从指定起始年为缺失年份填充统计值为0

现有基于pandas、numpy的Python代码，用于根据日期数组month_changes对应的值数组vals，按年份统计mean、median、max、min四个指标。目前仅能输出有数据的年份结果，需要修改为从指定的starting_year开始，所有中间缺失年份的统计值都填充为0。

原有代码（已修复语法错误） #

import numpy as np 
import pandas as pd 

# 原代码np.array创建缺少方括号，此处已修复语法问题
month_changes = np.array(["2018-04-01 00:00:00", "2018-05-01 00:00:00", "2019-03-01 00:00:00", "2019-04-01 00:00:00","2019-08-01 00:00:00", "2019-11-01 00:00:00", "2019-12-01 00:00:00","2021-01-01 00:00:00"]) 
vals = np.array([10, 23, 45, 4,5,12,4,-6])
starting_year = 2016

def YearlyIntervals(vals):
    data = pd.DataFrame({"Date": month_changes, "Averages": vals})
    data["Date"] = pd.to_datetime(data["Date"])
    out=(data.groupby(data["Date"].dt.year)
         .agg(['mean','median','max','min'])
         .droplevel(0,1)
         .rename(columns=lambda x:'Average' if x=='mean' else x.title())
        )
    return out

PnL_YearlyFilter= YearlyIntervals(vals)

原有输出 #

Average     Median    Max  Min 
Date                                                                         
2018      16.5        16.5      23   10
2019      14.0        5.0       45    4 
2021      -6.0       -6.0       -6   -6

预期输出 #

Average     Median    Max  Min 
Date
2016      0           0         0     0
2017      0           0         0     0
2018      16.5        16.5      23   10
2019      14.0        5.0       45    4
2020      0           0         0     0
2021      -6.0       -6.0       -6   -6

仅需对原有函数稍作修改，生成完整年份索引后重索引填充0即可，修改后的完整代码如下：

import numpy as np 
import pandas as pd 

month_changes = np.array(["2018-04-01 00:00:00", "2018-05-01 00:00:00", "2019-03-01 00:00:00", "2019-04-01 00:00:00","2019-08-01 00:00:00", "2019-11-01 00:00:00", "2019-12-01 00:00:00","2021-01-01 00:00:00"]) 
vals = np.array([10, 23, 45, 4,5,12,4,-6])
starting_year = 2016

def YearlyIntervals(vals):
    data = pd.DataFrame({"Date": month_changes, "Averages": vals})
    data["Date"] = pd.to_datetime(data["Date"])
    out=(data.groupby(data["Date"].dt.year)
         .agg(['mean','median','max','min'])
         .droplevel(0,1)
         .rename(columns=lambda x:'Average' if x=='mean' else x.title())
        )
    # 新增部分：生成完整年份索引，重索引填充0
    max_year = data["Date"].dt.year.max()
    full_years = pd.Index(range(starting_year, max_year + 1), name="Date")
    out = out.reindex(full_years, fill_value=0)
    return out

PnL_YearlyFilter= YearlyIntervals(vals)

核心修改说明 #

• 首先从原始数据的日期列中取到最大年份，结合传入的starting_year生成连续的完整年份索引
• 调用reindex方法对统计结果重排，所有缺失年份的统计值统一填充为0，同时保留原有年份的统计结果不变

运行上述代码即可得到完全符合预期的输出结果。

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