Python处理大规模数据集
问题描述
最近的项目中需要处理接近160万条数据的Excel，并将处理结果写入新的Excel表。Excel一张表的数量上限是100万，直接写表会报错，而且受到电脑性能限制，大规模数据一次性写入会出问题。根据实践尝试，本篇文章总结了两种方法解决上述问题。
解决方法
分块写入两个sheet
这种方法的思路是将整体数据量分成两部分，分别写入同一个Excel表的两个sheet中，以此避免Excel上限问题。
代码实现：
import pandas as pd
import numpy as np
# 假设 bills_all 是你的 DataFrame
bills_all = pd.DataFrame(...) # 替换为你的 DataFrame 数据
chunksize = 800000 # 分块大小
num_chunks = -(- len(bills_all) // chunksize) # 计算分块数量
chunks = np.array_split(bills_all, num_chunks) # 将bills_all表按照num_chunks指定的块数分割成多个子列表
try:
with pd.ExcelWriter(r'D:\Code\work\data\calculate.xlsx', engine='xlsxwriter') as writer:
for i, chunk in enumerate(chunks):
chunk.to_excel(writer, sheet_name=f'Sheet{i}', index=False)
print(f'Wrote Sheet{i} to Excel')
except Exception as e:
print(f'Error writing to Excel: {e}')
代码解释：
- 在项目中有一个包含160万条数据的Dataframe: bills_all
- 设置数据块的大小，保存在变量
chunksize中，实例中设置大小为800000，需要根据实际情况调整 - 计算分块的数量保存在变量
num_chunks中：
（1）len(bills_all)获取bills_all中元素的数量
（2） 使用-(- x // chunksize)实现向上取整除法：
1.第一个负号将 x 变为负数。
2.// 进行整除运算。
3.第二个负号恢复负数为原值。
这里先将x变为负数的原因是：
整除操作：x // chunksize会向下取整。
变负数：将 x 变为负数 -x，再进行整除，结果依然向下取整，实际上相当于向上取整。
恢复正数：最后再将结果变为正数。
例如：
25 // 10 结果为 2。
-25 // 10 结果为 -3（向下取整）。
-(-25 // 10) 结果为 3。
因此，-(-25 // 10) 确保结果为 3，即向上取整。 - 将bills_all表按照
num_chunks指定的块数分割成多个子列表，保存在变量chunks中。函数使用了numpy库的array_split方法来实现这个功能。每个子列表的长度可能不完全相等，但尽可能均匀分配。 - 使用
pd.ExcelWriter上下文管理器，准备将数据写入Excel文件。with语句确保文件在操作完成后正确关闭。
指定文件路径为D:\Code\work\data\calculate.xlsx，需要根据实际情况进行替换
使用xlsxwriter引擎进行写操作。xlsxwriter适合一次性生成大型 Excel 文件，处理大规模数据速度更快，但是不支持追加写入。 - 遍历所有
chunks中的数据列表，每一个列表写入一个sheet中
运行效果：
分块读取数据写到Excel
这种方法的思路是分块读取数据，读取一块数据写入到Excel中之后再读取下一块数据写入，当sheet表快写满时，创建新的sheet继续写入数据块，以此类推。
代码实现：
import pandas as pd
import numpy as np
# 假设 bills_all 是你的 DataFrame
bills_all = pd.DataFrame(...) # 替换为你的 DataFrame 数据
chunksize = 200000
num_chunks = -(-len(bills_all) // chunksize) # 计算需要多少块
chunks = np.array_split(bills_all, num_chunks) # 分割数据为块
output_path = r'D:\Code\work\data\calculate.xlsx' # 替换为你的文件路径
# 初始化计数器
chunk_counter = 0
sheet_counter = 0
try:
for i, chunk in enumerate(chunks):
if chunk_counter >= 4: # 每个 sheet 包含4个块
chunk_counter = 0
sheet_counter += 1
sheet_name = f'Sheet{sheet_counter}'
if chunk_counter == 0 and sheet_counter == 0:
# 如果是第一个块且是新文件，则直接写入
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
chunk.to_excel(writer, sheet_name=sheet_name, index=False)
else:
# 追加数据到现有的文件中
book = load_workbook(output_path)
with pd.ExcelWriter(output_path, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
writer.book = book
if sheet_name in writer.book.sheetnames:
start_row = writer.book[sheet_name].max_row
chunk.to_excel(writer, sheet_name=sheet_name, index=False, startrow=start_row, header=False)
else:
chunk.to_excel(writer, sheet_name=sheet_name, index=False)
chunk_counter += 1
print(f'Wrote chunk {i} to {sheet_name}')
except Exception as e:
print(f'Error writing to Excel: {e}')
代码解释：
- 与第一种方法相同，先设置分块大小，然后计算分块数量，将数据框分块保存到chunks中
- 初始化分块数量和sheet数量为0，用于计数
- 遍历
chunks中的数据块：
如果是第一个块且是新文件，则直接写入。
如果不是第一块，则在现有的sheet中追加写入。
如果是sheet中的数据块数量>4，则数据块计数器置零，sheet数+1，新创建一个sheet写入数据。 - 使用
openpyxl库的load_workbook函数加载 Excel 文件，返回一个表示整个 Excel 工作簿的对象，赋值给变量 book，用于后面判断目标工作表sheet_name是否已经存在。 - 使用
pd.ExcelWriter创建一个写入对象writer，设置为追加模式（mode='a'），并且当目标工作表已存在时覆盖原有内容（if_sheet_exists='overlay'）。 - 检查目标工作表sheet_name是否存在于工作簿book中。
- 如果工作表存在，获取当前工作表的最大行数start_row，然后将DataFrame chunk追加到该工作表的末尾，不包含列名（
header=False）。 - 如果工作表不存在，则直接将DataFrame chunk写入新的工作表sheet_name中，并且不包含索引列（
index=False）。
总结
-
两种方式都可以实现将大规模数据写入Excel表，第一种方式运行更快。
-
第一种方式也可以用
openpyxl引擎，但是处理效率低于xlsxwriter，关于这两个引擎这里总结一下：
-
以上两种解决方式第一种效率更高、处理速度更快，比较推荐。