Python Notebook Patterns

What this is: Common pandas patterns for analytical work in a Jupyter or similar notebook environment. Each block is designed to be adapted and extended — not run as-is.
Prerequisites: Python 3.9+, pandas, numpy, matplotlib/seaborn. Install via pip install pandas numpy matplotlib seaborn.
Covered in: The Data Foundations Guide on biztechprimer.com.

1. Load, Inspect, and Validate a Dataset

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

# ── Load ──────────────────────────────────────────────────────────────────
# From CSV
df = pd.read_csv("data/customers.csv", parse_dates=["created_at", "updated_at"])

# From a database (via SQLAlchemy or similar)
# from sqlalchemy import create_engine
# engine = create_engine("postgresql+psycopg2://user:pass@host/db")
# df = pd.read_sql("SELECT * FROM customers LIMIT 100000", engine)

# From Snowflake via snowflake-connector-python
# import snowflake.connector
# conn = snowflake.connector.connect(user=USER, password=PW, account=ACCOUNT, warehouse=WH, database=DB, schema=SCHEMA)
# df = pd.read_sql("SELECT * FROM customers", conn)

# ── Inspect ───────────────────────────────────────────────────────────────
print(f"Shape: {df.shape}")                 # (rows, columns)
print("\nColumn types:")
print(df.dtypes)

print("\nFirst 5 rows:")
display(df.head())

print("\nDescriptive statistics:")
display(df.describe(include='all'))          # include='all' covers non-numerics too

# ── Validate ──────────────────────────────────────────────────────────────
print("\nMissing values:")
missing = df.isnull().sum()
missing_pct = (missing / len(df) * 100).round(1)
print(pd.DataFrame({'count': missing, 'pct': missing_pct}).query('count > 0'))

print(f"\nDuplicate rows: {df.duplicated().sum()}")

# Check date range
if 'created_at' in df.columns:
    print(f"\nDate range: {df['created_at'].min()} → {df['created_at'].max()}")

2. Clean and Standardize

# ── Column naming ─────────────────────────────────────────────────────────
# Normalize all column names to snake_case
df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(r'[\s\-/]+', '_', regex=True)
    .str.replace(r'[^a-z0-9_]', '', regex=True)
)

# ── Handle nulls ──────────────────────────────────────────────────────────
# Drop rows where key fields are null
df = df.dropna(subset=['customer_id', 'email'])

# Fill nulls with defaults (be intentional — don't fill blindly)
df['segment'] = df['segment'].fillna('unknown')
df['revenue'] = df['revenue'].fillna(0)

# ── Deduplicate ───────────────────────────────────────────────────────────
# Keep most recent record per customer
df = (
    df
    .sort_values('updated_at', ascending=False)
    .drop_duplicates(subset=['customer_id'], keep='first')
    .reset_index(drop=True)
)

# ── Type casting ──────────────────────────────────────────────────────────
df['created_at'] = pd.to_datetime(df['created_at'], utc=True)
df['revenue'] = pd.to_numeric(df['revenue'], errors='coerce')  # coerce = NaN on parse failure
df['customer_id'] = df['customer_id'].astype(str)

# ── String cleaning ───────────────────────────────────────────────────────
df['email'] = df['email'].str.strip().str.lower()
df['plan_name'] = df['plan_name'].str.strip().str.title()

print(f"Clean dataset: {df.shape[0]:,} rows, {df.shape[1]} columns")

3. Aggregate and Summarize

# ── Group by + aggregate ──────────────────────────────────────────────────
# Named aggregations (pandas ≥ 0.25)
summary = df.groupby('plan_name').agg(
    customer_count=('customer_id', 'nunique'),
    total_revenue=('mrr', 'sum'),
    avg_mrr=('mrr', 'mean'),
    median_mrr=('mrr', 'median'),
    p90_mrr=('mrr', lambda x: x.quantile(0.9)),
    churn_count=('is_churned', 'sum'),
).reset_index()

# Add derived columns
summary['avg_revenue_per_customer'] = summary['total_revenue'] / summary['customer_count']
summary['churn_rate'] = (summary['churn_count'] / summary['customer_count'] * 100).round(1)

# Sort and display
summary = summary.sort_values('total_revenue', ascending=False)
display(summary)

# ── Time-series rollup ────────────────────────────────────────────────────
# Monthly aggregation
df['month'] = df['created_at'].dt.to_period('M')

monthly = (
    df
    .groupby('month')
    .agg(
        new_customers=('customer_id', 'nunique'),
        new_mrr=('mrr', 'sum'),
    )
    .reset_index()
)

monthly['month'] = monthly['month'].dt.to_timestamp()

# Rolling 3-month average
monthly['mrr_3mo_avg'] = monthly['new_mrr'].rolling(window=3, min_periods=1).mean()

4. Cohort Retention in Pandas

# ── Cohort retention from activity data ───────────────────────────────────
# Assumes: events df with (user_id, event_date) columns

# Step 1: Assign cohort (month of first event)
first_activity = (
    events
    .groupby('user_id')['event_date']
    .min()
    .reset_index()
    .rename(columns={'event_date': 'cohort_date'})
)
first_activity['cohort_month'] = first_activity['cohort_date'].dt.to_period('M')

# Step 2: Join cohort back to events, calculate months since signup
events_with_cohort = events.merge(first_activity[['user_id', 'cohort_month']], on='user_id')
events_with_cohort['event_month'] = events_with_cohort['event_date'].dt.to_period('M')
events_with_cohort['months_since_signup'] = (
    events_with_cohort['event_month'] - events_with_cohort['cohort_month']
).apply(lambda x: x.n)

# Step 3: Count active users per cohort-month pair
cohort_data = (
    events_with_cohort
    .groupby(['cohort_month', 'months_since_signup'])['user_id']
    .nunique()
    .reset_index()
    .rename(columns={'user_id': 'active_users'})
)

# Step 4: Get cohort sizes (month 0)
cohort_sizes = cohort_data.query('months_since_signup == 0')[['cohort_month', 'active_users']].rename(columns={'active_users': 'cohort_size'})
cohort_data = cohort_data.merge(cohort_sizes, on='cohort_month')

# Step 5: Calculate retention %
cohort_data['retention_pct'] = (cohort_data['active_users'] / cohort_data['cohort_size'] * 100).round(1)

# Step 6: Pivot to retention triangle
retention_pivot = cohort_data.pivot_table(
    index='cohort_month',
    columns='months_since_signup',
    values='retention_pct'
)

# Step 7: Visualize as heatmap
plt.figure(figsize=(14, 8))
sns.heatmap(
    retention_pivot,
    annot=True,
    fmt='.0f',
    cmap='Blues',
    vmin=0,
    vmax=100,
    linewidths=0.5,
    cbar_kws={'label': 'Retention %'}
)
plt.title('Monthly Cohort Retention (%)', fontsize=14, pad=12)
plt.xlabel('Months Since Signup')
plt.ylabel('Cohort (Signup Month)')
plt.tight_layout()
plt.show()

5. Basic Visualizations

# ── Set style ─────────────────────────────────────────────────────────────
plt.style.use('seaborn-v0_8-whitegrid')
COLORS = ['#2D6B82', '#6EA8BE', '#1A2238', '#A57A1F', '#2E7D5B']

# ── Bar chart — distribution by category ──────────────────────────────────
fig, ax = plt.subplots(figsize=(10, 5))
category_counts = df['plan_name'].value_counts()
ax.barh(category_counts.index, category_counts.values, color=COLORS[0])
ax.set_xlabel('Customer Count')
ax.set_title('Customers by Plan')
ax.invert_yaxis()  # Largest at top
plt.tight_layout()
plt.show()

# ── Line chart — metric over time ─────────────────────────────────────────
fig, ax = plt.subplots(figsize=(12, 5))
ax.plot(monthly['month'], monthly['new_mrr'], color=COLORS[0], linewidth=2, label='Monthly MRR')
ax.plot(monthly['month'], monthly['mrr_3mo_avg'], color=COLORS[3], linewidth=1.5, linestyle='--', label='3-mo rolling avg')
ax.set_title('New MRR by Month')
ax.set_xlabel('')
ax.set_ylabel('MRR ($)')
ax.legend()
plt.tight_layout()
plt.show()

# ── Histogram — distribution of a metric ──────────────────────────────────
fig, ax = plt.subplots(figsize=(10, 5))
ax.hist(df['mrr'].clip(upper=df['mrr'].quantile(0.95)), bins=40, color=COLORS[0], edgecolor='white')
ax.axvline(df['mrr'].median(), color=COLORS[3], linewidth=1.5, linestyle='--', label=f"Median: ${df['mrr'].median():,.0f}")
ax.axvline(df['mrr'].mean(), color='red', linewidth=1.5, linestyle=':', label=f"Mean: ${df['mrr'].mean():,.0f}")
ax.set_title('Distribution of MRR per Customer (clipped at p95)')
ax.set_xlabel('MRR ($)')
ax.set_ylabel('Count')
ax.legend()
plt.tight_layout()
plt.show()

6. Export Results

# ── Save to CSV ───────────────────────────────────────────────────────────
summary.to_csv("output/plan_summary.csv", index=False)
print("Saved: output/plan_summary.csv")

# ── Save to Excel with multiple sheets ───────────────────────────────────
with pd.ExcelWriter("output/analysis_results.xlsx", engine='openpyxl') as writer:
    summary.to_excel(writer, sheet_name='Plan Summary', index=False)
    monthly.to_excel(writer, sheet_name='Monthly MRR', index=False)
    cohort_data.to_excel(writer, sheet_name='Cohort Retention', index=False)

print("Saved: output/analysis_results.xlsx")

# ── Preview final output ───────────────────────────────────────────────────
print(f"\nFinal dataset: {summary.shape[0]} rows × {summary.shape[1]} columns")
display(summary.head(10))

Covered in the Data Foundations Guide on biztechprimer.com.