如何对含多列值的Pandas DataFrame执行Pivot（透视）操作

问题背景 #

原始DataFrame如下：

import pandas as pd
import numpy as np

data = pd.DataFrame([['Benz', 'MinSpeed', 0, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'TopSpeed', 200, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'ChasisNum', 654121, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'Seats', 5, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'AirBags', 5, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'VehicleType', np.nan, 'Sedan', 'USA', '2022-08-12'],
                     ['Benz', 'Color', np.nan, 'Black','USA', '2022-08-12'],
                     ['Benz', 'InternetInside', np.nan, 'Yes','USA', '2022-08-12'],
                     
                     ['Ferrari', 'MinSpeed', 0, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'TopSpeed', 250, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'ChasisNum', 781121, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'Seats', 4, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'AirBags', 2, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'VehicleType', np.nan, 'SUV', 'France', '2022-12-25'],
                     ['Ferrari', 'Color', np.nan, 'Red','France', '2022-12-25'],
                     ['Ferrari', 'InternetInside', np.nan, 'No','France', '2022-12-25'],
                     ], 
                    columns= ['CarModel', 'Features', 'NumericalValues', 'CategoricalValues','Country', 'DeliveryDate'])

尝试使用以下代码透视时，出现NumericalValues和CategoricalValues列层级重复的问题：

data.pivot(index='CarModel', columns='Features', values=['NumericalValues','CategoricalValues' ]).reset_index()

期望得到的输出格式：

output_data = pd.DataFrame([['Benz', 0, 200, 654121, 5, 5, 'Sedan', 'Black', 'Yes', 'USA', '2022-08-12'],
                         ['Ferrari', 0, 250, 781121, 4, 2, 'SUV', 'Red', 'No', 'France', '2022-12-25']
                     ],
                    columns=['CarModel', 'MinSpeed', 'TopSpeed', 'ChasisNum','Seats', 'AirBags', 'VehicleType', 'Color', 'InternetInside', 'Country', 'DeliveryDate'])

解决方法 #

问题核心在于原始数据中每个Features只对应NumericalValues或CategoricalValues中的一个值，另一列为NaN。我们可以先合并这两列，再进行透视，最后补充Country和DeliveryDate列。

步骤1：合并数值与分类列 #

创建一个新列Value，将两列的有效值合并：

data['Value'] = data['NumericalValues'].combine_first(data['CategoricalValues'])

或者用fillna也能达到同样效果：

data['Value'] = data['NumericalValues'].fillna(data['CategoricalValues'])

步骤2：透视合并后的数据 #

以CarModel为索引，Features为列，Value为值进行透视：

pivoted = data.pivot(index='CarModel', columns='Features', values='Value').reset_index()

步骤3：补充Country和DeliveryDate列 #

每个CarModel对应的Country和DeliveryDate是唯一的，通过分组取第一个值获取：

meta_data = data.groupby('CarModel')[['Country', 'DeliveryDate']].first().reset_index()

步骤4：合并所有数据 #

将透视表和元数据合并，再调整列顺序匹配预期输出：

final_output = pd.merge(pivoted, meta_data, on='CarModel')
final_output = final_output[['CarModel', 'MinSpeed', 'TopSpeed', 'ChasisNum','Seats', 'AirBags', 'VehicleType', 'Color', 'InternetInside', 'Country', 'DeliveryDate']]

完整代码 #

import pandas as pd
import numpy as np

# 原始数据
data = pd.DataFrame([['Benz', 'MinSpeed', 0, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'TopSpeed', 200, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'ChasisNum', 654121, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'Seats', 5, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'AirBags', 5, np.nan, 'USA', '2022-08-12'],
                     ['Benz', 'VehicleType', np.nan, 'Sedan', 'USA', '2022-08-12'],
                     ['Benz', 'Color', np.nan, 'Black','USA', '2022-08-12'],
                     ['Benz', 'InternetInside', np.nan, 'Yes','USA', '2022-08-12'],
                     
                     ['Ferrari', 'MinSpeed', 0, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'TopSpeed', 250, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'ChasisNum', 781121, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'Seats', 4, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'AirBags', 2, np.nan, 'France', '2022-12-25'],
                     ['Ferrari', 'VehicleType', np.nan, 'SUV', 'France', '2022-12-25'],
                     ['Ferrari', 'Color', np.nan, 'Red','France', '2022-12-25'],
                     ['Ferrari', 'InternetInside', np.nan, 'No','France', '2022-12-25'],
                     ], 
                    columns= ['CarModel', 'Features', 'NumericalValues', 'CategoricalValues','Country', 'DeliveryDate'])

# 合并数值和分类列
data['Value'] = data['NumericalValues'].combine_first(data['CategoricalValues'])

# 透视数据
pivoted = data.pivot(index='CarModel', columns='Features', values='Value').reset_index()

# 获取元数据（Country和DeliveryDate）
meta_data = data.groupby('CarModel')[['Country', 'DeliveryDate']].first().reset_index()

# 合并并调整列顺序
final_output = pd.merge(pivoted, meta_data, on='CarModel')
final_output = final_output[['CarModel', 'MinSpeed', 'TopSpeed', 'ChasisNum','Seats', 'AirBags', 'VehicleType', 'Color', 'InternetInside', 'Country', 'DeliveryDate']]

print(final_output)

运行后得到的结果与预期完全一致，无重复列层级，格式匹配需求。

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