Reading Only Some Columns of a Large CSV With pandas

Most analysis uses a handful of columns from a file that has dozens. The default read_csv parses and stores every one of them, so a 130 MB file becomes a few hundred megabytes of frame and the script that worked on a sample falls over on the full export. The fix is to tell pandas which columns you need before it reads them, and, if that is not enough, how to store them. This article measures what each option saves on a 2,000,000 row orders file, shows the one place where saving memory changes your answer, and covers the chunked read for files that will not fit even after that. The options are documented in the read_csv reference and the scaling guide. For other ways to make awkward files load, see the read_csv article, and for the whole picture the CSV in Python guide.

See the header first

You cannot choose columns without knowing their names. Reading zero rows returns the header and nothing else:

import pandas as pd

path = "/batches/csv-0001-0200/work/orders-2m.csv"
print("header only :", list(pd.read_csv(path, nrows=0).columns))
print("order       :", list(pd.read_csv(path, usecols=["country", "amount"], nrows=2).columns))
print("by position :", list(pd.read_csv(path, usecols=[3, 6], nrows=2).columns))
print("callable    :", list(pd.read_csv(path, usecols=lambda c: c.endswith("_id"), nrows=2).columns))
try:
    pd.read_csv(path, usecols=["country", "price"], nrows=2)
except ValueError as e:
    print("ValueError  :", e)
a = pd.read_csv(path, usecols=["amount"])["amount"]
b = pd.read_csv(path, usecols=["amount"], dtype={"amount": "float32"})["amount"]
print(f"float64 sum : {a.sum():.2f}")
print(f"float32 sum : {b.sum():.2f}  (column stored as float32, summed as {b.sum().dtype})")
print("first value :", a.iloc[0], "->", float(b.iloc[0]))

python /batches/csv-0001-0200/lab/py/pd_cols_misc.py

header only : ['order_id', 'customer_id', 'zip', 'amount', 'created_at', 'status', 'country', 'note']
order       : ['amount', 'country']
by position : ['amount', 'country']
callable    : ['order_id', 'customer_id']
ValueError  : Usecols do not match columns, columns expected but not found: ['price']
float64 sum : 160174646.36
float32 sum : 160174656.00  (column stored as float32, summed as float32)
first value : 103.1 -> 103.0999984741211

header only printed all eight names from nrows=0. A neighbor to this, nrows=1000, is how I peek at a file: it parses a thousand rows in about 0.01 s, as the timing table below shows, instead of the whole thing.

Choosing columns with usecols

usecols takes a list of names, a list of positions or a function that is given each name and returns whether to keep it:

• By name, usecols=["country", "amount"], returned the columns ['amount', 'country']. The order is the file's order, not the order in the list. If you need another order, reindex after the read.

• By position, usecols=[3, 6], returned the same two columns. Positions are brittle, because a file that gains a column shifts them all; I would only use them on files with no header.

• By function, usecols=lambda c: c.endswith("_id"), returned ['order_id', 'customer_id']. That is useful for wide files with a naming scheme.

• A name that is not in the header is an error: ValueError: Usecols do not match columns, columns expected but not found: ['price']. That is a good failure. A column renamed upstream is caught on the day it happens and not weeks later in a chart.

What it saves

I ran the same read six ways, each in its own process, and recorded the size of the frame, the peak memory of the process and the wall time:

import sys, time, resource
import pandas as pd

path = "/batches/csv-0001-0200/work/orders-2m.csv"
mode = sys.argv[1]
t = time.perf_counter()
if mode == "all":
    df = pd.read_csv(path)
elif mode == "usecols":
    df = pd.read_csv(path, usecols=["country", "amount"])
elif mode == "usecols-dtype":
    df = pd.read_csv(path, usecols=["country", "amount"], dtype={"country": "category", "amount": "float32"})
elif mode == "all-dtype":
    df = pd.read_csv(path, dtype={"status": "category", "country": "category", "zip": "str"})
elif mode == "nrows":
    df = pd.read_csv(path, nrows=1000)
elif mode == "chunks":
    parts = []
    for chunk in pd.read_csv(path, usecols=["country", "amount"], chunksize=250_000):
        parts.append(chunk.groupby("country")["amount"].sum())
    df = pd.concat(parts).groupby(level=0).sum().to_frame()
dt = time.perf_counter() - t
mem = df.memory_usage(deep=True).sum() / 1e6
rss = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024
print(f"{mode:14s} shape={str(df.shape):14s} frame={mem:7.1f}MB peak_rss={rss:5.0f}MB time={dt:5.2f}s")
if mode in ("usecols", "chunks"):
    print(df.groupby(level=0).sum().round(2).head(3) if mode == "chunks" else df.groupby("country")["amount"].sum().round(2).head(3))

for m in all all-dtype usecols usecols-dtype nrows chunks; do python /batches/csv-0001-0200/lab/py/pd_cols.py $m; done

all            shape=(2000000, 8)   frame=  197.8MB peak_rss=  486MB time= 0.54s
all-dtype      shape=(2000000, 8)   frame=  163.0MB peak_rss=  465MB time= 0.64s
usecols        shape=(2000000, 2)   frame=   36.0MB peak_rss=  152MB time= 0.36s
country
BR    22933345.89
DE    22915844.15
FR    22969870.61
Name: amount, dtype: float64
usecols-dtype  shape=(2000000, 2)   frame=   10.0MB peak_rss=  131MB time= 0.36s
nrows          shape=(1000, 8)      frame=    0.1MB peak_rss=  105MB time= 0.01s
chunks         shape=(7, 1)         frame=    0.0MB peak_rss=  128MB time= 0.41s
              amount
country             
BR       22933345.89
DE       22915844.15
FR       22969870.61

Choosing two columns cut the peak from 486 MB to 152 MB, a little over two thirds, and shortened the read from 0.54 s to 0.36 s. The time saving is modest because the parser still has to scan every byte of every line to find the separators; what it avoids is converting and storing the columns you did not want. The memory saving is the one that matters. The baseline for the process, with pandas imported and nothing loaded, accounts for roughly the 105 MB seen in the nrows row, so the real cost of the two column frame is the difference.

Declaring types helps the frame more than the process. Storing country as category, since it has seven distinct values, and amount as float32 took the frame from 36.0 MB to 10.0 MB. The peak memory of the process barely moved (152 MB to 131 MB) because the parser's own buffers dominate. If your constraint is the memory of the finished frame, dtype is worth the lines. If it is the peak during the read, usecols and chunksize are the levers.

The float32 trap

A smaller type is a different number. float32 has about seven significant decimal digits, and money has more than that to lose once you add it up. I summed the amount column both ways:

• as float64, the total is 160,174,646.36

• as float32, the total is 160,174,656.00

The difference is 9.64 on a total of 160 million, which is small in relative terms, and a reconciliation that does not tie to the cent. The first value shows where it starts: 103.1 in the file is stored as 103.0999984741211 in float32. For counts, ids, percentages and anything you only plot, float32 or small integer types are fine. For money, keep float64, or better read as strings and use decimal, or read cents as integers. category is a different case: it is lossless, and it speeds up groupby on that column, so I use it for any column with few distinct values.

Reading in chunks

When even the selected columns will not fit, or when you only need an aggregate, read the file in pieces and reduce each one before the next arrives. chunksize turns read_csv into an iterator of frames, and the chunks mode of the script above uses it. The last row of the table is that read: 250,000 rows at a time, each reduced to a per country sum, and the sums combined at the end. The per country totals matched the single pass read exactly (BR 22,933,345.89, DE 22,915,844.15, FR 22,969,870.61), and the peak was 128 MB, close to the lowest of the six, because only one chunk lived in memory at a time. The script keeps a list of small series and does a second groupby over their concatenation, which is the usual pattern.

This pattern works for any aggregate that can be combined: sums, counts, minimums and maximums, and averages if you carry the sum and the count. It does not work for a median, a distinct count or a rank, which need all the values together. For those, either reduce to a smaller set of columns first and fit it in memory, or use a tool that does the work out of core. A DuckDB query on the same file computes the whole grouped sum without any chunking code, in under half a second, as the DuckDB guide shows, and duckdb.sql(...).df() hands the result to pandas.

How to tell it worked

Check df.shape and df.columns. Compare an aggregate against one computed another way: the same sum from DuckDB or from awk over the file. Check df.memory_usage(deep=True).sum() against what you expected, and watch the process, not only the frame, when the constraint is the machine's memory. If you used a narrower numeric type, compare the totals with the wide type on a sample and decide whether the difference is acceptable.

Where these options stop helping

If you need most of the columns, usecols buys nothing, and a smaller dtype or a different tool is the only remaining lever. If a file has a few hundred columns and you need ten, the savings are large, and I always set usecols there. If the data will be read many times, parse it once and save it to Parquet, which stores columns separately and lets you read only the ones you need without scanning the rest, as in the Parquet conversion article. And if the file is far larger than memory, chunks are a workaround; a columnar engine that streams is the better answer.