解决含重复索引的Pandas DataFrame转宽表（Unstack）问题

原始数据 #

给定的DataFrame如下：

import pandas as pd

df = pd.DataFrame({
    'asset': ['Asset_1', 'Asset_1', 'Asset_2', 'Asset_2', 'Asset_3'],
    'valid_from': ['01/09/2020', '01/09/2020', '01/05/2022', '02/01/2023', '01/04/2018'],
    'valid_to': ['31/12/2049', '31/12/2049', '01/01/2023', '31/12/2049', '31/12/2049'],
    'history_table': ['market bm', 'energy_capacity', 'market bm', 'market bm', 'owner'],
    'value': ['2__MSTAT001', '10', 'V__JZENO001', 'V__JZEN002', 'ESB'],
    'latitude': ['53,80', '53,80', '51,30', '51,30', '52,97']
})

对应的表格：

需求与问题 #

• 该DataFrame混合长表与宽表格式，history_table是长格式标识列，value为对应值列，需要按asset分组转成宽表
• 使用df.pivot(index=['asset', 'valid_from', 'valid_to'], columns='history_table', values='value')时，遇到索引重复报错
• 尝试pivot_table但因value同时包含文本和数值，无法用聚合函数处理

解决方案 #

方案1：分组后Unstack（无重复项时用） #

如果数据中同一(asset, valid_from, valid_to, history_table)组合没有重复行，直接用groupby+unstack即可，同时保留latitude列（同一asset+有效期内纬度值一致，取第一个即可）：

# 分组并unstack转宽表
result = (df.groupby(['asset', 'valid_from', 'valid_to', 'history_table'])['value']
          .first()  # 无重复时first不影响，仅确保每组唯一
          .unstack('history_table')
          .reset_index())

# 补充latitude列
result['latitude'] = df.groupby(['asset', 'valid_from', 'valid_to'])['latitude'].first().values

# 调整列顺序，让基础信息在前
result = result[['asset', 'valid_from', 'valid_to', 'latitude', 'market bm', 'energy_capacity', 'owner']]

得到结果：

方案2：保留多值（有重复项时用） #

如果实际数据中同一组合存在多个value，不想丢失数据可以把值转成列表：

# 分组后将多值聚合为列表，再unstack
result = (df.groupby(['asset', 'valid_from', 'valid_to', 'history_table'])['value']
          .agg(list)
          .unstack('history_table')
          .reset_index())

# 补充latitude列
result['latitude'] = df.groupby(['asset', 'valid_from', 'valid_to'])['latitude'].first().values

比如某组有两个market bm值，会以['val1', 'val2']的形式存在对应列中。

方案3：添加序号拆分重复行 #

如果希望把重复的(asset, valid_from, valid_to)组合拆成单独行，可以给每组加序号再pivot：

# 给同一(asset, valid_from, valid_to)的行加序号
df['seq'] = df.groupby(['asset', 'valid_from', 'valid_to']).cumcount()

# 把序号加入索引进行pivot
result = df.pivot(index=['asset', 'valid_from', 'valid_to', 'seq'], 
                  columns='history_table', values='value').reset_index()

# 移除序号列，补充latitude
result = result.drop('seq', axis=1)
result['latitude'] = df.groupby(['asset', 'valid_from', 'valid_to'])['latitude'].first().values

这样即使同一有效期内同一history_table有多值，也会拆分成不同行展示。

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