如何基于DataFrame列中特定值实现数据转置规整？

原始数据与需求 #

原始DataFrame代码 #

import pandas as pd

column_data = [33.5,"W","A to B, OK","slinks down to hammer","T c V b Rell 10 (82b 6x1) DW:84.14",
33.4,"•","A to B, no","Tosses it uo",
33.3,2,"A to B, 2 R","On a right way","slinks down to hammer","BAN: 185/4CRR: 5.60 ","T 69 (80b 6x4)","Mu 7 (17b)","Mark 6-0-29-1","George Dockrel","Bet 31",
33.2,2,"A to T, 2 R","slinks down to hammer",
33.1,"2","A to T, 2 r","angling away, cuts it"]
df = pd.DataFrame(column_data, columns=['Col1'])

需求描述 #

需要将单列数据以33.5、33.4、33.3、33.2、33.1这些数值为分组起始标识，将每个分组内的元素转成一行的多列格式。最终每行对应一个分组，组内元素依次填充到对应列，分组长度不足的位置补NaN。

尝试的错误代码 #

import pandas as pd

num_columns = 11
num_rows = len(data2) // num_columns

data_dict = {}
for i in range(1, num_columns + 1):
    column_name = 'Col{}'.format(i)
    column_values = data2.iloc[(i - 1) * num_rows: i * num_rows, 0].tolist()
    data_dict[column_name] = column_values

df5 = pd.DataFrame(data_dict)
df5

该代码假设数据是固定长度的均匀分组，但实际每个分组长度不一致，因此无法得到预期结果。

正确解决方案 #

思路 #

1. 标记分组ID：识别以33.x开头的行作为分组起点，为每个分组分配唯一ID；
2. 标记组内位置：为每个分组内的元素分配顺序序号（从0开始）；
3. 转置分组数据：通过groupby+unstack将每个分组的元素转成列。

实现代码 #

import pandas as pd

# 1. 创建分组ID：当Col1是目标数值时，生成新分组
target_values = {33.1, 33.2, 33.3, 33.4, 33.5}
df['is_group_start'] = df['Col1'].apply(lambda x: x in target_values if isinstance(x, (int, float)) else False)
df['group_id'] = df['is_group_start'].cumsum()

# 2. 为每个分组内的元素分配位置序号
df['position'] = df.groupby('group_id').cumcount()

# 3. 转置数据：将每个分组的元素转成列
result = df.pivot(index='group_id', columns='position', values='Col1')

# 重命名列，符合需求的Col1、Col2...格式
result.columns = [f'Col{col+1}' for col in result.columns]
result = result.reset_index(drop=True)

print(result)

输出结果 #

Col1 Col2          Col3                  Col4                                   Col5                    Col6                  Col7         Col8               Col9             Col10    Col11
0  33.5    W    A to B, OK  slinks down to hammer  T c V b Rell 10 (82b 6x1) DW:84.14                    NaN                    NaN          NaN                 NaN               NaN      NaN
1  33.4    •    A to B, no           Tosses it uo                                   NaN                    NaN                    NaN          NaN                 NaN               NaN      NaN
2  33.3    2  A to B, 2 R        On a right way  slinks down to hammer  BAN: 185/4CRR: 5.60  T 69 (80b 6x4)  Mu 7 (17b)  Mark 6-0-29-1  George Dockrel  Bet 31
3  33.2    2  A to T, 2 R  slinks down to hammer                                   NaN                    NaN                    NaN          NaN                 NaN               NaN      NaN
4  33.1    2  A to T, 2 r    angling away, cuts it                                   NaN                    NaN                    NaN          NaN                 NaN               NaN      NaN

这个结果完全匹配需求：每个以33.x开头的分组转为一行，组内元素依次填充到对应列，长度不足的列自动补NaN。

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