保留其他列不变的同时对多列执行Unpivot操作

Excel Power Query 操作步骤 #

• 选中目标数据表，点击「数据」选项卡 →「从表格/区域」，进入Power Query编辑器
• 若有需要保留的固定列，先选中这些列；右键点击任意选中列，选择「逆透视列」→「逆透视其他列」，自动将剩余列转换为「属性」和「值」两列
• 拆分「属性」列：选中该列，点击「转换」→「拆分列」→「按分隔符」，选择列名中的分隔符（如“-”），拆分为「指标名称」和「季度」两列
• 调整列顺序后，点击「关闭并上载」即可生成目标格式数据

SQL 实现（以SQL Server为例） #

通过UNPIVOT完成逆透视，结合字符串拆分提取指标和季度：

SELECT 
    保留列1, 保留列2, -- 替换为实际需保留的列名
    LEFT(pivot_col, CHARINDEX('-', pivot_col) - 1) AS 指标名称,
    RIGHT(pivot_col, LEN(pivot_col) - CHARINDEX('-', pivot_col)) AS 季度,
    value AS 数值
FROM 
    原数据表名
UNPIVOT (
    value FOR pivot_col IN (
        [指标1-2022Q1], [指标1-2022Q2], -- 替换为所有需逆透视的列名
        [指标2-2022Q1], [指标2-2022Q2],
        -- 依次列出所有目标列，列数量较多时可使用动态SQL自动生成
    )
) AS unpvt;

Python Pandas 实现 #

利用melt函数快速逆透视，再拆分列：

import pandas as pd

# 读取原数据
df = pd.read_excel('原数据表.xlsx')

# 指定保留列和需逆透视的列
id_columns = ['保留列1', '保留列2']  # 替换为实际保留列
pivot_columns = [col for col in df.columns if '-' in col]

# 执行逆透视
melted_data = df.melt(
    id_vars=id_columns,
    value_vars=pivot_columns,
    var_name='指标_季度',
    value_name='数值'
)

# 拆分指标名称和季度
melted_data[['指标名称', '季度']] = melted_data['指标_季度'].str.split('-', expand=True)

# 整理最终结果
final_data = melted_data[id_columns + ['指标名称', '季度', '数值']].drop('指标_季度', axis=1)

# 保存到文件
final_data.to_excel('目标数据表.xlsx', index=False)

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