Reduce Memory When Reading Excel with pandas

A 9 MB Excel file sounds small. Read it with pandas and the Python process can briefly use hundreds of megabytes, because an .xlsx is compressed XML: the engine inflates it, parses every cell into Python or Arrow objects and only then builds the DataFrame. On a laptop that is fine; in a container limited to 512 MB, a batch job reading several such files at once is killed without a traceback. This guide, part of Working with Large Excel Files in Python, separates the two memory costs, shows measured numbers for each technique and applies them in code.

The two numbers that matter

Peak memory while reading is set by the engine. openpyxl walks the XML cell by cell and builds the frame from Python objects; calamine, written in Rust, loads the whole sheet into its own buffers first — much faster, but with a higher peak. The size of the DataFrame you keep is set by the columns and types you ask for. A job that reads one file and then does heavy work cares mostly about the second; a job that reads many files in parallel inside a tight container cares about the first.

Measured on an 8.6 MB workbook with 200,000 rows and eight columns (numbers, dates and text), each strategy in a fresh process:

Your numbers will differ with the data, but the pattern holds: dtypes shrink the frame, the engine sets the peak, and only streaming escapes loading the sheet.

Prerequisites

pip install pandas openpyxl python-calamine pyarrow

A smaller version of the test file, so the examples run quickly:

import numpy as np
import pandas as pd

rng = np.random.default_rng(0)
n = 20_000
pd.DataFrame({
    "order_id": np.arange(n),
    "date": pd.date_range("2025-01-01", periods=n, freq="min"),
    "region": rng.choice(["North", "South", "East", "West"], n),
    "product": rng.choice([f"SKU-{i:04d}" for i in range(500)], n),
    "customer": rng.choice([f"Customer {i}" for i in range(20000)], n),
    "qty": rng.integers(1, 50, n),
    "price": (rng.random(n) * 100).round(2),
    "notes": rng.choice(["", "gift", "urgent", "call first"], n),
}).to_excel("orders.xlsx", index=False)

Step 1 — Measure before changing anything

memory_usage(deep=True) reports the real size of each column, including the text:

df = pd.read_excel("orders.xlsx", engine="calamine")
usage = (df.memory_usage(deep=True, index=False) / 1e6).round(2)
print(usage.sort_values(ascending=False).to_dict())
print(round(usage.sum(), 2), "MB")

Without deep=True, pandas counts only the pointers for object columns and understates text-heavy frames badly. The per-column breakdown shows where to act: here the text columns — customer, product, region, notes — dominate. The peak during reading is a process-level number; measure it with resource.getrusage(resource.RUSAGE_SELF).ru_maxrss at the end of a script run in a fresh process, as for the table above.

Step 2 — Read only the columns you use

usecols is the cheapest saving: columns you never read cost nothing in the frame. Name them, rather than using positions, so a reordered export does not silently give you different data:

cols = ["date", "region", "product", "qty", "price"]
slim = pd.read_excel("orders.xlsx", engine="calamine", usecols=cols)
print(round(slim.memory_usage(deep=True).sum() / 1e6, 2), "MB")

Dropping customer, notes and order_id roughly halves the frame. With calamine it hardly changes the peak — the engine parses the whole sheet before pandas selects columns — so usecols is about what you keep, not about getting the file loaded.

Step 3 — Declare compact dtypes

Most columns do not need pandas' defaults. Text with few distinct values fits in a category, which stores each distinct string once and a small integer per row. Quantities that fit in a byte do not need 64 bits, and prices do not always need double precision:

DTYPES = {"region": "category", "product": "category", "qty": "int8", "price": "float32"}
typed = pd.read_excel("orders.xlsx", engine="calamine", usecols=cols, dtype=DTYPES)
print(typed.dtypes.astype(str).to_dict())
print(round(typed.memory_usage(deep=True).sum() / 1e6, 2), "MB")
assert typed["qty"].max() < 127

On the 200,000-row file this took the five-column frame from 10.5 MB to 3.2 MB. Each choice is a promise about the data: int8 holds −128 to 127, so check the maximum or pick int16; float32 keeps about seven significant digits, fine for a unit price but not for large currency totals; category pays off when a column has many repeats — a customer column with a distinct value on almost every row gains nothing. Declaring types at read time also documents the schema; see Check Excel Data Types with pandas.

Step 4 — Choose the engine for the constraint

If the peak is the problem, openpyxl — pandas' default for .xlsx — parses more slowly but held the lowest peak of the in-memory readers in our test. If time is the problem and the container has headroom, calamine is several times faster. Polars' reader was the fastest of all and kept a moderate peak, a reasonable choice if the rest of the pipeline can use Polars:

low_peak = pd.read_excel("orders.xlsx", usecols=cols, dtype=DTYPES)          # openpyxl
fast = pd.read_excel("orders.xlsx", engine="calamine", usecols=cols, dtype=DTYPES)
print(low_peak.equals(fast))

Both engines produce the same frame. The pyarrow dtype backend (dtype_backend="pyarrow") did not help here: pandas 3 already stores text as Arrow-backed strings by default, and the extra conversion raised the peak slightly. Engines are compared in more depth in Speed Up pandas Excel Reads with the calamine Engine.

Step 5 — Stream when the sheet does not fit

When even a lean frame is too big, do not build one. openpyxl's read-only mode yields rows one at a time, so memory stays near the interpreter's baseline however long the sheet is. Aggregate as you go:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx", read_only=True)
rows = wb.active.iter_rows(values_only=True)
header = next(rows)
i_region, i_qty, i_price = (header.index(c) for c in ("region", "qty", "price"))
revenue = {}
for row in rows:
    revenue[row[i_region]] = revenue.get(row[i_region], 0) + row[i_qty] * row[i_price]
wb.close()
print({k: round(v, 2) for k, v in sorted(revenue.items())})

On the 200,000-row file this peaked at 137 MB — 33 MB above the bare interpreter — against 242 MB for the default read. Streaming suits totals, filters and loading rows into a database in batches; anything that needs the whole table at once, such as a sort or a pivot across all rows, belongs in a database or a Parquet file. Streaming into a database is covered in Stream Excel Rows into a Database with openpyxl.

Common pitfalls

• Measuring in the same process. Python rarely returns freed memory to the operating system, so a second read in the same process looks cheaper than it is. Measure each strategy in a fresh process.
• Category columns that grow. Concatenating frames whose categories differ turns them back into plain text. Use union_categoricals or set the full category list up front.
• Parallel reads. Four workers each reading a large file quadruple the peak. Limit concurrency to what the container can hold.

Conclusion

Reading Excel costs memory twice: a peak set by the engine and a frame set by the columns and types. Measure both, read only the columns you use, declare compact dtypes such as category, int8 and float32, and choose openpyxl for a lower peak or calamine and Polars for speed. When a sheet will not fit at all, stream it with openpyxl's read-only mode and aggregate or load rows as they pass.

Frequently asked questions

Which pandas engine uses the least memory for Excel files? In our measurements on a 200,000-row workbook, openpyxl had the lowest peak memory but was about five times slower than calamine, which loads the whole sheet at once. Choose calamine for speed when memory allows, openpyxl when the peak is the constraint.

Does usecols reduce memory? It always makes the resulting DataFrame smaller. It barely lowers the peak with the calamine engine, which parses the whole sheet anyway, so combine it with dtypes for the biggest saving in the frame you keep.

How much memory do category dtypes save? For text columns with few distinct values, a lot. In our test, reading five columns with category, int8 and float32 types gave a frame of 3.2 MB instead of 10.5 MB for the same columns with default types.

What if the file is too big to load at all? Stream it. openpyxl in read-only mode yields one row at a time, so you can aggregate, filter or write rows to a database without ever holding the whole sheet. Peak memory then stays close to the interpreter's baseline.

Related

• Up one level: Working with Large Excel Files in Python — techniques for big workbooks.
• Read a Large Excel File in Chunks with pandas — processing a sheet piece by piece.
• Speed Up openpyxl with Read-Only Mode — the streaming reader in detail.
• Convert Excel Files to Parquet with Python — a format that avoids re-parsing XML.