DCFxx

Impute missing values within a form

On a paper form with a row-based layout, fishers may only fill in a value for certain fields on the first row with the expectation that this value is copied down into subsequent rows.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
CREATE TABLE dcf_imputation (code TEXT PRIMARY KEY,
                             form_type TEXT[],
                             impute_column TEXT,
                             all_eq_column TEXT);

INSERT INTO dcf_imputation VALUES
    ('DCFPM', '{"CEL"}', 'primary_method', NULL),
    ('DCFTS', '{"CEL"}', 'target_species', NULL),
    ('DCFEN', '{"CEL"}', 'effort_num', 'primary_method'),
    ('DCFHN', '{"CEL"}', 'total_hook_num', 'primary_method'),
    ('DCFNL', '{"CEL"}', 'total_net_length', 'primary_method'),
    -- ('DCFFD', '{"CEL"}', 'fishing_duration', NULL),
    -- ('DCFED', '{"CEL"}', 'effort_depth', NULL),
    -- ('DCFEH', '{"CEL"}', 'effort_height', NULL),
    -- ('DCFES', '{"CEL"}', 'effort_speed', NULL),
    -- ('DCFBD', '{"CEL"}', 'bottom_depth', NULL),
    -- ('DCFSX', '{"CEL"}', 'start_longitude', NULL),
    -- ('DCFEX', '{"CEL"}', 'end_longitude', NULL),
    -- ('DCFSY', '{"CEL"}', 'start_latitude', NULL),
    -- ('DCFEY', '{"CEL"}', 'end_latitude', NULL),
    ('DCFAC', '{"CEL"}', 'start_stats_area_code', NULL);


CREATE OR REPLACE FUNCTION DCFxx() RETURNS VOID AS $$
DECLARE
    dcf_rules RECORD;
BEGIN
    RAISE NOTICE 'Applying DCFxx rules...';

    FOR dcf_rules IN
       SELECT code,
              UNNEST(form_type) AS form_type,
              impute_column,
              COALESCE(all_eq_column, 'dcf_key') AS all_eq_column
         FROM dcf_imputation
    LOOP
        DROP TABLE IF EXISTS _dcf;
              
        EXECUTE format('CREATE TEMP TABLE _dcf AS
                        SELECT $1::char(5) AS code, 
                               e.id, 
                               MIN(f.%I) AS new_value,
                               e.dcf_key
                          FROM groomed.effort e
                          JOIN groomed.effort f ON e.dcf_key = f.dcf_key
                         WHERE e.%I IS NULL
                           AND f.%I IS NOT NULL
                           AND e.%I = f.%I
                           AND e.form_type IN ($2)
                      GROUP BY e.id, e.dcf_key
                        HAVING COUNT(DISTINCT f.%I) = 1',
                        dcf_rules.impute_column, 
                        dcf_rules.impute_column, 
                        dcf_rules.impute_column, 
                        dcf_rules.all_eq_column, 
                        dcf_rules.all_eq_column,
                        dcf_rules.impute_column) 
            USING dcf_rules.code, dcf_rules.form_type;   
                              
        INSERT INTO groomed.checks(code, "table", "column", id, orig, new, details)
        SELECT code, 'effort', dcf_rules.impute_column, id, NULL::TEXT, new_value,
               'dcf_key ' || dcf_key::TEXT
          FROM _dcf;

        EXECUTE format('UPDATE groomed.effort
                           SET checks = array_append(checks, u.code), %I = u.new_value
                          FROM _dcf u
                         WHERE groomed.effort.id = u.id', dcf_rules.impute_column);

    END LOOP;

    RAISE NOTICE 'Done applying DCFxx rules.';
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT DCFxx();