Reading a Large CSV in Chunks With pandas Without Surprises
Read a 20-million-row CSV in chunks with pandas: 150 MB peak instead of 3.9 GB, the same answer as DuckDB, and the traps: zip as int64, mixed types, pyarrow.
"Too big for pandas" means different things on different machines. The same 1.3 GB CSV is fine on a laptop with 16 GB and a crash on a server with 2 GB. Before you reach for chunks, find out whether you need them, because chunking has costs of its own: you have to write the aggregation twice, once per chunk and once to combine. This article measures a full read, a narrower read and a chunked read of a 20,000,000 row file, checks the answers against DuckDB, and lists the traps that show up when you read a big CSV in pieces.
Do you need chunks at all?
Start by reading the whole file and looking:
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_full.py20000000 rows; 1.94 GB by memory_usage(deep=True)
{'order_id': 'int64', 'customer_id': 'int64', 'zip': 'int64', 'amount': 'float64', 'created_at': 'str', 'status': 'str', 'country': 'str', 'note': 'str'}
count sum
status
failed 3999365 1.200298e+09
paid 4002203 1.200583e+09
pending 3999905 1.200166e+09
refunded 3997118 1.198801e+09
shipped 4001409 1.200862e+09
5.55 s, peak 3924400 KBOn this machine the whole 20,000,000-row file loaded in 5.55 s, with a peak of 3,924,400 KB: about 3.9 GB for a 1.3 GB file, a factor of three. (A second run took 3.39 s and 3,906,816 KB.) The DataFrame itself was 1.94 GB by memory_usage(deep=True). On a 16 GB machine, that is fine. On a 4 GB machine it is not, and the file would not have loaded.
Look also at the dtypes line: 'zip': 'int64'. The zip codes in this file are five-digit strings with leading zeros, such as 09587, and pandas read the column as an integer, so **09587 is now 9587**, with no warning. That is the default behavior and the first thing to fix:
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_variants.py defaultdefault 20,000,000 rows 1.94 GB in memory read in 2.32 s
zip: int64 | first zips: [58008, 16017, 40975]
3.39 s, peak 3906816 KBThe first zips printed [58008, 16017, 40975] as integers. Identifiers should be read as text, which the dtype argument does.
Read less
Often the answer is to read fewer columns. usecols makes the parser skip the rest, and dtype picks compact types:
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_variants.py usecolsusecols 20,000,000 rows 0.18 GB in memory read in 4.60 s
zip: - | first zips: -
5.26 s, peak 459744 KBReading two columns (status as a category, amount as a float) took 5.26 s with a 459,744 KB peak and left a DataFrame of 0.18 GB: a tenth of the memory of the full read, still holding all 20,000,000 rows. For a question that touches a few columns, this alone may be enough. The typed read of all columns:
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_variants.py typedtyped 20,000,000 rows 0.72 GB in memory read in 12.72 s
zip: str | first zips: ['58008', '16017', '40975']
13.47 s, peak 1721788 KBWith zip as text, status, country and note as categories, the ids as 32-bit integers, amount as float32 and created_at parsed as a date, the DataFrame was 0.72 GB, down from 1.94, and the peak 1,721,788 KB. It took 13.47 s, about four times the second default read's 3.39 s, and almost all of that is parse_dates; a column that you do not need as a date is cheaper left as text. Note float32 for money loses precision (seven digits); it is here for the memory arithmetic, not as a recommendation, and a decimal type or integer cents is the right choice for amounts.
For a first look, nrows reads only the head:
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_variants.py headhead 100,000 rows 0.01 GB in memory read in 0.05 s
zip: int64 | first zips: [58008, 16017, 40975]
0.60 s, peak 122860 KB100,000 rows in 0.05 s. A head is a biased sample, as the line counting article showed, so use it for the shape of the file and not for statistics.
Read in chunks
When the answer is an aggregate, you do not need the rows at the same time. With chunksize, read_csv returns an iterator of DataFrames. The pattern: compute a small partial result per chunk and combine the partials.
import pandas as pd, sys
size = int(sys.argv[1]); mode = sys.argv[2] if len(sys.argv) > 2 else "typed"
kw = dict(chunksize=size)
if mode == "typed":
kw.update(usecols=["status", "amount"], dtype={"status": "category", "amount": "float64"})
total = None
n = 0
for chunk in pd.read_csv("/batches/csv-0001-0200/work/big/orders-20m.csv", **kw):
n += len(chunk)
part = chunk.groupby("status", observed=True)["amount"].agg(["count", "sum"])
total = part if total is None else total.add(part, fill_value=0)
print(n, "rows in", "chunks of", f"{size:,}")
print(total.round(2).to_string())/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_chunks.py 100000020000000 rows in chunks of 1,000,000
count sum
status
failed 3999365 1.200298e+09
paid 4002203 1.200583e+09
pending 3999905 1.200166e+09
refunded 3997118 1.198801e+09
shipped 4001409 1.200862e+09
6.21 s, peak 150740 KBIt read the file in chunks of a million rows, kept only status and amount, grouped each chunk by status to get a count and a sum, and added the partial tables together with total.add(part, fill_value=0). The run took 6.21 s with a 150,740 KB peak, about 4% of the memory of the full read for about the same time. The counts per status (3,999,365 failed, 4,002,203 paid, 3,999,905 pending, 3,997,118 refunded, 4,001,409 shipped) add up to 20,000,000.
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_chunks.py 10000020000000 rows in chunks of 100,000
count sum
status
failed 3999365 1.200298e+09
paid 4002203 1.200583e+09
pending 3999905 1.200166e+09
refunded 3997118 1.198801e+09
shipped 4001409 1.200862e+09
7.29 s, peak 121788 KBChunks of 100,000 rows took 7.29 s and 121,788 KB: smaller chunks cost a little time and save a little memory, with diminishing returns. Reading all eight columns in a million-row chunk:
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_chunks.py 1000000 allcols20000000 rows in chunks of 1,000,000
count sum
status
failed 3999365 1.200298e+09
paid 4002203 1.200583e+09
pending 3999905 1.200166e+09
refunded 3997118 1.198801e+09
shipped 4001409 1.200862e+09
8.06 s, peak 380684 KB8.06 s and 380,684 KB. The memory rises with the number of columns kept, and the time with the amount parsed. Choose the chunk size so that a chunk fits comfortably in memory with room for the working copy that pandas makes while grouping; a million rows of two columns is nothing, and a million rows of fifty is not.
Check the answer
An aggregate you cannot check is a guess. The same query in DuckDB:
duckdb -c "SELECT status, count(*) AS count, round(sum(amount), 2) AS sum FROM read_csv('work/big/orders-20m.csv') GROUP BY 1 ORDER BY 1"┌──────────┬─────────┬───────────────┐
│ status │ count │ sum │
│ varchar │ int64 │ double │
├──────────┼─────────┼───────────────┤
│ failed │ 3999365 │ 1200297723.76 │
│ paid │ 4002203 │ 1200583216.06 │
│ pending │ 3999905 │ 1200166462.81 │
│ refunded │ 3997118 │ 1198800813.8 │
│ shipped │ 4001409 │ 1200862198.57 │
└──────────┴─────────┴───────────────┘The counts match to the row. The sums match: 1200297723.76 for failed, against 1.200298e+09 printed by pandas (pandas shows fewer digits in the default display; the values are the same). DuckDB took 1.23 s with a 718,120 KB peak for the whole job:
/usr/bin/time -f "%e s, peak %M KB" duckdb -c "SELECT status, count(*) AS count, round(sum(amount), 2) AS sum FROM read_csv('work/big/orders-20m.csv') GROUP BY 1 ORDER BY 1" > /dev/null1.23 s, peak 718120 KBThat is five times faster than the chunked pandas loop and uses no chunk code. If the aggregation is expressible in SQL, writing it in SQL is the shorter path (querying CSV with DuckDB shows how), and the chunk loop is for when you need pandas code, such as a custom function per row group.
What can be combined
A chunked computation works for statistics that can be built from partial results:
Counts and sums: add the partials. That is the loop above.
Min and max: take the min of mins and the max of maxes.
Mean: keep the sum and the count and divide at the end. Never average the chunk means unless the chunks have equal sizes.
Variance and standard deviation: possible with sums of squares or Welford's method, and easy to get subtly wrong.
Median and percentiles: not additive. You need a sketch, a second pass, or the whole column in memory.
Distinct counts: you must remember the distinct values, which can be as big as the data.
Sorting, joins of two big tables: not chunk-friendly; use a database.
The traps
Types are inferred per chunk. Without a dtype, each chunk is inferred on its own, and a column that looks numeric in one chunk and not in another comes out as a mix of types. The last block of the output reproduces it on 400,000 rows:
python - <<'PY'
import pandas as pd, warnings
warnings.simplefilter("always")
# an id column that is numeric for 1,000,000 rows and then has a letter
import io
rows = ["id,v"] + [f"{i},1" for i in range(1, 300001)] + ["A300001,1"] + [f"{i},1" for i in range(300002, 400001)]
df = pd.read_csv(io.StringIO("\n".join(rows)), low_memory=True)
print("dtype of id:", df["id"].dtype, "| types present:", sorted({type(x).__name__ for x in df["id"]}))
PY<stdin>:6: DtypeWarning: Columns (0: id) have mixed types. Specify dtype option on import or set low_memory=False.
dtype of id: object | types present: ['int', 'str']A column of ids with one value A300001 among the numbers produced a DtypeWarning and an object column containing both ints and strs. A later merge or comparison on it fails or silently mismatches. The fix is to pass dtype for every column that matters, as the typed read did, so that chunks cannot differ.
The pyarrow engine does not read this file. engine="pyarrow" is faster on clean files, and here it stopped:
/usr/bin/time -f "%e s, peak %M KB" python lab/big/pd_variants.py pyarrow 2>&1 | tail -n 4 | cut -c1-230 raise ParserError(err) from err
pandas.errors.ParserError: CSV parser got out of sync with chunker. This can mean the data file contains cell values spanning multiple lines; please consider enabling the option 'newlines_in_values'.
Command exited with non-zero status 1
2.87 s, peak 536844 KBThe last lines of the output are the error from pandas: CSV parser got out of sync with chunker. This can mean the data file contains cell values spanning multiple lines. The file has fields with line breaks, which pyarrow's parallel chunker splits incorrectly unless told about them; I found no way to pass that option through read_csv. Use the default C engine for files like this; the pyarrow CSV documentation describes the newlines_in_values option.
Identifiers lose their zeros (the zip, above), dates are not parsed unless you ask, and the header is only in the first chunk's source, which read_csv handles but a manual split does not, as the splitting article found.
How to tell it worked
Compare the chunked result with an independent computation, as above. Check the total number of rows across chunks against the file's record count, not its line count. Print the dtypes of the first and the last chunk. Run once with a small chunksize and once with a large one and expect identical results. And measure the peak memory, since the reason to chunk is to bound it.
When a chunk is the wrong unit
If the question involves the relationship between rows that are far apart (a running total over a sorted key, a join with itself, a rank), chunks of file order cannot answer it. If the file is going to be queried many times, convert it once to Parquet and query that (why Parquet wins for repeated analysis). If SQL can say it, say it in DuckDB, which did the job above in 1.23 s with a quarter of the memory of the full read. And if you must have the whole table in pandas, buy the memory: 3.9 GB is cheap compared with the hours spent making the code fit in 2. The large-file hub puts these options side by side.
