Reading CSV, Excel and Parquet

Getting data in correctly, with the right types, avoids most downstream cleaning headaches.

pandas has a read_* function for each common format, each returning a DataFrame.

`pd.read_csv(path)` is the workhorse. It accepts local paths, URLs and file-like objects, and infers compression from extensions like .gz or .zip. Options you'll use constantly:

sep=";" for other delimiters; header=None if there's no header row
usecols=[...] to read only some columns (faster and smaller)
dtype={"zip": "string", "qty": "int32"} to fix types instead of letting pandas guess; codes like zip codes or IDs with leading zeros must be read as strings
parse_dates=["date"] to get real datetimes
na_values=["-", "n/a"] for extra missing-value markers
index_col="id", nrows=1000 for a quick look, chunksize= for files too large to load at once
engine="pyarrow" for multi-threaded parsing of large files

`pd.read_excel(path, sheet_name="Data")` needs the openpyxl package for .xlsx. sheet_name=None reads every sheet into a dict of DataFrames. Excel files are slow to parse, so convert them once if you'll reread them.

`pd.read_parquet(path)` reads Apache Parquet, a compressed, columnar binary format that stores the schema. Types, including datetimes and categoricals, survive a round trip exactly, and columns=[...] reads only what you need. It requires pyarrow (or fastparquet). For anything you'll load repeatedly, Parquet is far faster and smaller than CSV.

Readers also accept dtype_backend="numpy_nullable" or "pyarrow" to get nullable or Arrow-backed types. Always check the result with df.info() or df.dtypes: a numeric column that came in as text usually means stray non-numeric values in the file.

Example

import io
import pandas as pd

csv = io.StringIO(
    "order_id;date;zip;qty;price\n"
    "1;2024-03-01;02134;2;9.5\n"
    "2;2024-03-02;10001;n/a;12.0\n"
    "3;2024-03-02;94105;1;-\n"
)
df = pd.read_csv(
    csv,
    sep=";",
    dtype={"zip": "string"},         # keep leading zeros
    parse_dates=["date"],
    na_values=["n/a", "-"],
    index_col="order_id",
)
print(df.dtypes)
# date     datetime64[ns]  (datetime64[us] on pandas 3)
# zip      string
# qty      float64        (int column with a missing value)
# price    float64
print(df.loc[1, "zip"])              # 02134

df.to_parquet("orders.parquet")      # needs pyarrow
back = pd.read_parquet("orders.parquet", columns=["zip", "qty"])
print(back.dtypes.astype(str).to_dict())   # {'zip': 'string', 'qty': 'float64'}

Remember

read_csv options: sep, usecols, dtype, parse_dates, na_values, index_col
Read IDs and codes as strings to keep leading zeros
Parquet keeps types and is much faster than CSV for repeated loads
Check df.dtypes right after loading

Watch out for

Letting pandas infer types on IDs like '00123', which become the integer 123.

Check yourself

A CSV column of integers contains a few blank cells. With default settings, what dtype does read_csv give it?
Why is Parquet usually preferred over CSV for data you reload often?

Official docs: pandas.pydata.org/docs/user_guide/io.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks