如何将非常规结构Excel文件正确读取为Pandas DataFrame？

待处理文件表结构参考下图：

问题原因说明 #

你之前使用如下代码读取失败：

jpm = pd.read_excel("Downloads/JPM2022_05_06.xlsx",  header=10, usecols='B:P')

触发列越界警告、读入结果全为NaN的核心原因：

• 指定的header=10和实际表头所在行位置不匹配
• 指定的usecols='B:P'超出了目标sheet的实际有效列范围，高版本Pandas会直接抛出解析警告，空区域读入后自然只会生成Unnamed空列。

这类带合并单元格、多层表头、前置说明文本的非标准Excel，不能靠默认参数一步读取到位，按以下步骤处理即可得到目标结构数据。

分步处理流程 #

1. 先全量读取定位有效数据范围 #

不要提前指定表头、列范围，先无配置读入全表，打印前20行确认实际的表头位置、数据起始行、合并单元格分布、列范围：

import pandas as pd
# 无表头全量读取
df_raw = pd.read_excel("Downloads/JPM2022_05_06.xlsx", header=None)
# 打印前20行定位有效区域
print(df_raw.head(20))

这一步需要确认3个关键信息：agency/coup/vin/Cbal等固定字段的表头行索引（从0开始计数）、表尾备注/合计行的数量、英文月份列的范围。

2. 按定位结果读取有效数据 #

替换为你上一步查到的实际参数，读取结构化的宽表数据：

# 参数值请替换为第一步查到的实际值，以下为示例值
df = pd.read_excel(
    "Downloads/JPM2022_05_06.xlsx",
    header=12, # 替换为实际表头所在的行索引
    skipfooter=3 # 替换为表尾需要跳过的无效行数量
)

3. 填充合并单元格空值 #

Excel的合并单元格仅在最左上角位置存储值，其余关联单元格均为空，需要做向前填充补全：

# 跨行合并的agency字段向下填充
df['agency'] = df['agency'].ffill()
# 固定赋值SRC字段
df['SRC'] = 'JPM'

4. 宽表转长表，实现月份维度行转置 #

使用melt把横向排列的月份CPR列转为纵向行结构：

# 固定保留的维度列
id_columns = ['SRC', 'agency', 'coup', 'vin', 'Cbal']
# 筛选所有值为英文月份的列
month_columns = [col for col in df.columns if col in [
    'January','February','March','April','May','June',
    'July','August','September','October','November','December'
]]

df_long = df.melt(
    id_vars=id_columns,
    value_vars=month_columns,
    var_name='Month',
    value_name='CPR'
)

5. 生成Pred_Month字段 #

按照样例的日期映射规则（December对应2022年12月，其余月份对应2023年）生成预测日期字段：

month_to_date = {
    'December': '2022-12-01',
    'January': '2023-01-01',
    'February': '2023-02-01',
    'March': '2023-03-01',
    'April': '2023-04-01',
    'May': '2023-05-01',
    'June': '2023-06-01',
    'July': '2023-07-01',
    'August': '2023-08-01',
    'September': '2023-09-01',
    'October': '2023-10-01',
    'November': '2023-11-01'
}
df_long['Pred_Month'] = pd.to_datetime(df_long['Month'].map(month_to_date))

6. 清洗无效数据 #

过滤掉空行，重置索引即可得到目标结构的DataFrame：

df_final = df_long.dropna(subset=['Cbal', 'CPR']).reset_index(drop=True)

注意事项 #

• 如果按上述步骤读取仍存在大量空列，回到第一步重新确认表头行位置，不要靠估算行号传参
• 遇到跨列合并的单元格，可先使用ffill(axis=1)做横向填充，再做纵向填充
• 如果文件存在大量隐藏行、特殊格式导致read_excel解析错位，可改用openpyxl加载工作簿逐行遍历取值，手动构造DataFrame，解析可控性更高。

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