如何使用 Pandas 读取 Excel的详细解决方案

Pandas 读取 Excel 终极指南：从入门到精通

在数据处理和分析的日常工作中，Excel 是一个不可或缺的工具。然而，当数据量变大、格式不规范或需要自动化处理时，手动操作 Excel 变得低效且容易出错。这时，Python 的 Pandas 库就成了我们的得力助手。Pandas 提供了强大而灵活的 read_excel() 函数，可以轻松地将 Excel 文件中的数据读取为 DataFrame 格式，为后续的数据清洗、分析和可视化铺平道路。

本文将作为一份详细的解决方案，带你全面掌握使用 Pandas 读取 Excel 的各项技能。

目录

1. 准备工作：安装必要的库
2. 核心函数：pd.read_excel() 概览
3. 基础用法：读取一个简单的 Excel 文件
4. 常用参数详解：精细化控制读取过程

• • sheet_name：指定工作表
  • header：指定表头行
  • names：自定义列名
  • usecols：选择要读取的列
  • index_col：指定索引列
  • skiprows 和 skipfooter：跳过行
  • dtype：指定数据类型
  • na_values：处理缺失值

1. 进阶技巧

• • 一次性读取多个工作表
  • 处理大型 Excel 文件时的性能考量

1. 实战演练：清洗一个不规范的 Excel 文件
2. 总结与速查表

1. 准备工作：安装必要的库

在使用 Pandas 读取 Excel 之前，你需要确保已经安装了 Python 和 Pandas。此外，Pandas 读取 Excel 文件依赖于一些第三方库作为“引擎”（Engine）。

• 读取 .xlsx 文件 (新版 Excel 格式): 需要 openpyxl 库。
• 读取 .xls 文件 (旧版 Excel 格式): 需要 xlrd 库。
• 读取 .xlsb 文件 (二进制格式): 需要 pyxlsb 库。

你可以使用 pip 来安装它们：

# 安装 Pandas
pip install pandas

# 安装 Excel 读取引擎 (推荐安装 openpyxl，因为它最常用)
pip install openpyxl xlrd pyxlsb

2. 核心函数：pd.read_excel() 概览

Pandas 读取 Excel 的核心就是 pd.read_excel() 函数。它的基本语法如下：

pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, ...)

这个函数有非常多的参数，但我们只需要掌握其中最常用的几个，就能解决 99% 的问题。

3. 基础用法：读取一个简单的 Excel 文件

假设我们有一个名为 sales.xlsx 的文件，内容如下：

Sheet1:

读取这个文件非常简单：

import pandas as pd

# 读取 Excel 文件，默认读取第一个工作表
df = pd.read_excel('sales.xlsx')

# 打印 DataFrame
print(df)

输出：

          日期  城市 产品   销量
0  2023-01-01  北京  A  100
1  2023-01-01  上海  B  150
2  2023-01-02  北京  B  120
3  2023-01-02  广州  A  200

Pandas 自动将第一行作为表头（列名），并将数据加载到一个整洁的 DataFrame 中。

4. 常用参数详解：精细化控制读取过程

现实中的 Excel 文件往往没有那么规整。read_excel 的强大之处在于它丰富的参数，可以应对各种复杂情况。

sheet_name：指定工作表

如果 Excel 文件有多个工作表，sheet_name 参数就派上用场了。

• 按索引读取 (0 代表第一个，1 代表第二个...):

# 读取第二个工作表
df_sheet2 = pd.read_excel('sales.xlsx', sheet_name=1)

• 按名称读取:

# 读取名为 "Q4_Sales" 的工作表
df_q4 = pd.read_excel('sales.xlsx', sheet_name='Q4_Sales')

header：指定表头行

默认情况下，Pandas 认为第 0 行是表头。如果你的表头在其他行，可以使用 header 参数。

假设 Excel 文件如下，数据从第 3 行开始（索引为 2）：

# 指定第 3 行（索引为 2）作为表头
df = pd.read_excel('report.xlsx', header=2)

如果文件没有表头，可以设置 header=None。

names：自定义列名

当原始文件没有表头时，你可以使用 names 参数来自定义列名。这通常与 header=None 结合使用。

# 文件没有表头，跳过第一行数据，并自定义列名
df = pd.read_excel('no_header.xlsx', header=None, names=['Date', 'City', 'Product', 'Volume'])

usecols：选择要读取的列

如果只对部分列感兴趣，使用 usecols 可以提高读取效率和内存利用率。

• 按列字母读取:

# 只读取 A, C, D 列
df = pd.read_excel('sales.xlsx', usecols='A,C:D')

• 按列名读取:

# 只读取 "日期" 和 "销量" 列
df = pd.read_excel('sales.xlsx', usecols=['日期', '销量'])

• 按索引读取:

# 只读取第 0 列和第 3 列
df = pd.read_excel('sales.xlsx', usecols=[0, 3])

index_col：指定索引列

默认情况下，Pandas 会创建一个从 0 开始的整数索引。你可以指定文件中的某一列作为 DataFrame 的索引。

# 将 "日期" 列作为索引
df = pd.read_excel('sales.xlsx', index_col='日期')

# 也可以使用列的索引
# df = pd.read_excel('sales.xlsx', index_col=0)

skiprows 和 skipfooter：跳过行

• skiprows: 跳过文件顶部的指定行数。
• skipfooter: 跳过文件底部的指定行数。

这对于跳过文件头部的标题或尾部的总计/备注信息非常有用。

# 跳过前 2 行和末尾 1 行
df = pd.read_excel('report.xlsx', header=0, skiprows=2, skipfooter=1)

dtype：指定数据类型

有时 Pandas 可能会错误地推断数据类型（例如，将数字编码的 ID 读取为整数而不是字符串）。dtype 参数可以强制指定列的数据类型。

# 将 "产品ID" 读取为字符串，"销量" 读取为浮点数
df = pd.read_excel('products.xlsx', dtype={'产品ID': str, '销量': float})

na_values：处理缺失值

在实际数据中，缺失值可能以各种形式存在，如 "N/A", "-", "空" 等。na_values 可以告诉 Pandas 这些值都应被视作 NaN (Not a Number)。

# 将 "N/A" 和 "-" 识别为缺失值
df = pd.read_excel('data_with_missing.xlsx', na_values=['N/A', '-'])

5. 进阶技巧

一次性读取多个工作表

如果你想一次性加载所有工作表，或者多个指定的工作表，可以将 sheet_name 设置为 None 或一个列表。

• 读取所有工作表:

# sheet_name=None 会返回一个字典，键是工作表名，值是对应的 DataFrame
all_sheets = pd.read_excel('sales_multi_sheet.xlsx', sheet_name=None)

# 访问第一个工作表的 DataFrame
df_sheet1 = all_sheets['Sheet1']

# 遍历所有工作表
for sheet_name, df in all_sheets.items():
    print(f"--- {sheet_name} ---")
    print(df.head())

• 读取指定的一些工作表:

# 读取 'Q1' 和 'Q2' 两个工作表
quarterly_data = pd.read_excel('sales_multi_sheet.xlsx', sheet_name=['Q1', 'Q2'])

处理大型 Excel 文件时的性能考量

Excel 文件（尤其是 .xlsx）本质上是压缩的 XML 文件，读取大文件时 I/O 开销较大，速度较慢。

• 只加载需要的列：使用 usecols 是最直接有效的优化手段。
• 选择更快的引擎：对于非常大的 .xlsx 文件，可以尝试其他引擎，但 openpyxl 通常是功能最全面的。
• 转换为更高效的格式：如果需要频繁读取一个巨大的 Excel 文件，最佳实践是首次读取后，将其转换为更高效的格式，如 CSV, Parquet 或 Feather。后续直接读取这些格式的文件，速度会快几个数量级。

# 首次读取
df = pd.read_excel('very_large_file.xlsx')
# 转换为 Parquet 格式
df.to_parquet('very_large_file.parquet')

# 后续直接读取 Parquet 文件，速度极快
df_fast = pd.read_parquet('very_large_file.parquet')

6. 实战演练：清洗一个不规范的 Excel 文件

假设我们有一个不规范的报告 messy_report.xlsx：

月度销售报告
生成日期：2023-12-31

ID,下单日期,城市,产品名称,销量,备注
1,2023-11-01,北京,产品A,150,无
2,2023-11-02,上海,产品B,N/A,缺货
3,2023-11-03,深圳,产品C,-,
4,2023-11-04,北京,产品A,200,无

---
数据来源：销售系统

我们的目标是：

1. 跳过顶部的标题和底部的备注。
2. 使用第 3 行作为表头。
3. 只保留 "下单日期", "城市", "产品名称", "销量" 四列。
4. 将 "ID" 列作为索引。
5. 将 "N/A" 和 "-" 视作缺失值。
6. 确保 "下单日期" 被解析为日期类型。

我们可以通过一个 read_excel 调用完成所有操作：

import pandas as pd

# 组合使用多个参数，一次性完成数据清洗和加载
df_cleaned = pd.read_excel(
    'messy_report.xlsx',
    skiprows=3,          # 跳过前3行（标题和空行）
    skipfooter=2,        # 跳过末尾2行（分隔符和备注）
    engine='openpyxl',   # 明确指定引擎
    usecols=['ID', '下单日期', '城市', '产品名称', '销量'], # 选择需要的列
    index_col='ID',      # 将 'ID' 列设为索引
    na_values=['N/A', '-'], # 定义缺失值
    parse_dates=['下单日期'] # 将 '下单日期' 列解析为日期格式
)

print(df_cleaned)
print("\n数据类型信息:")
print(df_cleaned.info())

输出：

            下单日期  城市 产品名称    销量
ID                                 
1  2023-11-01  北京  产品A  150.0
2  2023-11-02  上海  产品B    NaN
3  2023-11-03  深圳  产品C    NaN
4  2023-11-04  北京  产品A  200.0

数据类型信息:
<class 'pandas.core.frame.DataFrame'>
Int64Index: 4 entries, 1 to 4
Data columns (total 4 columns):
 #   Column  Non-Null Count  Dtype         
---  ------  --------------  -----         
 0   下单日期    4 non-null      datetime64[ns]
 1   城市      4 non-null      object        
 2   产品名称    4 non-null      object        
 3   销量      2 non-null      float64       
dtypes: datetime64[ns](1), float64(1), object(2)
memory usage: 160.0+ bytes
None

可以看到，我们成功地将一个格式混乱的 Excel 文件，在读取阶段就直接转换成了一个干净、可供分析的 DataFrame。

7. 总结与速查表

pandas.read_excel() 是一个功能极其丰富的函数。通过熟练掌握其常用参数，你可以极大地提升数据导入和预处理的效率。

read_excel 常用参数速查表

掌握了这些，你已经可以自信地应对绝大多数与 Excel 数据导入相关的挑战了。开始你的数据分析之旅吧！