如何将单索引DataFrame行转为多级索引DataFrame列（通用实现）

原始数据 #

你提供的单索引DataFrame如下：

month name product category          Metric  Flipkart    Active
0      April      Accessories  Stock Quantity       NaN   1808.00
1      April      Accessories  Stock Quantity       0.0       NaN
2        May      Accessories  Sales Quantity       NaN     61.00
3        May      Accessories  Sales Quantity       0.0       NaN
4      April           Anklet  Stock Quantity       NaN  21861.75

核心通用逻辑 #

不管用什么数据处理工具，转换的核心步骤统一：

• 按指定的索引列列表分组，合并重复的索引行
• 对每组内的数值列填充缺失值（取组内非缺失值，这里因每组仅一个有效数据，用first即可）
• 将指定列设置为多级索引，保留目标数值列

下面分别用Pandas和Polars两种工具实现，均支持自定义索引列和数值列。

方案1：使用Pandas实现 #

import pandas as pd

# 构造原始数据（可替换为你的数据源）
data = {
    "month name": ["April", "April", "May", "May", "April"],
    "product category": ["Accessories", "Accessories", "Accessories", "Accessories", "Anklet"],
    "Metric": ["Stock Quantity", "Stock Quantity", "Sales Quantity", "Sales Quantity", "Stock Quantity"],
    "Flipkart": [pd.NA, 0.0, pd.NA, 0.0, pd.NA],
    "Active": [1808.00, pd.NA, 61.00, pd.NA, 21861.75]
}
df = pd.DataFrame(data)

# 通用转换函数
def pivot_to_multiindex(df, index_cols, value_cols):
    # 按索引列分组，合并行并填充缺失值
    merged_df = df.groupby(index_cols, as_index=False).first()
    # 设置多级索引
    multi_index_df = merged_df.set_index(index_cols)
    # 保留目标数值列
    return multi_index_df[value_cols]

# 自定义参数：指定索引列和数值列
result_df = pivot_to_multiindex(
    df,
    index_cols=["month name", "product category", "Metric"],
    value_cols=["Flipkart", "Active"]
)

print(result_df)

输出结果 #

Flipkart    Active
month name product category Metric                                        
April      Accessories      Stock Quantity                0.0    1808.00
May        Accessories      Sales Quantity                0.0      61.00
April      Anklet           Stock Quantity               NaN   21861.75

方案2：使用Polars实现（非Pandas方案） #

Polars是高性能列式数据处理工具，适合大数据场景：

import polars as pl

# 构造原始数据
data = {
    "month name": ["April", "April", "May", "May", "April"],
    "product category": ["Accessories", "Accessories", "Accessories", "Accessories", "Anklet"],
    "Metric": ["Stock Quantity", "Stock Quantity", "Sales Quantity", "Sales Quantity", "Stock Quantity"],
    "Flipkart": [None, 0.0, None, 0.0, None],
    "Active": [1808.00, None, 61.00, None, 21861.75]
}
df = pl.DataFrame(data)

# 通用转换函数
def pivot_to_multiindex_polars(df, index_cols, value_cols):
    # 分组聚合，取每组非缺失值
    merged_df = df.group_by(index_cols).agg(
        [pl.col(col).first().alias(col) for col in value_cols]
    )
    # 设置多级索引
    multi_index_df = merged_df.set_index(index_cols)
    return multi_index_df

# 自定义参数调用
result_pl = pivot_to_multiindex_polars(
    df,
    index_cols=["month name", "product category", "Metric"],
    value_cols=["Flipkart", "Active"]
)

print(result_pl)

输出结果 #

shape: (3, 2)
┌────────────┬──────────────────┬──────────────────┬─────────┬──────────┐
│ month name ┆ product category ┆ Metric           ┆ Flipkart ┆ Active   │
│ ---        ┆ ---              ┆ ---              ┆ ---     ┆ ---      │
│ str        ┆ str              ┆ str              ┆ f64     ┆ f64      │
╞════════════╪══════════════════╪══════════════════╪═════════╪══════════╡
│ April      ┆ Accessories      ┆ Stock Quantity   ┆ 0.0     ┆ 1808.0   │
│ May        ┆ Accessories      ┆ Sales Quantity   ┆ 0.0     ┆ 61.0     │
│ April      ┆ Anklet           ┆ Stock Quantity   ┆ null    ┆ 21861.75 │
└────────────┴──────────────────┴──────────────────┴─────────┴──────────┘

内容的提问来源于stack exchange，提问作者Judy T Raj