Python Doc & Data Automation

Fix pandas read_excel Slow on Large Files

A 120 MB export with 480,000 rows and 34 columns takes 3 minutes 40 seconds to load, and the notebook reloads it every time a cell is rerun:

>>> %time frame = pd.read_excel("in/transactions-2026.xlsx")
CPU times: user 3min 38s, sys: 4.1s, total: 3min 42s
Wall time: 3min 44s

The same data as CSV loads in six seconds. Memory peaks above 5 GB during the read, and a laptop with 8 GB starts swapping.

Root Cause

.xlsx is a ZIP archive of XML. Reading it means decompressing, parsing XML for every cell, resolving shared strings, mapping styles to detect dates, and building Python objects — hundreds of operations per cell rather than the byte-level scanning a CSV parser does. openpyxl, pandas' default engine for .xlsx, builds a full in-memory object model of every cell including empty styled ones, which is why memory grows far beyond the file size. The work is proportional to cells, so a wide sheet costs more than a long one with the same byte size, and columns you never use cost exactly as much as the ones you do. Repeating that work on every notebook run, every job run and every retry is usually the larger part of the problem: the same bytes are parsed dozens of times a day.

Minimal Diagnostic

Time each engine on the real file and measure how much of the sheet is actually needed.

# pip install "pandas>=2.2" openpyxl python-calamine
import time
from pathlib import Path
import pandas as pd

SOURCE = Path("in/transactions-2026.xlsx")
NEEDED = ["order_id", "booked_at", "customer_ref", "region", "amount", "status"]

def time_read(label: str, **kwargs) -> pd.DataFrame | None:
    start = time.perf_counter()
    try:
        frame = pd.read_excel(SOURCE, **kwargs)
    except Exception as exc:
        print(f"{label:<34} failed: {type(exc).__name__}: {exc}")
        return None
    mb = frame.memory_usage(deep=True).sum() / 1e6
    print(f"{label:<34} {time.perf_counter() - start:7.1f}s  {frame.shape}  {mb:7.1f} MB in memory")
    return frame

if __name__ == "__main__":
    print(f"file: {SOURCE.stat().st_size / 1e6:.1f} MB")
    full = time_read("openpyxl, all columns", engine="openpyxl")
    time_read("openpyxl, needed columns", engine="openpyxl", usecols=NEEDED)
    time_read("calamine, all columns", engine="calamine")
    time_read("calamine, needed columns", engine="calamine", usecols=NEEDED)
    if full is not None:
        unused = [c for c in full.columns if c not in NEEDED]
        print(f"columns read but unused: {len(unused)} of {len(full.columns)}")

file: 121.4 MB
openpyxl, all columns              224.3s  (480113, 34)   1980.4 MB in memory
openpyxl, needed columns           138.9s  (480113, 6)     214.8 MB in memory
calamine, all columns               31.7s  (480113, 34)   1974.1 MB in memory
calamine, needed columns            22.4s  (480113, 6)     212.6 MB in memory
columns read but unused: 28 of 34

The engine change alone is a sevenfold improvement, and reading six columns instead of 34 cuts memory by 90 percent. Note that usecols does not save as much time with openpyxl as it does memory — the cells are still parsed before being discarded.

Read time by engine and column selection Reading all 34 columns with openpyxl took 224 seconds. Reading only the six needed columns with openpyxl took 139 seconds. Reading all columns with the calamine engine took 32 seconds. Reading the six needed columns with calamine took 22 seconds. Converting once to Parquet and reading that took 0.6 seconds. 480,113 rows x 34 columns, 121 MB .xlsx openpyxl, all 34 columns 224.3 s openpyxl, 6 columns 138.9 s calamine, all 34 columns 31.7 s calamine, 6 columns 22.4 s Parquet cache, 6 columns 0.6 s

Fix: Switch Engine, Select Columns, Cache as Parquet

Read with calamine, take only the columns the job uses, and keep a Parquet copy keyed on the workbook's modification time so repeated runs skip the Excel parse entirely. Changed lines carry comments.

# pip install "pandas>=2.2" python-calamine pyarrow
import hashlib
from pathlib import Path
import pandas as pd

SOURCE = Path("in/transactions-2026.xlsx")
CACHE_DIR = Path("cache")
NEEDED = ["order_id", "booked_at", "customer_ref", "region", "amount", "status"]
DTYPES = {"order_id": "string", "customer_ref": "string", "region": "category", "status": "category"}

def cache_key(path: Path, columns: list[str]) -> Path:
    stat = path.stat()
    fingerprint = f"{path.resolve()}|{stat.st_size}|{int(stat.st_mtime)}|{','.join(columns)}"
    digest = hashlib.sha256(fingerprint.encode()).hexdigest()[:16]     # changed: invalidates on any change
    return CACHE_DIR / f"{path.stem}-{digest}.parquet"

def read_fast(path: Path, columns: list[str] | None = None, use_cache: bool = True) -> pd.DataFrame:
    columns = columns or NEEDED
    cached = cache_key(path, columns)
    if use_cache and cached.exists():
        return pd.read_parquet(cached)                                  # changed: seconds instead of minutes
    frame = pd.read_excel(
        path,
        engine="calamine",                                              # changed: Rust reader, much faster
        usecols=columns,                                                # changed: skip unused columns
        dtype={k: v for k, v in DTYPES.items() if k in columns},        # changed: no inference pass
    )
    if "booked_at" in frame.columns:
        frame["booked_at"] = pd.to_datetime(frame["booked_at"], errors="coerce")
    if use_cache:
        CACHE_DIR.mkdir(parents=True, exist_ok=True)
        tmp = cached.with_suffix(".tmp.parquet")
        frame.to_parquet(tmp, compression="zstd", index=False)
        tmp.replace(cached)                                             # changed: atomic cache write
    return frame

if __name__ == "__main__":
    import time
    for run in (1, 2):
        start = time.perf_counter()
        frame = read_fast(SOURCE)
        print(f"run {run}: {time.perf_counter() - start:6.1f}s  {frame.shape}")

run 1:   23.1s  (480113, 6)
run 2:    0.6s  (480113, 6)

The cache key includes size and modification time, so a new export invalidates it automatically — no stale data, no manual clearing. Declaring dtypes removes pandas' inference pass and turns two repeated-value columns into categoricals, which is most of the remaining memory saving. python-calamine ships as a wheel with no system dependencies and supports .xlsx, .xlsm and .xlsb; pandas has supported engine="calamine" since 2.2.

Read path with a Parquet cache The job computes a cache key from the workbook path, byte size, modification time and the requested columns. If a Parquet file for that key exists it is read in under a second. Otherwise the workbook is read with the calamine engine and only the needed columns and declared dtypes, the result is written to a temporary Parquet file and renamed into the cache atomically, and returned. Cache key path, size, mtime, columns Cache hit? parquet exists Read Excel calamine, usecols, dtypes Write cache temp then rename DataFrame 0.6 s next time Hit read_parquet

Variant Fix 1: Streaming Rows Without Loading the Sheet

When the job aggregates rather than needing the whole frame — summing amounts, counting statuses — stream rows with openpyxl's read-only mode and never build a DataFrame at all:

# pip install openpyxl
from collections import Counter
from pathlib import Path
from openpyxl import load_workbook

def stream_totals(path: Path, sheet: str = "Transactions") -> dict:
    wb = load_workbook(path, read_only=True, data_only=True)      # read_only: streams, constant memory
    try:
        ws = wb[sheet]
        rows = ws.iter_rows(values_only=True)
        header = [str(h).strip() if h else "" for h in next(rows)]
        idx = {name: header.index(name) for name in ("amount", "status", "region")}
        totals, statuses = Counter(), Counter()
        count = 0
        for row in rows:
            if row[idx["amount"]] is None:
                continue
            totals[row[idx["region"]]] += float(row[idx["amount"]])
            statuses[row[idx["status"]]] += 1
            count += 1
        return {"rows": count, "by_region": dict(totals), "by_status": dict(statuses)}
    finally:
        wb.close()

Read-only mode keeps memory flat regardless of file size — it is slower per row than calamine, but it never allocates the whole sheet. Always call wb.close(): read-only workbooks hold open file handles, and a loop over hundreds of files without closing runs into the operating system's descriptor limit.

Variant Fix 2: Convert Once, Read Many Times

When the workbook is an input to several jobs, convert it centrally to Parquet as part of intake rather than caching per job:

# pip install "pandas>=2.2" python-calamine pyarrow
from pathlib import Path
import pandas as pd

def convert_workbook(path: Path, dest_dir: Path, sheets: list[str] | None = None) -> list[Path]:
    dest_dir.mkdir(parents=True, exist_ok=True)
    frames = pd.read_excel(path, sheet_name=sheets or None, engine="calamine")
    if isinstance(frames, pd.DataFrame):
        frames = {"Sheet1": frames}
    written = []
    for name, frame in frames.items():
        for col in frame.select_dtypes("object").columns:
            if frame[col].nunique(dropna=True) < max(50, len(frame) // 100):
                frame[col] = frame[col].astype("category")       # small dictionaries compress well
        out = dest_dir / f"{path.stem}--{name}.parquet"
        frame.to_parquet(out, compression="zstd", index=False)
        written.append(out)
    return written

Downstream jobs then read Parquet with column selection and filters pushed into the read, which is both faster and lower-memory than anything Excel-based. The conversion belongs in the same intake step that validates and archives the file, alongside the patterns in Watching Folders for Incoming Documents — and the schema considerations are the same as in convert a large CSV to Parquet with Python.

Which approach fits which job Reading with calamine and selected columns is fast and simple and suits a single job run. A Parquet cache makes repeated runs almost instant and suits notebooks and retried jobs. openpyxl read-only streaming keeps memory constant and suits aggregation over files too large for memory. A central conversion to Parquet at intake is best when several jobs read the same workbook. Approach Speed Memory Best for calamine + usecols fast frame size single runs Parquet cache instant on reruns frame size notebooks and retries openpyxl read_only slow constant aggregation only Central conversion fast for all readers frame size shared inputs

When the Workbook Itself Is the Problem

Sometimes the file is slow because of what it contains rather than how it is read. A quick look inside the archive shows where its size is:

# stdlib only
import zipfile
from collections import defaultdict
from pathlib import Path

def xlsx_internals(path: Path) -> None:
    with zipfile.ZipFile(path) as zf:
        groups = defaultdict(lambda: [0, 0])
        for info in zf.infolist():
            key = ("sheets" if info.filename.startswith("xl/worksheets/") else
                   "sharedStrings" if "sharedStrings" in info.filename else
                   "styles" if "styles" in info.filename else
                   "media" if info.filename.startswith("xl/media/") else "other")
            groups[key][0] += info.file_size
            groups[key][1] += 1
    for key, (size, count) in sorted(groups.items(), key=lambda kv: -kv[1][0]):
        print(f"{key:<14} {size / 1e6:8.1f} MB uncompressed in {count} part(s)")

if __name__ == "__main__":
    xlsx_internals(Path("in/transactions-2026.xlsx"))

A huge styles part usually means per-cell formatting applied to entire columns; a huge sharedStrings part means many distinct text values, which is normal for transaction data; large media parts mean embedded images that a data reader does not need. Where the workbook is generated by another team, asking them to export CSV or Parquet — or to stop formatting whole columns — often removes the problem at source, and is worth raising with the numbers from the diagnostic in hand.

Verification

Confirm the fast path returns exactly what the slow path did, and that the cache stays correct when the source changes.

# pip install "pandas>=2.2" python-calamine pyarrow
from pathlib import Path
import pandas as pd

def verify_fast_read(path: Path, columns: list[str], sample_rows: int = 20_000) -> None:
    slow = pd.read_excel(path, engine="openpyxl", usecols=columns, nrows=sample_rows)
    fast = read_fast(path, columns, use_cache=False).head(sample_rows)
    for col in columns:
        left = slow[col].astype("string").fillna("")
        right = fast[col].astype("string").fillna("")
        mismatches = (left != right).sum()
        assert mismatches == 0, f"{col}: {mismatches} value(s) differ between engines"
    cached = read_fast(path, columns)
    assert len(cached) >= sample_rows, "cache returned fewer rows than the sample"
    key_before = cache_key(path, columns)
    path.touch()                                            # simulate a new export
    assert cache_key(path, columns) != key_before, "cache key did not change when the file changed"
    print(f"fast read matches openpyxl on {sample_rows:,} rows; cache invalidates on change")

Comparing as strings avoids false failures from engines choosing int64 versus float64 for the same column, while still catching genuine value differences — the important risk when switching engines. Run it once per workbook shape, not per file, since it re-reads the slow way.

FAQ

Is calamine always faster? For .xlsx and .xlsb, substantially. It does not support writing, and it exposes less metadata than openpyxl, so keep openpyxl for editing workbooks and reading formatting.

Does usecols accept names? Yes — names, letters ("A:F") or indices. Names are clearest, and pandas raises if one is missing, which catches renamed headers early.

Why is memory still high after selecting columns? Object-dtype strings dominate. Convert repeated values to category and keep identifiers as string, as the dtype map does.

Can I read .xlsb files? Yes, with calamine, or pyxlsb as an older option. .xls needs xlrd, covered in fix xlrd error reading .xlsx files.

Part of Reading Excel Files with Python.