Python处理超大型EXCEL文件（66万行×17列）笔记（上）

目录

前言

1. pandas及openpyxl库的安装

2. pd.read_excel()函数

3. pd.ExcelFile()类

4. load_workbook()函数

总结

前言

python提供pandas库和openpyxl库对excel文件进行处理，其中pandas库的pd.read_excel()和pd.ExcelFile()，openpyxl库的load_workbook()均可对excel数据读入。然而，在读取大型excel文件时，函数的使用及设置不当可能会导致运行较慢及内存错误，本文以处理超大型excel文件（66万行×17列）为例子，说明上述三个函数的在处理大型excel的使用方法。

1. pandas及openpyxl库的安装

在调用读入函数前，需要确保python解释器中已经安装相应的库，以下是pandas和openpyxl库的几种安装方法。

方法一：WIN+R打开cmd，首先输入pycharm中使用的python解释器的绝对路径，然后输入-m pip install pandas或openpyxl，等待其安装完毕即可。

eg: 绝对路径\python.exe -m pip install pandas 或 绝对路径\python.exe -m pip install openpyxl

方法二：打开pycharm，点击“设置”，点击“python解释器”，点击软件包上方的“+”号，搜索“pandas”或“openpyxl”库，点击右下角“安装软件包”，完成安装后会弹出提示。

方法三：打开pycharm，点击左下角的“终端”，输入pip install pandas或openpyxl。

方法四：打开Anaconda Prompt终端，输入conda activate <环境名称>，进入环境后输入pip install pandas或openpyxl，等待其安装完毕即可。

2. pd.read_excel()函数

pd.read_excel()函数读入excel表格，返回一个DataFrame对象，其核心参数有以下：

注意：pd.read_excel()函数没有read_only这个参数！！

代码示例

• sheet_name（指定工作表）​

import pandas as pd
# 读取第一个工作表（索引0）
df1 = pd.read_excel('data.xlsx', sheet_name=0)

# 读取名为 "Sales" 的工作表
df2 = pd.read_excel('data.xlsx', sheet_name='Sales')

# 读取多个工作表（返回字典）
sheets_dict = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet3'])
print(sheets_dict['Sheet1'].head())  # 访问 'Sheet1' 的数据

• usecols（筛选列）​

# 按列字母范围选择（A到C列）
df_range = pd.read_excel('data.xlsx', usecols='A:C')

# 按列索引选择（第1、3列）
df_index = pd.read_excel('data.xlsx', usecols=[0, 2])

# 按列名选择（列名需严格匹配）
df_names = pd.read_excel('data.xlsx', usecols=['Name', 'Age'])

• skiprows和 nrows（控制行范围）​

# 跳过前2行（如标题下方的空行）
df_skip = pd.read_excel('data.xlsx', skiprows=2)

# 跳过特定行（如第1、3、5行）
df_skip_list = pd.read_excel('data.xlsx', skiprows=[0, 2, 4])

# 仅读取前100行（大文件抽样）
df_nrows = pd.read_excel('data.xlsx', nrows=100)

• header和 index_col（设置表头与索引）

# 指定第2行为列名（原表头在第二行）
df_header = pd.read_excel('data.xlsx', header=1)

# 无表头文件，自定义列名
df_no_header = pd.read_excel('data.xlsx', header=None, names=['ID', 'Value'])

# 将 "ID" 列设为行索引
df_index = pd.read_excel('data.xlsx', index_col='ID')

• dtype（强制数据类型）​

# 将 "Phone" 列转为字符串（避免丢失前导0）
df_str = pd.read_excel('data.xlsx', dtype={'Phone': str})

# 指定多列数据类型
df_multi_dtype = pd.read_excel('data.xlsx', dtype={'ID': int, 'Price': float})

• na_values（自定义缺失值标识）​

# 将 "N/A" 和 "NULL" 视为 NaN
df_na = pd.read_excel('data.xlsx', na_values=['N/A', 'NULL'])

# 不同列配置不同缺失值标识
df_na_dict = pd.read_excel('data.xlsx', na_values={'Age': [-1], 'Score': ['缺考']})

3. pd.ExcelFile()类

pd.ExcelFile()类读入excel表格，返回一个ExcelFile对象（需调用 .parse()获取数据），其核心参数有以下：

代码示例

• path_or_buffer（路径与文件）​

import pandas as pd

# 本地文件路径
excel_file = pd.ExcelFile('data.xlsx')

• engine（解析引擎）​

# 强制使用 openpyxl 解析 .xlsx 文件
excel_file = pd.ExcelFile('data.xlsx', engine='openpyxl')

由于pd.ExcelFile()返回的是一个ExcelFile对象，不能直接读出数据，因此需要通过调用 .parse()获取数据，返回的是一个DataFrame对象，代码示例如下：

excel_file = pd.ExcelFile("data.xlsx")

# 读取Sheet1表格中以第1行为表头的数据，返回一个DataFrame对象
df1 = excel_file.parse(sheet_name="Sheet1", header=0)

# 仅读取Sheet1表格中第1和第3列的数据，返回一个DataFrame对象
df2 = excel_file.parse(sheet_name="Sheet1", usecols=[0, 2])

事实上，在处理大型excel文件时，常使用pd.ExcelFile()读取文件，因为返回的ExcelFile回对象并不像DataFrame对象一样占用大量内存，pd.ExcelFile()常常配合iter_rows()​​​函数来逐行读取数据，减少内存花销。

4. load_workbook()函数

load_workbook()读入excel表格，其核心参数有以下：

代码示例

• filename（路径与文件）

from openpyxl import load_workbook

# 本地文件
wb = load_workbook(filename="data.xlsx")

# Path对象
wb = load_workbook(filename=Path("data.xlsx"))

• read_only（数据读取模式）

 # 只读模式，无法修改工作表内容，适用于处理大型文件
wb = load_workbook("formula.xlsx", read_only=True)

 # 可读写模式，可修改工作表内容，将整个工作表加载至内存，False值为默认值
wb = load_workbook("formula.xlsx", read_only=False)

在处理大型excel文件时，load_workbook()也常用于大型excel文件的读取，同样地，load_workbook()也常配合iter_rows()​​​函数来逐行读取数据，减少内存花销。

总结

本文介绍了pandas库和openpyxl库中的三个excel文件处理函数的使用方法，在Python处理超大型EXCEL文件（66万行×17列）笔记（下）篇中将结合具体的例子说明如何通过这些函数处理大型excel文件。

谢谢阅读，如有不足之处请指出！