如何转换/透视时间序列数据？转置数据并填充缺失年份为空单元格

我有一个包含时间序列数据的表格：

希望转换为以下形式（缺失年份留空）：

请问如何转置数据并将缺失年份填充为空单元格？

下面提供几种不同工具的实现方法，按需选择：

1. Excel/Google Sheets 操作 #

• 选中原始数据区域（包含表头），点击数据选项卡中的透视表（或数据透视表）。
• 在透视表设置中：
  • 将A列（ID字段）拖到行区域
  • 将B列（年份字段）拖到列区域
  • 将C列（数值字段）拖到值区域，确保值字段的汇总方式是求和（无重复年份时，求和与直接显示值效果一致）
• 生成透视表后，年份会自动按顺序排列；若缺失年份显示0，可通过单元格格式设置将0替换为空。

2. Python Pandas 实现 #

通过pivot_table方法完成行列转换，缺失值填充为空字符串：

import pandas as pd

# 构造原始数据DataFrame
df = pd.DataFrame({
    'A': ['ID1', 'ID1', 'ID1', 'ID1', 'ID2', 'ID2', 'ID2', 'ID2', 'ID2', 'ID2', 'ID3', 'ID3', 'ID3', 'ID3', 'ID3'],
    'B': [1978, 1979, 1980, 1947, 1950, 1952, 1955, 1958, 1963, 1969, 1970, 1976, 2002, 1972, 1973],
    'C': [1, 2, 5, 6, 8, 2, 3, 5, 4, 3, 9, 8, 7, 4, 6]
})

# 透视转换，缺失值填充为空
pivoted_df = df.pivot_table(index='A', columns='B', values='C', aggfunc='first').fillna('')

# 整理列名与索引
pivoted_df.columns = pivoted_df.columns.astype(str)
pivoted_df.reset_index(inplace=True)

# 输出结果
print(pivoted_df.to_string(index=False))

3. SQL 实现（以MySQL为例） #

通过CASE语句结合左连接实现行列转换：

-- 生成唯一年份列表
WITH years AS (
    SELECT DISTINCT B AS year FROM your_table
),
-- 生成唯一ID列表
ids AS (
    SELECT DISTINCT A AS id FROM your_table
)
SELECT 
    i.id,
    MAX(CASE WHEN y.year = 1947 THEN t.C END) AS '1947',
    MAX(CASE WHEN y.year = 1950 THEN t.C END) AS '1950',
    MAX(CASE WHEN y.year = 1952 THEN t.C END) AS '1952',
    MAX(CASE WHEN y.year = 1955 THEN t.C END) AS '1955',
    MAX(CASE WHEN y.year = 1958 THEN t.C END) AS '1958',
    MAX(CASE WHEN y.year = 1963 THEN t.C END) AS '1963',
    MAX(CASE WHEN y.year = 1969 THEN t.C END) AS '1969',
    MAX(CASE WHEN y.year = 1970 THEN t.C END) AS '1970',
    MAX(CASE WHEN y.year = 1972 THEN t.C END) AS '1972',
    MAX(CASE WHEN y.year = 1973 THEN t.C END) AS '1973',
    MAX(CASE WHEN y.year = 1976 THEN t.C END) AS '1976',
    MAX(CASE WHEN y.year = 1978 THEN t.C END) AS '1978',
    MAX(CASE WHEN y.year = 1979 THEN t.C END) AS '1979',
    MAX(CASE WHEN y.year = 1980 THEN t.C END) AS '1980',
    MAX(CASE WHEN y.year = 2002 THEN t.C END) AS '2002'
FROM ids i
CROSS JOIN years y
LEFT JOIN your_table t ON i.id = t.A AND y.year = t.B
GROUP BY i.id
ORDER BY i.id;

若年份数量较多，可使用动态SQL自动生成CASE语句，减少手动编写工作量。

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