Aggregate a Huge Excel File Without Loading It

The question is small — total revenue and order count per region and month — but the workbook is not: half a million rows that pandas reads into several hundred megabytes before a single sum is computed. On a laptop that is slow; on a small server, in a container or in a scheduled job sharing a machine, it can fail outright. Most summary statistics do not need the whole table at once. A sum, a count, a minimum or a maximum can be updated one row at a time, so a streaming group-by reads each row, updates a small dictionary of running totals and forgets the row. Memory then depends on the number of groups, not the number of rows. This page builds that aggregation over openpyxl and the faster calamine reader, deals with the statistics that cannot be streamed so simply, and checks the result against pandas. It belongs to Working with Large Excel Files in Python.

Prerequisites

pip install openpyxl python-calamine pandas

sales_big.xlsx has one sheet of 300,000 sales lines: date, region, product, quantity and unit price. Large enough to show the memory difference clearly, small enough to run in a minute; the same code works unchanged on files several times larger.

Step 1 — A streaming group-by

The core is a dictionary keyed by the group, holding running totals. Each row updates one entry and is then discarded:

from collections import defaultdict
from openpyxl import load_workbook

def new_acc():
    return {"orders": 0, "units": 0, "revenue": 0.0, "max_line": float("-inf"), "min_line": float("inf")}

def aggregate(rows, header):
    i = {name: header.index(name) for name in header}
    groups = defaultdict(new_acc)
    for r in rows:
        key = (r[i["Region"]], r[i["Date"]].strftime("%Y-%m"))
        line = r[i["Qty"]] * r[i["Unit price"]]
        a = groups[key]
        a["orders"] += 1
        a["units"] += r[i["Qty"]]
        a["revenue"] += line
        a["max_line"] = max(a["max_line"], line)
        a["min_line"] = min(a["min_line"], line)
    return groups

wb = load_workbook("sales_big.xlsx", read_only=True)
rows = wb["Sales"].iter_rows(values_only=True)
header = list(next(rows))
groups = aggregate(rows, header)
wb.close()
print(len(groups), "groups")
print(sorted(groups.items())[0])

openpyxl's read-only mode yields one row at a time without building the sheet in memory. The accumulator for each region and month holds five numbers, so the whole result is 36 small dictionaries, whatever the length of the file. defaultdict creates an accumulator the first time a group is seen. Averages come at the end: average revenue per order is revenue / orders — that is why the count is kept alongside the sum. Any statistic that can be built from such running values works this way: sums, counts, means, minima, maxima, and even variance (by also accumulating the sum of squares, or better, with Welford's method).

Step 2 — The same loop over calamine, faster

openpyxl parses XML in Python; python-calamine parses it in Rust and is much faster. Its iter_rows also yields one row at a time, so the same aggregate function works:

import time
from python_calamine import CalamineWorkbook

t0 = time.perf_counter()
wb = load_workbook("sales_big.xlsx", read_only=True)
rows = wb["Sales"].iter_rows(values_only=True)
g1 = aggregate(rows, list(next(rows)))
wb.close()
t_openpyxl = time.perf_counter() - t0

t0 = time.perf_counter()
sheet = CalamineWorkbook.from_path("sales_big.xlsx").get_sheet_by_name("Sales")
rows = sheet.iter_rows()
g2 = aggregate(rows, list(next(rows)))
t_calamine = time.perf_counter() - t0

same = all(abs(g1[k]["revenue"] - g2[k]["revenue"]) < 1e-6 and g1[k]["orders"] == g2[k]["orders"] for k in g1)
print(f"openpyxl read-only: {t_openpyxl:.1f}s | calamine: {t_calamine:.1f}s | same totals: {same}")

Both readers give identical totals; calamine took about 1 second against openpyxl's 8 on this file. Two details differ: calamine returns all numbers as floats (so quantities arrive as 3.0), and dates as date or datetime objects depending on the cell, which strftime handles either way. Note that calamine's iter_rows still parses the sheet up front in Rust — it is faster, but its memory use is closer to the size of the sheet's values than openpyxl's strictly row-by-row reading, so for the very largest files on the most constrained machines, openpyxl read-only remains the lowest-memory option.

Step 3 — Compare with pandas, on time and memory

To see what streaming buys, measure peak Python memory for the pandas approach and the streaming one:

import tracemalloc
import pandas as pd

def peak(fn):
    tracemalloc.start()
    out = fn()
    p = tracemalloc.get_traced_memory()[1]
    tracemalloc.stop()
    return out, round(p / 2**20, 1)

def with_pandas():
    df = pd.read_excel("sales_big.xlsx")
    df["Revenue"] = df["Qty"] * df["Unit price"]
    return df.groupby([df["Region"], df["Date"].dt.strftime("%Y-%m")]).agg(orders=("Qty", "size"), revenue=("Revenue", "sum"))

def streamed():
    wb = load_workbook("sales_big.xlsx", read_only=True)
    rows = wb["Sales"].iter_rows(values_only=True)
    out = aggregate(rows, list(next(rows)))
    wb.close()
    return out

pd_result, pd_mb = peak(with_pandas)
st_result, st_mb = peak(streamed)
diff = max(abs(pd_result.loc[k, "revenue"] - v["revenue"]) for k, v in st_result.items())
print(f"pandas peak {pd_mb} MB | streamed peak {st_mb} MB | largest revenue difference {diff:.6f}")

The pandas version peaked at about 111 MB for this file — the whole DataFrame plus openpyxl's working memory while reading — and grows in proportion to the rows. The streaming version peaked at about 24 MB, which is mostly openpyxl's own parsing buffers plus the 36 accumulators, and it does not grow with the file. The totals agree to floating-point rounding. Streaming is not faster than pandas with the same reader — pandas also has to parse every row — so the reason to stream is memory, not speed.

Step 4 — Statistics that need more than running totals

Medians, percentiles and distinct counts cannot be updated from a few numbers per group. Two practical options keep memory bounded:

from collections import Counter

def median_from_counts(counter):
    total = sum(counter.values())
    values = sorted(counter)
    def nth(k):                                   # k-th smallest value, 0-based
        running = 0
        for v in values:
            running += counter[v]
            if running > k:
                return v
    if total % 2:
        return float(nth(total // 2))
    return (nth(total // 2 - 1) + nth(total // 2)) / 2

qty_counts = defaultdict(Counter)
products_seen = defaultdict(set)
wb = load_workbook("sales_big.xlsx", read_only=True)
rows = wb["Sales"].iter_rows(values_only=True)
h = list(next(rows))
for r in rows:
    qty_counts[r[h.index("Region")]][r[h.index("Qty")]] += 1
    products_seen[r[h.index("Region")]].add(r[h.index("Product")])
wb.close()
check = pd.read_excel("sales_big.xlsx", usecols=["Region", "Qty"]).groupby("Region")["Qty"].median()
for region in sorted(qty_counts):
    print(region, "median qty", median_from_counts(qty_counts[region]), "| pandas", check[region],
          "| distinct products", len(products_seen[region]))

When the values are discrete — quantities, ratings, whole-euro amounts — a Counter of value frequencies per group is small (here eleven possible quantities) and gives an exact median or any percentile. For distinct counts, a set of seen values per group works while the number of distinct values is modest, as for products here; for millions of distinct customer IDs, store compact hashes, or accept an approximate algorithm such as HyperLogLog. For continuous values with no natural rounding, an exact streaming median needs either a second pass or an approximate sketch — or simply a sample, since a median from a random 10% of a large file is usually accurate enough for a report. The medians here match pandas exactly.

Step 5 — Write the summary

The aggregated result is tiny, so pandas is the right tool from here on:

summary = pd.DataFrame([
    {"Region": region, "Month": month, "Orders": a["orders"], "Units": a["units"],
     "Revenue": round(a["revenue"], 2), "Avg per order": round(a["revenue"] / a["orders"], 2),
     "Largest line": a["max_line"]}
    for (region, month), a in sorted(groups.items())
])
summary.to_excel("sales_summary.xlsx", index=False)
print(summary.head(4).to_string(index=False))
print("summary rows:", len(summary), "| grand total revenue:", round(summary["Revenue"].sum(), 2))

Thirty-six rows summarise 300,000; the average per order is computed from the accumulated revenue and count, which is why both were kept. From here, formatting, a pivot by month or a chart work exactly as for any small table. Streaming reads in general are covered in Speed Up openpyxl with Read-Only Mode.

Common pitfalls

• Loading the whole sheet to compute a few totals. Stream and accumulate instead.
• Keeping only the mean. Keep sum and count so averages can be combined and recomputed.
• Assuming medians stream like sums. Use value counts, a second pass, a sketch or a sample.
• Different number types from different readers. calamine returns floats for integers.
• Expecting streaming to be faster than pandas. It saves memory; speed comes from the reader.

Conclusion

To aggregate an Excel sheet too large to load comfortably, stream it: read one row at a time with openpyxl's read-only mode or python-calamine's iter_rows, update a dictionary of running totals per group — count, sum, minimum, maximum — and compute averages from sums and counts at the end. Memory then depends on the number of groups, not rows. Medians and distinct counts need a bit more: value counts for discrete data, sets or sketches for distinct values, or a sample. Hand the small result to pandas for formatting and output.

Frequently asked questions

How do I sum an Excel file that is too big for pandas? Read it row by row with openpyxl load_workbook(read_only=True) and iter_rows(values_only=True), adding each row's values to running totals in a dictionary keyed by group.

Can I calculate an average without loading all rows? Yes. Keep a running sum and count for each group and divide at the end.

How do I get a median from a streamed Excel file? For discrete values, count how often each value occurs per group and read the median from the counts. For continuous values, use a second pass, an approximate sketch or a random sample.

Is a streaming aggregation faster than pandas? Not usually, because both must parse every row. Its advantage is memory, which stays small regardless of file size.

Related

• Up one level: Working with Large Excel Files in Python — big files, fast code.
• Read a Large Excel File in Chunks with pandas — chunks instead of single rows.
• Reduce Memory When Reading Excel with pandas — when pandas is still the right tool.
• Create a Pivot Table from Excel with pandas — the in-memory equivalent.