Pandas中列对的聚合与重组：多重复列数据重构

导入CSV得到的Pandas DataFrame结构如下：

df = pd.DataFrame( {0: {0: 'ID', 1: '1', 2: '2', 3: '3', 4: '4', 5: '5'}, 1: {0: 'Net Cost', 1: '30', 2: '40', 3: '50', 4: '35', 5: '45'}, 2: {0: 'Charge Description', 1: 'Surcharge A', 2: 'Discount X', 3: 'Discount X', 4: 'Discount X', 5: 'Surcharge A'}, 3: {0: 'Charge Amount', 1: '9.5', 2: '-12.5', 3: '-11.5', 4: '-5.5', 5: '9.5'}, 4: {0: 'Charge Description', 1: 'Discount X', 2: '', 3: '', 4: 'Surcharge B', 5: 'Discount X'}, 5: {0: 'Charge Amount', 1: '-11.5', 2: '', 3: '', 4: '3.5', 5: '-10.5'}, 6: {0: 'Charge Description', 1: 'Discount Y', 2: '', 3: '', 4: '', 5: 'Surcharge B'}, 7: {0: 'Charge Amount', 1: '-3.25', 2: '', 3: '', 4: '', 5: '4.5'}, 8: {0: 'Charge Description', 1: 'Surcharge B', 2: '', 3: '', 4: '', 5: ''}, 9: {0: 'Charge Amount', 1: '2.5', 2: '', 3: '', 4: '', 5: ''}} )

该表格首行为表头，其中Charge Description与Charge Amount成对重复出现。需要将数据处理为：每个Charge Description对应唯一列，列按字母排序，空值以0填充，每个ID对应唯一一行，最终效果如下：

实现代码 #

import pandas as pd

# 1. 修正表头与数据行
df.columns = df.iloc[0]
df = df[1:].reset_index(drop=True)

# 2. 分离固定列和重复的Charge相关列
fixed_columns = ['ID', 'Net Cost']
charge_columns = [col for col in df.columns if col not in fixed_columns]

# 3. 将成对的Charge列转为长格式，拆分描述与金额
df_melted = df.melt(id_vars=fixed_columns, value_vars=charge_columns, var_name='Column_Type', value_name='Content')
# 按ID分组，每两行归为一组（对应一组Description和Amount）
df_melted['group_id'] = df_melted.groupby(fixed_columns + ['Column_Type']).cumcount() // 2
# 透视得到每个组的描述和金额对应关系
df_paired = df_melted.pivot_table(index=fixed_columns + ['group_id'], columns='Column_Type', values='Content', aggfunc='first').reset_index()
# 过滤空的Charge描述
df_paired = df_paired[df_paired['Charge Description'] != '']

# 4. 重新透视成目标宽表格式
final_df = df_paired.pivot_table(index=fixed_columns, columns='Charge Description', values='Charge Amount', aggfunc='first').reset_index()

# 5. 填充空值为0，按列名字母排序
final_df = final_df.fillna(0)
sorted_charge_cols = sorted([col for col in final_df.columns if col not in fixed_columns])
final_df = final_df[fixed_columns + sorted_charge_cols]

# 6. 转换为正确的数据类型
final_df['ID'] = final_df['ID'].astype(int)
final_df['Net Cost'] = final_df['Net Cost'].astype(float)
for col in sorted_charge_cols:
    final_df[col] = final_df[col].astype(float)

print(final_df)

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