Python Data Analytics

---
name: python-data-analytics
description: Use when computing complex analytics, KPIs, cohort/funnel/retention metrics, financial math (IRR/NPV/amortization), statistical tests, anomaly detection, or geospatial analytics in Python — for cases where SQL alone gets unwieldy.
metadata:
  portable: true
  compatible_with:
  - Codex
  - codex
---

# Python Data Analytics
Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, +256 784 464178.

<!-- dual-compat-start -->
## Use When

- Use when computing complex analytics, KPIs, cohort/funnel/retention metrics, financial math (IRR/NPV/amortization), statistical tests, anomaly detection, or geospatial analytics in Python — for cases where SQL alone gets unwieldy.

## Evidence Produced

| Category | Artifact | Format | Example |
|----------|----------|--------|---------|
| Correctness | Analytics test plan | Markdown doc covering KPI computation, cohort/funnel/retention, and edge-case (empty / sparse) tests | `docs/python/analytics-tests.md` |
| Performance | Pandas/Polars performance note | Markdown doc covering DataFrame size, vectorisation choices, and memory footprint per query | `docs/python/analytics-perf-note.md` |

## References

- Use the `references/` directory for deep detail after reading the core workflow below.
<!-- dual-compat-end -->
Use Python when SQL stops being the right tool: multi-step transformations, statistical tests, time-series analysis, anomaly detection, complex financial math, or geospatial computation. Pandas / numpy / Polars as the compute engine; results feed back to PHP via FastAPI sidecar or into Excel/PDF via document-generation.

**Prerequisites:** Load `python-modern-standards` and `python-saas-integration` before this skill.

## When this skill applies

- Computing cohort retention, funnel conversion, churn curves.
- Financial math: IRR, NPV, amortization, depreciation schedules at scale.
- Accounting analytics: trial balance, AR/AP aging, bank reconciliation, inventory valuation, fixed-asset reconciliation, cost variance, and finance dashboard calculations.
- Statistical tests: A/B test significance, distribution comparisons, trend detection.
- Analytics method selection: descriptive, diagnostic, predictive, and prescriptive
  analysis with explicit data-quality and decision-handoff controls.
- Outlier/anomaly detection on SaaS metrics.
- Geospatial analytics: territory stats, route distances, geofence compliance reports.
- Any workload where SQL requires >3 subqueries or a window function chain that's hard to read.

## When Python beats raw SQL (decision rule)

```text
Single-table aggregation, < 3 group-bys       -> SQL
Joins + window functions, still readable       -> SQL
Iterative calculation (running state, rolling) -> Python
Statistical test / hypothesis / distribution  -> Python
Financial schedule (amortization, IRR)         -> Python
Multi-source merge (DB + API + file)           -> Python
Requires chart output                          -> Python
Result is a matrix / pivot > 10 columns        -> Python
```

Rule of thumb: if explaining the SQL to a teammate takes more than 60 seconds, move to Python.

## Core stack

- **pandas 2.x** — default DataFrame library. Arrow-backed dtypes on 3.11+.
- **numpy** — array math, used by pandas under the hood.
- **Polars** — use for datasets > 1M rows or where pandas is too slow. Lazy API is great.
- **SQLAlchemy 2.x** — for DB access. Never mix with raw DB cursors in the same module.
- **Shapely + GeoPandas** — for geospatial work.
- **statsmodels + scipy** — statistical tests and time-series.
- **Decimal** (stdlib) — for all currency math; never `float`.

## Loading data

Use SQLAlchemy engine + `pandas.read_sql` with parameters. Chunk for large results.

```python
from sqlalchemy import create_engine, text
import pandas as pd

engine = create_engine(settings.database_url, pool_pre_ping=True)

sql = text("""
    SELECT customer_id, invoice_date, total_amount
    FROM invoices
    WHERE tenant_id = :tenant_id
      AND invoice_date >= :start
      AND invoice_date <  :end
""")
df = pd.read_sql(sql, engine, params={"tenant_id": tenant_id, "start": start, "end": end},
                 parse_dates=["invoice_date"])
```

For > 1M rows, chunked reads with `chunksize=100_000` and a generator pipeline. See `references/loading-data.md`.

## Pandas idioms (the ones that matter)

**Vectorize, don't loop.**

```python
# SLOW
df["total"] = df.apply(lambda r: r["qty"] * r["unit_price"], axis=1)

# FAST
df["total"] = df["qty"] * df["unit_price"]
```

**Avoid `inplace=True`.** Leads to chained assignment bugs in 2.x. Use explicit reassignment.

**Use dtypes on load.** `pd.read_sql(..., dtype={"customer_id": "int64"})`. Cast dates, categoricals, booleans early.

**`copy()` when slicing, always.** `df2 = df[df.status == "paid"].copy()` — prevents `SettingWithCopyWarning`.

**`groupby().agg()` with named columns** is clearer than dict form:

```python
summary = (
    df.groupby("tenant_id", as_index=False)
      .agg(
          revenue=("total_amount", "sum"),
          invoices=("id", "count"),
          avg_invoice=("total_amount", "mean"),
      )
)
```

See `references/pandas-idioms.md` for the full catalog (copy vs view, dtypes, datetime handling, common pitfalls).

## Cohort, funnel, retention

The three most-requested SaaS analytics patterns. Full recipes in `references/cohort-funnel-retention.md`. Summary here:

**Cohort retention:**
1. Assign each customer to their signup cohort (month).
2. For each month after signup, count active customers per cohort.
3. Divide by cohort size → retention %.

```python
customers["cohort"] = customers["signup_date"].dt.to_period("M")
activity = activity.merge(customers[["customer_id", "cohort"]], on="customer_id")
activity["period"] = activity["activity_date"].dt.to_period("M")
activity["offset"] = (activity["period"] - activity["cohort"]).apply(lambda x: x.n)

cohort_size = customers.groupby("cohort").size()
retention = (
    activity.groupby(["cohort", "offset"])["customer_id"].nunique()
            .unstack(fill_value=0)
            .div(cohort_size, axis=0) * 100
)
```

**Funnel conversion:** sequential step counts → percentage drop-offs.

**Retention curves:** for each cohort, plot offset vs %retained — identify where users churn.

## Financial math — always Decimal, never float

Currency math is Decimal. Period.

```python
from decimal import Decimal, ROUND_HALF_UP

def amortization_schedule(principal: Decimal, annual_rate: Decimal, months: int) -> list[dict]:
    monthly_rate = annual_rate / Decimal(12) / Decimal(100)
    # Payment formula using Decimal throughout
    ...
```

Recipes for IRR, NPV, amortization, depreciation (straight-line, declining, units-of-production), and day-count conventions (30/360, actual/365) in `references/financial-math.md`. For accounting and bookkeeping analytics, load `references/accounting-finance-analytics.md` before calculating trial balances, aging schedules, reconciliations, cost variances, or finance dashboards.

## Statistical tests and anomalies

- **A/B test significance:** two-sample t-test, chi-square for proportions, Mann-Whitney U when non-normal.
- **Outliers:** z-score (thresholds: 2.5–3), IQR method (1.5×IQR), Modified Z-score with median.
- **Trend detection:** Mann-Kendall for monotonic trends; decompose seasonality with STL.
- **Anomaly detection:** rolling-window mean ± k·std; isolation forest when rules aren't enough.

See `references/statistics-and-anomalies.md` for choosing the right test per question, and `python-ml-predictive` for model-based anomaly detection.

## Geospatial analytics

Use Shapely for geometry, GeoPandas when you have many features. Coordinate reference systems matter — always tag CRS (EPSG:4326 for WGS84 lat/lng).

```python
import geopandas as gpd
from shapely.geometry import Point

deliveries = gpd.GeoDataFrame(
    df, geometry=gpd.points_from_xy(df.lng, df.lat), crs="EPSG:4326"
).to_crs("EPSG:3857")   # project to meters for distance

zones = gpd.read_file("zones.geojson").to_crs("EPSG:3857")
joined = gpd.sjoin(deliveries, zones, how="left", predicate="within")
```

Distance calculations in EPSG:3857 (meters), not 4326 (degrees — degrees ≠ distance). See `references/geospatial-analytics.md`.

## Performance — when to leave pandas

- **Rows < 100K:** pandas is fine.
- **Rows 100K–5M:** pandas works; profile and optimize dtypes (`category`, Arrow-backed).
- **Rows > 5M or memory tight:** switch to **Polars**. Its lazy API (`pl.scan_csv`/`scan_parquet`) beats pandas substantially on aggregations.
- **Truly large (> 100M rows):** this doesn't belong in a sidecar. Move to worker + chunked processing, or to a warehouse (ClickHouse / DuckDB / BigQuery) and let Python orchestrate.

Always profile with `cProfile` or `py-spy` before optimizing. See `references/performance-and-polars.md`.

## Output handoff

Analytics results don't live in Python — they return to PHP or become documents.

- **JSON back to PHP:** Pydantic models → FastAPI response. Decimal → string (IEEE754 will bite you).
- **Persist to MySQL:** `df.to_sql(...)` is OK for < 10K rows; beyond that, use `SQLAlchemy Core` with `INSERT ... ON DUPLICATE KEY UPDATE` and chunked inserts.
- **Feed into document-generation:** pass the DataFrame directly to the skill's Excel/Word/PDF generators. See `python-document-generation`.

## Pitfalls specific to SaaS analytics

- **Timezone:** store UTC, report in tenant-local. `df["created_at"].dt.tz_convert(tenant_tz)`.
- **Currency:** if invoices have mixed currencies, convert to a common reporting currency at the tenant's chosen rate (spot vs transaction-date rate — decide and document).
- **Dates vs datetimes:** trimming to midnight UTC can shift dates by a day in the user's TZ. Always round-trip through the tenant TZ before truncating.
- **Division by zero in rates:** guard every rate calc (`retention_rate = active / cohort_size if cohort_size else 0`).
- **Tenant isolation:** every DataFrame loaded must be filtered by tenant at SQL level. Never filter in Python after loading — you'll paginate the wrong data.

## Read next

- `python-document-generation` — render the DataFrame as a branded Excel dashboard or PDF.
- `python-ml-predictive` — when the question is "what happens next" instead of "what happened".
- `python-data-pipelines` — when data needs to be fetched/cleaned before analysis.

## References

- `references/analytics-method-selection-and-governance.md`
- `references/pandas-idioms.md`
- `references/loading-data.md`
- `references/cohort-funnel-retention.md`
- `references/financial-math.md`
- `references/accounting-finance-analytics.md`
- `references/statistics-and-anomalies.md`
- `references/geospatial-analytics.md`
- `references/performance-and-polars.md`

## Decision Rules

| Condition | Action |
|---|---|
| Metric is clear and efficient in SQL | Compute near the data source |
| Analysis needs statistical or geospatial libraries | Use a reproducible Python pipeline |
| Monetary precision affects results | Use decimal or integer minor units |

## Capability Contract

Read access to data definitions is required. Execution is preferred; edits and data writes require authorisation.

## Degraded Mode

If data or execution is unavailable, return a runnable plan with schema assumptions. Do not present illustrative results as findings.

## Domain Anti-Patterns

- Computing a KPI before fixing its grain and denominator.
- Mixing currencies or time zones silently.
- Treating missing values as zero without a rule.
- Reporting significance without effect size and sample context.
- Exporting row-level tenant data without authorisation.
## Inputs
| Artefact | Required? | Purpose |
|---|---|---|
| Dataset, metric definitions, decision question, and quality context | yes | Ground analysis and validation |
## Outputs
- Produce reproducible analysis, validated findings, caveats, and decision implications.