Introduction
En ces temps de confinement, on peut voit fleurir tout un tas de tableau de bord pour suivre l’évolution de la pandémie de COVID 19. Le plus suivi semble être celui de l’Université John Hopkins (disponible ici).
Je me suis dit que moi aussi j’allais tenter l’expérience, les données sont facilement récupérables sur internet et j’ai à ma disposition un formidable outil de visualisation de données : GEO KEY (plus d’info ici).
Tout d’abord, voici le résultat (cliquez ici pour l’afficher dans un nouvel onglet) :
Je n’aborderai pas la création de l’application, nous allons nous concentrer sur la récupération et le traitement des données.
Nous allons donc créer deux scripts :
- Un script shell qui sera lancé toutes les nuits vers 5h pour effectuer les actions à mener.
- Un script SQL qui sera lancé par le précédent script pour traiter les données.
La machine sur laquelle s’exécute le script shell est une machine Linux. Il faudra légèrement adapter ce script si vous souhaiter le reproduire sur Windows.
Shell script
Nous allons créer un fichier « import_covid.sh » qui contiendra toutes les actions à mener. Ce fichier sera présent dans /home/my_user/covid19
Récupération des données brutes
Dans un premier temps, nous allons récupérer les données sur la pandémie de COVID 19. Deux sources sont ici utilisées :
- Les données de l’Université John Hopkins : https://github.com/CSSEGISandData/COVID-19
- Les données françaises, compilées chaque nuit par des contributeurs : https://github.com/opencovid19-fr/data
Pour récupérer des données, rien de plus simple :
# Définition des répertoires
# Script
var_script_dir="/home/my_user/covid19"
# Données
var_data_dir="${var_script_dir}/data"
# Supression des fichiers précédemment téléchargés
rm ${var_data_dir}/*
# Données France
# Récupération des données
wget -O "${var_data_dir}/data_fr.csv" "https://github.com/opencovid19-fr/data/raw/master/dist/chiffres-cles.csv"
# Données Monde
# Récupération des données "cas confirmés"
wget -O "${var_data_dir}/data_monde_cas.csv" "https://github.com/CSSEGISandData/COVID-19/raw/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv"
# Récupération des données "morts"
wget -O "${var_data_dir}/data_monde_mort.csv" "https://github.com/CSSEGISandData/COVID-19/raw/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"
# Récupération des données "guéris"
wget -O "${var_data_dir}/data_monde_gueris.csv" "https://github.com/CSSEGISandData/COVID-19/raw/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv"
Transfert en base
Il faut ensuite transférer les données en base.
Le problème est que les données en provenance de l’Université John Hopkins changent de structure chaque jour : Les données sont structurées avec une colonne pour chaque date (on a donc une nouvelle colonne chaque jour).
Pour remédier à ce problème, j’utilise pg_futter (plus d’infos ici) qui permet, à partir d’un fichier CSV, de créer une table dont la structure est identique a ce fichier puis d’y insérer les données qu’il contient.
# Paramètres
# BDD de destination
var_bdd_host="127.0.0.1"
var_bdd_port="5432"
var_bdd_name="my_bdd"
var_bdd_user="postgres"
var_bdd_pwd="postgres"
# Données France
# Nettoyage table d'import (on va recréer la structure avec pg_futter)
PGPASSWORD=${var_bdd_pwd} psql -h ${var_bdd_host} -p ${var_bdd_port} -U ${var_bdd_user} -d ${var_bdd_name} -c "DROP TABLE IF EXISTS covid19.import_data_fr;"
# Transfert en base
${var_script_dir}/pgfutter --host ${var_bdd_host} --port ${var_bdd_port} --user ${var_bdd_user} --pw ${var_bdd_pwd} --db ${var_bdd_name} --schema covid19 --table import_data_fr csv "${var_data_dir}/data_fr.csv"
# Données monde
# Cas
# Nettoyage table d'import (on va recréer la structure avec pg_futter)
PGPASSWORD=${var_bdd_pwd} psql -h ${var_bdd_host} -p ${var_bdd_port} -U ${var_bdd_user} -d ${var_bdd_name} -c "DROP TABLE IF EXISTS covid19.import_data_monde_cas;"
# Transfert en base
${var_script_dir}/pgfutter --host ${var_bdd_host} --port ${var_bdd_port} --user ${var_bdd_user} --pw ${var_bdd_pwd} --db ${var_bdd_name} --schema covid19 --table import_data_monde_cas csv "${var_data_dir}/data_monde_cas.csv"
# Morts
# Nettoyage table d'import (on va recréer la structure avec pg_futter)
PGPASSWORD=${var_bdd_pwd} psql -h ${var_bdd_host} -p ${var_bdd_port} -U ${var_bdd_user} -d ${var_bdd_name} -c "DROP TABLE IF EXISTS covid19.import_data_monde_mort;"
# Transfert en base
${var_script_dir}/pgfutter --host ${var_bdd_host} --port ${var_bdd_port} --user ${var_bdd_user} --pw ${var_bdd_pwd} --db ${var_bdd_name} --schema covid19 --table import_data_monde_mort csv "${var_data_dir}/data_monde_mort.csv"
# Guéris
# Nettoyage table d'import (on va recréer la structure avec pg_futter)
PGPASSWORD=${var_bdd_pwd} psql -h ${var_bdd_host} -p ${var_bdd_port} -U ${var_bdd_user} -d ${var_bdd_name} -c "DROP TABLE IF EXISTS covid19.import_data_monde_gueris;"
# Transfert en base
${var_script_dir}/pgfutter --host ${var_bdd_host} --port ${var_bdd_port} --user ${var_bdd_user} --pw ${var_bdd_pwd} --db ${var_bdd_name} --schema covid19 --table import_data_monde_gueris csv "${var_data_dir}/data_monde_gueris.csv"
Script SQL
Maintenant que tout ce petit monde est présent en BDD, il faut traiter les données.
Pour cela, on va utiliser un peu de SQL. Le but est d’obtenir une seule table avec :
- Une ligne par couple emprise/date
- Une colonne par indicateur (cas, mort, guéris…)
- Des données consolidées
Structuration des données finales
J’ai d’abord créé deux tables dans ma base :
- data_raw : table dans laquelle je pousse toutes les données pré-traitées.
- data_clean : table dans laquelle sont stockées les données nettoyées et qui sera utilisée par mon application.
Voici leur structures :
CREATE TABLE covid19.data_raw ( id text NOT NULL DEFAULT uuid_generate_v4() PRIMARY_KEY, niveau text NULL, -- Niveau de "zoom" nom_niveau_1 text NULL DEFAULT ''::text, -- Nom si on est au niveau 1 ou du parent de niveau 1 nom_niveau_2 text NULL DEFAULT ''::text, -- Nom si on est au niveau 2 ou du parent de niveau 2 nom_niveau_3 text NULL DEFAULT ''::text, -- Nom si on est au niveau 3 "date" date NULL, cas int4 NULL DEFAULT 0, cas_en_cours int4 NULL DEFAULT 0, reanimation int4 NULL DEFAULT 0, hospitalises int4 NULL DEFAULT 0, gueris int4 NULL DEFAULT 0, mort int4 NULL DEFAULT 0, mort_ehpad int4 NULL DEFAULT 0 ); CREATE INDEX data_raw_date_idx ON covid19.data_raw USING btree (date); CREATE INDEX data_raw_niveau_idx ON covid19.data_raw USING btree (niveau); CREATE INDEX data_raw_nom_niveau_1_idx ON covid19.data_raw USING btree (nom_niveau_1); CREATE INDEX data_raw_nom_niveau_2_idx ON covid19.data_raw USING btree (nom_niveau_2); CREATE INDEX data_raw_nom_niveau_3_idx ON covid19.data_raw USING btree (nom_niveau_3); CREATE TABLE covid19.data_clean ( id text NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY, niveau text NULL, -- Niveau de "zoom" nom_niveau_1 text NULL, -- Nom si on est au niveau 1 ou du parent de niveau 1 nom_niveau_2 text NULL, -- Nom si on est au niveau 2 ou du parent de niveau 2 nom_niveau_3 text NULL, -- Nom si on est au niveau 3 "date" date NULL, cas int4 NULL DEFAULT 0, cas_variation int4 NULL DEFAULT 0, cas_en_cours int4 NULL DEFAULT 0, cas_en_cours_variation int4 NULL DEFAULT 0, reanimation int4 NULL DEFAULT 0, reanimation_variation int4 NULL DEFAULT 0, hospitalises int4 NULL DEFAULT 0, hospitalises_variation int4 NULL DEFAULT 0, gueris int4 NULL DEFAULT 0, gueris_variation int4 NULL DEFAULT 0, mort int4 NULL DEFAULT 0, mort_variation int4 NULL DEFAULT 0, mort_ehpad int4 NULL DEFAULT 0, mort_ehpad_variation int4 NULL DEFAULT 0, ); CREATE INDEX data_clean_date_idx ON covid19.data_clean USING btree (date); CREATE INDEX data_clean_niveau_idx ON covid19.data_clean USING btree (niveau); CREATE INDEX data_clean_nom_niveau_idx ON covid19.data_clean USING btree (nom_niveau_1); CREATE INDEX data_clean_nom_niveau_2_idx ON covid19.data_clean USING btree (nom_niveau_2); CREATE INDEX data_clean_nom_niveau_3_idx ON covid19.data_clean USING btree (nom_niveau_3);
Traitements
Maintenant que nous avons nos deux tables, traitons les données.
Pour cela j’ajoute le fichier « traitement.sql » dans le même répertoire que celui de mon script shell. Dans ce fichier de traitement SQL, j’ajoute les codes suivants.
Les données France
D’abord on traite les données France.
DO
$corps$
DECLARE
var_emprise record;
var_date record;
BEGIN
RAISE NOTICE USING MESSAGE = 'Début traitement données FR';
-- Suppression des données récupérées précédemment
DELETE FROM covid19."data_raw"
WHERE
nom_niveau_1 = 'France'
;
-- Pour chaque emprise/secteur/zone de comptabilisation.
-- On groupe les données par emprise pour éviter les doublons.
FOR var_emprise IN
SELECT
CASE granularite
WHEN 'departement' THEN 'niveau_3'
WHEN 'region' THEN 'niveau_2'
WHEN 'pays' THEN 'niveau_1'
END AS niveau,
granularite,
maille_code,
maille_nom
FROM
covid19.import_data_fr
WHERE
granularite IN ('departement', 'pays', 'region')
GROUP BY
granularite,
maille_code,
maille_nom
ORDER BY
granularite,
maille_code
LOOP
--RAISE NOTICE USING MESSAGE = var_emprise.granularite || ' : ' || var_emprise.maille_nom;
-- Pour chaque date de relevée des données.
-- On groupe les données pour éviter les doublons. On utilise toujours la valeur maximale (mieux vaut surestimer les chiffres).
-- Si la valeur n'est pas numérique, alors on met 0.
FOR var_date IN EXECUTE
$a$SELECT
"date"::date,
CASE
WHEN max(cas_confirmes) SIMILAR TO '[0-9]+' THEN max(cas_confirmes)::NUMERIC
ELSE 0
END AS cas,
CASE
WHEN max(gueris) SIMILAR TO '[0-9]+' THEN max(gueris)::NUMERIC
ELSE 0
END AS gueris,
CASE
WHEN max(deces) SIMILAR TO '[0-9]+' THEN max(deces)::NUMERIC
ELSE 0
END AS mort,
CASE
WHEN max(deces_ehpad) SIMILAR TO '[0-9]+' THEN max(deces_ehpad)::NUMERIC
ELSE 0
END AS mort_ehpad,
CASE
WHEN max(reanimation) SIMILAR TO '[0-9]+' THEN max(reanimation)::NUMERIC
ELSE 0
END AS reanimation,
CASE
WHEN max(hospitalises) SIMILAR TO '[0-9]+' THEN max(hospitalises)::NUMERIC
ELSE 0
END AS hospitalises
FROM
covid19.import_data_fr
WHERE
granularite = $a$ || quote_literal(var_emprise.granularite) || $a$
AND
maille_code = $a$ || quote_literal(var_emprise.maille_code) || $a$
GROUP BY
"date"
ORDER BY
"date"$a$
LOOP
--RAISE NOTICE USING MESSAGE = ' Date : ' || var_date."date";
-- On insert les données récupérées.
EXECUTE
$a$INSERT INTO covid19."data_raw" (
niveau,
nom_niveau_1,
nom_niveau_2,
nom_niveau_3,
"date",
cas,
gueris,
mort,
mort_ehpad,
reanimation,
hospitalises,
cas_en_cours
)
VALUES (
$a$ || quote_literal(var_emprise.niveau) || $a$,
'France',
$a$ ||
CASE
WHEN var_emprise.niveau = 'niveau_2' THEN quote_literal(var_emprise.maille_nom)
ELSE quote_literal('France')
END
|| $a$,
$a$ ||
CASE
WHEN var_emprise.niveau = 'niveau_3' THEN quote_literal(var_emprise.maille_nom)
WHEN var_emprise.niveau = 'niveau_2' THEN quote_literal(var_emprise.maille_nom)
ELSE quote_literal('France')
END
|| $a$,
$a$ || quote_literal(var_date."date") || $a$,
$a$ || var_date.cas || $a$,
$a$ || var_date.gueris || $a$,
$a$ || var_date.mort || $a$,
$a$ || var_date.mort_ehpad || $a$,
$a$ || var_date.reanimation || $a$,
$a$ || var_date.hospitalises || $a$,
$a$ || (CASE WHEN var_date.cas = 0 THEN var_date.hospitalises ELSE var_date.cas END - var_date.gueris - var_date.mort) || $a$
)$a$
;
END LOOP;
END LOOP;
RAISE NOTICE USING MESSAGE = 'Fin traitement données FR';
END
$corps$
;
Les données mondiales
Ensuite, on traite les données mondiales.
Petites difficultés supplémentaires :
- Les données proviennent de trois tables différentes.
- Les données doivent être pivotées (une colonne par date dans la source alors que nous voulons une ligne par date).
DO
$corps$
DECLARE
var_col_date record;
var_requete_select TEXT := '';
var_emprise record;
var_date record;
BEGIN
RAISE NOTICE USING MESSAGE = 'Début traitement données mondiales';
-- Suppression des données mondiales
DELETE FROM covid19."data_raw"
WHERE
nom_niveau_1 <> 'France'
;
-- Pour chaque colonnes de date de chaque table mondiale, on créé une requête de sélection pour l'insertion.
-- Ceci nous évite d'avoir à passer en revue chaque valeur de chaque colonne de chaque ligne : une requête s'occupe de traiter la colonne entière.
-- La requête créée est ensuite liée à celle de la colonne suivante avec "UNION ALL" le but étant d'obtenir une seule requête d'insertion pour toutes les données.
FOR var_col_date IN
SELECT
-- Sélection de la date à partir du nom de la colonne
(substring(COALESCE(tc.column_name, tg.column_name, tm.column_name), '_[0-9]{1,2}_([0-9]{1,2})_[0-9]{1,2}') || '-' || substring(COALESCE(tc.column_name, tg.column_name, tm.column_name), '_([0-9]{1,2})_[0-9]{1,2}_[0-9]{1,2}') || '-' || substring(COALESCE(tc.column_name, tg.column_name, tm.column_name), '_[0-9]{1,2}_[0-9]{1,2}_([0-9]{1,2})'))::date AS "date",
-- Sélection des noms de colonne
tc.column_name AS col_name_tc,
tg.column_name AS col_name_tg,
tm.column_name AS col_name_tm
FROM
information_schema.columns tc
FULL JOIN
information_schema.columns tg
ON tg.table_schema = 'covid19'
AND tg.table_name = 'import_data_monde_gueris'
AND tg.column_name SIMILAR TO '_[0-9]{1,2}_[0-9]{1,2}_[0-9]{1,2}'
AND tg.column_name = tc.column_name
FULL JOIN
information_schema.columns tm
ON tm.table_schema = 'covid19'
AND tm.table_name = 'import_data_monde_mort'
AND tm.column_name SIMILAR TO '_[0-9]{1,2}_[0-9]{1,2}_[0-9]{1,2}'
AND tm.column_name = tc.column_name
WHERE
tc.table_schema = 'covid19'
AND
tc.table_name = 'import_data_monde_cas'
AND
tc.column_name SIMILAR TO '_[0-9]{1,2}_[0-9]{1,2}_[0-9]{1,2}'
ORDER BY
"date"
LOOP
-- Pour chaque date, on va construire une requête.
-- On utilise l'opérateur "UNION ALL" entre chaque requêtes sauf si on est au premier tour
IF var_requete_select <> '' THEN
var_requete_select = var_requete_select || $a$ UNION ALL $a$;
END IF;
-- Création de la requête de sélection des données
var_requete_select = var_requete_select || $a$
SELECT
CASE
WHEN COALESCE(tc.province_state, tg.province_state, tm.province_state) IS NULL OR COALESCE(tc.province_state, tg.province_state, tm.province_state) = '' THEN 'niveau_1'
ELSE 'niveau_2'
END AS niveau,
COALESCE(tc.country_region, tg.country_region, tm.country_region) AS nom_niveau_1,
CASE
WHEN COALESCE(tc.province_state, tg.province_state, tm.province_state) IS NULL OR COALESCE(tc.province_state, tg.province_state, tm.province_state) = '' THEN COALESCE(tc.country_region, tg.country_region, tm.country_region)
ELSE COALESCE(tc.province_state, tg.province_state, tm.province_state)
END AS nom_niveau_2,
CASE
WHEN COALESCE(tc.province_state, tg.province_state, tm.province_state) IS NULL OR COALESCE(tc.province_state, tg.province_state, tm.province_state) = '' THEN COALESCE(tc.country_region, tg.country_region, tm.country_region)
ELSE COALESCE(tc.province_state, tg.province_state, tm.province_state)
END AS nom_niveau_3,
$a$ || quote_literal(var_col_date."date") || $a$::date AS "date",
tc.$a$ || CASE WHEN var_col_date.col_name_tc IS NOT NULL THEN quote_ident(var_col_date.col_name_tc) ELSE quote_literal('0') END || $a$::numeric AS cas,
tg.$a$ || CASE WHEN var_col_date.col_name_tg IS NOT NULL THEN quote_ident(var_col_date.col_name_tg) ELSE quote_literal('0') END || $a$::numeric AS gueris,
tm.$a$ || CASE WHEN var_col_date.col_name_tm IS NOT NULL THEN quote_ident(var_col_date.col_name_tm) ELSE quote_literal('0') END || $a$::numeric AS mort,
tc.$a$ || CASE WHEN var_col_date.col_name_tc IS NOT NULL THEN quote_ident(var_col_date.col_name_tc) ELSE quote_literal('0') END || $a$::numeric - tg.$a$ || CASE WHEN var_col_date.col_name_tg IS NOT NULL THEN quote_ident(var_col_date.col_name_tg) ELSE quote_literal('0') END || $a$::numeric - tm.$a$ || CASE WHEN var_col_date.col_name_tm IS NOT NULL THEN quote_ident(var_col_date.col_name_tm) ELSE quote_literal('0') END || $a$::numeric AS cas_en_cours
FROM
covid19.import_data_monde_cas tc
FULL JOIN
covid19.import_data_monde_gueris tg
ON
tg.country_region = tc.country_region
AND
tg.province_state = tc.province_state
FULL JOIN
covid19.import_data_monde_mort tm
ON
tm.country_region = tc.country_region
AND
tm.province_state = tc.province_state
$a$;
END LOOP;
-- Ajout d'une condition pour extraire les données de France métropolitaine déjà récupérées dans le script précédent
var_requete_select = $a$
SELECT
*
FROM (
$a$ || var_requete_select || $a$
) AS t
WHERE
(
nom_niveau_1 <> 'France'
OR
(
nom_niveau_1 = 'France'
AND
(
nom_niveau_2 IS NOT NULL
AND
nom_niveau_2 <> ''
)
)
)
$a$;
--RAISE NOTICE USING MESSAGE = var_requete_select;
-- Insertion des données
EXECUTE
$a$INSERT INTO covid19.data_raw (
niveau,
nom_niveau_1,
nom_niveau_2,
nom_niveau_3,
"date",
cas,
gueris,
mort,
cas_en_cours
)
$a$ || var_requete_select
;
RAISE NOTICE USING MESSAGE = 'Fin traitement données mondiales';
END
$corps$
;
Pour certains pays, les données n’existent que pour leurs régions et pas pour le pays lui-même. Nous devons donc traiter ces données pour avoir l’information au niveau du pays.
-- Post traitement pour le Canada DO $corps$ DECLARE var_date record; BEGIN RAISE NOTICE USING MESSAGE = 'Début post-traitement données Canada'; -- Pour chaque date des données du Canada FOR var_date IN SELECT "date" FROM covid19.data_raw WHERE nom_niveau_1 = 'Canada' AND "date" IS NOT NULL GROUP BY 1 LOOP -- On met à la jour les valeurs de la date correspondante pour l'emprise "Canada" avec la somme des valeurs de ses régions (uniquement pour les nombres de cas, de cas en cours et de morts. EXECUTE $a$UPDATE covid19.data_raw t1 SET cas = ( SELECT sum(t2.cas) FROM covid19.data_raw t2 WHERE t2.nom_niveau_1 = 'Canada' AND t2.nom_niveau_2 <> 'Canada' AND t2.nom_niveau_3 <> 'Canada' AND t2."date" = $a$ || quote_literal(var_date."date") || $a$::date GROUP BY t2.nom_niveau_1) WHERE t1.nom_niveau_1 = 'Canada' AND t1.nom_niveau_2 = 'Canada' AND t1.nom_niveau_3 = 'Canada' AND "date" = $a$ || quote_literal(var_date."date") || $a$::date $a$ ; EXECUTE $a$UPDATE covid19.data_raw t1 SET mort = ( SELECT sum(t2.mort) FROM covid19.data_raw t2 WHERE t2.nom_niveau_1 = 'Canada' AND t2.nom_niveau_2 <> 'Canada' AND t2.nom_niveau_3 <> 'Canada' AND t2."date" = $a$ || quote_literal(var_date."date") || $a$::date GROUP BY t2.nom_niveau_1) WHERE t1.nom_niveau_1 = 'Canada' AND t1.nom_niveau_2 = 'Canada' AND t1.nom_niveau_3 = 'Canada' AND "date" = $a$ || quote_literal(var_date."date") || $a$::date $a$ ; EXECUTE $a$UPDATE covid19.data_raw t1 SET cas_en_cours = cas::NUMERIC - gueris::NUMERIC - mort::numeric WHERE t1.nom_niveau_1 = 'Canada' AND t1.nom_niveau_2 = 'Canada' AND t1.nom_niveau_3 = 'Canada' AND "date" = $a$ || quote_literal(var_date."date") || $a$::date $a$ ; END LOOP; RAISE NOTICE USING MESSAGE = 'Fin post-traitement données Canada'; END $corps$ ;
-- Post traitement pour la Chine et l'australie DO $corps$ DECLARE var_emprise text; var_date record; BEGIN RAISE NOTICE USING MESSAGE = 'Début post-traitement données Australie+Chine'; -- Pour chaque emprise à nettoyer FOREACH var_emprise IN ARRAY ARRAY['Australia','China'] LOOP -- On supprime les données de l'emprise en question (emprise de niveau 1 : pays, on ne supprime pas les données régionales) EXECUTE $a$DELETE FROM covid19.data_raw WHERE nom_niveau_1 = $a$ || quote_literal(var_emprise) || $a$ AND nom_niveau_2 = $a$ || quote_literal(var_emprise) || $a$ AND nom_niveau_3 = $a$ || quote_literal(var_emprise) ; -- Pour chaque date de relevée des données FOR var_date IN EXECUTE $a$SELECT "date" FROM covid19.data_raw WHERE nom_niveau_1 = $a$ || quote_literal(var_emprise) || $a$ AND "date" IS NOT NULL GROUP BY 1$a$ LOOP -- On met à la jour les valeurs de la date correspondante pour l'emprise en question avec la somme des valeurs de ses régions EXECUTE $a$INSERT INTO covid19.data_raw ( niveau, nom_niveau_1, nom_niveau_2, nom_niveau_3, "date", cas, mort, gueris, cas_en_cours ) SELECT 'niveau_1', $a$ || quote_literal(var_emprise) || $a$, $a$ || quote_literal(var_emprise) || $a$, $a$ || quote_literal(var_emprise) || $a$, $a$ || quote_literal(var_date."date") || $a$::date, sum(t2.cas), sum(t2.mort), sum(t2.gueris), sum(t2.cas) - sum(t2.mort) - sum(t2.gueris) FROM covid19.data_raw t2 WHERE t2.nom_niveau_1 = $a$ || quote_literal(var_emprise) || $a$ AND t2.nom_niveau_2 <> $a$ || quote_literal(var_emprise) || $a$ AND t2.nom_niveau_3 <> $a$ || quote_literal(var_emprise) || $a$ AND t2."date" = $a$ || quote_literal(var_date."date") || $a$::date GROUP BY t2.nom_niveau_1$a$ ; END LOOP; END LOOP; RAISE NOTICE USING MESSAGE = 'Fin traitement données Australie+Chine'; END $corps$ ;
Post-traitement final
Nous avons maintenant une donnée pour chaque date pour chaque emprise.
Ces données doivent être consolidées :
- Comme il s’agit de valeurs cumulées, les nouvelles valeurs sont obligatoirement égales ou supérieures aux valeurs précédentes.
- Les valeurs « 0 » ne font pas propres sur des graphiques : on utilise la valeur du jour précédent (choix personnel tout à fait discutable).
- Certaines emprises possèdent des trous dans la donnée : il faut compléter avec la valeur de la veille (ou de l’avant-veille, ou encore d’avant) (choix personnel tout à fait discutable).
- Il peut être intéressant de calculer la variation entre la veille et le jour même. Bien que pouvant être calculé dynamiquement, ce pré-calcul accélèrera notre tableau de bord.
DO
$corps$
DECLARE
var_emprise record;
var_date record;
var_method TEXT;
var_data_n_1 record;
var_data_n record;
var_new_cas int;
var_new_gueris int;
var_new_mort int;
var_new_mort_ehpad int;
var_new_reanimation int;
var_new_hospitalises int;
var_new_cas_en_cours int;
var_variation_cas int;
var_variation_gueris int;
var_variation_mort int;
var_variation_mort_ehpad int;
var_variation_reanimation int;
var_variation_hospitalises int;
var_variation_cas_en_cours int;
BEGIN
RAISE NOTICE USING MESSAGE = 'Début Nettoyage valeur';
-- Pour chaque emprise
FOR var_emprise IN
SELECT
niveau,
nom_niveau_1,
nom_niveau_2,
nom_niveau_3
FROM
covid19.data_raw
GROUP BY
niveau,
nom_niveau_1,
nom_niveau_2,
nom_niveau_3
ORDER BY
niveau,
nom_niveau_1,
nom_niveau_2,
nom_niveau_3
LOOP
--RAISE NOTICE USING MESSAGE = var_emprise.niveau || ' : ' || var_emprise.nom_niveau_1 || ' - ' || var_emprise.nom_niveau_2 || ' - ' || var_emprise.nom_niveau_3;
-- Pour chaque jour depuis le premier janvier
FOR var_date IN
SELECT
"date"::date
FROM
generate_series(
'2020-01-01'::date,
current_date-1,
'1 day') as t1("date")
LOOP
--RAISE NOTICE USING MESSAGE = ' Date : ' || var_date."date";
-- On regarde si la valeur existe déjà pour définir la méthode d'insertion de la données (INSERT ou UPDATE)
EXECUTE
$a$SELECT
'UPDATE'
FROM
covid19.data_clean
WHERE
niveau = $a$ || quote_literal(var_emprise.niveau) || $a$
AND
nom_niveau_1 = $a$ || quote_literal(var_emprise.nom_niveau_1) || $a$
AND
nom_niveau_2 = $a$ || quote_literal(var_emprise.nom_niveau_2) || $a$
AND
nom_niveau_3 = $a$ || quote_literal(var_emprise.nom_niveau_3) || $a$
AND
"date" = $a$ || quote_literal(var_date."date")
INTO var_method
;
var_method = COALESCE(var_method,'INSERT');
-- Si la date est le 1er janvier, on définit les valeurs à 0
IF var_date."date" = '2020-01-01' THEN
-- Selon la méthode, on insert ou on met à jour
IF var_method = 'INSERT' THEN
EXECUTE
$a$INSERT INTO covid19.data_clean (
niveau,
nom_niveau_1,
nom_niveau_2,
nom_niveau_3,
"date",
cas,
gueris,
mort,
mort_ehpad,
reanimation,
hospitalises,
cas_en_cours,
cas_variation,
gueris_variation,
mort_variation,
mort_ehpad_variation,
reanimation_variation,
hospitalises_variation,
cas_en_cours_variation
)
VALUES (
$a$ || quote_literal(var_emprise.niveau) || $a$,
$a$ || quote_literal(var_emprise.nom_niveau_1) || $a$,
$a$ || quote_literal(var_emprise.nom_niveau_2) || $a$,
$a$ || quote_literal(var_emprise.nom_niveau_3) || $a$,
$a$ || quote_literal(var_date."date") || $a$,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
)$a$
;
ELSE
EXECUTE
$a$UPDATE covid19.data_clean
SET
cas = 0,
gueris = 0,
mort = 0,
mort_ehpad = 0,
reanimation = 0,
hospitalises = 0,
cas_en_cours = 0,
cas_variation = 0,
gueris_variation = 0,
mort_variation = 0,
mort_ehpad_variation = 0,
reanimation_variation = 0,
hospitalises_variation = 0,
cas_en_cours_variation = 0
WHERE
niveau = $a$ || quote_literal(var_emprise.niveau) || $a$
AND
nom_niveau_1 = $a$ || quote_literal(var_emprise.nom_niveau_1) || $a$
AND
nom_niveau_2 = $a$ || quote_literal(var_emprise.nom_niveau_2) || $a$
AND
nom_niveau_3 = $a$ || quote_literal(var_emprise.nom_niveau_3) || $a$
AND
"date" = $a$ || quote_literal(var_date."date")
;
END IF;
-- Si on ne calcul par pour le 1er janvier
ELSE
-- On récupère la données de la veille
EXECUTE
$a$SELECT
cas,
gueris,
mort,
mort_ehpad,
reanimation,
hospitalises,
cas_en_cours
FROM
covid19.data_clean
WHERE
niveau = $a$ || quote_literal(var_emprise.niveau) || $a$
AND
nom_niveau_1 = $a$ || quote_literal(var_emprise.nom_niveau_1) || $a$
AND
nom_niveau_2 = $a$ || quote_literal(var_emprise.nom_niveau_2) || $a$
AND
nom_niveau_3 = $a$ || quote_literal(var_emprise.nom_niveau_3) || $a$
AND
"date" = $a$ || quote_literal(var_date."date"::date-1)
INTO var_data_n_1
;
-- On récupère la données source
EXECUTE
$a$SELECT
cas,
gueris,
mort,
mort_ehpad,
reanimation,
hospitalises,
cas_en_cours
FROM
covid19.data_raw
WHERE
niveau = $a$ || quote_literal(var_emprise.niveau) || $a$
AND
nom_niveau_1 = $a$ || quote_literal(var_emprise.nom_niveau_1) || $a$
AND
nom_niveau_2 = $a$ || quote_literal(var_emprise.nom_niveau_2) || $a$
AND
nom_niveau_3 = $a$ || quote_literal(var_emprise.nom_niveau_3) || $a$
AND
"date" = $a$ || quote_literal(var_date."date")
INTO var_data_n
;
-- Si la données source existe, on l'utilise, sinon on utilise la donnée de la veille.
IF var_data_n.cas IS NOT NULL AND var_data_n.cas > var_data_n_1.cas THEN
var_new_cas = var_data_n.cas;
ELSE
var_new_cas = var_data_n_1.cas;
END IF;
IF var_data_n.gueris IS NOT NULL AND var_data_n.gueris > var_data_n_1.gueris THEN
var_new_gueris = var_data_n.gueris;
ELSE
var_new_gueris = var_data_n_1.gueris;
END IF;
IF var_data_n.mort IS NOT NULL AND var_data_n.mort > var_data_n_1.mort THEN
var_new_mort = var_data_n.mort;
ELSE
var_new_mort = var_data_n_1.mort;
END IF;
IF var_data_n.mort_ehpad IS NOT NULL AND var_data_n.mort_ehpad > var_data_n_1.mort_ehpad THEN
var_new_mort_ehpad = var_data_n.mort_ehpad;
ELSE
var_new_mort_ehpad = var_data_n_1.mort_ehpad;
END IF;
IF var_data_n.reanimation IS NOT NULL AND var_data_n.reanimation > var_data_n_1.reanimation THEN
var_new_reanimation = var_data_n.reanimation;
ELSE
var_new_reanimation = var_data_n_1.reanimation;
END IF;
IF var_data_n.hospitalises IS NOT NULL AND var_data_n.hospitalises > var_data_n_1.hospitalises THEN
var_new_hospitalises = var_data_n.hospitalises;
ELSE
var_new_hospitalises = var_data_n_1.hospitalises;
END IF;
-- On calcul le nombre de cas en cours
-- Pour la France, le nombre de cas total n'est plus calculé pour les régions et les départements. On utilise alors le nombre d'hospitalisés (ce qui peut donner des incohérences).
var_new_cas_en_cours = CASE WHEN var_new_cas = 0 THEN var_new_hospitalises ELSE var_new_cas END - var_new_gueris - var_new_mort;
-- On calcul les variations des indicateurs
var_variation_cas = var_new_cas - var_data_n_1.cas ;
var_variation_gueris = var_new_gueris - var_data_n_1.gueris ;
var_variation_mort = var_new_mort - var_data_n_1.mort ;
var_variation_mort_ehpad = var_new_mort_ehpad - var_data_n_1.mort_ehpad ;
var_variation_reanimation = var_new_reanimation - var_data_n_1.reanimation ;
var_variation_hospitalises = var_new_hospitalises - var_data_n_1.hospitalises ;
var_variation_cas_en_cours = var_new_cas_en_cours - var_data_n_1.cas_en_cours ;
-- Selon la méthode, on insert ou on met à jour
IF var_method = 'INSERT' THEN
EXECUTE
$a$INSERT INTO covid19.data_clean (
niveau,
nom_niveau_1,
nom_niveau_2,
nom_niveau_3,
"date",
cas,
gueris,
mort,
mort_ehpad,
reanimation,
hospitalises,
cas_en_cours,
cas_variation,
gueris_variation,
mort_variation,
mort_ehpad_variation,
reanimation_variation,
hospitalises_variation,
cas_en_cours_variation
)
VALUES (
$a$ || quote_literal(var_emprise.niveau) || $a$,
$a$ || quote_literal(var_emprise.nom_niveau_1) || $a$,
$a$ || quote_literal(var_emprise.nom_niveau_2) || $a$,
$a$ || quote_literal(var_emprise.nom_niveau_3) || $a$,
$a$ || quote_literal(var_date."date") || $a$,
$a$ || var_new_cas || $a$,
$a$ || var_new_gueris || $a$,
$a$ || var_new_mort || $a$,
$a$ || var_new_mort_ehpad || $a$,
$a$ || var_new_reanimation || $a$,
$a$ || var_new_hospitalises || $a$,
$a$ || var_new_cas_en_cours || $a$,
$a$ || var_variation_cas || $a$,
$a$ || var_variation_gueris || $a$,
$a$ || var_variation_mort || $a$,
$a$ || var_variation_mort_ehpad || $a$,
$a$ || var_variation_reanimation || $a$,
$a$ || var_variation_hospitalises || $a$,
$a$ || var_variation_cas_en_cours || $a$
)$a$
;
ELSE
EXECUTE
$a$UPDATE covid19.data_clean
SET
cas = $a$ || var_new_cas || $a$,
gueris = $a$ || var_new_gueris || $a$,
mort = $a$ || var_new_mort || $a$,
mort_ehpad = $a$ || var_new_mort_ehpad || $a$,
reanimation = $a$ || var_new_reanimation || $a$,
hospitalises = $a$ || var_new_hospitalises || $a$,
cas_en_cours = $a$ || var_new_cas_en_cours || $a$,
cas_variation = $a$ || var_variation_cas || $a$,
gueris_variation = $a$ || var_variation_gueris || $a$,
mort_variation = $a$ || var_variation_mort || $a$,
mort_ehpad_variation = $a$ || var_variation_mort_ehpad || $a$,
reanimation_variation = $a$ || var_variation_reanimation || $a$,
hospitalises_variation = $a$ || var_variation_hospitalises || $a$,
cas_en_cours_variation = $a$ || var_variation_cas_en_cours || $a$
WHERE
niveau = $a$ || quote_literal(var_emprise.niveau) || $a$
AND
nom_niveau_1 = $a$ || quote_literal(var_emprise.nom_niveau_1) || $a$
AND
nom_niveau_2 = $a$ || quote_literal(var_emprise.nom_niveau_2) || $a$
AND
nom_niveau_3 = $a$ || quote_literal(var_emprise.nom_niveau_3) || $a$
AND
"date" = $a$ || quote_literal(var_date."date")
;
END IF;
END IF;
END LOOP;
END LOOP;
RAISE NOTICE USING MESSAGE = 'Fin Nettoyage valeur';
END
$corps$
;
Shell script
Enfin, on ajoute une petite ligne dans notre script shell pour lancer le code SQL que nous venons de voir :
# Post-traitement des données
PGPASSWORD=${var_bdd_pwd} psql -h ${var_bdd_host} -p ${var_bdd_port} -U ${var_bdd_user} -d ${var_bdd_name} -f "${var_script_dir}/traitement.sql"
Automatisation
Comme les données doivent être mises à jour chaque nuit, on créé une tâche CRON pour lancer le script shell toutes les nuits. Les sources étant mises à jour entre minuit et 2h du matin, on lance la tâche à 5h pour être sûr d’avoir les dernière données.
# On ouvre la table de cron crontab -e # On ajoute la ligne suivante qui permet de lancer le script shell tous les jours à 5h. 00 05 * * * /home/my_user/covid19/import_covid.sh
Et voila, les données sont intégrées, il ne reste plus qu’a monter votre tableau de bord avec divers outils.
Cet article vous a plu ?
N'hésitez pas à le partager, il interessera surement certains de vos contacts.
Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :
BashLinuxPostgreSQLSQL
