Real data lives in files, databases, and APIs. Read CSV, JSON, Parquet, and Excel into a DataFrame, pull from SQL, and know which format to reach for.
Why: pandas has a one-line reader for every format you will meet — the work is choosing the right one and passing the right options. When: CSV for interchange, Excel for business files, JSON for API dumps, Parquet for big analytical data. Where: each read_* has a matching to_* for writing.
import pandas as pd
df_csv = pd.read_csv("data.csv")
df_json = pd.read_json("data.json")
df_xlsx = pd.read_excel("data.xlsx", sheet_name="Sheet1")
df_parq = pd.read_parquet("data.parquet")
# Writing back out is symmetric:
df_csv.to_parquet("data.parquet")Why: raw CSVs are messy — wrong separators, missing-value markers, and columns that should be dates — and read_csv handles all of it with arguments. When: reach for these the moment a plain read_csv looks wrong. Where: getting dtypes and dates right at load time saves cleaning later.
df = pd.read_csv(
"messy.csv",
sep=";", # semicolon-separated
na_values=["", "NA", "n/a"], # treat these as missing
parse_dates=["signup_date"], # parse this column as datetime
dtype={"zip_code": str}, # keep leading zeros
usecols=["id", "signup_date", "zip_code"], # only read what you need
)
print(df.dtypes)Why: format choice affects speed, size, and whether types survive a round trip — Parquet is columnar, compressed, and keeps dtypes, so it beats CSV for anything large. When: use CSV/Excel to share with humans; use Parquet for data that feeds models. Where: CSV loses every dtype (everything is text on disk); Parquet preserves them.
FORMAT SIZE SPEED KEEPS TYPES? USE FOR
CSV large slow no sharing, interchange
Excel large slow partial business users
JSON large slow partial API payloads, nested data
Parquet small fast yes big analytical data, ML
Rule of thumb: humans read CSV/Excel; pipelines pass Parquet.Why: much data never touches a file — it lives in SQL databases or behind HTTP APIs, and pandas loads from both directly. When: read_sql for warehouses and app databases; requests + DataFrame for JSON APIs. Where: pull only the rows and columns you need in the query, not the whole table.
import pandas as pd
from sqlalchemy import create_engine
# From SQL — let the database filter, don't load the whole table:
engine = create_engine("postgresql://user:pass@localhost/shop")
df = pd.read_sql("SELECT id, total FROM orders WHERE total > 100", engine)
# From a JSON API:
import requests
rows = requests.get("https://api.example.com/users").json()
df = pd.DataFrame(rows)