14  读取Excel文件

14.1 引言Excel在金融数据交换中的地位

尽管Python和数据库在金融分析中日益重要,Excel仍然是:

  • 数据交换: 数据源和报告的标准格式
  • 人工输入: 交易员和分析师的常用工具
  • 遗留系统: 许多老系统仍使用Excel

14.2 本章学习目标

通过本章学习,你将能够:

  1. 使用 pd.read_excel 的 sheet_name、skiprows、usecols、nrows 参数精确读取指定工作表、数据区与行列范围
  2. 解释 converters 与 dtype 的差别,并用 converters 配合自定义函数在读取时清洗特殊标记
  3. 使用 pd.ExcelFile 上下文管理器一次打开文件、多次读取多个工作表
  4. 根据参数与适用场景,比较读取 Excel 与读取 CSV 的异同

先修内容:第 章节 10 章至第 章节 13 章的数据框操作基础。

数据说明:本章使用的 stores.xlsx 为教学平台内置数据文件,本地仓库不包含(相关代码块已注明),读取代码请在教学平台上运行。按本章用法,读取时统一指定 sheet_name='2019'(或 '2020')、skiprows=1(跳过数据区上方的说明行)、usecols='B:F'(只取 B 至 F 列)。其中 Flagship 列在本章只作为演示”读取即清洗”的对象列使用,不额外展开业务口径;其取值中存在空字符串与 'MISSING' 两种缺失标记。按平台代码注释的语义,fix_missing(x) 在 x 为空字符串或 'MISSING' 时返回 False,其余取值原样返回,因此经 converters={'Flagship': fix_missing} 转换后,缺失标记被统一映射为 False。

14.3 read_excel函数

列表 14.1: 使用read_excel读取Excel文件 
# 注：stores.xlsx数据文件本地没有，但平台已经内置

# =============================================================================
# 题目：使用read_excel读取Excel文件
# =============================================================================
# 本示例演示如何从Excel文件读取特定工作表和数据范围
# 金融应用:读取交易所提供的股票行情数据、财务报表Excel文件等

# ==================== 导入库 ====================
import pandas as pd  # 导入Pandas库,用于读取和处理Excel数据

# ==================== 基础读取Excel ====================
# 读取Excel文件的特定工作表和数据范围
df = pd.read_excel(
    "stores.xlsx",  # Excel文件路径(可以是相对路径或绝对路径)
    sheet_name="2019",    # 指定工作表名称(可以是名称字符串或索引数字)
    skiprows=1,           # 跳过前1行(常用于跳过标题行或说明行)
    usecols="B:F"         # 只读取B列到F列(可用字母范围或列名列表)
)
# 返回:包含读取数据的DataFrame对象

print("数据框信息:")  # 打印提示信息
print(df.info())  # 显示DataFrame的详细信息(列名、数据类型、非空值数量等)

参数详解:

  • sheet_name: 工作表名或索引
  • skiprows: 跳过的行数
  • usecols: 读取的列(字母或列名列表)
  • dtype: 指定列的数据类型
  • converters: 列转换函数字典

14.4 数据类型转换

任务要求:分三步读取 stores.xlsx:先按 sheet_name='2019'、skiprows=1、usecols='B:F' 读入 df 并输出 df.info();再定义 fix_missing 函数,以 converters={'Flagship': fix_missing} 重新读入 df2,输出 df2.info() 与整张 df2;最后用 pd.ExcelFile 上下文管理器一次打开文件,分别读取 2019、2020 两个工作表的前 2 行并输出 data1。请将代码原样输入教学平台(注释除外),判定以平台为准。

列表 14.2: 平台原始代码 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
# 注：stores.xlsx数据文件本地没有，但平台已经内置
import pandas as pd  # 导入Pandas数据分析库

df = pd.read_excel("stores.xlsx",sheet_name="2019", skiprows=1, usecols="B:F")  # 从Excel文件读取数据存入df
print(df.info())  # 输出数据框基本信息

def fix_missing(x):  # 定义函数fix_missing
  return False if x in ["", "MISSING"] else x  # 返回计算结果

# 从Excel文件读取数据存入df2
df2 = pd.read_excel("stores.xlsx",sheet_name="2019", skiprows=1, usecols="B:F",converters={"Flagship": fix_missing})
print(df2.info())  # 输出数据框基本信息
print(df2)  # 输出数据框数据

with pd.ExcelFile("stores.xlsx") as f:  # 使用上下文管理器
  # 从Excel文件读取数据存入data1
  data1 = pd.read_excel(f, "2019", skiprows=1, usecols="B:F", nrows=2,converters={"Flagship": fix_missing})
  # 从Excel文件读取数据存入data2
  data2 = pd.read_excel(f, "2020", skiprows=1, usecols="B:F", nrows=2,converters={"Flagship": fix_missing})

print(data1)  # 输出数据数据

预期输出(数据文件为教学平台内置,本机无法复现,判读要点如下;具体数值以平台运行结果为准):

  • 先看第一段 df.info():留意 Flagship 列的 dtype 与非空计数(non-null)——该列含有特殊标记,读取行为与普通列不同
  • 再对比第二段 df2.info() 中 Flagship 列的变化:converters 在读取时逐单元格调用 fix_missing,把空字符串与 'MISSING' 映射为 False;print(df2) 随后输出整张清洗后的表
  • 最后输出 2019 年工作表前 2 行的 data1(nrows=2);若在本地复现后续代码块,需先运行本代码块以使 fix_missing 已定义

14.5 ExcelFile类高效读取多表

列表 14.3: 使用ExcelFile类读取多个工作表 
# 注：stores.xlsx数据文件本地没有，且fix_missing函数定义于上方平台任务代码块中

# =============================================================================
# 题目：使用ExcelFile类高效读取多个工作表
# =============================================================================
# 本示例演示使用ExcelFile类一次性打开文件并读取多个工作表
# 金融应用:读取同一Excel文件中的多年财务数据、多个子公司的报表等

# ==================== 使用with语句打开Excel文件 ====================
# 使用ExcelFile类打开文件(提高多次读取效率)
with pd.ExcelFile("stores.xlsx") as f:
    # with语句确保文件在使用后自动关闭,释放系统资源
    # f是ExcelFile对象,代表已打开的Excel文件

    # ==================== 读取2019年数据 ====================
    # 读取2019年工作表的前2行数据
    data1 = pd.read_excel(
        f,  # 传入已打开的ExcelFile对象,而非文件路径
        "2019",  # 工作表名称
        skiprows=1,  # 跳过第1行
        usecols="B:F",  # 读取B到F列
        nrows=2,  # 只读取前2行数据(用于快速预览或测试)
        converters={"Flagship": fix_missing}  # 应用转换函数
    )
    # nrows参数常用于:数据预览、限制读取量、测试数据格式等

    # ==================== 读取2020年数据 ====================
    # 读取2020年工作表的前2行数据
    data2 = pd.read_excel(
        f,  # 复用同一个ExcelFile对象,避免重复打开文件
        "2020",  # 工作表名称
        skiprows=1,  # 跳过第1行
        usecols="B:F",  # 读取B到F列
        nrows=2,  # 只读取前2行
        converters={"Flagship": fix_missing}  # 应用转换函数
    )

# ==================== 显示读取结果 ====================
print("2019年前2行:")  # 打印提示信息
print(data1)  # 显示2019年数据
print(f"\n2020年前2行:")  # 打印提示信息(带换行)
print(data2)  # 显示2020年数据

优势:

  • 只打开文件一次
  • 适合读取多个工作表
  • 自动管理文件句柄

14.6 本章小结

要点:

  • sheet_name 选工作表,skiprows 跳过说明行,usecols 圈定列区,nrows 限制读取行数
  • converters 在读取时逐单元格调用函数,适合清洗空字符串与 'MISSING' 这类特殊标记
  • pd.ExcelFile 配合 with 只打开一次文件即可读取多个工作表,并自动管理文件句柄
  • 复用其他代码块定义的函数(如 fix_missing)时,先运行那个代码块

易错点:

  • usecols='B:F'(按列位置)与 usecols=['列名', ...](按列名)两种写法依据不同,混用易错位
  • skiprows=1 跳过的是数据区上方的行,若表头不止一行,读到的列名会不对
  • 忘记先定义 fix_missing 就运行使用它的代码块,会抛出 NameError

14.7 动手与思考

以下练习每题附参考答案(默认折叠)。请先独立完成并写下你的判断,再点开对照,最后上机验证。

  1. 输出预测:不运行代码,先写出下面代码的输出结果,再上机检验你的判断。

    import pandas as pd

def fix_missing(x):
    return False if x in ['', 'MISSING'] else x

df = pd.DataFrame({'Flagship': [True, '', 'MISSING', False]})
print(df['Flagship'].apply(fix_missing))

    参考答案（先写下你的预测再点开）

    解题思路：apply 把 fix_missing 逐个作用到列的每个元素上：True 不在 ['', 'MISSING'] 中，原样返回 True；空字符串 '' 命中列表，返回 False；'MISSING' 命中列表，返回 False；False 不在列表中，原样返回 False。四个返回值恰好都是布尔值，pandas 因此把整列推断为 bool 类型（而不是 object）。另要注意：空字符串 '' 是真实存在的字符串值，与缺失值 NaN 不同——'' in ['', 'MISSING'] 命中，而 NaN 参与比较既不等于 '' 也不等于 'MISSING'，会走 else 分支原样返回。

    # 验证脚本：观察fix_missing逐元素清洗后的取值与类型
import pandas as pd  # 导入Pandas库

def fix_missing(x):  # 与题面相同的清洗函数
    return False if x in ['', 'MISSING'] else x

df = pd.DataFrame({'Flagship': [True, '', 'MISSING', False]})  # 含两种缺失标记的Flagship列
print(df['Flagship'].apply(fix_missing))  # 逐元素清洗，缺失标记统一映射为False

    预期输出（本机 peter 环境实际运行结果，具体以平台运行结果为准）：

    0     True
1    False
2    False
3    False
Name: Flagship, dtype: bool

    回扣本章：对应本章小结“要点”第 2 条——converters（以及等价的 apply 后清洗）在读取时逐单元格调用函数，适合清洗空字符串与 'MISSING' 这类特殊标记。

  2. 概念辨析:converters 与 dtype 分别在读取的哪个环节起作用?为什么含 'MISSING' 标记的列不能直接声明 dtype=bool?pd.read_excel 与 pd.ExcelFile 各适合什么场景?

    参考答案（点开前请先独立完成）

    解题思路：逐点作答。第一，环节不同：converters 作用在“读取解析”环节——每读到一个单元格就调用一次指定函数，先转换后成列，可以把 ''、'MISSING' 这类特殊标记在读入时就映射成合法取值；dtype 作用在“成列定类型”环节——整列解析完成后强制声明类型，不做任何清洗，遇到无法映射的值直接抛异常。第二，含 'MISSING' 的列不能声明 dtype=bool：布尔类型转换要求每个值本身可映射为 True/False（如 0/1、'True'/'False'），'MISSING' 无法映射，会抛 ValueError；正确顺序是先用 converters={'Flagship': fix_missing} 把标记清洗成 False，清洗后整列取值自然成为布尔，类型不需要再强制。第三，分工：pd.read_excel 每次调用都会完整打开并解析一次文件，读单表最直接；pd.ExcelFile 配合 with 只打开一次文件、句柄可复用，同一文件的多个工作表或多次读取共享一次解析开销，并自动管理文件句柄。

    回扣本章：对应本章小结“要点”第 2、3 条与“易错点”第 3 条——converters 先清洗、dtype 后定类型；pd.ExcelFile 适合一次打开、读多张工作表。

  3. 变式任务(平台数据):在教学平台上用 pd.ExcelFile 一次打开 stores.xlsx,分别读取 2019 与 2020 两个工作表的全部数据,比较两年门店数量的变化。

    参考答案（点开前请先独立完成）

    解题思路：改造 列表 14.2 第三步的读取语句。第一步，把 with pd.ExcelFile('stores.xlsx') as f: 中的两次读取各去掉 nrows=2（读全部数据而非前 2 行），保留 skiprows=1（跳过数据区上方的说明行）与 usecols='B:F'，分别存入 df_2019、df_2020 两个数据框；converters={'Flagship': fix_missing} 可保留，也可视比较目标省去。第二步，比较两年门店数量：若每行对应一家门店，直接对比 df_2019.shape[0] 与 df_2020.shape[0]；若数据含门店编号列，更稳妥的是对比 nunique()（唯一门店数），可避免重复行干扰。若想同时看增减方向，可用 pd.concat([df_2019.assign(年份=2019), df_2020.assign(年份=2020)]) 拼接后 groupby('年份').size() 一并汇总。

    结构性判读：输出应为两个整数（两年各自的门店行数）或一张按年份汇总的行数表。判读要点看三件事：两年行数是否相等（不等说明有门店进出）；差异的方向与幅度（净增还是净减、幅度是否与业务背景相称）；用唯一门店数复核行数差异是否由重复记录造成。

    预期输出：数据文件由教学平台内置，本机无法复现，具体数值以平台运行结果为准。

    注意：以上为本题变式的思路，列表 14.2 对应的平台原始代码仍须原样输入教学平台，不要用本变式替换。

    回扣本章：对应本章小结“要点”第 1、3 条——skiprows/usecols/nrows 圈定读取范围，pd.ExcelFile 一次打开即可读取多个工作表。

  4. 动手验证:本地用 to_excel 自建一个含两个工作表的小文件,再分别用 sheet_name=0、sheet_name=1、nrows=2、skiprows=1 读取,逐一观察参数对读取结果的影响。

    参考答案（点开前请先独立完成）

    解题思路：先自建 4 行小表并用 ExcelWriter 写出两个工作表，且仿照正文中 stores.xlsx 的布局用 startrow=1 把数据区整体下移一行（第 1 行留空）。然后四个参数逐一观察：sheet_name=0 与 sheet_name=1 按位置选表，0 是第 1 张（Sheet1）、1 是第 2 张（Sheet2）；nrows=2 只读表头之后的前 2 行数据；skiprows=1 跳过文件最上方的 1 行——本例文件首行是空行，跳过它之后真正的表头才会被识别。对照最后一行输出：不写 skiprows 时，空行被当作表头，列名全部变成 Unnamed: 0、Unnamed: 1，真正的表头被挤成第一行数据，这正是正文强调 skiprows=1 用途的原因。

    # 验证脚本：自建双工作表文件并观察四个读取参数的效果
import pandas as pd  # 导入Pandas库

demo_df = pd.DataFrame({'代码': ['600000.SH', '600036.SH', '601318.SH', '600519.SH'], '收盘价': [10.5, 45.2, 55.8, 1700.0]}, index=pd.date_range('2024-01-01', periods=4))  # 自建4行小表
demo_df.index.name = '交易日'  # 给行索引命名，写出的首列才有表头
with pd.ExcelWriter('demo_two_sheets.xlsx') as writer:  # 一次写出两个工作表（数据区上方留1个空行，模拟正文stores.xlsx的布局）
    demo_df.to_excel(writer, sheet_name='Sheet1', startrow=1)  # Sheet1：自第2行起写
    demo_df.to_excel(writer, sheet_name='Sheet2', startrow=1)  # Sheet2：同样布局
print(pd.read_excel('demo_two_sheets.xlsx', sheet_name=0, skiprows=1))  # sheet_name=0按位置取第1张表；skiprows=1跳过空行后表头才正确
print(pd.read_excel('demo_two_sheets.xlsx', sheet_name=1, skiprows=1, nrows=2))  # sheet_name=1取第2张表；nrows=2只读前2行数据
print(pd.read_excel('demo_two_sheets.xlsx', sheet_name=0))  # 对照：不写skiprows时空行被当成表头，列名全变Unnamed

    预期输出（本机 peter 环境实际运行结果，具体以平台运行结果为准）：

             交易日         代码     收盘价
0 2024-01-01  600000.SH    10.5
1 2024-01-02  600036.SH    45.2
2 2024-01-03  601318.SH    55.8
3 2024-01-04  600519.SH  1700.0
         交易日         代码   收盘价
0 2024-01-01  600000.SH  10.5
1 2024-01-02  600036.SH  45.2
            Unnamed: 0 Unnamed: 1 Unnamed: 2
0                  交易日         代码        收盘价
1  2024-01-01 00:00:00  600000.SH       10.5
2  2024-01-02 00:00:00  600036.SH       45.2
3  2024-01-03 00:00:00  601318.SH       55.8
4  2024-01-04 00:00:00  600519.SH       1700

    回扣本章：对应本章小结“要点”第 1 条与“易错点”第 2 条——skiprows 跳过数据区上方的行，nrows 限制读取行数；说明行没跳对，读到的列名就不对。