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}')

代码解释：

1. 在项目中有一个包含160万条数据的Dataframe: bills_all
2. 设置数据块的大小，保存在变量 chunksize 中，实例中设置大小为800000，需要根据实际情况调整
3. 计算分块的数量保存在变量 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，即向上取整。
4. 将bills_all表按照num_chunks指定的块数分割成多个子列表，保存在变量 chunks 中。函数使用了numpy库的array_split方法来实现这个功能。每个子列表的长度可能不完全相等，但尽可能均匀分配。
5. 使用pd.ExcelWriter上下文管理器，准备将数据写入Excel文件。with语句确保文件在操作完成后正确关闭。
   指定文件路径为D:\Code\work\data\calculate.xlsx，需要根据实际情况进行替换
   使用xlsxwriter引擎进行写操作。xlsxwriter适合一次性生成大型 Excel 文件，处理大规模数据速度更快，但是不支持追加写入。
6. 遍历所有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}')

代码解释：

1. 与第一种方法相同，先设置分块大小，然后计算分块数量，将数据框分块保存到chunks中
2. 初始化分块数量和sheet数量为0，用于计数
3. 遍历chunks中的数据块：
   如果是第一个块且是新文件，则直接写入。
   如果不是第一块，则在现有的sheet中追加写入。
   如果是sheet中的数据块数量>4，则数据块计数器置零，sheet数+1，新创建一个sheet写入数据。
4. 使用 openpyxl 库的load_workbook 函数加载 Excel 文件，返回一个表示整个 Excel 工作簿的对象，赋值给变量 book，用于后面判断目标工作表sheet_name是否已经存在。
5. 使用pd.ExcelWriter创建一个写入对象writer，设置为追加模式（mode='a'），并且当目标工作表已存在时覆盖原有内容（if_sheet_exists='overlay'）。
6. 检查目标工作表sheet_name是否存在于工作簿book中。
7. 如果工作表存在，获取当前工作表的最大行数start_row，然后将DataFrame chunk追加到该工作表的末尾，不包含列名（header=False）。
8. 如果工作表不存在，则直接将DataFrame chunk写入新的工作表sheet_name中，并且不包含索引列（index=False）。

总结

1. 两种方式都可以实现将大规模数据写入Excel表，第一种方式运行更快。

2. 第一种方式也可以用openpyxl引擎，但是处理效率低于xlsxwriter，关于这两个引擎这里总结一下：
   

3. 以上两种解决方式第一种效率更高、处理速度更快，比较推荐。