补全Pandas透视表缺失月份列并合并年月日期标识

问题1：补全缺失月份对应的列并填充为0 #

要补全所有年份的1-12月列，需先生成完整的年月组合，再通过重新索引补全缺失值：

• 提取数据中所有唯一年份，生成每个年份对应的1-12月组合
• 构造与原透视表匹配的多层列索引，确保外层指标（Amount/Quantity）和内层年月组合完整
• 使用reindex补全缺失列，并用fill_value=0填充空值

问题2：合并Year和Month为单个月度日期标识 #

将多层列索引中的(Year, Month)元组转换为统一的日期格式（如YYYY-MM）：

• 遍历所有年月组合，将其格式化为字符串类型的月度标识
• 通过rename方法替换内层列索引，将原元组替换为合并后的日期字符串

完整可执行代码 #

import pandas as pd
import numpy as np

df = pd.DataFrame({
        'Year':[2022,2022,2023,2023,2024,2024],
        'Month':[1,12,11,12,1,1],
        'Code':[None,'John Johnson',np.nan,'John Smith','Mary Williams','ted bundy'],
        'Unit Price':[np.nan,200,None,56,75,65],
        'Quantity':[1500, 140000, 1400000, 455, 648, 759],
        'Amount':[100, 10000, 100000, 5, 48, 59],
        'Invoice':['soccer','basketball','baseball','football','baseball','ice hockey'],
        'energy':[100.,100,100,54,98,3],
        'Category':['alpha','bravo','kappa','alpha','bravo','bravo']
})

index_to_use = ['Category','Code','Invoice','Unit Price']
values_to_use = ['Amount','Quantity']
columns_to_use = ['Year','Month']

# 执行初始透视操作
df2 = df.pivot_table(index=index_to_use,
                            values=values_to_use,
                            columns=columns_to_use)

# --------------------------
# 解决问题1：补全缺失年月列并填充0
# --------------------------
# 获取数据中的所有唯一年份
unique_years = df['Year'].unique()
# 生成所有年份的1-12月完整组合
full_year_month_pairs = [(year, month) for year in unique_years for month in range(1, 13)]
# 构造完整的多层列索引（外层为指标，内层为年月组合）
full_multi_columns = pd.MultiIndex.from_product(
    [values_to_use, full_year_month_pairs],
    names=['Metric', ('Year', 'Month')]
)
# 重新索引补全列，空值填充为0
df2_filled = df2.reindex(columns=full_multi_columns, fill_value=0)

# --------------------------
# 解决问题2：合并年月为单个日期标识
# --------------------------
# 将(Year, Month)元组转换为"YYYY-MM"格式的字符串
formatted_month_cols = [f"{y}-{m:02d}" for y, m in full_year_month_pairs]
# 替换内层列索引，并重命名索引名称
df2_final = df2_filled.rename(
    columns=dict(zip(full_year_month_pairs, formatted_month_cols)),
    level=1
)
df2_final.columns.names = ['Metric', 'Month']

# 查看结果
print(df2_final.head())

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