Daten sichten, bereinigen und integrieren mit OpenRefine

Das Importieren ist der erste Schritt beim Arbeiten mit OpenRefine. Die folgenden Formate werden für den Import in OpenRefine unterstützt: CSV, TSV, JSON, XML, Microsoft Excel-Tabellen (.xslx, .xls), HTML-Tabellen (.html), Google Spreadsheets (online).

Nachdem man eine Datenquelle geöffnet hat, beginnt man normalerweise sich mit diesem Datensatz näher auseinanderzusetzen. Man will also den Dateninhalt ‘sehen’ und auch die Datentypen der Spalten bestimmen. Datentypen sind wichtig, weil sie das Arbeiten mit den Daten erleichtern, indem sie einzigartige Operationen ermöglichen. Einige grundlegende Datentypen sind Text, Zahl, Datum/Zeit, Boolean (true/false) und Enumeration (rot, gelb, grün).

Facetten sind eine wichtige Funktion von OpenRefine, die über Tabellenkalkulationen und traditionelle Datenbankwerkzeuge hinausgeht. Sie zeigen die Datenvarianz in einer bestimmten Spalte an. Die Facettierung ermöglicht es uns, einen besseren Überblick über den gesamten Datensatz zu erhalten und die Daten aus einer grösseren Perspektive zu betrachten.

Eine Facette gruppiert Werte (z.B. vom Datentyp Text oder numerisch), die sich in einer Spalte befinden, und ermöglicht es dem Benutzer, die Werte in verschiedenen Zellen gleichzeitig zu filtern und zu bearbeiten, was vergleichbar mit einer Browsing-Funktion ist.

Normalerweise werden Facetten für eine bestimmte Spalte erstellt, indem Sie auf die Spalte klicken, die Option Facette auswählen und dann auf eine der Facettenalternativen klicken. Wenn Sie z.B. Text Facet auswählen, wird der gesamte Inhalt der Spaltenzellen verglichen, um allgemeine Informationen über die Werte dieser Spalte anzuzeigen. Sie können nun entweder selber einen neuen Wert für den Facetteneintrag eingeben oder die OpenRefine-Option Clustering verwenden, die sich automatisch um die Facette kümmert, indem sie ähnliche Einträge zusammenfasst.

Es kann auch nach einem bestimmten Wert im Datensatz gefiltert werden, indem Sie die Option Textfilter im Dropdown-Menü der Spalte wählen. Dadurch wird ein Textfeld erstellt, in das Sie den Text einfügen können, den Sie zum Filtern der Spaltenwerte benötigen.

Clustering ist eine weitere wichtige Funktion zur Gruppierung ähnlicher Daten, die Ihnen hilft, Dateninkonsistenzen und Rechtschreibfehler zu erkennen. Dies ist bei vielen Datensätzen üblich.

Das Clustering kann durch Auswahl einer Facette auf der Spalte, die Sie clustern möchten, und durch Auswahl der Methode, mit der Sie Ihre Spaltenwerte clustern möchten, erfolgen.

Wie im obigen Bild zu sehen ist, handelt es sich um ein Beispiel für die Stadt Zürich, bei dem der Name der Stadt absichtlich ein paar Mal falsch geschrieben wurde. Die Clustering-Funktion findet diese Rechtschreibfehler und bietet uns Optionen für die Behandlung dieser Arten von Rechtschreibfehlern.

OpenRefine bietet einige schnelle Transformationsoperationen, die beim Umgang mit Daten nützlich sind. Sie können entweder eine der voreingestellten Transformationen verwenden (sofort einsatzbereit) oder die GREL-Sprache verwenden, um Ihre eigene Transformationsfunktion zu implementieren (erfordert leichte Programmierkenntnisse; siehe das separate Kapitel unten).

Einige der String-Operationen umfassen:

Wenn man diese Operationen richtig einsetzt, kann man unordentliche Daten schnell in saubere und maschinenlesbare Informationen umwandeln.

Die Verwendung von String-Operationen in OpenRefine erfolgt durch Klicken auf die Option Edit Cells im Dropdown-Menü der Spalte. Bei der Verwendung von String-Operationen können Sie eine der voreingestellten Transformationen wählen, die von OpenRefine angeboten werden, oder eigene Transformationen mit der GREL-Sprache von OpenRefine schreiben, z.B. den Ausdruck value.toDate() (siehe Abschnitt unten).

Eine weitere wichtige Funktion von OpenRefine ist die Split-Funktion, die für die Aufteilung von Spaltenwerten in mehrere Spalten nützlich ist. Alles, was Sie tun müssen, ist, das Zeichen zwischen den Wörtern anzugeben, und dann teilt OpenRefine diese Zeichenfolgen für Sie in mehrere Spalten auf. Dies ist sehr häufig der Fall, wenn Sie mit unübersichtlichen Daten arbeiten, denn viele Daten in einer Spalte machen viel mehr Sinn, wenn sie in mehrere Spalten aufgeteilt werden.

Das Aufteilen von Feldern in OpenRefine erfolgt durch Klicken auf Edit Column und dann Split into several Columns…​. Hier können Sie ein oder mehrere Trennzeichen oder eine Feldlänge für die Trennung Ihrer Spaltenwerte angeben.

Eine weitere Option, die gewissermassen das Gegenteil der Aufteilung von Feldern ist, heisst Joining Fields und wird verwendet, wenn Felder mit einem Trennzeichen (oder ohne) verbunden werden. Das Wort ist ziemlich selbsterklärend.

Das Verbinden von Feldern in OpenRefine erfolgt durch Klicken auf Edit Column und dann Join Columns…​. Daraufhin werden Ihnen die zu verbindenden Spalten und das Trennzeichen zwischen den Inhalten der einzelnen Spalten zur Auswahl angeboten. Sie können die Ergebnisse in dieselbe Spalte schreiben oder spontan eine neue Spalte erstellen.

OpenRefine bietet auch Funktionen zur Validierung Ihrer Daten gegen einen anderen Datensatz. Dies kann durch die Erstellung einer Benutzerdefinierten Textfacette unter Verwendung der GREL-Funktion cell.cross() erfolgen, die Werte aus zwei verschiedenen Spalten vergleicht. Es gibt keinen nativen/geradlinigen Weg für die Validierung von Datensätzen, aber es kann durch die Verwendung von Funktionen erreicht werden, die sich auf die gleiche Weise wie die GREL-Funktion cell.cross() verhalten.

Um die Daten zu entduplizieren, kann man beispielsweise das Clustering nach Schlüsselkollisionen verwenden, um ähnliche Daten zu finden. Anschliessend kann man die doppelten Daten manuell bewerten und entscheiden, was mit ihnen geschehen soll, nachdem der Clustering-Prozess abgeschlossen ist. Dies ist in der Regel eine recht unkomplizierte und effiziente Methode zur Deduplizierung eines Datensatzes.

Für diese Aufgabe werden die Daten aus der Datei address_list_original.xlsx verwendet. Diese ist Teil des Zip-Archivs Daten_OpenRefine.zip, das Sie von dieser Seite herunterladen können. (Gleicher Abschnitt wie dieses Arbeitsblatt.)

Um ein Projekt (den eigentlichen Arbeitsbereich, in dem mit den Daten gearbeitet wird) zu erstellen, müssen Sie Folgendes tun:

Filtern Sie den Datensatz, um nur Kunden aus dem Kanton Zürich herauszufiltern und deren Adressen in einer neuen Spalte mit der vollständigen Adresse zusammenzuführen.

Nachdem Sie die oben genannten Aufgaben erledigt haben, können Sie Ihren Datensatz (Projekt) exportieren, indem Sie auf Export  Excel 2007+ klicken. OpenRefine exportiert dann Ihren aktuellen Datensatz in eine neue Excel-Tabelle.

Für diese Aufgabe werden die Daten aus den Files address_list_original.xlsx & address_list_scrambled.xlsx verwendet.

Wenn Sie die beiden Excel-Tabellen heruntergeladen haben, erstellen Sie das Projekt, indem Sie:

Der Zieldatensatz ist derselbe wie in der ersten Übung, und hier geht es darum, einen anderen Datensatz, d. h. den Quelldatensatz (mit ähnlichen Spaltennamen und -werten) in den Zieldatensatz zu integrieren, einschliesslich korrektem Mapping und erfolgreicher Datenintegration und Deduplizierung.

Nachdem Sie das Projekt erstellt haben, sehen Sie eine neue Spalte mit der Bezeichnung File, die der Datei entspricht, zu der der Datensatz gehört. Diese Spalte File wird auch als Key (Schlüssel)-Spalte bezeichnet, wenn zwei Datensätze zusammengeführt werden, da sie die Spalte ist, auf die sich unser Datensatz stützt, um zu unterscheiden, woher die Daten stammen (siehe auch den ersten Fall im Kapitel Datensätze aus verschiedenen Quellen zusammenführen weiter oben im Arbeitsblatt).

Der Datensatz zeigt eine Summe aller Zeilen aus beiden Dateien, die alle Spalten aus beiden Dateien enthalten. Nachdem die Datensätze aus der ersten Datei (Original-/Zieldatensatz) zu Ende sind, beginnt die Anzeige der Datensätze aus der zweiten Datei (verschlüsselter/Quellendatensatz). Sie werden auch feststellen, dass einige Spalten (absichtlich) denselben Namen haben und daher jeder Datensatz Daten zu diesen Spalten enthält.

Nachfolgend finden Sie einige Screenshots, die zeigen, wie die Daten aussehen und wie sich die Datensätze von der ersten zur zweiten Datei entwickeln.

Zunächst müssen Sie die Spaltennamen (aus beiden Dateien) überprüfen und herausfinden, welche von ihnen zueinander gehören. Dieser Teil muss manuell durchgeführt werden.

Wenn Sie herausgefunden haben, dass die beiden Spalten der gleichen Sache entsprechen, müssen Sie diese zusammenführen. Dadurch werden alle Werte aus den beiden Dateien in einer einzigen Spalte zusammengefasst. Achten Sie darauf, dass Sie den separator leer lassen, wenn Sie die Spalten verbinden.

Sie werden sehen, dass in den verbundenen Spalten die erste Spalte auch die Werte aus der zweiten Spalte übernimmt und Sie einen Wert für jede unserer Zeilen (aus beiden Dateien) haben werden.

Führen Sie nun das Gleiche für alle übrigen Spalten durch und Sie erhalten einen Datensatz mit konsistenten Spalten.

Wenn Sie alle richtigen Spalten verbunden haben, entfernen Sie die anderen Spalten aus der zweiten Datei. Auf diese Weise sollten Sie einen Datensatz erhalten, der alle Daten aus beiden Dateien enthält, jedoch mit einheitlichen und standardisierten Spaltennamen (einige Spalten haben in beiden Dateien denselben Namen haben).

Der nächste Schritt besteht darin, die Daten zu entduplizieren. Einige Daten aus der zweiten Datei sind auch in der ersten Datei vorhanden, was bedeutet, dass es sich um Duplikate handelt, die Sie entfernen und nur einen der Einträge behalten müssen. Es gibt auch andere Daten in der zweiten Datei, die in der ersten Datei nicht vorhanden sind (d.h. keine Duplikate), sodass Sie diese nicht entfernen müssen.

Damit ein Datensatz als doppelt gilt, muss die folgende Bedingung erfüllt sein: Wenn der Vorname, der Nachname und das Geburtsdatum identisch sind, handelt es sich um dieselbe Person, d.h. um ein Duplikat.

Dazu müssen Sie die Funktion Duplikate-Facette verwenden, die sich unter Facet  Customized facets  Duplicates facet befindet.

Diese Facette gibt false zurück, wenn der Wert kein Duplikat ist, und true, wenn der Wert ein Duplikat ist. In unserem Fall geht es darum, drei Facetten zu erstellen, die die Spalte Firstname, Lastname und Date_Birth auf Duplikate überprüfen.

Erstellen Sie drei Facetten für die drei Spalten und wählen Sie den Wert true für jede dieser Spalten. Dadurch werden die Spalten zurückgegeben, die nun in allen drei Werten der ausgewählten Spalten (unserem Ziel) doppelt vorhanden sind.

Nun bleiben alle doppelten Datensätze übrig, d.h. gleicher Vorname, gleicher Nachname und gleiches Geburtsdatum. Die nächste Aufgabe besteht darin, die Datensätze alphabetisch nach einer der drei Spalten zu sortieren (z.B. nach Firstname). Nachdem Sie die Spalte sortiert haben, wählen Sie oben in der Menuleiste Sort und dann Reorder rows permanently. Dies ist notwendig, um die nächste Funktion zu verwenden, die die doppelten Datensätze (ausser dem ersten) ausblendet.

Wenn Sie die Zeilen endgültig neu sortiert haben (ein notwendiger Schritt), markieren Sie die Spalte, die Sie sortiert haben, und klicken Sie auf Edit Cells  Blank down. Dadurch werden alle doppelten Datensätze (abgesehen von einem Eintrag) ausgeblendet, was genau das ist, was hier benötigt wird.

Jetzt müssen Sie die Zeilen nach Leerzeichen facettieren. Dies ist eine benutzerdefinierte OpenRefine-Facette, die Sie unter Facet  Customized Facets  Facet by blank (null or empty string) finden.

Wählen Sie für die Facette, die mit der Option Facet by blank erstellt wurde, die Option true aus, damit nur noch die doppelten Datensätze (unser Ziel) übrig bleiben. Klicken Sie nun auf die Spalte All und wählen Sie Edit Rows  Remove matching rows. Dadurch werden alle Zeilen der aktuellen Auswahl (die doppelten Datensätze) entfernt.

Entfernen Sie alle Facetten, indem Sie auf Remove all klicken, und Sie erhalten nur noch eindeutige Datensätze.

In der Spalte Phone (oder Phone_Number) gibt es einige Telefonnummern mit Landesvorwahl und einige ohne. Es gibt auch Leerzeichen bei einigen der Nummern und bei anderen nicht.

Schreiben Sie eine Texttransformationsfunktion mit GREL für die Phone-Spalte, die alle Spaltenwerte standardisiert. Sie können dies tun, indem Sie entweder den Ländercode zu allen hinzufügen (die ihn nicht haben) oder indem Sie ihn von denen entfernen, die ihn haben. Sie müssen auch Leerzeichen aus den Werten entfernen, die sie haben, oder Leerzeichen zu den Werten hinzufügen, die sie nicht haben, das bleibt Ihnen überlassen.

Nachdem diese letzte Aufgabe erledigt ist, sind die Quelldaten erfolgreich in den Zieldatensatz integriert, validiert und dedupliziert.

Exportieren Sie die Daten in das Format MS Excel 2007+ (.xslx), um diese Übung zu beenden.

Für diese Aufgabe werden die Daten aus dem File address_list_original.xlsx verwendet.

Wenn Sie den richtigen Datensatz haben, öffnen Sie das Programm OpenRefine und erstellen Sie das Projekt mit dem gerade heruntergeladenen Datensatz.

Die Nominatim-API-Abfragen, die Breiten- und Längengrade ermitteln, können direkt in OpenRefine durchgeführt werden. Die Werte müssen dann nur noch in die jeweils richtige Spalte eingefügt werden.

Sie müssen die Adressspalten zu einer einzigen Spalte Full_Address zusammenführen, um für den Aufruf der Geokodierung bereit zu sein. Dies kann mithilfe von Spaltenverknüpfungen und anderen Funktionen wie folgt geschehen:

Jetzt müssen Sie eine Geokodierungs-API mit OpenRefine aufrufen, die uns weitere Informationen über unsere Adresse, einschliesslich Lat/Lon-Attribute, liefert.

Nun, da Sie das JSON-Objekt mit Informationen über die Standorte der Kunden haben, können Sie dieses JSON-Objekt verwenden, um neue Spalten daraus zu erstellen, z.B. lat/lon.

Um das machen zu können werden folgende Schritte benötigt:

Jetzt haben Sie einen sauberen Datensatz mit lat- und lon-Koordinaten für die Kundenadressen, die mittels OpenRefine und Geokodierungs-APIs abgerufen wurden. Möglicherweise gibt es einige Orte, die von der API nicht gefunden wurden. In diesem Fall können Sie eine Facette verwenden, um die leeren Werte herauszufiltern, und dann diese Adressen manuell ändern, um herauszufinden, was mit ihnen nicht stimmt, und dann die API-Anfrage erneut stellen.

Es gibt mehrere Möglichkeiten, den HTML-Inhalt einer Webseite in OpenRefine einzubinden. Für diese Übung können Sie jedoch die Option Clipboard von OpenRefine verwenden. Kopieren Sie den Link der Webseite und fügen Sie ihn in den Textbereich der Zwischenablage auf der Startseite von OpenRefine ein.

Konfiguration der Parsing-Optionen: (auf “standard” belassen), Projekt bennenen und auf Create Project klicken.

Bis zu diesem Punkt existiert nur eine Spalte mit einer Zelle, in welcher sich der Link befindet.

Klicken Sie nun auf das Dropdown-Menü dieser Spalte und wählen Sie Edit Column  Add column by fetching URLs…​. Geben Sie der neuen Spalte einfach einen Namen, z.B. html oder html_content, lassen Sie den Ausdruck als value stehen und klicken Sie auf OK.

Nun wird eine neue Spalte erstellt, die den gesamten reinen HTML-Inhalt der ausgewählten Webseite enthält. Sie sollten diese Spalte verwenden, um die notwendigen Informationen zu extrahieren, die Sie für die Daten der Schlösser benötigen.

Nun werden die HTML-Parsing-Funktionen von GREL verwendet, um den HTML-Code zu analysieren und in die entsprechenden Spalten einzuordnen.

Hier bietet es sich an eine forEach iterative Schleife von GREL zu verwenden, um die entsprechenden Elemente zu durchlaufen und nur die Daten über die Schlösser zu extrahieren. In der Schleife werden die HTML-Parsing-Funktionen von GREL und grundlegende CSS-Selektoren-Logik verwendet, um die benötigten HTML-Elemente auszuwählen.

Wenn Sie alle notwendigen Informationen zu den Schlössern extrahiert haben, bereinigen Sie Ihren Datensatz, indem Sie die HTML-Inhaltsspalten und andere unnötige Daten/Spalten entfernen. Übrig bleibt eine Tabelle mit den Schlössern und Festungen der Schweiz, die Sie mit OpenRefine aus Wikipedia extrahiert haben.

Es gibt mehrere Möglichkeiten, HTML-Inhalte zu extrahieren, aber sobald Sie alle Informationen, die Sie benötigen, effektiv erhalten können, sind sie alle gültig.