Passer au contenu

Insertion des prélèvements et analyses

insertion-des-prelevements-et-analyses.Rmd
library(data.nitrates)
# Lire le fichier de configuration
config <- yaml::read_yaml("config.yml")

# Accéder aux valeurs pour version et last_year
version <- config$version
last_year <- config$last_year

Données ESO et ESU de l’ARS

Chargement des données ARS brutes

La table des données brutes Nitrates de l’ARS est chargée :

nitrate_data_analyse_ars <- datalibaba::importer_data(
  table = "nitrate_data_analyse_ars",
  schema = "nitrates",
  db = "si_eau",
  user = "csd"
)

Consolidation des données ARS

On ajoute les variables source et code_support :

# Ajouter les variables source et code_support
nitrate_prelevement_analyse_ars <- nitrate_data_analyse_ars |>
  dplyr::mutate(
    source = "ARS",
    code_reseau = NA,
    code_support = 3,
    code_fraction_analysee = NA,
    limite_detection = NA,
  )

On remplace “h” par “:” dans la variable plv_heure :

# Remplacer "h" par ":" dans la colonne plv_heure
nitrate_prelevement_analyse_ars <- nitrate_prelevement_analyse_ars |>
  dplyr::mutate(plv_heure = stringr::str_replace_all(plv_heure, "h", ":"))

On supprime les enregistrements correspondants à des totaux :

# Supprimer les lignes ne correspondant pas à une analyse
nitrate_prelevement_analyse_ars <- nitrate_prelevement_analyse_ars |>
  dplyr::filter(!stringr::str_starts(param_nom, "Total"))

On remplace les chaînes de caractère par NA dans la variable ana_param_alpha_resultat :

# Remplacer chaînes de caractère dans la colonne ana_param_alpha_resultat
nitrate_prelevement_analyse_ars <- nitrate_prelevement_analyse_ars |>
  dplyr::mutate(
    ana_param_alpha_resultat = dplyr::case_when(
      ana_param_alpha_resultat %in% c("TRACES", "PRESENCE", "SEUIL", "ILLISIBL", "N.M.", "O", "?", ",", " ") ~ NA_character_,
      TRUE ~ ana_param_alpha_resultat
    )
  )

On remplace des valeurs dans ana_param_alpha_resultat et param_code:

# Remplacer les valeurs dans les colonnes ana_param_alpha_resultat et param_code
nitrate_prelevement_analyse_ars <- nitrate_prelevement_analyse_ars |>
  dplyr::mutate(
    ana_param_alpha_resultat = stringr::str_replace(ana_param_alpha_resultat, "\\,", "."),
    param_code = stringr::str_replace(param_code, "NO3", "1340")
  )

On affecte le code_remarque et la valeur des variables resultat_analyse et limite_quantification :

# Ajouter les colonnes code_remarque, resultat_analyse et limite_quantification
nitrate_prelevement_analyse_ars <- nitrate_prelevement_analyse_ars |>
  dplyr::mutate(
    # Ajout de la colonne code_remarque selon la condition spécifiée
    code_remarque = dplyr::case_when(
      stringr::str_starts(ana_param_alpha_resultat, "<") ~ 10,
      TRUE ~ 1
    ),
    # Renommage conditionnel des colonnes
    resultat_analyse = dplyr::case_when(
      stringr::str_starts(ana_param_alpha_resultat, "<") ~ as.character(ana_param_valeur_traduite),
      TRUE ~ ana_param_alpha_resultat
    ),
    limite_quantification = dplyr::case_when(
      stringr::str_starts(ana_param_alpha_resultat, "<") ~ ana_param_alpha_resultat,
      TRUE ~ NA_character_  # Utilisation de NA pour les valeurs non pertinentes
    )
  )

On supprime “<” dans la variable limite_quantification :

# Remplacer "h" par ":" dans la colonne plv_heure
nitrate_prelevement_analyse_ars <- nitrate_prelevement_analyse_ars |>
  dplyr::mutate(limite_quantification = stringr::str_replace_all(limite_quantification, "<", ""))

On sélectionne les champs utiles à la table des prélèvements et analyse :

# Sélectionner les variables
nitrate_prelevement_analyse_ars <- nitrate_prelevement_analyse_ars |>
  dplyr::select(code_intervenant = geo_dept_ddass_gest_code,
                source,
                code_reseau,
                code_station = ins_code_national,
                date_prelevement = plv_date,
                heure_prelevement = plv_heure,
                code_support,
                nature_eau,
                id_usage = usage,
                id_prelevement_motif = plv_motif,
                date_analyse = anl_date_fin_analyse,
                resultat_analyse,
                code_parametre = param_code,
                code_fraction_analysee,
                code_remarque,
                limite_detection,
                limite_quantification)
# Modifier le type de la variable resultat_analyse
nitrate_prelevement_analyse_ars$resultat_analyse <- as.numeric(nitrate_prelevement_analyse_ars$resultat_analyse)

Données Hub’eau ESO

Chargement des prélèvements Hub’eau ESO

La table des données brutes Nitrates Hub’eau ESO est chargée :

# Charger la table `qualite_nappes_eau_souterraine.nitrate_qualite_nappes_analyses`
nitrate_qualite_nappes_prelevements <- datalibaba::importer_data(
  table = "nitrate_qualite_nappes_analyses",
  schema = "qualite_nappes_eau_souterraine",
  db = "si_eau",
  user = "csd"

Filtre par code_producteur pour exclure les données ARS

On sélectionne les code_producteur correspondants aux enregistrements hors données ARS de la région :

# Filtrer pour exclure les données ARS
nitrate_prelevement_analyse_hubeau_eso <- nitrate_qualite_nappes_prelevements |>
  dplyr::filter(!code_producteur %in% c('44','49','53','72','85'))

Consolidation des prélèvements Hub’eau ESO

On ajoute les variables source, code_support et nature_eau :

# Ajouter les variables source, code_support et nature_eau
nitrate_prelevement_analyse_hubeau_eso <- nitrate_prelevement_analyse_hubeau_eso |>
  dplyr::mutate(
    source = "ADES",
    heure_prelevement = NA,
    code_support = 3,
    nature_eau = "ESO",
    id_usage = NA,
    id_prelevement_motif = NA,
    date_analyse = NA)

On remplace des valeurs dans limite_detection et limite_quantification :

# Remplacer les valeurs dans les colonnes limite_detection et limite_quantification
nitrate_prelevement_analyse_hubeau_eso <- nitrate_prelevement_analyse_hubeau_eso |>
  dplyr::mutate(
    resultat = stringr::str_replace(resultat, "\\,", "."),
    limite_detection = stringr::str_replace(limite_detection, "\\,", "."),
    limite_quantification = stringr::str_replace(limite_quantification, "\\,", ".")
  )

On modifie le type des variables numériques et on les renomme :

# Remplacer les valeurs dans les colonnes resultat_analyse et limite_quantification
nitrate_prelevement_analyse_hubeau_eso <- nitrate_prelevement_analyse_hubeau_eso |>
  dplyr::rename(code_intervenant = code_lieu_analyse,
                code_reseau = codes_reseau,
                code_station = bss_id,
                date_prelevement = date_debut_prelevement
  ) |>
  dplyr::mutate(code_support = as.integer(code_support),
                resultat_analyse = as.numeric(resultat),
                limite_detection = as.numeric(limite_detection),
                limite_quantification = as.numeric(limite_quantification),
                code_parametre = as.integer(code_param),
                code_fraction_analysee  = as.integer(code_fraction),
                code_remarque = as.integer(code_remarque_analyse)
  )

On sélectionne les champs utiles à la table des prélèvements et analyses :

# Sélectionner les variables
nitrate_prelevement_analyse_hubeau_eso <- 
  nitrate_prelevement_analyse_hubeau_eso |>
  dplyr::select(code_intervenant,
                source,
                code_reseau,
                code_station,
                date_prelevement,
                heure_prelevement,
                code_support,
                nature_eau,
                id_usage,
                id_prelevement_motif,
                date_analyse,
                resultat_analyse,
                code_parametre,
                code_fraction_analysee,
                code_remarque,
                limite_detection,
                limite_quantification)

Données Hub’eau ESU

Chargement des prélèvements Hub’eau ESU

La table des données brutes Nitrates Hub’eau ESU est chargée :

# Charger la table qualite_nappes_eau_souterraine.nitrate_qualite_rivieres_analyse_pc
nitrate_qualite_rivieres_prelevements <- datalibaba::importer_data(
  table = "nitrate_qualite_rivieres_analyse_pc",
  schema = "qualite_cours_d_eau",
  db = "si_eau",
  user = "csd")

On remplace des valeurs dans limite_detection et limite_quantification :

# Remplacer les valeurs dans les colonnes limite_detection et limite_quantification
nitrate_prelevement_analyse_hubeau_esu <- nitrate_qualite_rivieres_prelevements |>
  dplyr::mutate(
    resultat = stringr::str_replace(resultat, "\\,", "."),
    limite_detection = stringr::str_replace(limite_detection, "\\,", "."),
    limite_quantification = stringr::str_replace(limite_quantification, "\\,", ".")
  )

On ajoute les variables source et nature_eau :

# Ajouter les variables source et nature_eau
nitrate_prelevement_analyse_hubeau_esu <- nitrate_prelevement_analyse_hubeau_esu |>
  dplyr::mutate(
    source = "Na\u00efades",
    heure_prelevement = NA,
    code_support = 3,
    nature_eau = "ESU",
    id_usage = NA,
    id_prelevement_motif = NA,
    date_analyse = NA)

On modifie le type des variables resultat_analyse et limite_quantification :

# Remplacer les valeurs dans les colonnes resultat_analyse et limite_quantification
nitrate_prelevement_analyse_hubeau_esu <- nitrate_prelevement_analyse_hubeau_esu |>
  dplyr::mutate(resultat_analyse = as.numeric(resultat),
                limite_detection = as.numeric(limite_detection),
                limite_quantification = as.numeric(limite_quantification),
                code_parametre = as.integer(code_parametre),
                code_fraction_analysee  = as.integer(code_fraction),
                code_remarque = as.integer(code_remarque)
  )

On sélectionne les champs utiles à la table des prélèvements et analyses :

# Sélectionner les variables
nitrate_prelevement_analyse_hubeau_esu <- 
  nitrate_prelevement_analyse_hubeau_esu |>
  dplyr::select(code_intervenant = code_laboratoire,
                source,
                code_reseau,
                code_station,
                date_prelevement,
                heure_prelevement,
                code_support,
                nature_eau,
                id_usage,
                id_prelevement_motif,
                date_analyse,
                resultat_analyse,
                code_parametre,
                code_fraction_analysee,
                code_remarque,
                limite_detection,
                limite_quantification)

Publication en base

Agréation des données dans un dataframe unique 

# Lier les trois dataframes par source dans un dataframe global
nitrate_prelevement_analyse <- 
  rbind(nitrate_prelevement_analyse_ars, 
        nitrate_prelevement_analyse_hubeau_eso, 
        nitrate_prelevement_analyse_hubeau_esu)

Ajout d’un identifiant unique 

# Créer un identifiant unique 
nitrate_prelevement_analyse <- nitrate_prelevement_analyse |>
  dplyr::mutate(code_prelevement_analyse = dplyr::row_number()) |>
  dplyr::select(code_prelevement_analyse,
                code_intervenant,
                source,
                code_reseau,
                code_station,
                date_prelevement,
                heure_prelevement,
                code_support,
                nature_eau,
                id_usage,
                id_prelevement_motif,
                date_analyse,
                resultat_analyse,
                code_parametre,
                code_fraction_analysee,
                code_remarque,
                limite_detection,
                limite_quantification)

Insertion du dataframe en base

On insère enfin le dataframe consolidé dans la table globale et versionnée des analyses et prélèvements :

# Charger les données dans une nouvelle table en base
datalibaba::poster_data(
  data = nitrate_prelevement_analyse, 
  table = glue::glue("nitrate_prelevement_analyse_", version), 
  schema = "nitrates", 
  db = "si_eau",
  overwrite = TRUE,
  pk = "code_prelevement_analyse",
  user = "csd")

Ajout des commentaires 

# Établir une connexion à la base de données PostgreSQL
connexion <- datalibaba::connect_to_db(db = "si_eau", user = "csd")
  
# Génération de la date du jour
date_now <- format(Sys.Date(), "%d/%m/%Y")

# Liste des requêtes SQL
table_comment <- glue::glue("COMMENT ON TABLE nitrates.nitrate_prelevement_analyse_{version} IS 
      'Table des prélèvements et analyses 2007-{last_year} (version {version} du {date_now})';")

# Exécuter la commande SQL
DBI::dbExecute(connexion, table_comment)

# Ajouter les commentaires sur chaque champ
comments_sql <- c(
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_prelevement_analyse IS 'Identifiant du prélèvement';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_intervenant IS 'Identifiant de l''intervenant';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.source IS 'Source de la donnée';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_reseau IS 'Identifiant du réseau';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_station IS 'Identifiant de la station';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.date_prelevement IS 'Date du prélèvement';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.heure_prelevement IS 'Heure du prélèvement';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_support IS 'Code du support de prélèvement';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.nature_eau IS 'Nature de l''eau du prélèvement (ESO/ESU)';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.id_usage IS 'Code de l''usage du prélèvement';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.id_prelevement_motif IS 'Code du motif du prélèvement';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_parametre IS 'Identifiant du paramètre analysé';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_fraction_analysee IS 'Identifiant de la fraction analysée';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.date_analyse IS 'Date de l''analyse';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.resultat_analyse IS 'Résultat de l''analyse';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.code_remarque IS 'Code validant la donnée';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.limite_detection IS 'Limite de détection';"),
  glue::glue("COMMENT ON COLUMN nitrates.nitrate_prelevement_analyse_{version}.limite_quantification IS 'Limite de quantification';")
)
  
 # Exécuter chaque commande SQL
  for (sql in c(comments_sql)) {
    DBI::dbExecute(connexion, sql)
  }

  # Fermeture de la connexion à la base de données
  DBI::dbDisconnect(connexion)