data-analysis

Data Analysis SOP

When to Use

• User wants to analyze a dataset and find patterns or insights
• User asks for EDA (Exploratory Data Analysis) on a file
• User wants summary statistics, distributions, or correlations
• User needs to clean dirty data before analysis

---

Part 1 — The Analysis Workflow

1. Load & Inspect     → understand what you have
2. Clean              → handle nulls, types, duplicates, outliers
3. Explore (EDA)      → distributions, correlations, group comparisons
4. Hypothesize        → state specific questions to answer
5. Validate           → test hypotheses with statistics or aggregations
6. Communicate        → clear charts + written findings

---

Part 2 — Load & Inspect

import pandas as pd
import numpy as np

# ── Load ──────────────────────────────────────────────────────────
df = pd.read_csv("data.csv", parse_dates=["date_col"])
# For Excel: pd.read_excel("data.xlsx", sheet_name="Sheet1")
# For JSON:  pd.read_json("data.json", lines=True)  # JSONL
# For large files: pd.read_csv("data.csv", chunksize=100_000)

# ── Quick overview ────────────────────────────────────────────────
print(f"Shape: {df.shape[0]:,} rows × {df.shape[1]} columns")
print(f"Memory: {df.memory_usage(deep=True).sum() / 1e6:.1f} MB")
print()
print(df.dtypes)
print()
df.head(3)

Inspection Checklist

# 1. Types — are columns the right dtype?
df.dtypes

# 2. Nulls
null_report = pd.DataFrame({
    "null_count": df.isnull().sum(),
    "null_pct": (df.isnull().mean() * 100).round(1)
}).query("null_count > 0").sort_values("null_pct", ascending=False)
print(null_report)

# 3. Duplicates
dup_count = df.duplicated().sum()
print(f"Full duplicates: {dup_count} ({dup_count/len(df)*100:.1f}%)")

# 4. Cardinality — how many unique values per column?
df.nunique().sort_values(ascending=False)

# 5. Value ranges for numerics
df.describe().T.round(2)

---

Part 3 — Cleaning

# ── Fix dtypes ────────────────────────────────────────────────────
df["date"] = pd.to_datetime(df["date"], errors="coerce")
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df["category"] = df["category"].astype("category")

# ── Standardize strings ───────────────────────────────────────────
df["name"] = df["name"].str.strip().str.lower()

# ── Remove exact duplicates ───────────────────────────────────────
df = df.drop_duplicates()

# ── Handle nulls (choose strategy per column) ─────────────────────
# Drop rows where critical column is null
df = df.dropna(subset=["user_id", "event_type"])

# Fill with median (numeric)
df["revenue"] = df["revenue"].fillna(df["revenue"].median())

# Fill with mode (categorical)
df["country"] = df["country"].fillna(df["country"].mode()[0])

# Fill forward (time series)
df = df.sort_values("date")
df["price"] = df["price"].ffill()

# ── Handle outliers ───────────────────────────────────────────────
# IQR method — cap rather than drop
Q1 = df["amount"].quantile(0.25)
Q3 = df["amount"].quantile(0.75)
IQR = Q3 - Q1
lower, upper = Q1 - 1.5 * IQR, Q3 + 1.5 * IQR
df["amount_capped"] = df["amount"].clip(lower, upper)

# Z-score method — flag extreme outliers
from scipy import stats
df["amount_zscore"] = np.abs(stats.zscore(df["amount"].dropna()))
outliers = df[df["amount_zscore"] > 3]
print(f"Outliers (|z|>3): {len(outliers)}")