使用Pandas Pivot Table生成多级子总计的教程

import pandas as pd
from itertools import combinations

# 示例数据
data = {
    'date': ['Jan', 'Feb'],
    'subject': ['English', 'Chemistry'],
    'animal': ['cat', 'dog'],
    'colors': ['blue', 'green'],
    'value': [1, 2]
}
df = pd.DataFrame(data)

# 扩展DataFrame以模拟更多数据点，确保所有组合都有数据
# 否则，如果只有两个数据点，很多组合会是空的
df = pd.DataFrame({
    'date': ['Jan', 'Jan', 'Feb', 'Feb', 'Jan', 'Feb'],
    'subject': ['English', 'English', 'Chemistry', 'Chemistry', 'English', 'Chemistry'],
    'animal': ['cat', 'dog', 'cat', 'dog', 'cat', 'dog'],
    'colors': ['blue', 'green', 'blue', 'green', 'green', 'blue'],
    'value': [1, 2, 3, 4, 5, 6]
})

print("原始DataFrame:")
print(df)

# 定义需要转换为分类类型的列
cols_to_categorize = ['date', 'subject', 'animal', 'colors']

# 为每个列创建CategoricalDtype，并添加'all'类别
cats = {}
for col in cols_to_categorize:
    unique_values = list(df[col].unique())
    # 确保'all'类别排在最后，以便在透视表中显示时有序
    cats[col] = pd.CategoricalDtype(unique_values + ['all'], ordered=True)

df_categorized = df.astype(cats)

print("\n转换为分类类型并添加'all'类别后的DataFrame:")
print(df_categorized.dtypes)

# 存储所有子总计的列表
data_for_pivot = [df_categorized.copy()] # 包含原始数据

# 遍历所有可能的组合，生成子总计
# 例如，当r=len(cols_to_categorize)-1时，意味着一个维度是'all'
# 当r=len(cols_to_categorize)-2时，意味着两个维度是'all'，以此类推
# 我们可以通过迭代r从1到len(cols_to_categorize)来生成所有级别的子总计
for r in range(len(cols_to_categorize)):
    # print(f"\n生成 {len(cols_to_categorize)-r} 个维度是'all'的子总计:")
    for grp_cols in combinations(cols_to_categorize, r=r):
        # 确定哪些列将作为分组键，哪些列将是'all'
        grouping_keys = list(grp_cols)
        all_cols = [col for col in cols_to_categorize if col not in grouping_keys]

        if not grouping_keys: # 如果没有分组键，表示计算所有数据的总计
            subtotal_df = df_categorized['value'].agg(['sum']).reset_index()
            subtotal_df = subtotal_df.rename(columns={'sum': 'value'})
            for col in cols_to_categorize:
                subtotal_df[col] = 'all'
        else:
            # 计算当前分组键下的值总和
            subtotal_df = df_categorized.groupby(grouping_keys, as_index=False, observed=True)['value'].sum()
            # 将非分组键的列填充为'all'
            for col in all_cols:
                subtotal_df[col] = 'all'

        # 确保subtotal_df的列顺序和类型与原始df_categorized一致
        for col in cols_to_categorize:
            if col not in subtotal_df.columns:
                subtotal_df[col] = 'all' # 如果某个维度完全是'all'，则添加该列并赋值'all'
            subtotal_df[col] = subtotal_df[col].astype(cats[col])

        data_for_pivot.append(subtotal_df[cols_to_categorize + ['value']])

# 合并所有数据和子总计
out_df = pd.concat(data_for_pivot, ignore_index=True)

# 再次确保所有列都是正确的CategoricalDtype
for col in cols_to_categorize:
    out_df[col] = out_df[col].astype(cats[col])

print("\n包含所有子总计的预处理DataFrame:")
print(out_df)
print(out_df.dtypes)

# 使用pivot_table进行最终透视
# index: 作为行索引的列
# columns: 作为列索引的列，将形成多级标题
# values: 聚合的数值列
# aggfunc: 聚合函数，例如'sum', 'median', 'mean'等
# fill_value: 填充没有数据点的单元格的值
# observed=False: 确保pivot_table显示所有CategoricalDtype的类别，包括'all'，即使它们在数据中没有实际出现
final_pivot_table = out_df.pivot_table(
    index='date', 
    columns=['subject', 'animal', 'colors'], 
    values='value', 
    aggfunc='sum', # 聚合函数可以根据需求选择，例如'median'
    fill_value=0,  # 填充没有数据的组合，通常用0或NaN
    observed=False # 关键参数，确保显示所有分类，包括'all'
)

print("\n最终的多级子总计透视表:")
print(final_pivot_table)