Dans l’article COVID-19: Construisez vos propres analyses sur cartes géographiques dynamiques (1 de 2), nous avions regardé ensemble comment créer une requête, dans l’éditeur Power Query de Power BI Desktop, pour obtenir la liste de tous les rapports quotidiens du World Health Organization sur la COVID-19, sous forme de fichiers PDF en ligne. Dans le présent article, on verra ensemble les grandes étapes pour extraire l’information de ces fichiers PDF.
Création d’un paramètre
Dans un premier temps, il s’avère necessaire de créer un paramètre, qui servira à determiner les différents liens à utiliser pour récupérer les fichiers PDF en ligne. Si on veut développer un carte géographique dynamique, il faut que notre base de données comprenne les données de tous les pays, pour la durée désirée.
Création d’une fonction
Ce que l’on souhaite, c’est créer une fonction, qui pourra être appliquée à l’ensemble des fichiers PDF en ligne que nous avons obtenus au préalable (voir article précédent). Pour ce faire, il s’agit de procéder à une requête de transformation sur un des fichiers en question, de lui passer le paramètre défini ci-dessus et ensuite, de convertir cette requête en fonction, de façon à pouvoir l’appliquer à l’ensemble des autres fichiers. Ci-dessous, on voit comment Power Query interprète le contenu d’un fichier PDF, soit en identifiant des pages et des tables. Normalement, lorsque tous les fichiers PDF sont structurés exactement de la même façon, je n’hésite pas à recommander d’extraire les tables. Par contre, ici, force est de constater que les fichiers PDF sont tous un peu différents et que certaines tables ne sont pas sous format de tables. De plus, en explorant davantage les fichiers individuels, on peut remarquer un changement de structure important entre les fichiers qui précèdent le 2 mars 2020 et les autres fichiers. Pour cette raison, on travaillera exclusivement avec les fichiers du 2 mars et les suivants. Par contre, il serait aussi possible de faire deux exercices de transformation pour les fichiers avant le 2 mars et pour ceux après le 2 mars et ensuite fusionner le tout. Dans tous les cas, ici, on a donc décidé de travailler avec les pages, plutôt qu’avec les tables et c’est pourquoi on a apposé un filtre sur les pages de la colonne Kind.
Après avoir supprimé les colonnes inutiles, on peut développer les données de la colonne Data et obtenir quelque chose de semblable à ce qui suit (ça dépend du fichier que vous avez choisi d’extraire).
Après plusieurs essais et erreurs, on a trouvé un élément commun à tous les fichiers. La table qui présente les données par pays, n’est pas toujours la table 2, mais elle débute toujours par Western Pacific Region. On a donc inclus une colonne conditionnelle qui ramène la valeur de la colonne 1 lorsque celle-ci débute par Western, sinon, elle ramène la valeur null.
On peut donc apercevoir la ligne où débute réellement le tableau avec les valeurs par pays, identifiée par l’expression Western Pacific Region.
Comme la transformation précédente a aussi généré quelques erreurs, on remplace d’abord les erreurs par des valeurs null et ensuite, on peut aisément utiliser la fonctionnalité Remplir vers le bas et filtrer ensuite sur les lignes qui comprennent l’expression Western Pacific Region. Ceci nous permettra de récupérer les valeurs par pays. Il s’agira ensuite de renommer les colonnes, supprimer les erreurs, filtrer pour retirer les valeurs null, supprimer les colonnes inutiles et modifier les types de données afin d’obtenir ce qui suit.
On peut maintenant convertir le tout en fonction.
Vous devez analyser de grandes quantités de données et les présenter dans des rapports et tableaux de bord, avec des indicateurs de performance pertinents ? Développez vos compétences avec nos formations en Power BI. |
|---|
Préparer les données pour la construction de nos cartes géographiques dynamiques
On peut ensuite retourner à notre liste de liens URL qui pointent vers les fichiers PDF en ligne et ajouter une colonne qui viendra invoquer notre fonction. Toutefois, comme on est en présence de fichiers PDF qui ne sont pas très bien structurés, il faudra d’abord nettoyer les données obtenues. Tout d’abord, on voudra récupérer les dates des différents fichiers. Ces dates sont contenues dans les URL des fichiers en question. Il s’agira alors d’utiliser des fonctionnalités d’extraction (comme par exemple, extraire les 4 premiers caractères pour obtenir l’année) et ensuite, quand on aura les informations d’années, de mois et de jours dans des colonnes séparées, il suffira d’ajouter une colonne personnalisée avec la fonction #date pour reconstituer chacune des dates.
On peut ici apposer notre filtre pour ne conserver que les données après le 2 mars. Ensuite, il faudra nettoyer les données à l’aide de fonctionnalités comme par exemple, la fonctionnalité, Extraire avant le délimiteur. Un exemple du genre de transformation utile à l’aide de cette fonctionnalité serait de retirer les expressions qui sont suivies d’un ^. C’est le cas notamment de certaines expressions de la colonne Days since last reported case.
Ce type de transformation sera utile pour se départir aussi de:
- ( pour Country
- ^ pour Country et Transmission
- † pour Country
On pourra aussi s’assurer de supprimer les espsaces dans les expressions et appliquer une majuscule à chaque mot.
Dès lors, on observera quelques différences dans l’ortographe de certains noms de pays d’un fichier PDF à un autre. On pourra alors commencer un exercice soutenu de Remplacer les valeurs, comme par exemple:
- Table.ReplaceValue(#”Valeur remplacée”,”United States Of America#(lf)America”,”United States Of America”,Replacer.ReplaceText,{“Country”})
- Table.ReplaceValue(#”Valeur remplacée7″,”Curaçao”,”Curacao”,Replacer.ReplaceText,{“Country”})
- Table.ReplaceValue(#”Valeur remplacée8″,”Côte D’Ivoire”,”Cote D’Ivoire”,Replacer.ReplaceText,{“Country”})
Note: Remarquez l’expression #(lf) qui est fort utile pour convertir une expression brisée sur deux lignes.
Pour s’assurer de ne pas avoir de doublons, on peut maitenant fusionner les colonnes Country et Date et supprimer les doublons de cette colonne. De même, il sera necessaire de filtrer les lignes de la colonne Country pour retirer des items comme Subtotal par exemple, qui n’est pas un pays.
Appliquer la fonction à l’ensemble des fichiers
À cette étape, on constatera rapidement que certaines données, à certaines dates, sont manquantes pour certains pays. Ce faisant, comme notre objectif est de montrer l’évolution, jour après jour, si on s’arrête ici, on aura des journées qui montreront 0 cas pour certains pays alors qu’ils en cumulent en réalité plusieurs. Comment peut-on corriger une telle situation? Par l’emploi de listes qui créeront un champ unique pour chaque combinaison de Pays et de Date, afin de couvrir toutes les dates, pour tous les pays. Une fois qu’on aura toutes les combinaisons possibles, on s’assurera qu’aucune d’entre elle n’est nulle.
Pour créer la prochaine requête, on doit d’abord se lier à la précédente et en récupérer la colonne Country. Ensuite, on pourra ajouter une colonne personnalisée comme suit: Table.AddColumn(Source, “Personnalisé”, each List.Dates(#date(2020,3,2),Duration.Days(DateMax-#date(2020,3,2)),#duration(1,0,0,0))).
Une fois développées sur de nouvelles lignes, les listes auront permis de créer nos fameuses combinaisons. On pourra ajouter une colonne à partir de la fusion des colonnes Country et Date, de trier les données par pays, puis par date et ensuite les fusionner avec la requête précédente.
Attention: Pour éviter les messages qui demandent de refaire les requêtes, il est possible de modifier les options de confidentialité et demander d’ignorer les niveaux de confidentialité. On obtiendra alors la table suivante:
Il faudra ensuite développer le contenu des tables et ajouter une colonne Index qui débute à 0 et une colonne Index qui débute à 1. Ceci nous permettra de fusionner la table avec elle-même mais avec une ligne de décalage.
Ce truc est fort utile pour comparer des valeurs sur une ligne existante avec les valeurs de la ligne précédente. Ici, on va donc extraire la colonne Country et la renommer Prior Country (pays de la ligne précédente). Ensuite, on pourra s’arranger pour obtenir des null partout où un remplissage vers le bas nous permettra d’obtenir le total de cas ou le total de morts pour un pays où des données sont manquantes. Il s’agira d’écrire une fonction comme la suivante, dans une colonne personnalisée:
On pourra ensuite remplir la colonne vers le bas (d’où la nécessité d’utiliser des null et des 0). Le remplissage vers le bas s’effectue uniquement sur des lignes à valeur null. Il faudra aussi répéter le même scenario avec les morts et les modes de transmission. Il ne restera plus qu’à supprimer les colonnes inutiles et modifier les types de données.
Rapport Power BI et cartes géographiques dynamiques
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour obtenir la technique complète et apprendre comment effectuer du Web Scraping, je vous recommande fortement la nouvelle formation Recette magiques pour transformer vos données.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
