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

36 refine logoRechercher/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

 

Sommaire : Installer | Créer un projet | Aperçu de l'interface | Filtres et facettes | Typer une colonne Date | Transformations communes | Etoiles et drapeaux | Supprimer en masse | Rechercher / remplacer par | Diviser une colonne Remplir / vider les cellules | Subdiviser une celulle | Extraire le contenu d'une colonne | Changer le type de date | Concaténer des colonnes | Enrichir depuis un autre projet | Historique des modifications | Importer / exporter / réappliquer les modifications | Exporter les données | Importer un xml | Insérer des expressions régulières | Garder le début / la fin d'une cellule | Modifier une chaîne de caractère | Remplir une colonne vide par une autre colonne | Changer tous les mots en lettres capitales | Enlever les chiffres | Caractère d'échappement | Insérer un contenu en début de cellule Enrichir depuis Wikidata (ou une API) |

 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.

37 creer projet openrefine

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.

 

37 import donnees

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

37 apercu interface

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.

37 filtre texte

 

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.

37 facette texte

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.

 

37 facette texte editer

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

37 facette texte editer 2Pensez à 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.

 

37 filtres facettes

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

36 facettes grouper editer

 

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

36 typer colonne date


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.

36 transformations classiques

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.

36 marque etoile

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.

 

36 supprimer lignes colonnes

 
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]")

 36 rechercher remplacer par

 

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

36 diviser colonne 1

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 !

 

36 diviser colonne 2Il 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.

36 remplir cellule

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.

36 subdiviser cellule

 

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 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 les cellules / Transformer > 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.

36 historique


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.

36 historique sauvegarder

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

 

36 historique 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é.

36 exporter

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.  

36 import xml

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 !

36 import xml 2

 

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])[0]

 

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

Changer tous les mots en lettres capitales

 

Clic sur la colonne / Editer les cellules / Transformer > forEach(value.split(' '), e, if(isNull(e.trim().match(/([A-Z]{4,})/)), e, e.toTitlecase())).join(' ')

 {4,} signifie 4 et plus (lettres capitales qui se suivent), la valeur peut être modifiée.

 

Enlever tous les chiffres dans les cellules

Clic sur la colonne / Editer les cellules / Transformer > replace(value, /\d/, '')

 

Caractère d'échappement

L'apostrophe est un caractère utilisé dans les formules ; lorsqu'il est utilisé dans du contenu, il faut utiliser un "caractère d'échappement" pour qu'il soit correctement considéré, en l'occurrence le faire précéder de \

value.replace('d\'honorable','de ')

 

Insérer un contenu en début de cellule

Insérer un intitulé ou une URL tronquée en début de cellule.

Clic sur la colonne / Editer les cellules / Transformer > 'contenu à insérer' + value


Enrichir depuis Wikidata ou API

 

Enregistrer

Mots-clés: dataviz, openrefine, données

Imprimer E-mail

Ajouter un Commentaire


Code de sécurité
Rafraîchir