使用Pandas清洗特殊表头结构Excel文件并转换为目标长表格式

实现步骤如下：

• 提取前两行的列属性信息，转置生成列名与Type、Category的映射表
• 提取下方的日期与数值部分，用melt方法将宽表转为长表格式
• 关联属性映射表，删除Price列为空的记录，调整列顺序后得到最终结果

参考代码：

import pandas as pd

# 假设读取Excel得到的原始DataFrame为df_raw
# 1. 生成列属性映射表
attr_map = df_raw.head(2).set_index(df_raw.columns[0]).T.reset_index()
attr_map.columns = ['col_name', 'Type', 'Category']

# 2. 处理时间序列数据
data_part = df_raw.iloc[3:].copy()
data_part.columns = ['Dates'] + list(df_raw.columns[1:])
data_long = data_part.melt(id_vars='Dates', var_name='col_name', value_name='Price')

# 3. 关联属性、过滤空值、调整格式
res = data_long.merge(attr_map, on='col_name', how='left')
res = res.dropna(subset=['Price']).sort_values(['Type', 'Dates']).reset_index(drop=True)
res = res[['Dates', 'Price', 'Category', 'Type']]

# 若需匹配示例中Type=2对应Category=B的预期输出，补充以下代码调整属性即可
# res.loc[res['Type'] == '2', 'Category'] = 'B'

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