Nettoyer ses données avec OpenRefine (Niveau 1)

NETTOYER SES DONNÉES AVEC OPENREFINE (NIVEAU 1) AURÉLIEN MOISAN NOVEMBRE 2025 3 1. INTRODUCTION 2. INSTALLATION ET DÉCOUVERTE DE L’INTERFACE 3. FILTRES, FACETTES ET TRI 4. NETTOYER LES DONNÉES : LES FONCTIONS BASIQUES 5. INTRODUCTION À L'ÉDITEUR DE FORMULES GREL Sommaire 4 1 INTRODUCTION 5 OpenRefine Qu’est-ce que c’est ? OpenRefine est un outil gratuit et open source qui permet de nettoyer, transformer, convertir, enrichir des données. Pour plus d’informations rendez-vous sur : https://openrefine.org / 6 OpenRefine permet de : Convertir un jeu de données Nettoyer un jeu de données Transformer un jeu de données Enrichir un jeu de données Exposer des données sur Wikidata Crédits icones : Pixel perfect; alfanz; Soni Sokell; Freepik 7 •Il permet de modifier des données en masse (grâce à des traitements appliqués par colonnes) •Il permet de définir un ensemble de données sur lequel appliquer un traitement grâce à des filtres et des facettes •Ses formules préenregistrées et ses extensions permettent d’effectuer des traitements simples sans maitriser de langage de programmation •Il enregistre l’ensemble des traitements réalisés, pour que vous puissiez les reproduire à l’identique sur un autre jeu de données •Sa grande communauté d’utilisateurs Il est parfois présenté dans comme « un Excel sous stéroïdes » mais … OpenRefine Ses atouts 8 … ce n’est pas un tableur OpenRefine ne permet pas : •de visualiser les données sous forme graphique •le travail collaboratif Il n’est pas optimisé pour : •saisir des données •réaliser des calculs 9 Historique de l’outil 2009 -2010 Développement de l’outil par la société MetaWeb sous le nom Freebase Gridworks Fin 2010 Rachat de l’outil par Google. Il est renommé GoogleRefine 2012 L’outil qui n’est plus développé par Google est renommé OpenRefine 10 Les prérequis pour nettoyer efficacement ses données dans OpenRefine Bien connaitre les fonctionnalités d’OpenRefine Bien connaitre ses données Un peu de logique 17 Interface L’espace de travail Facette / Filtre Dans cet onglet vous pourrez paramétrer et supprimer vos facettes/filtres. Défaire / Refaire C’est l’historique des traitements effectués. Vous pouvez naviguer dans cet historique pour annuler ou réappliquer des traitements. Vous pouvez également extraire les traitements effectués pour les appliquer à un autre jeu de données. A l’inverse vous pouvez appliquer des traitements réalisés sur un autre jeu (voir diapo) lignes / entrées Un affichage « lignes » numérote chaque ligne et considère les lignes indépendamment les unes des autres. Un affichage « entrée » se base sur la première colonne (l’identifiant unique) pour définir des « entrées » qui peuvent contenir plusieurs lignes (exemple ci-dessous). Navigation dans les entrées Vous avez la possibilité de paramétrer le nombre de résultats affichés et de naviguer dans les différentes pages. Notez qu’OpenRefine est un outil pour effectuer des traitements en masse sur la totalité de vos entrées, vous n’avez donc pas besoin de toutes les voir. Pour vérifier si vos traitements ont bien fonctionné, vous pouvez utiliser les facettes pour afficher la liste des valeurs d’un champ 18 Installation et interface A vous de jouer Installez le logiciel et découvrez les données Pour télécharger l’outil rendez-vous ici : https://openrefine.org/download Téléchargez l’outil au format .zip, ainsi vous n’aurez aucune installation à réaliser. Dézippez le fichier et lancez le fichier openrefine.exe •Lancez le logiciel •Sélectionnez « Français » comme langue pour l’interface •Créez un nouveau projet en chargeant le tableau jeu_test_ESR.xlsx • Combien le tableau a t’il de lignes ? • Consultez la dernière ligne du tableau, de quel établissement s’agit-il ? 19 2 FILTRES, FACETTES ET TRI 20 Les filtres Comment les utiliser ? •Cliquez sur la colonne sur laquelle vous souhaitez appliquer un filtre > Filtrer le texte • Votre filtre apparait dans l’onglet « Facette / Filtre », saisissez la valeur souhaitée, le filtre s’applique en temps réel •Pour supprimer votre filtre cliquez sur la croix Le filtre cherche la chaine de caractère exacte ! Faites attention notamment quand vous filtrez avec des chiffres 21 Les filtres Dans quel cas les utiliser ? •Définir une plage: OpenRefine applique des traitements en masse mais parfois on souhaite simplement traiter un sous-ensemble •Explorer les données : bien que ce ne soit pas la fonction première d’OpenRefine, un filtre peu permette de savoir si une valeur est présente dans un champ ou non Ici un filtre sur le champ « Code postal » qui permet d’afficher les codes postaux commençant par 75, 91, 92 ou 93 •Cochez « expression rationnelle » si vous utilisez des opérateurs booléens ou des REGEX •Pour utiliser l’opérateur booléens OR, il faut utiliser sa version informatique | (AltGr + 6) •^est la REGEX utilisée pour « commence par » •Filtrer des valeurs inconnues : Il est possible de construire un filtre à partir de REGEX (expressions régulières). Par exemple le filtre «^01» sur un champ numéro de téléphone permettra de filtrer tous les numéros commençant par l’indicateur « 01 ». Ce qui serait trop fastidieux à partir d’une facette (exemple ci contre) 22 Les filtres A vous de jouer Nettoyez les URL dans la colonne « Site Internet » •Filtrez la colonne (1) avec l’expression régulière (^http) et inversez le filtre • Qu’observez vous ? • Avant d’effectuer des filtres basés sur des « commence par » ou « fini par » il faut toujours supprimer les espaces superflus pour cela : •Supprimez votre filtre le fermant avec la x •Utilisez la transformation courante appropriée sur la colonne « Site internet » (Editer les cellules > Transformation courante) (2) •Filtrer à nouveau (1) votre colonne avec l’expression régulière (^http) et inversez le filtre •Pour cet ensemble transformez les valeurs (Editer les cellules > transformer) (3) •« value » représente le contenu des cellules sélectionnées •Nous voulons ajouter ‘https://’ devant le contenu des cellules sélectionnées •Nous allons donc utiliser la formule "https://"+value 1 2 3 23 Les facette Comment les utiliser ? •Cliquez sur la colonne sur laquelle vous souhaitez appliquer une facette > Facette > Sélectionnez le type de facette •Les facettes fonctionnent avec les types de valeur. Par exemple pour appliquer une facette chronologique il faut un champ de type « date » (pour modifier le type d’un champ voir diapo) • Une fois la facette appliquée, elle s’affiche dans l’onglet « Facette /Filtre ». Vous pouvez alors sélectionner la ou les valeur(s) à filtrer •Les facettes peuvent lister les valeurs de votre colonne (ex: facette textuelle à gauche) ou être booléennes (facette doublons à droite) 24 Les facettes Dans quel cas les utiliser ? •Définir une plage: OpenRefine on applique des traitements en masse, mais parfois on souhaite simplement traiter un sous-ensemble ; •Explorer les données : une facette permet d’afficher toutes les valeurs d’un champ. Ainsi, on peut facilement identifier si des valeurs ne sont pas normées (facette textuelle; exemple bas), s’il y a des doublons (facette doublons; exemple haut), des valeurs erronées.. •Modifier des valeurs : Vous pouvez éditer les facettes textuelles, pour corriger des coquilles ou fusionner des valeurs. Cela permet de modifier l’ensemble des enregistrements correspondants à la valeur d’un seul coup ; •Clusteriser : La fonction « Groupe » (« grouper » en français), permet d’identifier des valeurs similaires grâce à des algorithmes basés sur les chaines de caractères. Cela pourra être utilisé pour normaliser les valeurs d’un champ. (voir diapo) 25 Les facettes A vous de jouer •En croisant des facettes textuelles dans différentes colonnes, identifiez les écoles privées sans numéro Siret •Supprimez les lignes du jeu de données (colonne Toutes > éditez les lignes > supprimez les lignes correspondantes) •Normalisez les valeurs de la colonne « Commune » en utilisant une facette textuelle et la fonction « Groupe » de la facette •N’hésitez pas à jouer avec les paramétrages pour identifier de potentiels doublons et voir comment fonctionnent les différents algorithmes Supprimez les écoles privées sans numéro Siret renseigné Normalisez le nom des communes 26 Le tri Comment l’utiliser ? •Cliquez sur la colonne sur laquelle vous souhaitez appliquer un tri > Trier > Sélectionnez le type de tri •Les tris fonctionnent avec les types de valeur. Par exemple pour appliquer un tri « nombre » il faut un champ de type « nombre » (pour modifier le type d’un champ voir diapo) •Une fois le tri effectué, un bouton « Trier », apparait à côté du nombre de résultats par page. Il permet de modifier ou supprimer un tri • Si vous appliquez plusieurs tris, ils s’appliqueront dans l’ordre •Par défaut un tri est temporaire, vous pouvez le supprimer. Mais vous pouvez l’appliquer de manière permanente, cela entrainera une renumérotation de vos lignes et vous ne pourrez plus le supprimer 33 Editer les cellules A vous de jouer •A l’aide d’une facette identifiez les établissements qui n’ont pas renseigné leurs effectifs pour 2017-18. Etoilez ces enregistrements (Colonne « Toutes » > Editez les lignes > Etoilez les enregistrements) •Faites la même opération pour les effectifs de 2016-17. •Récupérez l’ensemble des enregistrements étoilés avec la facette par étoile (Colonne « Toutes » > Facette) et supprimez les lignes correspondantes (Colonne « Toutes » > Editez les lignes ) Identifiez et supprimez les établissements qui n’ont pas renseigné de données d’inscription pour les années 2016-2017 ou 2017-2018 34 Editer les cellules Les transformations courantes •Supprimer les espaces de début et de fin / rassembler les espaces consécutifs: Ces fonctions permettent de supprimer les espaces indésirables. Quand vous manipulez des données, que vous fusionnez des cellules, il n’est pas rare que des espaces indésirables s’invitent dans vos cellules. Dans l’idéal effectuez « rassembler » puis « supprimer » les espaces avant de commencer à manipuler les données. Faites-le également après avoir terminé de manipuler vos données. •En nombre / En date / En texte: Ces fonctions permettent de modifier le type des données. Elles sont notamment utiles si vous souhaitez utiliser des facettes ou des tris spécifiques à un type de données (ex : facette TimeLine). Attention la conversion ne fait pas tout, la valeur initiale devra déjà correspondre à un formalisme particulier (format de date, pas d’espace dans les nombres..). •En valeur nulle: Permet de supprimer les données concernées. Cela s’applique à la plage de données sélectionnées. Si aucun filtre ou facette n’est sélectionné l’ensemble des valeurs de la colonne est supprimé. Ces modification peuvent être appliquées à plusieurs colonnes si elles sont effectuées à partir de la colonne « Toutes » (voir diapo) 35 Editer les cellules Remplacer Cette fonctionnalité permet de remplacer une chaine de caractères par une autre. Notez qu’elle fonctionne avec n’importe quel caractère y compris les espaces. Vous avez la possibilité de remplacer des caractères par une valeur nulle, ce qui aura pour effet de supprimer le caractère ou la chaine de caractères sélectionnée. Vous pouvez élégamment utiliser des REGEX pour identifier des chaines de caractères à remplacer (exemple ci-contre) Ici un exemple avec une REGEX pour remplacer les 0 en début de ligne par +33 pour modifier les numéros de téléphone en ajoutant l’indicatif français +33 12 34 36 Editer les cellules A vous de jouer Dans la colonne « Site internet », transformez les http:// en https:// •Utilisez la fonction Remplacer (Editez les cellules > Remplacer) (1) pour remplacer http:// par https:// (2)1 2 37 Editer les cellules A vous de jouer •En utilisant la transformation courante et les facettes, normalisez la colonne en remplaçant les valeurs « nd » par une valeur nulle. (1-2) • Dans la même colonne, supprimez les espaces avec l’aide de la fonction « Remplacer » afin d’avoir des nombres sans espaces (3) •Puis transformez le format des cellules en nombre (4) Convertissez les valeurs de la colonne «Effectif d’étudiants 2010-11 » en nombre 1 2 3 4 38 Les modifications groupées sur l’ensemble des colonnes La colonne « Toutes » permet de travailler sur l’ensemble des colonnes •Modifier toutes les colonnes : faire des transformations courantes (voir diapo) sur plusieurs colonnes •Editer les lignes : •Marquer / Etoiler : permet d’ajouter / de supprimer des étoiles ou des drapeaux pour la sélection •Supprimer les lignes correspondantes : permet de supprimer la sélection •Facettes : •Par étoile / par marque : permet d’afficher les entrées marquées (voir ci-dessus et diapo) •Par valeur vide : permet d’afficher les lignes entièrement vides •Valeurs / Entrées vides/non vides par colonne : permet d’afficher une facette avec chaque nom de colonne, qui affichera les cellules / entrées vides pour chaque colonne. •Editer les colonnes : •Retrier / Supprimer : permet de réordonner ou supprimer des colonnes •Recopier / vider les valeurs dans les cellules consécutives (voir diapo) 39 Editer les cellules / Transposer Les cellules multivaluées •Joindre les cellules multivaluées : Pour une même entrée, lorsqu’un champ a plusieurs valeurs sur plusieurs lignes, cette fonction permet de regrouper les valeurs avec un séparateur sur une seule ligne. •Diviser les cellules multivaluées : Sur la base d’un séparateur, vous créez autant de lignes que de valeurs présentes dans votre cellule initiale. •Transposer les cellules de plusieurs colonnes en ligne : permet de regrouper des cellules de plusieurs colonnes dans une seule colonne avec des cellules multivaluées. •Transposer les cellules en colonne séparée : Pour une même entrée, lorsqu’un champ a plusieurs valeurs sur plusieurs lignes, cette fonction permet de transposer les valeurs dans des colonnes. Attention vous devrez définir en amont le nombre de colonne, il faut que chaque entrée ait le même nombre de valeurs, ou connaitre l’entrée qui a le nombre de valeur le plus élevé. •Convertir en liste des colonne de clé/valeur : Permet de créer des colonnes sur la base d’une liste de valeurs d’un champ, et d’alimenter ces colonnes avec la valeur d’un autre champ 40 Editer les cellules Grouper et éditer (clusteriser) Grouper et éditer permet d’identifier des valeurs similaires qui ne seraient pas orthographiées de la même manière. Cette fonctionnalité est très utile pour normaliser les valeurs d’un champ. Il existe différents algorithmes qui permettent d’identifier des doublons potentiels. Cette fonctionnalité est aussi accessible via les facettes (voir diapo) 41 Editer les colonnes Renommer, supprimer, déplacer des colonnes •Renommer cette colonne : permet de renommer la colonne concernée. Notez que contrairement à un tableur, vous ne pouvez pas nommer deux colonnes de la même manière. Si vous avez prévu d’utiliser des formules, utilisez un nommage simple et explicite. •Supprimer cette colonne •Déplacer la colonne… : permet modifier la position d’une colonne. Notez qu’il est plus simple d’utiliser la colonne « Toutes » pour travailler sur la réorganisation et la suppression des colonnes (voir diapo) •Ajouter un colonne ? OpenRefine ne permet pas d’ajouter de nouvelles colonnes vierges. Vous pouvez le faire de manière détournée en utilisant la fonctionnalité « Ajouter une colonne en fonction de cette colonne » sur une colonne lambda et en remplaçant « value » par des doubles guillemets. 42 Editer les colonnes Joindre et diviser des colonnes •Diviser en plusieurs colonnes : Vous pouvez diviser votre colonne sur la base d’un séparateur ou d’une longueur. Notez que contrairement à un tableur classique : •Vous pouvez utiliser un séparateur de plusieurs caractères, ou basé sur une expression régulière. •La division créer de nouvelles colonnes, elle ne remplace pas les valeurs dans des colonnes déjà existantes •Joindre de colonnes : permet de fusionner plusieurs colonnes sur la base d’un ordre et d’un séparateur. Vous pouvez ou non prendre en compte les valeurs nulles, et ajouter le résultat dans la colonne active ou dans une nouvelle colonne. Pour des jonctions plus complexes vous pouvez aussi utiliser une formule (voir diapo) 49 L’éditeur de formule GREL La concaténation L’une des fonctions de base pour les chaines de caractères est la concaténation. Pour cela vous articulez vos « value » initiales avec d’autres chaines de caractères grâce à des « + » : "valeur à ajouter"+value Exemple : "https://idref.fr/"+value Pour générer une URL à partir d’un identifiant IDREF. Il faut ajouter« https://idref.fr/ » devant l’identifiant. 50 L’éditeur de formule GREL Remplacer le contenu d’une cellule par une cellule d’une autre colonne Cette formule permet de transférer les valeurs d’une colonne dans une autre colonne cells["colonne à transférer"].value Exemple : cells["test_ID"].value Si on applique cette formule sur une colonne, le contenu de chaque cellule de cette colonne sera remplacé par le contenu de la cellule de la colonne “test_ID” Un second exemple où l’on reconstitute une cellule à partir de cellules existantes. Ici on reconstitute une adresse postale complète en ajoutant le contenu de la colonne “Commune” puis de la colonne “Code postal” à la colonne “Adresse” initale. Le tout séparé par des virgules value+", "+cells["Commune"].value+", "+cells["Code postal"].value 51 L’éditeur de formule GREL Extraire une partie d’une cellule (division) Avec cette formule vous choisissez un séparateur pour diviser votre cellule et vous sélectionnez la partie que vous souhaitez conserver. La numérotation des positions commence à 0 (pour sélectionner le premier segment il faut choisir la position « 0 »). Vous pouvez aussi utiliser une numérotation inversée (pour sélectionner le premier segment en partant de la fin il faut choisir la position « -1 ») : value.split("Séparateur")[position de la chaine à conserver].toString() Exemple : value.split("930")[-1]. toString() 0 1 2 3 4 5 6 -7- -5 -4 -3 -2 -1 OU 52 L’éditeur de formule GREL Extraire une partie d’une cellule (REGEX) OpenRefine vous permet d’extraire une chaine de caractère d’une cellule avec la formule value.find(). Pour cela vous devez connaitre la structure de la chaine de caractères à exporter et l’indiquer via une expression régulière. Notez que dans une formule vous devez mettre les REGEX entre slash (/). Par défaut les résultats de cette formule sont stockés dans une liste, vous pouvez convertir ce résultat en chaine de caractères en ajoutant .toString() à la fin de votre formule value.find(/REGEX de la chaine de caractère à extraire/).toString() Exemple : value.find(/^\d{9}/).toString() Ici un exemple pour récupérer le numéro SIREN à partir du numéro SIRET. Le numéro est SIREN est composé des 9 premiers chiffres du numéro SIRET •\d est la REGEX pour les chiffres •\d{9} signifie 9 chiffres successifs •^ signifie situé au début de la chaine 53 L’éditeur de formule GREL Le croisement de colonnes OpenRefine vous permet d’importer des colonnes d’un autre projet OpenRefine sur la base d’une valeur pivot grâce à la fonction « cross » avec la formule suivante : cell.cross("Nom du projet duquel on souhaite importer la colonne", "colonne pivot").cells["colonne à importer"].value[0] Exemple : cell.cross("Test_ID_OpenRefine", "hal").cells["File_HAL"].value[0] 54 L’éditeur de formule GREL A vous de jouer La concaténation Formule type : "valeur à ajouter" + value Exercice : Dans la colonne « Identifiant wikidata », ajoutez « https://www.wikidata.org/wiki/ » devant chaque identifiant pour les transformer en URL consultables Le transfert de valeur dans une cellule Formule type : cells["colonne à transférer"].value + " > " value Exercice : Ajoutez le contenu de la colonne « Pays », comme premier élément dans la colonne « Localisation » Extraire une valeur avec un séparateur Formule type : value.split("séparateur")[position].toString() Exercice : Dans la colonne « Localisation », faire une extraction du département uniquement Extraire une valeur avec une REGEX Formule type : value.find(/REGEX/).toString() Exercice : Isolez les codes postaux dans la colonne « Adresse complète ». Pour cela trouver la REGEX qui permet d’identifier une suite de 5 chiffres 55 De la documentation complète sur GREL • Sur le site d’OpenRefine : https://openrefine.org/docs/manual/grelfunctions •Mathieu Saby –Mémo : Programmer dans Openrefine avec GREL, 2019 : https://fr.slideshare.net/27point7/programmer-dans-openrefine-avec-grel •Les tutoriels vidéo du Réseau Bases de Données : https://www.canalu.tv/chaines/rbdd/tes-premiers-pas-avec-openrefine-0 •Lancez-vous, testez, recherchez des solutions sur des forums ! 56 Nous Contacter [email protected] SORBONNE-UNIVERSITE.FR Sauf mention contraire, cette présentation est mise à disposition selon les termes de la Licence Creative Commons Attribution 2.0 France. Icônes : freepik MERCI Cellule données et humanités numériques [email protected]