Cách xử lý file Excel theo chunk (từng phần) trong Python với Pandas

Giới thiệu

Excel vẫn là công cụ quyết định quan trọng trong nhiều doanh nghiệp. Mặc dù không phải fan của MS Office, tôi sẽ chia sẻ thủ thuật xử lý file Excel lớn hiệu quả.

Cơ bản: Đọc/ghi Excel với Pandas

Cài đặt: pip install openpyxl xlwt

Tạo file Excel mẫu

import xlsxwriter

def write_data_to_files(inp_data, inp_file_name):
    workbook = xlsxwriter.Workbook(inp_file_name)
    worksheet = workbook.add_worksheet()
    row = 0
    col = 0
    for player, titles in inp_data:
        worksheet.write(row, col, player)
        worksheet.write(row, col + 1, titles)
        row += 1
    workbook.close()

# Dữ liệu tennis
file_data = (['player', 'titles'], ['Federer', 20], ['Nadal', 20], ['Djokovic', 17], ['Murray', 3])
write_data_to_files(file_data, "temporary_file.xlsx")

Đọc và thao tác với Pandas

import pandas as pd

def excel_functions_with_pandas(inp_file_name):
    data = pd.read_excel(inp_file_name)
    print(data.head())
    print(data.info())
    
    # Ghi sheet mới
    new_players_info = pd.DataFrame({
        "players": ["new Roger Federer", "new Rafael Nadal", "new Novak Djokovic", "new Andy Murray"],
        "titles": [20, 20, 17, 3]
    })
    with pd.ExcelWriter(inp_file_name, mode='a', if_sheet_exists='replace') as writer:
        new_players_info.to_excel(writer, sheet_name="Sheet2")

Xử lý file Excel lớn theo chunk (Pandas không hỗ trợ chunksize cho Excel mặc định)

Khác với CSV, pd.read_excel() không có tham số chunksize. Giải pháp: đọc thủ công theo lô với nrows và skiprows.

def global_excel_to_db_chunks(file_name, nrows):
    """ Đọc file Excel theo chunk kích thước nrows. """
    chunks = []
    i_chunk = 0
    skiprows = 1  # Bỏ qua header (dòng 0)
    
    # Đọc header riêng
    df_header = pd.read_excel(file_name, nrows=1)
    
    while True:
        df_chunk = pd.read_excel(
            file_name,
            nrows=nrows,
            skiprows=skiprows,
            header=None
        )
        skiprows += nrows
        
        if not df_chunk.shape[0]:  # Hết dữ liệu
            break
        else:
            print(f" ** Đang đọc chunk {i_chunk} với {df_chunk.shape[0]} dòng")
            chunks.append(df_chunk)
            i_chunk += 1
    
    # Gộp các chunk
    df_chunks = pd.concat(chunks, ignore_index=True)
    
    # Đổi tên cột theo header
    columns = {i: col for i, col in enumerate(df_header.columns.tolist())}
    df_chunks.rename(columns=columns, inplace=True)
    
    # Gộp header + data
    df = pd.concat([df_header, df_chunks], ignore_index=True)
    print(' *** Đọc hoàn tất theo chunk...')
    return df

# Test với file 9994 dòng, chunk 1000 dòng
if __name__ == '__main__':
    file_name = 'Sample-sales-data-excel.xls'
    stats = pd.read_excel(file_name)
    print(f" ** Tổng số dòng: {len(stats.index)}")
    global_excel_to_db_chunks(file_name, 1000)

Kết quả:

** Tổng số dòng: 9994
** Đang đọc chunk 0 với 1000 dòng
** Đang đọc chunk 1 với 1000 dòng
...
** Đang đọc chunk 9 với 994 dòng
*** Đọc hoàn tất theo chunk...