如何对Pandas DataFrame执行melt与unpivot（逆透视）操作？

初始数据 #

我的初始DataFrame代码如下：

import pandas as pd

columns = ["ID", "Position1", "Position2", "Position3", "Duration1", "Duration2", "Duration3", "Costs1", "Costs2", "Costs3"]
values1 = [1, 1, "null", 2, 10, "null", 20, 500, "null", 1500]
values2 = [2, 2, 3, 1, 55, 44, 22, 1000, 2000, 4000]
df = pd.DataFrame([values1, values2], columns=columns)

目标结构 #

我想要得到的DataFrame结构：

已完成步骤 #

我已经执行了以下代码，但不知道后续操作：

df_melted = df.melt(id_vars="ID")
df_melted = df_melted[df_melted.value != "null"]
df_melted["variable"] = df_melted["variable"].str.replace("(\d+)", "")

执行后得到的结果：

问题分析 #

之前的操作把变量名里的序号（比如Position1里的1）去掉了，导致无法关联同一个序号下的Position、Duration、Costs——比如Position1应该对应Duration1和Costs1，必须保留序号才能建立正确的对应关系。

完整解决代码 #

import pandas as pd

# 初始数据
columns = ["ID", "Position1", "Position2", "Position3", "Duration1", "Duration2", "Duration3", "Costs1", "Costs2", "Costs3"]
values1 = [1, 1, "null", 2, 10, "null", 20, 500, "null", 1500]
values2 = [2, 2, 3, 1, 55, 44, 22, 1000, 2000, 4000]
df = pd.DataFrame([values1, values2], columns=columns)

# 1. 宽表转长表，保留原变量名
df_melted = df.melt(id_vars="ID", var_name="category", value_name="value")
# 2. 过滤null值
df_melted = df_melted[df_melted["value"] != "null"]
# 3. 拆分变量名为指标类型（Position/Duration/Costs）和序号（1/2/3）
df_melted[["metric", "seq"]] = df_melted["category"].str.extract(r"(\D+)(\d+)")
# 4. 将value转为数值类型
df_melted["value"] = pd.to_numeric(df_melted["value"])
# 5. 透视表，按ID和序号分组，将指标类型转为列
final_df = df_melted.pivot(index=["ID", "seq"], columns="metric", values="value").reset_index(drop=False)
# 6. 整理列顺序，去掉序号列，过滤可能的缺失行
final_df = final_df[["ID", "Position", "Duration", "Costs"]].dropna()
# 7. 按ID和Position排序，匹配目标结构
final_df = final_df.sort_values(by=["ID", "Position"]).reset_index(drop=True)

print(final_df)

执行后得到的结果与目标结构一致：

ID  Position  Duration  Costs
0   1         1        10    500
1   1         2        20   1500
2   2         1        22   4000
3   2         2        55   2000
4   2         3        44   1000

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