Imputation model - species prediction - old (result - csv file)

[riyaeliza123]

This is for the imputation model that will only be run once to produce results

[riyaeliza123]

SELECT count(DISTINCT tag_id_long)
from field 
where species IS NULL

count: 55

[riyaeliza123]

This model cannot be implemented due to scarcity of data, a report will be written on how to implement the model if the challenges can be overcome.

[riyaeliza123]

Working on this using lab confirmed species

SELECT fg.date, fg.watershed, 
        fg.river, fg.site, 
        fg.method, fg.local, 
        fg.water_temp_start, 
        fg.fork_length_mm, fg.species as annoted_species, species_id.species as confirmed_species, fg.tag_id_long
FROM field_genetics fg 
INNER JOIN species_id ON species_id.indiv = fg.indiv
WHERE species_id.species = 'coho' OR species_id.species = 'chinook'OR species_id.species = 'steelhead'

[riyaeliza123]

The reason only these are pulled is because the rest of the data have either 2 or more predictions, or have the prediction = None

[riyaeliza123]

WITH table_1 AS(
SELECT fg.date, fg.watershed, 
        fg.river, fg.site, 
        fg.method, fg.local, 
        fg.water_temp_start, 
        fg.fork_length_mm, fg.species as annoted_species, species_id.species as confirmed_species, fg.tag_id_long
FROM field_genetics fg 
INNER JOIN species_id ON species_id.indiv = fg.indiv
WHERE species_id.species = 'coho' OR species_id.species = 'chinook'OR species_id.species = 'steelhead')

SELECT tag_id_long, date, watershed, river, site, method, local, fork_length_mm, species
FROM field A
WHERE NOT EXISTS (SELECT 1 FROM table_1 B WHERE B.tag_id_long = A.tag_id_long);

Data that has to be imputed

[riyaeliza123]

Th CSV has been created!