#36 - OpenRefine, "Excel aux hormones" pour nettoyage de données

36 openrefine 1 Rechercher/remplacer par, faire des filtres, des tris, voire des macros, c'est sympa, mais souvent très long, fastidieux, et parfois frustrant. Alors jetez-vous sur OpenRefine, "Excel aux hormones" selon l'excellente expression d'Antoine Courtin.

Testé et adopté à l'occasion du datasprint des archives, OpenRefine permet de faire extrêmement vite (et très bien) des nettoyages, des harmonisations et des enrichissements de données, en masse !

Même si l'essentiel de la documentation est en anglais, l'outil est utilisable en français et manipulable assez rapidement. Point non négligeable quand on travaille en collectivité sur des réseaux informatiques très verrouillés : il suffit d'avoir téléchargé le logiciel sur une clé USB, de glisser le répertoire sur son ordinateur et d'ouvrir le fichier exécutable pour l'utiliser (même sans droit administrateur).

Pour ma part, je me suis d'abord contentée de visualiser les 3 vidéos ci-dessous (en anglais, mais elles valent le coup d'oeil, juste pour avoir une idée de l'immensité des possibles)... avant de me jeter dans le bain, de tâtonner et de chercher au fur et à mesure des besoins.

Pour l'histoire rapide, l'outil a initialement été développé en open-source, puis soutenu par Google (d'où l'existence de versions Google Refine, de 2010 à 2012) puis de nouveau entièrement ouvert.

En plus d'être un peu didactique, le tutoriel ci-dessous est surtout un pense-bête personnel mais partagé, amené à s'enrichir régulièrement, pour garder sous la main les formules plus ou moins alambiquées trouvées parfois au bout de longues recherches et d'essais plus ou moins fructueux.

Webographie succincte

Site officiel
Documentation officielle (la Bible des formules, en anglais)
Thomas Padilla, Démarrer avec OpenRefine (en anglais)
INRA, tutoriel GoogleRefine (en français)
Mathieu Saby, Mini site tutoriel Openrefine (en français)
Mathieu Saby, Nettoyer et préparer des données avec OpenRefine (URFIST PACA, en français) ; Nettoyer et préparer des données avec OpenRefine (BULAC, mise à jour 2019)
Ettore Rizza, Tutoriels vidéos (en français)
Antoine Courtin, Pense-bête OpenRefine (en français)
Maïwenn Bourdic, Atelier OpenRefine - Forum des Archives 2019 (avec jeu de données exemple et captures d'écran pas à pas)
Maïwenn Bourdic, Atelier OpenRefine et Wikidata (2019, journées Wikimédia Culture et numérique)
Maïwenn Bourdic, Projet Datasprint - Permis de construire de Rennes (2016, avec nettoyage de données) (en français)
RefinePro knowledge base, plein de formules magiques (en anglais)
Enipedia, tutoriel OpenRefine (en anglais)
School of data, tutoriel OpenRefine (en anglais)
Library Carpentry, tutoriel OpenRefine (en anglais)
Online Journalism, Convertir du XML ou JSON en tableur (en anglais)

Installer

Télécharger sur OpenRefine le zip de la dernière version du logiciel.
Glisser le contenu du zip dans un nouveau répertoire, par exemple intitulé Openrefine (à créer sur l'ordinateur en local, pas sur un serveur).
Pour ouvrir le logiciel, double-cliquer sur le fichier openrefine avec l'icône (voire faire un raccourci pour les fois suivantes).

Une fenêtre d'invite de commandes (la fenêtre noire) s'ouvre automatique, et au bout de quelques secondes un nouvel onglet apparaît dans votre navigateur Internet. C'est dans ce navigateur que tout se passera. Pour fermer OpenRefine, il suffit de clore la fenêtre d'invite de commandes.

Cette procédure d'installation ne nécessite pas d'avoir les droits Administrateur de l'ordinateur. Pour l'utiliser sur un poste informatique un peu verrouillé côté DSI, il suffit de télécharger le zip sur une clé USB en amont, et de copier ensuite le répertoire et ses fichiers sur son ordinateur.

[avant toute chose vous avez le droit, dans le menu à gauche, de cliquer sur Language Settings et de basculer en français]

Créer un projet

Openrefine permet d'importer et de traiter des fichiers de tout format : CSV bien sûr, Excel, HTML, XML, JSON, texte... Pour importer des données, cliquer sur Parcourir pour ouvrir l'explorateur de fichiers, sélectionner le fichier puis cliquer sur Suivant.

On peut également importer des données en ligne en choisissant les options Web Addresses ou Google Data (suite bureautique Google).

À noter :

Le fichier importé doit être enregistré en local sur votre ordinateur ; si le fichier de données est sur un serveur partagé, il faut l'enregistrer préalablement en local.
L'import de fichier .ods (OpenOffice) ne marche pas ; il faut le convertir préalablement en .csv ou au format Excel.

Avant de créer le projet, un aperçu de l'import permet d'ajuster les paramètres pour que les données soient correctement intégrées dans le tableur qui va servir d'interface de travail.

Les formats sont détectés automatiquement lorsqu'ils sont conformes, mais des réglages peuvent être nécessaires.

encodage des accents : si les accents ne sont pas bien affichés, régler le champ Character encoding : UTF-8
si le caractère séparateur du format csv

L'aperçu se met à jour automatiquement. Une fois que cela semble correct, renommer si besoin le nom du projet et cliquer sur Create Project

Les différents projets sont accessibles depuis le menu gauche Open project (au besoin forcer le rafraîchissement du navigateur en faisant Ctrl + F5). Les données sont strictement stockées sur votre ordinateur ; le navigateur Internet n'est que l'interface d'exécution du logiciel.

Aperçu de l'interface

Les données sont présentées dans OpenRefine sous la forme d'un tableau, quel que soit le format d'origine (tableau csv ou Excel, XML arborescent, JSON, etc.).

Dans la première colonne, un numéro de ligne est attribué automatiquement à chaque ligne lors de l'import (utile pour retrier si besoin).

Les en-têtes de colonnes sont toutes munies d'une petite flèche cliquable, qui va être la clé de tout. Par défaut 10 lignes sont affichées sur l'écran, mais on peut augmenter jusqu'à 50 lignes ; la suite des données est paginée. Le nombre total de lignes est affiché au-dessus du tableau (ici 39703 lignes).

A gauche de l'écran, il est possible d'accéder à l'historique des modifications en cliquant sur Défaire / Refaire (voire d'exporter le script des modifications, voir plus loin).

En haut à gauche se trouve le bouton Exporter, qui permet de récupérer le fichier de données modifié.

De manière générale, une action s'applique à l'ensemble défini par les filtres / facettes actifs.

Filtres de texte et facettes

Histoire de garder ses repères, commençons par faire un simple Filtre de texte. Pour ce faire : Clic sur la colonne / Filtrer le texte. On peut activer la sensibilité à la casse, ou utiliser des expressions régulières (qui feront l'objet d'un autre tutoriel !).

Par exemple : un filtre sur "octroi" va afficher les 970 lignes concernées sur les 39703 totales.

Assez classique... On peut alors faire des modifications à partir de cette colonne filtrée (voir ensuite).

Plus intéressantes sont les facettes. Elles permettent de regrouper des occurrences similaires et de les manipuler en masse. Pour ce faire : Clic sur la colonne / Facette / Facette textuelle.

Les facettes peuvent être triées par ordre alphabétique (trier par nom) ou par nombre d'occurrence (trier par compte).

Elles peuvent surtout être éditées et la modification répercutée en masse.

Exemple : en éditant le contenu 2063-08-07 visiblement erroné, on peut en un clic corriger en masse les 195 occurrences par 1963-08-07.

On peut également rapidement identifier et corriger les coquilles de saisie, soit en utilisant la fonction éditer , soit en cliquant sur Groupe. Dans l'écran qui s'affiche alors, les occurrences avec des chaînes de caractères proches sont listées et peuvent être fusionnées. Plusieurs algorithmes sont disponibles pour analyser et optimiser la détection de ces potentiels doublons (paramètres Méthode et Fonction de codage).

Pensez à jeter un oeil aux facettes courantes, et notamment facette par blanc, qui permettent de filtrer les lignes dont les cellules sont pleines ou vides (et donc souvent d'éliminer en masse les "vides").

Enfin, filtres et facettes peuvent se cumuler pour créer et agir sur des ensembles définis.

On peut également utiliser directement la fonctionnalité Clic sur la colonne / Editer les cellules / Grouper et éditer.

Il existe également des facettes :

par blanc / vide : uniquement les cellules remplies / ou vide
par longueur de mot : pour trouver les éventuelles cellules avec du texte trop long là où on attend que quelques caractères
chronologique (pour les colonnes typées Date).

Typer une colonne en date

Transformer le format d'une colonne en date (de type aaaa-mm-jjT00:00:00Z, soit jusqu'à l'heure !) permet d'utiliser la facette chronologique, et donc la jauge temporelle. Pour ce faire : Clic sur la colonne / Editer les cellules / Transfomations courantes / En date.

Un conseil : dupliquer la colonne date pour faire la manipulation sur un double...

Transformations communes

Enlever les espaces blancs, les majuscules inopportunes, etc. OpenRefine permet d'appliquer facilement des transformations de base sur une colonne : Clic sur la colonne / Editer les cellules / Transformations courantes / au choix :

enlever les espaces inutiles en début et fin de cellule
enlever les doubles espaces inutiles
changer la casse
etc.

Etoiles et drapeaux

Nettoyer des données signifie souvent supprimer des lignes en masse. Pour gérer le tout, on peut utiliser le système de marquage, à coup d'étoile (plutôt positif) ou de drapeau (plutôt négatif) pour identifier des lignes. Pour ce faire : Clic sur la colonne Toutes / Editer les lignes / étoiler ou marquer les lignes.

Le marquage étoile ou drapeau s'applique bien sûr à toute la sélection en cours.

Supprimer des colonnes et des lignes en masse

Pour supprimer les lignes de la sélection en cours (facette sur les lignes marquées par un drapeau par exemple, ou tout autre facette ou tri) : Clic sur la colonne Toutes / Editer les lignes / Supprimer les lignes correspondantes.

De même, pour supprimer des colonnes inutiles ou les réordonner : Clic sur la colonne Toutes / Editer les colonnes / Retrier ou supprimer les colonnes. Sinon il reste toujours la possibilité, sur chaque colonne : Clic sur la colonne / Editer la colonne / Supprimer la colonne.

Rechercher / remplacer par

Classique mais essentiel, tout comme les facettes : le "rechercher / remplacer par" en masse, dans une colonne. Pour ce faire : Clic sur la colonne / Editer les cellules / Transformer > value.replace("[MotRecherché]","[MotLeRemplaçant]")

Exemples :

Remplacer les "espace tiret espace" par un "tiret" : value.replace(" - ","-")

Des choses plus poussées (avec regex), comme:

"Enlever tout sauf les chiffres" : replace(value,/[[a-z],[A-Z],(é|è|à|ù),\,\;\:\.\?\/\!\=\+\"\'\-\(\)\[\]]/,"")
Extraire une année, soit une suite de 4 chiffres qui se suivent : value.match(/.*(\d{4}).*/)[0]

Clic sur la colonne / Editer les cellules / Transformer > laisser vide est également un moyen très rapide pour dupliquer une colonne (avant de faire une manipulation complexe par exemple, pour ne pas perdre le contenu initial - même si toute mauvaise action peut être annulée)

Diviser une colonne

On souhaite diviser une colonne en plusieurs autres colonnes, sur la base d'un séparateur commun. Par exemple : dans la colonne Date de protection figure la date et le type de protection, séparés par "espace : espace". Créons une colonne date et une colonne type de protection.

Pour ce faire : Clic sur la colonne / Éditer la colonne / Diviser en plusieurs colonnes > définir le séparateur commun

Dans la fenêtre qui s'ouvre, indiquer le séparateur commun. Attention, il peut être nécessaire de décocher "Supprimer cette colonne" si on ne veut pas perdre la colonne de base !

Il est souvent nécessaire de nettoyer et vérifier avant cette action la "qualité" du séparateur commun, notamment grâce aux filtres (y a-t-il bien partout un espace avant et après, ...).

Vider / remplir des cellules

Il est possible de remplir / vider en masse des cellules, c'est-à-dire remplir toutes les cellules vides par le premier contenu rencontré au-dessus. Pour ce faire : Clic sur la colonne / Editer les cellules / Remplir.

Plus parlant en image : seule la première occurrence de la cote est saisie, mais je souhaite la dupliquer en masse sur les lignes inférieures.

Vider les cellules permet de faire l'inverse : ne garder que la première occurrence d'une valeur répétée sur plusieurs lignes.

Subdiviser des cellules

Les cellules contiennent du contenu à diviser en plusieurs lignes ? Autant que possible il vaut mieux avoir un séparateur commun entre les différentes valeurs (; par exemple). Ensuite : Clic sur la colonne / Editer les celulles / Diviser les celulles multivalués > indiquer le séparateur commun.

Extraire le contenu d'une colonne

OpenRefine permet d'extraire un contenu (date, hashtag, des mots etc.) d'une colonne, et d'en créer une nouvelle avec ce contenu. Pour ce faire : Clic sur la colonne / Editer la colonne / Ajouter une colonne en fonction de cette colonne > formule

Exemple de formules :

extraire une année (soit 4 chiffres qui se suivent) : value.match(/.*(\d{4}).*?/)[0]
extraire un hashtag (des stats Twitter par exemple) : replace(value,/(\s|^)([^\#]+)/,' ')

Changer le type de date

Clic sur la colonne / Editer les cellules / Transformer > value.toDate('yyyy-MM-dd','MMM-yy').toString('dd-MM-yyyy')

Concaténer

Rassembler le contenu de deux ou plusieurs colonnes en une seule, en les séparant.

Clic sur la colonne / Editer la colonne / Ajouter une colonne en fonction de cette colonne > cells["colonne 1"].value + " - " + cells["colonne 2"].value

Résultat : colonne 1 - colonne 2. Le séparateur souhaité est mis entre les guillemets.

Si les colonnes contiennent certaines cellules vides, utiliser à la place la formule suivante :

if(isBlank(cells["colonne 1"].value), " - ", cells["colonne 1"].value) + " - " + if(isBlank(cells["colonne 2"].value), " - ", cells["colonne 2"].value) + " - "
+ if(isBlank(cells["colonne 3"].value), " - ", cells["colonne 3"].value) + " - " + if(isBlank(cells["colonne 4"].value), " - ", cells["colonne 4"].value)

Enrichir à partir d'un autre projet openrefine

Il est possible d'importer les données d'un autre projet OpenRefine :

Je souhaite importer la colonne "geojson" du projet "Geo Contours Pays" (colonne qui contient tous les contours des pays du monde) dans mon jeu de données en cours. Je me positionne sur la colonne qui va servir de pivot (ici : "nation"), qui contient un contenu qu'on retrouve aussi dans le projet contenant le nom "Geo Contours Pays"

Clic sur la colonne / Editer a colonne / Ajouter une colonne en fonction de cette colonne : if (value!='null',cell.cross("Geo Contours Pays","pays").cells["geojson"].value[0],'')

Où :

Geo Contours Pays : est le nom du projet dont je veux importer du contenu
pays : est le nom de la colonne pivot, censée être identique à la colonne
geojson : est le nom de la colonne que je souhaite importer dans mon projet en cours (sur la base de "contenu de la colonne pays" = "contenu de la colonne "nation")

Historique des modifications

L'ensemble des modifications est enregistré.
On peut revenir en arrière pour annuler une ou plusieurs modifications.

Importer / exporter le script

Il est possible de récupérer partie ou totalité du script de modifications, pour par exemple le réappliquer sur des fichiers semblables.

Dans le menu gauche, Défaire / Refaire puis Extraire.

Copier tout ou partie du script de modifications dans un fichier texte. Et le réappliquer à un fichier semblable (Défaire / Refaire puis Appliquer).

Exporter le fichier modifié

Les données modifées sont exportables sous différents formats. Attention l'export concerne le contenu en cours d'affichage. S'il y a des filtres ou des facettes actifs, seul le contenu correspondant sera exporté.

Importer un xml

Il est possible d'importer un fichier XML qu'OpenRefine va transformer en tableau (en gros, une balise = une colonne). Dans le fichier XML à importer, enlever d'abord tout ce qui concerne la zone de déclaration. Pour un fichier XML ead par exemple, il est possible d'alléger le contenu du <archdesc> et ne garder que le contenu du <dsc> (en conservant quand même un fichier xml valide avec toutes les balises bien fermées).

Créer un projet > Importer le fichier. Dans l'aperçu du fichier, il va falloir sélectionner le noeud, soit en gros l'ensemble de balise qui constitue un enregistrement. Dans un fichier XML ead, il faut généralement sélectionner le <c> le plus haut ou le <did> dans l'arborescence.

Une fois le noeud sélectionné, l'aperçu permet d'ajuster les règlages. Lorsque cela convient, cliquer sur Créer un projet. Le xml est transformé en tableur, qu'il ne reste plus qu'à nettoyer !

Insérer des expressions régulières

Utiliser des expressions régulières dans OpenRefine : les encadrer par des slashs / /.

Garder le début / la fin d'une cellule

Ne garder que le premier mot (ou "string" : à savoir une chaîne de caractères consécutifs) dans les cellules d'une colonne.

Clic sur la colonne / Editer les cellules / Transformer > value.partition(smartSplit(value," ")[1])[0]

Ne garder que le dernier mot d'une colonne.

Clic sur la colonne / Editer les cellules / Transformer > value.partition(smartSplit(value," ")[-1])[1]

Modifier une chaîne de caractère

Modifier une chaîne de caractère dans une cellule : par exemple mettre systématiquement en majuscule le premier mot de chaque cellule.

Clic sur la colonne / Editer les cellules / Transformer > toUppercase(smartSplit(value,' ')[0])+ ' '+value.replace(smartSplit(value,' ')[0],'')

La cellule Dupont Martin deviendra DUPONT Martin.

[0] correspond au 1er mot de la cellule ; [1] correspond au 2ème mot

[-1] correspond au dernier mot de la cellule ; [-2] à l'avant-dernier, etc.

Remplir une colonne vide par une autre colonne

Remplir une colonne où les valeurs sont vides par le contenu d'une autre colonne.

Clic sur la colonne / Facettes / Facettes courantes / Facettes par blanc >"true"

puis

Clic sur la colonne / Editer les cellules / Transformer > cells["column1 à copier"].value

API de réconciliation Openrefine > Wikidata (fr) : https://wikidata.reconci.link/fr/api
Antoine Courtin, “Réconcilier” une liste de nom d’architectes avec Wikidata en utilisant OpenRefine (dont géolocalisation)
Réconcilier des données avec Commons

Aligner vos données avec Wikidata grâce à l'outil Open Refine from Gautier Poupeau

Ou enrichir Wikidata !

openrefine reconciliation wikidata

Récupérer les données de géolocalisation

depuis une réconciliation Wikidata (voire au-dessus), en récupérant la propriété "coordonnées géographiques"
Moissonner des API extérieures : Clic sur la colonne / Ajouter une colonne en moissonnant des URL" puis, au choix :
- Google Maps 'nécessite une APIKEY à mettre à la fin de : "https://maps.google.com/maps/api/geocode/json?sensor=false&address=" + escape(value, "url") + "&key=APIKEY"
- OpenStreetMap :
- Base Adresse (data.gouv.fr) : voir Christian Quest, "Géolocaliser avec l'API de adresse.data.gouv.fr". Valable uniquement pour la France, précision au numéro de voie - actuelle, mais nécessite d'avoir des colonnes voie / commune / code postal ou insee.

Ajouter un numéro incrémenté

Utile quand on a besoin de générer un numéro d'identifiant unique.

Clic sur une colonne / Ajouter une colonne en fonction de cette colonne / rowIndex

Il est possible de rajouter des caractères avant / après, par exemple "bdx_" + rowIndex, ce qui donnera : bdx_1, bdx_2, bdx_3...

API, SRU et réconciliation

SRU BNF : par exemple pour récupérer l'ISNI, ajouter une colonne en moissonnant les URL / "http://catalogue.bnf.fr/api/SRU?version=1.2&operation=searchRetrieve&query=aut.isni%20all%20%220000000121261347%22" + escape(value, "url")
VIAF : ajouter le service de réconciliation http://refine.codefork.com/reconcile/viaf
Commons : https://commonsreconcile.toolforge.org/en/api

Vrac

En mode Entrées, remplir toute la colonne d'un même enregistrement : row.record.cells["nom_colonne"].value[0]
value.toDate('yyyy-MM-dd','MMM-yy').toString('dd/MM/yyyy')
Ajouter le lien vers l'article Wikipedia : ajouter une colonne en fonction de cette colonne / langage Python : copier coller le code disponible en lien
Comparer les valeurs de 2 colonnes en insérant une colonne Yes/No : ajouter une colonne en fonction de cette colonne / if(cells["site officiel"].value == cells["website_url"].value, "Y", "N")

Enregistrer