如何在Python中重构Excel宽表数据？使用melt函数遇问题

问题分析 #

你的原表结构是Date列加上46组id_xxx和重复的mprice列，直接用melt会因为无法关联成对的id与mprice，导致生成大量null值和多余列。核心是要保证每组id_xxx和对应的mprice一一配对后再转长表。

方法一：分组遍历拼接（直观易理解） #

先读取数据，然后将每一组id_xxx和对应的mprice列单独提取、重命名，最后拼接所有分组结果：

import pandas as pd

# 读取Excel文件，pandas会自动给重复的mprice列添加后缀（如mprice.1、mprice.2）
df = pd.read_excel("your_large_file.xlsx")

# 筛选所有id列和对应的mprice列
id_cols = [col for col in df.columns if col.startswith("id_")]
mprice_cols = [col for col in df.columns if "mprice" in col]

# 初始化空的结果表
long_df = pd.DataFrame()

# 遍历每一组id和mprice列，处理后拼接
for id_col, price_col in zip(id_cols, mprice_cols):
    # 提取当前组的Date、id、mprice，并重命名列
    temp = df[["Date", id_col, price_col]].rename(columns={id_col: "id", price_col: "mprice"})
    long_df = pd.concat([long_df, temp], ignore_index=True)

# 可选：删除id或mprice为空的行（如果原表存在缺失值）
long_df = long_df.dropna(subset=["id", "mprice"])

方法二：使用wide_to_long（更简洁） #

利用pandas专门处理成对宽表的wide_to_long函数，需要先给重复的mprice列重命名，让其与id_xxx的后缀对应：

import pandas as pd

df = pd.read_excel("your_large_file.xlsx")

# 提取所有id列的后缀（如id_m00的后缀是m00）
id_suffixes = [col.split("_")[1] for col in df.columns if col.startswith("id_")]
# 获取所有mprice列，按顺序重命名为mprice_xxx（与id后缀对应）
mprice_cols = [col for col in df.columns if "mprice" in col]
for idx, col in enumerate(mprice_cols):
    df.rename(columns={col: f"mprice_{id_suffixes[idx]}"}, inplace=True)

# 使用wide_to_long转长表
long_df = pd.wide_to_long(
    df,
    stubnames=["id", "mprice"],  # 成对列的前缀
    i="Date",                    # 索引列（保持不变的列）
    j="group",                   # 生成的分组列（后续可删除）
    sep="_",                     # 列名的分隔符
    suffix=".+"                  # 匹配后缀的正则
).reset_index()

# 保留需要的三列，删除多余的group列
long_df = long_df[["Date", "id", "mprice"]].dropna(subset=["id", "mprice"])

为什么直接用melt会出错？ #

直接调用melt时，如果把所有id_xxx和mprice列都设为value_vars，pandas无法识别哪组id对应哪组mprice，会生成大量交叉行：比如一行是Date+id_m00值+null，另一行是Date+null+mprice值，最终出现大量null和无效行。

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