Python Pandas 处理 Excel：`read_excel` 入门 – wiki基地

作者： /

Python Pandas 处理 Excel：read_excel 入门指南

1. 引言：数据世界的桥梁——Excel与Python Pandas

在当今数据驱动的世界中，Excel作为最普及的数据存储和交换工具之一，几乎无处不在。从小型企业的数据记录到大型公司的财务报表，Excel文件承载着海量关键信息。然而，当数据量变得庞大、数据处理需求变得复杂、或者需要自动化重复性任务时，Excel本身的功能便显得捉襟见肘。手动复制粘贴、公式拖拽不仅效率低下，而且极易出错。

这时，Python，特别是其强大的数据分析库Pandas，便如一位数据炼金师般，将Excel中的原始数据转化为洞察力。Pandas提供了一套高效、灵活且易于使用的工具集，用于处理各种结构化数据，其中，处理Excel文件的能力是其最引人注目的特性之一。

pd.read_excel 函数是Pandas与Excel文件进行交互的基石。它允许我们以极大的灵活性将Excel数据加载到Pandas DataFrame中，从而打开了数据清洗、转换、分析、可视化以及自动化的大门。本文将深入探讨pd.read_excel函数的各项功能、常用参数、实际应用场景以及最佳实践，旨在为初学者提供一份详尽的入门指南，让你能够自信地驾驭Pandas处理Excel数据的能力。

2. 环境准备与基础概念

在深入学习read_excel之前，我们需要确保Python环境已准备就绪，并对Pandas的基本概念有所了解。

2.1. 安装必要的库

Pandas本身不直接支持所有Excel文件的读写，它依赖于一些第三方库来处理不同的Excel文件格式。
* openpyxl: 用于读写 .xlsx 文件（Excel 2010及更高版本）。
* xlrd: 用于读取 .xls 文件（旧版Excel，通常只读）。
* xlwt: 用于写入 .xls 文件（Pandas默认使用openpyxl写入.xlsx，所以xlwt不常直接用于read_excel的依赖，但了解一下无妨）。
* odfpy: 用于读取 .ods 文件（OpenDocument Spreadsheet）。
* pyxlsb: 用于读取 .xlsb 文件（Excel Binary Workbook）。

你可以使用pip安装这些库：

bash
pip install pandas openpyxl xlrd

通常，pandas、openpyxl和xlrd是处理.xlsx和.xls文件的基本组合。

2.2. Pandas DataFrame 简介

Pandas DataFrame 是一个二维的、表格型的数据结构，它带有行标签（索引）和列标签。你可以把它想象成一个加强版的Excel表格，每一列可以有不同的数据类型（整数、浮点数、字符串、日期等），并且拥有强大的数据操作方法。read_excel函数的目标就是将Excel表格数据转换成这种DataFrame结构。

“`python
import pandas as pd

创建一个示例Excel文件，用于后续演示

假设我们有一个名为 ‘sample_data.xlsx’ 的文件，包含以下内容：

Sheet1:

+—+———+——-+———-+

| ID| Name | Score | Date |

+—+———+——-+———-+

| 1 | Alice | 95 | 2023-01-15|

| 2 | Bob | 88 | 2023-01-16|

| 3 | Charlie | 92 | 2023-01-17|

| 4 | David | NaN | 2023-01-18|

+—+———+——-+———-+

Sheet2:

+—+———+——-+———-+

| Product| Price | Stock |

+—+———+——-+———-+

| A | 10.5 | 100 |

| B | 20.0 | 50 |

| C | 5.2 | NaN |

+—+———+——-+———-+

“`

3. pd.read_excel 的核心功能与基本用法

pd.read_excel函数的基本语法非常直观：

python
pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, skiprows=None, nrows=None, na_values=None, dtype=None, converters=None, parse_dates=False, date_parser=None, thousands=None, comment=None, engine=None, **kwargs)

乍一看参数很多，但别担心，我们大部分时候只需要用到其中的几个。

3.1. 最简单的用法：读取第一个工作表

最基本的使用方式是只提供文件路径。read_excel会默认读取Excel文件中的第一个工作表，并将第一行作为列标题。

“`python
import pandas as pd

假设 ‘sample_data.xlsx’ 存在

file_path = ‘sample_data.xlsx’
df_default = pd.read_excel(file_path)

print(“默认读取结果：”)
print(df_default.head())
print(“\n数据信息：”)
df_default.info()
“`

解释：
* df_default.head(): 显示DataFrame的前5行，快速预览数据。
* df_default.info(): 提供DataFrame的简洁摘要，包括索引类型、列的数量、每列的非空值数量以及每列的数据类型（dtype）。这是了解导入数据质量的关键步骤。

4. read_excel 的核心参数详解与实践

read_excel函数的强大之处在于其丰富的参数，它们允许我们精细地控制数据的导入过程，以适应各种复杂的Excel文件结构。

4.1. io: 文件路径或文件对象

  • 作用: 指定要读取的Excel文件的路径（字符串）或类文件对象（例如，BytesIO 或 StringIO）。
  • 默认值: 无，必须提供。

  • 示例:
    “`python
    df = pd.read_excel(‘data/my_excel_file.xlsx’) # 本地文件路径

    从URL读取Excel文件 (需要requests库)

    import requests

    url = ‘https://example.com/data.xlsx’

    r = requests.get(url)

    df = pd.read_excel(io=r.content, engine=’openpyxl’) # 从二进制内容读取

    “`

4.2. sheet_name: 选择工作表

  • 作用: 指定要读取的工作表。
  • 默认值: 0 (读取第一个工作表)。

  • 选项:

    • str: 工作表名称（如 'Sheet1'）。
    • int: 工作表索引（从0开始，如 0 代表第一个工作表）。
    • list of str/int: 读取多个指定的工作表。返回一个字典，键为工作表名称，值为对应的DataFrame。
    • None: 读取所有工作表。同样返回一个字典。

  • 示例:

    “`python

    读取名为 ‘Sheet2’ 的工作表

    df_sheet2 = pd.read_excel(file_path, sheet_name=’Sheet2′)
    print(“\n读取 Sheet2：”)
    print(df_sheet2.head())

    读取索引为 0 的工作表 (与默认行为相同)

    df_sheet0 = pd.read_excel(file_path, sheet_name=0)
    print(“\n读取索引为 0 的工作表：”)
    print(df_sheet0.head())

    读取多个工作表（例如：Sheet1和Sheet2）

    all_sheets = pd.read_excel(file_path, sheet_name=[‘Sheet1’, ‘Sheet2’])
    print(“\n读取多个工作表（返回字典）：”)
    for sheet_name, df in all_sheets.items():
    print(f”— 工作表: {sheet_name} —“)
    print(df.head())

    读取所有工作表

    all_sheets_dict = pd.read_excel(file_path, sheet_name=None)
    print(“\n读取所有工作表（返回字典）：”)
    for sheet_name, df in all_sheets_dict.items():
    print(f”— 工作表: {sheet_name} —“)
    print(df.head())
    “`

4.3. header: 指定列标题行

  • 作用: 指定哪一行作为DataFrame的列标题。
  • 默认值: 0 (第一行，索引从0开始)。

  • 选项:

    • int: 指定行号（索引）。
    • list of int: 如果列标题跨越多行，可以指定一个行号列表。Pandas会创建一个多级索引。
    • None: 不使用任何行作为列标题。Pandas会自动生成从0到N-1的整数作为列标题。

  • 示例:

    “`python

    假设 Excel 文件中的数据从第三行开始有标题，即 header=2

    df_header_row_3 = pd.read_excel(file_path, header=2)

    print(df_header_row_3.head())

    如果没有标题行 (或需要自定义标题)

    df_no_header = pd.read_excel(file_path, header=None, sheet_name=’Sheet1′)
    print(“\n无标题行读取：”)
    print(df_no_header.head())
    “`

4.4. names: 自定义列标题

  • 作用: 为DataFrame提供新的列标题。通常与 header=None 配合使用，或在原始文件没有合适的标题时使用。
  • 默认值: None。

  • 选项: list of str (字符串列表，长度应与实际列数匹配)。

  • 示例:

    python
    custom_names = ['学生ID', '学生姓名', '考试分数', '考试日期']
    df_custom_names = pd.read_excel(file_path, sheet_name='Sheet1', header=None, names=custom_names)
    print("\n自定义列标题：")
    print(df_custom_names.head())

4.5. index_col: 指定索引列

  • 作用: 指定哪一列或哪几列作为DataFrame的行索引。
  • 默认值: None (Pandas会自动生成一个从0开始的整数索引)。

  • 选项:

    • int: 列的索引（从0开始）。
    • str: 列的名称。
    • list of int/str: 指定多列作为多级索引。

  • 示例:

    “`python

    使用 ‘ID’ 列作为索引

    df_indexed = pd.read_excel(file_path, sheet_name=’Sheet1′, index_col=’ID’)
    print(“\n使用 ‘ID’ 列作为索引：”)
    print(df_indexed.head())

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

    df_indexed_by_0 = pd.read_excel(file_path, sheet_name=’Sheet1′, index_col=0)
    print(“\n使用第一列作为索引：”)
    print(df_indexed_by_0.head())
    “`

4.6. usecols: 选择要读取的列

  • 作用: 仅读取Excel文件中的指定列，这对于处理包含大量无关列的大文件时非常有用，可以节省内存和提高效率。
  • 默认值: None (读取所有列)。

  • 选项:

    • str: 单个列名。
    • list of str: 多个列名。
    • list of int: 多个列索引（从0开始）。
    • str (Excel列名): 如 'A:C' 读取A到C列，'A,C,E' 读取A、C、E列。
    • callable: 一个函数，接受列名字符串并返回布尔值。

  • 示例:

    “`python

    只读取 ‘Name’ 和 ‘Score’ 列

    df_selected_cols = pd.read_excel(file_path, sheet_name=’Sheet1′, usecols=[‘Name’, ‘Score’])
    print(“\n选择特定列（通过名称）：”)
    print(df_selected_cols.head())

    只读取第一列和第三列 (索引为0和2)

    df_selected_indices = pd.read_excel(file_path, sheet_name=’Sheet1′, usecols=[0, 2])
    print(“\n选择特定列（通过索引）：”)
    print(df_selected_indices.head())

    假设Excel中有更多列，我们可以通过Excel列名范围选择

    df_excel_cols = pd.read_excel(file_path, sheet_name=’Sheet1′, usecols=’A:C’)

    print(df_excel_cols.head())

    “`

4.7. skiprows 和 nrows: 跳过行与读取行数

  • 作用:
    • skiprows: 跳过文件开头指定数量的行，或跳过特定的行。
    • nrows: 读取指定数量的行，通常用于预览或处理大型文件的部分数据。
  • 默认值: None。

  • 选项:

    • skiprows: int (跳过开头N行) 或 list of int (跳过指定行号)。
    • nrows: int (读取N行)。

  • 示例:

    “`python

    跳过文件开头的1行，然后读取

    df_skip_rows = pd.read_excel(file_path, sheet_name=’Sheet1′, skiprows=1)
    print(“\n跳过第一行后读取：”)
    print(df_skip_rows.head())

    只读取前3行数据 (不包括标题行)

    注意：nrows是从数据行开始计数，如果header=0，则计数从第一行数据开始

    df_n_rows = pd.read_excel(file_path, sheet_name=’Sheet1′, nrows=3)
    print(“\n只读取前3行数据：”)
    print(df_n_rows)
    “`

4.8. na_values: 定义缺失值

  • 作用: 指定哪些字符串值应该被识别为缺失值（NaN）。
  • 默认值: None (Pandas会默认识别空白单元格、#N/A, NaN, NULL等)。

  • 选项:

    • str: 单个字符串。
    • list of str: 多个字符串。
    • dict: 针对特定列定义不同的缺失值表示。

  • 示例:

    “`python

    假设 Excel 中除了 NaN，还有 ‘N/A’ 和 ‘-‘ 表示缺失值

    我们需要在 Sheet1 的 David 行的 Score 列中手动设置一个 ‘N/A’ 或 ‘-‘ 来演示

    模拟 Excel 文件中 ‘Score’ 列有 ‘N/A’ 和 ‘-‘ 的情况

    例如：第四行 Score 为 ‘N/A’，第五行 Score 为 ‘-‘

    df_na_values = pd.read_excel(file_path, sheet_name=’Sheet1′, na_values=[‘N/A’, ‘-‘])
    print(“\n识别自定义缺失值：”)
    print(df_na_values)
    print(df_na_values.info())
    “`

4.9. dtype: 指定列的数据类型

  • 作用: 强制某些列在导入时具有特定的数据类型。这对于确保数据质量和节省内存非常重要，特别是当Pandas错误地推断了数据类型时。
  • 默认值: None (Pandas会自动推断数据类型)。

  • 选项: dict (键为列名或列索引，值为Pandas/NumPy数据类型，如 int64, float64, object, str, bool)。

  • 示例:

    “`python

    强制 ‘ID’ 为字符串，’Score’ 为浮点数

    df_dtypes = pd.read_excel(file_path, sheet_name=’Sheet1′, dtype={‘ID’: str, ‘Score’: float})
    print(“\n指定列数据类型：”)
    print(df_dtypes.info())
    ``
    **注意：** 如果指定了    int类型但列中包含NaN（缺失值），Pandas会将其转换为float类型，因为int类型不支持NaN。要处理这种情况，可以使用Int64Dtype()` (Pandas 1.0+ 的 nullable integer) 或在读取后再进行处理。

4.10. converters: 自定义列值转换

  • 作用: 允许你为特定的列指定一个函数，在读取数据时对该列的每个单元格值进行自定义转换。
  • 默认值: None。

  • 选项: dict (键为列名或列索引，值为一个函数，该函数接受一个单元格值作为输入并返回转换后的值)。

  • 示例:

    “`python

    假设 ‘Score’ 列可能包含 ‘Pass’/’Fail’，需要转换为 1/0

    为了演示，我们修改 Excel 文件，将某些分数值改为字符串 ‘Pass’ 或 ‘Fail’

    def score_converter(value):
    try:
    return float(value)
    except ValueError:
    if value == ‘Pass’:
    return 100.0
    elif value == ‘Fail’:
    return 0.0
    return pd.NA # 或者 float(‘nan’)

    实际演示中，你可能需要手动修改 sample_data.xlsx 的 Score 列

    例如：将 ID 为 4 的 Score 改为 ‘Pass’

    df_converted = pd.read_excel(file_path, sheet_name=’Sheet1′, converters={‘Score’: score_converter})

    print(“\n自定义转换器：”)

    print(df_converted)

    print(df_converted.info())

    “`

4.11. parse_dates: 解析日期列

  • 作用: 尝试将指定的列解析为日期时间对象。
  • 默认值: False。

  • 选项:

    • bool: True 将尝试解析所有看起来像日期的列。
    • list of int/str: 指定要解析的列。
    • list of list: 将多列组合起来解析为单个日期时间列。

  • 示例:

    “`python

    自动解析 ‘Date’ 列为日期时间对象

    df_parsed_dates = pd.read_excel(file_path, sheet_name=’Sheet1′, parse_dates=[‘Date’])
    print(“\n解析日期列：”)
    print(df_parsed_dates.info())
    print(df_parsed_dates[‘Date’].iloc[0]) # 查看解析后的日期对象类型
    “`

4.12. engine: 指定读取引擎

  • 作用: 强制Pandas使用特定的引擎来读取Excel文件。
  • 默认值: Pandas会根据文件扩展名自动选择（例如，.xlsx 使用 openpyxl，.xls 使用 xlrd）。

  • 选项: 'openpyxl', 'xlrd', 'odf', 'pyxlsb'。

  • 使用场景:

    • 当需要读取.xls文件但xlrd版本不支持时，可能需要降级xlrd或转换文件格式。
    • 在特定情况下，某个引擎可能表现更好或支持特定功能（如pyxlsb用于.xlsb文件）。
    • 当从URL或二进制数据流读取时，可能需要显式指定引擎。

  • 示例:

    “`python

    显式指定使用 openpyxl 引擎

    df_engine_openpyxl = pd.read_excel(file_path, engine=’openpyxl’)
    print(“\n指定引擎为 openpyxl：”)
    print(df_engine_openpyxl.head())
    “`

4.13. thousands, decimal: 处理数字格式

  • 作用: 当数字包含千位分隔符（如 , 或 .）或小数分隔符（如 ,）时，帮助Pandas正确解析数字。
  • 默认值: None。
  • 示例:
    python
    # 假设 Excel 中有类似 '1,234.56' 或 '1.234,56' 的数字
    # df_thousands = pd.read_excel(file_path, thousands=',') # 针对 '1,234.56'
    # df_decimal = pd.read_excel(file_path, decimal=',') # 针对 '1.234,56' (欧洲格式)

4.14. comment: 忽略注释行

  • 作用: 指定一个字符串，如果行的开头是这个字符串，则该行会被当作注释跳过。
  • 默认值: None。

5. 常见挑战与解决方案

5.1. Excel文件中的“脏数据”

Excel文件往往不是完美的，可能包含各种不规范的数据：
* 不规范的标题行: 标题行不在第一行，或者有多行标题。
* 解决方案: 使用 header 参数指定正确的标题行索引。如果有多行标题，可以使用 list 类型的值来创建多级索引，或先导入数据再手动重命名。
* 数据上方/下方有额外信息: 文件开头有说明文字、图表，或者结尾有统计汇总。
* 解决方案: 使用 skiprows 跳过开头的非数据行，使用 nrows 限制读取的数据行数，或者使用 usecols 仅读取实际数据区域的列。
* 合并单元格: Pandas读取合并单元格时，通常会将第一个单元格的值填充到所有合并的单元格中，而其余合并单元格在原始Excel中是空白的。Pandas会将这些空白单元格识别为 NaN。
* 解决方案: 这需要根据具体需求进行后处理。如果需要填充，可以使用 df.ffill() (前向填充) 或 df.bfill() (后向填充)。
* 空白行/列: 存在完全空白的行或列。
* 解决方案: pd.read_excel 通常会忽略完全空白的列。对于空白行，可以通过 df.dropna(how='all') 清除。

5.2. 数据类型问题

  • 数字被识别为字符串: Excel中某些数字列可能被误存为文本格式。
    • 解决方案: 使用 dtype 参数强制指定为数值类型 (int, float)，或使用 converters 进行自定义转换。
  • 日期被识别为字符串: 日期格式不统一或被Excel识别为文本。
    • 解决方案: 使用 parse_dates 参数尝试解析日期列。如果日期格式非常复杂，可能需要配合 date_parser 或在导入后再使用 pd.to_datetime() 进行转换。
  • 混合数据类型: 同一列中既有数字又有字符串。Pandas默认会将该列识别为 object (字符串) 类型。
    • 解决方案: 仔细检查数据源。如果混合类型是无意的，需要清洗数据。如果确实需要处理，可以使用 converters 或在导入后进行分类型处理。

5.3. 大型文件性能问题

当Excel文件非常大（数百万行或数十MB甚至GB）时，完整加载整个文件可能耗时且占用大量内存。
* 解决方案:
* usecols: 只加载所需的列。
* nrows: 只加载前N行进行预览或分批处理。
* dtype: 明确指定数据类型可以减少内存占用，例如使用更小的整数类型 int16 而非 int64。
* 分块读取: 可以结合 nrows 和 skiprows 或者使用 chunksize 参数 (虽然 read_excel 没有 chunksize，但可以自行构建循环实现)。

6. 最佳实践

  1. 始终检查导入后的数据： 使用 df.head(), df.info(), df.describe() 快速了解数据结构、数据类型和基本统计信息。这是发现导入问题的第一步。
  2. 明确指定参数： 即使默认值能满足需求，但在生产代码中，明确指定 sheet_name, header, index_col 等参数可以提高代码的可读性、健壮性和可维护性，防止因文件结构微小变化而导致代码失效。
  3. 处理缺失值： 在导入后，根据业务需求使用 df.dropna() (删除缺失值) 或 df.fillna() (填充缺失值)。
  4. 数据类型优化： 根据数据范围，尽可能使用更精确、内存效率更高的数据类型，例如 int16、float32、category 等。
  5. 模块化代码： 将读取Excel的逻辑封装成函数，特别是当你有多个Excel文件或需要重复读取类似结构的文件时。
  6. 错误处理： 使用 try-except 块来处理文件不存在、权限不足或文件损坏等潜在错误。
  7. 备份原始数据： 在对数据进行任何修改之前，始终保持原始Excel文件的副本。

7. 结语

pd.read_excel函数是Python Pandas处理Excel数据的强大起点。通过熟练掌握其各种参数，你将能够灵活、高效地从各种复杂结构的Excel文件中提取所需数据，将其转换为Pandas DataFrame这一分析利器。这不仅能够极大地提高你的数据处理效率，减少手动操作的错误，还能为后续的数据清洗、分析、建模和可视化工作奠定坚实的基础。

数据分析的旅程，从数据导入开始。理解并精通read_excel，将是你迈向Python数据科学之路的重要一步。现在，拿起你的Excel文件，用Pandas的力量，去探索数据背后的故事吧！

发表评论

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

滚动至顶部