如何将Pandas长格式DataFrame转为宽格式？两类场景示例

场景1：每个order对应固定行数的转宽需求 #

输入数据 #

import pandas as pd

df = pd.DataFrame({'order': {0: '1',
  1: '1',
  2: '2',
  3: '2',
  4: '3',
  5: '3'},
 'start': {0: pd.Timestamp('2023-04-01 04:00:00+0000', tz='UTC'),
  1: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  2: pd.Timestamp('2023-04-01 04:00:00+0000', tz='UTC'),
  3: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  4: pd.Timestamp('2023-04-01 04:00:00+0000', tz='UTC'),
  5: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC')},
 'end': {0: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  1: pd.Timestamp('2023-06-01 04:00:00+0000', tz='UTC'),
  2: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  3: pd.Timestamp('2023-06-01 04:00:00+0000', tz='UTC'),
  4: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  5: pd.Timestamp('2023-06-01 04:00:00+0000', tz='UTC')},
 'quant': {0: 10, 1: 10, 2: 20, 3: 30, 4: 40, 5: 50},
 'price': {0: 44, 1: 44, 2: 5, 3: 6, 4: 8, 5: 8}})

实现代码 #

# 为每个order内的行添加序号（从1开始）
df['row_idx'] = df.groupby('order').cumcount() + 1

# 透视转换为宽表
wide_df = df.pivot(index='order', columns='row_idx', values=['start', 'end', 'quant', 'price'])

# 调整列名为「字段名_序号」格式
wide_df.columns = [f'{col[0]}_{col[1]}' for col in wide_df.columns]

# 重置索引，将order转为普通列
wide_df = wide_df.reset_index()

print(wide_df)

输出结果 #

场景2：order对应行数不一致的转宽需求 #

输入数据 #

import pandas as pd

df = pd.DataFrame({'order': {0: '1',
  1: '1',
  2: '2',
  3: '2',
  4: '3',
  5: '3',
  6: '3',
  7: '3'},
 'start': {0: pd.Timestamp('2023-04-01 04:00:00+0000', tz='UTC'),
  1: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  2: pd.Timestamp('2023-04-01 04:00:00+0000', tz='UTC'),
  3: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  4: pd.Timestamp('2023-04-01 04:00:00+0000', tz='UTC'),
  5: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  6: pd.Timestamp('2023-03-01 04:00:00+0000', tz='UTC'),
  7: pd.Timestamp('2023-02-01 04:00:00+0000', tz='UTC')},
 'end': {0: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  1: pd.Timestamp('2023-06-01 04:00:00+0000', tz='UTC'),
  2: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  3: pd.Timestamp('2023-06-01 04:00:00+0000', tz='UTC'),
  4: pd.Timestamp('2023-05-01 04:00:00+0000', tz='UTC'),
  5: pd.Timestamp('2023-06-01 04:00:00+0000', tz='UTC'),
  6: pd.Timestamp('2023-04-01 04:00:00+0000', tz='UTC'),
  7: pd.Timestamp('2023-03-01 04:00:00+0000', tz='UTC')},
 'quant': {0: 10, 1: 10, 2: 20, 3: 30, 4: 40, 5: 50, 6:10, 7:10},
 'price': {0: 44, 1: 44, 2: 5, 3: 6, 4: 8, 5: 8, 6:9, 7:8}})

实现代码 #

# 为每个order内的行添加序号（从1开始）
df['row_idx'] = df.groupby('order').cumcount() + 1

# 透视转宽表，自动为缺失行填充NaN
wide_df = df.pivot(index='order', columns='row_idx', values=['start', 'end', 'quant', 'price'])

# 调整列名格式
wide_df.columns = [f'{col[0]}_{col[1]}' for col in wide_df.columns]

# 重置索引
wide_df = wide_df.reset_index()

print(wide_df)

输出结果 #

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