Spaß mit Daten: Fahrradzählung in Bochum

Das Open Data-Portal der Stadt Bochum bietet die Ergebnisse von Radverkehrszählungen zum Download an, die an der Fahrradzählstelle Herner Straße / Poststraße / Vierhausstraße im Jahr 2017 durchgeführt wurden.

Problem: Die Ergebnistabellen werden pro Fahrrichtung (Richtung Herne sowie Richtung Innenstadt) monatsweise veröffentlicht, so dass man zunächst 24 Tabellen verbinden muss. Diese Schritte werde ich hier darstellen.

Ich beginne mit der Fahrrichtung Innenstadt und kopiere zunächst die URLs aller zwölf Tabellen in eine Variable; das geht leider nur per Handarbeit:

fahrrad2017_Innenstadt <- c("https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_12_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_11_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_10_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_09_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_08_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_07_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_06_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_05_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_04_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_03_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_02_Radverkehr_HernerStr_FR_Bochum.csv","https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_01_Radverkehr_HernerStr_FR_Bochum.csv")

Anschließend wird der Reihe nach per for-Loop jede Tabelle heruntergeladen und in eine Variable eingelesen bzw. an diese drangehängt, so dass am Ende die Tabelle df_Innenstadt die Zählungen des gesamten Jahres für diese Fahrrichtung enthält:

df_Innenstadt <- data.frame(matrix(ncol = 3,nrow = 0))
for (i in c(1:12)) {
  download.file(url = fahrrad2017_Innenstadt[i], destfile = as.character(i))
  a <- read.csv(file = as.character(i), skip = 4, sep = ";", col.names = c("Datum","Zeit","Anzahl.Innenstadt"))
  df_Innenstadt <- rbind(df_Innenstadt, a)
}
head(df_Innenstadt)
##        Datum     Zeit Anzahl.Innenstadt
## 1 01.12.2017 00:01:00                 0
## 2 01.12.2017 00:02:00                 0
## 3 01.12.2017 00:03:00                 0
## 4 01.12.2017 00:04:00                 0
## 5 01.12.2017 00:05:00                 0
## 6 01.12.2017 00:06:00                 0

Diese Schritte werden mit den Tabellen für die Fahrrichtung Herne wiederholt:

fahrrad2017_Herne <- c(
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_12_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_11_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_10_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_09_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_08_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_07_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_06_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_05_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_04_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_03_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_02_Radverkehr_HernerStr_FR_Herne.csv",
  "https://www.bochum.de/C12571A3001D56CE/vwContentByKey/W2AVQCSN710BOCMDE/$FILE/2017_01_Radverkehr_HernerStr_FR_Herne.csv")

df_Herne <- data.frame(matrix(ncol = 3,nrow = 0))
for (i in c(1:12)) {
  download.file(url = fahrrad2017_Herne[i], destfile = as.character(i))
  a <- read.csv(file = as.character(i), skip = 4, sep = ";", col.names = c("Datum","Zeit","Anzahl.Herne"))
  df_Herne <- rbind(df_Herne, a)
}

Jetzt möchte ich gerne beide Tabellen (also Fahrtrichtungen) verbinden. Ihrer Struktur nach sollten beide für jede Minute jeden Tages des gesamten Jahres eine Messung enthalten, also die gleiche Länge aufweisen. Sind sie aber nicht, was zur Folge hat, dass ich sie nicht einfach per cbind verbinden kann (was aber wahrscheinlich aus vielen Gründen riskant)! Mit den möglichen Gründen halte ich mich nicht weiter auf, sondern entscheide mich dafür, sie per merge so zu verbinden, dass nur Zeitpunkte (Datum und Uhrzeit), die in beiden Tabellen vorkommen, in der neuen Tabelle enthalten sind. Dafür muss ich diesen Zeitstempel aber zunächst schaffen – heißt: Die Einträge der Spalten Datum und Zeit in eine neue Spalte namens ID zu schreiben:

df_Herne <- within(df_Herne, { ID = paste(Datum, Zeit) })
df_Innenstadt <- within(df_Innenstadt, { ID = paste(Datum, Zeit) })
head(df_Herne)
##        Datum     Zeit Anzahl.Herne                  ID
## 1 01.12.2017 00:01:00            0 01.12.2017 00:01:00
## 2 01.12.2017 00:02:00            0 01.12.2017 00:02:00
## 3 01.12.2017 00:03:00            0 01.12.2017 00:03:00
## 4 01.12.2017 00:04:00            0 01.12.2017 00:04:00
## 5 01.12.2017 00:05:00            0 01.12.2017 00:05:00
## 6 01.12.2017 00:06:00            0 01.12.2017 00:06:00
head(df_Innenstadt)
##        Datum     Zeit Anzahl.Innenstadt                  ID
## 1 01.12.2017 00:01:00                 0 01.12.2017 00:01:00
## 2 01.12.2017 00:02:00                 0 01.12.2017 00:02:00
## 3 01.12.2017 00:03:00                 0 01.12.2017 00:03:00
## 4 01.12.2017 00:04:00                 0 01.12.2017 00:04:00
## 5 01.12.2017 00:05:00                 0 01.12.2017 00:05:00
## 6 01.12.2017 00:06:00                 0 01.12.2017 00:06:00

Jetzt haben beide Tabellen jeweils eine (so hoffe ich) eindeutige ID und ich erzeuge die neue Tabelle Fahrrad_2017, die für jeden Zeitstempel die Zählergebnisse beider Fahrrichtungen enthält. Zusätzlich lese ich Datum und Uhrzeit per strptime korrekt (also gwm. maschinenlesbar) in die neue Spalte Zeitpunkt ein:

Fahrrad_2017 <- merge(df_Herne, df_Innenstadt, by="ID")
Fahrrad_2017 <- within(Fahrrad_2017, { Zeitpunkt = strptime(paste(Datum.x, Zeit.x), "%d.%m.%Y%H:%M:%S") })
head(Fahrrad_2017)
##                    ID    Datum.x   Zeit.x Anzahl.Herne    Datum.y   Zeit.y
## 1 01.01.2017 00:01:00 01.01.2017 00:01:00            0 01.01.2017 00:01:00
## 2 01.01.2017 00:02:00 01.01.2017 00:02:00            0 01.01.2017 00:02:00
## 3 01.01.2017 00:03:00 01.01.2017 00:03:00            0 01.01.2017 00:03:00
## 4 01.01.2017 00:04:00 01.01.2017 00:04:00            0 01.01.2017 00:04:00
## 5 01.01.2017 00:05:00 01.01.2017 00:05:00            0 01.01.2017 00:05:00
## 6 01.01.2017 00:06:00 01.01.2017 00:06:00            0 01.01.2017 00:06:00
##   Anzahl.Innenstadt           Zeitpunkt
## 1                 0 2017-01-01 00:01:00
## 2                 0 2017-01-01 00:02:00
## 3                 0 2017-01-01 00:03:00
## 4                 0 2017-01-01 00:04:00
## 5                 0 2017-01-01 00:05:00
## 6                 0 2017-01-01 00:06:00

Die Tabelle enthält jetzt gleich mehrfach Datum und Uhrzeit. Per select-Befehl aus dem dplyr-Package werden nur die drei Spalten ausgewählt, die ich wirklich benötige, nämlich Zeitpunkt, Anzahl in Richtung Herne und Anzahl in Richtung Innenstadt:

library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
Fahrrad_2017 <- Fahrrad_2017 %>% select(Anzahl.Herne, Anzahl.Innenstadt, Zeitpunkt)
head(Fahrrad_2017)
##   Anzahl.Herne Anzahl.Innenstadt           Zeitpunkt
## 1            0                 0 2017-01-01 00:01:00
## 2            0                 0 2017-01-01 00:02:00
## 3            0                 0 2017-01-01 00:03:00
## 4            0                 0 2017-01-01 00:04:00
## 5            0                 0 2017-01-01 00:05:00
## 6            0                 0 2017-01-01 00:06:00

Jetzt habe ich alle Ergebnisse der Fahrradzählung 2017 in einer Tabelle mit 523.419 Zeilen. Die Ergebnistabelle kann hier (ZIP, 2,6 MB) heruntergeladen werden.

In der Fortsetzung will ich mich endlich mit der Auswertung und Visualisierung der Daten beschäftigen. Dabei werden dann auch die Zählergebnisse aus dem Vorjahr 2016 berücksichtigt.

Spaß mit R und Berlin Open Data II: ESF-Förderungen

Ein neuer Datensatz im Open Data-Portal Berlins: ESF-geförderte Vorhaben des Landes Berlin 2014-2020

Aus der Beschreibung:

In der Förderperiode 2014-2020 stehen der deutschen Hauptstadt Berlin rund 215 Mio. EUR aus dem Europäischen Sozialfonds (ESF) zur Verfügung, die in die hier lebenden Menschen investiert werden können.
Schwerpunkte sind dabei die soziale Eingliederung benachteiligter Gruppen, die bessere Qualifizierung für einen Arbeitsplatz, die Bekämpfung der Jugendarbeitslosigkeit sowie die Optimierung der allgemeinen und beruflichen Bildung.

Zunächst muss der Datensatz korrekt eingelesen werden – das brauchte bei mir mehrere Versuche:

esf <- read.csv("esf.csv", sep = ";", fileEncoding = "UTF-8", dec = ",")

Da die CSV-Datei nicht mit Kommata sondern Semikola als separierenden Zeichen angelegt wurde, muss R das mittels des Attributs sep mitgeteilt werden. Damit Sonderzeichen korrekt dargestellt werden, wird außerdem auf das fileEncoding UTF-8 hingewiesen. Zu guter Letzt muss R auch wissen, dass die „Nachkommastellen“ nicht hinter einem Punkt (amerikanische Schreibweise), sondern eben hinter einem Komma stehen. Nur so werden die Euro-Beträge auch als Zahlen (class = numeric) und nicht als hier eher unbrauchbare Faktoren erkannt.

Ein Blick in die Struktur dieses Data Frames ergibt, dass die Variablen territoriale_umsetzungsmechanismen und finanzierungsform jeweils nur eine Ausprägung haben, nämlich „07 : Nicht zutreffend“ und „Nicht rückzahlbare Finanzhilfe“. Die Variable land besteht ebenso und naheliegenderweise nur aus der einen Ausprägung DE300. Auf diese Spalten kann ich also verzichten, so dass ich sie mittels der select-Funktion aus dem dplyr-Package „lösche“:

esf <- select(esf, -c(11,13,15))

Die Tabelle weist 641 Zuwendungen an 182 verschiedene Empfänger aus. Interessant wäre es also zu wissen, wieviel die verschiedenen Empfänger insgesamt aus dem ESF erhalten haben:

summen <- tapply(esf$gesamtbetrag_förderfähiger_ausgaben, esf$name_des_begünstigten, sum)

Diese neue Tabelle summen sortiere ich absteigend:

summen <- sort(summen, decreasing = TRUE)

Und lasse mir dann die Top10 der 182 Empfänger ausgeben:

head(summen,10)

            Arbeit und Bildung e.V.         Stiftung Naturschutz Berlin                  WeTeK Berlin gGmbH 
                            9085780                             5667636                             4695658 
                   Chance BJS gGmbH Beuth Hochschule für Technik Berlin                           BUS gGmbH 
                            3349125                             3086155                             2958709 
               Personal_inform GmbH                     LKJ Berlin e.V.       ComFort-Schulungszentrum GmbH 
                            2882466                             2777685                             2776943 
  FrauenComputerZentrumBerlin e. V. 
                            2697057 

Das sind aber nur die Summen, die direkt aus dem ESF geflossen sind. Für jede ESF-Förderung muss – sofern ich die Tabelle wirklich richtig verstehe – die öffentliche Hand einen Teil beisteuern. Darüber gibt die Variable kofinanzierungssatz_in_prozent Auskunft:

summary(esf$kofinanzierungssatz_in_prozent)
Min. 1st Qu.  Median    Mean 3rd Qu.    Max.
15.04   49.92   49.92   49.60   50.00   66.67 

Wir sehen: Der Kofinanzierungssatz bewegt sich zwischen 15,04 und 66,67 Prozent. In Mittel und Median liegt er bei knapp unter 50 Prozent. Dieser Prozentsatz wird auf die ESF-Fördersumme noch aufgeschlagen.

Mich stört die Variable Themenfeld, denn dort besteht jede Ausprägung aus einer ID-Nummer gefolgt von einer Beschreibung, bspw. 117 - Förderung des gleichen Zugangs zum lebenslangen Lernen für alle Altersgruppen im formalen, nichtformalen und informellen... Dadurch steht immer unerhört viel Text in den Zellen.

Also verwende ich die separate-Funktion aus dem tidyr-Package, um daraus zwei Spalten zu machen – das schreibe ich zur Sicherheit in eine neue Tabelle esf_sep:

esf_sep <- separate(esf, themenfeld, c("themenfeld", "beschreibung themenfeld"), " - ")

Und um ein paar weitere der für mich noch recht mysteriösen Funktionen aus dem dplyr-Package auszuprobieren, wende ich die group_by-Funktion auf die Empfänger an:

empf <- group_by(esf_sep, name_des_begünstigten)

Jetzt sollte ich mittels summarize, einigen Argumenten und ein wenig Magie schöne Übersichtstabellen erstellen können:

summarize(empf, Gesamtsumme = sum(gesamtbetrag_förderfähiger_ausgaben), 'Anzahl der Förderungen' = length(gesamtbetrag_förderfähiger_ausgaben))

# A tibble: 182 x 3
   name_des_begünstigten                                               Gesamtsumme `Anzahl der Förderungen`
                                                                                            
 1 A&QUA gGmbH                                                            1796959.                       23
 2 ABBV GmbH                                                               959359.                        8
 3 ABT gGmbH - Akademie für berufliches Training                          1712346.                       17
 4 abw - gemeinnützige Gesellschaft für Arbeit, Bildung und Wohnen mbH     370433.                        5
 5 agens Arbeitsmarktservice gGmbH                                         509435.                        9
 6 AGRARBÖRSE Deutschland Ost e. V.                                        542946.                        6
 7 ajb GmbH Gemeinnützige Gesellschaft für Jugendberatung                  692342.                        2
 8 Akelei e. V.                                                            418609.                        2
 9 all2gethernow e.V.                                                      321707.                        1
10 Allianz deutscher Designer AGD e.V.                                     113616.                        1
# ... with 172 more rows

Das ließe sich in ähnlicher Weise auf Postleitzahlen, Themenfelder oder sogar Zeiträume anwenden. Dafür müsste ich mich aber erst wieder mit den schrecklichen Datumsfunktionen vertraut machen.

Spaß mit R und Berlin Open Data I: Die Zuwendungsdatenbank

Zur Zeit beschäftige ich mich mit der statistischen Programmiersprache R, und weil das Üben mit echten Datensätzen mehr Spaß macht, habe ich mir auf OpenData Berlin einen interessanten Datensatz herausgesucht – die Zuwendungsdatenbank.

In der Zuwendungsdatenbank werden die von den Behörden ausgereichten Zuwendungen an juristische Personen veröffentlicht (ab einem Zuwendungsbetrag i. H. v. 100 Euro rückwirkend für die letzten 5 Jahre), aktuell die Zuwendungen des Jahres 2016.

Der Datensatz ist schön groß: 40.820 Zeilen, also Zuwendungen mit zehn (eigentlich neun – id ist nur eine fortlaufende Nummer) Variablen:
[1] "id" "name" "geber" "art" "jahr" "anschrift"
[7] "politikbereich" "zweck" "betrag" "empfaengerid"

Zunächst werfe ich einen Blick in die Spalte betrag:
summary(all$betrag)
Min. 1st Qu. Median Mean 3rd Qu. Max.
100 4673 16477 229216 61176 487261162

Der Mindestbetrag liegt bei 100 €, das wusste ich schon aus der Beschreibung des Datensatzes. Die Höchstzuwendung liegt bei stattlichen 487.261.162 €. Wer hat die erhalten und wofür?
> all[all$betrag == max(all$betrag),]

id name geber art jahr
9445 18889 BVG Senatsverwaltung für Stadtentwicklung und Umwelt Projektförderung 2013
anschrift politikbereich zweck betrag empfaengerid
9445 Holzmarktstraße 15-17, 10179 Berlin Verkehr U 8; Nord 487261162 hra_031152

Die BVG erhielt den Betrag für den Zweck „U 8; Nord“ in 2013.

Wieviel wurde insgesamt zugewendet?
> sum(as.numeric(all$betrag))
[1] 9356595690

Der Datensatz bildet also 9.356.595.690 € Gesamtzuwendungen in den Jahren 2012 bis 2016 ab.

Damit es auch mal etwas komplizierter wird, habe ich ein kleines Skript geschrieben, welches für jeden in der Spalte name vorkommenden Empfänger die Häufigkeit zählt, mit der er Zuwendungen erhalten hat. Das wird am Schluss per summary ausgegeben:

x <- unique(all$name)
vec <- c()
for (i in 1:length(x)) { vec <- c(vec,nrow(all[all$name == x[i],])) }
summary(vec)

Min. 1st Qu. Median Mean 3rd Qu. Max.
1.0 1.0 2.0 4.4 4.0 667.0

Die Mindestzahl an Zuwendungen liegt erwartbar bei 1, das Maximum hingegen bei imposanten 667. Inzwischen überrascht es niemanden mehr: Empfängerin war auch hier die BVG.

Fortsetzung folgt