Exercises

# ============================================================
# Drill 1 — Compute the 7-day moving average of daily revenue
# ============================================================
# Data: df['date'] (datetime64), df['total_cents']
#
# ANSWER:
import pandas as pd
daily = (df.set_index('date')['total_cents']
           .resample('D').sum())
ma7 = daily.rolling(7, min_periods=1).mean()
# Why min_periods=1: avoid NaN at the start when fewer than 7 days exist.

# ============================================================
# Drill 2 — Per-customer top 3 orders by amount
# ============================================================
# ANSWER:
top3 = (df.sort_values(['customer_id', 'total_cents'], ascending=[True, False])
          .groupby('customer_id')
          .head(3))
# OR with window function (more flexible):
df['rk'] = df.groupby('customer_id')['total_cents'].rank('dense', ascending=False)
top3 = df[df['rk'] <= 3]

# ============================================================
# Drill 3 — Merge that mysteriously drops rows
# ============================================================
# Setup:
left  = pd.DataFrame({'id': ['1', '2', '3']})
right = pd.DataFrame({'id': [1, 2], 'name': ['a', 'b']})
m = left.merge(right, on='id', how='left')
# m has NaN in 'name'. WHY?
#
# ANSWER: dtype mismatch. left.id is string; right.id is int. Cast first:
right['id'] = right['id'].astype(str)
m = left.merge(right, on='id', how='left', indicator=True)
# Always pass indicator=True until the join shape is verified.

# ============================================================
# Drill 4 — pivot for a heatmap
# ============================================================
# Per-day, per-hour count of events
#
# ANSWER:
df['hour'] = df['ts'].dt.hour
df['day']  = df['ts'].dt.date
pivot = (df.pivot_table(index='day', columns='hour', values='id', aggfunc='count')
           .fillna(0))
# Optionally normalise rows for a row-relative heatmap:
norm = pivot.div(pivot.sum(axis=1), axis=0)

# ============================================================
# Drill 5 — chunked processing of a huge CSV
# ============================================================
# Goal: aggregate a 20GB CSV without loading into memory
#
# ANSWER:
totals = {}
for chunk in pd.read_csv('orders.csv', chunksize=100_000,
                          usecols=['customer_id', 'total_cents'],
                          dtype={'customer_id': 'category'}):
    sub = chunk.groupby('customer_id', observed=True)['total_cents'].sum()
    for k, v in sub.items():
        totals[k] = totals.get(k, 0) + v
result = pd.Series(totals).sort_values(ascending=False)
# OR use duckdb:
# import duckdb
# duckdb.sql("SELECT customer_id, SUM(total_cents) FROM 'orders.csv' GROUP BY 1")

# ============================================================
# Drill 6 — round trip to parquet preserves types
# ============================================================
df['status'] = df['status'].astype('category')
df.to_parquet('snap.parquet', engine='pyarrow', compression='zstd')
back = pd.read_parquet('snap.parquet')
assert back['status'].dtype == 'category'   # parquet preserves dtype
# CSVs do not — switch to parquet for any analytics pipeline.

# ============================================================
# Scoring
# ============================================================
# 6 / 6 -> ship to production analytics
# 4 / 6 -> bookmark the cheatsheet
# < 4   -> read the pandas user guide for IO + groupby

# ============================================================
# Pitfalls
# ============================================================
# - .apply on rows: vectorise first; .apply only when nothing else works
# - chained indexing for assignment: use .loc / .at
# - resample('M').sum() vs ('ME').sum() — month-end ('ME') vs the deprecated 'M'
# - implicit datetime parsing: always pass format=... to read_csv when speed matters
# - merge without indicator: silent row loss

# Fill in: df[df['age'] ____ 18]