Take a Random Sample of Rows from a Large Excel File

Exploring a 400,000-row export, building and testing a report against realistic data, or sending a colleague "a representative slice" all call for a sample. The tempting shortcut — the first 10,000 rows — is not a sample: exports are usually sorted by date, customer or region, so the first rows are the oldest records, or one customer, or one region, and every figure computed from them is biased. A proper random sample gives every row the same chance of being chosen. When the file is too big to load, that means sampling while streaming. This page uses reservoir sampling to draw a fixed-size random sample in one pass, extends it to a stratified sample that keeps small groups represented, shows a pandas shortcut for when the row count is known, and checks the sample against the full file. It belongs to Working with Large Excel Files in Python.

Prerequisites

pip install openpyxl pandas

transactions.xlsx holds 200,000 transactions with an ID, a region and an amount. Like many real exports it is not shuffled: the last 10,000 rows are the small "Islands" region, 5% of the data,, whose amounts are higher. That ordering is exactly what makes "the first N rows" misleading.

Step 1 — Why the first rows are not a sample

Compare the first 10,000 rows with the full file:

import pandas as pd

full = pd.read_excel("transactions.xlsx")          # fine here; the point is to avoid this on bigger files
head = pd.read_excel("transactions.xlsx", nrows=10_000)
print("full mean:", round(full["Amount"].mean(), 2), "| first 10,000 mean:", round(head["Amount"].mean(), 2))
print("regions in first 10,000:", sorted(head["Region"].unique()))

The first 10,000 rows contain no Islands transactions at all, so their average amount, 249.89, is well below the true 257.48, and one region is simply missing. A report built and tested on that slice would look fine until it met the real data. Loading the full file here is only to have the true figures for comparison; the methods below never do.

Step 2 — Reservoir sampling in one streaming pass

Reservoir sampling draws a uniform random sample of fixed size k from a stream of unknown length, keeping only k rows in memory. Fill the reservoir with the first k rows; after that, row number i replaces a random reservoir slot with probability k / i:

import random
from openpyxl import load_workbook

def reservoir_sample(path, k, seed=42, sheet=None):
    rnd = random.Random(seed)
    wb = load_workbook(path, read_only=True)
    rows = (wb[sheet] if sheet else wb.active).iter_rows(values_only=True)
    header = next(rows)
    reservoir = []
    for i, row in enumerate(rows, start=1):
        if i <= k:
            reservoir.append(row)
        else:
            j = rnd.randrange(i)              # 0 … i-1
            if j < k:
                reservoir[j] = row
    wb.close()
    return pd.DataFrame(reservoir, columns=header), i

sample, seen = reservoir_sample("transactions.xlsx", k=10_000)
print(f"sampled {len(sample):,} of {seen:,} rows")
print("sample mean:", round(sample["Amount"].mean(), 2), "| regions:", sample["Region"].value_counts().to_dict())

After one pass every row has had exactly the same probability, k / n, of ending up in the sample — the algorithm's guarantee — even though n was unknown until the end. The sample's mean is close to the full file's, and the Islands appear in roughly their true proportion. Memory holds 10,000 rows at most. Using a dedicated random.Random(seed) instance makes the sample reproducible: the same seed gives the same rows, which matters when a colleague needs to rerun your analysis.

Step 3 — A stratified sample keeps small groups visible

A simple random sample reflects proportions, so a group that is 1% of the data gets about 1% of the sample — sometimes too few rows to say anything about it. A stratified sample draws a fixed number per group. With streaming, that is one reservoir per group:

from collections import defaultdict

def stratified_sample(path, column, per_group, seed=42):
    rnd = random.Random(seed)
    wb = load_workbook(path, read_only=True)
    rows = wb.active.iter_rows(values_only=True)
    header = next(rows)
    col = header.index(column)
    reservoirs, counts = defaultdict(list), defaultdict(int)
    for row in rows:
        g = row[col]
        counts[g] += 1
        res = reservoirs[g]
        if len(res) < per_group:
            res.append(row)
        else:
            j = rnd.randrange(counts[g])
            if j < per_group:
                res[j] = row
    wb.close()
    df = pd.DataFrame([r for res in reservoirs.values() for r in res], columns=header)
    weights = {g: counts[g] / len(reservoirs[g]) for g in counts}
    return df, weights

strat, weights = stratified_sample("transactions.xlsx", "Region", per_group=2_000)
print(strat["Region"].value_counts().to_dict())
print("rows represented per sampled row:", {g: round(w, 1) for g, w in weights.items()})

Each region contributes 2,000 rows, so even the smallest is well represented. Because the groups are now over- or under-represented relative to the file, figures for the whole population must be weighted: each sampled row stands for weight rows of its region. The weights returned by the function — the group's size divided by its sample size — are exactly what is needed. Stratifying is worth it whenever the report breaks figures down by a group with very uneven sizes.

Step 4 — Check the sample against the full file

A sample is only useful if its figures are close to the truth. Compare means and group shares for all three approaches:

truth_mean = full["Amount"].mean()
truth_share = full["Region"].value_counts(normalize=True)

strat_w = strat["Region"].map(weights)
estimates = {
    "first 10,000 rows": head["Amount"].mean(),
    "reservoir sample": sample["Amount"].mean(),
    "stratified (weighted)": (strat["Amount"] * strat_w).sum() / strat_w.sum(),
    "stratified (unweighted)": strat["Amount"].mean(),
}
for name, est in estimates.items():
    print(f"{name:24} mean {est:8.2f}  error {est - truth_mean:+7.2f}")
print("Islands share — file:", round(truth_share["Islands"], 3), "| reservoir:",
      round((sample["Region"] == "Islands").mean(), 3))

The reservoir sample and the weighted stratified sample both estimate the mean to within about 0.3; the first-10,000 slice is off by 7.6, more than twenty times as much. The unweighted stratified mean is the worst of all, nearly 30 too high — it treats the Islands, 5% of the data, as a quarter of it — which is exactly why the weights matter. The Islands' share in the reservoir sample is close to its true share. A check like this belongs in any analysis built on a sample: it costs one comparison on the summary figures you care about, and catches a sample that is not doing its job.

Step 5 — The pandas shortcut when the row count is known

If you know how many rows the sheet has — from the sheet's dimension, or a quick row count — pandas can skip the rows not chosen while reading. Choose the row numbers first, then pass a skiprows function:

import numpy as np

n_rows = 200_000                                  # e.g. from count rows without loading the file
keep = set(np.random.default_rng(7).choice(np.arange(1, n_rows + 1), size=10_000, replace=False))
quick = pd.read_excel("transactions.xlsx", skiprows=lambda i: i > 0 and i not in keep)
print(len(quick), "rows | mean", round(quick["Amount"].mean(), 2))

Row 0 is the header and is always kept; every other row is skipped unless its number was drawn. This is convenient, but note that pandas' openpyxl reader still parses every row of the sheet — skiprows saves memory, not reading time. Getting the row count cheaply is covered in Count Rows in an Excel File Without Loading It.

Common pitfalls

• Taking the first N rows. Exports are sorted; the head is rarely representative.
• Unseeded randomness. Fix the seed so the sample can be reproduced.
• Ignoring weights in a stratified sample. Whole-file figures need each row weighted by its group's size.
• Samples too small for the groups you report. Stratify when some groups are small.
• Expecting skiprows to read faster. It saves memory; the sheet is still parsed.

Conclusion

A fair sample of a large Excel sheet needs randomness, not the first rows. Reservoir sampling draws a fixed-size uniform sample in one streaming pass with openpyxl's read-only mode, holding only the sample in memory; a reservoir per group gives a stratified sample that represents small groups, provided whole-file figures are weighted by group size. When the row count is known, pandas' skiprows with a set of drawn row numbers is a convenient alternative. Fix the seed, and compare the sample's key figures with the full data before relying on them.

Frequently asked questions

How do I take a random sample from a large Excel file in Python? Stream the rows with openpyxl in read-only mode and use reservoir sampling: keep the first k rows, then replace a random one with each later row i with probability k divided by i.

Why shouldn't I just use the first rows of the file? Exports are usually sorted by date, region or customer, so the first rows are not representative and figures computed from them are biased.

How do I make sure small groups appear in my sample? Use a stratified sample with a separate reservoir for each group, and weight each sampled row by its group size divided by the group's sample size when estimating totals.

Can pandas read only a random subset of rows? Yes, if you know the number of rows: draw the row numbers to keep and pass a skiprows function to read_excel that skips all others except the header.

Related

• Up one level: Working with Large Excel Files in Python — big files, fast code.
• Read Only Some Rows of an Excel File with pandas — selecting rows by position.
• Aggregate a Huge Excel File Without Loading It — exact totals instead of estimates.
• Generate Realistic Test Data for Excel Reports with Faker — when real samples cannot be shared.