Nerds

Technische Details zur Justizgelder-Datenbank

von Stefan Wehrmeyer

In unserer Justizgelder-Datenbank kann jeder schauen, an welche Einrichtungen die deutsche Justiz 170 Millionen Euro verteilt hat. In diesem Blogpost wollen wir den Code hinter dem Projekt erklären. Wer an der Technik hinter der Web-Anwendung interessiert ist, kann hier in die Tiefe gehen. Zudem gibt es einen Link zum kompletten Quelltext auf Github.

Uns ist es bei CORRECTIV wichtig, dass mit der Veröffentlichung einer Geschichte die Entwicklung der dazu gehörenden Datenbank nicht abgeschlossen sein muss. Deswegen machen wir unsere Arbeit transparent, veröffentlichen in der Regel die Quellcodes unserer Datenbanken und geben möglichst alle Daten frei. Wir sind gespannt, was in den Daten steckt, das wir nicht gesehen haben.

## Ablauf des Projektes

Zunächst mussten wir die Daten für das Projekt „Spendengerichte“ besorgen. Unser Reporter Jonathan Sachse hat hier letzte Woche beschrieben, auf welche Hürden er in der Justiz gestoßen ist. Nachdem wir die Dateien aus den verschiedenen Bundesländern besorgt hatten, haben wir sie nach Bundesland, Jahr und Behörde aufgeteilt in Ordner abgelegt.

Das war die Ausgangssituation für die eigentliche Programmierung:

Die Dateien liegen im PDF-, Excel- und Word-Format vor. Unser Ziel ist es, aus diesem Ausgangsmaterial eine einzige Tabelle mit allen Zahlungen zu erstellen. Bei der Durchsicht der Quelldateien stellte sich leider schnell heraus, dass es kein einheitliches Datenschema gibt. Auch wenn einige Dateien viel mehr Informationen beinhalten, in wirklich allen Dateien waren letztlich nur der Name der Einrichtung und der zugewiesene Betrag enthalten.

## Konvertierung nach CSV

Um alle Daten zusammenzuführen bietet es sich an, zuerst alle Dateien in das selbe Format zu konvertieren. Das CSV-Format (Comma Separated Values) ist ideal für die maschinelle Weiterverarbeitung: keine Formatierungen, keine versteckten oder verbundenen Spalten – eine einzige Tabelle mit klar definiertem Kopf. Unsere Tabelle muss mindestens die Spalte „name“ und die Spalte „betrag“ haben. Weitere Spalten sind optional, sollten jedoch die gleiche Bezeichnung haben, wenn sie das gleiche beinhalten.

Die eigentliche Konvertierung ist viel Handarbeit. Während aus Excel-Dokumenten meist recht einfach passende CSV-Dateien exportiert werden können, müssen PDFs gegebenenfalls vorher mit OCR behandelt werden, um die Schrift maschinenlesbar zu machen. Im Anschluss werden sie dann durch Tabellenerkennungs-Werkzeuge wie Tabula geschickt.

Die entstehenden CSV-Dateien stellen die Datenbasis dar. Hat die Behandlung der Dateien mit OCR Fehler produziert oder finden sich andere Probleme in den Daten, werden diese Fehler an dieser Stelle korrigiert. Das ist viel Handarbeit.

## Zusammenführung und automatisierte Reinigung

Leider ist die Datenbasis nach der Konvertierung immer noch sehr dreckig: die Namensspalte enthält oft noch eine durchlaufende Nummer, die Adresse der Organisation oder weitere Informationen. Auch die Betragsspalte besteht aus verschiedenen Kombinationen aus Ziffern, Punktierungen, Leerzeichen und Variationen der Währung (€, EUR, Euro,…).

Ein Python-Script (eine Anwendung, geschrieben in der Programmiersprache Python) liest alle CSV-Dateien ein, bildet eine Gesamtmenge an Spaltennamen, reinigt bekannte Spalten nach bestimmten Mustern (reguläre Ausdrücke und schlimmer) und schreibt dann alle Daten in eine große CSV-Datei.

## Deduplizierung mit Open Refine

Open Refine hilft bei der Säuberung von tabellarischen Datensätzen. Die gröbsten Unreinheiten wurden schon mit dem Python-Skript entfernt, daher brauchen wir Refine nur noch für die Deduplizierung. Damit wollen wir vermeiden, dass der selbe Verein mehrere Male mit unterschiedlichen Beträgen in unseren Tabellen auftaucht – stattdessen soll am Ende ein Gesamtbetrag pro Verein stehen. Dazu nutzen wir den „Cluster & Edit“-Modus auf der Namensspalte.

Bei Open Refine gibt es mehrere Möglichkeiten, um Einträge zusammenzuführen. Eine der genauesten Funktionen nennt sich „nearest neighbor“. Bei „nearest neighbor“ vergleicht Open Refine jeden Eintrag mit jedem anderen Eintrag und schaut, wie ähnlich die Einträge sich sind. Bei mehr als 44.000 Zeilen dauert das selbst extrem lange. Es empfiehlt sich daher eher, eine von mehreren möglichen „key collision“-Methoden zu wählen. Bei der „key collision“ bildet das Programm aus jeder Zeile ein bestimmtes Muster, es schaut sich zum Beispiel jeden dritten Buchstaben an und legt die Einträge zusammen, bei denen dieses Muster übereinstimmt. Bei Open Refine kann man verschiedene dieser „key collision“-Funktionen durchprobieren. Wir haben mit den verschiedenen Parametern herumgespielt und so schnell viele gute Duplikat-Treffer erzeugt, die sich verbinden lassen.

Was dann am Ende tatsächlich Duplikate sind (und nicht nur ähnlich gemusterte Einträge), ist natürlich Definitionsfrage. Vereine an unterschiedlichen Registern können den gleichen Namen haben, sind aber nicht der gleiche Verein. Regionale Ableger einer Einrichtung sind auch nicht identisch zu ihrem gleichnamigen Bundesverband. Die Keying-Funktion „metaphone3“ zum Beispiel fasst alle Einrichtungen zusammen, die mit dem Wort „Förderverein“ beginnen. Das produziert natürlich einen Haufen falscher Ergebnisse. Besser ist dagegen die Fingerprint-Funktion in Open Refine, damit werden viele Tipp- und OCR-Fehler erkannt und auf die richtige Schreibweise normalisiert.

Die Deduplizierung beinhaltet viele menschliche Entscheidungen. Diese lassen sich aus Refine im Undo/Redo-Menü als Kommandos im JSON-Format extrahieren und in die Datenverarbeitungs-Pipeline einbinden. So bleibt stets überprüf- und reproduzierbar, wie wir an den Daten gearbeitet haben. Und wir können die einmal getroffenen Entscheidungen auch für neue Daten oder spätere Updates weiter nutzen.

## Pipeline

Die Idee hinter einer Daten-Pipeline ist, dass die Daten von der Quelle über automatisierte Verarbeitungsschritte in die Datenbank fließen, so dass bei Fehlerbehebungen an der Quelle die Daten nicht händisch integriert werden müssen. So eine Pipeline haben wir für dieses Projekte aufgebaut: die einzelnen CSV-Dateien, werden zu einer CSV-Datei zusammengefasst und gereinigt. Abgespeicherten Refine-Befehle werden auf die Gesamt-CSV-Datei angewendet und erst dann wird das Ergebnis in die finale Datenbank geladen.

## Die Web-Anwendung

Ziel des Projekts ist es, interessierten Bürgern Zugriff auf unsere Zuwendungsdatenbank zu geben. Ein Web-Interface mit Such- und Filterfunktion und eigenen Seiten für jede Einrichtung ist die naheliegende Lösung.

Die Web-Anwendung ist ein Django-Projekt. Die Daten werden zwar in einer herkömmlichen Datenbank gehalten, aber für die Suche noch einmal in ElasticSearch indiziert. Die Anwendung nutzt den offiziellen ElasticSearch Python Client und einen ElasticSearch-Query mit verschachtelten Aggregationen, Filter-Queries und Sortierung.

Der komplette Quelltext der Web-Anwendung findet sich auf dem CORRECTIV-GitHub-Account.