Pandas/Python实现重复索引行转列（映射至OP#1-OP#5）方案咨询

我处理的数据量始终少于1000行，无需关注性能问题；数据转换的列数不会超过OP#5。求告知在Pandas或纯Python中实现该转换的最佳方案。

我曾尝试使用dataframe.pivot_table，但遇到问题：重复索引会为每个CAT生成独立行，而非将同索引下的第1个CAT放入OP#1、第2个放入OP#2以此类推。

转换前数据 #

转换后目标数据 #

方法一：Pandas实现 #

核心逻辑是先按ID分组生成组内序号，再通过透视转换将序号映射为OP#N列，最后调整列顺序并补全空值。

import pandas as pd

# 构造原始数据（实际场景可替换为读取文件）
df = pd.DataFrame([
    ["id1", 123, "CAT A", "VALUE"],
    ["id1", 123, "CAT B", "VALUE"],
    ["id1", 123, "CAT C", "VALUE"],
    ["id1", 123, "CAT C", "VALUE"],
    ["id1", 123, "CAT E", "VALUE"],
    ["id2", 145, "CAT A", "VALUE"],
    ["id2", 145, "CAT B", "VALUE"],
    ["id3", 101, "CAT D", "VALUE"],
    ["id3", 101, "CAT E", "VALUE"],
    ["id4", 500, "CAT D", "VALUE"],
    ["id5", 600, "CAT E", "VALUE"]
], columns=["ID", "MISC", "CAT", "TIME"])

# 1. 为每个ID组内的行生成从1开始的序号
df["seq"] = df.groupby("ID").cumcount() + 1
# 2. 限制序号不超过5（匹配OP#1到OP#5的上限）
df["seq"] = df["seq"].clip(upper=5)
# 3. 透视转换：将序号转为列，CAT和TIME作为对应值
pivoted = df.pivot(index=["ID", "MISC"], columns="seq", values=["CAT", "TIME"])
# 4. 重命名列，调整为OP#N和TIME交替的格式
pivoted.columns = [f"OP#{col[1]}" if col[0] == "CAT" else f"TIME_{col[1]}" for col in pivoted.columns]
# 重新排列列顺序，让OP与对应TIME相邻
new_cols = []
for i in range(1, 6):
    new_cols.append(f"OP#{i}")
    new_cols.append(f"TIME_{i}")
pivoted = pivoted[new_cols]
# 5. 重置索引并填充空值为空字符串
result = pivoted.reset_index().fillna("")
# 去掉TIME列的序号后缀，匹配目标格式
result.columns = [col.replace("_1", "").replace("_2", "").replace("_3", "").replace("_4", "").replace("_5", "") for col in result.columns]

print(result)

方法二：纯Python实现 #

通过字典分组存储每个ID的条目，再手动构建结果行，逻辑直观适合小数据量场景。

# 原始数据列表
data = [
    ["id1", 123, "CAT A", "VALUE"],
    ["id1", 123, "CAT B", "VALUE"],
    ["id1", 123, "CAT C", "VALUE"],
    ["id1", 123, "CAT C", "VALUE"],
    ["id1", 123, "CAT E", "VALUE"],
    ["id2", 145, "CAT A", "VALUE"],
    ["id2", 145, "CAT B", "VALUE"],
    ["id3", 101, "CAT D", "VALUE"],
    ["id3", 101, "CAT E", "VALUE"],
    ["id4", 500, "CAT D", "VALUE"],
    ["id5", 600, "CAT E", "VALUE"]
]

# 1. 按ID分组，存储每组的MISC和CAT-TIME条目
groups = {}
for row in data:
    id_val, misc, cat, time_val = row
    if id_val not in groups:
        groups[id_val] = {"misc": misc, "entries": []}
    groups[id_val]["entries"].append((cat, time_val))

# 2. 构建结果表头和数据行
result = []
header = ["ID", "MISC"]
for i in range(1, 6):
    header.append(f"OP#{i}")
    header.append("TIME")
result.append(header)

# 填充每个ID的转换后行
for id_val, group in groups.items():
    misc = group["misc"]
    entries = group["entries"]
    row = [id_val, misc]
    # 最多取5组条目，不足的补空
    for i in range(5):
        row.extend(entries[i] if i < len(entries) else ["", ""])
    result.append(row)

# 打印结果
for line in result:
    print(line)

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