如何用Pandas/SQLite3转换数据格式以按年份对比薪资涨幅与CPI？

我有一份存储在DataFrame中的数据，格式如下：

目前我用sqlite3读取数据并通过matplotlib绘图，想要对比员工薪资涨幅与年度CPI（在柱状图中展示2019-2022各年份各地区的百分比数据及对应年份的CPI），需要将数据转换为以下格式：

请问能否通过pandas查询或sqlite3轻松实现该数据格式转换？

一、用Pandas实现转换 #

这是最直接高效的方式，通过melt+pivot两步即可完成：

1. 将宽表转为长表
   用melt拆分年份列，提取年份信息：

import pandas as pd

# 假设原始DataFrame名为df
melted_df = df.melt(
    id_vars=['Location'],
    value_vars=['2019_perc', '2020_perc', '2021_perc', '2022_perc'],
    var_name='Year',
    value_name='Percentage'
)
# 移除年份后缀，提取纯年份数字
melted_df['Year'] = melted_df['Year'].str.replace('_perc', '')

2. 将长表转回目标宽表
   用pivot重新排列列与行的结构：

result_df = melted_df.pivot(index='Year', columns='Location', values='Percentage').reset_index()
# 调整列顺序，与目标格式完全对齐（可选）
result_df = result_df[['Year', 'Crawford', 'Deck', 'Peoria']]

执行后就能得到所需格式，Type列因转换后无需求可直接忽略。

二、用SQLite3实现转换 #

如果数据已存入SQLite数据库，可通过SQL查询直接生成目标格式，核心是UNION ALL拆分年份+CASE WHEN条件聚合：

假设数据库表名为salary_data，执行以下SQL语句：

SELECT
    '2019' AS Year,
    MAX(CASE WHEN Location = 'Crawford' THEN 2019_perc END) AS Crawford,
    MAX(CASE WHEN Location = 'Deck' THEN 2019_perc END) AS Deck,
    MAX(CASE WHEN Location = 'Peoria' THEN 2019_perc END) AS Peoria
FROM salary_data
UNION ALL
SELECT
    '2020' AS Year,
    MAX(CASE WHEN Location = 'Crawford' THEN 2020_perc END) AS Crawford,
    MAX(CASE WHEN Location = 'Deck' THEN 2020_perc END) AS Deck,
    MAX(CASE WHEN Location = 'Peoria' THEN 2020_perc END) AS Peoria
FROM salary_data
UNION ALL
SELECT
    '2021' AS Year,
    MAX(CASE WHEN Location = 'Crawford' THEN 2021_perc END) AS Crawford,
    MAX(CASE WHEN Location = 'Deck' THEN 2021_perc END) AS Deck,
    MAX(CASE WHEN Location = 'Peoria' THEN 2021_perc END) AS Peoria
FROM salary_data
UNION ALL
SELECT
    '2022' AS Year,
    MAX(CASE WHEN Location = 'Crawford' THEN 2022_perc END) AS Crawford,
    MAX(CASE WHEN Location = 'Deck' THEN 2022_perc END) AS Deck,
    MAX(CASE WHEN Location = 'Peoria' THEN 2022_perc END) AS Peoria
FROM salary_data;

该查询会逐年份提取各地区数据，再通过条件聚合将不同Location的值映射到对应列，最后合并成目标结果集。

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