Für das NDR-Projekt #WasAtmestDu (https://www.ndr.de/fernsehen/sendungen/45_min/Die-groesste-Luft-Messaktion-im-Norden,luft132.html) haben wir Menschen in Norddeutschland gebeten, Stickstoffoxid-Sammelröhrchen aufzuhängen. Insgesamt haben sich über 5000 Menschen über ein Online-Forumlar beworben.
In dem Formular wurde nach dem Messort gefragt. Allerdings: Manche Menschen haben dort eine Adresse eingegeben, andere Geo-Koordinaten. Das sah dann so aus (die Adressdaten wurden von mir für dieses Tutorial anonymisiert).
Die Beispiel-Datei lässt sich hier herunterladen: Download
Das Ziel: Eine Spalte, in der valide Geo-Informationen stehen. Deshalb müssen zunächst die Adressen von den Lat/Long-Daten getrennt werden. Das geht am besten mit OpenRefine (http://openrefine.org/). OpenRefine läuft lokal auf dem eigenen Rechner und nutzt den Browser als Interface.
Nachdem die Excel-Tabelle geladen wurde, kann man nun die Daten "putzen".
Dafür als erstes auf den kleinen Drop-Down-Knopf links neben der Spalte "Ort" klicken. Dann "Edit column" > "Add column based on this column..." auswählen. Wir erstellen also eine neue Spalte, die auf unserer Spalte "Ort" basiert.
Nun lassen sich Funktionen in der OpenRefine-Sprache GREL eingeben. Eine Übersicht gibt es unter anderem hier (https://github.com/OpenRefine/OpenRefine/wiki/Documentation-For-Users#reference) - allerdings ist die Übersetzung der Beispiele für die eigene Anwendung nicht immer einfach.
Zunächst muss man in dem Fenster der neuen Spalte einen Namen geben - in unserem Fall zum Beispiel "latlong".
Mit diesem GREL-Schnipsel lassen sich die Lat/Long-Daten in eine neue Spalte kopieren:
if(value.contains(/^(\d+(\.\d+)?),\s*(\d+(\.\d+))/),value,"")
Zur Info: Das nennt man eine Regular Expression oder RegEx. RegEx sind die Hölle, aber sehr mächtig. Es gibt mehrere Tools, mit denen man RegEx für seine Anwendung testen kann - das Beispiel für unsere Anwendung sieht man hier: https://regex101.com/r/Go3GUs/1
Nachdem wir "Ok" geklickt haben, erscheint eine neue Spalte.
Jetzt kann man das Ganze nochmal wiederholen - wir nennen die neue Spalte "adresse". Und wir vertauschen die letzten beiden Ausdrücke in der GREL-Funktion:
if(value.contains(/^(\d+(\.\d+)?),\s*(\d+(\.\d+))/),"",value)
Jetzt haben wir zwei saubere Spalten mit den Adressen und den Lat/Long-Angaben.
Im nächsten Schritt müssen wir die Adressen ebenfalls in Lat/Long-Informationen umwandeln. Der Vorgang nennt sich Geocoden. Es gibt unterschiedliche Dienste, die das anbieten, zum Beispiel Google Maps API, OpenStreetMap oder Mapbox.
Es ist relativ einfach, das Ganze mit der Google Maps API zu testen. Allerdings braucht man dafür mittlerweile einen API-Schlüssel. Wie man sich diesen - erstmal kostenlosen - Schlüssel beschafft, steht hier: https://developers.google.com/maps/documentation/javascript/get-api-key
Zurück in OpenRefine. Diesmal klickt man auf das Drop-Down neben "adresse" und wählt "Edit column" > "Add column by fetching URLs based on column adress".
OpenRefine ruft dann quasi für jede Zelle eine URL auf (in diesem Fall die des Geocoders) und speichert die Ausgabe.
Zunächst muss man wieder den Namen der neuen Spalte bestimmen. Throttle delay kann heruntergesetzt werden auf z.B. 10 Millisekunden. Alle anderen Felder können so bleiben.
Die GREL-Expression für den Google Geocoder sieht so aus:
'https://maps.googleapis.com/maps/api/geocode/json?' +
'sensor=false&' + 'address=' + escape(value, 'url') + '&key=HierStehtDeinGoogleMapsAPIKey'
Nun legt der Geocoder los, und ein paar Minuten später steht das Ergebnis in unserer neuen Spalte "googlemaps". Der Geocoder gibt dabei eine soegannte JSON-Struktur zurück, die OpenRefine speichert. Da sind die einzelnen Informationen verschachtelt enthalten:
Wir interessiern uns ja aber nur für die Lat/Long-Daten des Geocoders. Die können wir aus der JSON-Struktur extrahieren. Und zwar so:
Den kleinen Drop-Down-Knopf links neben der Spalte "googlemaps" klicken. Dann "Edit column" > "Add column based on this column..." auswählen.
Wir nennen die neue Spalte "lat" und nutzen diese GREL-Expression:
value.parseJson().results[0].geometry.location.lat
Das gleiche dann nochmal für die Spalte "long" mit dieser GREL-Expression:
value.parseJson().results[0].geometry.location.lng
Da in unserer Karte auch die Adressen der Messorte angezeigt werden sollten, brauchten wir für die Fälle, bei denen es nur Lat/Long-Informationen gab, ein reverse geocoding. Auch das lässt sich mit Open Refine relativ einfach lösen.
Diesmal klickt man auf das Drop-Down neben "latlong" und wählt "Edit column" > "Add column by fetching URLs based on column adress".
'https://maps.googleapis.com/maps/api/geocode/json?' +
'sensor=false&' + 'latlng=' + escape(value, 'url') + '&key=HierStehtDeinGoogleMapsAPIKey'
Die Ausgabe-Spalte kann man z.B. mit "reverse_adresse" benennen. Das Ergebnis ist wieder eine JSON-Rückmeldung vom Google Maps Geocoder.
Problem: Normalerweise würde man versuchen, mit dem ähnlichen Codeschnipsel wie bei den Adressen, die relevanten Informationen aus dem JSON zu ziehen.
Mit dem folgenden Code
value.parseJson().results[0].address_components[1].long_name
lässt sich zwar in der Regel die Straße bestimmen - sucht man aber die Stadt, wird es schwierig, da bei manchen Einträgen an der Stelle der Stadtteil und bei anderen die Stadt zu finden ist:
Dieses Problem lässt sich in OpenRefine afaik nicht einfach lösen, sondern man muss das ganze z.B. in Python verarbeiten.
Man kann höchstens alle adress_components in einzelne Spalten exrahieren und dann das Problem in Excel lösen. Also:
value.parseJson().results[0].address_components[0].long_name
value.parseJson().results[0].address_components[1].long_name
value.parseJson().results[0].address_components[2].long_name
value.parseJson().results[0].address_components[3].long_name
[usw.]
Außerdem kann man extrahieren, um welchen Typ es sich bei [2] und [3] handelt:
value.parseJson().results[0].address_components[2].types
value.parseJson().results[0].address_components[3].types
Das Ergebnis sieht dann so aus:
Was kann man draus lesen?
- Steht in Spalte H der Wert "["locality","political"]", befindet sich der Ortsname in Spalte M
- Steht in Spalte I der Wert "["locality","political"]", befindet sich der Ortsname in Spalte N
Mit einer Wenn/Dann-Formel in Excel lässt sich jetzt der Ort bestimmen:
=WENN(NICHT(ISTFEHLER(SUCHEN("locality";H2)));M2;N2)
Die Röhrchen wurden in mehreren Runden ausgewertet, das Labor hat uns mehrere Excel-Listen geschickt. Allerdings waren die IDs der Röhrchen teilweise unterschiedliche formatiert: Mal als Zahl, mal als Röhrchennummer (z.B. "DNDR0021").
Auch das lässt sich z.B. in Excel korrgieren:
Das kann man in einer einzigen Formel machen, aber damit man die bessere Übersicht behält, empfehle ich das Ganze in mehreren Schritten zu tun.
Mit der Formel
=TEXT(A2;"0000")
in Zelle B2 wird aus der Zahl ein vierstelliger Text (der so aussieht wie eine Zahl).
In C2 kann man den Text aus B2 nun mit dem Röhrchencode DNDR verketten:
=VERKETTEN("DNDR";B2)
Und schließlich kann man in D2 abfragen, ob man den Wert aus A2 oder C2 nimmt:
=WENN((ISTZAHL(A2));C2;A2)
Voilà:
to be continued