Data Import and Export

Every analytics project starts by reading data in and ends by writing results out. Pandas provides a uniform read_ / to_ API across a dozen formats.

Format Reference

CSV — The Workhorse

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "roll": [101, 102, 103, 104],
    "name": ["Riya", "Zoya", "Kabir", "Aarav"],
    "marks": [88, 76, 91, 54],
    "joined": pd.to_datetime(["2026-01-14", "2026-01-15", "2026-02-01", "2026-02-03"]),
})

# WRITE
df.to_csv("students.csv", index=False)              # index=False avoids a junk column
df.to_csv("students_tab.tsv", sep="\t", index=False)
df.to_csv("students_subset.csv", columns=["name", "marks"], index=False)

# READ
data = pd.read_csv("students.csv")
print(data.head())
print(data.dtypes)      # NOTE: 'joined' comes back as object (string), not datetime

Important read_csv Parameters

data = pd.read_csv(
    "students.csv",
    sep=",",                      # delimiter (use "\t" for TSV, ";" for European CSV)
    header=0,                     # row number to use as column names (None if no header)
    names=None,                   # supply your own column names
    index_col="roll",             # use this column as the index
    usecols=["roll", "name", "marks"],   # read only these columns — saves memory
    dtype={"roll": "int32"},      # force specific dtypes
    parse_dates=["joined"],       # parse these as datetime
    na_values=["NA", "n/a", "-", "missing"],   # extra strings to treat as NaN
    skiprows=0,                   # skip leading rows
    nrows=None,                   # read only the first n rows
    encoding="utf-8",             # try "latin-1" or "cp1252" if you get a UnicodeDecodeError
    thousands=",",                # parse "1,200" as 1200
)
print(data.dtypes)                # now 'joined' is datetime64[ns]

Reading Large Files in Chunks

# When the file does not fit in memory, process it piece by piece
total = 0
count = 0
for chunk in pd.read_csv("students.csv", chunksize=2):
    total += chunk["marks"].sum()
    count += len(chunk)
    print(f"Processed chunk of {len(chunk)} rows")

print(f"Overall mean: {total / count:.2f}")

# Or build a filtered subset without ever loading the whole file
high_scorers = pd.concat([
    chunk[chunk["marks"] > 75]
    for chunk in pd.read_csv("students.csv", chunksize=2)
])
print(high_scorers)

Excel

# WRITE — single sheet
df.to_excel("students.xlsx", sheet_name="Marks", index=False)

# WRITE — multiple sheets in one workbook
summary = df.groupby(df["marks"] >= 75)["marks"].agg(["count", "mean"])
with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Raw Data", index=False)
    summary.to_excel(writer, sheet_name="Summary")
    df.describe().to_excel(writer, sheet_name="Statistics")

# READ
data = pd.read_excel("students.xlsx", sheet_name="Marks")
all_sheets = pd.read_excel("report.xlsx", sheet_name=None)      # dict of DataFrames
print(list(all_sheets.keys()))                                   # sheet names

# Read a specific cell range
# pd.read_excel("report.xlsx", sheet_name="Raw Data", usecols="A:C", skiprows=2, nrows=10)

JSON

# WRITE — orient controls the structure
df.to_json("students_records.json", orient="records", indent=2, date_format="iso")
df.to_json("students_index.json", orient="index")

# READ
data = pd.read_json("students_records.json")
print(data)

# NESTED JSON — flatten with json_normalize
nested = {
    "students": [
        {"name": "Riya", "scores": {"da": 88, "dbms": 92},
         "address": {"city": "Delhi", "pin": "110001"}},
        {"name": "Zoya", "scores": {"da": 76, "dbms": 81},
         "address": {"city": "Noida", "pin": "201301"}},
    ]
}
flat = pd.json_normalize(nested["students"])
print(flat.columns.tolist())
# ['name', 'scores.da', 'scores.dbms', 'address.city', 'address.pin']
print(flat)

SQL Databases

import sqlite3

conn = sqlite3.connect("college.db")

# WRITE a DataFrame to a table
df.to_sql("students", conn, if_exists="replace", index=False)

# READ with a query
result = pd.read_sql_query("""
    SELECT name, marks
    FROM students
    WHERE marks > 70
    ORDER BY marks DESC
""", conn)
print(result)

# Read a whole table
whole = pd.read_sql_table if False else pd.read_sql("SELECT * FROM students", conn)
print(whole)

# Parameterised query — ALWAYS do this with user input (prevents SQL injection)
safe = pd.read_sql_query(
    "SELECT * FROM students WHERE marks > ?", conn, params=(75,)
)
print(safe)

conn.close()

# Other databases via SQLAlchemy
# from sqlalchemy import create_engine
# engine = create_engine("postgresql://user:password@localhost:5432/mydb")
# engine = create_engine("mysql+pymysql://user:password@localhost/mydb")
# df = pd.read_sql("SELECT * FROM sales WHERE year = 2026", engine)

APIs and Web Data

import requests

# REST API returning JSON
# response = requests.get("https://api.example.com/v1/sales",
#                         params={"year": 2026}, timeout=10)
# response.raise_for_status()
# df = pd.json_normalize(response.json()["data"])

# HTML tables straight from a web page
# tables = pd.read_html("https://en.wikipedia.org/wiki/List_of_states_by_population")
# print(f"Found {len(tables)} tables")
# df = tables[0]

Parquet — The Big Data Format

# pip install pyarrow
df.to_parquet("students.parquet", compression="snappy", index=False)
data = pd.read_parquet("students.parquet")
print(data.dtypes)      # dtypes ARE preserved, unlike CSV

# Read only specific columns — columnar storage makes this genuinely cheap
partial = pd.read_parquet("students.parquet", columns=["name", "marks"])

Format Comparison

# Measuring the difference on a realistic dataset
import os, time

big = pd.DataFrame({
    "id": range(200_000),
    "value": np.random.randn(200_000),
    "category": np.random.choice(["A", "B", "C", "D"], 200_000),
    "date": pd.date_range("2020-01-01", periods=200_000, freq="min"),
})

for fmt, writer, reader in [
    ("csv", lambda f: big.to_csv(f, index=False), pd.read_csv),
    ("parquet", lambda f: big.to_parquet(f, index=False), pd.read_parquet),
]:
    path = f"bench.{fmt}"
    t0 = time.time(); writer(path); w = time.time() - t0
    t0 = time.time(); _ = reader(path); r = time.time() - t0
    size = os.path.getsize(path) / 1024**2
    print(f"{fmt:8} write {w:.3f}s  read {r:.3f}s  size {size:.2f} MB")
    os.remove(path)
# Parquet is typically 5-10x smaller and several times faster to read.

Common Import Problems

# Memory optimisation — downcast dtypes after loading
def optimise_memory(df):
    before = df.memory_usage(deep=True).sum() / 1024**2
    for col in df.select_dtypes(include=["int64"]).columns:
        df[col] = pd.to_numeric(df[col], downcast="integer")
    for col in df.select_dtypes(include=["float64"]).columns:
        df[col] = pd.to_numeric(df[col], downcast="float")
    for col in df.select_dtypes(include=["object"]).columns:
        if df[col].nunique() / len(df) < 0.5:          # low cardinality
            df[col] = df[col].astype("category")
    after = df.memory_usage(deep=True).sum() / 1024**2
    print(f"Memory: {before:.2f} MB -> {after:.2f} MB ({(1-after/before)*100:.1f}% saved)")
    return df

big = optimise_memory(big)

With data loaded, the next lesson covers reshaping and manipulating it into analysis-ready form.