Pandas read_excel 函数详解与示例 – wiki基地

作者： /

Pandas read_excel 函数详解与示例：驾驭 Excel 数据导入的瑞士军刀

在数据分析、科学计算以及日常办公自动化中，Excel 文件（.xls, .xlsx）无疑是最常见的数据存储格式之一。无论是业务报表、实验记录还是各种数据集合，Excel 文件都扮演着重要角色。然而，直接操作大型或结构复杂的 Excel 文件进行数据清洗和分析往往效率低下且容易出错。这时，强大的数据处理库 Pandas 就成为了连接 Excel 与 Python 世界的桥梁。

Pandas 提供了多种数据读取函数，其中用于读取 Excel 文件的核心函数便是 pd.read_excel()。这个函数功能异常强大且灵活，能够处理各种复杂的 Excel 文件结构，包括多工作表、不规范的表头、缺失值、混合数据类型等。掌握 read_excel() 函数的使用，是进行高效 Excel 数据处理的基础。

本文将对 Pandas 的 read_excel() 函数进行全方位、深度的解析，详细介绍其常用及重要参数，并通过丰富的示例代码展示其在不同场景下的应用。无论你是 Pandas 的初学者，还是希望更深入挖掘 read_excel() 潜力的进阶用户，本文都将为你提供一份详实的参考指南。

1. read_excel() 函数概览与基本用法

pandas.read_excel() 函数用于将 Excel 文件读取到 Pandas DataFrame 中。其最简单的用法只需要指定文件路径：

“`python
import pandas as pd

假设你的 Excel 文件名为 ‘sample_data.xlsx’ 位于当前目录下

file_path = ‘sample_data.xlsx’

基本读取

try:
df = pd.read_excel(file_path)
print(“成功读取 Excel 文件！”)
print(df.head()) # 打印前5行数据
except FileNotFoundError:
print(f”错误：文件未找到，请检查路径: {file_path}”)
except Exception as e:
print(f”读取文件时发生错误: {e}”)
“`

注意： read_excel 函数依赖于一些第三方库来解析 Excel 文件。对于较新的 .xlsx 格式文件（自 Pandas 0.23.0 起），默认使用 openpyxl 引擎；对于较旧的 .xls 格式文件，则通常需要 xlrd 引擎。对于 .odf, .ods, .odt 格式，则需要 odfpy 引擎。如果未安装相应的库，可能会报错 ImportError。你需要使用 pip install 命令进行安装：

bash
pip install openpyxl xlrd odfpy

安装完成后，即可顺利使用 read_excel 读取不同格式的 Excel 文件。

基本用法会尝试读取 Excel 文件中的第一个工作表（sheet），并假定第一行是表头（header）。然而，实际的 Excel 文件往往更加复杂，我们需要借助 read_excel() 的各种参数来精确控制读取行为。

2. read_excel() 主要参数详解

read_excel() 函数提供了众多参数，使得我们可以高度定制化数据读取过程。下面我们将详细介绍一些最常用和最重要的参数。

2.1 io (文件路径或缓冲区)

  • 参数类型: str, path object, file-like object
  • 作用: 指定要读取的 Excel 文件路径或文件对象。
  • 说明: 这是唯一必须的参数。可以是本地文件的路径字符串，也可以是 URL。此外，它还可以接受文件对象（如通过 open() 函数打开的文件）或 BytesIO/StringIO 对象，这在处理内存中的数据流时非常有用。

“`python

本地文件路径

df = pd.read_excel(‘data/my_spreadsheet.xlsx’)

使用文件对象 (需要手动关闭文件)

with open(‘another_data.xls’, ‘rb’) as f:
df = pd.read_excel(f)

从 URL 读取 (需要网络连接)

df = pd.read_excel(‘http://example.com/data/online_report.xlsx’)

“`

2.2 sheet_name (工作表名称或索引)

  • 参数类型: str, int, list, None
  • 作用: 指定要读取的工作表。
  • 说明:
    • str: 指定工作表的名称，例如 'Sheet1', '销售数据'.
    • int: 指定工作表的索引（从 0 开始），例如 0 表示第一个工作表，1 表示第二个工作表。
    • list: 指定一个包含工作表名称或索引的列表，例如 ['Sheet1', 'Sheet2'] 或 [0, 2]. 当指定为列表时，read_excel 会返回一个字典，其键是工作表名称，值是对应的 DataFrame。
    • None: 读取所有工作表。同样会返回一个字典，键是工作表名称，值是对应的 DataFrame。

“`python

读取名为 ‘产品列表’ 的工作表

df_products = pd.read_excel(file_path, sheet_name=’产品列表’)

读取第二个工作表 (索引为 1)

df_sheet2 = pd.read_excel(file_path, sheet_name=1)

读取 ‘订单明细’ 和 ‘客户信息’ 这两个工作表

sheets_dict = pd.read_excel(file_path, sheet_name=[‘订单明细’, ‘客户信息’])
df_orders = sheets_dict[‘订单明细’]
df_customers = sheets_dict[‘客户信息’]

读取所有工作表

all_sheets = pd.read_excel(file_path, sheet_name=None)

遍历所有工作表

for sheet_name, df in all_sheets.items():
print(f”正在处理工作表: {sheet_name}”)
print(df.head())
“`

如果未指定 sheet_name，默认值是 0，即读取第一个工作表。

2.3 header (表头行)

  • 参数类型: int, list of int, None
  • 作用: 指定哪些行作为列名（表头）。
  • 说明:
    • int: 指定作为表头的行索引（从 0 开始）。例如 header=0 表示第一行是表头（默认行为），header=1 表示第二行是表头。
    • list of int: 如果表头跨越多行（例如主标题和副标题），可以指定一个列表。Pandas 会将这些行的单元格内容组合起来作为多级索引（MultiIndex）的列名。
    • None: 表示文件没有表头。Pandas 会自动生成数字索引作为列名（0, 1, 2, …）。

“`python

默认行为，第一行是表头

df_default_header = pd.read_excel(file_path)

指定第二行作为表头 (跳过第一行)

df_header_row1 = pd.read_excel(file_path, header=1)

多级表头示例：假设前两行是表头

原始数据可能像这样：

| | Q1 | Q1 | Q2 | Q2 |

| Product | Sales | Profit | Sales | Profit |

| A | 100 | 20 | 120 | 25 |

df_multi_header = pd.read_excel(file_path, header=[0, 1])
print(df_multi_header.columns) # 输出类似 MultiIndex 结构

文件没有表头

df_no_header = pd.read_excel(file_path, header=None)
print(df_no_header.columns) # 输出类似 RangeIndex(start=0, stop=N, step=1)
“`

当 header=None 时，如果你想自己指定列名，可以使用 names 参数。

2.4 names (自定义列名)

  • 参数类型: array-like
  • 作用: 为读取的数据指定自定义列名。
  • 说明: 通常与 header=None 一起使用，当你需要读取一个没有表头或者想完全替换现有表头时非常有用。提供的列表长度必须与实际读取的列数匹配。

“`python

假设文件没有表头，我们想指定列名

custom_names = [‘产品ID’, ‘产品名称’, ‘单价’, ‘库存’]
df_custom_names = pd.read_excel(file_path, header=None, names=custom_names)
print(df_custom_names.head())
“`

如果同时指定了 header 和 names，read_excel 会先使用 header 读取数据，然后将结果的列名替换为 names 中指定的值。但通常的做法是：有表头时只用 header，没表头时用 header=None 和 names。

2.5 index_col (索引列)

  • 参数类型: int, str, list of int, list of str, None
  • 作用: 指定哪些列作为 DataFrame 的行索引。
  • 说明:
    • int 或 str: 指定某一列作为索引列（按列的索引或名称）。
    • list of int 或 list of str: 指定多列作为复合索引（MultiIndex）。
    • None: 不使用任何列作为索引，使用默认的整数索引 (0, 1, 2, …)。

“`python

使用第一列 (索引 0) 作为索引

df_indexed_by_col0 = pd.read_excel(file_path, index_col=0)
print(df_indexed_by_col0.index)

使用名为 ‘产品ID’ 的列作为索引

df_indexed_by_id = pd.read_excel(file_path, index_col=’产品ID’)

使用 ‘年份’ 和 ‘月份’ 两列作为复合索引

假设文件中有 ‘年份’ 和 ‘月份’ 两列

df_multi_indexed = pd.read_excel(file_path, index_col=[‘年份’, ‘月份’])
print(df_multi_indexed.index)

不使用任何列作为索引 (默认行为)

df_default_index = pd.read_excel(file_path, index_col=None)
print(df_default_index.index)
“`

2.6 usecols (选择列)

  • 参数类型: str, list-like of str, list-like of int, callable
  • 作用: 指定只读取哪些列。
  • 说明: 这对于处理包含大量不必要列的 Excel 文件非常有用，可以提高读取效率和减少内存占用。
    • str: 指定一个范围，例如 'A:C' 读取 A, B, C 三列；'A,C,E' 读取 A, C, E 三列。
    • list-like of int: 指定列的索引列表（从 0 开始），例如 [0, 2, 4] 读取第一、第三、第五列。
    • list-like of str: 指定列的名称列表，例如 ['产品名称', '库存'] 读取这两列（如果文件有表头）。
    • callable: 一个函数，该函数接受列名（如果在 header 中找到）或列索引作为输入，返回 True 表示包含该列，False 表示排除。

“`python

只读取前三列 (按索引 0, 1, 2)

df_subset_cols_index = pd.read_excel(file_path, usecols=[0, 1, 2])

只读取 ‘产品名称’, ‘单价’, ‘库存’ 三列 (按名称)

假设文件中有这些列名

df_subset_cols_names = pd.read_excel(file_path, usecols=[‘产品名称’, ‘单价’, ‘库存’])

只读取 Excel 列 A, C, E

df_subset_cols_excel_range = pd.read_excel(file_path, usecols=’A:C,E’) # 注意：需要xlrd引擎支持这种字符串格式

使用 callable 只读取数值列

def is_numeric_col(col):
# 这是一个简化的例子，实际可能需要更复杂的逻辑判断列是否包含数值
# 这里假设列名中包含 ‘数量’ 或 ‘金额’ 的是数值列
return ‘数量’ in str(col) or ‘金额’ in str(col)

df_numeric_only = pd.read_excel(file_path, usecols=is_numeric_col) # 这个用法在较新版本中可能更灵活

更好的 callable 示例：根据列名判断

def filter_cols_by_name(col_name):
return col_name in [‘产品名称’, ‘单价’]

注意：当使用 header 时，callable 接收列名。当 header=None 时，callable 接收列索引。

df_filtered_by_callable_name = pd.read_excel(file_path, usecols=filter_cols_by_name)

使用 callable 根据索引判断 (如果 header=None)

def filter_cols_by_index(col_index):
return col_index in [1, 3] # 读取第2列和第4列

df_filtered_by_callable_index = pd.read_excel(file_path, header=None, usecols=filter_cols_by_index)

“`

使用 usecols 能够显著提升处理大型 Excel 文件的效率，因为 Pandas 只会读取指定列的数据。

2.7 dtype (数据类型)

  • 参数类型: Type name or dict of column -> type, default None
  • 作用: 指定列的数据类型。
  • 说明: Pandas 会尝试自动推断数据类型，但这有时可能不准确，或者对于内存优化而言不是最优选择。通过 dtype 参数，你可以强制指定某些列的数据类型。
    • None: Pandas 自动推断。
    • Type name (如 str, int, float, bool, datetime): 将所有读取的列尝试转换为指定类型。
    • dict: 映射列名到所需数据类型的字典，例如 {'列A': 'int', '列B': 'float'}。未在字典中指定的列将继续由 Pandas 自动推断类型。

“`python

指定 ‘产品ID’ 为字符串类型，’库存’ 为整数类型

dtype_mapping = {‘产品ID’: str, ‘库存’: int, ‘单价’: float}
df_specified_dtype = pd.read_excel(file_path, dtype=dtype_mapping)
print(df_specified_dtype.dtypes)

将所有列都读取为字符串类型

df_all_str = pd.read_excel(file_path, dtype=str)
“`

显式指定 dtype 对于确保数据准确性（例如避免长数字被转换为科学计数法或丢失精度）和内存管理（使用更小的整数或浮点数类型）非常重要。

2.8 na_values (自定义缺失值)

  • 参数类型: scalar, str, list-like, dict, default None
  • 作用: 指定哪些值应该被识别为 NaN（Not a Number），即缺失值。
  • 说明: 除了 Excel 中的空单元格外，有时特定的字符串（如 ‘N/A’, ‘-‘, ‘未填写’）也表示缺失值。
    • None: 只将空单元格识别为 NaN。
    • scalar 或 str: 指定一个特定的值，所有与该值匹配的单元格都将被视为 NaN。
    • list-like: 指定一个值的列表，所有与列表中任一值匹配的单元格都将被视为 NaN。
    • dict: 为不同的列指定不同的缺失值表示，例如 {'列A': ['N/A', '-'], '列B': '空'}。

“`python

将 ‘N/A’ 和 ‘未知’ 识别为缺失值

df_custom_na = pd.read_excel(file_path, na_values=[‘N/A’, ‘未知’])

为 ‘库存’ 列将小于等于 0 的值也视为缺失值 (虽然这个例子更适合后处理，但在某些场景下可以直接处理)

或者更典型的，将 ‘NA’ 和 ‘-‘ 视为缺失

df_specific_col_na = pd.read_excel(file_path, na_values={‘库存’: [-1, ‘NA’], ‘单价’: [‘-‘]})
“`

2.9 keep_default_na (保留默认缺失值处理)

  • 参数类型: bool, default True
  • 作用: 是否在 na_values 的基础上保留 Pandas 默认识别的缺失值（如空字符串 ''）。
  • 说明: 如果设置为 False，则只有 na_values 中指定的值会被视为 NaN，空字符串等默认值将不会被转换为 NaN。通常保持默认值 True 即可。

“`python

通常不需要修改，保持默认 True

df = pd.read_excel(file_path, na_values=[‘N/A’]) # N/A 和默认空单元格都会被视为 NaN

如果设置为 False，则只有指定的 N/A 会被视为 NaN，空单元格不会

df = pd.read_excel(file_path, na_values=[‘N/A’], keep_default_na=False)

“`

2.10 parse_dates (解析日期)

  • 参数类型: bool, list-like, list-like of list-like, dict, default False
  • 作用: 将指定的列解析为日期时间类型。
  • 说明: Excel 中的日期有时以数字或字符串格式存储，直接读取可能不是 datetime 类型。
    • bool: True 尝试解析所有看起来像日期的列；False 不解析。
    • list-like of int 或 list-like of str: 指定要解析为日期时间类型的列索引或名称。
    • list-like of list-like: 用于将多个列组合成一个日期时间列。例如 [[0, 1, 2]] 表示将第 0, 1, 2 列合并解析为一个日期。
    • dict: 映射新列名到要组合的列索引/名称列表，例如 {'日期': [0, 1]} 将第 0, 1 列合并解析为名为 ‘日期’ 的新列。

“`python

自动解析所有可能的日期列

df_parse_all_dates = pd.read_excel(file_path, parse_dates=True)
print(df_parse_all_dates.dtypes)

指定 ‘订单日期’ 和 ‘发货日期’ 两列解析为日期

df_parse_specific_dates = pd.read_excel(file_path, parse_dates=[‘订单日期’, ‘发货日期’])

假设 ‘年份’, ‘月份’, ‘日’ 三列需要组合成日期

数据可能像这样：| 年份 | 月份 | 日 | … |

| 2023 | 10 | 26 | … |

| 2023 | 11 | 5 | … |

df_combined_date = pd.read_excel(file_path, parse_dates=[[‘年份’, ‘月份’, ‘日’]])
print(df_combined_date.head()) # 会生成一个名为 ‘年份_月份_日’ 的新日期列，原始列可能会被移除

组合并指定新列名

df_combined_date_named = pd.read_excel(file_path, parse_dates={‘完整日期’: [‘年份’, ‘月份’, ‘日’]})
print(df_combined_date_named.head())
“`

2.11 date_parser (自定义日期解析函数)

  • 参数类型: function, default None
  • 作用: 提供一个自定义函数来解析日期字符串。
  • 说明: 当默认的日期解析无法满足需求时（例如日期格式非常特殊），可以提供一个函数，它接收一个字符串作为输入，返回一个日期时间对象。这个参数通常与 parse_dates 参数一起使用，parse_dates 指定哪些列需要解析，date_parser 指定解析方法。

“`python
from dateutil import parser as dateutil_parser

假设日期格式非常规，例如 ‘YYYY年MM月DD日’

def custom_date_parser(date_str):
try:
# 使用更强大的 dateutil 库进行解析
return dateutil_parser.parse(date_str)
except:
return pd.NaT # 解析失败返回 Not a Time

df_custom_parsed_dates = pd.read_excel(file_path, parse_dates=[‘日期列’], date_parser=custom_date_parser)

“`

2.12 thousands (千位分隔符) 和 decimal (小数点分隔符)

  • 参数类型: str, default None
  • 作用: 处理包含千位分隔符和不同小数点分隔符的数字。
  • 说明: 在某些地区，数字格式可能使用逗号作为小数点，或使用点作为千位分隔符。
    • thousands: 指定千位分隔符字符，例如 , 或 ..
    • decimal: 指定小数点分隔符字符，例如 . 或 ,.

“`python

假设数字格式是 1.234,56 (点是千位分隔符，逗号是小数点)

df_localized_numbers = pd.read_excel(file_path, thousands=’.’, decimal=’,’)

假设数字格式是 ‘¥1,234.56’ 或 ‘$ 5000’ 等带有符号和千位分隔符

通常 Pandas 可以自动处理常见的符号和千位分隔符，但如果不行，可以使用 thousands

df_thousands = pd.read_excel(file_path, thousands=’,’)
“`

2.13 skiprows (跳过指定行)

  • 参数类型: list-like of int, int, callable, default None
  • 作用: 跳过文件开头的指定行。
  • 说明: 用于跳过文件开头的注释、标题或其他不相关信息。
    • int: 跳过文件最开始的 N 行。例如 skiprows=5 跳过前 5 行。
    • list-like of int: 跳过指定索引的行。例如 skiprows=[0, 2, 5] 跳过第一、第三、第六行（从 0 开始计数）。
    • callable: 一个函数，接收行索引作为输入，返回 True 表示跳过该行。

“`python

跳过文件开头的 3 行介绍性文本

df_skip_initial = pd.read_excel(file_path, skiprows=3)

跳过某些特定的行 (例如版权信息在第 10 行，免责声明在第 15 行)

df_skip_specific = pd.read_excel(file_path, skiprows=[9, 14]) # Excel 行号 10 和 15 对应索引 9 和 14
“`

2.14 nrows (读取行数)

  • 参数类型: int, default None
  • 作用: 指定要读取的最大行数（数据行数，不包括 header 行）。
  • 说明: 用于读取文件的前 N 行数据，对于快速预览大型文件或只处理部分数据非常有用。

“`python

只读取前 100 行数据 (不包括表头)

df_first_100_rows = pd.read_excel(file_path, nrows=100)
“`

结合 skiprows 和 nrows，可以读取文件中特定范围的数据块。例如，要读取从第 6 行开始的 100 行数据（即 Excel 中的第 7 行到第 106 行），你可以使用 skiprows=6 和 nrows=100。

2.15 skipfooter (跳过末尾行)

  • 参数类型: int, default 0
  • 作用: 跳过文件末尾的 N 行。
  • 说明: 用于跳过文件末尾的统计信息、脚注等。

“`python

跳过文件末尾的 5 行总结

df_skip_footer = pd.read_excel(file_path, skipfooter=5)
“`

2.16 engine (解析引擎)

  • 参数类型: str, default None
  • 作用: 指定用于读取 Excel 文件的引擎。
  • 说明: Pandas 支持多个引擎来解析不同格式的 Excel 文件。
    • openpyxl: 用于 .xlsx 文件（推荐且通常是默认）。
    • xlrd: 用于 .xls 文件（在 Pandas 1.2.0 后，读取 .xls 需要显式安装 xlrd < 2.0，因为新版 xlrd 移除了对 .xls 的支持，或者使用 openpyxl 读取 .xlsb 二进制文件）。
    • odfpy: 用于 .odf, .ods, .odt 文件。
    • pyxlsb: 用于 .xlsb 二进制文件。

通常 Pandas 会根据文件扩展名自动选择合适的引擎，但在某些情况下，你可能需要显式指定。

“`python

显式指定使用 openpyxl 引擎读取 .xlsx 文件

df = pd.read_excel(file_path, engine=’openpyxl’)

如果需要读取老版本的 .xls 文件 (并且安装了 xlrd < 2.0)

df = pd.read_excel(‘old_file.xls’, engine=’xlrd’)

“`

2.17 comment (注释行)

  • 参数类型: str, default None
  • 作用: 指定一个字符，任何以该字符开头的行将被视为注释并跳过。
  • 说明: 用于跳过文件中以特定字符（如 # 或 //）开头的注释行。

“`python

跳过以 ‘#’ 开头的行

df_no_comments = pd.read_excel(file_path, comment=’#’)
“`

2.18 converters (类型转换函数)

  • 参数类型: dict, default None
  • 作用: 允许为特定的列指定自定义的转换函数。
  • 说明: 这是一个非常强大的参数，可以在读取数据时对某些列的值进行复杂的转换。字典的键是列的索引或名称，值是一个函数，该函数接收原始单元格的值作为输入，返回转换后的值。

“`python

假设 ‘产品ID’ 列有时包含前导零，并且想保留为字符串

假设 ‘价格’ 列包含货币符号，需要去除并转换为浮点数

def clean_price(price_str):
if isinstance(price_str, str):
return float(price_str.replace(‘¥’, ”).replace(‘$’, ”).replace(‘,’, ”))
try:
return float(price_str)
except:
return pd.NA # 或其他表示缺失的值

df_converted = pd.read_excel(
file_path,
dtype={‘产品ID’: str}, # 确保产品ID首先被读取为字符串
converters={‘价格’: clean_price, 1: lambda x: str(x).zfill(5)} # 第二列 (索引1) 填充前导零到5位
)
print(df_converted.head())
“`

converters 在处理复杂、非标准格式的数据清洗时非常有用。

2.19 其他参数

read_excel() 还有一些其他参数，用于更精细地控制读取行为，例如：

  • squeeze (bool, default False, 在未来版本可能移除): 如果解析的数据只有一列，返回一个 Series 而不是 DataFrame。
  • mangle_dupe_cols (bool, default True): 如果列名重复，通过添加 .1, .2 等后缀使其唯一。设置为 False 则重复列名会保持原样，这可能导致问题。
  • true_values, false_values (list-like, default None): 指定哪些值应该被解析为 True 或 False 布尔值。
  • verbose (bool, default False): 显示更多关于解析过程的输出。

这些参数在大多数情况下使用默认值即可，但在特定场景下可能需要调整。

3. 复杂场景示例

结合上述参数，我们可以处理许多复杂的 Excel 读取场景。

3.1 处理带有多余信息的工作表

很多时候，Excel 文件的开头或结尾会有合并单元格的标题、注释、数据源信息等，或者中间有空行、统计行。

“`python

假设 Excel 文件结构如下：

合并单元格的大标题

数据生成日期：YYYY-MM-DD

(空行)

表头行 (例如在第4行，索引3)

Data Row 1

Data Row 2

…

Data Row N

(空行)

统计信息：…

作者：…

file_with_junk = ‘report_with_junk.xlsx’

跳过前3行，并指定第4行（索引3）为表头，同时跳过末尾的2行

df_cleaned = pd.read_excel(file_with_junk,
header=3,
skiprows=3, # skiprows 从文件开头算起，跳过3行
skipfooter=2) # skipfooter 从文件末尾算起，跳过2行

print(df_cleaned.head())
print(df_cleaned.tail())
``
注意header和skiprows的区别：skiprows是物理上跳过文件开头的行，而header是在剩余的行中指定哪一行作为列名。当同时使用时，skiprows先执行，然后从剩余的行中根据header参数确定表头。在上面的例子中，skiprows=3移除了前3行，剩余行的第一行（原第四行）的索引变为0，所以header=0或者header=3(如果skiprows参数描述的是原始行号) 都可以达到目的。为了清晰，建议header参数总是指代 *原始* Excel 文件中的行号（从0开始），而skiprows指代要跳过的 *原始* 行数。不过，Pandas 的官方文档说明skiprows会在读取之前将这些行从文件中移除，然后再应用header参数。所以，如果原始文件第4行是表头，跳过前3行后，原第四行变成读取数据的首行，其相对索引是0。因此，header=0是更准确的写法，当与skiprows一起使用时，header指的是 **在跳过skiprows` 行后的数据中的行索引**。

更安全的写法是先用 skiprows 移除前面的垃圾行，然后根据剩余数据中的表头位置设置 header。

“`python

假设原始文件第4行（索引3）是表头

跳过前3行，然后剩下的第一行就是表头

df_cleaned = pd.read_excel(file_with_junk,
skiprows=3, # 跳过前3行
header=0, # 此时跳过3行后的第一行就是表头，其相对索引是0
skipfooter=2)
“`

3.2 读取特定区域的数据

有时数据只存在于工作表的某个矩形区域，周围都是无关信息。

“`python

假设数据区域在 ‘Sheet1’ 工作表的 B3 到 F10 范围内

B列是产品ID，C列是名称，D列是数量，E列是价格，F列是备注

B3是产品ID的表头

A列和1、2行是无关信息

file_partial_data = ‘partial_data.xlsx’

我们可以结合 skiprows, usecols, header 来实现

数据从第3行（索引2）开始，表头在第3行

数据列是 B, C, D, E, F，对应索引 1, 2, 3, 4, 5

df_partial = pd.read_excel(file_partial_data,
sheet_name=’Sheet1′,
header=2, # 第3行（索引2）是表头
skiprows=2, # 跳过前2行
usecols=’B:F’) # 只读取 B 到 F 列

print(df_partial.head())
``
在这个例子中，skiprows=2移除了前两行。剩余数据的第1行（原始的第3行）就是表头，所以header=0（相对于剩余数据）。使用usecols=’B:F’` 选择了所需的列。

3.3 读取不规则文件 (例如，每个工作表有不同的结构)

如果一个 Excel 文件中的不同工作表有不同的结构（例如不同的表头行、不同的数据区域），你需要分步读取它们，对每个工作表应用不同的参数。

“`python
file_mixed_sheets = ‘mixed_structure.xlsx’

读取 Sheet1，假设结构规范，第一行表头

df_sheet1 = pd.read_excel(file_mixed_sheets, sheet_name=’Sheet1′)

读取 Sheet2，假设表头在第三行，需要跳过前两行

df_sheet2 = pd.read_excel(file_mixed_sheets, sheet_name=’Sheet2′,
header=2, skiprows=2)

读取 Sheet3，假设没有表头，只有两列数据，需要指定列名

df_sheet3 = pd.read_excel(file_mixed_sheets, sheet_name=’Sheet3′,
header=None, names=[‘类别’, ‘数值’])

然后可以将这些 DataFrame 合并或分别处理

combined_df = pd.concat([df_sheet1, df_sheet2, df_sheet3])

“`

3.4 处理包含特殊字符的列名或数据

如果列名包含特殊字符（如空格、连字符），在访问列时需要使用方括号语法 df['列 名']。如果数据中有特殊字符，read_excel 通常会正确读取为字符串，但如果需要进一步清洗，可以考虑使用 converters 或在读取后进行处理。

3.5 内存优化与大数据量处理

对于非常大的 Excel 文件，直接读取可能会占用大量内存甚至导致崩溃。可以使用以下策略进行优化：

  • 使用 usecols: 只读取需要的列。
  • 使用 dtype: 为列指定合适的数据类型，特别是使用更小的整数或浮点数类型（如 int16, float32）或分类类型（category）来存储重复性高的字符串数据。
  • 使用 nrows: 分块读取文件，或者只读取文件的前几行进行结构和数据类型检查。
  • 分批处理: 如果内存不足以一次加载整个文件，可以考虑编写代码，利用 skiprows 和 nrows 迭代地读取文件的一部分进行处理。但这对于 Excel 文件不像 CSV 文件那样方便实现。对于大数据量，通常建议将 Excel 转换为 CSV 或 Parquet 等格式处理。
  • 使用更高效的引擎: 对于 .xlsb 文件，使用 pyxlsb 可能比其他引擎更快。

4. ExcelFile 对象

除了直接使用 pd.read_excel() 外，Pandas 还提供了 pd.ExcelFile 对象。这个对象代表一个 Excel 文件，可以用来更有效地读取文件中的多个工作表，因为它只解析文件一次。

“`python

创建一个 ExcelFile 对象

excel_file = pd.ExcelFile(file_path)

查看文件中的所有工作表名称

print(excel_file.sheet_names)

从 ExcelFile 对象中读取特定工作表

df_sheet1_from_obj = excel_file.parse(‘Sheet1’) # parse 方法的使用与 read_excel 类似
df_sheet2_from_obj = excel_file.parse(1)

关闭文件 (如果使用 with 语句则无需手动关闭)

excel_file.close()

使用 with 语句确保文件被关闭

with pd.ExcelFile(file_path) as excel_file:
print(excel_file.sheet_names)
df_sheet1_from_with = excel_file.parse(‘Sheet1’)
df_sheet2_from_with = excel_file.parse(1, header=2, skiprows=2) # parse 同样支持 read_excel 的大部分参数
“`

当你需要从同一个 Excel 文件中读取多个工作表时，使用 ExcelFile 通常比多次调用 pd.read_excel() 更加高效。

5. 常见问题与调试技巧

  • ImportError: 确保安装了正确的引擎库 (openpyxl, xlrd, odfpy, pyxlsb)。
  • FileNotFoundError: 检查文件路径是否正确，包括文件扩展名。
  • 数据类型错误:
    • 数字被读取为字符串：检查单元格格式是否是文本，尝试显式指定 dtype 为数值类型。
    • 日期未被正确解析：检查单元格格式是否是日期格式，使用 parse_dates 参数，如果格式特殊则结合 date_parser。
    • 混合类型列：如果一列中既有数字又有文本，Pandas 默认可能将其读取为 object 类型（通常是字符串）。可以尝试使用 dtype 指定为字符串，或在读取后进行清洗。
  • 表头或索引错误: 仔细检查 header, index_col, skiprows 参数。记住 header 和 index_col 的索引是相对于 读取到的数据 的起始行（在应用 skiprows 后）。
  • 缺失值未被识别: 检查 na_values 参数是否包含了所有需要识别为缺失的值，以及 keep_default_na 是否是默认的 True。
  • 内存不足: 对于大文件，考虑使用上面提到的内存优化策略。
  • 查看数据: 在读取后，使用 .head(), .tail(), .info(), .dtypes, .isnull().sum() 等方法检查 DataFrame 的结构、数据类型、非空值数量和缺失值数量，帮助诊断问题。

6. 总结

Pandas 的 read_excel() 函数是一个功能丰富且强大的工具，能够满足绝大多数从 Excel 文件读取数据的需求。通过熟练掌握 io, sheet_name, header, index_col, usecols, dtype, na_values, parse_dates, skiprows, nrows, skipfooter, engine, converters 等核心参数，你可以：

  • 精确控制读取哪个文件或数据流。
  • 选择性地读取特定工作表或所有工作表。
  • 正确处理各种复杂的表头结构，甚至无表头文件。
  • 指定将哪些列用作索引。
  • 只读取文件中的部分列或部分行，节省时间和内存。
  • 在读取时就指定列的数据类型，避免后续转换并优化内存。
  • 自定义缺失值的识别方式。
  • 自动或手动解析日期时间列。
  • 处理包含特殊分隔符的数字。
  • 利用自定义函数在读取时进行数据清洗和转换。
  • 应对包含额外信息或不规则数据区域的工作表。

虽然 Excel 文件在结构上不如 CSV 等纯文本格式规范，但凭借 read_excel() 提供的强大灵活性，Pandas 使我们能够高效、准确地将 Excel 数据加载到 DataFrame 中，为后续的数据分析工作打下坚实的基础。结合 ExcelFile 对象，还可以进一步优化读取多个工作表的效率。

通过本文的详细解析和示例，希望你能更深入地理解和应用 read_excel() 函数，自如地应对各种 Excel 数据导入挑战。实践是最好的学习方式，不妨找几个结构不同的 Excel 文件，动手尝试使用不同的参数进行读取和探索吧！

发表评论

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

滚动至顶部