掌握 Pandas read_excel：参数、技巧与最佳实践 – wiki基地

作者： /

精通 Pandas read_excel：参数、技巧与最佳实践

在数据分析的生态系统中，Microsoft Excel 文件（.xls 或 .xlsx）仍然是一种极为普遍的数据存储和交换格式。无论是业务报告、实验数据、还是简单的列表，Excel 的普及性意味着数据分析师们不可避免地需要与这些文件打交道。Python 的 Pandas 库凭借其强大的数据处理能力，成为了数据科学领域的基石，而 pandas.read_excel() 函数则是连接 Python 世界与 Excel 文件之间的关键桥梁。

read_excel() 函数不仅仅是一个简单的文件读取工具，它提供了极其丰富和灵活的参数选项，能够应对各种复杂和“不规范”的 Excel 文件格式。掌握这个函数不仅能让你高效地将数据导入 Pandas DataFrame，更能让你在数据加载阶段就完成初步的数据清洗和塑形。本文将深入探讨 read_excel() 的核心参数、实用技巧以及推荐的最佳实践，帮助你从入门到精通，自如应对各种 Excel 数据读取挑战。

一、read_excel() 基础入门

在深入细节之前，让我们先看一个最基本的使用示例。假设我们有一个名为 data.xlsx 的 Excel 文件，其中包含一个名为 Sheet1 的工作表，数据从第一行开始，且第一行是列标题。

“`python
import pandas as pd

最简单的读取方式

try:
df = pd.read_excel(‘data.xlsx’)
print(df.head())
except FileNotFoundError:
print(“错误：文件 ‘data.xlsx’ 未找到。”)
except Exception as e:
print(f”读取 Excel 文件时发生错误: {e}”)

“`

在这个最简单的调用中，read_excel() 会执行以下默认行为：

  1. 读取指定路径下的 Excel 文件 (data.xlsx)。
  2. 默认读取文件的第一个工作表 (Sheet)。
  3. 默认将工作表的第一行作为列名 (header)。
  4. 默认将工作表的第一列作为行索引 (index) 吗？并非如此。默认情况下，Pandas 会自动创建一个从 0 开始的整数行索引。
  5. 尝试推断每一列的数据类型。

虽然这个简单的调用在许多情况下足够用，但现实世界中的 Excel 文件往往更加复杂。数据可能不在第一个工作表，可能没有标题行，可能包含需要跳过的注释行，或者你可能只需要读取特定的列或行。这时，我们就需要利用 read_excel() 丰富的参数集。

二、核心参数详解

read_excel() 提供了大量参数来定制读取过程。下面我们将详细介绍其中最常用和最重要的参数。

1. io:
这是函数的第一个参数，用于指定要读取的 Excel 文件的来源。

  • 字符串 (String): 最常见的用法，直接提供文件的本地路径或 URL。
    python
    df_local = pd.read_excel('path/to/your/local/data.xlsx')
    # df_url = pd.read_excel('http://example.com/data.xlsx') # 需要网络连接和相应库支持
  • ExcelFile 对象: 当你需要多次从同一个文件读取不同工作表或进行更复杂的交互时，先创建一个 ExcelFile 对象会更高效，因为它只打开文件一次。
    python
    excel_file = pd.ExcelFile('data.xlsx')
    df1 = pd.read_excel(excel_file, sheet_name='Sheet1')
    df2 = pd.read_excel(excel_file, sheet_name='Sheet2')
  • 文件类对象 (File-like object): 可以是任何具有 read() 方法的对象，例如通过 open() 打开的文件对象或 BytesIO 对象。这在你需要处理内存中的 Excel 数据流时非常有用。
    python
    from io import BytesIO
    # 假设 excel_data 是包含 Excel 文件内容的字节串
    # excel_data = get_excel_data_from_api_or_elsewhere()
    # with open('data.xlsx', 'rb') as f:
    # excel_data = f.read()
    # df_memory = pd.read_excel(BytesIO(excel_data))

2. sheet_name:
指定要读取哪个或哪些工作表。这是非常关键的参数。

  • 整数 (Integer): 读取指定索引的工作表（从 0 开始）。sheet_name=0 读取第一个工作表（默认行为），sheet_name=1 读取第二个，以此类推。
    python
    df_sheet1 = pd.read_excel('data.xlsx', sheet_name=0)
  • 字符串 (String): 读取指定名称的工作表。
    python
    df_sales = pd.read_excel('data.xlsx', sheet_name='SalesData')
  • 列表 (List): 读取列表中指定索引或名称的多个工作表。返回值是一个字典，其中键是工作表索引或名称，值是对应的 DataFrame。
    python
    dict_of_dfs = pd.read_excel('data.xlsx', sheet_name=[0, 'SalesData', 2])
    df_sheet1 = dict_of_dfs[0]
    df_sales = dict_of_dfs['SalesData']
  • None: 读取文件中的所有工作表。返回值同样是一个包含所有工作表数据的字典。
    python
    all_sheets_dict = pd.read_excel('data.xlsx', sheet_name=None)
    for sheet_name, df_sheet in all_sheets_dict.items():
    print(f"\n--- 工作表: {sheet_name} ---")
    print(df_sheet.head())
  • 注意: 如果指定的工作表不存在，Pandas 会抛出错误。

3. header:
指定哪一行（或多行）作为列名。

  • 整数 (Integer): 指定行号（从 0 开始）作为列名。例如，header=0 表示第一行是列名（默认）。header=1 表示第二行是列名。
    python
    # 假设数据标题在第 3 行 (index 2)
    df = pd.read_excel('data.xlsx', sheet_name='Report', header=2)
  • None: 如果工作表中没有标题行，设置 header=None。Pandas 会使用默认的整数列名 (0, 1, 2, …)，并且所有行都会被视为数据。你通常需要配合 names 参数来自定义列名。
    python
    df_no_header = pd.read_excel('data_no_header.xlsx', header=None, names=['ColA', 'ColB', 'ColC'])
  • 列表 (List of Integers): 用于处理多级/层级标题 (MultiIndex)。例如，header=[0, 1] 表示前两行共同构成了列名的层级结构。
    python
    # 假设 Excel 中第 1 行和第 2 行是合并的标题
    # | Group A | Group B |
    # | Val1 | Val2 | Val1 | Val2 |
    #---|---------|---------|---------|--------|
    # ... data ...
    df_multiindex_header = pd.read_excel('multi_header.xlsx', header=[0, 1])
    print(df_multiindex_header.columns) # 会显示一个 MultiIndex 对象

4. names:
一个列表，用于在没有标题行 (header=None) 或希望覆盖现有标题行时，提供自定义的列名。列表的长度应与数据列数匹配。

“`python
df_custom_names = pd.read_excel(‘data.xlsx’, header=0, names=[‘ID’, ‘UserName’, ‘ScoreValue’, ‘DateRecorded’])

注意：即使 header=0，names 也会覆盖 Excel 文件中的标题行

“`

5. index_col:
指定哪一列（或多列）作为 DataFrame 的行索引 (index)。

  • 整数 (Integer): 指定列的索引（从 0 开始）作为行索引。
    python
    # 使用第一列 ('ID') 作为行索引
    df_indexed = pd.read_excel('data.xlsx', index_col=0)
  • 字符串 (String): 指定列的名称作为行索引（这要求已经正确设置了 header）。
    python
    df_indexed_by_name = pd.read_excel('data.xlsx', header=0, index_col='StudentID')
  • 列表 (List of Integers/Strings): 创建一个多级/层级行索引 (MultiIndex)。
    python
    # 使用 'Category' 和 'SubCategory' 列作为联合索引
    df_multiindex_row = pd.read_excel('data.xlsx', header=0, index_col=['Category', 'SubCategory'])
  • False 或 None: 不使用任何列作为行索引，Pandas 会创建默认的整数索引（这是默认行为，除非你指定了 index_col）。

6. usecols:
非常重要的参数，用于选择要读取的特定列。这对于处理宽表格（列数很多）或优化内存使用非常有效。

  • 字符串 (String): 使用 Excel 风格的列范围表示法，如 ‘A:E’（读取 A 到 E 列），’A,C,E:F’（读取 A 列、C 列、E 到 F 列）。
    python
    df_subset_cols = pd.read_excel('data.xlsx', usecols='A,C:E,G')
  • 列表 (List of Integers/Strings): 提供要读取的列的索引（0-based）或列名（需要 header 已确定）。
    python
    df_subset_by_index = pd.read_excel('data.xlsx', usecols=[0, 2, 4]) # 读取第 1, 3, 5 列
    df_subset_by_name = pd.read_excel('data.xlsx', header=0, usecols=['ID', 'ScoreValue', 'DateRecorded'])
  • 可调用对象 (Callable): 一个函数，接受列名作为输入，返回 True 表示读取该列，False 表示跳过。这对于基于列名模式进行选择非常方便。
    python
    def select_cols(col_name):
    return 'ID' in col_name or col_name.endswith('Date')
    df_callable_cols = pd.read_excel('data.xlsx', header=0, usecols=select_cols)

7. skiprows:
指定需要跳过的起始行数或具体的行号（0-based）。

  • 整数 (Integer): 跳过文件顶部的 N 行。
    python
    # 跳过文件开头的 5 行注释或空白行
    df_skipped_top = pd.read_excel('data.xlsx', skiprows=5, header=0) # 注意 header 是相对于跳过后的行
  • 列表 (List of Integers): 跳过指定的行号（例如，跳过第 0, 2, 5 行）。
    python
    df_skipped_specific = pd.read_excel('data.xlsx', skiprows=[0, 2, 5])
  • 可调用对象 (Callable): 一个函数，接受行号索引作为输入，返回 True 表示跳过该行，False 表示保留。

8. nrows:
指定从文件（跳过 skiprows 之后）开始，总共要读取的数据行数。对于快速预览大型文件或进行内存限制下的分析很有用。

“`python

只读取前 100 行数据 (在跳过 skiprows 和 header 之后)

df_preview = pd.read_excel(‘large_data.xlsx’, nrows=100)
“`

9. skipfooter:
指定需要从文件底部跳过的行数。这对于忽略文件末尾的总计行或注释非常有用。注意: 使用此参数通常需要指定 engine='openpyxl' 或 engine='pyxlsb'，因为默认的 xlrd 引擎（对于 .xls 文件）可能不支持。

“`python

忽略文件末尾的 3 行

try:
df_skip_footer = pd.read_excel(‘data.xlsx’, skipfooter=3, engine=’openpyxl’)
except ImportError:
print(“需要安装 openpyxl: pip install openpyxl”)
“`

10. na_values:
指定哪些值在读取时应被视为空值 (NaN)。

  • 单个值 (Scalar): 一个字符串或数字，在所有列中遇到时都被视作 NaN。
    python
    df_na = pd.read_excel('data.xlsx', na_values='N/A')
  • 列表 (List): 列表中的所有值在任何列中遇到时都被视作 NaN。
    python
    df_na_list = pd.read_excel('data.xlsx', na_values=['N/A', 'Not Available', -999, '--'])
  • 字典 (Dictionary): 为特定的列指定不同的 NaN 标识符。键是列名或列索引，值是要在该列中识别为 NaN 的值（可以是单个值或列表）。
    python
    na_dict = {
    'ScoreValue': [-999, -1],
    'Comments': ['Missing', ''], # 注意: 默认情况下空字符串不被视作 NaN，除非在此指定
    'Status': ['Unknown']
    }
    df_na_dict = pd.read_excel('data.xlsx', header=0, na_values=na_dict, keep_default_na=True)
    # keep_default_na=True 表示除了自定义的 na_values 外，仍然保留 Pandas 默认的 NaN 识别（如 '#N/A', 'NA', 'NaN'等）
    # keep_default_na=False 则只使用你提供的 na_values

11. dtype:
非常重要的参数，用于显式指定列的数据类型。避免 Pandas 自动推断类型可能导致的错误或性能问题（特别是对于包含混合类型或大量数值的列被错误推断为 object 的情况）。

  • 字典 (Dictionary): 键是列名或列索引，值是期望的数据类型 (如 str, int, float, np.int64, np.float64, bool, datetime64[ns], timedelta64[ns], category, object)。
    python
    dtype_spec = {
    'StudentID': str, # 确保 ID 被当作字符串处理，防止前导零丢失
    'ScoreValue': float,
    'DateRecorded': 'datetime64[ns]', # 明确指定为日期时间类型
    'Category': 'category' # 对于取值范围有限的列，使用 category 可以节省内存
    }
    df_typed = pd.read_excel('data.xlsx', header=0, dtype=dtype_spec)
  • 指定全局类型: dtype=str 会尝试将所有列读取为字符串。
  • 好处:
    • 准确性: 确保数据按预期类型加载。
    • 性能: 对于数值类型，避免了先作为 object 加载再转换的开销。
    • 内存效率: 使用如 category, np.int32 等比默认 object 或 np.int64 更节省内存。

12. parse_dates:
尝试将指定的列解析为日期时间类型 (datetime64[ns])。

  • 布尔值 (Boolean): parse_dates=True 会尝试将看起来像日期的列（特别是作为索引的列）解析为日期。通常不推荐，因为它可能行为不确定。
  • 列表 (List of Integers/Strings): 提供需要解析为日期的列的索引或名称。
    python
    df_dates = pd.read_excel('data.xlsx', header=0, parse_dates=['DateRecorded', 'LastUpdate'])
  • 列表的列表 (List of Lists): 将多个列合并起来解析为一个日期时间列。例如，如果日期和时间分开在两列。
    python
    # 假设有 'Date' 列和 'Time' 列
    df_combined_datetime = pd.read_excel('data.xlsx', header=0, parse_dates=[['Date', 'Time']])
    # 这会生成一个名为 'Date_Time' 的新列 (或覆盖第一个列名)
  • 字典 (Dictionary): 类似列表的列表，但可以为合并后的新列指定名称。
    python
    date_spec = {'DateTimeColumn': ['Date', 'Time']}
    df_named_datetime = pd.read_excel('data.xlsx', header=0, parse_dates=date_spec)
  • 注意: 虽然 parse_dates 很方便，但如果日期格式复杂或不一致，解析可能会失败或变慢。在这种情况下，更稳健的方法是使用 dtype=str 读取，然后使用 pd.to_datetime() 进行更精细的转换和错误处理。

13. engine:
指定用于读取 Excel 文件的底层库。Pandas 会根据文件扩展名自动选择，但有时需要手动指定。

  • None (默认):
    • 对于 .xlsx 文件，尝试 openpyxl (如果已安装)。
    • 对于 .xls 文件，使用 xlrd。
    • 对于 .xlsb 文件，尝试 pyxlsb (如果已安装)。
    • 对于 .odf 文件，尝试 odfpy (如果已安装)。
  • 'openpyxl': 推荐用于 .xlsx 文件。功能更全面，支持更多现代 Excel 特性，并且是目前处理 .xlsx 的活跃开发库。需要安装 (pip install openpyxl)。
  • 'xlrd': 主要用于读取旧版 .xls 文件。重要提示: 从 xlrd 2.0.0 版本开始，它移除了对 .xlsx 文件的支持。如果你需要用 xlrd 读取 .xlsx（不推荐，除非有特定原因），需要安装旧版本 (pip install xlrd==1.2.0)。
  • 'pyxlsb': 用于读取 Excel 二进制格式 .xlsb 文件。需要安装 (pip install pyxlsb)。
  • 'odf': 用于读取 OpenDocument Format (.ods) 文件。需要安装 (pip install odfpy)。

为什么需要关心 engine？

  • 文件格式支持: 确保使用了能处理你文件类型的引擎。
  • 特性支持: 某些参数（如 skipfooter）可能只在特定引擎下工作。
  • 性能: 不同引擎在处理大型文件时性能可能有差异。openpyxl 通常是 .xlsx 的不错选择。

14. comment:
指定一个字符（或多个字符的字符串），当某行的开头是这个字符时，该行（及其之后的部分）会被视为注释而被忽略。

“`python

忽略以 ‘#’ 开头的行

df_no_comments = pd.read_excel(‘data_with_comments.xlsx’, comment=’#’)
“`

15. thousands:
指定千位分隔符（如 , 或 .），以便在读取数值时将其移除。

“`python

处理带有逗号千位分隔符的数字，如 “1,234.56”

df_thousands = pd.read_excel(‘data_formatted.xlsx’, thousands=’,’)
“`

三、高级技巧与应用场景

掌握了核心参数后，我们可以探索一些更高级的技巧和特定场景下的应用。

1. 读取大型 Excel 文件

Excel 文件可能非常大，一次性加载到内存中可能导致 MemoryError。

  • 选择性加载:
    • 使用 usecols 只加载必要的列。
    • 使用 nrows 只加载部分行（用于分析或测试）。
    • 指定明确的 dtype，特别是对数值类型使用更小的整数类型（如 np.int32）或对低基数列使用 category，可以显著减少内存占用。

  • 分块读取 (chunksize):
    read_excel() 支持 chunksize 参数。设置 chunksize=N 会使函数返回一个迭代器，每次迭代产生一个包含 N 行数据的 DataFrame。这允许你逐块处理大文件，而无需将整个文件加载到内存。

    “`python
    chunk_iter = pd.read_excel(‘very_large_data.xlsx’, chunksize=10000) # 每次读取 10000 行

    all_results = []
    for chunk_df in chunk_iter:
    # 在这里对每个 chunk_df 进行处理
    processed_chunk = chunk_df[chunk_df[‘Value’] > 0] # 示例处理
    all_results.append(processed_chunk)

    如果需要，最后合并所有处理过的块

    final_df = pd.concat(all_results, ignore_index=True)
    “`

2. 处理不规范的表头和结构

  • 多行注释/元数据: 使用 skiprows 跳过文件顶部的非数据行。
  • 数据起始行不固定: 可能需要先用 openpyxl 或类似库检查文件，确定数据实际开始的行号，再传递给 skiprows 和 header。
  • 合并单元格: Pandas 读取合并单元格时，通常只在合并区域的左上角单元格读取值，其他单元格读取为 NaN。这可能需要后续处理，例如使用 fillna(method='ffill') 向下填充。建议尽可能在 Excel 源文件中取消合并单元格。
  • 页脚/总计行: 使用 skipfooter 参数（需要合适的 engine）。如果 skipfooter 不可用或不满足需求，可以在加载整个 DataFrame 后，使用 df.iloc[:-n] 来移除最后的 n 行。

3. 循环读取多个文件或工作表

  • 读取同一文件夹下所有 Excel 文件: 可以使用 os 或 glob 库找到所有文件，然后循环调用 read_excel。

    “`python
    import os
    import glob

    folder_path = ‘path/to/excel/files/’
    all_files = glob.glob(os.path.join(folder_path, “.xlsx”)) # 或 “.xls”

    list_of_dfs = []
    for f in all_files:
    try:
    df = pd.read_excel(f, / 其他参数 /)
    # 可以添加一列来标识数据来源文件
    df[‘source_file’] = os.path.basename(f)
    list_of_dfs.append(df)
    except Exception as e:
    print(f”读取文件 {f} 时出错: {e}”)

    combined_df = pd.concat(list_of_dfs, ignore_index=True)
    ``
    * **读取一个文件中的所有工作表**: 使用    sheet_name=None`，然后处理返回的字典。

    “`python
    all_sheets = pd.read_excel(‘multi_sheet_file.xlsx’, sheet_name=None)
    combined_sheets_df = pd.concat(all_sheets.values(), ignore_index=True, keys=all_sheets.keys())

    keys 参数会在合并后的 DataFrame 中添加一个 MultiIndex，标识数据来自哪个工作表

    “`

4. 处理数据类型问题

  • 混合类型列: Excel 中的列有时会包含数字和文本的混合。Pandas 默认可能将此类列推断为 object 类型。如果需要进行数值计算，后续需要清理和转换。使用 dtype=str 读取，然后尝试用 pd.to_numeric(errors='coerce') 转换是常用策略。
  • 日期时间解析: 如前所述，parse_dates 很方便，但对于复杂或不一致的格式，推荐 dtype=str 读取，然后用 pd.to_datetime(df['DateColumn'], format='...', errors='coerce') 进行更精确、容错的转换。

四、最佳实践

为了编写更健壮、高效、可维护的代码，以下是一些使用 read_excel() 的最佳实践：

  1. 显式优于隐式:

    • 总是指定 sheet_name: 即使你只需要第一个工作表，明确写 sheet_name=0 或 sheet_name='SheetName' 可以让代码意图更清晰，并防止因 Excel 文件结构变化（如添加新工作表到开头）而导致的潜在错误。
    • 明确指定 header: 根据你的数据结构确定标题行，不要过度依赖默认值 header=0。如果无标题，使用 header=None 并配合 names。
    • 尽可能指定 dtype: 这是最重要的实践之一。它可以防止类型推断错误，提高加载性能，减少内存使用，并使后续的数据处理更可靠。对于 ID 类、邮政编码等应为字符串的数值，尤其要指定 dtype=str。
    • 使用 usecols: 只加载你需要的列，特别是处理宽表时。这能显著提高性能和降低内存消耗。

  2. 管理内存使用:

    • 对于大文件，优先考虑 usecols, nrows, dtype 和 chunksize。
    • 选择合适的 dtype，如 category 用于低基数分类变量，int32/float32 用于不需要 64 位精度的数值。

  3. 选择合适的引擎:

    • .xlsx 文件优先使用 engine='openpyxl'。确保已安装。
    • .xls 文件使用 engine='xlrd'（注意版本对 .xlsx 的支持）。
    • .xlsb 文件使用 engine='pyxlsb'。
    • 如果你使用了 skipfooter 等特定引擎才支持的参数，务必指定该引擎。

  4. 错误处理:

    • 使用 try...except 块来捕获 FileNotFoundError 和其他潜在的读取错误（如 ValueError for 无效参数, xlrd.biffh.XLRDError for 文件损坏或格式问题, ImportError for 缺少引擎库）。
    • 对于分块读取或循环读取多个文件，确保错误处理逻辑能处理单个文件/块的失败，而不是让整个过程崩溃。

  5. 代码可读性与维护性:

    • 将常用的参数组合（如 dtype 字典）定义为变量，使 read_excel 调用更简洁。
    • 添加注释解释为什么选择特定的参数值，尤其是对于 skiprows, usecols, na_values 等可能不直观的设置。

  6. 考虑数据预处理:

    • 如果 Excel 文件结构非常混乱（大量合并单元格、不规则布局、嵌入图表等），有时在 Python 中处理会非常困难。考虑是否可以在数据源头（Excel 中）进行一些清理和标准化，或者使用专门的 Excel 处理库（如 openpyxl）进行更底层的解析，然后再构造 DataFrame。

  7. 版本控制:

    • 如果可能，将你的输入 Excel 文件纳入版本控制系统（如 Git，可能需要 Git LFS 处理大文件），以便追踪数据源的变化。

五、总结

pandas.read_excel() 是 Pandas 库中一个功能强大且极其灵活的函数，是数据分析师日常工作中不可或缺的工具。它不仅仅是简单地读取数据，更提供了一套丰富的选项来应对现实世界中各种格式和结构的 Excel 文件。

通过深入理解并熟练运用 io, sheet_name, header, index_col, usecols, dtype, parse_dates, skiprows, nrows, na_values, engine 等核心参数，结合分块读取 (chunksize)、错误处理等技巧，你可以高效、准确地将 Excel 数据导入 Pandas DataFrame，并为后续的分析和处理打下坚实的基础。

遵循最佳实践，如明确指定参数、管理内存、选择合适引擎、进行错误处理，将使你的数据加载代码更加健壮、高效和易于维护。掌握 read_excel()，意味着你掌握了打通 Excel 数据孤岛、释放数据价值的关键一步。不断实践和探索，你会发现这个函数比你最初想象的更加强大。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部