LACFM
Replace missing conversion factors with the median over all years
Starr suggests “Find missing conversion factor fields and insert correct value for relevant state code and fishing year. Missing fields can be inferred from the median of the non-missing fields”. For each state_code replace missing values with median.
Although Bentley (2012) states “In this implementation we replace missing values with the median over all fishing_years for that state_code”, his code actually calculates the median by species, state and fishing year. Bentley’s code only retained values with at least 10000 non-null records of a state in a year; presumably the high threshold was due to the intention to assess the median over all years.
Here, the median per state and species is calculated at the fishing year level and no threshold is applied.
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LACFM', 'edw', 'landings', 'Fix', 'Replace missing conversion factors with the median over all years');
CREATE OR REPLACE FUNCTION LACFM() RETURNS VOID AS $$
with _lacfm as (
SELECT species_code, state_code, fishing_year,
percentile_cont(0.5) WITHIN GROUP (ORDER BY conv_factor) AS conv_factor
FROM groomed.landings
WHERE state_code IS NOT NULL AND conv_factor IS NOT NULL
GROUP BY species_code,state_code,fishing_year
--HAVING count(*)>10000
)
INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LACFM', 'landings', 'conv_factor', l.id, l.conv_factor::TEXT, c.conv_factor::TEXT
FROM groomed.landings l
JOIN _lacfm c ON l.state_code = c.state_code
AND l.species_code = c.species_code
AND l.fishing_year = c.fishing_year
WHERE l.conv_factor = 0
OR l.conv_factor IS NULL;
UPDATE groomed.landings SET checks = array_append(checks, 'LACFM'),
conv_factor = u.new::NUMERIC
FROM groomed.checks u
WHERE u.code = 'LACFM'
AND groomed.landings.id = u.id;
$$ LANGUAGE SQL;
SELECT LACFM();Effects of grooming
