🐼pandas in 15 minutes

If a problem hands you a CSV or asks you to "implement transform(df)", pandas is the fastest way to write it. Each idea below is shown next to its SQL equivalent, since you'll think in both.

Load & inspect

import pandas as pd
df = pd.read_csv("orders.csv", dtype={"zip": str}, parse_dates=["order_date"])
df = pd.read_json("events.jsonl", lines=True)
df.info(); df.head(); df.describe(include="all"); df.isna().sum(); df.nunique()
df = pd.json_normalize(list_of_nested_dicts, sep=".")   # flatten JSON into columns

SQL ↔ pandas Rosetta stone

Dates & time series

df["ts"] = pd.to_datetime(df["ts"], errors="coerce")     # bad values become NaT, not a crash
df["month"] = df["ts"].dt.to_period("M").astype(str)       # '2026-10'
df["day"] = df["ts"].dt.date; df["dow"] = df["ts"].dt.day_name()
daily = df.set_index("ts").resample("D")["amount"].sum()    # fills missing days with 0
ma7 = daily.rolling(7, min_periods=1).mean()            # 7-day moving average (on the daily series)

# sessionize in pandas
df = df.sort_values(["user", "ts"])
gap = df.groupby("user")["ts"].diff() > pd.Timedelta(minutes=30)
df["session"] = gap.groupby(df["user"]).cumsum() + 1      # first event: NaT diff → False → session 1

Reshape

wide = df.pivot_table(index="user", columns="status", values="id", aggfunc="count", fill_value=0).reset_index()
long = wide.melt(id_vars="user", var_name="status", value_name="n")
items = orders.explode("items").reset_index(drop=True)     # one row per list element
items = pd.concat([items.drop(columns="items"), pd.json_normalize(items["items"])], axis=1)

Gotchas the judge will catch

• Index: tests often compare to_dict("records") or use assert_frame_equal. Finish with .reset_index(drop=True) and the exact column order df[["a","b","c"]].
• dtypes: an int column with NaN becomes float64 (1 → 1.0). Use astype("Int64") if ints are required.
• Chained assignment: write df.loc[mask, "col"] = v, not df[mask]["col"] = v.
• groupby drops NaN keys by default; pass dropna=False to keep them.
• Never loop with iterrows over big frames. Use vectorised ops, .map(dict), or np.where.
• Sorting ties: pass all tie-break columns, and add kind="stable" if the input order matters.

Worked example

import pandas as pd
def monthly_top_customer(orders: pd.DataFrame) -> pd.DataFrame:
    """For each month: the customer with the highest completed revenue (ties → smallest id)."""
    d = orders[orders.status == "completed"].copy()
    d["month"] = pd.to_datetime(d.order_date).dt.strftime("%Y-%m")
    g = d.groupby(["month", "customer_id"], as_index=False)["amount"].sum()
    g = g.sort_values(["month", "amount", "customer_id"], ascending=[True, False, True])
    return g.drop_duplicates("month", keep="first").reset_index(drop=True)

More tools worth knowing

df["city"].value_counts(normalize=True)                  # share per value (sorted desc)
pd.crosstab(df.user, df.status)                          # quick count pivot
df["bucket"] = pd.cut(df.amount, bins=[0, 10, 100, float("inf")], labels=["S", "M", "L"], right=False)
df["email"] = df["email"].str.strip().str.lower()        # vectorised string ops (.str.contains, .str.extract)
df["code"] = df["raw"].str.extract(r"#(\d+)", expand=False)
df["status"] = df["status"].map({"c": "completed", "x": "cancelled"}).fillna("unknown")

# method chaining with assign keeps each step readable
out = (df.query("status == 'completed'")
         .assign(revenue=lambda d: d.qty * d.price,
                 month=lambda d: d.order_date.str[:7])
         .groupby("month", as_index=False)["revenue"].sum())

# as-of join: latest rate at or before each trade (both sides sorted by the key)
trades = pd.merge_asof(trades.sort_values("ts"), rates.sort_values("ts"), on="ts", direction="backward")

# top-N per group
top3 = df.sort_values(["cat", "sales", "name"], ascending=[True, False, True]).groupby("cat").head(3)
# the row with the max per group
best = df.loc[df.groupby("cat")["sales"].idxmax()]

Reading messy files & writing exact output

df = pd.read_csv(path,
                 sep=";",                      # or sep=None, engine="python" to sniff
                 na_values=["", "NA", "null", "-"],
                 thousands=",",                # "1,200" → 1200
                 dtype={"id": str},            # keep leading zeros in ids / zip codes
                 skipinitialspace=True)
df.columns = df.columns.str.strip().str.lower()  # headers with stray spaces / capitals

df.to_csv("out.csv", index=False, float_format="%.2f")   # no index column, fixed decimals
print(df.to_csv(index=False), end="")                    # same thing to stdout
print(df.to_string(index=False))                         # aligned text table

Performance in one paragraph

Vectorised column operations run in C and are roughly 100× faster than apply(axis=1) or iterrows. groupby(...).agg with built-in names ("sum", "size", "nunique") is fast; a Python lambda inside agg is slow. Convert repeated strings to category dtype to save memory. For files bigger than RAM, read_csv(chunksize=…) and aggregate chunk by chunk (sums and counts combine; medians don't).