Python Doc & Data Automation

Convert a Large CSV to Parquet with Python

A 9 GB transactions export takes four minutes to read into pandas, needs 14 GB of memory to hold, and is re-read from scratch by every analysis. Converting it to Parquet once should fix all three, and the obvious pd.read_csv(path).to_parquet(out) fails on the read with MemoryError. Chunking the read fixes the memory problem but introduces new ones: pyarrow.lib.ArrowInvalid: Schema at index 3 was different when a column's inferred type changes between chunks, and a file that ends up larger than expected because every column was written as a string.

Root Cause

CSV is a row-oriented text format with no types; Parquet is a column-oriented binary format where every column has one declared type for the whole file. Converting means deciding those types, and reading in chunks means pandas infers them per chunk: the first ten thousand rows of customer_ref may be all digits (inferred int64) while a later chunk contains A-1042 (inferred object), and a column of missing values in one chunk becomes float64 while elsewhere it is int64. When each chunk is written as a separate row group, PyArrow requires a consistent schema and raises on the first mismatch. Type inference also decides file size and query speed: a column stored as strings instead of a dictionary-encoded category, or floats instead of integers, can double the output. The conversion has to fix the schema up front rather than let each chunk vote.

Minimal Diagnostic

Sample the file to learn its real types before converting: read a chunk from the beginning, the middle and the end, and compare inferred dtypes.

# pip install "pandas>=2.2" pyarrow
from pathlib import Path
import pandas as pd

SOURCE = Path("in/transactions-2026.csv")

def sample_dtypes(path: Path, rows: int = 50_000, samples: int = 3) -> pd.DataFrame:
    total_bytes = path.stat().st_size
    frames = {}
    frames["head"] = pd.read_csv(path, nrows=rows)
    for i in range(1, samples):
        skip = int(total_bytes / samples * i / max(total_bytes / 1, 1))       # rough row offset by bytes
        frames[f"sample{i}"] = pd.read_csv(path, skiprows=range(1, 1 + i * 2_000_000), nrows=rows)
    report = pd.DataFrame({name: frame.dtypes.astype(str) for name, frame in frames.items()})
    report["consistent"] = report.nunique(axis=1) == 1
    report["nulls_head"] = frames["head"].isna().sum()
    report["distinct_head"] = frames["head"].nunique()
    return report

if __name__ == "__main__":
    print(sample_dtypes(SOURCE).to_string())

                  head   sample1   sample2  consistent  nulls_head  distinct_head
txn_id           int64     int64     int64        True           0          50000
customer_ref     int64    object    object       False           0          31204
amount         float64   float64   float64        True           0          28891
currency        object    object    object        True           0              4
booked_at       object    object    object        True           0          49811
notes           object   float64    object       False       50000              2

customer_ref is numeric at the start of the file and mixed later; notes is entirely empty in the first chunk, so pandas guessed float64. Both would break a naive chunked conversion, and both would be stored badly even if it succeeded.

Declared types instead of inferred ones txn_id is consistently int64 and is declared int64. customer_ref is inferred as int64 in the first chunk and object later, and is declared as string because it contains values such as A-1042. amount is float64 everywhere and is declared as decimal-friendly float64. currency has four distinct values and is declared as dictionary-encoded string. booked_at is text and is declared as timestamp. notes is empty at the start and is declared string. Column Inferred Declared Why txn_id int64 int64 stable customer_ref int64 then object string mixed values currency object dictionary 4 distinct values booked_at object timestamp parse once notes float64 then object string all null at start

Fix: Declare the Schema and Stream Chunks into One Parquet File

Write the schema once, read the CSV in chunks with matching pandas dtypes, and append each chunk as a row group through a single ParquetWriter. Changed lines carry comments.

# pip install "pandas>=2.2" pyarrow
from pathlib import Path
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

SOURCE = Path("in/transactions-2026.csv")
DEST = Path("out/transactions-2026.parquet")

SCHEMA = pa.schema([                                        # changed: one declared type per column
    ("txn_id", pa.int64()),
    ("customer_ref", pa.string()),
    ("amount", pa.float64()),
    ("currency", pa.dictionary(pa.int8(), pa.string())),    # changed: few distinct values
    ("booked_at", pa.timestamp("us")),
    ("notes", pa.string()),
])
READ_DTYPES = {"txn_id": "int64", "customer_ref": "string", "amount": "float64",
               "currency": "category", "notes": "string"}   # changed: stop pandas guessing

def convert(src: Path, dest: Path, chunksize: int = 500_000, compression: str = "zstd") -> dict:
    dest.parent.mkdir(parents=True, exist_ok=True)
    rows = 0
    writer = None
    try:
        reader = pd.read_csv(src, chunksize=chunksize, dtype=READ_DTYPES,
                             parse_dates=["booked_at"])      # changed: parse once, not per query
        for chunk in reader:
            table = pa.Table.from_pandas(chunk, schema=SCHEMA, preserve_index=False)  # changed: enforce schema
            if writer is None:
                writer = pq.ParquetWriter(dest, SCHEMA, compression=compression,
                                          use_dictionary=["currency"], write_statistics=True)
            writer.write_table(table)                        # changed: one row group per chunk
            rows += len(chunk)
    finally:
        if writer is not None:
            writer.close()
    return {"rows": rows, "csv_mb": round(src.stat().st_size / 1e6, 1),
            "parquet_mb": round(dest.stat().st_size / 1e6, 1)}

if __name__ == "__main__":
    print(convert(SOURCE, DEST))

{'rows': 41823119, 'csv_mb': 9120.4, 'parquet_mb': 611.8}

pa.Table.from_pandas(..., schema=SCHEMA) converts each chunk to the declared types and raises immediately if a value cannot be converted — the error names the column and the offending value instead of appearing three hours later as a wrong total. Chunk size trades memory for row-group size: 500,000 rows of this shape uses roughly 1 GB while producing row groups large enough for efficient reading. Dictionary encoding on low-cardinality columns such as currency is where much of the size reduction comes from.

File size by format and compression The same 41.8 million transaction rows occupy 9,120 megabytes as raw CSV, 2,310 megabytes as gzipped CSV, 842 megabytes as Parquet with snappy compression and 612 megabytes as Parquet with zstd compression, while remaining column-selectable and typed. 41.8 million rows, six columns CSV 9120 MB CSV gzip 2310 MB Parquet snappy 842 MB Parquet zstd 612 MB Unlike gzipped CSV, Parquet can be read column by column and filtered without decompressing everything

Variant Fix 1: Let PyArrow Do the Streaming

PyArrow can read the CSV itself in blocks, which avoids pandas entirely and is usually faster:

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

def convert_with_arrow(src: Path, dest: Path, compression: str = "zstd") -> int:
    convert_options = pv.ConvertOptions(
        column_types={"customer_ref": pa.string(), "notes": pa.string(),
                      "currency": pa.dictionary(pa.int8(), pa.string())},   # declared types
        timestamp_parsers=["%Y-%m-%d %H:%M:%S", "%Y-%m-%d"],
        strings_can_be_null=True, null_values=["", "NA", "NULL", "n/a"],
    )
    read_options = pv.ReadOptions(block_size=64 << 20)                       # 64 MB blocks
    rows = 0
    with pv.open_csv(src, read_options=read_options, convert_options=convert_options) as reader:
        writer = None
        try:
            for batch in reader:
                table = pa.Table.from_batches([batch])
                if writer is None:
                    writer = pq.ParquetWriter(dest, table.schema, compression=compression)
                writer.write_table(table)
                rows += batch.num_rows
        finally:
            if writer is not None:
                writer.close()
    return rows

PyArrow infers types from the first block unless told otherwise, so column_types plays the same role as the pandas dtype map. It is typically two to four times faster than the pandas path on wide files and uses less memory, at the cost of pandas-specific conveniences such as parse_dates heuristics.

Variant Fix 2: Partition by Date for Faster Queries

A single large Parquet file is fine for full scans; partitioning by a date column lets readers skip irrelevant data entirely:

# pip install "pandas>=2.2" pyarrow
from pathlib import Path
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

def convert_partitioned(src: Path, dest_dir: Path, chunksize: int = 500_000) -> int:
    dest_dir.mkdir(parents=True, exist_ok=True)
    rows = 0
    for chunk in pd.read_csv(src, chunksize=chunksize, dtype=READ_DTYPES, parse_dates=["booked_at"]):
        chunk = chunk.assign(booked_month=chunk["booked_at"].dt.strftime("%Y-%m"))
        table = pa.Table.from_pandas(chunk, preserve_index=False)
        pq.write_to_dataset(table, root_path=str(dest_dir), partition_cols=["booked_month"],
                            compression="zstd", existing_data_behavior="overwrite_or_ignore")
        rows += len(chunk)
    return rows

Each partition becomes a folder such as booked_month=2026-09/, and readers that filter on booked_month touch only those folders. Keep partitions reasonably large — tens of megabytes at least; a partition per day on a small dataset produces thousands of tiny files that are slower to read than one file. Querying the result without loading it all is covered in query large CSV files with DuckDB, which reads Parquet natively.

Conversion pipeline The CSV is sampled at several offsets to reveal inconsistent inferred dtypes. A schema is declared once for all columns, including dictionary encoding for low-cardinality columns and timestamps for date columns. The CSV is read in chunks with matching pandas dtypes, each chunk is converted to an Arrow table against the schema, and written as a row group to one Parquet file. Row counts and column sums are then compared between the CSV and the Parquet file. Sample dtypes head, middle, end Declare schema types + dictionary Read chunks fixed dtypes Write row groups one writer Verify counts and sums

Verification

Compare the Parquet file with the CSV on row count, per-column null counts and numeric sums, reading both in a streaming fashion so the check itself does not need the memory the conversion avoided.

# pip install "pandas>=2.2" pyarrow
from pathlib import Path
import pandas as pd
import pyarrow.parquet as pq

def verify_parquet(csv_path: Path, parquet_path: Path, numeric_cols=("amount",),
                   chunksize: int = 500_000) -> None:
    csv_rows, csv_sums, csv_nulls = 0, {c: 0.0 for c in numeric_cols}, {}
    for chunk in pd.read_csv(csv_path, chunksize=chunksize, dtype=READ_DTYPES, parse_dates=["booked_at"]):
        csv_rows += len(chunk)
        for col in numeric_cols:
            csv_sums[col] += float(chunk[col].sum())
        for col in chunk.columns:
            csv_nulls[col] = csv_nulls.get(col, 0) + int(chunk[col].isna().sum())
    pf = pq.ParquetFile(parquet_path)
    assert pf.metadata.num_rows == csv_rows, f"{pf.metadata.num_rows} rows in Parquet, {csv_rows} in CSV"
    table_sums = {c: 0.0 for c in numeric_cols}
    parquet_nulls: dict[str, int] = {}
    for batch in pf.iter_batches(batch_size=chunksize):
        frame = batch.to_pandas()
        for col in numeric_cols:
            table_sums[col] += float(frame[col].sum())
        for col in frame.columns:
            parquet_nulls[col] = parquet_nulls.get(col, 0) + int(frame[col].isna().sum())
    for col in numeric_cols:
        assert abs(table_sums[col] - csv_sums[col]) < 0.01, f"{col}: sums differ"
    differing = {c for c in csv_nulls if csv_nulls[c] != parquet_nulls.get(c, 0)}
    assert not differing, f"null counts differ for {sorted(differing)}"
    print(f"verified {csv_rows:,} rows, sums and null counts match; "
          f"{pf.metadata.num_row_groups} row groups, {parquet_path.stat().st_size / 1e6:.0f} MB")

Null counts are the check that catches a type conversion silently turning unparseable values into NaN — a column of dates where a handful of rows held n/a loses those rows' values, and only the null count reveals it.

FAQ

Which compression should I choose?zstd for the best size at similar speed to snappy; snappy where readers are older or CPU is scarce; gzip only for compatibility with legacy tools.

Can Excel open Parquet? Current Excel versions can import Parquet through Power Query. For general sharing, keep the Parquet as the working format and export a filtered CSV or xlsx for people.

Does Parquet preserve pandas dtypes exactly? Close, but not identically: pandas nullable integers, categoricals and time zones map to Arrow equivalents. Round-trip a sample and compare dtypes if exact types matter downstream.

Should I delete the CSV afterwards? Keep the original until the verification has run and the Parquet has been used in anger. Then archive the CSV compressed rather than deleting it, unless it is reproducible from a source system.

Part of Handling Large CSV Files with Chunking.