Group, join, write

What you will be able to do

• Aggregate with groupby and agg
• Merge two DataFrames on a key
• Write a result CSV to data/out

Why this matters for data work

Most notebook “insights” are group by and join. In pipelines you materialize those results as files or tables. Learn the verbs here; SQL will repeat them in the warehouse.

Concepts

groupby — split–apply–combine: df.groupby("store")["line_total"].sum().

agg — several metrics at once.

merge — join-like: pd.merge(left, right, on="sku", how="left").

to_csv — write without the index unless you need it: index=False.

Parquet is common in DE but needs extra engines (e.g. pyarrow). This lesson stays on CSV; add Parquet when a project requires it.

Practice on your machine

cd ~/dcubes/python-lab
source .venv/bin/activate
cat > data/raw/skus.csv << 'EOF'
sku,category
SKU-100,grocery
SKU-101,electronics
SKU-102,home
EOF
cat > pandas_reshape_lab.py << 'EOF'
import pandas as pd
from pathlib import Path

root = Path(__file__).resolve().parent
orders = pd.read_csv(root / "data" / "raw" / "orders-frames.csv")
skus = pd.read_csv(root / "data" / "raw" / "skus.csv")

orders = orders.dropna(subset=["quantity"]).copy()
orders["quantity"] = orders["quantity"].astype(int)
orders["line_total"] = orders["quantity"] * orders["unit_price"]

joined = orders.merge(skus, on="sku", how="left")
by_cat = (
    joined.groupby("category", as_index=False)
    .agg(orders=("order_id", "count"), revenue=("line_total", "sum"))
    .sort_values("revenue", ascending=False)
)
print(by_cat)

out = root / "data" / "out" / "revenue_by_category.csv"
out.parent.mkdir(parents=True, exist_ok=True)
by_cat.to_csv(out, index=False)
print("wrote", out)
EOF
python pandas_reshape_lab.py
cat data/out/revenue_by_category.csv

What you should see

• A small table of category, order count, and revenue.
• A CSV under data/out with a header row.

Common mistakes

• Forgetting as_index=False when you want category as a column.
• Inner join by accident dropping unmatched keys — prefer explicit how=.
• Writing with default index — creates an extra column on reload.

Next

Postgres from Python