如何重塑Pandas DataFrame，基于Seq_Sp列创建新的宽表列

问题背景 #

现有如下结构的长表df1：

import pandas as pd

df1 = pd.DataFrame( columns=['Serial','Seq_Sp','PT','FirstPT','DiffAngle','R1'], 
              data=[['1001W','2_1',15.13,15.07,1.9,7.4], ['1001W','2_2',16.02,15.80,0.0,0.05],
                    ['1001W','2_3',14.3,15.3,6,0.32],['1001W','2_4',14.18,15.07,2.2,0.16],
                    ['6279W','2_1',15.13,15.13,2.3,0.31],['6279W','2_2',13.01,15.04,1.3,0.04],
                    ['6279W','2_3',14.13,17.04,2.3,0.31],['6279W','2_4',14.01,17.23,3.1,1.17]
                   ])
display(df1)

需要将其转换为以Serial为唯一值的宽表df2，新列命名规则为「原列名_Seq_Sp值」，目标结构如下：

df2 = pd.DataFrame( columns=['Serial','PT_2_1','FirstPT_2_1','DiffAngle_2_1','R1_2_1','PT_2_2','FirstPT_2_2','DiffAngle_2_2',
                             'R1_2_2','PT_2_3','FirstPT_2_3','DiffAngle_2_3','R1_2_3','PT_2_4','FirstPT_2_4','DiffAngle_2_4',
                             'R1_2_4'],
                   data=[
                       ['1001W',15.13,15.07,1.9,7.4,16.02,15.80,0.0,0.05, 14.3,15.3,6,0.32 ,14.18,15.07,2.2,0.16],
                       ['6279W',15.13,15.13,2.3,0.31,13.01,15.04,1.3,0.04,14.13,17.04,2.3,0.31,14.01,17.23,3.1,1.17]
                   ])
df2

解决方案 #

可以通过Pandas的pivot方法结合列名拼接实现，步骤如下：

1. 用pivot方法将长表转为多层列的中间表，以Serial为索引，Seq_Sp为列维度：

pivoted = df1.pivot(index='Serial', columns='Seq_Sp')

2. 将多层列名拼接成「原列名_Seq_Sp值」的格式：

pivoted.columns = pivoted.columns.map(lambda x: f"{x[0]}_{x[1]}")

3. 重置索引，把Serial从索引转为普通列：

df2 = pivoted.reset_index()

完整可执行代码：

import pandas as pd

df1 = pd.DataFrame( columns=['Serial','Seq_Sp','PT','FirstPT','DiffAngle','R1'], 
              data=[['1001W','2_1',15.13,15.07,1.9,7.4], ['1001W','2_2',16.02,15.80,0.0,0.05],
                    ['1001W','2_3',14.3,15.3,6,0.32],['1001W','2_4',14.18,15.07,2.2,0.16],
                    ['6279W','2_1',15.13,15.13,2.3,0.31],['6279W','2_2',13.01,15.04,1.3,0.04],
                    ['6279W','2_3',14.13,17.04,2.3,0.31],['6279W','2_4',14.01,17.23,3.1,1.17]
                   ])

# 执行数据重塑
pivoted = df1.pivot(index='Serial', columns='Seq_Sp')
pivoted.columns = pivoted.columns.map(lambda x: f"{x[0]}_{x[1]}")
df2 = pivoted.reset_index()

display(df2)

执行后会直接生成符合要求的宽表df2，列顺序与目标结构一致，数据匹配准确。

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