Prendre un thé
Tapoter des doights
Monsieur fantôme
Quelle heure est-il ?
BazinGa's Tips & tuto IT
BazinGa's - Tips & tuto IT

Automatiser la récupération de données – Cas du COVID 19

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 :

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

50%