Reading and writing data

CSV, Excel, JSON and SQL in and out — plus the encoding, dtype and date problems that appear on real files.

Reading files

df = pd.read_csv("sales.csv")

pd.read_csv("sales.csv",
            sep=";",
            encoding="utf-8",
            usecols=["date", "amount"],
            parse_dates=["date"],
            dtype={"zip": "string"},
            na_values=["", "NA", "null"],
            nrows=1000)

pd.read_excel("book.xlsx", sheet_name="Q3")
pd.read_json("data.json", lines=True)     # one JSON object per line
pd.read_sql("SELECT * FROM orders", con=engine)

Argument	Why you need it
`encoding`	Non-UTF-8 exports (latin-1, cp1252) otherwise crash
`sep`	Many European exports use semicolons
`parse_dates`	Turns strings into real datetimes so you can filter by time
`dtype`	Stops leading zeros in postal codes being eaten
`usecols`	Loads only what you need — big speed and memory win
`na_values`	Teaches pandas which strings mean "missing"

⚠️

Read the file with nrows=5 first and print df.head(). Real-world CSVs have stray headers, thousands separators and quoted commas that a blind full load will happily mangle.

First five minutes with any dataset

df.shape
df.head()
df.info()                       # dtypes + missing counts
df.isna().sum().sort_values(ascending=False)
df.duplicated().sum()
df.describe(include="all")
df["category"].value_counts(dropna=False)

This sequence answers the questions that decide everything afterwards: how big is it, which columns have holes, are there duplicates, and which fields are actually categorical.

Writing data out

df.to_csv("clean.csv", index=False)          # drop the index: it is not data
df.to_csv("clean.csv", index=False, encoding="utf-8")
df.to_excel("report.xlsx", sheet_name="Summary", index=False)
df.to_json("out.json", orient="records", indent=2)
df.to_parquet("out.parquet")                  # keeps dtypes, much smaller

💡

index=False is the one people forget. Without it the row numbers get written as an unnamed first column, and the next reader inherits a phantom column.

FAQ

CSV or Parquet?

Parquet for anything you control end to end: it stores types, compresses well and reads far faster. CSV stays the interchange format for humans and external partners.

Why does read_excel fail on a big file?

Excel is slow for large sheets. Export to CSV once and work from that; keep Excel for the final, small deliverable.

Series and DataFrame Selecting, filtering and cleaning

Last refreshed 2026-09-18.

Reading and writing data

Reading files

First five minutes with any dataset

Writing data out

FAQ

Related