Why Business Analysts Must Learn Python

# pandas — clean and summarise a messy export.
# This is the single most useful Python skill for a business analyst.

import pandas as pd

# 1. Load — works for CSV and Excel exports from any tool
df = pd.read_csv("orders_export.csv")

# 2. Inspect before you trust it
print(df.shape)          # (rows, columns)
print(df.columns.tolist())
print(df.isna().sum())   # missing values per column

# 3. Clean — the three things that break every manual report
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df = df.dropna(subset=["order_date", "amount"])

# Remove duplicate order IDs (very common in tool exports)
df = df.drop_duplicates(subset=["order_id"])

# 4. Normalise inconsistent category labels
df["region"] = (
    df["region"]
    .str.strip()
    .str.title()
    .replace({"Ncr": "NCR", "Delhi Ncr": "NCR"})
)

# 5. Summarise — the actual analysis
by_region = (
    df.groupby("region")
      .agg(orders=("order_id", "count"),
           revenue=("amount", "sum"),
           avg_order=("amount", "mean"))
      .sort_values("revenue", ascending=False)
)

# 6. Month-over-month trend
monthly = (
    df.set_index("order_date")
      .resample("M")["amount"]
      .sum()
      .pct_change()
      .mul(100)
      .round(1)
)

print(by_region)
print(monthly.tail())

# Why this matters in an interview:
# - You can replace a 4-hour manual report with a script
# - You handle the messy cases (dates, duplicates, labels)
# - Your numbers are reproducible, not re-typed

# Automate the weekly Excel report.
# The BA-specific win: the deliverable stays Excel, the work becomes a script.

import pandas as pd
from datetime import date
from pathlib import Path

INPUT = Path("data/orders_export.csv")
OUTPUT_DIR = Path("reports")
OUTPUT_DIR.mkdir(exist_ok=True)

# 1. Read and clean (same as the previous pattern)
df = pd.read_csv(INPUT)
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df = df.dropna(subset=["order_date", "amount"])
df = df.drop_duplicates(subset=["order_id"])

# 2. Build the summary a stakeholder actually reads
summary = (
    df.groupby("region")
      .agg(orders=("order_id", "count"), revenue=("amount", "sum"))
      .reset_index()
      .sort_values("revenue", ascending=False)
)

exceptions = df[df["amount"] > 100000][["order_id", "region", "amount"]]

# 3. Write a multi-sheet workbook — one sheet per audience
today = date.today().strftime("%Y-%m-%d")
out_file = OUTPUT_DIR / f"weekly_report_{today}.xlsx"

with pd.ExcelWriter(out_file, engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    exceptions.to_excel(writer, sheet_name="Exceptions", index=False)

    # Light formatting so the file is presentable without manual work
    ws = writer.sheets["Summary"]
    ws.column_dimensions["A"].width = 20
    ws.column_dimensions["B"].width = 12
    ws.column_dimensions["C"].width = 16

print(f"Wrote {out_file} with {len(summary)} regions")

# Why this matters:
# - The deliverable is still Excel — nobody has to change their habits
# - Re-running for a new week is one command, not four hours
# - The logic is visible, reviewable, and versioned

# Turn acceptance criteria into tests.
# This is the BA superpower most people ignore: executable requirements.

import requests
import pytest

BASE = "https://api.example.com"

def test_order_returns_expected_fields():
    """AC1: GET /orders/{id} returns id, status, total."""
    r = requests.get(f"{BASE}/orders/12345", timeout=10)
    assert r.status_code == 200

    body = r.json()
    for field in ["id", "status", "total"]:
        assert field in body, f"missing field: {field}"

def test_discount_cannot_exceed_total():
    """AC2: discount is never greater than the order total."""
    r = requests.get(f"{BASE}/orders/12345", timeout=10)
    body = r.json()
    assert body["discount"] <= body["total"]

def test_invalid_order_returns_404():
    """AC3: unknown order id returns 404, not 500."""
    r = requests.get(f"{BASE}/orders/does-not-exist", timeout=10)
    assert r.status_code == 404

# Run with:  pytest test_acceptance.py -v

# Why this matters:
# - "The system should validate the discount" is opinion
# - assert body["discount"] <= body["total"] is a check
# - When it fails, the developer knows exactly what broke
# - You stop arguing about whether something works

# You do not need to write a full test suite.
# Five checks around the risky parts of a feature is enough to
# change how your team talks about requirements.