使用Pandas实现分组下的Melt、Pivot与交叉表格式问题求助

需要将数据集的部分字段值转为列头生成交叉表，使用Pandas的melt和pivot_table处理后出现值缺失问题。相关信息如下：

原始数据 #

year    qtr     ID  type    growth  re     nondd_re   se_re or
2024    2024Q1  NY  aa      3.18    1.14    0         0     0
2024    2024Q2  NY  aa      2.1     1.14    0         0     0
2024    2024Q1  NY  dd      6.26    3.07    3.07      0     0
2024    2024Q2  NY  dd      4.13    3.07    3.07      0     0
2024    2024Q1  CA  aa      0       0       0         0     0
2024    2024Q2  CA  aa      0.03    0       0         0     0
2024    2024Q1  CA  dd      0       0       0         0     0
2024    2024Q2  CA  dd      0.06    0       0         0     0

期望输出 #

ID  metric      type  2024Q1  2024Q2
NY  growth      dd    6.26    4.13
NY  nondd_re    dd    3.07    3.07
NY  se_re       dd    0       0
NY  or          dd    0       0
NY  re          dd    3.07    3.07
NY  growth      aa    3.18    2.1
NY  nondd_re    aa    0       0
NY  se_re       aa    0       0
NY  or          aa    0       0
NY  re          aa    1.14    1.14
CA  growth      dd    0       0.06
CA  nondd_re    dd    0       0
CA  se_re       dd    0       0
CA  or          dd    0       0
CA  re          dd    0       0
CA  growth      aa    0       0.03
CA  nondd_re    aa    0       0
CA  se_re       aa    0       0
CA  or          aa    0       0
CA  re          aa    0       0 

当前代码 #

# Melt the dataframe to transform metrics columns into rows
melted_df = df.melt(id_vars=["year", "qtr", "ID", "type"], var_name="type", value_name="value")
# Pivot the melted dataframe
pivot_df = melted_df.pivot_table(index=["ID","type"], columns="qtr", values="value", fill_value=0)
# Reset index to turn multi-index into columns
pivot_df = pivot_df.reset_index()

错误原因 #

1. 字段名冲突：melt时var_name设为"type"，与原数据中表示aa/dd的type字段重名，导致后续索引混乱。
2. 行索引缺失：pivot_table的index仅设置了["ID","type"]，但这里的type已经被覆盖为指标名（如growth），丢失了原数据中aa/dd的维度，无法生成期望的层级行结构。

修正后的代码 #

import pandas as pd

# 读取原始数据（示例）
data = [
    [2024, "2024Q1", "NY", "aa", 3.18, 1.14, 0, 0, 0],
    [2024, "2024Q2", "NY", "aa", 2.1, 1.14, 0, 0, 0],
    [2024, "2024Q1", "NY", "dd", 6.26, 3.07, 3.07, 0, 0],
    [2024, "2024Q2", "NY", "dd", 4.13, 3.07, 3.07, 0, 0],
    [2024, "2024Q1", "CA", "aa", 0, 0, 0, 0, 0],
    [2024, "2024Q2", "CA", "aa", 0.03, 0, 0, 0, 0],
    [2024, "2024Q1", "CA", "dd", 0, 0, 0, 0, 0],
    [2024, "2024Q2", "CA", "dd", 0.06, 0, 0, 0, 0]
]
df = pd.DataFrame(data, columns=["year", "qtr", "ID", "type", "growth", "re", "nondd_re", "se_re", "or"])

# 1. Melt转换：明确指定要转的指标列，避免字段名冲突
melted_df = df.melt(
    id_vars=["year", "qtr", "ID", "type"],  # 保留的维度列
    value_vars=["growth", "re", "nondd_re", "se_re", "or"],  # 需要转为行的指标列
    var_name="metric",  # 新的指标列名，替换原冲突的"type"
    value_name="value"
)

# 2. Pivot生成交叉表：行索引包含ID、metric、原type（aa/dd）
pivot_df = melted_df.pivot_table(
    index=["ID", "metric", "type"],
    columns="qtr",
    values="value",
    fill_value=0,
    aggfunc="first"  # 确保每个组合取唯一值，避免聚合问题
)

# 3. 重置索引并调整列顺序
pivot_df = pivot_df.reset_index()
pivot_df = pivot_df[["ID", "metric", "type", "2024Q1", "2024Q2"]]

print(pivot_df)

关键说明 #

• melt时用metric作为指标列名，避免与原type字段冲突；明确指定value_vars确保只转换需要的指标列。
• pivot_table的index包含ID、metric（指标名）、type（aa/dd）三个维度，匹配期望输出的行结构。
• 添加aggfunc="first"防止因重复值触发聚合，保证每个维度组合对应唯一值。

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