如何用Pandas将多行转列并整理含复合表头的Excel数据？

问题描述 #

读取的复合表头Excel结构如下：

ori code   cgk       cgk    clg       clg
                              ori city   jakarta   NaN    cilegon   NaN
                              ori prop   jakarta   NaN    banten    NaN
                              ori area   jawa      NaN    jawa      NaN

code    city       district   island    type a     days    type b    days
001     jakarta    jakarta     jawa      12000       2      13000      3
002     surabaya   surabaya    jawa      13000       3      14000      4

需要转换为目标格式：

code   city        district    island    type       price_type   days    ori_code   ori_city     ori_prop   ori_area
001    jakarta     jakarta     jawa      type a     12000         2         cgk     jakarta    jakarta      jawa
001    jakarta     jakarta     jawa      type b     13000         3         clg     cilegon    banten       jawa 
002    surabaya    surabaya    jawa      type a     13000         3         cgk     jakarta    jakarta      jawa
002    surabaya    surabaya    jawa      type b     14000         4         clg     cilegon    banten       jawa

已执行操作：

df = df.set_index(['code','city','district','island'])

分步解决方案 #

1. 提取ori信息映射表 #

首先从复合表头的前4行提取cgk和clg对应的ori属性映射：

# 假设读取Excel时指定header=[0,1,2,3]来加载复合表头
# 构建ori信息映射字典
ori_mapping = {}
for ori_code in ['cgk', 'clg']:
    # 遍历4层表头，提取对应属性
    ori_info = {
        'ori_code': ori_code,
        'ori_city': df.columns.get_level_values(1)[df.columns.get_level_values(0) == ori_code][0],
        'ori_prop': df.columns.get_level_values(2)[df.columns.get_level_values(0) == ori_code][0],
        'ori_area': df.columns.get_level_values(3)[df.columns.get_level_values(0) == ori_code][0]
    }
    ori_mapping[ori_code] = ori_info

2. 拆分并融合type与days列 #

先重置索引，再通过melt和pivot来拆分type/days列，关联ori信息：

# 重置索引，恢复基础列
df_reset = df.reset_index()

# 拆分多列为行，保留基础列作为id
melted = df_reset.melt(
    id_vars=['code', 'city', 'district', 'island'],
    var_name=['ori_col', 'col_type'],
    value_name='content'
)

# 区分price_type和days类别，提取type名称
melted['category'] = melted['col_type'].map(lambda x: 'price_type' if 'type' in x else 'days')
melted['type'] = melted['col_type'].where(melted['col_type'].str.contains('type'), None)

# 旋转表格，将price_type和days转为列
pivoted = melted.pivot_table(
    index=['code', 'city', 'district', 'island', 'ori_col', 'type'],
    columns='category',
    values='content',
    aggfunc='first'
).reset_index()

# 填充同一组内的type空值（days行对应同组的type）
pivoted['type'] = pivoted.groupby(['code', 'city', 'district', 'island', 'ori_col'])['type'].ffill().bfill()

# 关联ori映射表，添加ori属性列
pivoted = pivoted.merge(
    pd.DataFrame.from_dict(ori_mapping, orient='index').reset_index().rename(columns={'index': 'ori_col'}),
    on='ori_col',
    how='left'
)

3. 调整列顺序并输出结果 #

最后调整列顺序匹配目标格式：

# 按目标顺序排列列
final_df = pivoted[[
    'code', 'city', 'district', 'island', 'type', 'price_type', 'days',
    'ori_code', 'ori_city', 'ori_prop', 'ori_area'
]]

# 查看结果
print(final_df)

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