| name | analytics-hand-skill |
| version | 1.0.0 |
| description | Expert knowledge for AI data analytics -- statistical methods, visualization best practices, pandas reference, and reporting patterns |
| runtime | prompt_only |
Data Analytics Expert Knowledge
pandas Quick Reference
Data Loading
import pandas as pd
df = pd.read_csv('data.csv')
df = pd.read_csv('data.csv', parse_dates=['date_col'], index_col='id')
df = pd.read_json('data.json')
df = pd.read_json('data.json', orient='records')
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']})
Data Inspection
df.shape
df.dtypes
df.info()
df.describe()
df.head(10)
df.isnull().sum()
df.duplicated().sum()
df.nunique()
Data Cleaning
df.dropna()
df.fillna(0)
df.fillna(df.mean())
df['col'].interpolate()
df.drop_duplicates()
df.drop_duplicates(subset=['col1', 'col2'])
df['col'] = df['col'].astype(int)
df['date'] = pd.to_datetime(df['date'])
df['cat'] = df['cat'].astype('category')
Q1 = df['col'].quantile(0.25)
Q3 = df['col'].quantile(0.75)
IQR = Q3 - Q1
df = df[(df['col'] >= Q1 - 1.5*IQR) & (df['col'] <= Q3 + 1.5*IQR)]
Aggregation & Grouping
df.groupby('category').agg({'value': ['mean', 'sum', 'count']})
pd.pivot_table(df, values='value', index='row_cat', columns='col_cat', aggfunc='mean')
pd.crosstab(df['cat1'], df['cat2'])
df['rolling_mean'] = df['value'].rolling(window=7).mean()
df['pct_change'] = df['value'].pct_change()
Time Series
df.set_index('date', inplace=True)
df.resample('W').mean()
df.resample('M').sum()
df.resample('Q').count()
pd.date_range(start='2025-01-01', periods=30, freq='D')
df['prev_value'] = df['value'].shift(1)
df['next_value'] = df['value'].shift(-1)
Visualization Best Practices
matplotlib + seaborn Reference
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import seaborn as sns
sns.set_theme(style='whitegrid')
plt.rcParams['figure.figsize'] = (10, 6)
Chart Selection Guide
| Data Type | Question | Chart Type |
|---|
| Categorical | Comparison | Bar chart |
| Categorical | Proportion | Pie chart (if <6 categories) |
| Numerical | Distribution | Histogram / Box plot |
| Two numerical | Relationship | Scatter plot |
| Time series | Trend | Line chart |
| Matrix | Correlation | Heatmap |
| Categories + values | Comparison | Grouped bar / Stacked bar |
| Geographical | Location | Map / Choropleth |
Chart Templates
Bar Chart:
fig, ax = plt.subplots(figsize=(10, 6))
data = df['category'].value_counts()
data.plot(kind='bar', ax=ax, color='steelblue')
ax.set_title('Distribution by Category', fontsize=14, fontweight='bold')
ax.set_xlabel('Category')
ax.set_ylabel('Count')
plt.xticks(rotation=45, ha='right')
plt.tight_layout()
plt.savefig('bar_chart.png', dpi=150, bbox_inches='tight')
plt.close()
Line Chart (Time Series):
fig, ax = plt.subplots(figsize=(12, 6))
ax.plot(df.index, df['value'], linewidth=2, color='steelblue')
ax.fill_between(df.index, df['value'], alpha=0.1, color='steelblue')
ax.set_title('Trend Over Time', fontsize=14, fontweight='bold')
ax.set_xlabel('Date')
ax.set_ylabel('Value')
plt.tight_layout()
plt.savefig('line_chart.png', dpi=150, bbox_inches='tight')
plt.close()
Correlation Heatmap:
fig, ax = plt.subplots(figsize=(10, 8))
corr = df.select_dtypes(include='number').corr()
sns.heatmap(corr, annot=True, fmt='.2f', cmap='RdBu_r', center=0, ax=ax)
ax.set_title('Correlation Matrix', fontsize=14, fontweight='bold')
plt.tight_layout()
plt.savefig('heatmap.png', dpi=150, bbox_inches='tight')
plt.close()
Scatter Plot:
fig, ax = plt.subplots(figsize=(10, 6))
ax.scatter(df['x'], df['y'], alpha=0.6, edgecolors='black', linewidth=0.5)
ax.set_title('X vs Y', fontsize=14, fontweight='bold')
ax.set_xlabel('X Variable')
ax.set_ylabel('Y Variable')
plt.tight_layout()
plt.savefig('scatter.png', dpi=150, bbox_inches='tight')
plt.close()
Visualization Do's and Don'ts
Do:
- Start y-axis at 0 for bar charts
- Use consistent colors across related charts
- Label axes clearly with units
- Add titles that describe the insight, not just the data
- Use appropriate scales (log scale for exponential data)
Don't:
- Use 3D charts (distorts perception)
- Use more than 6-7 colors in one chart
- Truncate axes to exaggerate differences
- Use pie charts for more than 5 categories
- Add unnecessary chart junk (borders, backgrounds, grids)
Statistical Methods
Descriptive Statistics
| Measure | pandas | Purpose |
|---|
| Mean | df['col'].mean() | Central tendency |
| Median | df['col'].median() | Robust central tendency |
| Std Dev | df['col'].std() | Variability |
| Skewness | df['col'].skew() | Distribution symmetry |
| Kurtosis | df['col'].kurtosis() | Distribution tails |
| Percentiles | df['col'].quantile([0.25, 0.5, 0.75]) | Distribution spread |
Correlation Analysis
df['col1'].corr(df['col2'])
df['col1'].corr(df['col2'], method='spearman')
df.select_dtypes(include='number').corr()
Interpretation:
- |r| > 0.7: Strong correlation
- 0.4 < |r| < 0.7: Moderate correlation
- |r| < 0.4: Weak correlation
- Correlation != Causation
Hypothesis Testing (scipy)
from scipy import stats
t_stat, p_value = stats.ttest_ind(group1, group2)
chi2, p_value, dof, expected = stats.chi2_contingency(contingency_table)
u_stat, p_value = stats.mannwhitneyu(group1, group2, alternative='two-sided')
f_stat, p_value = stats.f_oneway(group1, group2, group3)
shapiro_stat, p_value = stats.shapiro(data)
Statistical Significance Decision Guide
Test selection flowchart:
| Data Situation | Normal Distribution? | Test to Use |
|---|
| Compare 2 group means | Yes | Independent t-test (ttest_ind) |
| Compare 2 group means | No | Mann-Whitney U (mannwhitneyu) |
| Compare 3+ group means | Yes | One-way ANOVA (f_oneway) |
| Compare 3+ group means | No | Kruskal-Wallis (kruskal) |
| Compare paired samples | Yes | Paired t-test (ttest_rel) |
| Compare paired samples | No | Wilcoxon signed-rank (wilcoxon) |
| Test categorical independence | N/A | Chi-squared (chi2_contingency) |
| Test correlation | Yes | Pearson (pearsonr) |
| Test correlation | No | Spearman (spearmanr) |
P-value interpretation:
| p-value | Interpretation | Action |
|---|
| p < 0.01 | Strong evidence against null hypothesis | Report as statistically significant |
| 0.01 ≤ p < 0.05 | Moderate evidence | Report as significant with caveat |
| 0.05 ≤ p < 0.10 | Weak evidence | Report as marginally significant |
| p ≥ 0.10 | Insufficient evidence | Do not claim significance |
Practical significance — always report effect size:
def cohens_d(group1, group2):
n1, n2 = len(group1), len(group2)
var1, var2 = group1.var(), group2.var()
pooled_std = ((n1 - 1) * var1 + (n2 - 1) * var2) / (n1 + n2 - 2)
return (group1.mean() - group2.mean()) / (pooled_std ** 0.5)
Sample size awareness:
- n < 30: Use non-parametric tests; results are exploratory
- 30 ≤ n < 100: Parametric tests OK if normality holds; moderate confidence
- n ≥ 100: Central Limit Theorem applies; high confidence in parametric tests
- Always report sample size alongside p-values
Confidence threshold mapping:
| Setting | p-value threshold | Minimum effect size | Minimum sample size |
|---|
| High | p < 0.01 | Cohen's d ≥ 0.5 | n ≥ 100 |
| Medium | p < 0.05 | Cohen's d ≥ 0.3 | n ≥ 30 |
| Low | p < 0.10 | Any | Any |
Report Structure Best Practices
CRISP-DM Framework
- Business Understanding: What question are we answering?
- Data Understanding: What data do we have? Quality?
- Data Preparation: Cleaning, transformation, feature engineering
- Modeling: Statistical analysis, ML models
- Evaluation: Are results valid and useful?
- Deployment: Reports, dashboards, recommendations
Insight Hierarchy
Level 1: What happened (descriptive)
"Revenue increased 15% last quarter"
Level 2: Why it happened (diagnostic)
"Revenue increase driven by 30% growth in enterprise segment"
Level 3: What will happen (predictive)
"Based on current trends, Q2 revenue projected at $X"
Level 4: What to do (prescriptive)
"Invest in enterprise sales team to capitalize on growth trajectory"
Data Quality Assessment Template
| Dimension | Score | Details |
|-----------|-------|---------|
| Completeness | 85% | 15% missing values in 'email' column |
| Accuracy | High | Validated against source system |
| Consistency | Medium | Date formats vary across sources |
| Timeliness | Current | Data refreshed daily |
| Uniqueness | 99% | 1% duplicate records found |
Worked Examples
Example 1: E-commerce Sales Analysis
Goal: Analyze 12 months of order data to identify revenue drivers, customer segments, and growth trends.
Step 1 — Load and clean
import pandas as pd
import numpy as np
df = pd.read_csv('orders.csv', parse_dates=['order_date'])
print(f"Rows: {len(df):,} Columns: {df.shape[1]}")
print(df.isnull().sum()[df.isnull().sum() > 0])
df = df.dropna(subset=['customer_id', 'order_total'])
df['order_total'] = df['order_total'].clip(lower=0)
df['order_month'] = df['order_date'].dt.to_period('M')
Step 2 — Revenue trend analysis
monthly = (
df.groupby('order_month')
.agg(revenue=('order_total', 'sum'),
orders=('order_id', 'nunique'),
customers=('customer_id', 'nunique'))
.reset_index()
)
monthly['aov'] = monthly['revenue'] / monthly['orders']
monthly['revenue_mom'] = monthly['revenue'].pct_change()
fig, axes = plt.subplots(2, 1, figsize=(12, 8), sharex=True)
axes[0].bar(monthly['order_month'].astype(str), monthly['revenue'], color='steelblue')
axes[0].set_title('Monthly Revenue', fontsize=14, fontweight='bold')
axes[0].set_ylabel('Revenue ($)')
axes[1].plot(monthly['order_month'].astype(str), monthly['aov'], marker='o', color='coral')
axes[1].set_title('Average Order Value', fontsize=14, fontweight='bold')
axes[1].set_ylabel('AOV ($)')
plt.xticks(rotation=45, ha='right')
plt.tight_layout()
plt.savefig('revenue_trend.png', dpi=150, bbox_inches='tight')
plt.close()
Step 3 — Customer segmentation (RFM)
snapshot_date = df['order_date'].max() + pd.Timedelta(days=1)
rfm = df.groupby('customer_id').agg(
recency=('order_date', lambda x: (snapshot_date - x.max()).days),
frequency=('order_id', 'nunique'),
monetary=('order_total', 'sum')
)
for col in ['recency', 'frequency', 'monetary']:
labels = [4, 3, 2, 1] if col == 'recency' else [1, 2, 3, 4]
rfm[f'{col}_score'] = pd.qcut(rfm[col], q=4, labels=labels, duplicates='drop')
rfm['rfm_score'] = (rfm['recency_score'].astype(int)
+ rfm['frequency_score'].astype(int)
+ rfm['monetary_score'].astype(int))
def segment(row):
r, f, m = int(row['recency_score']), int(row['frequency_score']), int(row['monetary_score'])
if r >= 3 and f >= 3:
return 'Champions'
elif r >= 3 and f < 3:
return 'New / Promising'
elif r < 3 and f >= 3:
return 'At Risk'
else:
return 'Needs Attention'
rfm['segment'] = rfm.apply(segment, axis=1)
print(rfm.groupby('segment').agg(
count=('monetary', 'size'),
avg_revenue=('monetary', 'mean'),
avg_frequency=('frequency', 'mean')
).sort_values('avg_revenue', ascending=False))
Step 4 — Cohort retention analysis
df['cohort'] = df.groupby('customer_id')['order_date'].transform('min').dt.to_period('M')
df['order_period'] = df['order_date'].dt.to_period('M')
df['cohort_index'] = (df['order_period'] - df['cohort']).apply(lambda x: x.n)
cohort_table = (
df.groupby(['cohort', 'cohort_index'])['customer_id']
.nunique()
.reset_index()
.pivot(index='cohort', columns='cohort_index', values='customer_id')
)
retention = cohort_table.div(cohort_table[0], axis=0) * 100
fig, ax = plt.subplots(figsize=(14, 8))
sns.heatmap(retention, annot=True, fmt='.0f', cmap='YlOrRd_r', ax=ax)
ax.set_title('Cohort Retention (% of original customers)', fontsize=14, fontweight='bold')
ax.set_xlabel('Months Since First Purchase')
ax.set_ylabel('Cohort')
plt.tight_layout()
plt.savefig('cohort_retention.png', dpi=150, bbox_inches='tight')
plt.close()
Example 2: A/B Test Analysis
Goal: Evaluate whether a new checkout flow (variant B) improves conversion rate over the existing flow (variant A).
Step 1 — Sample size calculation (pre-test)
from scipy import stats
import numpy as np
baseline_rate = 0.12
mde = 0.02
alpha = 0.05
power = 0.80
p1 = baseline_rate
p2 = baseline_rate + mde
p_avg = (p1 + p2) / 2
z_alpha = stats.norm.ppf(1 - alpha / 2)
z_beta = stats.norm.ppf(power)
n_per_group = ((z_alpha * np.sqrt(2 * p_avg * (1 - p_avg))
+ z_beta * np.sqrt(p1 * (1 - p1) + p2 * (1 - p2))) ** 2
/ (p2 - p1) ** 2)
print(f"Required sample size per group: {int(np.ceil(n_per_group)):,}")
print(f"Total required: {int(np.ceil(n_per_group)) * 2:,}")
Step 2 — Run the test and collect results
ab = pd.read_csv('ab_test_results.csv')
summary = ab.groupby('variant').agg(
visitors=('user_id', 'nunique'),
conversions=('converted', 'sum')
)
summary['conversion_rate'] = summary['conversions'] / summary['visitors']
print(summary)
Step 3 — Statistical significance
a = ab[ab['variant'] == 'A']
b = ab[ab['variant'] == 'B']
contingency = pd.crosstab(ab['variant'], ab['converted'])
chi2, p_value, dof, expected = stats.chi2_contingency(contingency)
from statsmodels.stats.proportion import proportions_ztest
successes = [summary.loc['B', 'conversions'], summary.loc['A', 'conversions']]
trials = [summary.loc['B', 'visitors'], summary.loc['A', 'visitors']]
z_stat, p_val = proportions_ztest(successes, trials, alternative='larger')
print(f"Z-statistic: {z_stat:.4f}")
print(f"P-value: {p_val:.4f}")
print(f"Significant: {'Yes' if p_val < 0.05 else 'No'} (at alpha=0.05)")
Step 4 — Effect size and confidence interval
p_a = summary.loc['A', 'conversion_rate']
p_b = summary.loc['B', 'conversion_rate']
n_a = summary.loc['A', 'visitors']
n_b = summary.loc['B', 'visitors']
lift = (p_b - p_a) / p_a
se_diff = np.sqrt(p_a * (1 - p_a) / n_a + p_b * (1 - p_b) / n_b)
ci_lower = (p_b - p_a) - 1.96 * se_diff
ci_upper = (p_b - p_a) + 1.96 * se_diff
print(f"Control rate: {p_a:.4f}")
print(f"Variant rate: {p_b:.4f}")
print(f"Absolute lift: {p_b - p_a:+.4f}")
print(f"Relative lift: {lift:+.2%}")
print(f"95% CI for diff: [{ci_lower:+.4f}, {ci_upper:+.4f}]")
Step 5 — Recommendation template
## A/B Test Report: New Checkout Flow
| Metric | Control (A) | Variant (B) |
|---------------------|-------------|-------------|
| Visitors | 15,204 | 15,198 |
| Conversions | 1,824 | 2,127 |
| Conversion Rate | 12.00% | 13.99% |
**Result**: Statistically significant (p = 0.0003, alpha = 0.05)
**Lift**: +1.99pp absolute / +16.6% relative
**95% CI**: [+0.90pp, +3.08pp]
**Recommendation**: Deploy variant B. The effect is both statistically
and practically significant with a lower bound above the +1pp threshold.
Example 3: Customer Churn Analysis
Goal: Identify which factors most strongly predict customer churn and quantify their relative importance.
Step 1 — Feature engineering
df = pd.read_csv('customers.csv')
features = df.copy()
features['tenure_months'] = (pd.Timestamp.now() - pd.to_datetime(df['signup_date'])).dt.days / 30
features['support_tickets_per_month'] = df['total_tickets'] / features['tenure_months'].clip(lower=1)
features['avg_session_minutes'] = df['total_session_minutes'] / df['total_sessions'].clip(lower=1)
features['days_since_last_login'] = (pd.Timestamp.now() - pd.to_datetime(df['last_login'])).dt.days
features['has_premium'] = (df['plan'] == 'premium').astype(int)
feature_cols = [
'tenure_months', 'support_tickets_per_month', 'avg_session_minutes',
'days_since_last_login', 'has_premium', 'monthly_spend', 'num_features_used'
]
Step 2 — Correlation analysis
churn_corr = features[feature_cols + ['churned']].corr()['churned'].drop('churned').sort_values()
fig, ax = plt.subplots(figsize=(8, 5))
churn_corr.plot(kind='barh', ax=ax, color=['coral' if x > 0 else 'steelblue' for x in churn_corr])
ax.set_title('Feature Correlation with Churn', fontsize=14, fontweight='bold')
ax.set_xlabel('Pearson Correlation')
ax.axvline(x=0, color='black', linewidth=0.5)
plt.tight_layout()
plt.savefig('churn_correlations.png', dpi=150, bbox_inches='tight')
plt.close()
Step 3 — Key driver identification via group comparison
churned = features[features['churned'] == 1]
retained = features[features['churned'] == 0]
comparison = []
for col in feature_cols:
t_stat, p_val = stats.ttest_ind(churned[col].dropna(), retained[col].dropna())
d = cohens_d(churned[col].dropna(), retained[col].dropna())
comparison.append({
'feature': col,
'churned_mean': churned[col].mean(),
'retained_mean': retained[col].mean(),
'diff_pct': (churned[col].mean() - retained[col].mean()) / retained[col].mean() * 100,
'cohens_d': abs(d),
'p_value': p_val,
'significant': p_val < 0.05
})
result = pd.DataFrame(comparison).sort_values('cohens_d', ascending=False)
print(result.to_string(index=False))
Step 4 — Interpret and report
## Churn Driver Analysis
**Top 3 factors distinguishing churned vs. retained customers:**
| Factor | Churned (avg) | Retained (avg) | Diff | Effect Size |
|----------------------------|---------------|----------------|----------|-------------|
| Days since last login | 34.2 | 8.7 | +293% | Large |
| Support tickets per month | 2.8 | 0.9 | +211% | Large |
| Number of features used | 3.1 | 7.4 | -58% | Medium |
**Actionable insights:**
1. Customers inactive >14 days are 4x more likely to churn -- trigger re-engagement email at day 10
2. High support ticket rate signals frustration -- escalate accounts with >2 tickets/month to success team
3. Low feature adoption correlates with churn -- implement onboarding flow targeting unused features
Advanced pandas Patterns
Window Functions
df['cumulative_avg'] = df['value'].expanding().mean()
df['cumulative_max'] = df['value'].expanding().max()
df['ewma_7'] = df['value'].ewm(span=7).mean()
df['ewma_a'] = df['value'].ewm(alpha=0.3).mean()
df['rolling_avg'] = df['value'].rolling(window=30, min_periods=5).mean()
df['rolling_pctile'] = df['value'].rolling(90).rank(pct=True)
Multi-Index Operations
multi = df.groupby(['region', 'product']).agg(
revenue=('amount', 'sum'),
units=('quantity', 'sum')
)
multi.loc['North']
multi.loc[('North', 'Widget')]
multi.xs('Widget', level='product')
multi = multi.swaplevel().sort_index()
flat = multi.reset_index()
wide = multi['revenue'].unstack(level='product')
long = wide.stack()
Merge and Join Patterns
merged = orders.merge(customers, on='customer_id', how='inner')
merged = orders.merge(customers, on='customer_id', how='left', indicator=True)
unmatched = merged[merged['_merge'] == 'left_only']
merged = df1.merge(df2, on=['date', 'region'], how='left')
merged = orders.merge(products, left_on='prod_id', right_on='product_id')
anti = df_a.merge(df_b, on='key', how='left', indicator=True)
anti = anti[anti['_merge'] == 'left_only'].drop(columns='_merge')
df_prev = df[['customer_id', 'order_date', 'amount']].rename(
columns={'order_date': 'prev_date', 'amount': 'prev_amount'}
)
df_with_prev = df.merge(df_prev, on='customer_id', how='left')
df_with_prev = df_with_prev[df_with_prev['prev_date'] < df_with_prev['order_date']]
Apply and Transform
df['group_mean'] = df.groupby('category')['value'].transform('mean')
df['pct_of_group'] = df['value'] / df.groupby('category')['value'].transform('sum')
df['z_within_group'] = df.groupby('category')['value'].transform(
lambda x: (x - x.mean()) / x.std()
)
def top_n(group, n=3):
return group.nlargest(n, 'value')
top3_per_category = df.groupby('category', group_keys=False).apply(top_n, n=3)
df['result'] = df.apply(lambda row: row['a'] * row['b'] + row['c'], axis=1)
df['result'] = df['a'] * df['b'] + df['c']
df['tier'] = np.where(df['revenue'] > 10000, 'high', 'low')
conditions = [
df['revenue'] > 10000,
df['revenue'] > 5000,
df['revenue'] > 0,
]
choices = ['high', 'medium', 'low']
df['tier'] = np.select(conditions, choices, default='none')
Memory Optimization for Large Datasets
print(df.memory_usage(deep=True).sum() / 1024**2, "MB")
df['int_col'] = pd.to_numeric(df['int_col'], downcast='integer')
df['float_col'] = pd.to_numeric(df['float_col'], downcast='float')
for col in df.select_dtypes(include='object'):
if df[col].nunique() / len(df) < 0.5:
df[col] = df[col].astype('category')
chunks = pd.read_csv('huge_file.csv', chunksize=100_000)
results = []
for chunk in chunks:
processed = chunk.groupby('category')['value'].sum()
results.append(processed)
final = pd.concat(results).groupby(level=0).sum()
dtypes = {
'id': 'int32',
'category': 'category',
'value': 'float32',
'flag': 'bool'
}
df = pd.read_csv('data.csv', dtype=dtypes)
df = pd.read_csv('data.csv', engine='pyarrow', dtype_backend='pyarrow')
Dashboard and Reporting Patterns
Executive Dashboard Template
import matplotlib.pyplot as plt
import matplotlib.gridspec as gridspec
from matplotlib.patches import FancyBboxPatch
def executive_dashboard(kpis, trend_df, comparison_df, output='dashboard.png'):
"""
kpis: dict with keys like {'Revenue': '$1.2M', 'Growth': '+15%', ...}
trend_df: DataFrame with 'date' and 'value' columns
comparison_df: DataFrame with 'category' and 'current'/'previous' columns
"""
fig = plt.figure(figsize=(16, 10))
gs = gridspec.GridSpec(3, len(kpis), hspace=0.4, wspace=0.3)
for i, (label, value) in enumerate(kpis.items()):
ax = fig.add_subplot(gs[0, i])
ax.text(0.5, 0.6, value, ha='center', va='center',
fontsize=28, fontweight='bold', color='#2c3e50')
ax.text(0.5, 0.2, label, ha='center', va='center',
fontsize=12, color='#7f8c8d')
ax.set_xlim(0, 1)
ax.set_ylim(0, 1)
ax.axis('off')
rect = FancyBboxPatch((0.05, 0.05), 0.9, 0.9, boxstyle="round,pad=0.05",
facecolor='#f8f9fa', edgecolor='#dee2e6')
ax.add_patch(rect)
ax_trend = fig.add_subplot(gs[1, :])
ax_trend.plot(trend_df['date'], trend_df['value'], linewidth=2, color='steelblue')
ax_trend.fill_between(trend_df['date'], trend_df['value'], alpha=0.1, color='steelblue')
ax_trend.set_title('Trend Over Time', fontsize=13, fontweight='bold')
ax_trend.set_ylabel('Value')
ax_comp = fig.add_subplot(gs[2, :])
x = range(len(comparison_df))
width = 0.35
ax_comp.bar([i - width/2 for i in x], comparison_df['previous'], width,
label='Previous', color='#bdc3c7')
ax_comp.bar([i + width/2 for i in x], comparison_df['current'], width,
label='Current', color='steelblue')
ax_comp.set_xticks(list(x))
ax_comp.set_xticklabels(comparison_df['category'], rotation=45, ha='right')
ax_comp.set_title('Current vs. Previous Period', fontsize=13, fontweight='bold')
ax_comp.legend()
plt.savefig(output, dpi=150, bbox_inches='tight', facecolor='white')
plt.close()
Weekly Metrics Report Template
def weekly_report(df, date_col='date', metric_col='value', group_col=None):
"""Generate a standard weekly metrics summary."""
df[date_col] = pd.to_datetime(df[date_col])
df['week'] = df[date_col].dt.isocalendar().week.astype(int)
df['year'] = df[date_col].dt.year
current_week = df['week'].max()
prev_week = current_week - 1
curr = df[df['week'] == current_week]
prev = df[df['week'] == prev_week]
report = {
'period': f"Week {current_week}",
'total': curr[metric_col].sum(),
'mean': curr[metric_col].mean(),
'median': curr[metric_col].median(),
'wow_change': (curr[metric_col].sum() - prev[metric_col].sum())
/ prev[metric_col].sum() * 100
if prev[metric_col].sum() != 0 else None,
}
if group_col:
report['by_group'] = curr.groupby(group_col)[metric_col].agg(['sum', 'mean', 'count'])
weekly_totals = (
df.groupby('week')[metric_col].sum()
.tail(8)
.reset_index()
)
fig, ax = plt.subplots(figsize=(6, 2))
ax.plot(weekly_totals['week'], weekly_totals[metric_col], marker='o',
linewidth=2, color='steelblue', markersize=4)
ax.fill_between(weekly_totals['week'], weekly_totals[metric_col],
alpha=0.1, color='steelblue')
ax.set_title(f'{metric_col.title()} — Last 8 Weeks', fontsize=10)
ax.tick_params(labelsize=8)
plt.tight_layout()
plt.savefig('weekly_sparkline.png', dpi=150, bbox_inches='tight')
plt.close()
return report
Anomaly Detection Patterns
def detect_anomalies(series, method='zscore', threshold=3.0, window=30):
"""
Detect anomalies in a numeric series.
Methods:
- 'zscore': Flag values beyond `threshold` standard deviations from mean
- 'iqr': Flag values beyond 1.5x IQR from quartiles
- 'rolling': Flag values beyond `threshold` std devs from rolling mean
"""
anomalies = pd.Series(False, index=series.index)
if method == 'zscore':
z = (series - series.mean()) / series.std()
anomalies = z.abs() > threshold
elif method == 'iqr':
q1 = series.quantile(0.25)
q3 = series.quantile(0.75)
iqr = q3 - q1
anomalies = (series < q1 - 1.5 * iqr) | (series > q3 + 1.5 * iqr)
elif method == 'rolling':
rolling_mean = series.rolling(window, min_periods=5).mean()
rolling_std = series.rolling(window, min_periods=5).std()
anomalies = (series - rolling_mean).abs() > threshold * rolling_std
return anomalies
anomalies = detect_anomalies(df['metric'], method='rolling', threshold=2.5, window=30)
fig, ax = plt.subplots(figsize=(14, 5))
ax.plot(df.index, df['metric'], linewidth=1, color='steelblue', label='Metric')
ax.scatter(df.index[anomalies], df['metric'][anomalies],
color='red', s=40, zorder=5, label='Anomaly')
ax.legend()
ax.set_title('Anomaly Detection (Rolling Z-Score)', fontsize=14, fontweight='bold')
plt.tight_layout()
plt.savefig('anomalies.png', dpi=150, bbox_inches='tight')
plt.close()
print(f"Detected {anomalies.sum()} anomalies out of {len(series):,} data points")
Method selection guide:
| Method | Best For | Assumptions | Sensitivity |
|---|
| Z-score | Stationary data with normal distribution | Constant mean and variance | Low (misses local anomalies) |
| IQR | Skewed distributions, outlier screening | None (non-parametric) | Medium |
| Rolling z-score | Time series with trends or seasonality | Local stationarity within window | High (adapts to drift) |
Common Analytics Pitfalls
Simpson's Paradox
A trend that appears in grouped data reverses when the groups are combined.
Department A: Drug works better (80% vs 70%)
Department B: Drug works better (50% vs 40%)
Combined: Drug appears WORSE (55% vs 60%) <-- paradox
Why it happens: Unequal group sizes create a confounding effect. Department B (with lower overall rates) sent most patients to the drug group.
Prevention: Always segment data by relevant confounders before drawing conclusions. If aggregate and segmented results disagree, trust the segmented analysis and report the confounding variable.
Survivorship Bias
Analyzing only entities that "survived" a selection process, ignoring those that dropped out.
Classic examples:
- Studying only successful companies to find success patterns (ignoring failed companies with the same patterns)
- Analyzing only current customers to understand satisfaction (ignoring those who already left)
- Looking at fund performance by examining only funds that still exist (dead funds were closed)
Prevention: Always ask "what is missing from this dataset?" before drawing conclusions. If possible, include data from non-survivors. Explicitly note the selection criteria and what it excludes.
Correlation vs. Causation
A statistically significant correlation between X and Y does not mean X causes Y. Possible explanations:
| Explanation | Example |
|---|
| X causes Y | Exercise reduces blood pressure |
| Y causes X | Depression reduces exercise (not exercise causes depression) |
| Z causes both | Income drives both education spending AND health outcomes |
| Coincidence | Ice cream sales correlate with drowning deaths (both driven by summer) |
Prevention: Establish causation only with randomized controlled experiments (A/B tests). For observational data, state findings as "associated with" not "causes." Look for confounders and test whether the relationship holds when controlling for them.
Cherry-Picking Time Windows
Selecting a start/end date that makes a metric look better or worse than the true trend.
df['yoy_change'] = df.groupby(df['date'].dt.month)['revenue'].pct_change(periods=12)
Prevention checklist:
- Compare like-for-like periods (YoY for seasonal businesses)
- Show the full time range, not a selected subset
- Use multiple time windows (WoW, MoM, QoQ, YoY) and note if they disagree
- Include a moving average to show the underlying trend separate from noise
Small Sample Size Issues
Small samples produce unstable statistics that can flip with just a few more observations.
from scipy.stats import beta
a_small, b_small = 3 + 1, 10 - 3 + 1
ci_small = beta.interval(0.95, a_small, b_small)
print(f"n=10: 30% conversion, 95% CI: [{ci_small[0]:.1%}, {ci_small[1]:.1%}]")
a_large, b_large = 300 + 1, 1000 - 300 + 1
ci_large = beta.interval(0.95, a_large, b_large)
print(f"n=1000: 30% conversion, 95% CI: [{ci_large[0]:.1%}, {ci_large[1]:.1%}]")
Rules of thumb:
- n < 30: Do not draw firm conclusions. Report as directional only.
- Conversion rates need hundreds (not dozens) of conversions to stabilize.
- Always report confidence intervals alongside point estimates.
- If sample size is fixed and small, use exact tests (Fisher's exact) rather than approximations (chi-squared).
