Sampling Random Rows From a Huge CSV Without Loading It

You rarely need all 20 million rows to understand a file. A few thousand random ones tell you the types, the odd values, the ranges and the shape of the mess, and they fit in a spreadsheet. The question is how to draw them from a file too big to open. There are five common ways. They differ in speed by a factor of fifty, and in whether the sample is made of whole rows. This article draws samples from a 1.3 GB, 20,000,000-record file each way and checks the results.

What a good sample is

Three properties matter. It should be random, so every row had the same chance. It should be whole records, so each sampled row still parses. And it should be repeatable when you need to explain how you got it: the same seed gives the same rows. Speed comes last, because every method here finishes in seconds, but memory matters, since the point is not to load the file.

shuf

(head -n 1 orders.csv; tail -n +2 orders.csv | shuf -n 1000 --random-source=<(yes 42)) > sample.csv

The header is copied first so it is not sampled. shuf -n 1000 uses reservoir sampling for a limited count, so it reads the input once and holds only 1,000 lines. It took 4.39 s with about 2.7 MB of peak memory and wrote 1,001 lines. --random-source=<(yes 42) makes the choice deterministic, and I confirmed the checksum of the output was the same on two runs. Without it, two runs gave different checksums. See the GNU coreutils manual.

awk

awk -v k=1000 'BEGIN {srand(42)}
  NR==1 {print; next}
  {i++; if (i <= k) r[i]=$0; else {j=int(rand()*i)+1; if (j<=k) r[j]=$0}}
  END {for (n=1; n<=k; n++) print r[n]}' orders.csv > sample.csv

This is Algorithm R by hand: keep the first k lines, then replace a random slot with decreasing probability. It took 5.05 s and 3 MB. If you do not need exactly k rows, a Bernoulli filter is simpler: awk 'NR==1 || rand() < 0.00005' keeps each line with probability 0.00005. It took 3.86 s and returned 1,002 lines for an expected 1,000, the size being random.

Seeking

import os, random
size = os.path.getsize(path)
with open(path, "rb") as f:
    header = f.readline(); start = f.tell()
    for _ in range(1000):
        f.seek(random.randrange(start, size - 1))
        f.readline()          # finish the line we landed in
        print(f.readline().decode(), end="")

Instead of reading the file, jump to random byte offsets and take the next full line. It took 0.08 s for 1,000 rows, because it touches 1,000 places in the file. Two limitations. The sample is biased: a random byte is more likely to land inside a long line than a short one, so rows with long text are over-represented, and the row after a long row is favored. And it can return the same row twice. Use it for a quick look at a huge file, not for statistics.

DuckDB

COPY (
  SELECT * FROM read_csv('orders.csv', header=true, all_varchar=true)
  USING SAMPLE reservoir(1000 ROWS) REPEATABLE (42)
) TO 'sample.csv' (FORMAT csv, HEADER);

DuckDB parses the whole file and samples records. It took 4.69 s with a 675 MB peak, and the output parsed cleanly as 1,000 records. USING SAMPLE 0.5 PERCENT (bernoulli, 42) took 4.64 s and returned 99,909 records, a Bernoulli sample of about 100,000. REPEATABLE (42) fixes the seed. The sampling clause is documented on the DuckDB sample page. It uses more memory than the line tools because it reads in parallel, which is the price of parsing correctly.

pandas and Python

pandas can skip rows at random while reading, so only the sample is kept:

import pandas as pd, random
random.seed(42)
df = pd.read_csv("orders.csv", skiprows=lambda i: i > 0 and random.random() > 0.00005, dtype=str)

It returned 1,000 rows in 7.54 s with a 113 MB peak. A hand-written reservoir that uses the csv module so that it respects quotes took 26.83 s with a 13 MB peak: correct and about six times slower than DuckDB, because Python parses 20 million rows.

The trap: lines are not rows

Four of the methods sample lines. A CSV record can contain a line break, and a line sample can pick one half of it. In a 100,000-line sample from shuf and one from the seek script, I counted commas per line. A clean row of this file has seven. shuf returned 99,887 clean lines, 67 with eight commas and 47 with none; the seek sample had 99,913, 45 and 43. Those are the pieces of rows whose note contained a quote and a break, and neither sample parsed as CSV (',' expected after '"'). DuckDB's 100,000-row sample parsed as 100,000 records.

For a sample of 1,000 lines the chance of hitting one broken row is about one in two on this file, and you will not notice until a parser stops. If the file has no multi-line fields, the line tools are perfect; check first that line count equals record count (counting lines). If it might, use DuckDB or a csv-module reservoir, or verify the sample with a strict parser (checking counts).

Making a sample you can defend

Record the method, the seed and the size of the file. Check that the sample matches the whole on a few simple measures: the share of each category, the mean of a numeric column, the earliest and latest dates. A sample of 1,000 gives you roughly one decimal place on a proportion; do not read more into it. For stratified needs (equal rows per country), sample inside each group with USING SAMPLE over a PARTITIONed query or filter first. Keep the sample file with the notes, and when you move to the full file, rerun the same analysis there (the large-file hub lists the options).

How to tell it worked

Count the records with a parser, confirm the header is present once, compare category shares with the full file, and run the same sampling command twice with the same seed to confirm the checksum matches. Then try a larger sample and see whether your conclusions move.

When a sample is not enough

Rare events (a handful of fraud rows in 20 million) vanish in a random sample; filter for them instead. Joins between two samples drawn independently find almost nothing in common. Exact counts, sums and distinct counts need the full file, which DuckDB can scan in about a second per gigabyte, as the chunked pandas article compared. And a sample of a sorted or time-ordered file is only representative if the sampling is random across the whole file, which is why reading the first rows is not sampling.