Python实现数据透视表多Cohort同期群报告生成及缺失值填充

需求梳理 #

• 基础数据规则
  • 统计周期共5个，范围为201910-202002
  • 各周期gross adds基准值为[20000, 30000, 32000, 40000, 36000]
  • 原始输入为5行5列结构的数据透视表，行列索引均匹配上述统计周期，表中存在若干NaN缺失值
• 报告生成规则
  • Cohort01：透视表内有效值除以对应所属周期的gross adds值，结果为百分比格式，参考首列值为5%、3%、3%、1%、1%
  • Cohort02及后续版本：逐版本递增缺失值填充数量，每版比上一版多填充1处缺失值
    • Cohort02：单处缺失值取Cohort01对应行最后一个有效值的平均值填充
    • Cohort03：2处缺失值取Cohort01、Cohort02对应位置数值的平均值填充
    • Cohort04：3处缺失值取Cohort02、Cohort03对应位置数值的平均值填充
    • 后续版本按上述逻辑递推，直到所有缺失值填充完成

实现代码 #

依赖环境：Python 3.7+，需安装pandas、numpy库

import pandas as pd
import numpy as np

# ------------ 基础参数配置 ------------
periods = ['201910', '201911', '201912', '202001', '202002']
gross_adds = pd.Series([20000, 30000, 32000, 40000, 36000], index=periods)

# 替换为实际业务的5*5原始透视表数据，以下为匹配参考首列值的测试数据
raw_pivot = pd.DataFrame(
    [
        [1000, np.nan, np.nan, np.nan, np.nan],
        [900, np.nan, np.nan, np.nan, np.nan],
        [960, np.nan, np.nan, np.nan, np.nan],
        [400, np.nan, np.nan, np.nan, np.nan],
        [360, np.nan, np.nan, np.nan, np.nan]
    ],
    index=periods,
    columns=periods
)

# ------------ 生成Cohort01基准报告 ------------
cohort_reports = {}
cohort_reports['Cohort01'] = raw_pivot.div(gross_adds, axis=0)

# ------------ 按规则批量生成后续版本报告 ------------
# 定位所有缺失值坐标，按先行后列顺序排序
nan_coords = list(zip(*np.where(raw_pivot.isna())))
total_nan_count = len(nan_coords)

for version in range(2, total_nan_count + 2):
    prev_version = cohort_reports[f'Cohort{version-1}']
    curr_report = prev_version.copy()
    # 当前版本需要填充的缺失值数量：版本号-1
    curr_fill_count = version - 1
    fill_targets = nan_coords[:curr_fill_count]

    for row_idx, col_idx in fill_targets:
        if pd.isna(curr_report.iloc[row_idx, col_idx]):
            if version == 2:
                # Cohort02填充规则：取Cohort01对应行已有的有效值均值
                valid_series = cohort_reports['Cohort01'].iloc[row_idx, :col_idx+1].dropna()
                fill_val = valid_series.mean() if not valid_series.empty else 0
            else:
                # 后续版本填充规则：取前两个版本对应位置的数值均值
                v_prev2 = cohort_reports[f'Cohort{version-2}'].iloc[row_idx, col_idx]
                v_prev1 = cohort_reports[f'Cohort{version-1}'].iloc[row_idx, col_idx]
                fill_val = np.nanmean([v_prev2, v_prev1])
            curr_report.iloc[row_idx, col_idx] = fill_val
    
    cohort_reports[f'Cohort{version:02d}'] = curr_report

# ------------ 结果调用与导出 ------------
# 打印Cohort01百分比结果验证
print("Cohort01 百分比结果：")
print((cohort_reports['Cohort01'] * 100).round(2).astype(str) + '%')

# 如需导出所有版本到同一Excel文件，取消注释以下代码
# with pd.ExcelWriter('all_cohort_reports.xlsx') as writer:
#     for report_name, report_df in cohort_reports.items():
#         (report_df * 100).round(2).to_excel(writer, sheet_name=report_name)

使用说明 #

• 安装依赖执行命令：pip install pandas numpy
• 将代码中raw_pivot变量替换为实际业务的5*5透视表数据即可运行
• 所有生成的Cohort报告均存储在cohort_reports字典中，可按需调用、格式化或导出

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