Best Python Libraries for CSV Parsing

# pip install pandas polars pyarrow psutil
import csv
import time
import tracemalloc
from pathlib import Path

import pandas as pd
import polars as pl
import pyarrow.csv as pa_csv

DATA = Path("sample.csv")  # replace with your file

def measure(label: str, fn):
    tracemalloc.start()
    t0 = time.perf_counter()
    result = fn()
    elapsed = time.perf_counter() - t0
    _, peak = tracemalloc.get_traced_memory()
    tracemalloc.stop()
    print(f"{label:20s}  {elapsed:.2f}s  peak={peak / 1_048_576:.1f} MB")
    return result

# stdlib csv — row-by-row, no type inference
def load_csv_stdlib():
    try:
        with DATA.open(newline="", encoding="utf-8") as fh:
            return list(csv.DictReader(fh))
    except OSError as exc:
        raise SystemExit(f"Cannot open {DATA}: {exc}") from exc

# pandas — eager, vectorised
def load_pandas():
    try:
        return pd.read_csv(DATA, low_memory=False)
    except (OSError, pd.errors.ParserError) as exc:
        raise SystemExit(f"pandas failed: {exc}") from exc

# polars — lazy, multi-threaded
def load_polars():
    try:
        return pl.scan_csv(DATA).collect()
    except Exception as exc:
        raise SystemExit(f"polars failed: {exc}") from exc

# pyarrow — columnar Arrow table
def load_pyarrow():
    try:
        return pa_csv.read_csv(DATA)
    except (OSError, Exception) as exc:
        raise SystemExit(f"pyarrow failed: {exc}") from exc

measure("csv (stdlib)", load_csv_stdlib)
measure("pandas",       load_pandas)
measure("polars",       load_polars)
measure("pyarrow",      load_pyarrow)

# pip install chardet  (stdlib csv needs no install)
import csv
from pathlib import Path

DATA = Path("transactions.csv")

def stream_csv(path: Path):
    """Yield rows one at a time — O(1) memory regardless of file size."""
    try:
        with path.open(newline="", encoding="utf-8-sig", errors="replace") as fh:
            # Sniff the dialect from the first 4 KB to handle ; and \t delimiters
            sample = fh.read(4096)
            fh.seek(0)
            try:
                dialect = csv.Sniffer().sniff(sample, delimiters=",;\t|")
            except csv.Error:
                dialect = csv.excel  # safe fallback
            reader = csv.DictReader(fh, dialect=dialect)
            for row in reader:
                yield row
    except OSError as exc:
        raise SystemExit(f"Cannot open {path}: {exc}") from exc

for record in stream_csv(DATA):
    amount = float(record.get("amount", 0) or 0)  # manual cast
    print(record["id"], amount)

# pip install pandas
import pandas as pd
from pathlib import Path

DATA = Path("sales_q1.csv")

try:
    df = pd.read_csv(
        DATA,
        encoding="utf-8-sig",   # handles Excel BOM
        on_bad_lines="warn",     # log malformed rows, don't crash
        low_memory=False,        # avoid mixed-type column warnings
        parse_dates=["order_date"],
    )
except (OSError, pd.errors.ParserError) as exc:
    raise SystemExit(f"Failed to load {DATA}: {exc}") from exc

# Verify the load looked right
print(df.dtypes)
print(df.shape)
assert df["order_id"].notna().all(), "order_id column has unexpected nulls"

# pip install polars
import polars as pl
from pathlib import Path

DATA = Path("events_2025.csv")

try:
    # scan_csv is lazy — no I/O happens here
    lf = pl.scan_csv(
        DATA,
        infer_schema_length=10_000,  # sample more rows for schema
        ignore_errors=True,          # skip structurally malformed rows
        try_parse_dates=True,
    )

    # Push filters before collect so polars skips irrelevant rows
    result = (
        lf
        .filter(pl.col("status") == "COMPLETE")
        .select(["event_id", "user_id", "amount", "status"])
        .collect(engine="streaming")   # out-of-core for large files
    )
except Exception as exc:
    raise SystemExit(f"polars scan failed: {exc}") from exc

print(result.shape)
print(result.head())

# pip install pyarrow
import pyarrow.csv as pa_csv
import pyarrow.parquet as pq
from pathlib import Path

DATA    = Path("large_export.csv")
OUTPUT  = Path("large_export.parquet")

try:
    # ConvertOptions lets you control null tokens and type overrides
    convert_opts = pa_csv.ConvertOptions(
        null_values=["", "NULL", "N/A", "n/a"],
        strings_can_be_null=True,
    )
    read_opts = pa_csv.ReadOptions(
        block_size=32 * 1024 * 1024,  # 32 MB read blocks
    )
    table = pa_csv.read_csv(
        DATA,
        read_options=read_opts,
        convert_options=convert_opts,
    )
except (OSError, Exception) as exc:
    raise SystemExit(f"pyarrow read failed: {exc}") from exc

# Write directly to Parquet — no pandas round-trip needed
try:
    pq.write_table(table, OUTPUT, compression="snappy")
    print(f"Wrote {OUTPUT} ({OUTPUT.stat().st_size / 1_048_576:.1f} MB)")
except OSError as exc:
    raise SystemExit(f"Parquet write failed: {exc}") from exc

# Or query with DuckDB without copying memory
try:
    import duckdb
    con = duckdb.connect()
    con.register("events", table)
    print(con.execute("SELECT status, COUNT(*) FROM events GROUP BY 1").df())
except ImportError:
    print("duckdb not installed — skipping query example")

# pip install pandas
import pandas as pd
from pathlib import Path

DATA = Path("huge_report.csv")

totals = {}
try:
    for chunk in pd.read_csv(DATA, chunksize=200_000, low_memory=False):
        for key, val in chunk.groupby("region")["revenue"].sum().items():
            totals[key] = totals.get(key, 0) + val
except (OSError, pd.errors.ParserError) as exc:
    raise SystemExit(f"Chunked read failed: {exc}") from exc

print(totals)

# pip install polars
import polars as pl
from pathlib import Path

DATA = Path("huge_report.csv")

try:
    result = (
        pl.scan_csv(DATA, ignore_errors=True)
        .group_by("region")
        .agg(pl.col("revenue").sum())
        .collect(engine="streaming")
    )
    print(result.sort("revenue", descending=True))
except Exception as exc:
    raise SystemExit(f"polars streaming aggregate failed: {exc}") from exc

# pip install pandas polars pyarrow
# Run whichever block matches your chosen library

# --- pandas ---
import pandas as pd
from pathlib import Path

df = pd.read_csv(Path("output.csv"))

assert not df.empty, "DataFrame is empty — check delimiter or encoding"
assert df.isnull().mean().max() < 0.5, "More than 50% nulls in at least one column"
assert df.select_dtypes("object").shape[1] < df.shape[1], (
    "All columns are object dtype — type inference may have failed"
)
print("pandas load verified:", df.shape)

# --- polars ---
import polars as pl

df_pl = pl.read_csv(Path("output.csv"))
assert df_pl.height > 0
assert df_pl.null_count().row(0) != tuple([df_pl.height] * df_pl.width), (
    "All-null column detected"
)
print("polars load verified:", df_pl.shape)

# --- pyarrow ---
import pyarrow.csv as pa_csv

tbl = pa_csv.read_csv(Path("output.csv"))
assert tbl.num_rows > 0
assert tbl.num_columns > 1
print("pyarrow load verified:", tbl.shape)