Pandas实现列头转行值、拼接新列并重构DataFrame

你有包含多个工作表的Excel工作簿，单表初始结构如下：

[ID]        [Date]              [TEST_A]       [TEST_B]       [TEST_C]
ID_1234     13/06/2017 11:00    1194.256258    1287.016744    1343.434
ID_1234     13/06/2017 12:00    1194.266828    1287.16688     1463.2352
...

各工作表存在以下差异：

• [ID]列取值不同
• 测试列数量不固定：最少仅含TEST_A，最多到TEST_E
• 测试列排列顺序不固定

需要将所有工作表统一转换为4列结构：

[ID]       [ID_TEST]           [Date]               [Result]
ID_1234    ID_1234-TEST_A      13/06/2017 11:00     1194.256258
ID_1234    ID_1234-TEST_A      13/06/2017 12:00     1194.266828
ID_1234    ID_1234-TEST_B      13/06/2017 11:00     1287.016744
ID_1234    ID_1234-TEST_B      13/06/2017 12:00     1287.1688
ID_1234    ID_1234-TEST_C      13/06/2017 11:00     1343.434
ID_1234    ID_1234-TEST_C      13/06/2017 12:00     1463.2352

其中ID_TEST为ID值与对应测试列名的拼接值，Result存储对应测试列的原始数值。
之前尝试的df.apply(lambda col: col.name +" "+ col.astype(str) )会对所有列生效，无法仅针对TEST列处理。

不需要用pivot_table，用pandas原生的宽表转长表方法melt即可适配所有列数不固定、列顺序不一致的工作表，核心逻辑是自动识别测试列、统一做长宽转换、再拼接生成目标列，步骤如下：

1. 自动识别所有TEST_开头的测试列，无需手动写死列名，兼容不同工作表的列差异
2. 用melt把宽格式的测试列转成长格式，固定ID、Date为不转换的标识列
3. 拼接生成ID_TEST列，调整列顺序得到最终结构

单工作表转换代码：

import pandas as pd

# 读取单个工作表
df = pd.read_excel("你的文件路径.xlsx", sheet_name="对应工作表名")

# 自动识别所有TEST开头的测试列，兼容任意数量、任意排列顺序的测试列
test_cols = [col for col in df.columns if col.startswith("TEST_")]

# 宽格式转长格式
df_long = df.melt(
    id_vars=["ID", "Date"],  # 保留不做转换的固定列
    value_vars=test_cols,    # 指定需要转成行的测试列
    var_name="test_type",    # 转换后存储原测试列名的临时列
    value_name="Result"      # 转换后存储测试值的列，对应目标结构的Result列
)

# 拼接生成ID_TEST列
df_long["ID_TEST"] = df_long["ID"] + "-" + df_long["test_type"]

# 调整为目标要求的4列顺序，删除临时列
df_result = df_long[["ID", "ID_TEST", "Date", "Result"]]

批量处理整个工作簿所有工作表的代码：

# 读取工作簿下所有工作表，返回字典格式：key为工作表名，value为对应DataFrame
all_sheets = pd.read_excel("你的文件路径.xlsx", sheet_name=None)
result_list = []

for sheet_name, df in all_sheets.items():
    test_cols = [col for col in df.columns if col.startswith("TEST_")]
    df_long = df.melt(
        id_vars=["ID", "Date"], 
        value_vars=test_cols, 
        var_name="test_type", 
        value_name="Result"
    )
    df_long["ID_TEST"] = df_long["ID"] + "-" + df_long["test_type"]
    result_list.append(df_long[["ID", "ID_TEST", "Date", "Result"]])

# 合并所有工作表的转换结果
final_df = pd.concat(result_list, ignore_index=True)

之前用apply全局处理的问题在于没有指定列范围，就算沿用apply思路，也只需要遍历识别到的test_cols单独处理即可，但melt是针对这类宽转长场景的原生实现，性能和代码简洁度都更优。

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