Python File Formats for Data Engineers: Reading and Writing CSV, JSON, Parquet, and Excel with pandas, pyarrow, and openpyxl — Every Parameter, Pattern, and Pitfall

Why File Formats Matter
Reading CSV Files
Writing CSV Files
Reading JSON Files
Writing JSON Files
Reading Parquet Files
Writing Parquet Files
Reading Excel Files
Writing Excel Files
Converting Between Formats
Working with Large Files — Memory Optimization
Schema Validation and Type Safety
Real-World ETL Pattern: CSV to Partitioned Parquet
Common Mistakes
Interview Questions
Wrapping Up

Our File Handling post covered Python’s built-in file operations — open(), read(), write(), and context managers. This post goes further into the data file formats you work with every day as a data engineer: CSV, JSON, Parquet, and Excel. We use pandas for tabular operations, pyarrow for high-performance Parquet I/O, and openpyxl for Excel-specific features.

Analogy — A warehouse receiving dock. Shipments arrive in different packaging: some in cardboard boxes (CSV — simple, universal, anyone can open them), some in labeled plastic bins (JSON — structured, nested, self-describing), some in shrink-wrapped pallets (Parquet — compressed, optimized for bulk storage), and some in display cases with compartments (Excel — multiple sheets, formatting, formulas). The receiving dock (your Python script) needs to know how to unpack each type, inspect the contents, and repackage them for the warehouse shelves (your data lake). This post teaches you every unpacking and repacking technique.

Why File Formats Matter

Choosing the wrong file format is like shipping a truckload of bricks in individual gift boxes — technically possible, but wasteful. A 1 GB CSV file might compress to 200 MB as Parquet. A nested API response stored as flat CSV loses its hierarchy. An Excel report sent to a Spark cluster causes the entire pipeline to slow down because Spark cannot read Excel efficiently.

As a data engineer, you encounter all four formats daily. CSV arrives from legacy systems, FTP drops, and manual uploads. JSON comes from APIs, webhooks, and event streams. Parquet is what you store in your data lake (Delta tables are Parquet under the hood). Excel comes from business users, finance teams, and anyone who lives in spreadsheets. Knowing how to read, write, convert, and optimize each format is a non-negotiable skill.

Format	Human Readable?	Columnar?	Schema?	Compression	Best For
CSV	Yes	No (row-based)	No	None (raw text)	Data exchange, legacy systems, quick exports
JSON	Yes	No	Self-describing	None (raw text)	API responses, nested/hierarchical data
Parquet	No (binary)	Yes	Embedded	Snappy, Gzip, Zstd	Data lakes, analytics, Spark/Fabric
Excel	Yes (with app)	No	Headers only	Zip-based (xlsx)	Business reports, finance, manual review

Reading CSV Files

CSV (Comma-Separated Values) is the oldest and most universal data format. Every database can export it, every tool can import it, and every text editor can open it. But that simplicity hides complexity: no schema, no types, no compression, and endless encoding issues.

Analogy — A handwritten grocery list. Anyone can read it (human-readable), but there is no structure — are those commas separating items or embedded in item names? Is “1,000” the number one thousand or two separate values? CSV has the same ambiguities, and pandas provides parameters to handle every edge case.

Basic CSV Reading

The simplest read_csv() call takes a file path and returns a DataFrame. Pandas infers column types, parses headers from the first row, and uses commas as delimiters by default.

import pandas as pd

# Basic read — pandas infers everything
df = pd.read_csv("sales.csv")
print(df.head())
print(df.dtypes)       # Check what pandas inferred
print(df.shape)        # (rows, columns)

Essential Parameters

In practice, you rarely use bare read_csv(). Real-world CSV files have custom delimiters, missing headers, date columns that need parsing, encoding issues, and columns you do not need. Here are the parameters you use most often, grouped by purpose.

# Delimiter — TSV files, pipe-delimited, semicolon (European CSVs)
df = pd.read_csv("data.tsv", sep="\t")               # Tab-separated
df = pd.read_csv("data.csv", sep="|")                 # Pipe-delimited
df = pd.read_csv("european.csv", sep=";", decimal=",") # European: ; separator, , decimal

# Headers — when the first row is NOT headers, or you want custom names
df = pd.read_csv("no_header.csv", header=None)                        # No header row
df = pd.read_csv("no_header.csv", header=None, names=["id", "name", "amount"])  # Custom names
df = pd.read_csv("multi_header.csv", header=[0, 1])                   # Multi-level headers

# Column selection — read only what you need (saves memory)
df = pd.read_csv("wide_file.csv", usecols=["id", "name", "amount"])   # By name
df = pd.read_csv("wide_file.csv", usecols=[0, 3, 5])                  # By position

# Date parsing — convert date strings to datetime objects during read
df = pd.read_csv("orders.csv", parse_dates=["order_date", "ship_date"])
df = pd.read_csv("orders.csv", parse_dates=["order_date"],
                 date_format="%d/%m/%Y")              # Custom date format

# Data types — force specific types to avoid inference mistakes
df = pd.read_csv("data.csv", dtype={"zip_code": str, "quantity": int})
# Without dtype=str, zip code "07102" becomes integer 7102 (leading zero lost!)

# Missing values — define what counts as "missing"
df = pd.read_csv("data.csv", na_values=["N/A", "NULL", "-", ""])

# Encoding — handle non-UTF-8 files
df = pd.read_csv("legacy.csv", encoding="latin-1")     # Windows legacy files
df = pd.read_csv("japanese.csv", encoding="shift_jis")  # Japanese encoding

# Skip rows — skip metadata rows at the top or bottom
df = pd.read_csv("report.csv", skiprows=3)              # Skip first 3 rows
df = pd.read_csv("report.csv", skipfooter=2, engine="python")  # Skip last 2 rows

# Row limit — read only a sample
df = pd.read_csv("huge.csv", nrows=1000)                # First 1000 rows only

Handling Large CSV Files with Chunking

When a CSV file is too large to fit in memory, pandas can read it in chunks. Each chunk is a DataFrame with chunksize rows. You process each chunk independently and combine results — this is the pandas equivalent of streaming.

Analogy — Eating a pizza. You do not stuff the entire pizza in your mouth at once. You eat it slice by slice (chunk by chunk). Each slice is manageable, and you still finish the whole pizza.

# Process a 10 GB CSV file in 100,000-row chunks
results = []
for chunk in pd.read_csv("huge_file.csv", chunksize=100_000):
    # Process each chunk independently
    filtered = chunk[chunk["amount"] > 100]
    results.append(filtered)

# Combine all processed chunks into one DataFrame
df = pd.concat(results, ignore_index=True)
print(f"Processed {len(df)} qualifying rows from entire file")

# Memory-efficient aggregation — never load the full file
total = 0
row_count = 0
for chunk in pd.read_csv("huge_file.csv", chunksize=50_000,
                          usecols=["amount"]):
    total += chunk["amount"].sum()
    row_count += len(chunk)
print(f"Grand total: {total} across {row_count} rows")

Writing CSV Files

Writing CSV is straightforward but has important parameters that affect downstream consumers. The most common mistake is including the pandas index as an extra column — always set index=False unless you specifically need it.

# Basic write — ALWAYS set index=False
df.to_csv("output.csv", index=False)

# Custom delimiter and encoding
df.to_csv("output.tsv", sep="\t", index=False, encoding="utf-8-sig")
# utf-8-sig adds a BOM (Byte Order Mark) — Excel opens it correctly

# Control quoting — useful when values contain commas
import csv
df.to_csv("output.csv", index=False, quoting=csv.QUOTE_NONNUMERIC)

# Write specific columns only
df.to_csv("output.csv", index=False, columns=["id", "name", "total"])

# Date formatting — control how dates appear in the CSV
df.to_csv("output.csv", index=False, date_format="%Y-%m-%d")

# Handle missing values — write a custom string for NaN
df.to_csv("output.csv", index=False, na_rep="NULL")

# Compression — write directly as gzip (saves disk space)
df.to_csv("output.csv.gz", index=False, compression="gzip")

Reading JSON Files

JSON (JavaScript Object Notation) is the language of APIs. Every REST endpoint returns JSON. Event streaming platforms (Kafka, Event Hub) send JSON payloads. Configuration files use JSON. Unlike CSV, JSON is self-describing — field names are embedded in every record — and it supports nesting — objects within objects, arrays within arrays.

Analogy — A filing cabinet with labeled folders. Each folder (JSON object) has a label on the tab (key) and contents inside (value). Some folders contain other folders (nesting). CSV is like a flat spreadsheet taped to the outside of the cabinet — no hierarchy, no labels beyond the header row.

Flat JSON (Array of Records)

The simplest JSON structure is an array of flat objects — each object has the same keys. This maps directly to a DataFrame.

# File: sales.json
# [
#   {"id": 1, "product": "Widget", "amount": 29.99},
#   {"id": 2, "product": "Gadget", "amount": 49.99}
# ]

df = pd.read_json("sales.json")
print(df)
#    id  product  amount
# 0   1   Widget   29.99
# 1   2   Gadget   49.99

Nested JSON

Real-world API responses are almost never flat. They contain nested objects, arrays, and mixed types. pd.json_normalize() flattens nested structures into a tabular DataFrame.

import json

# Nested JSON from an API response
data = {
    "order_id": 101,
    "customer": {
        "name": "Alice",
        "address": {
            "city": "Toronto",
            "province": "ON"
        }
    },
    "items": [
        {"product": "Widget", "qty": 2, "price": 29.99},
        {"product": "Gadget", "qty": 1, "price": 49.99}
    ]
}

# Flatten the nested structure
df = pd.json_normalize(data, record_path="items",
                       meta=["order_id", ["customer", "name"],
                             ["customer", "address", "city"]])
print(df)
#   product  qty  price  order_id customer.name customer.address.city
# 0  Widget    2  29.99       101         Alice               Toronto
# 1  Gadget    1  49.99       101         Alice               Toronto

JSON Lines (JSONL / NDJSON)

JSON Lines is one JSON object per line — no wrapping array, no commas between records. This format is ideal for streaming and append-only logging because you can append new records without modifying the file structure.

# File: events.jsonl
# {"event": "click", "user": "alice", "ts": "2026-07-19T10:00:00"}
# {"event": "purchase", "user": "bob", "ts": "2026-07-19T10:01:00"}

df = pd.read_json("events.jsonl", lines=True)
print(df)
#       event   user                   ts
# 0     click  alice  2026-07-19 10:00:00
# 1  purchase    bob  2026-07-19 10:01:00

Reading JSON from APIs

When you fetch JSON from an API, you get a Python dictionary (via requests), not a file. Convert it to a DataFrame directly.

import requests

response = requests.get("https://api.example.com/v1/orders")
data = response.json()  # Python dict or list

# If the response is a list of flat records
df = pd.DataFrame(data)

# If the response has nested structure
df = pd.json_normalize(data, record_path="results",
                       meta=["page", "total_pages"])

Writing JSON Files

# Write as array of records (default orient)
df.to_json("output.json", orient="records", indent=2)
# [{"id": 1, "name": "Widget"}, {"id": 2, "name": "Gadget"}]

# Write as JSON Lines (one object per line) — best for streaming/append
df.to_json("output.jsonl", orient="records", lines=True)
# {"id": 1, "name": "Widget"}
# {"id": 2, "name": "Gadget"}

# Write with date formatting
df.to_json("output.json", orient="records", date_format="iso", indent=2)

# Compression
df.to_json("output.json.gz", orient="records", compression="gzip")

Reading Parquet Files

Parquet is the gold standard for data engineering storage. Delta Lake tables, Hive tables, Spark datasets, and Fabric Lakehouses all use Parquet under the hood. Understanding Parquet is not optional — it is foundational.

Analogy — A library organized by subject, not by book. A CSV file is like a library organized by book — to find all the science content, you must walk through every shelf and check every book. A Parquet file is like a library organized by subject — all science books are on one shelf, all history on another. If you only need science (one column), you read one shelf and skip the rest. That is columnar storage: data is stored column by column, not row by row. Reading 3 columns from a 100-column Parquet file reads only 3% of the data. Reading 3 columns from a CSV reads 100% and discards 97%.

Why Parquet Beats CSV for Data Engineering

Feature	CSV	Parquet
Storage	Row-based (text)	Columnar (binary)
File size	1 GB	~200 MB (5x compression typical)
Read speed	Must scan entire file	Reads only requested columns
Schema	None (inferred on read)	Embedded in file metadata
Type safety	Everything is a string	int64, float64, timestamp, bool preserved
Encoding	UTF-8 (usually)	Dictionary, RLE, delta encoding per column
Compression	None by default	Snappy (default), Gzip, Zstd
Spark/Fabric support	Slow (parse text on every read)	Native (zero-copy reads possible)

Basic Parquet Reading

# Basic read — pyarrow is the default engine
df = pd.read_parquet("sales.parquet")

# Read only specific columns (columnar advantage — reads less data)
df = pd.read_parquet("sales.parquet", columns=["order_id", "amount", "order_date"])

# Use Arrow-backed dtypes for better memory efficiency
df = pd.read_parquet("sales.parquet", dtype_backend="pyarrow")
# Arrow dtypes use ~35% less memory than NumPy for strings and nullable ints

Reading with Filters (Predicate Pushdown)

Parquet supports predicate pushdown — the filter is applied during read, so rows that do not match are never loaded into memory. This is dramatically faster than reading the entire file and filtering afterward.

# Filter during read — only matching rows are loaded
df = pd.read_parquet("sales.parquet",
                     filters=[("amount", ">", 100),
                              ("region", "==", "Ontario")])
# Equivalent to: read full file → df[df["amount"] > 100 & df["region"] == "Ontario"]
# But MUCH faster because non-matching row groups are skipped entirely

Reading Partitioned Parquet (Hive-Style)

Data lakes often store Parquet files partitioned by date, region, or other keys. The directory structure looks like sales/year=2026/month=07/data.parquet. Pandas reads the entire partition tree and reconstructs the partition columns.

# Partitioned Parquet directory:
# sales/
#   year=2025/month=01/part-001.parquet
#   year=2025/month=02/part-001.parquet
#   year=2026/month=07/part-001.parquet

# Read the entire dataset (all partitions)
df = pd.read_parquet("sales/")

# Read with filter — only scans the matching partition directories
df = pd.read_parquet("sales/",
                     filters=[("year", "==", 2026), ("month", "==", 7)])
# Only reads sales/year=2026/month=07/ — skips all other folders

Reading Parquet with pyarrow Directly

For maximum control and performance, use pyarrow’s native API instead of going through pandas. This is useful when you need metadata inspection, schema validation, or when working with very large datasets.

import pyarrow.parquet as pq

# Read the schema without loading data
schema = pq.read_schema("sales.parquet")
print(schema)
# order_id: int64
# product: string
# amount: double
# order_date: timestamp[us]

# Read as a PyArrow Table (zero-copy, faster than pandas for large files)
table = pq.read_table("sales.parquet", columns=["order_id", "amount"])
print(table.num_rows)

# Convert to pandas when needed
df = table.to_pandas()

# Read Parquet metadata (row groups, compression, sizes)
metadata = pq.read_metadata("sales.parquet")
print(f"Row groups: {metadata.num_row_groups}")
print(f"Rows: {metadata.num_rows}")
print(f"Columns: {metadata.num_columns}")
print(f"Created by: {metadata.created_by}")

Writing Parquet Files

# Basic write with Snappy compression (default, fast, good compression)
df.to_parquet("output.parquet", index=False)

# Explicit compression choices
df.to_parquet("output.parquet", index=False, compression="snappy")   # Default: fast read/write
df.to_parquet("output.parquet", index=False, compression="gzip")     # Smaller files, slower
df.to_parquet("output.parquet", index=False, compression="zstd")     # Best ratio, modern
df.to_parquet("output.parquet", index=False, compression=None)       # No compression

# Write partitioned Parquet (Hive-style directories)
df.to_parquet("output_dir/", index=False, partition_cols=["year", "region"])
# Creates: output_dir/year=2026/region=Ontario/part-0.parquet
#          output_dir/year=2026/region=Quebec/part-0.parquet

# Write with pyarrow for more control
import pyarrow as pa
import pyarrow.parquet as pq

table = pa.Table.from_pandas(df)
pq.write_table(table, "output.parquet",
               compression="zstd",
               row_group_size=500_000,       # Control row group size
               use_dictionary=True,          # Dictionary encoding for strings
               write_statistics=True)        # Enable min/max stats for pushdown

Compression Comparison

Compression	Write Speed	Read Speed	Compression Ratio	Best For
Snappy	Fastest	Fastest	Good (3-4x)	Interactive queries, Spark default
Gzip	Slow	Medium	Great (5-7x)	Archival, cold storage, bandwidth-limited
Zstd	Fast	Fast	Best (5-8x)	Modern pipelines, best overall balance
None	Instant	Fastest	1x (no compression)	Temporary files, debugging

Reading Excel Files

Excel files come from business users, finance teams, and anyone outside the engineering org. They are messy — merged cells, multiple sheets, formatting, formulas, hidden rows — and pandas handles most of it.

Analogy — Unpacking a gift basket. An Excel file is like a gift basket: there are multiple items (sheets) inside, some wrapped in tissue paper (formatting), some with cards attached (comments), and the basket itself has a bow on top (the filename). You need to unwrap each item carefully and extract just the data.

# Basic read — reads the first sheet by default
df = pd.read_excel("report.xlsx")

# Read a specific sheet by name or index
df = pd.read_excel("report.xlsx", sheet_name="Q2 Revenue")
df = pd.read_excel("report.xlsx", sheet_name=1)          # Second sheet (0-indexed)

# Read ALL sheets — returns a dict of DataFrames
all_sheets = pd.read_excel("report.xlsx", sheet_name=None)
for sheet_name, df in all_sheets.items():
    print(f"{sheet_name}: {df.shape}")

# Skip metadata rows and set custom headers
df = pd.read_excel("report.xlsx", skiprows=4, header=0)

# Read specific columns
df = pd.read_excel("report.xlsx", usecols="A:D")          # Excel-style column range
df = pd.read_excel("report.xlsx", usecols=[0, 2, 5])      # By position
df = pd.read_excel("report.xlsx", usecols=["Name", "Revenue"])  # By name

# Handle dates and types
df = pd.read_excel("report.xlsx", parse_dates=["Date"],
                   dtype={"ZipCode": str})

# Specify the engine (openpyxl for .xlsx, xlrd for .xls)
df = pd.read_excel("report.xlsx", engine="openpyxl")
df = pd.read_excel("legacy.xls", engine="xlrd")

Writing Excel Files

# Basic write
df.to_excel("output.xlsx", index=False, sheet_name="Data")

# Write multiple sheets to one file
with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df_revenue.to_excel(writer, sheet_name="Revenue", index=False)
    df_costs.to_excel(writer, sheet_name="Costs", index=False)
    df_summary.to_excel(writer, sheet_name="Summary", index=False)
# All three DataFrames end up as separate sheets in one .xlsx file

# Write with formatting using openpyxl directly
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Sales Report"

# Header row with bold + blue background
headers = ["Product", "Revenue", "Units"]
header_fill = PatternFill(start_color="4472C4", fill_color="4472C4", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF")
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill

# Data rows
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

wb.save("formatted_report.xlsx")

Converting Between Formats

One of the most common data engineering tasks is format conversion — CSV from a vendor lands in your S3 bucket, and you need it as Parquet in the data lake. These one-liners handle the conversion.

# CSV → Parquet (most common conversion in data engineering)
df = pd.read_csv("raw_data.csv", dtype={"zip": str})
df.to_parquet("clean_data.parquet", index=False, compression="snappy")

# JSON → Parquet (API response to data lake)
df = pd.read_json("api_response.json")
df.to_parquet("api_data.parquet", index=False)

# Excel → Parquet (business report to analytics layer)
df = pd.read_excel("finance_report.xlsx", sheet_name="Transactions")
df.to_parquet("finance.parquet", index=False)

# Parquet → CSV (for a business user who needs Excel compatibility)
df = pd.read_parquet("analytics.parquet", columns=["product", "revenue"])
df.to_csv("for_business_user.csv", index=False)

# JSON Lines → Partitioned Parquet (event stream to data lake)
df = pd.read_json("events.jsonl", lines=True)
df["date"] = pd.to_datetime(df["timestamp"]).dt.date
df.to_parquet("events/", index=False, partition_cols=["date"])

Working with Large Files — Memory Optimization

When files are too large for memory, you have several strategies beyond chunking. These techniques reduce memory usage by 50-90% without losing data.

Strategy 1: Optimize dtypes Before Processing

# Read a sample to understand the data
sample = pd.read_csv("huge.csv", nrows=1000)
print(sample.dtypes)
print(sample.memory_usage(deep=True))

# Define optimal dtypes based on actual data ranges
dtype_map = {
    "user_id": "int32",          # int64 → int32 (if values < 2 billion)
    "amount": "float32",         # float64 → float32 (if precision is acceptable)
    "status": "category",        # string → category (if few unique values)
    "country_code": "category",  # string → category (200 countries vs millions of rows)
    "zip_code": str,             # Prevent leading-zero loss
}
df = pd.read_csv("huge.csv", dtype=dtype_map)
print(f"Memory: {df.memory_usage(deep=True).sum() / 1e6:.1f} MB")

Strategy 2: Arrow-Backed Dtypes

# Arrow-backed dtypes use ~35% less memory, especially for strings
df = pd.read_parquet("data.parquet", dtype_backend="pyarrow")
# Or for CSV:
df = pd.read_csv("data.csv", dtype_backend="pyarrow")

print(df.dtypes)
# id       int64[pyarrow]
# name     string[pyarrow]
# amount   double[pyarrow]

Strategy 3: Read Only What You Need

# Column pruning — read 3 of 50 columns
df = pd.read_parquet("wide_table.parquet", columns=["id", "amount", "date"])

# Row pruning with Parquet filters
df = pd.read_parquet("sales.parquet",
                     filters=[("date", ">=", "2026-01-01")])

# For CSV: combine usecols + nrows for exploration
df = pd.read_csv("huge.csv", usecols=["id", "amount"], nrows=10_000)

Schema Validation and Type Safety

In production pipelines, you should never trust that the file has the expected schema. A column that was always integer might suddenly contain “N/A” strings, breaking your downstream pipeline. Validate before processing.

def validate_csv_schema(filepath, expected_columns, expected_dtypes):
    # Validate a CSV file matches the expected schema before processing.
    df = pd.read_csv(filepath, nrows=0)  # Read headers only (zero rows)

    # Check column names
    missing = set(expected_columns) - set(df.columns)
    extra = set(df.columns) - set(expected_columns)
    if missing:
        raise ValueError(f"Missing columns: {missing}")
    if extra:
        print(f"Warning: unexpected columns will be ignored: {extra}")

    # Read a sample and check types
    sample = pd.read_csv(filepath, nrows=100, dtype=expected_dtypes)

    # Check for nulls in required columns
    for col in expected_columns:
        null_count = sample[col].isna().sum()
        if null_count > 0:
            print(f"Warning: {col} has {null_count} nulls in first 100 rows")

    return True

# Usage
validate_csv_schema(
    "vendor_data.csv",
    expected_columns=["order_id", "product", "amount", "order_date"],
    expected_dtypes={"order_id": int, "product": str, "amount": float, "order_date": str}
)

# Parquet schema validation with pyarrow
import pyarrow.parquet as pq

def validate_parquet_schema(filepath, expected_fields):
    # Check Parquet schema matches expectations without reading data.
    schema = pq.read_schema(filepath)
    actual_fields = {field.name: str(field.type) for field in schema}

    for name, expected_type in expected_fields.items():
        if name not in actual_fields:
            raise ValueError(f"Missing field: {name}")
        if actual_fields[name] != expected_type:
            print(f"Type mismatch: {name} is {actual_fields[name]}, expected {expected_type}")

    return True

validate_parquet_schema("sales.parquet", {
    "order_id": "int64",
    "amount": "double",
    "order_date": "timestamp[us]"
})

Real-World ETL Pattern: CSV to Partitioned Parquet

Here is a complete, production-ready pipeline that ingests a raw CSV vendor drop, validates the schema, cleans the data, and writes partitioned Parquet to a data lake structure.

import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq
from pathlib import Path
from datetime import datetime

def csv_to_partitioned_parquet(
    csv_path: str,
    output_dir: str,
    partition_cols: list[str],
    date_columns: list[str] = None,
    dtype_overrides: dict = None,
    compression: str = "snappy"
):
    # Production ETL: CSV to cleaned, partitioned Parquet.
    # Handles encoding detection, type casting, null cleaning,
    # and Hive-style partitioned output.

    # 1. Read with explicit types to prevent inference mistakes
    df = pd.read_csv(
        csv_path,
        dtype=dtype_overrides or {},
        parse_dates=date_columns or [],
        na_values=["", "N/A", "NULL", "null", "None", "-"],
        encoding="utf-8"
    )
    print(f"Read {len(df)} rows, {len(df.columns)} columns from {csv_path}")

    # 2. Clean column names (lowercase, underscores, no spaces)
    df.columns = (
        df.columns.str.strip()
        .str.lower()
        .str.replace(r"[^a-z0-9]+", "_", regex=True)
        .str.strip("_")
    )

    # 3. Drop fully empty rows and columns
    df.dropna(how="all", inplace=True)
    df.dropna(axis=1, how="all", inplace=True)

    # 4. Write partitioned Parquet
    output = Path(output_dir)
    output.mkdir(parents=True, exist_ok=True)

    table = pa.Table.from_pandas(df, preserve_index=False)
    pq.write_to_dataset(
        table,
        root_path=str(output),
        partition_cols=partition_cols,
        compression=compression
    )

    print(f"Written to {output_dir} with partitions: {partition_cols}")
    print(f"Compression: {compression}")

# Usage
csv_to_partitioned_parquet(
    csv_path="vendor_drop/orders_20260719.csv",
    output_dir="data_lake/bronze/orders/",
    partition_cols=["region"],
    date_columns=["order_date", "ship_date"],
    dtype_overrides={"zip_code": str, "phone": str},
    compression="snappy"
)

Common Mistakes

1. Not setting index=False when writing CSV or Parquet. By default, pandas writes the DataFrame index as an extra column (often “Unnamed: 0”). This bloats the file and confuses downstream consumers. Always set index=False unless you specifically need the index in the output.

2. Reading zip codes, phone numbers, and IDs as integers. Zip code “07102” becomes 7102 when read as int. Phone number “0412345678” loses the leading zero. Always dtype={"zip_code": str, "phone": str} for these columns.

3. Using CSV for data lake storage. CSV has no schema, no compression, no column pruning, and no predicate pushdown. A 1 GB CSV file might be 200 MB as Parquet and 10x faster to query. Use Parquet for anything stored in a data lake.

4. Not using parse_dates during read. If you read dates as strings and convert them later, you waste memory (strings are larger than timestamps) and risk format inconsistencies. Let read_csv() parse dates during read with parse_dates=["date_col"].

5. Ignoring encoding errors. Legacy systems produce Latin-1, Windows-1252, or Shift-JIS encoded files. A bare read_csv() call defaults to UTF-8 and crashes on non-UTF-8 bytes. Detect encoding with chardet or specify encoding="latin-1" explicitly.

6. Loading entire large files into memory. A 10 GB CSV file will consume 20-30 GB of RAM as a pandas DataFrame. Use chunksize for CSV, or use Parquet with columns and filters to read only what you need.

7. Not validating schemas in production pipelines. A column that was always “int64” might suddenly contain “N/A” from a vendor change, breaking your pipeline at 3 AM. Validate schema before processing with nrows=0 (headers only) or pq.read_schema().

8. Using json.load() for large JSON files instead of pd.read_json(lines=True). Python’s json.load() reads the entire file into memory as a dict. For JSON Lines files (one object per line), pd.read_json(path, lines=True) streams the file and returns a DataFrame directly.

Interview Questions

Q: What is the difference between row-based and columnar storage, and why does it matter? A: Row-based formats (CSV, JSON) store each record as a complete unit — all fields of row 1, then all fields of row 2. Columnar formats (Parquet, ORC) store each column as a contiguous block — all values of column A, then all values of column B. This matters because analytical queries typically read a few columns from many rows. A columnar format reads only the requested columns (3 of 100 = 3% of data), while a row-based format reads every column (100%) and discards what it does not need. Columnar storage also compresses better because similar values (same column) are stored together.

Q: What is predicate pushdown in Parquet and how does it work? A: Predicate pushdown applies row-level filters during the read operation, before data reaches your application. Parquet files are divided into row groups, and each row group stores min/max statistics for every column. When you filter amount > 100, the reader checks each row group’s statistics: if a row group’s max amount is 50, the entire group is skipped without reading any data. This can eliminate 90%+ of the data before it reaches pandas, making reads dramatically faster than read-then-filter.

Q: When would you use JSON Lines instead of regular JSON? A: JSON Lines (one JSON object per line, no wrapping array) is better for streaming, logging, and large datasets. Regular JSON requires the entire file to be valid JSON (a single array), so you cannot append records without rewriting the file. JSON Lines lets you append by adding a new line. It is also streamable — you can process one line at a time without loading the entire file. Use JSON Lines for event logs, streaming pipelines, and any file that grows incrementally. Use regular JSON for configuration files, API responses, and small datasets.

Q: How do you handle a CSV file that is too large to fit in memory? A: Three strategies. First, use chunksize in read_csv() to process the file in manageable batches — each chunk is an independent DataFrame. Second, convert the CSV to Parquet first (even chunk by chunk), then use Parquet’s columnar reading and predicate pushdown to read only what you need. Third, optimize dtypes — use int32 instead of int64, category for low-cardinality strings, and usecols to read only needed columns. For truly massive files (100 GB+), switch to PySpark or Dask instead of pandas.

Q: What compression should you use for Parquet files? A: Snappy for interactive workloads (Spark, Fabric queries) — it is fast to read and write with good compression. Zstd for modern pipelines — it offers the best compression ratio with fast speed, making it ideal for data lakes where storage cost matters. Gzip for archival and bandwidth-limited transfers — it compresses the most but is slower to read. Use no compression only for temporary files or debugging.

Q: How would you convert a messy Excel file into clean Parquet for a data lake? A: Read the Excel file with pd.read_excel() specifying skiprows to skip metadata headers, usecols to select only data columns, and dtype to force correct types. Clean column names (lowercase, underscores). Drop empty rows and columns. Parse dates explicitly. Validate the schema against expectations. Then write to Parquet with to_parquet(index=False, compression="snappy"). For multiple sheets, loop through sheet_name=None (returns a dict of DataFrames) and write each as a separate Parquet file or partition.

Q: What is the advantage of Arrow-backed dtypes in pandas? A: Arrow-backed dtypes (dtype_backend="pyarrow") store data in Apache Arrow’s memory format instead of NumPy arrays. Benefits include approximately 35% less memory usage (especially for strings, which are stored as variable-length in Arrow vs fixed-size objects in NumPy), native null support (no need for object dtype to hold NaN in integer columns), and faster conversion to/from Parquet (since Parquet uses Arrow internally, there is near zero-copy conversion). The trade-off is that some pandas operations may be slightly slower on Arrow-backed DataFrames in the current version, but this is improving rapidly.

Wrapping Up

File format mastery is what separates a Python developer from a Python data engineer. Know when to use CSV (data exchange, legacy compatibility), JSON (APIs, nested data), Parquet (data lakes, analytics, anything Spark or Fabric touches), and Excel (business reports, finance handoffs). Convert early in your pipeline — raw CSV or JSON arrives in bronze, gets validated and cleaned, and lands as partitioned Parquet in silver. Every downstream consumer benefits from the columnar, compressed, schema-embedded format.

← Previous: Python for Data Engineers Python (22/31) Next: Database Connections →

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.

Python File Formats for Data Engineers: Reading and Writing CSV, JSON, Parquet, and Excel with pandas, pyarrow, and openpyxl — Every Parameter, Pattern, and Pitfall

Table of Contents

Why File Formats Matter

Reading CSV Files

Basic CSV Reading

Essential Parameters

Handling Large CSV Files with Chunking

Writing CSV Files

Reading JSON Files

Flat JSON (Array of Records)

Nested JSON

JSON Lines (JSONL / NDJSON)

Reading JSON from APIs

Writing JSON Files

Reading Parquet Files

Why Parquet Beats CSV for Data Engineering

Basic Parquet Reading

Reading with Filters (Predicate Pushdown)

Reading Partitioned Parquet (Hive-Style)

Reading Parquet with pyarrow Directly

Writing Parquet Files

Compression Comparison

Reading Excel Files

Writing Excel Files

Converting Between Formats

Working with Large Files — Memory Optimization

Strategy 1: Optimize dtypes Before Processing

Strategy 2: Arrow-Backed Dtypes

Strategy 3: Read Only What You Need

Schema Validation and Type Safety

Real-World ETL Pattern: CSV to Partitioned Parquet

Common Mistakes

Interview Questions

Wrapping Up

Leave a Comment Cancel Reply

Table of Contents

Why File Formats Matter

Reading CSV Files

Basic CSV Reading

Essential Parameters

Handling Large CSV Files with Chunking

Writing CSV Files

Reading JSON Files

Flat JSON (Array of Records)

Nested JSON

JSON Lines (JSONL / NDJSON)

Reading JSON from APIs

Writing JSON Files

Reading Parquet Files

Why Parquet Beats CSV for Data Engineering

Basic Parquet Reading

Reading with Filters (Predicate Pushdown)

Reading Partitioned Parquet (Hive-Style)

Reading Parquet with pyarrow Directly

Writing Parquet Files

Compression Comparison

Reading Excel Files

Writing Excel Files

Converting Between Formats

Working with Large Files — Memory Optimization

Strategy 1: Optimize dtypes Before Processing

Strategy 2: Arrow-Backed Dtypes

Strategy 3: Read Only What You Need

Schema Validation and Type Safety

Real-World ETL Pattern: CSV to Partitioned Parquet

Common Mistakes

Interview Questions

Wrapping Up

Related Posts

Leave a Comment Cancel Reply