如何用pd.melt优雅处理多索引列，合并状态与位置数据集？

问题 #

我在编写易读的pandas代码时遇到了麻烦，怀疑没掌握pd.melt处理多层列索引的正确用法。现有两个结构相似的数据集：

• time：状态变更时间
• name和instance：用于唯一标识记录的复合键
• 各自包含一个随时间变更的字段：state（状态）和location（位置）

示例记录：

1. dict(time=0, name="a", instance=0, state=1)
2. dict(time=5, name="a", instance=0, location="london")

期望合并后得到每个(name, instance)在各时间点的最新state和location，预期结果：

[
    dict(time=0, name="a", instance=0, state=1, location=np.nan),
    dict(time=5, name="a", instance=0, state=1, location="london"),
]

目前我用pd.DataFrame.pivot_table、pd.DataFrame.ffill、pd.DataFrame.melt和pd.DataFrame.reset_index的组合实现了需求，但代码繁琐且可读性差，尤其是pd.melt部分。不确定是没掌握pd.melt处理多层列的用法，还是有更合适的pandas工具。当前实现代码如下：

import pandas as pd

states = [
    dict(time=0, name="a", instance=0, state=0),
    dict(time=0, name="a", instance=1, state=0),
    dict(time=0, name="a", instance=2, state=0),
    dict(time=0, name="b", instance=1, state=0),
    dict(time=0, name="b", instance=2, state=0),
    dict(time=1, name="a", instance=1, state=1),
    dict(time=2, name="a", instance=2, state=1),
    dict(time=2, name="b", instance=1, state=1),
]

locations = [
    dict(time=0, name="a", instance=0, location="tokyo"),
    dict(time=0, name="a", instance=1, location="tokyo"),
    dict(time=0, name="a", instance=2, location="tokyo"),
    dict(time=0, name="b", instance=1, location="tokyo"),
    dict(time=0, name="b", instance=2, location="tokyo"),
    dict(time=1, name="a", instance=0, location="london"),
    dict(time=1, name="a", instance=2, location="london"),
    dict(time=1, name="b", instance=1, location="london"),
    dict(time=1, name="b", instance=2, location="london"),
    dict(time=1, name="a", instance=1, location="paris"),
    dict(time=2, name="a", instance=2, location="paris"),
    dict(time=2, name="b", instance=1, location="paris"),
]

states = pd.DataFrame.from_dict(states)
locations = pd.DataFrame.from_dict(locations)

combined = pd.concat([states, locations], axis="index")
combined = combined.pivot_table(
    index="time",
    columns=["name", "instance"],
    values=["state", "location"],
    aggfunc="last",
)
combined = combined.ffill()

ugly_melt = combined.melt(ignore_index=False)
ugly_melt = ugly_melt.rename(columns={None: "state_status"})
ugly_melt = (
    ugly_melt.reset_index()
    .pivot(
        index=["time", "name", "instance"],
        columns=["state_status"],
        values="value",
    )
    .reset_index()
)
print(ugly_melt)

更简洁的实现方案 #

核心思路是先对两个数据集分别做按复合键的前向填充，再通过合并全量时间-标识组合得到结果，避免复杂的多层索引操作：

方案一：分组填充+全量合并 #

import pandas as pd
import numpy as np

# 原始数据（省略重复定义）
states = pd.DataFrame.from_dict([...])
locations = pd.DataFrame.from_dict([...])

# 生成所有可能的时间-标识组合，确保无遗漏
all_times = pd.Series(np.unique(np.concatenate([states['time'], locations['time']])))
all_ids = pd.merge(
    states[['name', 'instance']].drop_duplicates(),
    locations[['name', 'instance']].drop_duplicates(),
    on=['name', 'instance'],
    how='outer'
)
full_df = pd.MultiIndex.from_product(
    [all_times, all_ids['name'], all_ids['instance']],
    names=['time', 'name', 'instance']
).to_frame(index=False)

# 处理states：按分组排序后，补全所有时间点并前向填充
states_processed = states.sort_values(['name', 'instance', 'time'])
states_processed = states_processed.groupby(['name', 'instance'], group_keys=False).apply(
    lambda x: x.set_index('time').reindex(all_times).ffill().reset_index()
)

# 处理locations：逻辑同states
locations_processed = locations.sort_values(['name', 'instance', 'time'])
locations_processed = locations_processed.groupby(['name', 'instance'], group_keys=False).apply(
    lambda x: x.set_index('time').reindex(all_times).ffill().reset_index()
)

# 合并得到最终结果
result = pd.merge(full_df, states_processed, on=['time', 'name', 'instance'], how='left')
result = pd.merge(result, locations_processed, on=['time', 'name', 'instance'], how='left')

print(result)

方案二：优化原有的多层索引melt操作 #

如果坚持用你最初的pivot_table+ffill路线，可以通过指定col_level参数简化melt步骤，替代原来的ugly_melt部分：

# 基于你原代码中的combined（多层列索引DataFrame）
melted = combined.melt(ignore_index=False, col_level=[0, 1])
melted = melted.reset_index()
# 重命名列，拆分多层索引的字段
melted.columns = ['time', 'type', 'name', 'instance', 'value']
# 转成宽表得到结果
result = melted.pivot(
    index=['time', 'name', 'instance'],
    columns='type',
    values='value'
).reset_index()

print(result)

方案优势说明 #

1. 可读性更强：每一步逻辑清晰，避免了反复的pivot/melt转换，无需处理混乱的匿名列名
2. 逻辑直观：直接针对每个(name, instance)分组处理，确保每个时间点都能拿到最新的状态/位置
3. 扩展性好：后续新增类似字段（如status）时，只需复制处理states的逻辑即可

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