Sanitize implausible numeric measurements (physiologic limits or statistical outliers)
Source:R/dqd.R
sanitize_measurements.RdScreens measurement.value_as_number for artifacts such as a systolic blood pressure of 0 or 999,
negative lab values, or NaN/Inf, and returns the numeric measurements with a sanitized value, the raw
value and a per-row status (RFC 6.1). Everything is computed in DuckDB SQL (no temporary or persistent
objects are created and the database is only read), so it works on a read-only connection and gives the
same result as omop_etl.sanitize_measurements() in Python.
Usage
sanitize_measurements(
con,
cohort_table = NULL,
method = "dqd_biologic_limits",
action = "nullify",
outlier_action = NULL,
measurement_concept_ids = NULL,
limits = NULL,
iqr_multiplier = 3,
z_threshold = 4,
person_col = "subject_id",
measurement_table = "measurement"
)Arguments
- con
Active DuckDB DBI connection to the OMOP CDM database (read-only is fine).
- cohort_table
Optional table or view (
"name"or"schema.name") restricting the scope to the persons listed in itsperson_col. Duplicate cohort rows do not duplicate measurements.NULL= everyone.- method
One of
"dqd_biologic_limits"(default),"winsorize_iqr"or"z_score_cutoff".- action
One of
"nullify"(default),"clamp"or"drop_row".- outlier_action
Optional alias for
action(e.g."winsorize","drop").- measurement_concept_ids
Optional integer vector: only sanitize these
measurement_concept_idvalues.NULL= all concepts.- limits
Optional data.frame of custom limits for
"dqd_biologic_limits"with columnsconcept_id,min_value,max_valueand optionallyunit_concept_id(names are case-insensitive, extra columns are ignored). See Details.- iqr_multiplier
Fence width
kfor"winsorize_iqr"(default 3; 1.5 is the classic Tukey fence). Must be positive and finite.- z_threshold
Cut-off for
"z_score_cutoff"(default 4). Must be positive and finite.- person_col
Person id column of
cohort_table(default"subject_id"). It must exist; there is no fallback to another column.- measurement_table
Optional measurement table or view name (default
"measurement").
Value
A data.frame with one row per in-scope measurement (rows whose value_as_number is NULL are
not part of the result), ordered by measurement_id, with columns measurement_id, person_id,
measurement_concept_id, measurement_date, unit_concept_id, value_as_number (sanitized),
value_as_number_raw, sanitize_status and, when the measurement table has them,
measurement_datetime and visit_occurrence_id. sanitize_status is one of "ok", "below_min",
"above_max", "invalid_number", "no_limit", "unit_skipped" or "insufficient_data". The
per-concept report is attached as attr(x, "sanitization_report").
Details
Methods. "dqd_biologic_limits" compares each value with the bundled per-concept, per-unit
plausibility limits (inst/extdata/physiologic_limits.csv, see
system.file("extdata", "physiologic_limits.csv", package = "omopduckdb")) or with limits.
"winsorize_iqr" flags values outside the Tukey fences Q1 - k*IQR .. Q3 + k*IQR of their
(concept, unit) group (k = iqr_multiplier). "z_score_cutoff" flags values with |z| > z_threshold
using the group mean and sample standard deviation.
Actions. "nullify" sets a flagged value to NA; "clamp" moves it to the violated bound (for
"winsorize_iqr" the fence, for "z_score_cutoff" mean +/- z_threshold * sd); "drop_row" removes the
row from the result.
Invalid numbers. NaN and +/-Inf are invalid under every method, whether or not a limit exists
(sanitize_status = "invalid_number"). "nullify" and "drop_row" behave as usual. "clamp" moves
+Inf/-Inf to the group's upper/lower bound when there is one, and falls back to NA for NaN and
whenever no finite bound applies.
Inclusive bounds. A value equal to a bound is "ok".
Units ("dqd_biologic_limits"). A limit row applies only when the measurement's unit_concept_id
equals the row's. A concept that has limits only in other units, or a measurement with a NULL/0 unit,
is left untouched and reported as "unit_skipped" (a creatinine in umol/L is never compared with the
mg/dL bounds). A concept with no limit row at all is "no_limit".
Custom limits. A limits row with a unit_concept_id replaces the bundled row for that concept
and unit (or adds one). A row without a unit (NA, or no such column) is an any-unit limit: it replaces
every bundled row of the concept and applies whatever unit the measurement has, including NULL/0. A
missing min_value or max_value leaves that side open. A unit-specific row wins over an any-unit row
of the same concept. Passing limits with another method warns and is ignored.
Statistical methods. Statistics are computed per (measurement_concept_id, unit_concept_id) (a
NULL unit is its own group) over the finite, in-scope values, i.e. after the cohort and concept
restrictions. A group with fewer than 3 finite values, a zero IQR ("winsorize_iqr") or a zero or
undefined standard deviation ("z_score_cutoff") has no usable spread: its values are kept and reported
as "insufficient_data". measurement_concept_id = 0 (unmapped) is never pooled into a group and is
reported as "no_limit". Quartiles use linear interpolation (DuckDB quantile_cont, the same as R's
quantile(type = 7) and numpy's default).
The default "dqd_biologic_limits" never guesses a unit, so an ETL that leaves unit_concept_id at 0 (this
package's PCORnet ETL does for lab results: the text stays in unit_source_value) gets no screening of
those rows. A warning is raised when more than half of the measurements of a concept with limits are skipped
for that reason: map the units in the ETL, or pass limits rows without unit_concept_id (an any-unit
limit) for the concepts you want screened.
Choosing an action. "nullify" (or "drop_row") is the right action for the artifacts
"dqd_biologic_limits" finds: a blood pressure of 0 or 999 is a sentinel, not an extreme measurement, and
"clamp" would turn it into 40 or 300, a plausible-looking value that downstream models treat as real. Use
"clamp" (winsorization) only for values that are extreme but real.
Skewed data and masking. "winsorize_iqr" and "z_score_cutoff" assume a roughly symmetric
distribution. Right-skewed labs (creatinine, ALT, CRP, ...) have a long valid tail, so clinically important
values (an AKI creatinine of 3-6 mg/dL) can be nullified or clamped: prefer "dqd_biologic_limits" for
such labs, or transform them first. "z_score_cutoff" is also not robust: the outliers themselves inflate
the mean and standard deviation, and with a sample standard deviation no value of a group of n can have
|z| above (n - 1) / sqrt(n), so the default z_threshold = 4 cannot flag anything in a group of fewer
than 18 values.
Report. attr(x, "sanitization_report") is a data.frame with one row per measurement_concept_id
and unit_concept_id and columns measurement_concept_id, unit_concept_id, n, n_ok, n_below,
n_above, n_invalid, n_changed, n_dropped, n_no_limit, n_unit_skipped and
n_insufficient_data. It counts every in-scope row, including dropped ones, and reconciles exactly:
n = n_ok + n_below + n_above + n_invalid + n_no_limit + n_unit_skipped + n_insufficient_data and
n_changed + n_dropped = n_below + n_above + n_invalid.
Examples
con <- DBI::dbConnect(duckdb::duckdb())
#> duckdb keeps downloaded extensions and secrets in a temporary directory:
#> ℹ /tmp/RtmpFV3zps/duckdb
#> This is removed when the R session ends.
#> • Extensions are re-downloaded each session.
#> • Secrets are lost.
#> ℹ Run duckdb(shared_home = TRUE) (or create ~/.duckdb) to keep them (suitable for most users).
#> ℹ Run duckdb(shared_home = FALSE) to accept the temporary directory (and silence this message).
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbExecute(con, "
CREATE TABLE measurement AS SELECT * FROM (VALUES
(1, 1, 3004249, DATE '2021-01-01', 8876, 120.0),
(2, 1, 3004249, DATE '2021-01-02', 8876, 999.0),
(3, 2, 3004249, DATE '2021-01-03', 8876, 0.0),
(4, 2, 3016723, DATE '2021-01-04', 8749, 88.0)
) AS t(measurement_id, person_id, measurement_concept_id, measurement_date, unit_concept_id,
value_as_number)")
#> [1] 4
clean <- sanitize_measurements(con)
clean[, c("measurement_id", "value_as_number", "value_as_number_raw", "sanitize_status")]
#> measurement_id value_as_number value_as_number_raw sanitize_status
#> 1 1 120 120 ok
#> 2 2 NA 999 above_max
#> 3 3 NA 0 below_min
#> 4 4 88 88 ok
attr(clean, "sanitization_report")
#> measurement_concept_id unit_concept_id n n_ok n_below n_above n_invalid
#> 1 3004249 8876 3 1 1 1 0
#> 2 3016723 8749 1 1 0 0 0
#> n_changed n_dropped n_no_limit n_unit_skipped n_insufficient_data
#> 1 2 0 0 0 0
#> 2 0 0 0 0 0
DBI::dbDisconnect(con, shutdown = TRUE)