Python用pandas读取Excel教程

想要高效读取 Excel 文件，告别繁琐操作？本文为你详细解读 Python 中 Pandas 库的 `read_excel()` 函数。作为数据分析的利器，Pandas 结合 `openpyxl` 库，能轻松应对各种 Excel 文件，无论是指定 sheet 工作表、控制数据类型，还是处理缺失值，都能灵活实现。本文将深入讲解 `sheet_name`、`dtype`、`na_values` 等关键参数的用法，并提供实用技巧，教你如何跳过行、选择列，精准读取所需数据。更进一步，针对大型 Excel 文件，本文还将分享性能优化策略，包括精确选取所需列、合理定义数据类型，甚至将 Excel 转换为 CSV 文件，助你提升数据处理效率，让你的 Python 脚本飞起来！

使用pandas.read_excel()函数可高效读取Excel文件，需先安装pandas和openpyxl库。通过指定文件路径、sheet_name参数读取特定工作表或所有工作表，结合dtype和na_values参数控制数据类型与缺失值识别，利用skiprows、header、usecols和nrows等参数灵活选择数据范围。处理大型文件时，优化策略包括精确选取所需列、合理定义数据类型以降低内存占用，并可考虑将Excel转为CSV提升读取效率。

Python 中使用 Pandas 读取 Excel 文件，最核心且直接的方法就是利用 pandas.read_excel() 函数。它能轻松将 .xls 或 .xlsx 格式的数据加载到 DataFrame 中，为后续的数据分析和处理打下基础。这个函数功能强大，能应对各种复杂的 Excel 文件结构，从最简单的单工作表读取到多工作表、指定行/列乃至数据类型控制，都能搞定。

解决方案

要开始，首先确保你已经安装了 Pandas 库，并且根据 Excel 文件格式，可能还需要安装 openpyxl (用于 .xlsx 文件) 或 xlrd (用于 .xls 文件)。通常，openpyxl 是推荐的，因为它支持较新的 Excel 格式。

安装方法： pip install pandas openpyxl

读取 Excel 文件的基本步骤非常简单：

1. 导入 Pandas 库： 这是所有 Pandas 操作的第一步。
2. 调用 pd.read_excel()： 将你的 Excel 文件路径作为第一个参数传给它。

import pandas as pd

# 假设你的Excel文件名为 'sales_data.xlsx' 并且在当前工作目录下
try:
    df = pd.read_excel('sales_data.xlsx')
    print("文件读取成功！前5行数据如下：")
    print(df.head())
except FileNotFoundError:
    print("错误：文件未找到。请检查文件路径和文件名是否正确。")
except Exception as e:
    print(f"读取文件时发生错误: {e}")

# 如果是旧版 .xls 文件，可能需要 xlrd 引擎
# df_old_excel = pd.read_excel('legacy_data.xls', engine='xlrd')
# print("\n旧版Excel文件读取成功！")
# print(df_old_excel.head())

这个 pd.read_excel() 函数背后其实做了很多事情，它会尝试猜测文件的结构，比如哪一行是表头、哪些是数据。但实际工作中，Excel 文件往往不那么“标准”，所以我们需要掌握一些参数来精细控制读取过程。

Python Pandas 如何读取 Excel 文件中的特定工作表？

Excel 文件经常包含多个工作表（Sheet），而我们通常只关心其中一个或几个。pd.read_excel() 提供了一个 sheet_name 参数来处理这种情况，这在我看来是读取 Excel 时最常用也最重要的参数之一。

sheet_name 可以接受多种类型的值：

• 字符串： 直接指定工作表的名称。
• 整数： 指定工作表的索引（从 0 开始计数）。
• None： 读取所有工作表，结果会是一个字典，键是工作表名称，值是对应的 DataFrame。
• 列表： 读取指定名称或索引的工作表列表，结果也是一个字典。

import pandas as pd

file_path = 'multi_sheet_data.xlsx'

# 假设 multi_sheet_data.xlsx 包含 'Sheet1', 'SalesData', 'Config' 三个工作表

# 1. 读取名为 'SalesData' 的工作表
try:
    df_sales = pd.read_excel(file_path, sheet_name='SalesData')
    print("\n读取 'SalesData' 工作表：")
    print(df_sales.head())
except Exception as e:
    print(f"读取 'SalesData' 时发生错误: {e}")

# 2. 读取第一个工作表（索引为 0）
try:
    df_first_sheet = pd.read_excel(file_path, sheet_name=0)
    print("\n读取第一个工作表（索引 0）：")
    print(df_first_sheet.head())
except Exception as e:
    print(f"读取第一个工作表时发生错误: {e}")

# 3. 读取所有工作表
try:
    all_sheets = pd.read_excel(file_path, sheet_name=None)
    print("\n读取所有工作表，结果是一个字典：")
    for sheet_name, df in all_sheets.items():
        print(f"--- 工作表: {sheet_name} ---")
        print(df.head(2)) # 只打印前两行，避免输出过多
except Exception as e:
    print(f"读取所有工作表时发生错误: {e}")

# 4. 读取指定名称的多个工作表
try:
    selected_sheets = pd.read_excel(file_path, sheet_name=['SalesData', 'Config'])
    print("\n读取 'SalesData' 和 'Config' 两个工作表：")
    for sheet_name, df in selected_sheets.items():
        print(f"--- 工作表: {sheet_name} ---")
        print(df.head(2))
except Exception as e:
    print(f"读取指定多个工作表时发生错误: {e}")

在我看来，sheet_name=None 这种方式在探索一个不熟悉的 Excel 文件结构时特别有用，能让你一眼看到所有工作表的数据概貌。

Pandas 读取 Excel 时，如何有效处理数据类型和缺失值？

数据类型和缺失值是数据清洗过程中绕不开的两个大问题，在从 Excel 读取数据时，如果不加以控制，很容易出现意想不到的错误。Pandas 提供了 dtype 和 na_values 参数来帮助我们预先处理这些问题。

数据类型 (dtype)： Excel 单元格的格式并不总是能准确地映射到 Python 的数据类型。比如，一个全是数字的列，如果其中某个单元格被手动格式化为文本，Pandas 可能会将整个列都读作字符串（object 类型），这在后续的数值计算中会造成麻烦。使用 dtype 参数，我们可以强制指定某些列的数据类型。

dtype 接受一个字典，键是列名或列索引，值是 NumPy 或 Python 的数据类型。

缺失值 (na_values)： Excel 中表示缺失值的方式多种多样，除了空白单元格，还可能有 "N/A", "-", "None" 等自定义标记。pd.read_excel() 默认会将空白单元格读取为 NaN (Not a Number)，但对于自定义的缺失值标记，它就无能为力了。na_values 参数可以接受一个列表或字典，告诉 Pandas 哪些值应该被识别为 NaN。

import pandas as pd
import numpy as np

file_path = 'data_with_issues.xlsx'

# 假设 data_with_issues.xlsx 包含：
# - 'OrderID' 列，可能被读成浮点数（如1001.0），我们希望是整数。
# - 'Price' 列，我们希望是浮点数。
# - 'Status' 列，某些单元格可能是 'N/A' 或 '-'，我们希望识别为缺失值。
# - 'Notes' 列，可能有一些自定义的缺失值标记，如 '无'。

# 定义数据类型和缺失值
dtype_spec = {
    'OrderID': int,       # 确保 OrderID 是整数
    'Price': float,       # 确保 Price 是浮点数
    'Quantity': 'int64',  # 也可以用字符串形式
    'ProductCode': str    # 确保 ProductCode 是字符串
}

na_values_spec = [
    'N/A',                # 将 'N/A' 识别为 NaN
    '-',                  # 将 '-' 识别为 NaN
    '无',                 # 将 '无' 识别为 NaN
    'None'                # 有些 Excel 文件中 'None' 也是缺失值
]

try:
    df_clean = pd.read_excel(file_path,
                             dtype=dtype_spec,
                             na_values=na_values_spec)

    print("\n处理数据类型和缺失值后的 DataFrame：")
    print(df_clean.head())
    print("\n各列数据类型：")
    print(df_clean.dtypes)
    print("\n缺失值统计：")
    print(df_clean.isnull().sum())

except Exception as e:
    print(f"读取并处理数据时发生错误: {e}")

# 一个常见的场景是，Excel 中的整数列如果包含空白，Pandas 会自动将其转换为浮点数（如 1.0, NaN）。
# 如果我们希望保持整数类型并允许缺失值，可以使用 Pandas 的 nullable integer 类型：
# 'OrderID': pd.Int64Dtype()
# 这种类型在处理含有缺失值的整数列时非常有用。
# df_nullable_int = pd.read_excel(file_path, dtype={'OrderID': pd.Int64Dtype()})
# print("\n使用 nullable integer 后的 OrderID 类型：")
# print(df_nullable_int['OrderID'].dtype)

dtype 和 na_values 组合使用，能大大减少后续数据清洗的工作量，尤其是在处理那些数据源不那么规范的 Excel 文件时，简直是神器。

在 Pandas 中，如何灵活控制 Excel 文件的读取范围，例如跳过行或指定列？

实际的 Excel 文件往往不只是纯粹的数据表格，可能包含标题、批注、汇总行等非数据内容，或者我们只对其中一部分列感兴趣。pd.read_excel() 提供了 skiprows, nrows, usecols, header 等参数，让我们能够精准地选择需要读取的数据区域。

跳过行 (skiprows)： 这个参数非常实用，可以跳过文件开头的几行，或者跳过文件中的特定行。

• 整数： 跳过文件开头的指定行数。
• 列表： 跳过文件中指定索引的行（从 0 开始计数）。

指定表头 (header)： 默认情况下，Pandas 认为第一行（索引 0）是表头。但如果你的表头在其他行，比如第 3 行，就需要通过 header 参数来指定。header 接受一个整数，表示作为表头的行索引。

读取指定行数 (nrows)： 如果只想读取文件的前 N 行数据，可以使用 nrows 参数。这在处理大型文件时，用于快速预览数据结构非常方便。

选择特定列 (usecols)： 我们不一定需要 Excel 文件中的所有列。usecols 参数可以让我们只读取感兴趣的列，这不仅能减少内存占用，还能加快读取速度。

• 列表（字符串）： 指定要读取的列名列表。
• 列表（整数）： 指定要读取的列索引列表。
• 字符串： 指定一个列范围，如 'A:C' 或 'A,C,E'。
• 可调用对象： 传入一个函数，该函数接收列名并返回 True 或 False 来决定是否读取该列。

import pandas as pd

file_path = 'complex_layout_data.xlsx'

# 假设 complex_layout_data.xlsx 文件结构如下：
# - 前两行是报告标题和生成日期
# - 第 3 行是实际的列名（表头）
# - 数据从第 4 行开始
# - 我们只对 'Product', 'Quantity', 'Price' 这三列感兴趣
# - 文件可能包含很多行，但我们只想读取前 100 行数据

# 1. 跳过前两行，将第三行作为表头
try:
    df_skip_header = pd.read_excel(file_path,
                                   skiprows=2,       # 跳过前2行（索引0和1）
                                   header=0)         # 跳过之后的第一行（原文件的第3行）作为表头
    print("\n跳过前两行，并将第三行作为表头读取：")
    print(df_skip_header.head())
except Exception as e:
    print(f"读取时发生错误 (跳过行和表头): {e}")


# 2. 只读取 'Product', 'Quantity', 'Price' 三列，并限制行数
try:
    df_partial = pd.read_excel(file_path,
                               skiprows=2,
                               header=0,
                               usecols=['Product', 'Quantity', 'Price'], # 指定列名
                               nrows=10)                                 # 只读取数据的前10行
    print("\n只读取指定列和前10行数据：")
    print(df_partial)
except Exception as e:
    print(f"读取时发生错误 (指定列和行数): {e}")


# 3. 使用列索引来指定列（假设 Product 是第2列，Quantity是第3列，Price是第4列，从0开始）
try:
    df_col_index = pd.read_excel(file_path,
                                 skiprows=2,
                                 header=0,
                                 usecols=[1, 2, 3], # 读取索引为1, 2, 3的列
                                 nrows=5)
    print("\n使用列索引读取指定列和前5行数据：")
    print(df_col_index)
except Exception as e:
    print(f"读取时发生错误 (使用列索引): {e}")


# 4. 如果 Excel 文件中存在一些不规则的行，比如中间插入了批注行，可以使用 skiprows 列表
# 假设我们要跳过第0, 1, 5行（原文件中的行号）
# df_irregular_skip = pd.read_excel(file_path, skiprows=[0, 1, 5], header=0)
# print("\n跳过不规则行后的数据：")
# print(df_irregular_skip.head())

这些参数的组合使用，让 pd.read_excel() 变得异常灵活。在我自己的项目里，很多时候 Excel 文件都是由不同部门同事提供的，格式五花八门，这些参数就是我快速把数据整理成可用 DataFrame 的利器。

处理大型 Excel 文件时，Pandas 读取性能优化有哪些实用技巧？

处理小型 Excel 文件时，性能通常不是问题。但当文件大小达到几十甚至上百兆，包含几十万上百万行数据时，读取速度和内存占用就会变得很关键。虽然 pd.read_excel() 不像 pd.read_csv() 那样直接支持 chunksize 进行分块读取（它会将整个文件加载到内存），但我们仍然有一些策略可以优化其性能。

1. 精确指定 usecols： 这是最直接也最有效的优化手段之一。如果你的 Excel 文件有几十上百列，但你只需要其中的几列，那么明确指定 usecols 可以显著减少内存占用和读取时间。Pandas 只需要解析和加载你指定的列数据，而不是整个表格。

2. 优化数据类型 (dtype)： 如前所述，dtype 不仅能确保数据正确性，也能大幅优化内存使用。默认情况下，Pandas 可能会为数字列分配 float64，为字符串列分配 object。如果知道某些整数列不会有缺失值且数值范围不大，可以指定为 int32 或 int16。字符串列如果只有少数几种固定值，可以考虑转换为 category 类型。

import pandas as pd
import time
import os

# 假设 large_data.xlsx 是一个非常大的文件
large_file_path = 'large_data.xlsx' # 请替换为你的大文件路径

# 创建一个模拟的大型Excel文件（如果不存在）
if not os.path.exists(large_file_path):
    print(f"正在创建模拟大型文件 '{large_file_path}'，请稍候...")
    data = {
        'col_int': range(1_000_000),
        'col_float': [float(i) * 1.23 for i in range(1_000_000)],
        'col_str_short': [f'Item_{i%100}' for i in range(1_000_000)],
        'col_str_long': [f'This is a very long string for item {i}' for i in range(1_000_000)],
        'col_bool': [i % 2 == 0 for i in range(1_000_000)],
        'col_date': pd.to_datetime([f'2023-01-01'] * 1_000_000)
    }
    large_df_gen = pd.DataFrame(data)
    large_df_gen.to_excel(large_file_path, index=False, engine='openpyxl')
    print("模拟文件创建完成。")
else:
    print(f"文件 '{large_file_path}' 已存在，跳过创建。")


# 场景1：不进行任何优化，读取所有列
print("\n--- 场景1：不优化读取所有列 ---")
start_time = time.time()
try:
    df_unoptimized = pd.read_excel(large_file_path)
    end_time = time.time()
    print(f"读取耗时: {end_time - start_time:.2f} 秒")
    print(f"DataFrame 内存占用: {df_unoptimized.memory_usage(deep=True).sum() / (1024**2):.2f} MB")
except Exception as e:
    print(f"读取失败: {e}")


# 场景2：指定 usecols 和 dtype 进行优化
print("\n--- 场景2：使用 usecols 和 dtype 优化 ---")
optimized_dtype = {
    'col_int': 'int32',
    'col_float': 'float32',
    'col_str_short': 'category', # 如果字符串重复度高，category 很有用
    'col_bool': 'bool'
}
selected_cols = ['col_int', 'col_float', 'col_str_short', 'col_bool']

start_time = time.time()
try:
    df_optimized = pd.read_excel(large_file_path,
                                 usecols=selected_cols,
                                 dtype=optimized_dtype)
    end_time = time.time()
    print(f"读取耗时: {end_time - start_time:.2f} 秒")
    print(f"DataFrame 内存占用: {df_optimized.memory_usage(deep=True).sum() / (1024**2):.2f} MB")
except Exception as e:
    print(f"读取失败: {e}")

从上面的模拟测试中，你应该能直观感受到 usecols 和 dtype 对性能和内存的巨大影响。

3. 使用 nrows 进行抽样或分批处理： 虽然 read_excel 没有内置的 chunksize 迭代器，但如果你只需要文件的一部分数据进行分析，或者想分批处理（例如，每次读取几万行然后处理），可以使用 nrows 和 skiprows 组合来实现。这需要你手动循环，每次读取下一批数据。

4. 优先使用 openpyxl 引擎： 对于 .xlsx 格式的文件，openpyxl 是默认且推荐的引擎。它通常比 xlrd 更快，并且支持更多的 Excel 特性。你也可以显式指定 engine='openpyxl'。

5. 考虑将 Excel 转换为 CSV： 如果 Excel 文件真的非常大，并且你的数据结构相对简单（没有复杂的公式、宏等），一个非常有效的策略是先将 Excel 文件转换为 CSV 文件。Pandas 读取 CSV 文件的效率远高于 Excel，因为它不需要解析复杂的二进制或 XML 结构。你可以使用 Excel 软件手动转换，或者用 openpyxl 库在 Python 中实现转换。

在我看来，处理大型数据时，数据类型和列选择的精细控制是避免内存溢出和提高效率的关键。很多时候，一个小小的 dtype 调整，就能让你的脚本从“卡死”变成“秒出结果”。

今天关于《Python用pandas读取Excel教程》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！