如何在Python Pandas中基于二进制与分类列生成指定透视表？

我有如下Pandas DataFrame：

数据类型 #

• ID：数值型
• U1、U2、U3：数值型（二进制0/1）
• CP：字符串型（区间）

原始DataFrame #

需要生成一个透视表，包含以下字段：

• COUNT_CP：U列与CP区间组合对应的ID数量（例如U1=1且CP='10-20'时为3）
• COUNT_U：各U列中值为1的ID总数（例如U1列共有3个1）
• 多级索引idx（第一级为U列名，第二级为格式化后的CP区间，如CP_10_20）

目标透视表结构 #

实现步骤 #

1. 构造原始DataFrame #

先把示例数据转换成可直接运行的代码：

import pandas as pd

data = {
    'ID': [111, 222, 333, 444, 555],
    'U1': [1, 1, 0, 0, 1],
    'U2': [1, 0, 1, 1, 0],
    'U3': [0, 1, 0, 1, 0],
    'CP': ['10-20', '10-20', '20-30', '40-50', '10-20']
}
df = pd.DataFrame(data)

2. 定义全量CP区间 #

目标结果包含原数据中没有的30-40，先列出所有需要的区间：

all_cp_intervals = ['10-20', '20-30', '30-40', '40-50']

3. 宽表转长表 #

将U1/U2/U3列转为「列名-值」的长格式，只保留值为1的行（我们只关心U列=1的情况）：

melted_df = df.melt(id_vars=['ID', 'CP'], var_name='idx', value_name='is_one')
melted_df = melted_df[melted_df['is_one'] == 1]

4. 生成全量组合 #

用笛卡尔积生成U列和CP区间的所有可能组合，确保没有缺失行：

u_cols = ['U1', 'U2', 'U3']
# 生成多级索引的全量组合
full_combinations = pd.MultiIndex.from_product([u_cols, all_cp_intervals], names=['idx', 'CP'])
full_combinations_df = full_combinations.to_frame(index=False)

5. 计算COUNT_CP #

统计每个U列+CP区间的ID数量，缺失组合填充0，并格式化CP列名称：

# 统计原始数据中的组合计数
count_cp = melted_df.groupby(['idx', 'CP'])['ID'].count().reset_index(name='COUNT_CP')
# 合并到全量组合，缺失值填充0
result = pd.merge(full_combinations_df, count_cp, on=['idx', 'CP'], how='left').fillna(0)
# 格式化CP为目标样式（10-20 → CP_10_20）
result['CP'] = 'CP_' + result['CP'].str.replace('-', '_')

6. 计算COUNT_U并合并 #

统计每个U列中值为1的总ID数，合并到结果中：

count_u = melted_df.groupby('idx')['ID'].nunique().reset_index(name='COUNT_U')
result = pd.merge(result, count_u, on='idx', how='left')

7. 整理索引与显示格式 #

调整为目标的多级索引结构，并优化显示效果：

# 设置多级索引
result = result.set_index(['idx', 'CP'])
# 重命名第二级索引名为idx
result.index = result.index.set_names(['idx', 'idx'])
# 重置索引并处理重复值显示（让第一级idx重复项显示为空）
result = result.reset_index()
result['idx'] = result['idx'].mask(result['idx'].duplicate(), '')

最终结果 #

运行上述代码后，打印result即可得到目标结构：

print(result.to_string(index=False))

输出：

idx       idx  COUNT_CP  COUNT_U
  U1  CP_10_20       3.0        3
      CP_20_30       0.0        3
      CP_30_40       0.0        3
      CP_40_50       0.0        3
  U2  CP_10_20       2.0        3
      CP_20_30       0.0        3
      CP_30_40       0.0        3
      CP_40_50       1.0        3
  U3  CP_10_20       1.0        2
      CP_20_30       0.0        2
      CP_30_40       0.0        2
      CP_40_50       1.0        2

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