Analytics Hand Skill

Data Analytics Expert Knowledge

pandas Quick Reference

Data Loading

import pandas as pd

# CSV
df = pd.read_csv('data.csv')
df = pd.read_csv('data.csv', parse_dates=['date_col'], index_col='id')

# JSON
df = pd.read_json('data.json')
df = pd.read_json('data.json', orient='records')

# Excel
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# From dict
df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']})

Data Inspection

df.shape              # (rows, columns)
df.dtypes             # Column types
df.info()             # Summary including memory usage
df.describe()         # Statistical summary
df.head(10)           # First 10 rows
df.isnull().sum()     # Missing values per column
df.duplicated().sum() # Number of duplicate rows
df.nunique()          # Unique values per column

Data Cleaning

# Handle missing values
df.dropna()                          # Drop rows with any NaN
df.fillna(0)                         # Fill NaN with 0
df.fillna(df.mean())                 # Fill with column means
df['col'].interpolate()              # Interpolate missing values

# Remove duplicates
df.drop_duplicates()
df.drop_duplicates(subset=['col1', 'col2'])

# Type conversion
df['col'] = df['col'].astype(int)
df['date'] = pd.to_datetime(df['date'])
df['cat'] = df['cat'].astype('category')

# Outlier removal (IQR method)
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']  0.7: Strong correlation
- 0.4  0.05 means normal

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.8 = large

**Sample size awareness:**
- n  0])

# Clean
df = df.dropna(subset=['customer_id', 'order_total'])
df['order_total'] = df['order_total'].clip(lower=0)  # Remove negative values
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']  # Average order value
monthly['revenue_mom'] = monthly['revenue'].pct_change()  # Month-over-month growth

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')
)

# Score each dimension 1-4 using quartiles
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))

# Segment mapping
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 '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')
)

# Convert to retention percentages
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      # Current conversion rate: 12%
mde = 0.02                # Minimum detectable effect: 2 percentage points
alpha = 0.05              # Significance level
power = 0.80              # Statistical power

# Using the normal approximation formula
p1 = baseline_rate
p2 = baseline_rate + mde
p_avg = (p1 + p2) / 2

z_alpha = stats.norm.ppf(1 - alpha / 2)  # Two-tailed
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']

# Chi-squared test for proportions
contingency = pd.crosstab(ab['variant'], ab['converted'])
chi2, p_value, dof, expected = stats.chi2_contingency(contingency)

# Proportions z-test (more direct)
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 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())  # From earlier definition
    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 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

# Expanding window (cumulative statistics)
df['cumulative_avg'] = df['value'].expanding().mean()
df['cumulative_max'] = df['value'].expanding().max()

# Exponentially weighted moving average (EWMA) -- emphasizes recent values
df['ewma_7'] = df['value'].ewm(span=7).mean()     # Span-based decay
df['ewma_a'] = df['value'].ewm(alpha=0.3).mean()   # Explicit decay factor

# Comparison: rolling vs. EWMA
# - rolling(7).mean() weights all 7 values equally
# - ewm(span=7).mean() weights recent values exponentially more
# Use EWMA when recent data matters more (stock prices, real-time metrics)

# Rolling with min_periods (handles early rows with insufficient data)
df['rolling_avg'] = df['value'].rolling(window=30, min_periods=5).mean()

# Rolling rank (percentile within window)
df['rolling_pctile'] = df['value'].rolling(90).rank(pct=True)

Multi-Index Operations

# Create multi-index from groupby
multi = df.groupby(['region', 'product']).agg(
    revenue=('amount', 'sum'),
    units=('quantity', 'sum')
)

# Access levels
multi.loc['North']                        # All products in North region
multi.loc[('North', 'Widget')]            # Specific region + product
multi.xs('Widget', level='product')       # All regions for Widget

# Swap and sort levels
multi = multi.swaplevel().sort_index()

# Reset to flat columns
flat = multi.reset_index()

# Stack / unstack (reshape between long and wide)
wide = multi['revenue'].unstack(level='product')    # Products become columns
long = wide.stack()                                  # Back to multi-index

Merge and Join Patterns

# Inner join (only matching rows)
merged = orders.merge(customers, on='customer_id', how='inner')

# Left join with indicator (see which rows matched)
merged = orders.merge(customers, on='customer_id', how='left', indicator=True)
unmatched = merged[merged['_merge'] == 'left_only']

# Join on multiple keys
merged = df1.merge(df2, on=['date', 'region'], how='left')

# Join with different column names
merged = orders.merge(products, left_on='prod_id', right_on='product_id')

# Anti-join (rows in A that have no match in B)
anti = df_a.merge(df_b, on='key', how='left', indicator=True)
anti = anti[anti['_merge'] == 'left_only'].drop(columns='_merge')

# Self-join (compare rows within same table)
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']  10000, 'high', 'low')

# np.select for multiple conditions
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

# Check current memory usage
print(df.memory_usage(deep=True).sum() / 1024**2, "MB")

# Downcast numeric types
df['int_col'] = pd.to_numeric(df['int_col'], downcast='integer')    # int64 -> int8/16/32
df['float_col'] = pd.to_numeric(df['float_col'], downcast='float')  # float64 -> float32

# Use category type for low-cardinality strings
for col in df.select_dtypes(include='object'):
    if df[col].nunique() / len(df)  threshold

    elif method == 'iqr':
        q1 = series.quantile(0.25)
        q3 = series.quantile(0.75)
        iqr = q3 - q1
        anomalies = (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

# Usage: detect and visualize
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 contr

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: librefang
• Source: librefang/librefang-registry
• License: MIT

Install and usage instructions live in the source repository linked above.