如何用Pandas快速合并100个Excel文件（缩短至5-10分钟内）

Your current pandas.concat approach is slow because it loads all 100 DataFrames into memory simultaneously, and pandas' default Excel I/O tools aren't optimized for bulk operations. Below are targeted optimizations to get your runtime down to 5-10 minutes:

Why Your Original Code Is Slow #

• Storing 100 full DataFrames in a list consumes massive memory, causing slow concatenation and potential disk swapping.
• pandas.read_excel with default settings loads entire workbooks into memory, which is inefficient for large files.
• Writing the full merged DataFrame in one go is slower than incremental writing.

Solution 1: Read in Read-Only Mode + Incremental Write (Pandas + Openpyxl) #

Use pandas' read-only mode to minimize memory usage when reading files, then append each file's data directly to the output Excel without storing everything in memory.

import os
import pandas as pd
from openpyxl import load_workbook

org_dir = 'D:/soft/project/excel' 
out_filepath = 'D:/soft/project/excel/concat_file.xlsx'

# Initialize output with the first file's data and headers
file_list = [f for f in os.listdir(org_dir) if f.endswith('.xlsx')]
first_file = file_list[0]
first_df = pd.read_excel(
    os.path.join(org_dir, first_file),
    dtype=str,
    engine='openpyxl',
    read_only=True
)
first_df.to_excel(out_filepath, index=False)

# Append remaining files
for file in file_list[1:]:
    file_path = os.path.join(org_dir, file)
    df = pd.read_excel(
        file_path,
        dtype=str,
        engine='openpyxl',
        read_only=True
    )
    
    # Load existing workbook to append
    book = load_workbook(out_filepath)
    writer = pd.ExcelWriter(
        out_filepath,
        engine='openpyxl',
        mode='a',
        if_sheet_exists='overlay'
    )
    writer.book = book
    
    # Write data starting after the last row
    startrow = book.active.max_row
    df.to_excel(writer, index=False, header=False, startrow=startrow)
    
    writer.close()

Solution 2: Use Pyexcelerate for Faster Writing #

Pyexcelerate is a lightweight library optimized for writing large Excel files. Pair it with pandas' fast read-only mode for maximum speed.

import os
import pandas as pd
from pyexcelerate import Workbook

org_dir = 'D:/soft/project/excel' 
out_filepath = 'D:/soft/project/excel/concat_file.xlsx'

wb = Workbook()
ws = wb.new_sheet("Sheet1")

file_list = [f for f in os.listdir(org_dir) if f.endswith('.xlsx')]
first_file = file_list[0]

# Write header from first file
first_df = pd.read_excel(
    os.path.join(org_dir, first_file),
    dtype=str,
    engine='openpyxl',
    read_only=True
)
ws.range("A1").value = first_df.columns.tolist()

# Write all data rows
current_row = 2
for file in file_list:
    df = pd.read_excel(
        os.path.join(org_dir, file),
        dtype=str,
        engine='openpyxl',
        read_only=True
    )
    
    # Skip header for subsequent files
    if file != first_file:
        df = df.iloc[1:]
    
    # Write rows in bulk
    ws.range(f"A{current_row}").value = df.values.tolist()
    current_row += len(df)

wb.save(out_filepath)

Solution 3: CSV Intermediate Step (Fastest for Large Datasets) #

CSV I/O is drastically faster than Excel. Convert each Excel file to CSV, merge the CSVs, then convert back to Excel. This is often the quickest approach for large data volumes.

import os
import pandas as pd
import csv
import shutil

org_dir = 'D:/soft/project/excel' 
temp_csv_dir = 'D:/soft/project/temp_csv'
out_filepath = 'D:/soft/project/excel/concat_file.xlsx'
merged_csv_path = 'D:/soft/project/merged_temp.csv'

# Create temp directory for CSVs
os.makedirs(temp_csv_dir, exist_ok=True)

# Convert all Excel files to CSV
for file in os.listdir(org_dir):
    if file.endswith('.xlsx'):
        excel_path = os.path.join(org_dir, file)
        csv_path = os.path.join(temp_csv_dir, f"{os.path.splitext(file)[0]}.csv")
        
        df = pd.read_excel(
            excel_path,
            dtype=str,
            engine='openpyxl',
            read_only=True
        )
        df.to_csv(csv_path, index=False, encoding='utf-8')

# Merge CSVs with pure CSV module (faster than pandas)
with open(merged_csv_path, 'w', newline='', encoding='utf-8') as outfile:
    writer = csv.writer(outfile)
    first_file = True
    
    for csv_file in os.listdir(temp_csv_dir):
        csv_full_path = os.path.join(temp_csv_dir, csv_file)
        
        with open(csv_full_path, 'r', encoding='utf-8') as infile:
            reader = csv.reader(infile)
            
            if first_file:
                writer.writerows(reader)
                first_file = False
            else:
                next(reader)  # Skip header row
                writer.writerows(reader)

# Convert merged CSV back to Excel
merged_df = pd.read_csv(merged_csv_path, dtype=str)
merged_df.to_excel(out_filepath, index=False, engine='openpyxl')

# Cleanup temporary files (optional)
shutil.rmtree(temp_csv_dir)
os.remove(merged_csv_path)

Key Optimizations Breakdown #

• Read-Only Mode: Reduces memory overhead by loading only necessary data from Excel files.
• Incremental Writing: Avoids storing all data in memory by writing each file's content immediately after reading.
• CSV Intermediate: Leverages faster text-based I/O to speed up merging, then converts to Excel once.
• Specialized Libraries: Pyexcelerate cuts down Excel writing time compared to pandas' default openpyxl engine.

内容的提问来源于stack exchange，提问作者zhugq_2012