datasette-outliers 0.1.0

Datasette plugin for finding statistical outliers in SQL query results

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:

datasette install datasette-outliers

Usage

Run Datasette as normal:

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).
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:

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

Run the tests:

python -m unittest discover