Contact: 514-605-7112 / info@lecfomasque.com

COVID-19: Construisez vos propres analyses sur cartes géographiques dynamiques (2 de 2)

Publié le : 23 mars 2020

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. Pour obtenir la technique complète et le fichier résolu, je vous invite à suivre notre formation Recettes magiques pour transformer les données, qui sera offerte très bientôt en ligne! Suivez notre blogue.

 

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.

Paramètres chemin fichier

 

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.

 

Structure PDF

 

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).

Extraction

 

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.

 

Colonne conditionnelle

 

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.

 

Colonne conditionnelle

 

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.

 

Extraction complète

 

On peut maintenant convertir le tout en fonction.

 

Préparer les données

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.

 

Texte avant le délimiteur

 

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))).

 

Listes par pays

 

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:

 

Requête fusionnée

 

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.

 

Requête fusionnée avec elle-même

 

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:

 

Colonne personnalisée avec conditions

 

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 carte dynamique

Coronavirus

 

Apprenez à faire votre propre Web Scraping

Notre formations Recette magiques pour transformer vos données vous enseigne notamment comment effectuer du Web Scraping. Si cette formation vous intéresse, sachez qu’elle est prévue en classe prochainement (à moins que le virus ait encore raison de nous à cette date) et qu’elle sera offerte en ligne très prochainement. Si vous souhaitez être avisé de la sortie en ligne de cette formation, laissez-nous savoir par courriel en nous écrivant à  info@lecfomasque.com. Dites-nous simplement que vous souhaitez être avisé lorsque la formation Recette magiques pour transformer vos données sera disponible en ligne.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire