Metadata-Version: 2.4
Name: datasette-outliers
Version: 0.1.0
Summary: Datasette plugin for finding statistical outliers in SQL query results
Author: Julius Welby
License-Expression: Apache-2.0
Project-URL: Homepage, https://github.com/jwelby/datasette-outliers
Project-URL: Issues, https://github.com/jwelby/datasette-outliers/issues
Project-URL: Source, https://github.com/jwelby/datasette-outliers
Keywords: datasette,datasette-plugin,outliers,anomaly-detection,data-quality,statistics,iqr,z-score
Classifier: Development Status :: 4 - Beta
Classifier: Framework :: Datasette
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Science/Research
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database :: Front-Ends
Classifier: Topic :: Scientific/Engineering :: Information Analysis
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: datasette
Dynamic: license-file

# datasette-outliers

Find statistical outliers in Datasette SQL query results.

This plugin adds an **Outlier analysis** panel to the Datasette SQL query page.
It takes whatever SQL is in the editor — something you just typed, or a saved
query you loaded — wraps it as a common table expression named `base`, and
builds an analytical query over a chosen numeric column of those results. You
can produce either a **summary** (quartiles, fences, outlier count) or the
**actual outlier rows**, ranked by how extreme they are.

Everything happens by rewriting the SQL in the editor, so you always see the
query that produced the numbers, can edit it, and can export the result as CSV
or JSON like any other Datasette query. Nothing is saved and the database is
never modified.

## Features

- **Outlier stats** — a one-row summary of the chosen column: `n`, min/Q1/median/Q3/max,
  the fences, and the outlier count and percentage.
- **Outlier rows** — the actual anomalous records, ranked by how far past the
  fence they sit.
- **Four methods** — Tukey IQR fences (1.5× / 3×), IQR on `log10` for skewed
  data, robust MAD / modified z-score, and classic z-score (2σ / 3σ).
- **Scan all numeric columns** — a data-quality pass that ranks every numeric
  column by how extreme its worst value is, for catching parser and import errors.
- **Inline chart** — a self-contained SVG box plot and jittered strip of the
  column, with outliers highlighted and a log-scale toggle. No charting library.
- **Works on any table** — the column dropdown is pre-populated from the current
  query and refreshes as you edit it; table pages get a one-click launcher.

## Installation

Install it into the same environment as Datasette:

```bash
datasette install datasette-outliers
```

## Usage

Run Datasette as normal:

```bash
datasette data.db
```

On the SQL query page (`/database?sql=...`), an **Outlier analysis** panel
appears under the SQL editor. On a normal table page it adds an **"Analyse this
table for outliers"** link that opens the SQL page seeded with
`select * from <table>`.

1. Write or load a query, or follow the link from a table page.
2. Pick a column from the **Column** dropdown. It is pre-populated with the
   numeric columns of the current results — columns whose sampled values are all
   numbers, including numbers stored as text such as a year column — and
   refreshes automatically as you edit the query. If there is only one numeric
   column it is selected for you.
3. Choose a **method** (see below).
4. Click **Outlier stats** for a summary, **Show outlier rows** for the records
   themselves, or **Show chart** to see the distribution (see
   [Visualising the distribution](#visualising-the-distribution)).
5. For the stats and rows, review the generated SQL and press Datasette's
   **Run** button. The chart renders immediately, in the panel.

Clicking again always re-wraps the original query, so you can switch column or
method freely without nesting queries — even after you have run an analysis and
the editor holds the generated SQL.

### Methods

| Method | What it flags |
| --- | --- |
| **IQR fences (1.5× / 3×)** | Tukey fences: values below `Q1 − k·IQR` or above `Q3 + k·IQR`. |
| **IQR on log10** | Tukey fences applied to `log10(value)`, with fences shown back on the original scale. Best for heavy-tailed positive data (counts, sizes) where raw fences flag most of the tail. Positive values only. |
| **MAD / modified z (3.5)** | Robust: `median ± k·MAD`, unaffected by the very outliers it looks for. |
| **Z-score (2σ / 3σ)** | Classic `mean ± k·stddev`. Simple but sensitive to the outliers themselves. |

### Output

**Outlier stats** returns a single row that leads with the `column_name`, then
`n`, `min`, `q1`, `median`, `q3`, `max`, `lower_fence`, `upper_fence`,
`outliers`, and `outlier_pct`. (MAD and z-score report their own centre and
spread — `median`/`mad` or `mean`/`stddev` — in place of the quartiles.)

**Show outlier rows** returns the source rows that fall outside the fences,
with the fences appended and a score column showing how extreme each row is,
sorted most-extreme first (top 200):

| Method | Score column |
| --- | --- |
| IQR / IQR on log10 | `iqrs_beyond_fence` (how many IQRs past the fence) |
| MAD | `modified_z` |
| Z-score | `z_score` |

### Scan all numeric columns

The **Scan all numeric columns** button runs a data-quality pass: one row per
numeric column, ranked by `severity` (how many IQRs beyond its fence the most
extreme value sits). It is good for catching parser and import errors.

For example, scanning a citation-profile table immediately surfaces a
`page_max` of `20231015` — a date (`2023-10-15`) that was parsed into a page
number — sitting tens of thousands of IQRs beyond a fence of ~800, while the
genuinely clean columns score a severity near zero.

### Visualising the distribution

**Show chart** draws a compact box plot and jittered strip of the selected
column directly in the panel — no page reload, and no external charting
library (it is inline SVG). The box spans Q1–Q3 with the median marked, the
whiskers reach the fences, and each sampled point is drawn beneath, with points
outside the fences highlighted.

A **log scale** toggle re-plots on a `log10` axis, which is essential for
heavy-tailed columns: on a raw axis a single extreme value (such as the
`page_max` parse error above) squashes the whole distribution into one edge,
whereas on a log axis the box and the tail of outliers both become legible. The
log-IQR method turns the toggle on automatically.

Each outlier point is interactive: **hover** to see a tooltip with the record's
label and id (auto-detected from the query's columns), the value, and its score;
**click** to open a Datasette query isolating that record so you can see it in
full context.

The box is always quartile-based (that is what a box plot is). For the IQR and
log-IQR methods it draws the selected fences; for MAD and z-score it falls back
to IQR 1.5× fences, noted in the chart title.

## How it works

The plugin uses Datasette's `extra_body_script` hook to inject a small script
on database, query, and table pages. On pages with the SQL editor it adds the
analysis panel; on table pages it adds the launcher link.

SQLite has no percentile or standard-deviation functions, so quartiles are
computed with a window-function nearest-rank and variance as
`avg(v*v) - avg(v)*avg(v)`. The parent query is wrapped with
`with base as materialized (...)` so an expensive query is evaluated once even
though later steps reference it repeatedly.

## Compatibility and limitations

- **Requires SQLite 3.35 or newer** for `WITH ... AS MATERIALIZED` and the math
  functions (`log10`, `sqrt`, `pow`). This is the SQLite bundled with Python
  3.11+ on most platforms; older SQLite will raise an error on the generated
  SQL. Tested with Datasette 0.65.2.
- **Large scans can exceed Datasette's SQL time limit.** Scanning many columns
  over a large table sorts once per column and can take longer than the default
  `sql_time_limit_ms` of 1000 ms. Raise it for scans, e.g.
  `datasette data.db --setting sql_time_limit_ms 15000`.
- **Columns dominated by a single value** (for example more than 75% zeros)
  have `Q1 = Q3` and therefore no IQR, so IQR-based methods and the scan report
  them as clean even when a large maximum exists.
- **MAD with `MAD = 0`** (more than half the values identical) flags everything
  that differs from the median; prefer another method for such columns.
- **The log10 method ignores non-positive values.**
- **Table names with unusual characters** in the launcher link rely on simple
  URL decoding and may need the SQL written by hand.
- **The chart's strip** uses the first 500 sampled rows (plus the fetched
  outliers), not a uniform random sample. The tooltip/click-through pick an id
  and label column heuristically (names matching `id`/`_id`/`lsid` and
  `label`/`name`/`title`); if the query has no such columns the tooltip falls
  back to the value and score alone.

## For developers

Clone this repository and install your local copy in editable mode, so your
edits take effect without reinstalling:

```bash
pip install -e . --no-build-isolation
```

Run the tests:

```bash
python -m unittest discover
```
