如何调整Pandas pivot_table实现指定格式的DataFrame转换？

原始数据 #

import pandas as pd

data = [
    {'column1': 'asd', 'column2': 'a', 'column3': 'mileage', 'column4': 7889, 'column5': None},
    {'column1': 'fsd', 'column2': 'a', 'column3': 'temp', 'column4': 4557, 'column5': None},
    {'column1': None, 'column2': 'a', 'column3': 'string', 'column4': None, 'column5': 'value_string1'},
    {'column1': 'asd', 'column2': 'b', 'column3': 'mileage', 'column4': 5678, 'column5': None},
    {'column1': 'fsd', 'column2': 'b', 'column3': 'temp', 'column4': 5830, 'column5': None},
    {'column1': None, 'column2': 'b', 'column3': 'string', 'column4': None, 'column5': 'value_string2'},
    {'column1': None, 'column2': 'c', 'column3': 'temp', 'column4': 10, 'column5': None},
    {'column1': None, 'column2': 'c', 'column3': 'temp', 'column4': 8, 'column5': None},
]

df = pd.DataFrame(data)

期望输出 #

column2 mileage temp_1  temp_2  
a       7889.0  4557.0  NaN
b       5678.0  5830.0  NaN
c        NaN    10.0    NaN
c        NaN     NaN    8.0

现有问题 #

直接使用pivot_table会自动对column2和column3相同的行做平均值聚合，丢失原始行的独立信息：

pivot_table = df.pivot_table(index='column2', columns='column3', values='column4')

输出结果：

column3 mileage temp
column2     
a       7889.0  4557.0
b       5678.0  5830.0
c         NaN    9.0

解决方案 #

核心思路是为同组内的重复项添加唯一序号，避免透视时被聚合，以下是两种可行方法：

方法一：结合cumcount与pivot #

# 筛选有效数据：排除column3为string的行，保留column4非空的记录
df_process = df[(df['column3'] != 'string') & df['column4'].notna()].copy()

# 为每个(column2, column3)分组添加组内序号
df_process['seq'] = df_process.groupby(['column2', 'column3']).cumcount() + 1

# 构造新列名：重复项添加序号，非重复项保留原名
df_process['new_col'] = df_process.apply(
    lambda x: f"{x['column3']}_{x['seq']}" if x['seq'] > 1 else x['column3'],
    axis=1
)

# 透视：用column2和seq作为联合索引，确保重复项不会被合并
result = df_process.pivot(
    index=['column2', 'seq'],
    columns='new_col',
    values='column4'
).reset_index(level=1, drop=True).reset_index()

# 调整列顺序，匹配期望输出
final_df = result[['column2', 'mileage', 'temp_1', 'temp_2']]
print(final_df)

方法二：简化版处理 #

df_process = df[(df['column3'] != 'string') & df['column4'].notna()].copy()

# 生成组内序号
df_process['row_num'] = df_process.groupby(['column2', 'column3']).cumcount()

# 构造带序号的列名，mileage直接保留原名
df_process['col_name'] = df_process['column3']
df_process.loc[df_process['column3'] == 'temp', 'col_name'] = 'temp_' + (df_process['row_num'] + 1).astype(str)

# 透视并整理格式
result = df_process.pivot(
    index=['column2', 'row_num'],
    columns='col_name',
    values='column4'
).reset_index(level=1, drop=True).reset_index()[['column2', 'mileage', 'temp_1', 'temp_2']]

print(result)

两种方法都能得到符合要求的输出，保留原始行的独立数据，不会自动聚合重复项。

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