Reading and Writing Data
Getting bytes into tables — and back out
CSV, Parquet, Excel, JSON and SQL are the data interchange formats: reading, type inference, dtype control and fast binary formats are where real pipelines start.
What you will learn
- Read common formats with dtype control
- Choose binary formats (Parquet) for performance
- Write reproducible outputs and round-trips
What is this?
Pandas reads CSV (`pd.read_csv`), Excel, JSON, Parquet, Feather and SQL tables into DataFrames, inferring dtypes from the first N rows (or `dtype=` you specify). Binary formats like Parquet preserve dtypes exactly and compress well, making them the object-storage workhorse of modern pipelines.
Why do you need it?
- CSV is the lingua franca but slow, whitespace-fragile and dtype-lossy.
- Parquet reads sub-100ms for columns you need and is the de-facto warehouse/lake format.
- dtype inference failure is a top source of 'my numbers turned to strings' bugs.
- Round-trip fidelity (save→load→same) is the difference between trust and drift.
Mental model
Shipping crates: paper manifest vs sealed climate-controlled container
CSV vs Parquet
The manifest (CSV) is readable anywhere but loses the crate's conditions (dtypes, compression). The container (Parquet) is faster, denser and preserves the metadata — but needs a compatible terminal to open.
How it works
- pd.read_csv infers dtypes from up to 1e6 leading rows; pass dtype={'id': str} to force.
- CSV pitfalls: quoted commas, inconsistent headers, date parsing — read_csv has a knob for each.
- pd.read_parquet(path) pulls Parquet (fast, typed, compressed, columnar).
- df.to_parquet / df.to_csv / df.to_json / pd.read_sql with engines (pyarrow, sqlalchemy).
- Formats dictate the pipeline: raw files (CSV/JSON) → staged (Parquet) → consumed (query engines).
File formats by use case.
Examples
CSV dtype control
import pandas as pd, iocsv = "id,amount,when\nA01,10.50,2026-01-01\nA02,,2026-01-02"df = pd.read_csv(io.StringIO(csv), dtype={"id": str})print(df.dtypes) # id object, when object (needs parse_dates)df2 = pd.read_csv(io.StringIO(csv), dtype={"id": str}, parse_dates=["when"])print(df2["when"].dtype) # datetime64[ns]Forcing id to str prevents pandas turning 'A01' into garbage or numeric; parse_dates upgrades the date column from object to real datetime. These two args fix the majority of read_csv surprises.
In production
- A data ingestion job lands daily Parquet partitions on S3 that analysts read directly — typed and compressed.
- A revenue pipeline writes CSV to an Excel team but stages Parquet for the modeling path.
- Database extracts flow through read_sql with a query and land as Parquet partitions for the warehouse.
Best practices
- Always set dtype for identifier columns (ids are strings, never numbers).
- Use parse_dates for date columns at read time — string dates come back to bite algorithms.
- Prefer Parquet for anything > 50MB or any recurring pipeline.
- Set low_memory=False or explicit dtypes to stop the mixed-type warnings.
Common mistakes
- Reading an identifier column as int, silently dropping leading zeros.
- Assuming dtypes survive a CSV round-trip — they don't.
- Heavy CSV re-reads instead of caching to Parquet.
Debugging tips
- 'mixed types in column' warning → read with dtype= specified and normalize.
- If amounts come out as objects, there's probably a bad cell (e.g., '10.5 USD') in the file.
- Compare df.dtypes before/after a round-trip to catch format loss.
Interview checkpoint
Hands-on lab
- Read an untidy CSV with the right knobs
- Repair dtypes and parse dates
- Round-trip to Parquet and verify dtype fidelity
Load, Type, Round-Trip
30 min · beginner
Quick quiz
Which format preserves column dtypes across save/load?
pd.read_csv infers column dtypes automatically, but the inference can mislead.