Comment créer une carte interactive des résultats électoraux avec
Excel et CartoDB

carte finale

Ce tutoriel est tiré d’une présentation que j’ai faite pour un Meetup de Hacks/Hackers Montréal. Devenez membre si vous habitez dans la région!

Je vous montre ici comment utiliser les données de l’élection générale canadienne de 2011 pour réaliser une carte interactive avec CartoDB.

CartoDB est un outil formidable qui, en plus de permettre la création de très belles cartes, dispose aussi d’une librairie JavaScript CartoDB.js et d’une API. Mais ces deux aspects feront l’objet d’un autre article!

Pour l’instant, lançons-nous dans les données de l’élection et dans les formules Excel!

PS1: Je me limite ici aux circonscriptions, mais vous pouvez faire une carte encore plus précise en utilisant directement les données des bureaux de scrutin et les fichiers géographiques qui y sont associés.

PS2: Si vous êtes coincé, n’hésitez pas à m’écrire!

 

#I Télécharger et préparer les données

Pour commencer, téléchargeons les données des résultats électoraux sur le site web d’Élections Canada.

Élections Canada

Allez chercher le tableau, au bas de la page, qui contient les résultats de chaque candidat, dans chacune des circonscriptions. Il s’agit du tableau 12.

table 12 bon fr

Le fichier téléchargé est dans le format CSV (Comma-Separated Values), qui est très commun dans le monde des données et qui donne un charabia incompréhensible à l’ouverture, dans Excel.

Excel csv

En fait, les informations sont séparées par des virgules et il faut demander à Excel de transformer les virgules en colonnes.

Sélectionnez la première colonne au complet. Dans l’onglet Data, cliquez sur Text to Columns.

texte to columns

Puis sur Delimited, ensuite sur Next, et finalement cochez Comma avant de cliquer sur Finish.

text to column comma

Voilà qui est mieux! Excel a réorganisé vos données.

column comma finish

Commençons par supprimer les colonnes inutiles, soit la A, E, F, I et J. Gardez uniquement le nom de la circonscription, son numéro d’identification, le nom du candidat et son parti, ainsi que le nombre de votes recueilli et le pourcentage.

Profitez-en pour renommer vos colonnes.

colonnes nouveaux noms

Parfait! Nous pouvons commencer à travailler le tout. Tout d’abord, trions les données pour qu’elles soient dans le bon ordre.

Cliquez sur Custom Sort.

custom sort

Choisissez de trier tout d’abord par numéro de circonscription du plus petit au plus grand, puis cliquez sur le + et choisissez de trier ensuite par nombre de votes du plus petit au plus grand également. Cliquez sur OK.

custom sort ok

Vos données sont désormais dans le bon ordre. Vous allez comprendre pourquoi plus tard! Pour l’instant, faites-moi confiance. 🙂

donnes triees

Passons maintenant au nettoyage.

On peut voir que dans la colonne Pourcentage, si c’est inférieur à 1, il manque le 0. De plus, pour une meilleure présentation sur la carte, il serait pratique d’ajouter le symbole %.

Pour ce faire, nous allons utiliser la formule suivante:

formule pourcentage

Cette formule consiste en trois fonctions différentes:

  • La fonction IF permet de poser une condition. Si la condition est remplie, Excel réalise une opération de votre choix. Et si la condition n’est pas remplie, vous pouvez demander à Excel de faire quelque chose d’autre.
  • La fonction LEFT permet de sélectionner un nombre X de caractères dans une cellule, à partir de la gauche.
  • La fonction CONCATENATE permet d’assembler du texte et des cellules les uns à la suite des autres.

La combinaison de ces différentes fonctions fait en sorte que notre formule:

  1. Vérifie si le premier caractère de la cellule dans la colonne E est un point.
  2. Si c’est un point, elle ajoute un zéro avant le contenu de la cellule et un signe de pourcentage après. Elle met le résultat dans la colonne F, où elle se trouve.
  3. Si ce n’est pas un point, elle ajoute seulement un signe de pourcentage après et elle met le résultat dans la colonne F.

Une fois que vous avez entré la formule, appliquez la à l’ensemble de la colonne. Pour ce faire, mettez votre curseur sur le coin inférieur droit de la cellule. Quand une petite croix noire apparaît, double-cliquez et Excel la copiera pour toutes les cellules plus bas.

pourcentage toute la colonne

Sélectionnez l’ensemble des valeurs dans la colonne F (à partir de F2). Copiez les.

Placez-vous dans la cellule E2.

Dans l’onglet Home, cliquez le bouton Paste Special et choisissez Values.

paste special values 2

Supprimez maintenant la colonne où se trouvait votre formule, la colonne F. Nous n’en avons plus besoin!

apres pourcentage

Attaquons-nous maintenant au nom du candidat et au parti politique. Étrangement, ces deux informations se trouvent dans la même colonne. Nous allons devoir les séparer.

Pour commencer, on peut constater que le nom du parti en français est isolé du reste par une barre oblique. On peut donc facilement le mettre dans une nouvelle colonne.

Commençons par déplacer la colonne pour mieux la manipuler. Sélectionnez l’ensemble de la colonne C. Copiez et collez la dans la colonne F. Supprimez ensuite la colonne C.

apres copier coller nom

Sélectionnez maintenant l’ensemble de la colonne E (dans laquelle se trouve désormais les informations sur les candidats et leur parti politique).

Dans l’onglet Data, cliquez encore une fois sur Text to Columns, puis sur Delimited et Next.

Maintenant, cochez Other et mettez une barre oblique dans la boite. Cliquez sur Finish.

barre oblique twet column

Et voilà! Le nom en français est isolé du reste! Appelez la colonne F Parti.

apres barre oblique

Toutefois, un nom de parti contenait plusieurs barres obliques. Aux lignes 638, 699, 786, 834, 1020, 1046 et 1580, vous pouvez constater que le Animal Alliance Environnement Voters occupe les cellules de la colonne G et H.

Supprimez-les manuellement et mettez le nom complet dans la colonne F.

Animal alliance environnement

Dans la colonne E, nous avons toujours le nom des partis en anglais, dont nous devons nous débarrasser. De plus, pour certains candidats,  deux étoiles sont présentes.

Commençons par supprimer ces dernières. Pour ce faire, appuyez sur CMD + F pour lancer une recherche.

Dans le champ de recherche, tapez ~*, cliquez sur Replace et laissez le champ de replacement vide.

replace stars

Cliquez maintenant sur Replace All ce qui permettra de les supprimer.

starts replaced

Il nous reste désormais le nom du parti en anglais dans la colonne E.

La méthode la plus simple pour supprimer les différents partis serait de sélectionner la colonne au complet, puis de faire des recherches de chacun des noms et de les remplacer par rien, exactement comme nous avons fait pour les deux étoiles.

Mais ce serait long et fastidieux. Il y a tout de même plus de 20 partis différents!

Alors je vous ai préparé une formule qui va s’occuper de tout supprimer pour vous. La voici:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2;”No affiliation”;””);”Bloc Québécois”;””);”Communist”;””);”Conservative”;””);”Environment Voters”;””);”FPNP”;””);”Independent”;””);”Liberal”;””);”Libertarian”;””);”Marxist-Leninist”;””);”NDP-New Democratic Party”;””);”CAP”;””);”PC Party”;””);”Pirate Party”;””);”United Party”;””);”Green Party”;””);”CHP Canada”;””);”Radical Marijuana”;””);”Rhinoceros”;””);”WBP”;””);”Animal Alliance”;””))

Cette formule utilise la fonction SUBSTITUTE pour supprimer les noms des partis et la fonction TRIM pour retirer les espaces parasites qui pourraient se retrouver à la fin des noms des candidats.

Insérez une nouvelle colonne entre E et F. Pour ce faire, faites un clic droit sur l’entête de la colonne G et choisissez Insert.

nouvelle colonne

Copiez-collez maintenant la formule ci-dessus dans la cellule F2.

formule sub f2

Placez votre curseur sur le côté inférieur droit de la cellule. Double-cliquez pour appliquez la formule à l’ensemble de la colonne. Et voilà! Vous avez les noms des candidats!

Copiez le contenu de la colonne F (à partir de F2). Placez vous dans la cellule E2. Sur l’onglet Home, cliquez sur le bouton Paste Special et sélectionnez Values (comme nous l’avons fait pour les pourcentages).

Supprimez maintenant la colonne F.

Et voilà! Toutes nos données sont propres maintenant!

excel tout propre!

 

#II Transformer les données en code HTML

Nous avons maintenant nos données triées et formatées comme il se doit. Toutefois, dans le fichier de notre carte finale, puisqu’il y avait 308 circonscriptions en 2011, nous aurons seulement 308 lignes d’informations.

Or nous avons 1 587 lignes dans notre fichier Excel, parce qu’il y avait 1 587 candidats lors de l’élection de 2011!

Nous devons donc regrouper encore une fois nos données. L’ensemble des résultats des candidats, pour chaque circonscription, doit se retrouver sur une seule ligne.

Pour que l’affichage soit optimal sur notre carte, nous allons en plus formater les données en HTML.

C’est parti!

Copiez la formule ci-dessous et collez la dans la cellule G2.
=CONCATENATE(“<b>”;E2;”</b><br>”;F2;”<br>”;C2;” votes<br>”;D2;”<br><br>”)

formule G2

La balise <b> et </b> vont entourer le prénom et le nom du candidat, ce qui va permettre de le mettre en gras.

Les balises <br> indiquent des retours à la ligne, ce qui va permettre de placer les informations les unes au-dessus des autres. Et on termine avec deux balises de ce type là pour qu’une ligne vide sépare les différents candidats dans chaque circonscription.

On en profite aussi pour ajouter votes après le nombre de votes.

apres formule g2

Placez votre curseur en bas à droite de la cellule jusqu’à ce qu’une petite croix noire s’affiche, puis double-cliquez pour appliquer la formule à toute la colonne.

formule g2 toute colonne

On avance! Maintenant, il faudrait qu’on assemble le code HTML des candidats par circonscription.

Cette fois-ci, en plus de la fonction CONCATENATE, nous allons utiliser la fonction IF.

Copiez la formule ci-dessous et collez la dans la cellule H2:
=IF(A1=A2;CONCATENATE(G2;H1);G2)

formule h2 bonne

Appliquez la formule pour l’ensemble de la colonne H.

Cette formule ajoute le code HTML du candidat de la ligne sur laquelle elle se trouve, mais en plus, si le nom de la circonscription sur la ligne précédente est identique (dans la colonne A), le code HTML de la cellule précédente (dans la colonne H) est ajouté aussi.

Par conséquent, sur la dernière ligne concernant chaque circonscription, on obtient un code HTML avec les informations pour l’ensemble des candidats!

concatenate h2 formule

Par exemple, la ligne 6 est la dernière ligne pour la circonscription d’Avalon.

Si on copie le code de la cellule H6 et on le colle dans le visualiseur HTML de Code Beautify, on peut voir la liste des candidats avec leur résultat. Notre formule fonctionne!

Par ailleurs, vous remarquerez que le nom du parti pour la dernière ligne est celui du candidat ayant obtenu le plus de vote, grâce à notre tri de données que nous avons fait au tout début. C’est parfait. On pourra utiliser cette colonne pour notre carte!

code viewer html bon fr

Toutefois, nous avons toujours 1 587 lignes pour 1 587 candidats et il nous faut seulement 308 lignes pour 308 circonscriptions.

Utilisons une autre formule pour distinguer les dernières lignes pour chaque circonscription, puisque c’est là que se retrouve l’ensemble du code HTML avec les résultats des différents candidats.

Dans la cellule I2, copiez-collez la formule ci-dessous et appliquez là l’ensemble de la colonne I:
=IF(A2<>A3;”DERNIERE”;””)

Cette formule ajoute le mot DERNIERE sur chacune des dernières lignes de chaque circonscription.

derniere 1

Nous pouvons maintenant filtrer les colonnes pour ne garder que celles où il est écrit DERNIERE!

Cliquez sur l’entonnoir pour afficher les filtres automatiques.

entonoir bon

Et maintenant cliquez sur le filtre dans la colonne I, pour choisir uniquement les cellules contenant DERNIERE.

derniere filtre bon

Tadam! Vous n’avez désormais que les dernières lignes d’affichées et il y en a… 308!

Nous approchons du but!

Maintenant, sélectionnons l’ensemble des cellules filtrées (CMD+ A), puis copions les (CMD + C).

Ouvrons un nouveau fichier Excel (CMD + N). Placez vous sur la première cellule et finalement collons uniquement les valeurs dans ce nouveau fichier.

paste value final fr

Renommez la colonne H HTML, puis supprimez les colonnes C, D, E, G et I (vous devez donc garder A, B, F, H).

Enregistrez votre fichier sous le nom Resultat_2011_CLEAN, dans un format Excel.

Et voilà! Vous avez votre fichier Excel avec, pour chaque circonscription, les résultats de chaque candidat en HTML!

final clean html

 

#V Créer la carte dans CartoDB

Maintenant que nous avons notre fichier, il nous reste à cartographier le tout!

Nous allons lier notre fichier Excel avec les fichiers géographiques des circonscriptions.

Pour commencer, téléchargez les fichiers géographiques des circonscriptions électorales de 2011.

Pour pouvoir les utiliser avec CartoDB, vous devez les compresser (format .zip).

Sélectionnez les fichiers en français (voir ci-dessous) et compressez les.

compresser

Renommez le nouveau fichier Circonscriptions_2011.zip.

Maintenant, allons sur CartoDB!

Si vous n’avez pas de compte, c’est le moment d’en créer un. CartoDB vous permet d’héberger jusqu’à 50 mo de données gratuitement, ce qui sera suffisant pour la carte que nous nous apprêtons à faire.

cartodb

Une fois inscrit et/ou identifié, rendez-vous sur votre Dashboard et cliquez sur New Dataset.

new dataset

Ensuite, cliquez sur SELECT A FILE (ou BROWSE) et ajoutez votre fichier Circonscriptions_2011.zip en cliquant sur CONNECT DATASET.

select file

Une fois le fichier envoyé, vous devriez voir quelque chose comme ci-dessous.

data view circonscription

Cliquez sur MAP VIEW et vous allez voir vos circonscriptions cartographiées!

circonscription carto

Maintenant que nous avons le fichier géographique sur CartoDB, amenons le fichier avec les résultats des élections.

Retournez sur le Dashboard, cliquez sur New dataset, puis sur SELECT A FILE (ou BROWSE) et envoyez votre fichier Resultat_2011_CLEAN, où se trouve tous les résultats avec les candidats, en cliquant sur CONNECT DATASET.

clean on cartodb

Il nous reste maintenant à connecter le fichier géographique avec le fichier contenant les résultats!

Nous allons utiliser le numéro d’identification des circonscriptions pour faire le lien entre les deux fichiers.

Pour ce faire, cliquer sur le petit symbole en bas à droite.

merge final carto

Cliquez sur COLUMN JOIN. Une nouvelle fenêtre s’ouvre.

Sur le côté gauche, pour votre fichier Resultats_2011_CLEAN, choisissez numero_de_circonscription.

Sur le côté droit, assurez-vous d’avoir circonscriptions_2011 de sélectionné, et choisissez numcef (tout en bas de la page).

Cliquez ensuite sur NEXT STEP.

Ces deux colonnes contiennent les numéros d’identification des circonscriptions, ce qui va permettre à CartoDB de faire le lien.

le join entre colonne

Sur la page suivante, CartoDB vous demande quelles colonnes vous souhaitez conserver.

Pour resultats_2011_CLEAN, choisissez les toutes, sauf geometry.

Pour circonscriptions_2011, choisissez uniquement geometry, puisque c’est ce fichier qui contient les informations géographiques des circonscriptions.

Cliquez sur MERGE DATASETS pour lancer l’opération!

merge dataset bon

Et voilà! Toutes les données sont réunies et prêtes à être cartographiées!

clean merge reussi

Cliquez sur MAP VIEW.  Ensuite sur WIZARDS (le petit pinceau sur la gauche). Choisissez Parti pour que la carte soit colorée en fonction des affiliations politiques des candidats vainqueurs.

category map

Modifiez les couleurs des catégories pour qu’elles correspondent aux couleurs officielles des partis.

carte premiere

Il vous reste à afficher les informations sur les candidats lorsque l’on clique sur une circonscription.

Cliquez sur l’onglet INFOWINDOW. Sélectionnez nom_de_circonscription et html. Mettez les dans l’ordre et modifiez les titres.

Remarquez à quel point notre code HTML fonctionne à la perfection!

carte infowindow

Maintenant, cliquez sur VISUALIZE, en haut à gauche, puis sur OK, CREATE MAP.

Vous pouvez maintenant cliquer sur PUBLISH (en haut à gauche encore une fois), ce qui vous donnera le code pour intégrer votre carte sur n’importe quel site web!

Et voilà! C’est terminé! Vous avez créé votre carte interactive des résultats électoraux de 2011!

Maintenant, vous pouvez faire la même chose pour l’élection de 2015! 🙂

carte finale

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *