lesson 222 Data Formats & Analytical Workflows · advanced

Reading a file in chunks

read_csv(chunksize=n) hands you a large file n rows at a time, so memory holds one chunk instead of the whole file. The answer is built by combining partial results, which works for sums and counts but not for averages of averages.

You'll be able to

Iterate over pd.read_csv(path, chunksize=n) and read only the needed columns with usecols
Combine per-chunk sums and counts into an exact overall result
Explain why averaging the chunk averages gives the wrong mean

The idea

The full taxi file has more than a hundred thousand trips; a year of trips has tens of millions, more than fits in memory. pd.read_csv(path, chunksize=10_000) doesn't read the file: it returns a reader that you loop over, getting a DataFrame of up to 10,000 rows each time. Like the generators of the Iterators & Generators module, it holds one piece at a time. usecols=[...] reads only the columns you name, which saves memory again.

Every chunk is an ordinary DataFrame, so you compute a partial result on it and combine those. Sums and counts combine by adding.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Total fares, three rows at a time

import io
import pandas as pd

csv_text = """trip_id,payment_type_id,fare_amount,tip_amount
16126,1,40.8,10.7
16127,1,7.2,2.55
16128,0,17.2,0
16129,0,22.06,0
16130,1,5.1,1
16131,1,16.3,4.41
16132,1,42.9,11.12
"""
total = 0.0
rows = 0
for chunk in pd.read_csv(io.StringIO(csv_text), chunksize=3, usecols=["fare_amount"]):
    print(len(chunk), list(chunk.columns))
    total += chunk["fare_amount"].sum()
    rows += len(chunk)
print(rows, round(total, 2))

example-2.py · Asking a chunk reader for its length

import io
import pandas as pd

reader = pd.read_csv(io.StringIO("trip_id,fare_amount\n16126,40.8\n16127,7.2\n"), chunksize=1)
print(len(reader))

example-3.py · Average fare per payment type, exactly

import io
import pandas as pd

csv_text = """trip_id,payment_type_id,fare_amount
16126,1,40.8
16127,1,7.2
16128,0,17.2
16129,0,22.06
16130,1,5.1
16131,1,16.3
16132,1,42.9
"""
sums, counts, chunk_means = {}, {}, []
for chunk in pd.read_csv(io.StringIO(csv_text), chunksize=4):
    part = chunk.groupby("payment_type_id")["fare_amount"].agg(["sum", "count"])
    for kind, row in part.iterrows():
        sums[kind] = sums.get(kind, 0) + row["sum"]
        counts[kind] = counts.get(kind, 0) + row["count"]
    chunk_means.append(chunk.loc[chunk["payment_type_id"] == 1, "fare_amount"].mean())
print({int(k): round(float(sums[k] / counts[k]), 2) for k in sorted(sums)})
print(round(sum(chunk_means) / len(chunk_means), 2), "is the wrong card average")

When to use it: files larger than you want in memory, or streams you process once. Use usecols always. Mistakes to avoid: averaging averages (keep sums and counts); medians and other order statistics can't be combined from chunk results at all (use an approach built for them, or a database); a chunk boundary can split a group, so never assume one chunk holds a whole customer or day; and reusing a reader that is already exhausted.

Review

read_csv(path, chunksize=n, usecols=[...]) yields DataFrames of up to n rows; memory holds one at a time.
Combine sums and counts across chunks; divide at the end.
Averages of averages, and medians, don't combine.
Next: let a database do the work with SQL.

Practise this