Exporting Scraped Data to CSV and Parquet

Choosing an export format looks like a matter of taste until the dataset reaches a few million rows, and this article — part of Storing and Exporting Scraped Data — puts numbers against the two formats scrapers reach for most, along with the streaming and partitioning patterns that keep either of them from exhausting memory.

For anything you will query, join, or load repeatedly, Parquet wins on every axis that matters: it is roughly seven to twelve times smaller, it preserves types so a price stays a decimal and a timestamp stays a timestamp, and a reader can load one column without decoding the rest of the file. CSV remains the right answer for exactly one job — handing a finished extract to a person who will open it in a spreadsheet — and for that job its total absence of tooling requirements is the feature.

What CSV Actually Costs

CSV has no schema. Every value is a sequence of characters, and every reader has to guess what those characters mean. pandas.read_csv guesses well and guesses wrong in the same predictable ways on every scraped dataset: a product code of 0071 becomes the integer 71, an ISBN of 9780306406157 becomes a float once one row in a million is blank, and a column of prices where a handful of rows contain N/A becomes object dtype holding a mixture of strings and floats.

The fixes are all manual and all have to be repeated by every consumer of the file. Passing dtype={"sku": "string"} protects one column in one script; the next person to open the file gets the original behaviour back.

Quoting is the second cost. Scraped text contains commas, newlines, and quote characters, because it came from a page written by humans. Python's csv module handles this correctly as long as you use it — writer.writerow quotes fields containing the delimiter and doubles embedded quotes — but the moment a field contains a literal \r\n, the file has records spanning multiple physical lines and every naive line-based tool downstream breaks. Setting newline="" on the file object is not optional: without it, the module's \r\n line terminator meets the platform's newline translation and Windows produces a blank line between every record.

Encoding is the third. CSV carries no encoding declaration, so a file written as UTF-8 and opened in a spreadsheet configured for a legacy code page shows mojibake. Writing a UTF-8 byte-order mark with encoding="utf-8-sig" is the standard workaround for that specific audience, and it is also a small landmine for anything that reads the file programmatically without expecting the BOM. The same class of problem when reading pages is covered in Fixing Common Unicode Errors in Python Scraping.

Finally, there is size. A CSV stores 2026-07-14T09:31:00Z as twenty bytes on every single row, and the string "In stock" in full on every row where it appears.

Those figures come from one million rows of scraped product data — url, title, category, price, currency, availability string, and a scrape timestamp — written four ways from the same pyarrow table. The interesting comparison is not CSV against Parquet but gzipped CSV against Parquet: 96 MB versus 34 MB, and the gzipped file must be decompressed in its entirety before a reader can look at a single value, while the Parquet file can answer "how many rows have price above 50" by reading a few kilobytes of column statistics.

Why Parquet Is Smaller and Faster

Parquet writes each column as a contiguous chunk, then compresses each chunk independently. That is the whole trick, and it produces three compounding benefits.

Column chunks compress far better than rows do, because adjacent values in a column are similar: a category column with 40 distinct values across a million rows becomes a dictionary of 40 strings plus a million small integers. A timestamp column collected during one crawl is a tight band of nearly identical values, which delta encoding reduces to a base plus a million small offsets. Interleaved in a CSV row, those values sit next to unrelated text and no encoder can exploit them.

Reading one column means reading one region of the file. pd.read_parquet(path, columns=["url", "price"]) transfers only those two chunks; the equivalent pd.read_csv(path, usecols=["url", "price"]) still reads and tokenises every byte, discarding the rest after parsing. On the million-row file above, the column-selective Parquet read is under a tenth of a second against roughly three seconds for the CSV.

Predicate pushdown is the third benefit. Every column chunk carries min and max statistics in the file footer. A filter of price > 500 lets the reader skip any row group whose maximum price is 500 or below without decompressing it. That only helps when the data has some locality — a fully shuffled dataset has every row group spanning the full range — which is one reason writing in scrape order, or sorting before writing, is worth thinking about.

Types are the benefit that shows up as fewer bugs rather than fewer seconds. Parquet stores a schema, so price comes back as double or decimal128, in_stock as bool, and scraped_at as a timezone-aware timestamp, on every read, in every tool. Normalising those values before they are written is a separate job — see Normalizing Prices, Dates and Units — but only Parquet preserves the result.

Writing Both Formats

The pandas path is two lines and is right for a dataset that fits in memory.

import pandas as pd

rows = [
    {"url": "https://books.toscrape.com/catalogue/a-light-in-the-attic_1000/index.html",
     "title": "A Light in the Attic", "price": 51.77, "in_stock": True},
    {"url": "https://books.toscrape.com/catalogue/tipping-the-velvet_999/index.html",
     "title": "Tipping the Velvet", "price": 53.74, "in_stock": True},
]

df = pd.DataFrame(rows)
df["scraped_at"] = pd.Timestamp.utcnow()

# Spreadsheet hand-off: BOM so Excel detects UTF-8, no index column.
df.to_csv("books.csv", index=False, encoding="utf-8-sig")

# Analytical copy: typed, compressed, one file.
df.to_parquet("books.parquet", index=False, compression="zstd", engine="pyarrow")

back = pd.read_parquet("books.parquet", columns=["title", "price"])
print(back.dtypes)

compression="zstd" is worth setting explicitly. The pyarrow default is snappy, which decodes marginally faster and produces files around 70% larger; for scraped data that will be read far less often than it is written, zstd at its default level 1 is the better trade. index=False matters for both formats — a pandas RangeIndex written into the file becomes a meaningless __index_level_0__ column that every downstream reader has to ignore.

For a crawl whose output does not fit in memory, pyarrow.parquet.ParquetWriter appends row groups to an open file.

from collections.abc import Iterator, Iterable

import pyarrow as pa
import pyarrow.parquet as pq

SCHEMA = pa.schema([
    ("url", pa.string()),
    ("title", pa.string()),
    ("price", pa.float64()),
    ("in_stock", pa.bool_()),
    ("scraped_at", pa.timestamp("us", tz="UTC")),
])

def batched(records: Iterable[dict], size: int) -> Iterator[list[dict]]:
    chunk: list[dict] = []
    for record in records:
        chunk.append(record)
        if len(chunk) >= size:
            yield chunk
            chunk = []
    if chunk:
        yield chunk

def stream_to_parquet(records: Iterable[dict], path: str, rows_per_group: int = 50_000) -> int:
    """Write an arbitrarily long stream of dicts without holding it in memory."""
    total = 0
    writer = pq.ParquetWriter(path, SCHEMA, compression="zstd")
    try:
        for chunk in batched(records, rows_per_group):
            table = pa.Table.from_pylist(chunk, schema=SCHEMA)
            writer.write_table(table)
            total += len(chunk)
    finally:
        writer.close()          # writes the footer — without it the file is unreadable
    return total

The finally is the important line. A Parquet file's schema and row-group index live in a footer written by close(). A process killed mid-crawl leaves a file with valid row groups and no footer, which every reader will reject as corrupt. Streaming to disk protects you from losing work to a crash only if the writer is closed, so a long crawl should either close and reopen periodically or accept that the current file is provisional.

CSV has the opposite property, and it is the one genuine advantage of the format at scale: an append-mode CSV is readable at every instant, and a truncated one loses only the final partial line. That makes it a reasonable choice for a raw landing file that a later step converts to Parquet.

import csv

FIELDS = ["url", "title", "price", "in_stock", "scraped_at"]

def append_rows(rows: list[dict], path: str) -> None:
    exists = False
    try:
        with open(path, "r", encoding="utf-8") as probe:
            exists = bool(probe.readline())
    except FileNotFoundError:
        pass
    with open(path, "a", newline="", encoding="utf-8") as fh:
        writer = csv.DictWriter(fh, fieldnames=FIELDS, extrasaction="ignore")
        if not exists:
            writer.writeheader()
        writer.writerows(rows)

extrasaction="ignore" prevents an extra key in one record from raising mid-write and leaving a half-written line; newline="" prevents the blank-line-per-record problem on Windows.

Partitioning by Scrape Date

Scrapers produce time series whether or not anyone planned for it. Writing every run into a directory keyed by date turns "what did this look like last Tuesday" into a directory listing rather than a full scan, and lets a reader skip entire days without opening a file.

import datetime as dt

import pyarrow as pa
import pyarrow.parquet as pq

def write_partition(records: list[dict], root: str = "data/products") -> str:
    today = dt.date.today()
    table = pa.Table.from_pylist(records, schema=SCHEMA)
    table = table.append_column(
        "scrape_date", pa.array([today] * table.num_rows, type=pa.date32())
    )
    pq.write_to_dataset(
        table,
        root_path=root,
        partition_cols=["scrape_date"],
        compression="zstd",
        existing_data_behavior="overwrite_or_ignore",
    )
    return f"{root}/scrape_date={today.isoformat()}"

# Later, read only two days without touching the rest of the dataset.
recent = pq.read_table(
    "data/products",
    filters=[("scrape_date", ">=", dt.date.today() - dt.timedelta(days=1))],
)
print(recent.num_rows)

The layout this produces — data/products/scrape_date=2026-08-01/part-0.parquet — is the Hive convention that pandas, DuckDB, Spark, and every cloud query engine understand without configuration. The partition column is stored in the directory name rather than in the files, so it costs nothing on disk.

Partition granularity is the one decision to get right. Daily partitions on a crawl producing a few thousand rows a day generate thousands of small files, and small Parquet files are inefficient: each one carries a footer, and each one is a separate open on object storage. Under roughly 100 MB per day, partition monthly instead. Over a gigabyte per day, add a second level such as a category or a source hostname.

Edge Cases and Caveats

• Nested fields do not survive CSV. A record with a list of image URLs becomes a mangled string with embedded commas. Parquet stores real list and struct types; if you must use CSV, flatten first, as in Flattening Nested JSON with pandas.
• Money should not be a float. 0.1 + 0.2 is not 0.3 in binary floating point, and summed over a million prices the drift is visible. Use pa.decimal128(12, 2) in the schema for anything financial.
• Schema drift breaks appends. ParquetWriter rejects a table whose schema differs from the one it opened with, including a column that arrived as int64 in one batch and double in the next because a null crept in. Declare the schema explicitly, as above, rather than inferring it from the first batch.
• float("nan") and None are not the same in Parquet. pandas conflates them for numeric columns; pyarrow does not. Reading back a column written from pandas can therefore produce nulls where the scraper wrote NaN, which changes the result of a count.
• Timezones are preserved only if you set them. A naive datetime written to Parquet comes back naive, and two crawls run from different hosts then compare incorrectly. Store UTC with an explicit tz="UTC" in the schema.
• A Scrapy feed export can write Parquet. The built-in exporters cover CSV, JSON, JSON Lines and XML; Parquet needs either a custom exporter or a pipeline that buffers and writes, which is the shape described in Writing Scrapy Item Pipelines.
• CSV is still the fastest thing to eyeball. head -5 out.csv works everywhere; the Parquet equivalent needs a Python session or a CLI tool. For debugging a scraper mid-development that is a real advantage.

Frequently Asked Questions

Is Parquet worth it for only a hundred thousand rows? Usually yes, because the benefit that matters at that size is type preservation rather than compression. A hundred thousand rows is small enough that read speed is irrelevant, but large enough that a silently coerced product code costs an afternoon. The exception is a file destined for a spreadsheet, where CSV is the only sensible answer.

Can I append to an existing Parquet file? Not to a closed one — the footer is written at the end and cannot be extended. The equivalent operations are keeping the ParquetWriter open for the life of the crawl, or writing each run as a new file inside a partitioned dataset directory, which readers then treat as a single logical table.

Why is my Parquet file bigger than the CSV? Almost always because it holds very few rows. Parquet's footer, schema and per-column metadata are a fixed overhead of a few kilobytes, so a file of two hundred rows can easily be larger than its CSV equivalent. The crossover is somewhere around a few thousand rows; below that the format choice should be made on typing, not size.

Does Parquet need pandas? No. pyarrow reads and writes it directly, and polars and duckdb both read it natively with lower memory overhead than pandas. Installing pyarrow alone is enough — pandas simply calls into it for to_parquet and read_parquet.

Related

• Storing and Exporting Scraped Data — the parent topic on picking a sink and writing incrementally.
• Saving Scraped Data to PostgreSQL — when a queryable database beats files entirely.
• Writing Scrapy Item Pipelines — where the buffering and flushing logic belongs inside a Scrapy project.
• Flattening Nested JSON with pandas — turning nested API payloads into the flat rows these writers expect.