How to process large CSV files in ETL with pandas

Working with large CSV files is a very common task in ETL, but opening a file of several gigabytes all at once can exhaust your computer's memory and trigger the dreaded MemoryError. This happens because many extraction routines load everything at once, without considering the real size of the data. The solution is to process the file in parts (chunks) with pandas, reading and transforming the data little by little. Let's look, step by step, at how to process large CSV files in ETL simply and safely.

Prerequisites

• Python 3.9 or later installed.
• The pandas library: install it with pip install pandas.
• A large CSV file to test with (for example, vendas.csv with columns such as pais and valor).
• Basic Python knowledge (variables and for loops).

Step 1: Understand the problem

The usual approach, pd.read_csv("vendas.csv"), loads the entire file into RAM. If the file is 5 GB, pandas needs several free gigabytes, often more than the file size, because each column takes up extra space. On machines with little memory, the result is a MemoryError and the program stops.

When RAM runs out, the operating system starts using the disk as virtual memory (swap), and the program becomes extremely slow before, in many cases, failing. The core idea is to never hold the whole file in memory at the same time: we read a block of rows, process that block, free the memory, and move on to the next one.

Step 2: Read the CSV in chunks

The chunksize parameter of read_csv tells pandas how many rows each block should contain. Instead of a DataFrame, pandas returns an iterator that yields one DataFrame at a time:

import pandas as pd

# cada bloco tem 100 000 linhas
for chunk in pd.read_csv("vendas.csv", chunksize=100_000):
    print(chunk.shape)

Each chunk is a normal pandas DataFrame. You can apply everything you already know: filters, new columns, groupings.

Tip: start with blocks of 100 000 rows and adjust according to the available memory. Larger blocks are faster but use more RAM; smaller blocks are safer on modest machines.

Step 3: Transform and aggregate each block

In an ETL pipeline we want to transform the data and, quite often, compute a summary. The next example cleans each block (keeping only positive values) and adds up the sales total per country, accumulating the result in a dictionary:

import pandas as pd

total_por_pais = {}

for chunk in pd.read_csv("vendas.csv", chunksize=100_000):
    # transformar: manter apenas vendas com valor positivo
    chunk = chunk[chunk["valor"] > 0]
    # agregar: somar o valor por pais neste bloco
    soma = chunk.groupby("pais")["valor"].sum()
    for pais, valor in soma.items():
        total_por_pais[pais] = total_por_pais.get(pais, 0) + valor

resultado = pd.Series(total_por_pais).sort_values(ascending=False)
print(resultado)

Notice that we only keep the small total_por_pais dictionary in memory. This pattern (summarizing each block and summing the summaries) works for counts, sums, and weighted averages, and is the basis of any aggregation that does not fit in memory all at once.

Step 4: Write the result without exhausting memory

If your goal is to save the processed data to another file, write each block as you process it. The trick is to write the header only for the first block and use mode "a" (append) for the following ones:

import pandas as pd

primeiro = True
for chunk in pd.read_csv("vendas.csv", chunksize=100_000):
    limpo = chunk[chunk["valor"] > 0]
    limpo.to_csv(
        "vendas_limpas.csv",
        mode="w" if primeiro else "a",
        header=primeiro,
        index=False,
    )
    primeiro = False

This way, the output file grows block by block and never needs to hold everything in memory.

Step 5: Reduce memory even further (optional)

You can save a lot of memory by reading only the columns you need with usecols and specifying lighter types with dtype:

import pandas as pd

for chunk in pd.read_csv(
    "vendas.csv",
    usecols=["pais", "valor"],
    dtype={"valor": "float32"},
    chunksize=100_000,
):
    ...

Reading fewer columns and using float32 instead of float64 reduces the space each block takes, allowing larger blocks or more modest machines.

Verify the result

To confirm you didn't lose data, count the rows of the output file, also in chunks, so you don't fill up memory again:

import pandas as pd

total = sum(len(c) for c in pd.read_csv("vendas_limpas.csv", chunksize=100_000))
print("Linhas no ficheiro final:", total)

Compare this number with what you expected after cleaning. While it runs, open Task Manager (or the htop command on Linux) and confirm that the memory used by Python stays stable, instead of rising without stopping.

Conclusion

With the chunksize parameter you can process CSV files much larger than the available memory, keeping your ETL pipeline stable and predictable. From here, try tuning the block size to your hardware and explore alternatives such as polars or dask when you need even more speed. What is the largest file you have ever had to process, and how long did it take?