Pandas反向聚合实现复杂长表转置：缺失列问题求助

我有一份数据集，需要将聚合值拆分到独立行中，并按类别分组执行转置操作。当前代码无法捕获所有输出列，恳请提供改进建议。

更新后的数据 #

Period      Date        Area    BB stat AA stat CC stat DD stat BB test AA test CC test DD test BB re   AA re   CC re BB test2  AA test2 CC test2  DD test2                                                
8/1/2016    9/1/2016    NY      5       5       5               1       1       1               0       0       0     0          0       0         0
9/1/2016    10/1/2016   NY      6       6       6               4       4       4               0       0       0     0          0       0         0
8/1/2016    9/1/2016    CA      2       2       2               4       4       4               0       0       0     0          0       0         0
9/1/2016    10/1/2016   CA      1       1       1              -2      -2      -2               0       0       0     0          0       0         0

期望输出 #

Period      Date            Area    stat    test    type    re  test2
8/1/2016    9/1/2016        NY      5       1       BB      0   0
9/1/2016    10/1/2016       NY      6       4       BB      0   0
8/1/2016    9/1/2016        NY      5       1       AA      0   0   
9/1/2016    10/1/2016       NY      6       4       AA      0   0   
8/1/2016    9/1/2016        NY      5       1       CC      0   0
9/1/2016    10/1/2016       NY      6       4       CC      0   0   
8/1/2016    9/1/2016        NY      0       0       DD      0   0
9/1/2016    10/1/2016       NY      0       0       DD      0   0
8/1/2016    9/1/2016        CA      2       4       BB      0   0
9/1/2016    10/1/2016       CA      1       -2      BB      0   0
8/1/2016    9/1/2016        CA      2       4       AA      0   0
9/1/2016    10/1/2016       CA      1       -2      AA      0   0
8/1/2016    9/1/2016        CA      2       4       CC      0   0
9/1/2016    10/1/2016       CA      1       -2      CC      0   0
8/1/2016    9/1/2016        CA      0       0       DD      0   0
9/1/2016    10/1/2016       CA      0       0       DD      0   0

当前实现代码 #

value_vars = ["BB stat",    "AA stat",  "CC stat",  "DD stat",  "BB test",
"AA test",  "CC test",  "DD test",  "BB re",    "AA re",    "CC re"]
df = df.melt(id_vars=["Period", "Date", "Area"], value_vars=value_vars)


temp_df = df.variable.str.split("_", 1, expand=True)
df["type"] = temp_df[0]
df["name"] = temp_df[1]
df = df.drop(columns=["variable"])
first_half = df.iloc[:len(df)//2]
second_half = df.iloc[len(df)//2:]
df = pd.merge(first_half, second_half, on=["Period", "Date", "Area", "type"], suffixes=("_1", "_2"))


df.rename(columns = {'value_3':'stat''value_2':'test', 'value_1':'re'}, inplace = True)
df.drop(columns=["name_1", "name_2"], inplace=True)
df = df[[ "Period",     "Date",         "Area", "stat", "test", "type", "re"    ]]



df.sort_values(["Area", "type"], ascending=False, inplace=True)
df.to_markdown()

代码问题分析与改进方案 #

原代码存在的问题 #

1. value_vars遗漏列：未包含BB test2、AA test2等test2相关列，导致最终输出缺少test2字段
2. 列拆分逻辑错误：原列名是空格分隔（如BB stat），但代码用下划线_拆分，无法正确提取type和指标名称
3. 数据拆分方法不严谨：用iloc[:len(df)//2]硬拆分数据的方式依赖数据顺序，容易出错且无法处理所有指标
4. 重命名语法错误：{'value_3':'stat''value_2':'test'}缺少逗号，属于语法错误，且列名对应逻辑混乱

改进后的代码实现 #

import pandas as pd

# 读取原始数据（示例，实际根据你的数据源调整）
data = [
    ["8/1/2016", "9/1/2016", "NY", 5, 5, 5, None, 1, 1, 1, None, 0, 0, 0, 0, 0, 0, 0],
    ["9/1/2016", "10/1/2016", "NY", 6, 6, 6, None, 4, 4, 4, None, 0, 0, 0, 0, 0, 0, 0],
    ["8/1/2016", "9/1/2016", "CA", 2, 2, 2, None, 4, 4, 4, None, 0, 0, 0, 0, 0, 0, 0],
    ["9/1/2016", "10/1/2016", "CA", 1, 1, 1, None, -2, -2, -2, None, 0, 0, 0, 0, 0, 0, 0]
]
columns = ["Period", "Date", "Area", "BB stat", "AA stat", "CC stat", "DD stat", "BB test", "AA test", "CC test", "DD test", "BB re", "AA re", "CC re", "BB test2", "AA test2", "CC test2", "DD test2"]
df = pd.DataFrame(data, columns=columns)

# 1. 重塑数据：将所有指标列转成行
df_melted = df.melt(id_vars=["Period", "Date", "Area"], var_name="variable", value_name="value")

# 2. 拆分variable列：提取type（BB/AA/CC/DD）和指标名称（stat/test/re/test2）
# 处理test2的特殊情况，先替换" test2"为"_test2"再拆分
df_melted["variable"] = df_melted["variable"].str.replace(" test2", "_test2")
df_melted[["type", "metric"]] = df_melted["variable"].str.split(" ", 1, expand=True)
# 把_test2还原回test2
df_melted["metric"] = df_melted["metric"].str.replace("_test2", "test2")

# 3. 转置：将metric转成列，填充空值为0
df_pivoted = df_melted.pivot_table(
    index=["Period", "Date", "Area", "type"],
    columns="metric",
    values="value",
    aggfunc="first"
).reset_index().fillna(0)

# 4. 调整列顺序，匹配期望输出
df_final = df_pivoted[["Period", "Date", "Area", "stat", "test", "type", "re", "test2"]]

# 5. 按Area和type排序（和期望输出一致）
df_final = df_final.sort_values(["Area", "type"], ascending=[False, True])

# 查看结果
print(df_final.to_markdown(index=False))

改进说明 #

• 覆盖了所有列：包括test2相关字段，确保输出包含所有期望列
• 正确拆分列名：通过替换test2的空格为下划线，避免拆分错误，再还原，保证type和metric提取准确
• 用pivot_table重塑数据：逻辑清晰，无需硬拆分数据，自动处理不同type的指标匹配
• 填充空值为0：符合期望输出中DD的stat、test字段为0的要求
• 排序逻辑匹配期望输出：按Area降序（NY在前，CA在后），type升序（AA/BB/CC/DD）

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