如何对多列执行Pivot操作？将指定输入表转换为目标格式

输入表 #

目标表 #

方法一：Python Pandas实现 #

通过哑变量生成与条件判断结合，快速实现多列转换：

import pandas as pd

# 构造输入数据（实际场景可替换为读取文件/数据库）
df = pd.DataFrame({
    'index': [1,2,3,4],
    'income': ['income_1','income_2','income_1','income_3'],
    'Education': ['Education_0','Education_2','Education_0','Education_1'],
    'age1to_20': [1,1,1,0],
    'pcd': ['A5009','A3450','A5009','A3450']
})

# 处理income列的Pivot转换
income_cols = pd.get_dummies(df['income']).apply(lambda col: col * df['pcd']).replace('', '0')
income_cols = income_cols.rename(columns={'income_3': 'INCOME_3'})

# 处理Education列的Pivot转换
edu_cols = pd.get_dummies(df['Education']).apply(lambda col: col * df['pcd']).replace('', '0')

# 处理age1to_20列的转换
df['age1to_20'] = df.apply(lambda row: row['pcd'] if row['age1to_20'] == 1 else '0', axis=1)

# 合并所有结果并调整列顺序
final_df = pd.concat([df[['index']], income_cols, edu_cols, df[['age1to_20']]], axis=1)
final_df = final_df[['index', 'income_1', 'income_2', 'INCOME_3', 'Education_0', 'Education_1', 'Education_2', 'age1to_20']]

print(final_df)

逻辑说明 #

• 用pd.get_dummies生成分类列的哑变量，再与pcd相乘得到对应值，空值替换为'0'
• 单独处理age1to_20列，根据原值判断赋值pcd或'0'
• 合并后调整列顺序，完全匹配目标表结构

方法二：SQL实现（以MySQL为例） #

通过CASE WHEN条件语句直接生成目标列，逻辑直观：

SELECT
    `index`,
    CASE WHEN income = 'income_1' THEN pcd ELSE '0' END AS income_1,
    CASE WHEN income = 'income_2' THEN pcd ELSE '0' END AS income_2,
    CASE WHEN income = 'income_3' THEN pcd ELSE '0' END AS INCOME_3,
    CASE WHEN Education = 'Education_0' THEN pcd ELSE '0' END AS Education_0,
    CASE WHEN Education = 'Education_1' THEN pcd ELSE '0' END AS Education_1,
    CASE WHEN Education = 'Education_2' THEN pcd ELSE '0' END AS Education_2,
    CASE WHEN age1to_20 = 1 THEN pcd ELSE '0' END AS age1to_20
FROM
    input_table;

逻辑说明 #

• 对每个目标列，用CASE WHEN判断原列的取值，匹配则返回pcd，否则返回'0'
• 直接查询即可得到目标表结构，无需额外分组操作（因为index为唯一标识）

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