Benchmarking Pandas, Polars, and PyArrow for Fare-File Ingestion

Choosing an ingestion engine for nightly settlement and tap-event CSVs is a throughput, memory, and correctness decision all at once — and the correctness half is the one most benchmarks quietly get wrong. This page compares pandas, Polars, and PyArrow reading the same fare file across three volume tiers (100K, 1M, 10M rows), measuring read throughput and peak resident memory while insisting that the amount_cents column survive the trip as an exact integer or Decimal, never a lossy float64. It sits under CSV Batch Parsing Workflows within Fare Data Ingestion & GTFS-RT Sync, and it exists to answer one operational question: which engine do you reach for at each file size, and what does it cost you at the settlement ledger if you pick the fast one and let money become a float.

Engine selection flow

The three engines are not interchangeable — each wins a different tier, and the money-handling rule cuts across all of them. The flow below routes a fare file to an engine by volume and by whether the pipeline needs streaming, then funnels every path through the same money-parsing gate before a row reaches the ledger.

Step 1 — A common fare file and the money trap

Every engine reads the same canonical AFC export: a transaction_id, a card_uid, a tap_timestamp, a route_id, and an amount_cents column. The single most consequential decision is how amount_cents is typed. If the vendor writes minor units as bare integers you are safe, but many settlement files carry a decimal major-unit column like 2.75, and the instant a reader parses that as float64 the value becomes the nearest IEEE-754 double, not 2.75. Summed across ten million taps, that rounding error is a real settlement variance an auditor will find.

The rule is uniform across pandas, Polars, and PyArrow: read the money column as a string, then convert deterministically to a scaled integer or decimal.Decimal. Never let the CSV reader infer a floating dtype for money.

from decimal import Decimal, ROUND_HALF_UP

def major_units_to_cents(raw: str) -> int:
    """Parse a major-unit money string to exact integer cents, or raise."""
    if raw is None or raw.strip() == "":
        raise ValueError("Empty money value; refuse to default to zero.")
    # Decimal parses the *text* exactly; float would parse the nearest double.
    cents = (Decimal(raw) * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP)
    return int(cents)

assert major_units_to_cents("2.75") == 275
assert major_units_to_cents("0.10") == 10   # float64 would carry 0.1000000000000000055

The 0.10 case is the whole argument in one line: Decimal("0.10") is exactly one tenth, float("0.10") is not. Applying major_units_to_cents after the string read makes all three engines produce byte-identical ledgers.

Step 2 — Reading the same file in each engine

Each snippet reads the identical file, forces the money column to arrive as text, and defers the cents conversion to the shared function above. The differences are in the dtype API and the execution model, not in the correctness contract.

import pandas as pd

def read_with_pandas(path: str) -> pd.DataFrame:
    """Eager, single-threaded read; money column pinned to string dtype."""
    df = pd.read_csv(
        path,
        dtype={"transaction_id": "string", "card_uid": "string",
               "route_id": "string", "amount_major": "string"},
        parse_dates=["tap_timestamp"],
    )
    df["amount_cents"] = df["amount_major"].map(major_units_to_cents).astype("Int64")
    return df.drop(columns=["amount_major"])

Polars reads the same file with a multi-threaded CSV parser. Using scan_csv instead of read_csv returns a lazy frame, so the money cast is pushed into the query plan and the file is streamed rather than fully materialized:

import polars as pl

def read_with_polars(path: str) -> pl.DataFrame:
    """Multi-threaded lazy scan; amount read as Utf8, cast to Int64 cents."""
    lazy = pl.scan_csv(
        path,
        schema_overrides={"amount_major": pl.Utf8, "transaction_id": pl.Utf8},
        try_parse_dates=True,
    ).with_columns(
        (pl.col("amount_major").cast(pl.Decimal(scale=2)) * 100)
        .cast(pl.Int64).alias("amount_cents")
    ).drop("amount_major")
    return lazy.collect(streaming=True)

PyArrow reads the file as record batches, which is the bounded-memory option: you iterate batches instead of holding the whole table. Forcing the column type to string in ConvertOptions keeps money out of a float column:

import pyarrow as pa
from pyarrow import csv as pacsv

def read_with_pyarrow_batches(path: str, block_size: int = 1 << 24):
    """Stream Arrow record batches; yield (batch, cents_list) pairs."""
    convert = pacsv.ConvertOptions(column_types={"amount_major": pa.string()})
    read_opts = pacsv.ReadOptions(block_size=block_size)
    with pacsv.open_csv(path, read_options=read_opts, convert_options=convert) as reader:
        for batch in reader:
            raw = batch.column("amount_major").to_pylist()
            yield batch, [major_units_to_cents(v) for v in raw]

Benchmark results by volume tier

The table below reports what these three readers typically look like relative to one another on a warm local NVMe disk. These figures are illustrative, not measured guarantees — throughput and memory depend entirely on your CPU core count, disk, column mix, and library versions. Treat the shape of the comparison as the takeaway, not the absolute numbers.

The relative throughput bars below restate the 1M-row row of that table. They are drawn to illustrate the typical ordering — pandas slowest, Polars fastest, PyArrow between — and are deliberately labelled as illustrative rather than presented as precise measured values.

Step 3 — Choosing an engine per tier

The recommendation follows the tiers, with money correctness held constant because it is non-negotiable regardless of engine:

• ~100K rows — pandas. At this size throughput differences are milliseconds and irrelevant. Pick pandas for its ecosystem: it hands directly to the validation and reconciliation code in CSV Batch Parsing Workflows, and the extra speed of the others buys nothing you can feel.
• ~1M rows — Polars. This is where multi-threaded parsing earns its keep on a nightly close. Polars reads several times faster than eager pandas and its Decimal cast keeps money exact without a Python-level map.
• ~10M rows — PyArrow batches or Polars streaming. Here the constraint is memory, not speed. If you need a strict, constant memory ceiling — for example a container with a hard limit — iterate PyArrow record batches. If you can afford a larger but still bounded working set and want the richer expression API, use pl.scan_csv(...).collect(streaming=True). Eager pandas at 10M rows is the one combination to avoid; if you must use pandas here, switch to the chunked reader in Optimizing Pandas Chunksize for 10M-Row Fare Files.

Validation & Test Cases

The test that matters most is not “which is fastest” but “do all three produce the same ledger total from the same file.” Cross-engine agreement on the summed cents is the correctness gate; a mismatch means one reader let money become a float.

import csv
import tempfile
from pathlib import Path

def _write_sample(path: Path) -> None:
    rows = [("T1", "C1", "2026-07-03T08:00:00Z", "R12", "2.75"),
            ("T2", "C2", "2026-07-03T08:01:00Z", "R12", "0.10"),
            ("T3", "C3", "2026-07-03T08:02:00Z", "R44", "10.05")]
    with path.open("w", newline="") as fh:
        w = csv.writer(fh)
        w.writerow(["transaction_id", "card_uid", "tap_timestamp", "route_id", "amount_major"])
        w.writerows(rows)

with tempfile.TemporaryDirectory() as d:
    fp = Path(d) / "fares.csv"
    _write_sample(fp)

    # Expected exact total: 275 + 10 + 1005 = 1290 cents
    pd_total = int(read_with_pandas(str(fp))["amount_cents"].sum())
    pl_total = int(read_with_polars(str(fp))["amount_cents"].sum())
    pa_total = sum(c for _, cents in read_with_pyarrow_batches(str(fp)) for c in cents)

    assert pd_total == pl_total == pa_total == 1290

# Edge case: a money string a float would corrupt still round-trips exactly.
assert major_units_to_cents("70.07") == 7007
# Edge case: empty money is refused, never silently zeroed.
try:
    major_units_to_cents("")
    raise AssertionError("expected ValueError on empty money")
except ValueError:
    pass

The normal case asserts all three engines agree on 1290 cents. The edge cases prove the money gate refuses an empty value instead of defaulting to zero, and that 70.07 — a value float64 cannot hold exactly — survives as 7007. If you ever see the three totals diverge, the first suspect is a reader that inferred a float dtype before the string cast could run.

Edge Cases & Honest Benchmarking

A few gotchas separate a defensible benchmark from a misleading one:

1. Warm vs cold cache. The first read of a file pays disk I/O; the second reads from the OS page cache and can look several times faster. State which one you measured, and measure both — a nightly job hits a cold file.
2. Dtype inference skews everything. If you let one engine infer float64 for money and force another to string, you are benchmarking dtype code paths, not the engines. Pin the money column to text in all three, as shown.
3. Peak memory needs a real probe. Report peak resident set via tracemalloc or /usr/bin/time -v, not df.memory_usage(), which misses transient copies during the read.
4. Version drift. Polars and PyArrow move fast; a result on polars 1.0 may not hold on a later release. Always pin and print versions alongside numbers, and never quote a magnitude as if it were universal.

Integration Note

This benchmark chooses the engine; the parent CSV Batch Parsing Workflows is what runs downstream of that choice, applying schema gates, idempotency guards, and the running SHA-256 checksum regardless of which reader produced the rows. Its closest sibling is Optimizing Pandas Chunksize for 10M-Row Fare Files: if this page steers you to pandas at a volume where memory is tight, that guide is how you keep pandas within a fixed ceiling by sizing chunks rather than switching engines. The two together answer “which engine” and “how big a bite,” and both defer money correctness to the same string-to-Decimal rule.

FAQ

Related

• CSV Batch Parsing Workflows — the parent pipeline that runs downstream of whichever engine you pick here
• Optimizing Pandas Chunksize for 10M-Row Fare Files — how to keep pandas within a fixed memory ceiling instead of switching engines
• AFC API Data Extraction — pulling the same fare data over REST for cross-checking flat-file totals
• Schema Validation Pipelines — model-level enforcement of AFC event contracts downstream of the reader
• Smart Card Schema Mapping — where card_uid and the canonical column names originate

Part of CSV Batch Parsing Workflows, within Fare Data Ingestion & GTFS-RT Sync.