Datenvisualisierung zum Anfassen

Kapitel 4. Unordentliche Daten bereinigen

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

Meistens sind die Datensätze unübersichtlich und auf Anhieb schwer zu visualisieren. Sie enthalten fehlende Werte, Datumsangaben in verschiedenen Formaten, Text in rein numerischen Spalten, mehrere Elemente in denselben Spalten, verschiedene Schreibweisen desselben Namens und andere unerwartete Dinge. Siehe Abbildung 4-1 zur Inspiration. Wundere dich nicht, wenn du mehr Zeit damit verbringst, Daten zu bereinigen, als sie zu analysieren und zu visualisieren.

Abbildung 4-1. Meistens sehen die Rohdaten unordentlich aus.

In diesem Kapitel lernst du verschiedene Tools kennen, die dir bei der Entscheidung helfen, welches Tool du für eine effiziente Bereinigung deiner Daten verwenden solltest. Wir beginnen mit grundlegenden Bereinigungsmethoden mit Google Sheets in "Smart Cleanup with Google Sheets", "Find and Replace with Blank", "Transpose Rows and Columns", "Split Data into Separate Columns" und "Combine Data into One Column". Obwohl wir in unseren Beispielen Google Sheets verwenden, gelten viele dieser Prinzipien (und in einigen Fällen dieselben Formeln) auch für Microsoft Excel, LibreOffice Calc, Macs Numbers oder andere Tabellenkalkulationsprogramme. Als Nächstes lernst du, wie du Tabellendaten aus textbasierten PDF-Dokumenten mit Tabula extrahieren kannst, einem kostenlosen Tool, das von Datenjournalisten und Forschern auf der ganzen Welt genutzt wird, um Ausgabendaten, Gesundheitsberichte und alle möglichen anderen Datensätze zu analysieren, die sich in PDFs verfangen (siehe "Tabellen aus PDFs mit Tabula extrahieren"). Schließlich stellen wir dir in "Daten bereinigen mit OpenRefine" OpenRefine vor , ein leistungsstarkes und vielseitiges Tool, mit dem du auch die unordentlichsten Tabellenkalkulationen bereinigen kannst, z. B. solche, die Dutzende von verschiedenen Schreibweisen desselben Namens enthalten.

Smart Cleanup mit Google Sheets

Einer der neuesten Gründe, mit deinen Daten in Google Sheets zu arbeiten, ist die Funktion Smart Cleanup, die dabei hilft, ungenaue Daten zu erkennen und Korrekturen vorzuschlagen. Das Tool öffnet ein Menü in der Seitenleiste, das potenzielle Probleme aufzeigt, und du entscheidest, ob du den Vorschlag akzeptierst.

Erfahre, welche Arten von Problemen Smart Cleanup erkennt und welche es übersieht, indem du unsere Beispieldaten zu den 10 bevölkerungsreichsten Ländern der Welt verwendest, die einige Probleme enthalten, die wir absichtlich hinzugefügt haben:

1. Öffne die Smart Cleanup-Beispieldatei in Google Sheets, melde dich mit deinem Konto an und gehe auf Datei > "Eine Kopie erstellen", um eine Version zu erstellen, die du in deinem Google Drive bearbeiten kannst.

2. Gehe zu Daten > "Bereinigungsvorschläge" und sieh dir die Elemente an, die in der Seitenleiste erscheinen.

   

Die Funktion Smart Cleanup hat erfolgreich einen doppelten Eintrag (Zeile 12) und Leerzeichen in den Zellen A4 und A5 entdeckt. Klicke auf die grünen Schaltflächen "Entfernen" und "Alle trimmen", um zu bestätigen, dass Google Sheets sie bereinigen soll.

Kannst du diese anderen Fehler entdecken, die Smart Cleanup übersehen hat?

• In Zelle A10 ist Russsia mit einem zusätzlichen s falsch geschrieben.

• In Zelle C6 erscheint der Anteil Pakistans an der Weltbevölkerung in Dezimalform, nicht in Prozent.

• In Zelle D4 erscheint das US-Datum in einem Format, das sich von den anderen Einträgen unterscheidet. Wenn du dich mit den verschiedenen internationalen Datumsformaten auskennst, wirst du dich auch fragen, ob mit 12/10/2020 das Format MM/DD/YYYY gemeint ist, das in den USA üblicherweise verwendet wird, oder das Format DD/MM/YYYY, das in anderen Ländern üblich ist. Smart Cleanup kann diese Frage nicht für dich beantworten.

Die Funktion "Smart Cleanup" von Google Sheets ist ein guter Startpunkt. Wenn deine Daten wirklich chaotisch sind, musst du vielleicht auf anspruchsvollere Tools zurückgreifen, die weiter unten in diesem Kapitel beschrieben werden, z. B. "Daten bereinigen mit OpenRefine". Im nächsten Abschnitt lernst du eine weitere Bereinigungsmethode kennen, die in jeder Tabellenkalkulation funktioniert: Suchen und Ersetzen durch einen leeren Eintrag.

Suchen und Ersetzen mit Leerzeichen

Eines der einfachsten und leistungsfähigsten Bereinigungswerkzeuge in jeder Tabellenkalkulation ist der Befehl "Suchen und Ersetzen". Du kannst ihn auch verwenden, um verschiedene Schreibweisen desselben Namens zu ändern, z. B. um den Namen eines Landes zu verkürzen (von Republik Indien zu Indien), einen Namen zu erweitern (von USA zu Vereinigte Staaten) oder Namen zu übersetzen (von Italien zu Italien). Außerdem kannst du die Funktion Suchen und Ersetzen mit einem leeren Eintrag verwenden, um Maßeinheiten zu entfernen, die sich manchmal in denselben Zellen wie die Zahlen befinden (z. B. 321 kg in 321 ändern).

Schauen wir uns "Suchen und Ersetzen" in der Praxis an. Ein häufiges Problem bei US-Volkszählungsdaten ist, dass geografische Namen unnötige Wörter enthalten. Wenn du zum Beispiel Daten über die Bevölkerung der Städte in Connecticut herunterlädst, enthält die Ortsspalte nach jedem Namen das Wort "Stadt":

Hartford town
New Haven town
Stamford town

Normalerweise willst du eine saubere Liste von Städten haben, die du entweder in einem Diagramm anzeigen oder mit einem anderen Datensatz zusammenführen kannst, wie hier:

Hartford
New Haven
Stamford

Verwenden wir die Funktion "Suchen und Ersetzen" in einer US-Volkszählungsdatei, die wir unter heruntergeladen haben und die 169 Städtenamen in Connecticut und ihre Einwohnerzahl enthält, um die unerwünschte Bezeichnung "Stadt" nach jedem Ortsnamen zu entfernen:

1. Öffne die CT Town Geonames-Datei in Google Sheets, melde dich mit deinem Konto an und gehe auf Datei > "Kopie erstellen", um eine Version zu erstellen, die du in deinem Google Drive bearbeiten kannst.

2. Wähle die Spalte, die du ändern möchtest, indem du auf ihre Überschrift klickst. Wenn du keine Spalte auswählst, wirst du im gesamten Arbeitsblatt suchen und ersetzen.

3. Wähle im Menü Bearbeiten die Option "Suchen und ersetzen". Du wirst Folgendes sehen.

   

4. Gib in das Feld Suchen ein townein und achte darauf, dass du ein Leerzeichen vor dem Wort einfügst. Wenn du kein Leerzeichen einfügst, löschst du versehentlich die Stadt aus Orten wie Middletown. Außerdem erzeugst du versehentlich Leerzeichen am Ende einer Zeile, auf die keine weiteren Zeichen folgen, was in Zukunft zu Problemen führen kann.

5. Lass das Feld "Ersetzen durch" leer. Füge kein Leerzeichen ein. Lass es einfach leer.

6. Das Suchfeld sollte auf den Bereich eingestellt sein, den du in Schritt 2 ausgewählt hast, oder auf "Alle Blätter", wenn du nichts ausgewählt hast.

7. Du hast die Möglichkeit, "Groß- und Kleinschreibung abgleichen". Wenn diese Option aktiviert ist, werden town und Town und tOwN unterschiedlich behandelt. Für unsere Zwecke kannst du die Option "Groß-/Kleinschreibung abgleichen" deaktivieren.

8. Drücke auf "Alle ersetzen". Da diese Beispieldatei 169 Städte enthält, wird im Fenster angezeigt, dass 169 Instanzen von "Stadt" ersetzt worden sind.

9. Überprüfe das Ergebnis. Achte darauf, dass Orte, die eine Stadt in ihrem Namen haben, wie Middletown, unberührt bleiben.

Zeilen und Spalten vertauschen

Manchmal lädst du gute Daten herunter, aber dein Visualisierungstool verlangt von dir, dass du die Zeilen und Spalten transponierst oder vertauschst, um das gewünschte Diagramm oder die Karte zu erstellen. Dieses Problem tritt häufig auf, wenn du mit Zeitreihen oder historischen Daten arbeitest, weil sie in Tabellen und Diagrammen auf unterschiedliche Weise behandelt werden. Wenn du eine Tabelle entwirfst, besteht die richtige Methode darin, die Datumsangaben horizontal als Spaltenüberschriften zu platzieren, so dass wir sie von links nach rechts lesen, etwa so:¹

| Year    | 2000 | 2010 | 2020 |
|---------|------|------|------|
| Series1 |  333 |  444 |  555 |
| Series2 |  777 |  888 |  999 |

Beim Entwerfen eines Liniendiagramms in Google Sheets und ähnlichen Tools, die du in Kapitel 6 kennenlernen wirst, müssen wir die Daten so transponieren, dass die Daten in der ersten Spalte vertikal verlaufen, damit die Software sie als Beschriftungen für eine Datenreihe liest, so wie hier:

| Year | Series1 | Series2 |
|------|---------|---------|
| 2000 |     333 |     777 |
| 2010 |     444 |     888 |
| 2020 |     555 |     999 |

Lerne in unseren Beispieldaten, wie du Zeilen und Spalten transponieren kannst:

1. Öffne die Transpose-Beispieldatendatei in Google Sheets, melde dich mit deinem Konto an und gehe auf Datei > "Kopie erstellen", um eine Version zu erstellen, die du in deinem Google Drive bearbeiten kannst.

2. Markiere alle Zeilen und Spalten, die du transponieren möchtest, und gehe auf Bearbeiten > Kopieren.

3. Scrolle weiter nach unten in der Tabelle und klicke auf eine Zelle oder öffne eine neue Registerkarte der Tabelle und gehe auf Bearbeiten > "Spezial einfügen" > "Transponiert einfügen".

   

Nachdem du nun weißt, wie du Daten durch das Transponieren von Zeilen und Spalten bereinigen kannst, erfährst du im nächsten Abschnitt, wie du Daten in einzelne Spalten aufteilen kannst.

Daten in getrennte Spalten aufteilen

Manchmal erscheinen mehrere Daten in einer einzigen Zelle, z. B. Vor- und Nachname (John Doe), geografische Koordinaten (40.12,-72.12) oder Adressen (300 Summit St, Hartford, CT, 06106). Für deine Analyse möchtest du sie vielleicht in separate Einheiten aufteilen, so dass deine FullName-Spalte (mit John Doe ) zu den Spalten Vorname (John) und Nachname (Doe) wird, die Koordinaten zu den Spalten Breitengrad und Längengrad und deine FullAddress-Spalte zu vier Spalten: Straße, Ort, Bundesland und Postleitzahl.

Beispiel 1: Einfaches Splitting

Beginnen wir mit einem einfachen Beispiel, in dem wir Paare von geografischen Koordinaten, die durch Kommas getrennt sind, in separate Spalten aufteilen:

1. Öffne die Beispieldaten für geteilte Koordinatenpaare in Google Sheets, melde dich mit deinem Konto an und gehe auf Datei > "Kopie erstellen", um eine Version zu erstellen, die du in deinem Google Drive bearbeiten kannst.

2. Wähle die Daten aus, die du teilen möchtest, entweder die gesamte Spalte oder nur einige Zeilen. Beachte, dass du immer nur Daten aus einer Spalte aufteilen kannst.

3. Vergewissere dich, dass sich in der Spalte rechts neben der Spalte, die du aufteilst, keine Daten befinden, da alle Daten dort überschrieben werden.

4. Gehe zu Daten und wähle "Text in Spalten aufteilen".

   

5. Google Sheets wird automatisch versuchen, dein Trennzeichen zu erraten. Du wirst sehen, dass deine Koordinaten jetzt durch ein Komma getrennt sind und das Trennzeichen in der Dropdown-Liste auf "Automatisch erkennen" eingestellt ist. Du kannst es manuell in ein Komma (,), ein Semikolon (;), einen Punkt (.), ein Leerzeichen oder ein anderes benutzerdefiniertes Zeichen (oder sogar eine Folge von Zeichen, die wir in Beispiel 2 dieses Abschnitts besprechen werden) ändern.

6. Du kannst die neuen Spalten in Längengrad (erste Zahl) und Breitengrad (zweite Zahl) umbenennen.

Beispiel 2: Komplexes Splitting

Schauen wir uns nun ein etwas komplizierteres Beispiel an. Jede Zelle enthält eine vollständige Adresse, die du in vier Spalten aufteilen möchtest: Straße, Ort, Bundesland und Postleitzahl. Beachte aber, dass die Trennzeichen unterschiedlich sind: ein Komma zwischen Straße und Ort, ein Leerzeichen zwischen Ort und Bundesland und zwei Bindestriche zwischen Bundesland und Postleitzahl. In diesem Fall musst du manuell einige Anweisungen hinzufügen, um den Text richtig in vier Spalten aufzuteilen:

| Location                          |
| --------------------------------- |
| 300 Summit St, Hartford CT--06106 |
| 1012 Broad St, Hartford CT--06106 |
| 37 Alden St, Hartford CT--06114   |

1. Öffne die Beispieldatei "Geteilte komplexe Adresse" in Google Sheets, melde dich in deinem Konto an und gehe auf Datei > "Kopie erstellen", um eine Version in deinem Google Drive zu speichern, die du bearbeiten kannst.

2. Wähle die Spalte aus und gehe auf Daten > "Text in Spalten aufteilen", um die Aufteilung von links nach rechts zu starten.

3. Google Sheets teilt deine Zelle automatisch in zwei Teile auf, 300 Summit St und Hartford CT--06106, wobei ein Komma als Trennzeichen verwendet wird. (Falls nicht, wähle einfach Komma aus dem angezeigten Dropdown-Menü).

4. Wähle nun nur die zweite Spalte aus und führe erneut "Text in Spalten aufteilen" aus. Google Sheets trennt automatisch die Stadt vom Bundesland und der Postleitzahl, weil es automatisch ein Leerzeichen als Trennzeichen ausgewählt hat. (Wenn das nicht der Fall ist, wähle Leerzeichen aus dem Dropdown-Menü).

5. Schließlich wählst du nur die dritte Spalte aus und führst erneut "Text in Spalten aufteilen" aus. Google Sheets erkennt die beiden Bindestriche nicht als Trennzeichen, also musst du manuell " Benutzerdefiniert" auswählen, die beiden Bindestriche (--) in das Feld " Benutzerdefiniertes Trennzeichen" eingeben und die Eingabetaste drücken. Jetzt hast du die vollständige Adresse erfolgreich in vier Spalten aufgeteilt.

   

Tipp

Google Sheets behandelt Postleitzahlen als Zahlen und löscht führende Nullen (aus 06106 wird also 6106). Um das zu ändern, wähle die Spalte aus und gehe zu Format > Zahl > "Klartext". Jetzt kannst du die Nullen manuell wieder einfügen. Wenn dein Datensatz sehr groß ist, kannst du die Nullen auch mit der im folgenden Abschnitt vorgestellten Formel hinzufügen.

Daten in einer Spalte zusammenfassen

Lass uns den umgekehrten Vorgang durchführen, indem wir die Daten in einer Spalte mit einer Tabellenkalkulationsformel, auch Verkettung genannt, mit dem kaufmännischen Und-Symbol (&) kombinieren. Stell dir vor, du erhältst Adressdaten in vier separaten Spalten: Straße, Ort, Bundesland und Postleitzahl:

| Street        | City       | State  | Zip   |
| ------------- | ---------- | ------ | ----- |
| 300 Summit St | Hartford   | CT     | 06106 |

Aber stell dir vor, du musst die Adressen mit einem Tool wie dem, das wir in "Adressen in Google Sheets geokodieren" vorgestellt haben , geokodieren , wofür alle Daten in einer Spalte wie dieser zusammengefasst werden müssen:

| Location                          |
| --------------------------------- |
| 300 Summit St, Hartford, CT 06106 |

In jeder Tabellenkalkulation kannst du eine einfache Formel schreiben, um Begriffe mit dem kaufmännischen Und-Symbol (&) zu kombinieren (oder zu verketten). Du kannst auch Trennzeichen in deine Formel einfügen, z. B. ein Leerzeichen in Anführungszeichen (" "), ein Leerzeichen mit Komma (", ") oder eine beliebige Kombination von Zeichen. Probieren wir es mit einigen Beispieldaten aus:

1. Öffne die Beispieldaten "Getrennte Spalten kombinieren" in Google Sheets, melde dich mit deinem Konto an und gehe auf Datei > "Kopie erstellen", um eine Version zu erstellen, die du in deinem Google Drive bearbeiten kannst. Das Blatt enthält Adressen, die in vier Spalten unterteilt sind: Straße, Stadt, Bundesland und Postleitzahl.

2. Gib in Spalte E eine neue Überschrift namens Ort ein.

3. In Zelle E2 gibst du ein =A2 & ", " & B2 & ", " & C2 & " " & D2. In dieser Formel werden die vier Elemente mit Ampersands kombiniert und durch Anführungszeichen und Leerzeichen voneinander getrennt. Drücke dann die Eingabetaste.

4. Klicke auf Zelle E2 und ziehe das Fadenkreuz in der rechten unteren Ecke nach unten, um den Rest der Spalte auszufüllen.

   

Jetzt, wo du die Begriffe erfolgreich in einer Ortsspalte zusammengefasst hast, kannst du das Geocoding by SmartMonkey Google Sheets Add-on verwenden, das wir in "Adressen in Google Sheets geocodieren" beschrieben haben, um die Längen- und Breitengradkoordinaten zu finden und deine Daten zu kartieren, wie wir in Kapitel 7 besprechen werden.

Als weitere Lektüre empfehlen wir Lisa Charlotte Rosts brillanten Datawrapper-Blogbeitrag über das Bereinigen und Aufbereiten von Tabellenkalkulationsdaten für Analysen und Visualisierungen.²

Tabellenkalkulationen sind großartige Werkzeuge, um Daten zu finden und zu ersetzen, Daten in einzelne Spalten aufzuteilen oder Daten in einer Spalte zu kombinieren. Aber was ist, wenn deine Datentabelle in einer PDF-Datei gefangen ist? Im nächsten Abschnitt stellen wir Tabula vor und zeigen dir, wie du Tabellen aus textbasierten PDF-Dokumenten in Tabellen umwandelst, die du in Tabellenkalkulationen analysieren kannst.

Tabellen aus PDFs mit Tabula extrahieren

Es kann vorkommen, dass der Datensatz, an dem du interessiert bist, nur als PDF-Dokument verfügbar ist. Nicht verzweifeln - du kannst Tabula benutzen, um Tabellen zu extrahieren und sie als CSV-Dateien zu speichern. Denke daran, dass es zwei Arten von PDFs gibt: textbasierte und bildbasierte. Wenn du mit dem Cursor Text markieren, kopieren und einfügen kannst, ist die PDF-Datei textbasiert und du kannst sie mit Tabula bearbeiten. Um ein bildbasiertes PDF in ein textbasiertes PDF umzuwandeln, musst du eine OCR-Software (Optical Character Recognition) wie Adobe Acrobat Pro oder ein anderes OCR-Tool verwenden. Außerdem kann Tabula nur Daten aus Tabellen extrahieren, nicht aus Diagrammen oder anderen Arten von Visualisierungen.

Tabula ist ein kostenloses Tool, das auf Java in deinem Browser läuft und für Mac-, Windows- und Linux-Computer verfügbar ist. Es läuft auf deinem lokalen Rechner und sendet deine Daten nicht in die Cloud, sodass du es auch für sensible Dokumente nutzen kannst.

Um loszulegen, lade die neueste Version von Tabula herunter. Du kannst die Download-Schaltflächen auf der linken Seite verwenden oder nach unten zum Abschnitt "Tabula herunterladen und installieren" scrollen, um eine Kopie für deine Plattform herunterzuladen. Im Gegensatz zu den meisten anderen Programmen muss Tabula nicht installiert werden. Entpacke einfach das heruntergeladene Archiv und doppelklicke auf das Symbol.

Warnung

Auf einem Mac siehst du möglicherweise diese Warnung, wenn du Tabula zum ersten Mal startest: "Tabula ist eine App, die aus dem Internet heruntergeladen wurde. Bist du sicher, dass du sie öffnen willst?" Wenn ja, klicke auf Öffnen.

Wenn du die Tabula startest, öffnet sie deinen Standardbrowser als localhost mit einer URL, die http://127.0.0.1/ ähnelt, mit oder ohne zusätzliche Portnummer, wie :8080, wie in Abbildung 4-2 gezeigt. Tabula läuft auf deinem lokalen Computer, nicht im Internet. Wenn dein Standardbrowser (z. B. Safari oder Edge) nicht mit Tabula zusammenarbeitet, kannst du die URL kopieren und in einen anderen Browser (z. B. Firefox oder Chrome) einfügen.

Abbildung 4-2. Die Tabula-Begrüßungsseite.

Jetzt laden wir ein textbasiertes PDF-Beispiel hoch und erkennen alle Tabellen, die wir extrahieren möchten. Zu Beginn der COVID-19-Pandemie hat die Gesundheitsbehörde von Connecticut die Daten über Fälle und Todesfälle nur im PDF-Format veröffentlicht. Für diese Demonstration kannst du unser textbasiertes PDF-Beispiel vom 31. Mai 2020 verwenden oder dein eigenes zur Verfügung stellen:

1. Wähle die PDF-Datei aus, aus der du Daten extrahieren möchtest, indem du auf die blaue Schaltfläche Durchsuchen... klickst.

2. Klicke auf Importieren. Tabula wird mit der Analyse der Datei beginnen.

3. Sobald Tabula das Laden der PDF-Datei beendet hat, siehst du einen PDF-Viewer mit einzelnen Seiten. Die Oberfläche ist recht übersichtlich und hat nur vier Schaltflächen in der Kopfzeile.

4. Klicke auf Autodetect Tables, damit Tabula nach relevanten Daten sucht. Das Tool hebt jede Tabelle, die es findet, in rot hervor.

   

Jetzt wollen wir unsere ausgewählten Tabellen manuell anpassen und die Daten exportieren:

5. Klicke auf die grüne Schaltfläche "Vorschau & Extrahierte Daten exportieren", um zu sehen, wie die Daten nach Meinung von Tabula exportiert werden sollten.

6. Wenn die Vorschautabellen nicht die gewünschten Daten enthalten, versuche in der linken Seitenleiste zwischen den Extraktionsmethoden Stream und Lattice zu wechseln.

7. Wenn die Tabellen immer noch nicht richtig aussehen oder du einige Tabellen entfernen möchtest, die Tabula automatisch erkannt hat, klicke auf "Auswahl überarbeiten". So kommst du zurück zum PDF-Viewer.

8. Jetzt kannst du alle Markierungen aufheben und die gewünschten Tabellen manuell auswählen. Verwende Drag-and-Drop-Bewegungen, um Tabellen (oder Teile von Tabellen) auszuwählen, die dich interessieren.

9. Wenn du die Auswahl auf einige oder alle Seiten "kopieren" möchtest, kannst du das Dropdown-Menü "Diese Auswahl wiederholen" verwenden, das in der unteren rechten Ecke deiner Auswahlen erscheint, um die Änderungen zu übertragen. Das ist besonders nützlich, wenn dein PDF aus vielen ähnlich formatierten Seiten besteht.

10. Wenn du mit dem Ergebnis zufrieden bist, kannst du es exportieren. Wenn du nur eine Tabelle hast, empfehlen wir, CSV als Exportformat zu verwenden. Wenn du mehr als eine Tabelle hast, solltest du das Exportformat im Dropdown-Menü auf "zip of CSVs" umstellen. Auf diese Weise wird jede Tabelle als eigene Datei gespeichert und nicht alle Tabellen in einer CSV-Datei.

Nachdem du deine Daten auf deinen Computer exportiert hast, navigiere zu der Datei und öffne sie mit einem Tabellenkalkulationsprogramm, um sie zu analysieren und zu visualisieren.

Wenn du jetzt eine Tabelle aus einem PDF-Dokument extrahiert hast, kann das Ergebnis unübersichtlich sein. Im nächsten Abschnitt werden wir unordentliche Datensätze mit einem sehr leistungsstarken Tool namens OpenRefine bereinigen.

Daten bereinigen mit OpenRefine

Öffne den Beispieldatensatz "US Foreign Aid" im Google Sheets-Format als , wie in Abbildung 4-3 gezeigt. Kannst du irgendwelche Probleme darin erkennen? Dieser Datenauszug stammt aus dem Datensatz "US Overseas Loans and Grants (Greenbook)", der die wirtschaftliche und militärische Hilfe der USA für verschiedene Länder darstellt. Wir haben uns entschieden, nur die Hilfe für Südkorea und Nordkorea für die Jahre zwischen 2000 und 2018 zu berücksichtigen. Zur Veranschaulichung haben wir absichtlich Rechtschreib- und Formatierungsfehler hinzugefügt, aber die Werte nicht verändert.

Beachte, dass es in der Spalte Land verschiedene Schreibweisen von Nord- und Südkorea gibt. Beachte auch, dass die Spalte " FundingAmount" nicht einheitlich ist. Bei einigen Beträgen werden Tausender durch Kommas getrennt, bei anderen durch Leerzeichen. Einige Beträge beginnen mit einem Dollarzeichen, andere nicht. Datensätze wie dieser können bei der Analyse ein absoluter Albtraum sein. Zum Glück bietet OpenRefine leistungsstarke Tools, um Daten zu bereinigen und zu standardisieren.

Abbildung 4-3. Kannst du bei diesen Beispieldaten irgendwelche Probleme erkennen?

OpenRefine einrichten

Benutze OpenRefine, um diese unordentlichen Daten zu bereinigen. Lade OpenRefine für Windows, Mac oder Linux herunter. Genau wie Tabula läuft es in deinem Browser und keine Daten verlassen deinen lokalen Rechner, was gut für die Vertraulichkeit ist.

Um OpenRefine unter Windows zu starten, entpacke die heruntergeladene Datei, doppelklicke auf die .exe-Datei und das Tool sollte sich in deinem Standardbrowser öffnen.

Um OpenRefine auf einem Mac zu starten, doppelklicke auf die heruntergeladene .dmg-Datei, um es zu installieren. Wahrscheinlich wirst du eine Sicherheitswarnung sehen, die verhindert, dass OpenRefine automatisch gestartet wird, weil Apple den Entwickler dieses Open-Source-Projekts nicht kennt. Um das Problem zu beheben, gehe zu Systemeinstellungen > "Sicherheit & Datenschutz" > Registerkarte Allgemein und klicke in der unteren Hälfte des Fensters auf Trotzdem öffnen, wie in Abbildung 4-4 gezeigt. Wenn die Eingabeaufforderung mit einem anderen Fenster erscheint, klicke auf Öffnen.

Abbildung 4-4. Wenn dein Mac eine Warnung zum Starten von OpenRefine anzeigt, passe deine "Sicherheit & Datenschutz"-Einstellungen an, um das Programm zu öffnen.

Wenn du OpenRefine startest, öffnet es deinen Standardbrowser mit der Adresse localhost 127.0.0.1, mit oder ohne die zusätzliche Portnummer :3333, wie in Abbildung 4-5 gezeigt. Wenn dein Standardbrowser (z. B. Safari) nicht mit OpenRefine zusammenarbeitet, kopiere die localhost-Adresse und füge sie in einen anderen Browser (z. B. Firefox oder Chrome) ein.

Abbildung 4-5. Die OpenRefine Willkommensseite.

Daten laden und ein neues Projekt starten

Um einen unordentlichen Datensatz zu bereinigen, müssen wir ihn in ein neues Projekt laden. Mit OpenRefine kannst du einen Datensatz von deinem lokalen Rechner oder einer entfernten Webadresse (z. B. einem Google Sheet) hochladen. OpenRefine kann auch Daten direkt aus SQL-Datenbanken extrahieren, aber das würde den Rahmen dieses Buches sprengen:

1. Öffne den US-Auslandshilfe-Beispieldatensatz in Google Sheets, melde dich mit deinem Konto an und gehe auf Datei > Herunterladen, um eine Version im CSV-Format auf deinem Computer zu speichern.

2. Klicke in OpenRefine unter "Daten abrufen von: diesem Computer" auf Durchsuchen... und wähle die CSV-Datei aus, die du heruntergeladen hast. Klicke auf Weiter.

3. Bevor du mit dem Bereinigen der Daten beginnen kannst, kannst du mit OpenRefine sicherstellen, dass die Daten richtig geparst werden. In unserem Fall bedeutet das Parsen die Art und Weise, wie die Daten in Spalten aufgeteilt werden. Vergewissere dich, dass OpenRefine den richtigen Spalten Werte zugewiesen hat, oder ändere die Einstellung im Block "Daten parsen als" unten auf der Seite, bis es sinnvoll aussieht. Klicke dann auf Projekt erstellen in der oberen rechten Ecke.

   

Wenn du die Daten erfolgreich in ein neues Projekt eingelesen hast, geht es jetzt an den spaßigen Teil: Text in Zahlen umwandeln, überflüssige Zeichen entfernen und die Schreibweisen für Nord- und Südkorea korrigieren.

Dollarbeträge von Text in Zahlen umwandeln

Sobald dein Projekt erstellt ist, siehst du die ersten 10 Zeilen des Datensatzes. Du kannst sie auf 5, 10, 25 oder 50 ändern, indem du auf die entsprechende Zahl in der Kopfzeile klickst.

Jede Spaltenüberschrift hat ihr eigenes Menü, das du durch Klicken auf die Pfeil-nach-unten-Schaltfläche auswählen kannst. Linksbündige Zahlen in einer Spalte werden wahrscheinlich als Text dargestellt, wie in unserem Fall die Spalte FundingAmount, und müssen in ein numerisches Format umgewandelt werden:

1. Um Text in Zahlen umzuwandeln, wähle das Menü der Spalte FundingAmount und gehe zu "Zellen bearbeiten" > "Allgemeine Transformationen" > "In Zahl".

   

   Du wirst sehen, dass einige Zahlen grün und rechtsbündig geworden sind, was einen Teilerfolg signalisiert, aber die meisten haben sich nicht verändert. Das liegt daran, dass Dollarzeichen ($) und Kommas (,) OpenRefine verwirren und verhindern, dass die Werte in Zahlen umgewandelt werden.

2. Entfernen wir $ und , aus der Spalte " FundingAmount". Wähle im Spaltenmenü dieses Mal "Zellen bearbeiten" > Transformieren..., denn wir müssen die gewünschte Änderung manuell eingeben. Gib im Ausdrucksfenster Folgendes ein value.replace(',', '') ein und beobachte, wie die Kommas im Vorschaufenster verschwinden. Wenn du sicher bist, dass deine Formel keine Syntaxfehler enthält, klicke auf OK.

   

3. Nun wiederholst du den vorherigen Schritt, aber statt eines Kommas entfernst du das Zeichen $, indem du einen anderen Ausdruck eingibst: value.replace('$', ''), bestätige die Formel und klicke auf OK.

4. In den Schritten 2 und 3 haben wir Text- (oder String-) Werte durch andere Textwerte ersetzt, so dass OpenRefine denkt, dass diese Spalte nicht mehr numerisch ist. Daher sind alle Werte wieder linksbündig und in Schwarz dargestellt. Führe Schritt 1 erneut aus. Diesmal sind fast alle Zellen grün, was bedeutet, dass sie erfolgreich in numerische Werte umgewandelt wurden. Es verbleiben jedoch einige nicht numerische schwarze Zellen.

5. Um die verbleibenden nicht-numerischen schwarzen Zellen zu korrigieren, müssen wir Leerzeichen und ein a Zeichen am Ende einer Zahl entfernen. Korrigiere diese manuell, indem du mit dem Mauszeiger über eine Zelle fährst, auf Bearbeiten klickst und in dem neuen Pop-up-Fenster "Datentyp" in "Zahl" änderst und auf Übernehmen drückst.

   

Jetzt sollten alle Finanzierungsbeträge saubere Zahlen sein, rechtsbündig und in grüner Farbe. Jetzt können wir zur Spalte " Land" übergehen und die verschiedenen Schreibweisen von "Korea" korrigieren.

Cluster Ähnliche Schreibweisen

Wenn du verschiedene Datenquellen kombinierst oder Umfragedaten verarbeitest, bei denen die Befragten ihre Antworten aufgeschrieben haben, anstatt sie aus einem Dropdown-Menü auszuwählen, kann es passieren, dass du mehrere Schreibweisen desselben Wortes erhältst (Ortsname, Bildungsgrad - was immer du willst!). Eine der leistungsfähigsten Funktionen von OpenRefine ist die Möglichkeit, ähnliche Antworten zu gruppieren.

Wenn du unsere ursprüngliche Beispieldatei verwendest, sieh dir die Spalte Land und alle Schreibweisen von Nord- und Südkorea an. Wähle im Dropdown-Menü der Spalte Land die Option Facette > "Textfacette". Daraufhin öffnet sich auf der linken Seite ein Fenster mit allen Schreibweisen (und Anzahlen) der Spaltenwerte - 26 Auswahlmöglichkeiten für eine Spalte, die eigentlich nur zwei verschiedene Werte haben sollte: Nordkorea und Südkorea!

1. Um mit der Vereinheitlichung der Schreibweisen zu beginnen, klicke auf die Pfeil-nach-unten-Schaltfläche in der Spaltenüberschrift Land und wähle "Zellen bearbeiten" > "Clustern und bearbeiten...". Es öffnet sich ein Fenster wie dieses.

   

   Du hast die Wahl zwischen zwei Clustermethoden: Key Collision oder Nearest Neighbour. Das Key Collision Clustering ist eine viel schnellere Technik, die sich für größere Datensätze eignet, aber weniger flexibel ist. Nearest Neighbour ist eine rechenintensivere Methode, die bei größeren Datenmengen langsam ist, aber eine bessere Feinabstimmung und Präzision ermöglicht. Beide Methoden können durch verschiedene Funktionen unterstützt werden, die du auf der Wiki-Seite des Projekts nachlesen kannst. Für diese Übung belassen wir es bei der Standard-Schlüsselkollisionsmethode mit Fingerprint-Funktion.

   OpenRefine berechnet eine Liste von Clustern. Die Spalte Werte in Cluster enthält gruppierte Schreibweisen, die OpenRefine als gleich ansieht.

2. Wenn du mit einer Gruppierung einverstanden bist, aktiviere das Kästchen "Zusammenführen?" und gib den wahren Wert in das Eingabefeld "Neuer Zellenwert" ein, wie im ersten Cluster in Schritt 1 gezeigt. In unserem Beispiel wäre dies entweder North Korea oder South Korea.

3. Du kannst alle Gruppierungen durchgehen oder nach ein oder zwei Gruppierungen aufhören und auf die Schaltfläche "Ausgewählte zusammenführen & neu gruppieren" klicken. Die von dir ausgewählten Cluster werden zusammengeführt und die Gruppierung wird neu berechnet. (Keine Sorge, das Fenster verschwindet nicht.) Gruppiere so lange, bis du mit dem Ergebnis zufrieden bist.

   Nimm dir etwas Zeit, um mit den Parametern der Keying-Funktion zu spielen, und beobachte, wie sie Cluster unterschiedlicher Größe und Genauigkeit erzeugen.

4. Wenn du mit dem Bereinigen und Clustern der Daten fertig bist, speicherst du den bereinigten Datensatz, indem du in der oberen rechten Ecke des OpenRefine-Fensters auf Export klickst. Du kannst dein Format wählen (wir empfehlen CSV). Jetzt hast du einen sauberen Datensatz, der analysiert und visualisiert werden kann.

Zusammenfassung

In diesem Kapitel haben wir uns angesehen, wie man Tabellen in Google Sheets bereinigt, tabellarische Daten in PDFs mit Tabula befreit und OpenRefine verwendet, um sehr unordentliche Datensätze zu bereinigen. Du wirst oft mehrere dieser Tools für denselben Datensatz verwenden, bevor er für deine Analyse gut genug ist. Wir ermutigen dich, in deiner Freizeit weitere Formeln in Google Sheets zu lernen und zusätzliche Funktionen von OpenRefine zu erkunden. Je mehr Bereinigungstools und -techniken du kennst, desto fähiger und anpassungsfähiger wirst du, um auch komplexere Fälle zu lösen.

Du weißt jetzt, wie du deine Daten bereinigst, also lass uns mit dem nächsten Schritt fortfahren, bevor du sie visualisierst. Im folgenden Kapitel werden wir darüber sprechen, warum du Daten normalisieren und eine präzise Sprache verwenden solltest, um aussagekräftige Vergleiche anzustellen.

¹ Stephen Few, Show Me the Numbers: Designing Tables and Graphs to Enlighten, 2. Auflage (Burlingame, CA: Analytics Press, 2012), S. 166.

² Lisa Charlotte Rost, "How to Prepare Your Data for Analysis and Charting in Excel & Google Sheets", Datawrapper (Blog), Zugriff am 28. August 2020, https://oreil.ly/emSQz.