如何在Python中对含重复instance_id的DataFrame进行分组转置？

原始数据 #

import pandas as pd

data = {
    'instance_id': [
        'wt_E70491_0.857', 'wt_E70492_0.857', 'wt_E70490_0.857', 'wt_E70486_0.857', 'wt_E70493_0.857',
        'wt_E70484_0.857', 'wt_E70487_0.857', 'wt_E70489_0.857', 'wt_E70483_0.857', 'wt_E70485_0.857',
        'wt_E70488_0.857', 'wt_E70491_0.857', 'wt_E70492_0.857', 'wt_E70490_0.857', 'wt_E70486_0.857',
        'wt_E70493_0.857', 'wt_E70484_0.857', 'wt_E70487_0.857', 'wt_E70489_0.857', 'wt_E70483_0.857',
        'wt_E70485_0.857', 'wt_E70488_0.857', 'wt_E70491_0.857', 'wt_E70492_0.857', 'wt_E70490_0.857',
        'wt_E70486_0.857', 'wt_E70493_0.857', 'wt_E70484_0.857', 'wt_E70487_0.857', 'wt_E70489_0.857',
        'wt_E70483_0.857', 'wt_E70485_0.857', 'wt_E70488_0.857'
    ],
    'wake_speed_factor': [
        '1,00001', '0,817721', '1,00054', '1', '0,926203', '0,865908', '1', '0,930648', '0,957561', '0,968135',
        '0,996474', '1,00001', '0,803226', '1,00029', '1', '0,852778', '0,828148', '1,00002', '0,881657', '0,896756',
        '0,921366', '0,999333', '1,00001', '0,878923', '0,999948', '1', '0,76646', '0,837149', '1,00003', '0,821626',
        '0,76071', '0,818493', '0,991048'
    ]
}

df = pd.DataFrame(data)

需求说明 #

原始DataFrame中instance_id每11行重复一次，需将数据重排为以instance_id为列名，每组11条数据对应一行的格式。

实现方案 #

核心思路 #

1. 生成分组标识：用整数除法给每11条数据分配同一个分组ID
2. 重塑数据：通过pivot或set_index+unstack将行转列

方法一：使用pivot #

import numpy as np

# 添加分组列，每11条数据为一组
df['group'] = np.arange(len(df)) // 11

# 重塑数据，group作为行索引，instance_id作为列，取值为wake_speed_factor
result_df = df.pivot(index='group', columns='instance_id', values='wake_speed_factor')

# 可选：去掉group索引，重置为默认整数索引
result_df = result_df.reset_index(drop=True)

方法二：使用set_index + unstack #

import numpy as np

df['group'] = np.arange(len(df)) // 11

# 设置多级索引后，将instance_id层级转成列
result_df = df.set_index(['group', 'instance_id'])['wake_speed_factor'].unstack()
result_df = result_df.reset_index(drop=True)

最终结果 #

wt_E70491_0.857 wt_E70492_0.857 wt_E70490_0.857 wt_E70486_0.857 wt_E70493_0.857 wt_E70484_0.857 wt_E70487_0.857 wt_E70489_0.857 wt_E70483_0.857 wt_E70485_0.857 wt_E70488_0.857
0        1,00001       0,817721        1,00054               1       0,926203       0,865908               1       0,930648       0,957561       0,968135       0,996474
1        1,00001       0,803226        1,00029               1       0,852778       0,828148        1,00002       0,881657       0,896756       0,921366       0,999333
2        1,00001       0,878923       0,999948               1        0,76646       0,837149        1,00003       0,821626        0,76071       0,818493       0,991048

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