Loading data: CSV, Excel, JSON and SQL

pd.read_csv() is the most-used function in data science, and it has around fifty arguments. Six of them solve almost every real problem.

import pandas as pd

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

The six arguments that matter

df = pd.read_csv(
    "messy.csv",
    sep=";",                      # semicolon-separated (common in Europe)
    encoding="utf-8",             # or "latin-1" for older exports
    na_values=["", "NA", "N/A", "-", "absent", "999"],
    parse_dates=["enrolled_on"],  # real datetimes, not text
    dtype={"phone": "string", "student_id": "string"},
    thousands=",",                # "1,250" -> 1250
)

• sep — the delimiter. Use sep=None, engine="python" to let pandas guess when you are unsure.
• encoding — a UnicodeDecodeError means the file is not UTF-8. Try latin-1, which never fails, then check the strange characters by eye.
• na_values — every organisation invents its own way of writing "missing". List them all or they poison the column's type.
• parse_dates — without it, dates are text and no date arithmetic works.
• dtype — force IDs and phone numbers to stay text so leading zeros survive.
• thousands — a single comma inside a number turns the whole column into text.

Other formats

# Excel -- pick the sheet, and skip decorative header rows
df = pd.read_excel("report.xlsx", sheet_name="Term 1", skiprows=3)
sheets = pd.read_excel("report.xlsx", sheet_name=None)   # dict of all sheets

# JSON, including nested structures
df = pd.read_json("records.json")
df = pd.json_normalize(payload, record_path="items", meta=["order_id"])

# A whole HTML table from a web page (returns a list of DataFrames)
tables = pd.read_html("results.html")

# SQL -- the professional path
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user:pass@localhost/yanjye")
df = pd.read_sql("SELECT district, mark FROM students WHERE term = 1", engine)

When the data lives in a database, filter and aggregate in SQL and bring back only what you need. Pulling ten million rows into pandas to keep a thousand is a beginner move that will exhaust your memory.

Big files

# read only the columns you need
df = pd.read_csv("huge.csv", usecols=["district", "mark", "term"])

# peek before committing
df = pd.read_csv("huge.csv", nrows=1000)

# process in pieces when the whole file will not fit
totals = {}
for chunk in pd.read_csv("huge.csv", chunksize=100_000):
    for district, total in chunk.groupby("district")["mark"].sum().items():
        totals[district] = totals.get(district, 0) + total

Saving your work

df.to_csv("clean.csv", index=False)     # index=False, almost always
df.to_excel("report.xlsx", index=False)
df.to_parquet("clean.parquet")          # typed, compressed, much faster

Forgetting index=False adds a nameless first column, which then reappears as Unnamed: 0 the next time anyone loads the file. Parquet keeps dtypes and is the right choice for anything you will reload.

Try it yourself

Take your students.csv and deliberately break it: change the separator to semicolons, write one mark as "absent", add a date column typed as 05/03/2026, and put a comma in a number. Then load it correctly in a single read_csv call and confirm every dtype with df.dtypes.