Interroger l'ensemble de données COVID-19 à l'aide de BigQuery

Publié le dimanche 31 janvier 2021
#BigQuery #Google Cloud #COVID-19 #Ensemble de données publiques
This post thumbnail

Interroger l'ensemble de données COVID19 à l'aide de BigQuery

Cet article est une démonstration de l’utilisation de BigQuery et de l’ensemble de données publiques sur la COVID19.

Pour ce faire, je vais utiliser BigQuery pour obtenir les données de nouveaux cas de COVID19 au Québec et ceux dans la région du Bas-Saint-Laurent. Le Bas-Saint-Laurent est l’endroit où demeurent mes parents et j’aime bien pouvoir suivre l’évolution de la pandémie tous les jours. Ceux de la province de Québec seront utilisés pour comparer les résultats obtenus avec ceux publiés dans les journaux.

Dans ce premier article, je vais utiliser le langage SQL Standard de BigQuery pour extraire les données. Et en même temps calculer la moyenne mobile sur 7 jours, car il est un meilleur indicateur de l’évolution des cas.

Ensemble de données publiques disponibles

Google Cloud offre un grand nombre d’ensembles de données publiques disponibles aux utilisateurs de BigQuery. Il y a plus de 200 ensembles disponibles à ce jour dans divers domaines. Soit la météorologie, les sciences de la vie et de la santé, le transport, les cryptomonnaies et bien d’autres. Beaucoup d’autres organismes rendent leurs données disponibles sous forme d’ensemble de données publiques dans BigQuery, voir les références à la fin de cet article.

L’ensemble de données COVID-19 Open Data

L’ensemble de données COVID-19 est fourni et maintenu par Google. Il utilise les données provenant de divers pays qui rendent publique l’information. Il contient les données journalières sur divers paramètres relatifs à la pandémie tels que les données relatives au nombre de cas, la météo, les données démographiques et autres. Plus d’une quarantaine de champs d’information peuvent être disponibles en fonction de la source de données.

Habituellement, les données proviennent des autorités de santé publique des pays. L’ensemble est mis à jour quotidiennement par des moyens automatisés et manuels.

L’ensemble de données est disponible pour traitement dans la région US et la région EU de Google Cloud.

Les données sont disponibles aussi en format CSV et JSON pour récupération et utilisation par d’autres logiciels d’analyse de données. Elles sont alors disponibles sur Github, le lien est fourni à la fin de l’article.

Cet ensemble contient 1.88 GB de données par pays, région et sous-région.

À la date de publication, il est possible d’utiliser 1TB de traitement BigQuery gratuitement par mois. Aussi pour ceux qui débutent avec BigQuery, il est possible d’explorer le potentiel de BigQuery à l’aide du “Bac à sable BigQuery” sans avoir à fournir une carte de crédit.

Premières requêtes : les cas au Québec du 1er octobre 2020 au 30 novembre 2020

En premier, nous allons effectuer une requête simplifiée pour avoir un aperçu des champs disponibles pour les données de la province de Québec.

SELECT
    *
    FROM`bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE country_code = 'CA'
AND subregion1_code = 'QC'
AND date > '2020-11-01'
AND date < '2020-11-30'
LIMIT 500

Voici un extrait des données obtenues :

[
     {
    "date": "2020-11-29",
    "location_key": "CA_QC",
    "country_code": "CA",
    "country_name": "Canada",
    "subregion1_code": "QC",
    "subregion1_name": "Quebec",
    "subregion2_code": null,
    "subregion2_name": null,
    "iso_3166_1_alpha_2": "CA",
    "iso_3166_1_alpha_3": "CAN",
    "aggregation_level": "1",
    "new_confirmed": "1395",
    "new_deceased": "12",
    "new_recovered": "1108",
    "new_tested": "9360",
    "cumulative_confirmed": "141038",
    "cumulative_deceased": "7033",
    "cumulative_recovered": "122014",
    "cumulative_tested": "2177421",
    "new_hospitalized_patients": null,
    "new_intensive_care_patients": null,
    "new_ventilator_patients": null,
    "cumulative_hospitalized_patients": null,
    "cumulative_intensive_care_patients": null,
    "cumulative_ventilator_patients": null,
    "current_hospitalized_patients": null,
    "current_intensive_care_patients": null,
    "current_ventilator_patients": null,
    "mobility_transit_stations": "-45",
    "mobility_retail_and_recreation": "-21",
    "mobility_grocery_and_pharmacy": "-5",
    "mobility_parks": "23",
    "mobility_residential": "7",
    "mobility_workplaces": "-12",
    "wikidata_id": "Q176",
    "datacommons_id": null,
    "openstreetmap_id": "61549",
    "latitude": "52.0",
    "longitude": "-72.0",
    "location_geometry": "POINT(-72 52)",
    "average_temperature_celsius": "-5.305556",
    "minimum_temperature_celsius": "-9.486111",
    "maximum_temperature_celsius": "-2.847222",
    "rainfall_mm": "1.143",
    "snowfall_mm": null
  },
  {
    "date": "2020-11-02",
    "location_key": "CA_QC_2401",
    "country_code": "CA",
    "country_name": "Canada",
    "subregion1_code": "QC",
    "subregion1_name": "Quebec",
    "subregion2_code": "2401",
    "subregion2_name": "Bas-Saint-Laurent",
    "iso_3166_1_alpha_2": "CA",
    "iso_3166_1_alpha_3": "CAN",
    "aggregation_level": "2",
    "new_confirmed": "1",
    "new_deceased": "0",
    "new_recovered": null,
    "new_tested": null,
    "cumulative_confirmed": "481",
    "cumulative_deceased": "7",
    "cumulative_recovered": null,
    "cumulative_tested": null,
    "new_hospitalized_patients": null,
    "new_intensive_care_patients": null,
    "new_ventilator_patients": null,
    "cumulative_hospitalized_patients": null,
    "cumulative_intensive_care_patients": null,
    "cumulative_ventilator_patients": null,
    "current_hospitalized_patients": null,
    "current_intensive_care_patients": null,
    "current_ventilator_patients": null,
    "mobility_transit_stations": null,
    "mobility_retail_and_recreation": null,
    "mobility_grocery_and_pharmacy": null,
    "mobility_parks": null,
    "mobility_residential": null,
    "mobility_workplaces": null,
    "wikidata_id": null,
    "datacommons_id": null,
    "openstreetmap_id": null,
    "latitude": null,
    "longitude": null,
    "location_geometry": null,
    "average_temperature_celsius": null,
    "minimum_temperature_celsius": null,
    "maximum_temperature_celsius": null,
    "rainfall_mm": null,
    "snowfall_mm": null
  }
]

On s'aperçoit ici que pour le Québec et le Bas-Saint-Laurent, tous les champs ne sont pas remplis. Et aussi que certains champs tels que les données de localisation et conditions météorologiques ne sont pas disponibles pour le Bas-Saint-Laurent.

Cependant, pour les besoins de cet article, j'ai seulement besoin de pouvoir extraire les nouveaux cas confirmés pour le Québec et le Bas-Saint-Laurent.

Cette requête ne doit pas être utilisée fréquemment, car elle utilise la clause SELECT * qui enfreint les règles de bonnes pratiques de BigQuery. Comme BigQuery à l’interne enregistre chaque colonne d’un ensemble des données dans un fichier Capacitor séparé, il est recommandé de spécifiquement identifier les champs requis dans la requête (Voir références). Cela réduit le nombre d’octets qui seront traités et par la même occasion peut réduire les frais d’utilisation.

La nouvelle requête contenant seulement les champs requis sera la suivante :

SELECT  
    date, country_code, country_name, 
    subregion1_code, subregion1_name, 
    subregion2_name, 
    cumulative_confirmed, cumulative_recovered, cumulative_deceased, cumulative_tested, 
    new_confirmed,
    FROM `bigquery-public-data.covid19_open_data.covid19_open_data` 
    WHERE country_code = 'CA'
    AND subregion1_code = 'QC'
    AND date > '2020-10-23'
    LIMIT 1000

Calculer une moyenne mobile

Comme on désire à la fin avoir une idée de l’évolution du nombre de cas, une mesure plus juste utilisée par beaucoup de sites d’information est la moyenne mobile sur 7 jours. Cet indicateur donne une meilleure idée de la tendance, car elle supprime les fluctuations qui peuvent par exemple être plus élevées ou moins élevées en raison d’un problème d’entrée de données, de capacité de laboratoire… Elle met l’accent sur la tendance à plus long terme.

Pour comparer avec les données obtenues de l’Institut de la Santé Publique du Québec, nous allons utiliser la moyenne mobile glissante sur 7 jours. La moyenne mobile sur 7 jours correspond au nombre moyen d’évènements (cas ou décès) du jour sélectionné, des 3 jours précédents et des 3 jours suivants.

Il est possible de la calculer directement avec la clause AVG.

SELECT  
    date, country_code, country_name, 
    subregion1_code, subregion1_name, 
    subregion2_name, 
    cumulative_confirmed, cumulative_recovered, cumulative_deceased, cumulative_tested, 
    new_confirmed,
     AVG(new_confirmed) 
     OVER (PARTITION BY subregion2_name ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) 
     AS avg_new_confirmed_7days
    FROM `bigquery-public-data.covid19_open_data.covid19_open_data` 
    WHERE country_code = 'CA'
    AND subregion1_code = 'QC'
    AND subregion2_name IS NULL
    AND date > '2020-10-23'
    LIMIT 1000

Cette requête utilise 556 MiB de données alors que la même requête prendrait 1.9 GiB si elle utilisait un SELECT * au lieu de seulement récupérer les colonnes requises pour les traitements que nous allons exécuter par la suite.

datecountry_namesubregion1_namesubregion2_namenew_confirmedavg_new_confirmed_7days
2020-12-22CanadaQuebec21831867.2857142857142
2020-12-23CanadaQuebec22472217.8571428571427
2020-12-24CanadaQuebec23492238.5714285714284
2020-12-25CanadaQuebec02262.4285714285716
2020-12-26CanadaQuebec44922290.714285714286
2020-12-27CanadaQuebec22912328.4285714285716
2020-12-28CanadaQuebec22752395.5714285714284
2020-12-29CanadaQuebec23812796.714285714286
2020-12-30CanadaQuebec25112438.714285714286
2020-12-31CanadaQuebec28192521.285714285714
2021-01-01CanadaQuebec28082560.0
2021-01-02CanadaQuebec19862578.1428571428573
2021-01-03CanadaQuebec28692596.714285714286
2021-01-04CanadaQuebec25462553.8571428571427
2021-01-05CanadaQuebec25082522.4285714285716
2021-01-06CanadaQuebec26412685.4285714285716
2021-01-07CanadaQuebec25192645.285714285714
2021-01-08CanadaQuebec25882548.5714285714284
2021-01-09CanadaQuebec31272466.5714285714284
2021-01-10CanadaQuebec25882385.1428571428573
2021-01-11CanadaQuebec18692329.8571428571427
2021-01-12CanadaQuebec19342234.1428571428573
2021-01-13CanadaQuebec20712105.285714285714
2021-01-14CanadaQuebec21322013.2857142857142
2021-01-15CanadaQuebec19181951.142857142857
2021-01-16CanadaQuebec22251872.857142857143
2021-01-17CanadaQuebec19441791.5714285714287
2021-01-18CanadaQuebec14341719.0
2021-01-19CanadaQuebec13861678.0
2021-01-20CanadaQuebec15021600.857142857143
2021-01-21CanadaQuebec16241531.2857142857142
2021-01-22CanadaQuebec16311498.2857142857142
2021-01-23CanadaQuebec16851466.857142857143
2021-01-24CanadaQuebec14571442.0
2021-01-25CanadaQuebec12031405.4285714285713
2021-01-26CanadaQuebec11661357.4285714285713
2021-01-27CanadaQuebec13281312.0
2021-01-28CanadaQuebec13681278.5714285714287

Voici le graphique que présente le nombre de nouveau et la moyenne mobile pour l’ensemble de la province du Québec à partir des données obtenues :

Nombre de nouveau cas selon l'ensemble de données publiques via BigQuery pour la province du Québec >< Nombre de nouveau cas selon l'ensemble de données publiques via BigQuery pour la province du Québec

Comparaison avec les données de l’Institut Nationale de la Santé Publique du Québec (INSPQ)

Nombre de nouveau cas selon l'INSPQ Nombre de nouveau cas selon l'INSPQ

Si l’on compare le graphique provenant de l’INSPQ et celui obtenu par notre requête SQL. On voit que les données sont assez proches sans être exactes. Les données de l’INSPQ sont corrigées lorsque les rapports de journées précédentes ne sont pas exacts. Par exemple, durant la journée du 25 décembre aucun nouveau cas n’a été inscrit alors que le 26 ses nouveaux cas ont été ajoutés à la journée. L’ensemble de données publiques lui n’a pas été corrigé.

Données du Bas-Saint-Laurent

Comme, je m’intéresse au nombre de nouveau cas dans le Bas-Saint-Laurent, voici la nouvelle requête pour extraire et calculer la moyenne mobile :

SELECT  
    date, country_code, country_name, 
    subregion1_code, subregion1_name, 
    subregion2_name, 
    cumulative_confirmed, cumulative_recovered, cumulative_deceased, cumulative_tested, 
    new_confirmed,
     AVG(new_confirmed) 
     OVER (PARTITION BY subregion2_name ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) 
     AS avg_new_confirmed_7days
    FROM `bigquery-public-data.covid19_open_data.covid19_open_data` 
    WHERE country_code = 'CA'
    AND subregion1_code = 'QC'
    AND subregion2_name = 'Bas-Saint-Laurent'
    AND date > '2020-10-23'
    LIMIT 1000

Voici le graphique que présente le nombre de nouveau et la moyenne mobile pour le Bas-Saint-Laurent à partir des données obtenues :

Nombre de nouveau cas au Bas-Saint-Laurent selon l'ensemble de données publiques via BigQuery ><Nombre de nouveau cas au Bas-Saint-Laurent selon l'ensemble de données publiques via BigQuery

On remarque que les nouveaux cas ont baissé en janvier par rapport à la période des fêtes.

Conclusion

Cet article démontre comment BigQuery peut être un outil utile pour interroger des ensembles de données publiques. Et qu’il est possible de le faire à l’aide d’outils infonuagiques qui ne demandent pas d’installations sur un poste de travail. En complément, Google Cloud offre la possibilité de créer facilement des tableaux de bord connectés à BigQuery à l’aide de DataStudio. Dans un prochain article, je vais présenter une solution pour rendre disponibles les données de l’ensemble COVID-19 par une API à l’aide de BigQuery et de Cloud Run.

Références