Pandas: DataFrames, Indexing, Groupby, Merge and Time Series Reference Guide

# Install — pandas + arrow backend (faster + nullable types)
pip install "pandas>=2.2" "pyarrow>=14"

# Conda — ships with sensible dependencies pinned
conda install -c conda-forge pandas pyarrow

# Engine extras worth knowing
pip install pandas[parquet,excel,plot,performance,html,sql]
# parquet      → pyarrow / fastparquet
# excel        → openpyxl / xlsxwriter
# performance  → numba, bottleneck, numexpr
# plot         → matplotlib

# Two display tweaks every notebook should set
python -c "
import pandas as pd
pd.options.display.max_columns = 50
pd.options.display.width       = 160
pd.options.future.infer_string = True   # 2.2+: opt into PyArrow strings
"

import pandas as pd

df = pd.DataFrame({
    "store": ["a","a","b","b","b"],
    "item":  ["x","y","x","y","x"],
    "qty":   [3, 1, 2, 5, 4],
    "price": [10, 5, 12, 7, 11],
})

# Single agg, single column
df.groupby("store")["qty"].sum()                    # Series indexed by store

# Multiple aggs, multiple columns — named aggregation (clean column names)
out = df.groupby("store").agg(
    total_qty   = ("qty",   "sum"),
    avg_price   = ("price", "mean"),
    rows        = ("item",  "count"),
)

# Transform: same shape as input — broadcast group stats back to rows
df["qty_share"] = df["qty"] / df.groupby("store")["qty"].transform("sum")

# Filter groups by a predicate on the group as a whole
df.groupby("store").filter(lambda g: g["qty"].sum() > 5)

# Apply arbitrary func per group (slower, last resort)
df.groupby("store").apply(
    lambda g: g.sort_values("price").head(1),
    include_groups=False,                            # 2.2+: don't pass keys
)

# Pivot table — group + reshape in one step
df.pivot_table(index="store", columns="item",
               values="qty", aggfunc="sum", fill_value=0)

import pandas as pd

orders = pd.DataFrame({"order_id":[1,2,3,4], "customer_id":[10,11,10,99]})
cust   = pd.DataFrame({"customer_id":[10,11,12], "name":["Ana","Bob","Cy"]})

# Inner join — only matched rows
orders.merge(cust, on="customer_id")

# Left join — keep all orders; missing customers become NaN
orders.merge(cust, on="customer_id", how="left", indicator=True)
# _merge column tells you which side a row came from

# Different key names left vs right
orders.merge(cust, left_on="customer_id", right_on="customer_id")

# Many-to-many → validate to catch accidental row explosions
orders.merge(cust, on="customer_id", validate="m:1")     # raises if violated

# Asof join — match each row to the *most recent* key on the right
trades = pd.DataFrame({
    "ts":[pd.Timestamp("09:00:01"), pd.Timestamp("09:00:03")],
    "ticker":["A","A"], "qty":[100, 200],
}).sort_values("ts")
quotes = pd.DataFrame({
    "ts":[pd.Timestamp("09:00:00"), pd.Timestamp("09:00:02")],
    "ticker":["A","A"], "px":[10.0, 10.5],
}).sort_values("ts")
pd.merge_asof(trades, quotes, on="ts", by="ticker",
              direction="backward", tolerance=pd.Timedelta("1s"))

import pandas as pd
import numpy as np

# Build a time index — tz-aware where it matters
idx = pd.date_range("2026-01-01", periods=90, freq="D", tz="UTC")
df  = pd.DataFrame({"price": np.random.default_rng(0).normal(100, 5, 90).cumsum()},
                   index=idx)

# Partial-string slicing on a DatetimeIndex
df["2026-01"]                                # whole January
df["2026-01-15":"2026-02-15"]                # any range

# Resample — group time-bucketed rows; pick label / closed convention
df.resample("W", label="right", closed="right").agg(
    open=("price", "first"), high=("price", "max"),
    low =("price", "min"),   close=("price", "last"),
)

# Rolling window + min_periods to keep early rows
df["sma_7"] = df["price"].rolling(7, min_periods=1).mean()
df["zscore_7"] = (df["price"] - df["sma_7"]) / df["price"].rolling(7).std()

# Time-zone conversion
df_ny = df.tz_convert("America/New_York")

# Period vs Timestamp
pd.period_range("2026Q1", periods=4, freq="Q")

# Shift + diff — produce lags safely
df["ret"] = df["price"].pct_change()                # = price.diff() / price.shift()
df["lag1"] = df["price"].shift(1)

# Load → clean → enrich → aggregate → persist. Chained, idiomatic, no copies.
import pandas as pd

orders = (
    pd.read_csv("orders.csv",
                parse_dates=["placed_at"],
                dtype={"customer_id": "int64", "sku": "string"},
                dtype_backend="pyarrow")             # nullable, fast
    .rename(columns=str.lower)
    .dropna(subset=["customer_id", "placed_at"])
    .assign(
        month   = lambda d: d["placed_at"].dt.to_period("M"),
        revenue = lambda d: d["qty"] * d["unit_price"],
    )
    .query("status == 'paid' and revenue > 0")
)

# Customer × month revenue, top 10 customers by total spend
cust_month = (orders
    .groupby(["customer_id", "month"], observed=True, as_index=False)
    .agg(orders=("order_id", "nunique"),
         revenue=("revenue", "sum"))
)

top10 = (cust_month
    .groupby("customer_id")["revenue"].sum()
    .nlargest(10).index
)

(cust_month[cust_month["customer_id"].isin(top10)]
    .pivot(index="month", columns="customer_id", values="revenue")
    .fillna(0)
    .to_parquet("top10_monthly.parquet", compression="zstd"))