如何用自定义条件生成Pandas透视表？补全缺失观测问题

1. 生成所有索引组合（笛卡尔积） #

先提取三个字段的唯一值，用pd.MultiIndex.from_product直接生成所有可能的组合，避免手动遍历：

import pandas as pd

d = {'cat' : ['One','One','One','Two','Two'],
     'cha': ['A', 'A', 'B', 'B', 'C'], 
     'fruit': ['Banana', 'Apple', 'Orange', 'Banana', 'Orange'], 
     'number': [7, 8, 12, 1, 11]}

data = pd.DataFrame(d)

# 获取三个字段的唯一值集合
cats = data['cat'].unique()
chas = data['cha'].unique()
fruits = data['fruit'].unique()

# 生成所有cat/cha/fruit的组合
all_index_combs = pd.MultiIndex.from_product([cats, chas, fruits], names=['cat', 'cha', 'fruit'])

2. 生成含全组合的透视表 #

先做常规透视表，再用全组合索引重新索引，缺失值填0：

# 基础透视表（仅包含存在的组合）
base_pivot = pd.pivot_table(data, values='number', index=['cat', 'cha', 'fruit'], aggfunc='sum')

# 重新索引补全所有组合，缺失值填充为0
full_pivot = base_pivot.reindex(all_index_combs, fill_value=0).reset_index()

3. 替代方案：用合并方式生成全组合表 #

如果不想用reindex，也可以先创建全组合空表，再和原数据合并：

# 把全组合转为DataFrame
full_combs_df = pd.DataFrame(all_index_combs.tolist(), columns=['cat', 'cha', 'fruit'])

# 左连接原数据，填充缺失的number为0
merged_df = pd.merge(full_combs_df, data, on=['cat', 'cha', 'fruit'], how='left')
merged_df['number'] = merged_df['number'].fillna(0)

# 按索引组合聚合（和透视表效果一致）
full_pivot_alt = merged_df.groupby(['cat', 'cha', 'fruit'])['number'].sum().reset_index()

4. 添加映射列的两种方法 #

假设你要给fruit字段加对应的颜色映射，两种实现方式：

方法1：用map快速映射 #

适合映射关系简单的场景：

color_mapping = {'Banana': 'Yellow', 'Apple': 'Red', 'Orange': 'Orange'}
full_pivot['fruit_color'] = full_pivot['fruit'].map(color_mapping)

方法2：用merge关联映射表 #

适合映射关系复杂、存于单独表的场景：

# 映射表DataFrame
color_table = pd.DataFrame([
    ('Banana', 'Yellow'),
    ('Apple', 'Red'),
    ('Orange', 'Orange')
], columns=['fruit', 'fruit_color'])

# 合并到透视表
full_pivot_with_color = pd.merge(full_pivot, color_table, on='fruit', how='left')

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