Daten-Check mit OpenRefine

SODa Workshops: Einführung zum Daten-Check mit OpenRefine (Tutorial)

von Louise Tharandt und Michael Markert

Zum Einstieg

OpenRefine verstehen

OpenRefine sieht ein wenig aus wie Excel, hat sonst aber wenig Gemeinsamkeiten.

Wesentliche Eigenschaften von OpenRefine:

  • kann praktisch alle Text-Dateiformate und Datenstrukturen wie CSV, XML, JSON importieren
  • kann auf Webseiten und Schnittstellen zugreifen und diese Daten verarbeiten (z. B. für einen Abgleich mit der GND, Wikidata, OpenStreetMap usw.)
  • erlaubt einen schnellen Überblick über Dateien mit zehntausenden Zeilen
  • ist ideal für Bereinigungs- und Sortieraufgaben, etwa bei dem Import von Daten in ein Museumsdokumentationssystem
  • eignet sich deshalb vor allem für wiederholbare, komplexe Bearbeitungsschritte, die ganze Spalten oder die ganze Tabelle betreffen
  • und hat dafür eine eigene Skriptsprache namens GREL integriert, es kann aber auch Python genutzt werden
  • ist für Kalkulation und Diagramme ebenso wenig geeignet, wie die Arbeit an einzelnen Zellen/Feldern – dafür gibt es Excel

weitere Tutorials

Spezielle Themen

Clustering

Algorithmische Suche von ähnlichen Begriffen, die dann durch einen Begriff ersetzt werden können – etwa bei Tippfehlern

GND-Abgleich in OpenRefine
Batch-Upload von Daten zu Wikimedia Commons
Mit OpenRefine Daten aus dem Web laden und verarbeiten

Bei allen Problemen kann man sich auch an das sehr hilfreiche englischsprachige Forum wenden – wenn dort nicht ohnehin schon eine Lösung beschrieben ist.

Regex und GREL

Übungen

Für die Übungen benötigt man zwei Demo Datensätze.

  1. Sammlungsobjekte-Demo: Der erste Datensatz stammt aus dem Demosystem von Objekte im Netz, einem vom BMBF geförderten Projekt für eine gemeinsame Erschließungs- und Digitalisierungsstrategie für die Sammlungen der Friedrich Alexander Universität Erlangen-Nürnberg (https://objekte-im-netz.fau.de/oindemo/). Diese Daten wurden für unseren Demo Datensatz angepasst und abgeändert:

    Sammlungsobjekte-Demo Datensatz: Sammlungsobjekte_ObjekteImNetz_FAU.csv

  2. GBIF-Owl-Pellet-Demo: Der zweite Datensatz stammt vom Royal Saskatchewan Museum und identifizierte Kleintiere aus mehr als 10000 Geölle Sammlungen von Eulen. Zu finden ist der Datensatz auf GBIF und unter: Frier D (2020). Royal Saskatchewan Museum - Owl Pellet Collection. Version 2.3. Royal Saskatchewan Museum. Occurrence dataset https://doi.org/10.5886/f0xsbu. Die Daten dieses Datensatzes wurden nicht verändert.

Samlungsobjekte-Demo

1) Reihenfolge des Namens abgleichen und korrigieren

Spalte "Bearbeiter" => Facet => Text Facet

  • Namen vergleichen / Fehler finden

edit => apply

  • innerhalb des kleinen Facet-Fensters mit der Maus über Jennifer Höhne schweben und auf edit klicken
  • Reihenfolges des Namens ändern, auf Apply clicken
  • runterscrollen zu Wurst, Wurst, Hans und auf den Namen klicken
  • beide Zeilen sind rechts im großen Fenster nun sichtbar

include => edit => apply

  • mit der Maus über Wurst, Hans schweben und auf der rechten Seite auf include clicken
  • alle drei Zeilen sind nun im großen Fenster sichtbar ausgewählt
  • innerhalb des kleinen Filter-Fensters mit der Maus über Wurst, Wurst, Hans schweben und auf edit klicken den doppelten Nachnamen löschen, auf Apply clicken
  • Korrektur ist nun in beiden Fenstern sichtbar
  • um wieder alle Zeilen zu sehen, auf den orange markierten Namen klicken oder im kleinen Fenster oben rechts auf reset
  • Facet Fenster schliessen, oben links auf x

2) Leerzeichen suchen, finden und löschen

Spalte "Bezeichnung/Titel" => Facet => Text Facet

  • innerhalb des kleinen Filter-Fensters sieht man an erster Stelle eine graue 1
  • beim Anklicken der Auswahl wird die Zeile im großen Fenster gezeigt
  • in der Spalte "Bezeichnung/Titel" auf edit klicken, man sieht, dass dort ein Leerzeichen gesetzt war
  • Leerzeichen löschen und Apply drücken
  • im kleinen Filter Fenster auf reset klicken, Auswahl ist wieder zurückgesetzt

Spalte "Hersteller (Person)" => Facet => Text Facet

  • die ersten beiden Namen sind durch Leerzeichen am Anfang etwas weiter eingerückt

Spalte "Hersteller (Person)" => Edit Cells => Common Transforms => Trim leading and trailing whitespaces

  • alle Leerzeichen an Anfang und Ende werden in allen Feldern dieser Spalte gelöscht

Spalte "Hersteller (Person)" => Edit Cells => Common Transforms => Collapse consecutive whitespaces

  • alle aufeinander folgenden Leerzeichen (bspw. zwei hintereinander) werden gelöscht
  • Diese Funktionen können auch über alle Spalten hinweg durchgeführt werden

Spalte "All" => Edit all columns => Trim leading and trailing whitespaces... Spalte "All" => Edit all columns => Collapse consecutive whitespaces...

Tipp: Am Anfang eines Projektes kann man nach dem Auswählen des Datensatzes bevor man das Projekt in OpenRefine erstellt ein Häkchen unten links bei Trim leading & trailing whitespace from strings machen.

3) Sortieren und filtern

Spalte "Objektart" => Sort ... => Sort ... =>

  • Text und Sortierung a-z ausgewählt lassen
  • auf der rechten Seite das Feld "blanks" nach oben ziehen
  • die Zeilen sind nun sortiert, so dass in der Spalte "Objektart" leere Felder zuerst angezeigt werde und danach nach dem Alphabet die Felder sortiert sind

Spalte "Objektart" => Text filter

  • auf der linken Seite erscheint ein Suchfeld
  • Kreisel eingeben
  • alle Zeilen mit dem Wort Kreisel in der Spalte "Objektart" sind jetzt im großen Fenster zu sehen
  • Um zur Ausgangssortierung zu kommen, im Suchfeld auf reset klicken oder links oben das x drücken

Spalte "Objektart" => Sort ... => Remove sort

Tipp: oben in der Zeile, in der man die Anzahl an zu sehenden Reihen sieht, kann man die Sort Option auch ändern

4) URLs auftrennen und korrigieren

Spalte "Bild" => Edit cells => Split multi-valued cells

  • die Auswahl bei "by Separator" lassen und in das freie Feld folgenden Wert (Expression für Zeilenumbruch) einfügen:

    \n
  • ein Häkchen ☑️ bei regular expression setzen
  • dadurch sind bei mehrfachen Werten in der Spalte neue Zeilen entstanden
  • dies kann man bei einem Vergleich oben links zwischen rows (in diesem Fall 49) und records (42) sehen

Spalte "Bild" => Edit cells => Transform

  • In das Feld für die Expression folgende Funktion einfügen:
value.replace(" ","%20")

Spalte "Bild" => Edit cells => Join multi-valued cells

  • Es sind wieder 42 rows und 42 records vorhanden
  • Join arbeitet immer pro record, so kommt wieder zusammen, was vorher zusammen war

5) Clustern von Begriffen zur Vereinheitlichung von Schreibweisen

Spalte "Teilsammlung" => Facet => Text Facet

  • im kleinen Facet Fenster oben rechts auf Cluster drücken
  • im neu geöffneten Fenster keine Änderungen vornehmen und in der Mitte auf Cluster klicken
  • für dieses Beispiel reicht die Cluster Funktion noch nicht
  • in der Mitte oben bei Keying function die verschiedenen Funktionen ausprobieren und Unterschiede beobachten
  • Keying function "Cologne phonetic" auswählen und auf Cluster drücken
  • alle Schreibweisen sind nun vorhanden und können angepasst werden
  • dort, wo eine Vereinheitlichung durchgeführt werden soll, ein Häkchen bei Merge setzen und rechts daneben die korrekte Schreibweise eintragen
  • Merge selected & close
  • alle Begriffe sind nun korrekt vereinheitlicht

6) Werte aus einer Spalte in mehreren aufteilen

Spalte "Messung" => Facet => Text Facet

  • Werte kontrollieren
  • Werte an 2. Position (H.: 17cm, Dm.: 13cm) korrigieren und anpassen (Höhe: 17cm, Durchmesser: 13cm) durch edit und apply

Spalte "Messung" => Edit column => Add column based on this column ...

  • Oben im Feld neuen Spalten Namen einfügen, e.g. Länge
  • innerhalb des Expression Feldes folgende Funktion einfügen:
value.find(/Länge:\s*\d+(,\d+)?(mm|cm)/)[0]

Erklärung:

Expression Bedeutung
Länge: Sucht den genauen Text „Länge:“
\s* Erlaubt beliebig viele Leerzeichen (oder keine) nach „Länge:“
\d+ Erfordert mindestens eine Ziffer (ganze Zahl wie 12, 456 usw.)
(,\d+)? Optional: Ein Komma gefolgt von einer weiteren Zahlenfolge (z. B. ,45 für Dezimalzahlen)
(mm|cm) Erwartet, dass der Text mit "mm" oder "cm“ endet


Aus der Spalte "Messung" wurden nur die Längenangaben extrahiert, dies kann man durch austauschen der Wörter mit den Angaben für Breite, Höhe und Durchmesser wiederholen.

Für eine weitere Spalte "Gewicht" folgende Funktion einfügen:

value.find(/Gewicht:\s*\d+(,\d+)?(g|kg)/)[0]

7) Export als Tabellendaten

  • Rechts oben in der Oberfläche auf "Export" klicken
  • Format wählen, z. B.:
    • "Comma-separated value" für möglichst universelle Weiterverarbeitung (kann Probleme mit Umlauten und Sonderzeichen in Excel geben)
    • "Excel (.xls)" wenn es in gängigen Tabellenkalkulationen möglichst reibungslos funkionieren soll
    • "Custom tabular" wenn man genau einstellen möchte, welche Spalten in welcher Zeichenkodierung und mit welchen Trennzeichen in welches Zielformat exportiert werden sollen

GBIF-Owl-Pellet-Demo

8) letztes Element einer komplexen Zeichenkette wählen

Datensatz GBIF-Demo => Spalte "occurrenceID" => Edit cells => Transform => Expression:

value.split(":")[-1]

Erstes Element wäre [0], zweites [1] usw.

9) Link zu GBIF-Datensatz der Art ergänzen

Datensatz GBIF-Demo => Spalte "speciesKey" => Edit cells => Transform => Expression:

"https://www.gbif.org/species/" + value

10) Schneller Überblick über verwendete Begriffe und ihre Häufigkeit

Datensatz GBIF-Demo => Spalte "genus" => Facet => Text facet

kann kombiniert werden, etwa mit Facetten auf Spalten "stateProvince" und "year"

11) Nach Datumsbereich filtern

Datensatz GBIF-Demo => Spalte "eventDate" => Edit cells => Common transforms => To date

Spalte "eventDate" => Facet => Numeric Facet

Um datumslose Datensätze auszufiltern Spalte "eventDate" => Facet => Customized facets => Facet by blank => "false"

12) JSON parsing für OpenStreetMap-Reverse-Suche nach "Region" zur Validierung der GBIF-Demo Spalte "stateProvince"

::: info
Der Nominatim-Dienst von OpenStreetMap ist ein kostenloses Angebot, dass man nicht zu intensiv nutzen sollte, eventuell wird sonst die eigene IP-Adresse dafür gesperrt, s. https://operations.osmfoundation.org/policies/nominatim/ => Abstand zwischen Abfragen sollte mindestens 1 Sekunde betragen

::

Datensatz GBIF-Demo => Spalte "decimalLatitude" => Edit column => Add column by fetching URLs => New column name: "OSM", Throttle Delay: "1000", Expression:

"https://nominatim.openstreetmap.org/reverse.php?lat=" + value + "&lon=" + cells["decimalLongitude"].value + "&zoom=6&format=jsonv2"

Spalte "OSM" => Edit column => Add column based on this column => New column name: "Region", Expression:

parseJson(value).address.state

Nächster Beitrag