Exploiter Python pour dynamiser les feuilles de calcul en data science

Excel, mon vieux compagnon

Je fais partie de ces personnes qui manipulent des données dans des feuilles de calcul juste parce que c’est possible. La semaine dernière, mon lead a envoyé une liste des employés qui n’avaient pas mis à jour leurs check-lists projet. Je l’ai aussitôt ouverte, créé des tableaux, calculé des statistiques par collaborateur, puis je lui ai envoyé un récapitulatif, ventilé par personne. Ça a dû le rendre un peu dingue, mais je n’y peux rien : j’adore l’analyse de données et j’adore Excel. J’utilise Excel depuis 2001 et, jusqu’à il y a quelques mois, c’était mon outil de prédilection pour organiser, analyser et visualiser les données. Il est excellent pour structurer l’information, effectuer des calculs et des analyses, et fait même office de petite base de données. Je l’ai utilisé pour tout : suivre mon budget, mes dépenses, mes entraînements, jusqu’à des rapports d’analyse des effectifs sur plusieurs pages dans l’armée. Dans ce contexte, Excel convenait globalement bien et c’était la seule option. Pour des raisons de sécurité, je n’avais pas accès à VBA, Python ni à aucune automatisation. Les jeux de données étaient aussi suffisamment petits pour qu’Excel les gère.

Il y a presque un an jour pour jour, j’ai rejoint l’Abilities Lab de Cerner comme ingénieur système en tests de performance logicielle. Pour faire simple : je teste les performances des logiciels. Nous testons les nouvelles versions avant leur déploiement chez les clients, et nous passons tout en revue : CPU et mémoire sous Windows et Unix, performance de la JVM, temps de réponse côté services back-end et interface utilisateur, performances des bases de données, impact sur nos logiciels existants, réseau, et à peu près tout ce à quoi vous pouvez penser. Aucun test ne se limite à une seule machine : la plupart du temps, c’est un mix d’environnements Windows et Unix (physiques et virtuels), de postes utilisateurs et de serveurs. Sur l’un des projets auxquels j’ai participé, il fallait collecter des données sur plus de 20 systèmes.

Selon la durée du projet, il peut y avoir jusqu’à 50 tests à examiner, analyser et restituer. Toutes ces données doivent être synthétisées dans un rapport final publié sur une page Jira interne pour l’équipe de développement. Nous avons bien des outils maison capables d’agréger une bonne partie des données, mais pas la totalité, et leurs rapports ne sont pas assez personnalisables pour mon type de tests. Mon outil favori pour rédiger et livrer ces rapports : Excel. Pourquoi pas ? J’ai un solide bagage Excel et je pensais pouvoir tout y faire.

Excel, nous avons un problème…

Je ne vais pas détailler le processus fastidieux consistant à transférer les données de nos outils internes vers Excel, mais même en étant très à l’aise avec Excel, cela me prenait des heures. Il fallait copier, nettoyer, trier, filtrer. Quelles que soient mes formules, je devais presque toujours les ajuster, puis adapter tableaux et graphiques selon le nombre de tests, de nœuds et les différents temps de réponse enregistrés. Ensuite venaient les remplacements pour corriger les noms de tests, la vérification des formats numériques et des surbrillances ligne par ligne, page par page, et un nettoyage général. J’ai tenté des modèles, des macros et des scripts VBA. Rien n’était assez flexible pour s’adapter au volume variable de tests, d’appareils et d’indicateurs de performance sans un gros ménage à chaque fois. J’ai fini par créer un modèle qui accélérait un peu les choses, mais il me fallait encore une demi-journée pour tout caler.

Je souris en repensant aux formules et scripts VBA absurdement complexes que ce modèle contenait. Les choses ont empiré quand nous avons mis à jour Java et que nos outils internes n’arrivaient plus à parser correctement les logs. Nous sommes passés à un outil externe, GC Easy, excellent pour convertir les logs en JSON chargeable dans Excel. On peut importer ces JSON via PowerQuery, mais c’est lent, pénible, et lorsqu’on tente de traiter plus de 50 fichiers JSON dépassant chacun le million de lignes, on finit par faire planter Excel régulièrement. Ajoutez à cela le besoin d’automatiser, et je n’ai pas trouvé de solution satisfaisante. Même quand j’arrivais à charger les données dans Excel, les visualisations (aires empilées représentant la Heap Size dans le temps) figeaient l’application et provoquaient d’autres crashs. Et même si je résolvais ça, les JSON n’étaient qu’un début.

Copier, nettoyer et analyser manuellement des données conduit inévitablement à des erreurs. Nous sommes humains : on rate des choses, on est distrait, on clique au mauvais endroit. Mon processus ne fonctionnait pas et ne fonctionnerait jamais à l’échelle, à moins de former toute l’équipe de manière poussée sur Excel. Par habitude militaire ou simple trait de caractère, je cherche toujours à améliorer les processus et gagner en efficacité. J’ai donc commencé à réfléchir à une solution de bout en bout plus fluide.

Notre outil interne, à l’époque, n’avait ni API ni fonction d’export fiable. Impossible d’en extraire les données autrement qu’en copiant-collant. J’ai donc visé la source : les fichiers journaux de chaque appareil testé. Cela impliquait de parcourir un répertoire de test et sa myriade de sous-dossiers, d’extraire des données issues de multiples formats, d’effectuer tous les calculs, puis d’écrire le tout dans Excel au format voulu, avec visualisations et mises en forme conditionnelles.

Les types de fichiers à parser : .txt, .csv, .json (structures variées), .xml (structures variées), .dat, .html, et certaines données à extraire d’une base Oracle. Il fallait gérer des arborescences variables, des volumes et structures de fichiers hétérogènes, des .xml sans balises de fermeture, et des tailles dépassant 10 Go. Le processus devait aussi être clair, aussi automatisé que possible, et capable de traiter des millions de lignes. Excel ne pouvait clairement pas répondre à tout cela, alors je me suis tourné vers des outils BI connus : Power BI et Tableau. Sans en faire un banc d’essai, disons simplement qu’ils ne répondaient pas à mes besoins.

Alors, quel outil fait tout ce qu’Excel sait faire, peut écrire des rapports dans Excel, et coche toutes les cases de mon cahier des charges ?

Faites connaissance avec mes nouveaux alliés : Python et pandas

Si vous connaissez un tant soit peu Excel, vous allez adorer pandas pour Python. pandas marie les atouts d’Excel à la puissance du langage Python. Les colonnes d’Excel deviennent des Series, les tableaux des DataFrames, et les formules complexes se transforment en fonctions Python. pandas sait tout faire comme Excel :

Lecture des données

Excel s’en sort plutôt bien avec les fichiers plats, et avec PowerQuery il peut interroger certaines bases et lire certains fichiers .xml et .json. Sur le papier, la documentation fait rêver ; en pratique, c’est une autre histoire. L’interface est lourde, l’automatisation quasi inexistante, et le nettoyage des données est à peine plus agréable qu’une visite chez le dentiste. pandas, au contraire, simplifie radicalement la lecture des données.

Cette bibliothèque Python de manipulation de données sait lire nativement une multitude de sources. Consultez la documentation pour la liste complète. Et si pandas ne lit pas un format en natif, il y a de fortes chances qu’un paquet Python s’en charge, ou qu’une poignée de lignes de code suffise. Pour approfondir, voyez la série de cours de DataCamp Importing Data in Python, entre autres.

J’ai pu utiliser pandas pour lire tous les types de fichiers cités plus haut, y compris les .xml sans balises de fermeture. Avec un peu de traitement du langage naturel, je récupère aussi toutes les métadonnées de test depuis les fichiers .txt bruts, chose impossible avec Excel car ils ne sont pas formatés pour les données. La version actuelle de mon script lit non seulement tous les formats requis, mais en quelques lignes supplémentaires, je récupère automatiquement les journaux .json de Garbage Collection via l’API de GC Easy : chose infaisable avec Excel. Par rapport à Excel ? Avec pandas, je parcours l’intégralité d’un répertoire de test et je récupère toutes les données dans de jolis DataFrames en quelques minutes. Même les logs de plus de 10 Go passent sans broncher. Cela ne vous paraît peut‑être pas énorme, mais bon courage pour ouvrir un tel fichier avec Excel.

Je commence même à délaisser les fichiers plats bruts au profit d’extractions depuis une base Oracle multi‑téraoctets. Difficile de comparer avec Excel : à part un essai rapide, j’ai évité les requêtes base de données dans Excel. De mémoire, je n’ai aucune envie de réitérer. En Python, en revanche, c’est un jeu d’enfant avec SQLAlchemy. La connexion est simple et les performances des requêtes enterrent Excel. Voyez Introduction to Relational Databases in Python pour une excellente initiation.

Nettoyage des données

Visualisations mises à part, je suis convaincu qu’il faut des tableaux propres, nets, bien formatés et étiquetés. pandas propose d’excellentes options pour nettoyer et présenter des tableaux de qualité. La page Styling du site officiel est mon guide préféré pour formater des DataFrames. En quelques lignes, vous fixez le nombre de décimales, formatez les nombres, ajoutez des mises en forme de lignes et conditionnelles, avec un processus reproductible à l’infini. Pouvoir automatiser certaines étapes de nettoyage, quelle que soit la taille, m’a fait gagner des heures.

Pour être juste, on peut parfaitement nettoyer des données dans Excel. Il dispose d’une large panoplie d’outils pour cela, et c’est exactement ce que je devais faire à chaque fois que j’arrivais (enfin) à charger les données. Mais c’était un processus largement manuel, bien trop riche en clics, et très propice aux erreurs et aux pertes de temps. Même avec une grande maîtrise d’Excel, les erreurs finissent par arriver. On peut accélérer avec des formules, des macros et des scripts VBA (j’y viens), mais on est loin de l’idéal.

Analyse des données

Je vais être direct : pour l’analyse, pandas surclasse Excel sans discussion possible. La comparaison n’est pas équitable. On peut écrire des formules et utiliser des fonctions natives dans Excel ; je l’ai fait des années, et ça fonctionne. J’ai même donné des cours d’analyse de données sous Excel, plaidé pour son usage, et suivi un cours de statistiques où Excel était l’outil imposé. Je ne dis pas qu’on ne peut pas analyser avec Excel. Mais face à pandas, c’est papier-crayon contre calculatrice : ce n’est pas la même catégorie.

En août dernier, Kaggle signalait que Python avait dépassé R en data science, et en décembre, Quartz qualifiait pandas de « l’outil le plus important en data science ». Inutile d’en dire beaucoup plus.

Macros et VBA

En 2012, alors que j’étais encore en service actif, je suis allé à un rendez-vous de routine chez le dentiste à Fort Bliss, El Paso (Texas). À peine assis, il m’annonce qu’il faut retirer mes dents de sagesse et, comme il avait du temps, il a fait l’extraction sur-le-champ sous simple anesthésie locale. Une expérience atroce qui m’a laissé souffrir plusieurs jours. Entre revivre ça et travailler avec les macros Excel ou Visual Basic, je crois que je préfère encore me faire arracher d’autres dents.

Beaucoup de personnes excellent en VBA et j’ai vu des choses bluffantes avec des scripts bien pensés ; chapeau bas. Mais je n’en suis pas fan et je ne le recommande à personne, surtout quand on peut utiliser Python à la place. Python est une alternative si populaire à VBA que Microsoft envisage d’intégrer Python comme langage de script officiel dans Excel.

Mais j’ai encore besoin d’Excel

Autant j’aimerais tout faire en Python, autant ce n’est pas réaliste dans ma situation. Pour l’instant, mes résultats doivent encore être livrés dans Excel. Heureusement, des bibliothèques comme OpenPyXl et XlsxWriter existent précisément pour cela. J’utilise XlsxWriter au quotidien pour transformer des DataFrames en rapports Excel très professionnels, sans formules, macros ni scripts VBA. Il gère même les visualisations.

À propos de visualisation…

Fini les graphiques ennuyeux

Je n’ai jamais été un passionné de dataviz. J’ai toujours préféré voir les données en lignes et colonnes, avec quelques surbrillances, et éventuellement un histogramme. Excel propose pas mal d’options, mais, en ce qui me concerne, je me limitais aux aires empilées, barres et courbes. Pour chacun, je sélectionnais les données, « Insertion d’un graphique », puis je choisissais le type. Ensuite, un peu de filtrage et un titre, et basta.

Pour être transparent, Excel offre énormément d’options de personnalisation, mais le processus est souvent ardu et la courbe d’apprentissage raide. Je n’aime toujours pas créer des graphiques dans Excel, mais avec Python, c’est une autre histoire.

Je suis désormais convaincu par la visualisation et j’y consacre beaucoup d’efforts pour améliorer l’analyse. En suivant des cours sur DataCamp et en découvrant Matplotlib et Bokeh, j’ai compris la vraie puissance et l’objectif de la dataviz. Avec Matplotlib, on crée facilement des visualisations depuis pandas ou en important pyplot. La palette de visualisations possibles avec pyplot est immense, et l’on peut personnaliser virtuellement tous les aspects d’un graphique. Le cours Introduction to Data Visualization With Python montre comment réaliser d’excellentes visualisations avec Matplotlib.

Aussi puissant que soit Matplotlib, j’avais besoin d’un peu plus d’interactivité. Je voulais pouvoir sélectionner, zoomer, survoler, trier et filtrer les données dans une même viz, et je n’avais clairement pas envie d’apprendre react.js ou D3. Apprendre Python est un plaisir qui continue, mais JavaScript n’est pas mon fort. Heureusement, Python vient encore à la rescousse avec Bokeh et Dash.

Bokeh, soutenu par Anaconda, s’appuie sur D3.js et Python pour produire des graphiques pleinement interactifs, exportables en HTML autonome, intégrables dans des pages web ou exécutables sur un serveur Bokeh. L’apprentissage est plus abrupt que pour Matplotlib, mais les résultats en valent la peine : jetez un œil à la galerie. Je suis loin d’être aussi compétent en Bokeh que je le souhaite, mais c’est l’une de mes bibliothèques favorites depuis la première leçon du cours Interactive Data Visualization With Bokeh. Si Bokeh ne convient pas, il y a Dash, proposé par l’équipe de Plotly.

Dash est un autre outil de visualisation libre et open source pour Python, basé sur React.js plutôt que D3. Je l’ai découvert récemment : il produit lui aussi d’excellentes visualisations interactives sans écrire une ligne de JavaScript. Difficile de trancher entre Dash et Bokeh, tous deux très capables. Mon seul bémol avec Dash, pour l’instant, c’est que je n’ai réussi à faire tourner les visualisations que sur un serveur Dash. Je n’ai pas pu les intégrer dans des documents autonomes, des pages HTML ou des notebooks Jupyter. Cela dit, le potentiel est réel, comme le montre leur galerie.

Gagnez du temps avec la planification et l’automatisation

À l’été 2014, l’organisation pour laquelle je travaillais avait un problème. La réserve de l’Armée est pilotée par une série d’indicateurs appelés « Readiness ». C’est le pourcentage de personnels d’une unité pouvant être mobilisés. Ce score se calcule à partir de données médicales, dentaires, éducatives, de condition physique, et bien d’autres. Je ne peux pas donner le chiffre, mais le nôtre était loin du niveau attendu.

Ma solution : un rapport Excel multi-pages avec plusieurs tableaux et graphiques, consolidant des informations issues de diverses bases RH. On y trouvait des agrégats au niveau de l’organisation, des détails par individu pour chaque indicateur, et des projections à trois mois selon les actions en cours. Ce fut un vrai succès : notre « Readiness » a nettement augmenté et s’est maintenu longtemps.

Mais à quel prix ! Le lundi matin, j’arrivais tôt pour extraire manuellement les informations des différentes sources, nettoyer les données et les copier dans le rapport de la semaine. Je passais ensuite au moins une heure à traquer les erreurs et à nettoyer encore. Puis je l’envoyais au staff administratif pour vérification et ajout des mises à jour non encore présentes dans les bases. En moyenne, il fallait trois jours pour diffuser ce rapport. Un coût en heures homme excessif et inutile.

Avec quelques scripts Python, j’aurais pu collecter automatiquement toutes les données, tout compiler dans des DataFrames pandas, générer le rapport Excel et l’envoyer par email au staff. Le mieux : ce processus aurait pu être planifié à 00 h 01 chaque lundi, économisant un temps précieux. Sous Linux, programmer l’exécution de scripts Python à des horaires donnés via crontab est d’une simplicité déconcertante. Dans mon poste actuel, avec la multiplicité des systèmes, crontab est essentiel et la planification une compétence clé. Besoin d’extraire des données chaque nuit à minuit et de les injecter dans un rapport ? Sur Linux, crontab est la voie royale.

Je suis fan de Linux, mais l’essentiel de mon travail se fait sous Windows, mon OS de prédilection. Là, plusieurs options existent pour planifier des tâches : Windows Task Scheduler, Schtask.exe ou PowerShell. Avec l’une ou l’autre, vous programmez vos scripts pendant les heures creuses, quand les ressources sont disponibles, ou juste après la mise à jour d’une source de données. Une option que j’aurais aimé avoir il y a quelques années.

Mon outil préféré : Jupyter Notebooks

Après autant d’années sur Excel, apprendre Python, c’était comme vivre avec une simple pince et découvrir un jour une boîte à outils complète, toute neuve. Chacun de ces outils a son utilité, mais s’il ne devait en rester qu’un pour moi, ce serait Jupyter Notebooks. Tout ce que j’ai écrit en Python l’a été dans un Notebook, soit pour un usage direct, soit pour en faire ensuite un script autonome. Jupyter est idéal pour écrire des fonctions, tester du code, mener des analyses exploratoires et même présenter un livrable final. On peut aussi l’utiliser pour rédiger des billets de blog (comme celui-ci) ou sur GitHub Pages. Parcourez les tutoriels sur DataCamp : beaucoup ont été créés de la même manière.

Pourquoi en parler dans une discussion sur Excel ?

Un Notebook Jupyter peut remplacer Excel comme livrable d’analyse. Vous y écrivez votre code, affichez des DataFrames pandas, des visualisations et vos conclusions, le tout sur une seule page, exportable en HTML en un clic. Un de mes objectifs personnels est d’abandonner complètement Excel et d’utiliser des Notebooks pour envoyer mes rapports. C’est encore plus pertinent depuis la sortie de JupyterLab. Avec cela et les widgets, vous transformez un Notebook en application interactive d’analyse de données.

En guise de conclusion

J’utilise encore Excel tous les jours : c’est un excellent outil, profondément ancré dans de nombreuses organisations, dont la mienne. Presque tout le monde le connaît et sait s’en servir pour consommer de l’information et faire des analyses simples. Ce que peu de personnes réalisent, c’est que réaliser la même analyse avec pandas et Python est non seulement plus efficace, mais aussi plus simple.

Mon objectif est de me passer totalement d’Excel d’ici la fin de l’année, et je pourrai y parvenir avec les outils mentionnés dans cet article. Vous ne deviendrez pas expert du jour au lendemain, mais c’est pareil avec Excel. J’espère que cette lecture vous donnera envie d’essayer ces nouveaux outils. Vous gagnerez en efficacité, en productivité, et vous accélérerez vos workflows.