Sentinel values - onceover

Some columns record a code where a measurement is missing: −999 in an amount column, −1 in a count. The value is repeated exactly, it sits inside a limited stretch of rows — one extract, one instrument, one period — and it is not a plausible measurement. The sentinels detector reports these as missingness findings of type sentinel_values.

A column card does not separate the code from the data. The null count reads zero, so the column looks fully observed, while the mean, the spread, and every model fitted on the column absorb the code as if it were a measurement.

The test¶

Three conditions must hold together. The value occurs at least 20 times. Its occurrences are concentrated: the shortest window covering at least 80% of them spans at most 25% of the column’s rows. And the value lies outside the bulk of the column’s other values, tested two ways: outside the Tukey far-out fence (Q1 − 3 · IQR, Q3 + 3 · IQR) of those values, or across their sign boundary — a negative value in a column whose other values are non-negative, a positive value in a non-positive column, or a repeated 0 in a column whose other values are strictly positive or strictly negative.

Each condition excludes a different ordinary pattern. A genuine value can be far out (a scattered tail value has no window), and a genuine value can be common (a zero-inflated count’s zeros sit inside the fence). The code is the value that is all three: repeated, windowed, and outside.

import random

import polars as pl

import onceover

rng = random.Random(44)
vals = [rng.gauss(50, 10) for _ in range(2000)]
block = random.Random(7)
for i in range(600, 760):
    if block.random() < 0.5:
        vals[i] = -999.0          # one extract encoded missing as -999

df = pl.DataFrame({"amount": vals})
report = onceover.profile(df)

for f in report.findings:
    if f.get("type") == "sentinel_values":
        print(f["claim"])
        print("   evidence:", f["evidence"][0]["metrics"])

column amount uses -999 as a probable missing-data sentinel: 92 occurrences (4.6% of 2000 rows), 74 of them inside rows 623-749 (a 127-row window, 6.3% of the column); the value sits outside the rest of the column's bulk [-0.0233, 99.2043]; excluding it the column mean is 50.1977 (vs 1.9346 with it)
   evidence: {'bulk_fence_high': 99.20433504548122, 'bulk_fence_low': -0.023342785138240174, 'mean_excluding': 50.19772138200961, 'mean_with': 1.9346261984370938, 'method': "exact scan: shortest window covering >= 80% of a repeated value's occurrences spans <= 25% of the column, and the value lies outside the Tukey far-out fence (q1 - 3*IQR, q3 + 3*IQR) of the column's other values or across their sign boundary", 'occurrences': 92, 'share': 0.046, 'value': -999.0, 'window_end_row': 749, 'window_occurrences': 74, 'window_span_rows': 127, 'window_start_row': 623}

Reading the evidence¶

value is the code. occurrences is its count, and window_start_row and window_end_row bound the shortest window holding at least 80% of the occurrences. mean_with and mean_excluding give the column mean computed with the code treated as data and with its rows removed — the size of the distortion the code was causing.

What the detector does not report¶

Repeated values inside the bulk. Zeros in a zero-inflated count, or a common code in a numeric column, sit inside the fence and are not reported: they are the column’s distribution, not an encoding of missingness.
Scattered implausible values. A far-out value spread evenly over the column has no covering window. The surprises pass may report individual extremes, but the sentinel test requires the window.
Codes below the count floor. Fewer than 20 occurrences are not reported, however implausible the value: at small counts the window test cannot separate a code from a cluster of genuine values.