Pandas合并349个CSV时MemoryError问题及解决方案咨询

Great question—handling large CSV merges with Pandas is super common, but it’s easy to run into memory issues if you’re loading everything into memory at once. Let’s break down what’s going wrong with your current code, then walk through better approaches.

What’s Causing Your MemoryError? #

• Storing all DataFrames in a list: Your dfs collection holds every loaded file in memory simultaneously. CSV files expand significantly when loaded into Pandas (often 2-5x their original size), so 15GB of raw data could easily balloon to 30-75GB in memory—way beyond what most systems can handle.
• Inefficient batch merging: Using df.append() in a loop creates a new DataFrame every time, copying all existing data with each iteration. This leads to exponential memory overhead and glacial performance.
• Sleeping doesn’t fix memory issues: Pausing execution doesn’t free up memory; the DataFrames in dfs still occupy space until explicitly deleted.

Better Approaches to Avoid Memory Overload #

1. Stream Files Directly to Output CSV (Most Memory-Efficient) #

Instead of loading all data into memory, process each file individually and append its contents directly to your final output file. This uses almost no extra memory beyond the single file you’re currently processing.

import glob
import pandas as pd
import gc

path = r"C:\path\*\certificates.csv"
keep_cols = [...]  # Your list of columns to retain
output_path = "combined_certificates.csv"

file_list = list(glob.iglob(path, recursive=True))
first_file = True

for file in file_list:
    # Load ONLY the columns you need (critical for memory savings)
    df = pd.read_csv(
        file,
        usecols=keep_cols,
        # Optional: Specify data types to reduce memory usage
        dtype={
            "category_column": "category",  # Ideal for columns with many repeated values
            "integer_column": "int32",      # Smaller type than default int64
            "float_column": "float32"       # Smaller type than default float64
        }
    )
    
    # Write to output: include header only for the first file
    df.to_csv(
        output_path,
        mode="a",
        header=first_file,
        index=False
    )
    
    # Free up memory immediately
    del df
    gc.collect()  # Force garbage collection to release unused memory
    
    first_file = False
    print(f"Processed: {file}")

Key optimizations here:

• usecols: Loads only the columns you need upfront, instead of reading the entire file then filtering.
• dtype: Specifies smaller or more efficient data types to cut down memory usage per DataFrame.
• Immediate deletion + garbage collection: Ensures each file’s data is cleared from memory before moving to the next.

2. Use Dask for Parallel, Out-of-Core Processing #

If you need to perform analysis on the merged data (not just combine files), Dask DataFrames are designed for this exact scenario. Dask splits your data into chunks, processes them in parallel, and only loads small portions into memory at a time.

import dask.dataframe as dd
import glob

path = r"C:\path\*\certificates.csv"
keep_cols = [...]

# Read all CSV files as a Dask DataFrame
ddf = dd.read_csv(
    path,
    usecols=keep_cols,
    dtype={
        "category_column": "category",
        "integer_column": "int32"
    }
)

# Save the merged data to disk (Dask handles chunking automatically)
ddf.to_csv("combined_certificates_*.csv", index=False, single_file=True)

# If you need to work with the data directly (and have enough memory for the final dataset):
# merged_df = ddf.compute()

Dask mimics Pandas syntax, so you can use most of the same operations (groupby, filter, etc.) without modifying your workflow too much.

Fixing Your Original Batch Merging Code (If You Must Use Pandas) #

If you want to stick with Pandas batch merging, rewrite it to avoid storing all DataFrames and use pd.concat for batches instead of append:

import glob
import pandas as pd
import gc

path = r"C:\path\*\certificates.csv"
keep_cols = [...]
batch_size = 10
output_path = "combined_certificates.csv"

file_list = list(glob.iglob(path, recursive=True))
first_batch = True

for i in range(0, len(file_list), batch_size):
    batch_files = file_list[i:i+batch_size]
    batch_dfs = []
    
    for file in batch_files:
        df = pd.read_csv(file, usecols=keep_cols)
        batch_dfs.append(df)
    
    # Merge the batch into a single DataFrame
    batch_merged = pd.concat(batch_dfs, ignore_index=True)
    
    # Write to output
    batch_merged.to_csv(
        output_path,
        mode="a",
        header=first_batch,
        index=False
    )
    
    # Free memory for this batch
    del batch_dfs, batch_merged
    gc.collect()
    
    first_batch = False
    print(f"Processed batch {i//batch_size + 1}")

This way, you only keep one batch of DataFrames in memory at a time, instead of all 349.

Final Notes #

Pandas isn’t the wrong tool here—you just need to avoid loading your entire dataset into memory. Streaming to CSV is best for simple merging, while Dask is ideal if you need to analyze the data afterward. Always use usecols and dtype to minimize memory usage per file.

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