FEMPI

Replace missing methods if there is only one method used on the trip (by form type)

Starr (2007) suggests replacing missing values for the column primary_method with the value recorded for other fishing events in the trip providing those other values are all the same.

Here methods are only copied from events on the same trip with the same form type as the event with the missing method.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('FEPMI', 'edw', 'effort', 'Fix', 'Replace missing methods if there is only one method used on the trip (by form type)');

CREATE OR REPLACE FUNCTION FEPMI()
RETURNS VOID AS $$

with _fepmi as (
  SELECT trip, form_type, s.primary_method
  FROM (
      SELECT trip, form_type, MAX(primary_method) AS primary_method
      FROM groomed.effort
      WHERE trip IS NOT NULL AND primary_method IS NOT NULL
      GROUP BY trip, form_type
      HAVING COUNT(DISTINCT primary_method) = 1
  ) s
  WHERE trip IN (
      SELECT DISTINCT trip FROM groomed.effort WHERE primary_method IS NULL
  )
)
INSERT INTO groomed.checks (code, "table", "column", id, details, orig, new)
SELECT 'FEPMI', 'effort', 'primary_method', e.id, e.trip::TEXT, NULL, _fepmi.primary_method
FROM   _fepmi
JOIN   groomed.effort e ON e.trip = _fepmi.trip AND e.form_type = _fepmi.form_type
AND    e.primary_method IS NULL;

UPDATE groomed.effort SET checks = checks || '{FEPMI}', primary_method = u.new
FROM   groomed.checks u
WHERE  u.code = 'FEPMI'
AND    groomed.effort.primary_method IS NULL
AND    groomed.effort.id = u.id;

$$ LANGUAGE SQL;
SELECT FEPMI();

Effects of grooming

Figure 1: The number of records affected by the FEPMI rule, by year and source.

Figure 2: The proportion of records affected by the FEPMI rule, by form type, for all form types where some records are groomed by the FEPMI rule.