Pandas：堆叠多层索引表头时排除首列并保留Store列

问题场景 #

我将CSV文件读取为带多层索引表头的Pandas DataFrame，示例代码与结果如下：

import pandas as pd
df = pd.DataFrame([[1,5,5,2,3], [2,10,4,20,3]])
df.columns = pd.MultiIndex.from_tuples((("1/1/2024","Store"), ("product code 1", "quantity onhand"), ("product code 1",'quantity sold'), ("product code 2", "quantity onhand"), ("product code 2",'quantity sold')))
print(df)

输出表格：

需要去掉首列的日期层级，保留Store列，并将数据转换为以下目标格式：

data = [[1,'product code 1',5,5],[1,'product code 2',3,2],[2,'product code 1',4,10],[2,'product code 2',3,20]]
columns = ['Store','Code','quantity sold','quantity onhand']
df2 = pd.DataFrame(data, columns=columns)
print(df2)

目标表格：

试过stack操作，但想确认能否通过pivot实现该需求。

解决方案 #

可以实现，推荐两种高效方法，其中第二种结合了pivot操作：

方法一：Stack层级直接整理 #

先提取Store列并去除日期层级，再堆叠剩余列的索引层级：

import pandas as pd

# 示例数据
df = pd.DataFrame([[1,5,5,2,3], [2,10,4,20,3]])
df.columns = pd.MultiIndex.from_tuples((("1/1/2024","Store"), ("product code 1", "quantity onhand"), ("product code 1",'quantity sold'), ("product code 2", "quantity onhand"), ("product code 2",'quantity sold')))

# 提取Store列，移除日期层级
store_col = df.pop(("1/1/2024", "Store"))
df = df.assign(Store=store_col)

# 堆叠列索引，重塑为目标格式
df_result = df.set_index("Store").stack(level=0).reset_index()
df_result.columns = ["Store", "Code", "quantity onhand", "quantity sold"]
# 调整列顺序匹配目标
df_result = df_result[["Store", "Code", "quantity sold", "quantity onhand"]]
print(df_result)

方法二：Melt+Pivot实现 #

先将数据转为长格式，再通过pivot转换为目标结构：

import pandas as pd

# 示例数据
df = pd.DataFrame([[1,5,5,2,3], [2,10,4,20,3]])
df.columns = pd.MultiIndex.from_tuples((("1/1/2024","Store"), ("product code 1", "quantity onhand"), ("product code 1",'quantity sold'), ("product code 2", "quantity onhand"), ("product code 2",'quantity sold')))

# 提取Store列，移除日期层级
store_col = df.pop(("1/1/2024", "Store"))
df = df.assign(Store=store_col)

# Melt转为长格式，再Pivot重塑
df_melt = df.set_index("Store").stack([0,1]).reset_index()
df_melt.columns = ["Store", "Code", "Metric", "Value"]
df_result = df_melt.pivot(index=["Store", "Code"], columns="Metric", values="Value").reset_index()
# 移除列名层级
df_result.columns.name = None
# 调整列顺序匹配目标
df_result = df_result[["Store", "Code", "quantity sold", "quantity onhand"]]
print(df_result)

两种方法都能输出符合要求的结果。

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