Angenommen Sie haben einen Cousin in Spanien mit dem Sie regen Kontakt haben und den Sie auch immer wieder besuchen.
Sie kommen bei einem Besuch in Spanien auf die aktuelle Lage in seinem Heimatland zu sprechen. Ihr Cousin berichtet über die sehr hohe Arbeitslosigkeit, insbesondere Jugendarbeitslosigkeit in seinem Land. Er behauptet, dass Sie dies nicht nachvollziehen könnten, da es in Deutschland praktisch keine Arbeitslosigkeit gibt. Daheim angekommen schauen Sie sich die Daten zur Arbeitslosigkeit im Euro-Raum an (was Sie auch im 2. - 4. RTutor Problem Set machen) und sehen in der Tat, dass Deutschland eine der niedrigsten Arbeitslosenquoten aller Euro-Länder hat und Spanien deutlich höhere Arbeitslosenquoten aufweist. Doch hat ihr Cousin recht, wenn er davon spricht, dass Deutschland keine Arbeitslosigkeit kennt? Gilt dies für alle Regionen in Deutschland, oder gibt es auch in Deutschland Regionen mit hohen Arbeitslosenquoten? Wenn Sie regionale Unterschiede finden, welche Gründe könnte dies haben?
Dem wollen wir in dieser Case-Study auf den Grund gehen.
Diese Case-Study besteht aus mehreren Teilen und wird Sie durch die komplette Vorlesung als konkretes Anschauungsobjekt begleiten. Hierbei dient die Case-Study hauptsächlich dazu, ihnen an einem konkreten und umfangreichen Beispiel die Kenntnisse für eine erfolgreiche Projektarbeit zu vermitteln und diese Kenntnisse zu vertiefen. Natürlich können Sie die Case-Study auch als Referenz heranziehen, wenn Sie ihre eigene Projektarbeit anfertigen.
Wir wollen uns in dieser Case-Study mit der Pro-Kopf Verschuldung, der Arbeitslosigkeit und dem BIP in einzelnen Regionen in Deutschland beschäftigen und hier mögliche regionale Unterschiede aufdecken.
Im Ersten Schritt ist es wichtig sich zu überlegen, woher Sie ihre Datensätze beziehen. Um makroökonomische Informationen zum BIP oder der Arbeitslosigkeit zu erhalten empfiehlt es sich immer auf die Seiten des Statistischen Bundesamtes oder der Bundesagentur für Arbeit zu schauen. Hier finden Sie z.B. Quartalsinformationen zu BIP und Arbeitslosigkeit für ganz Deutschland.
In dieser Case-Study wollen wir jedoch etwas feingranularere Informationen sammeln, und zwar auf Landkreis-, Verwaltungs-, bzw. Gemeindeebene.
Uns interessieren die Pro-Kopf Verschulung, Arbeitslosigkeit und das BIP.
Bevor wir mit der Analyse starten sollten wir einige Pakete in R
laden, welche wir später verwenden möchten, da sie uns bei der Analyse
unterstützen können. Dies geschieht mit dem library()
Befehl.
(Alternative: Vor jeden Befehl das dazugehörige
Paket schreiben, d.h. statt read_xlsx könnten wir auch
readxl::read_xlsx schreiben. Jedoch wollen wir im
Projektkurs immer die Variante mit library()
verwenden.)
library(readxl)
library(tidyverse)
library(skimr)Mit den Befehlen aus dem readxl und readr
Paketen könnten Sie direkt URLs einlesen, wenn sich dahinter Text,- bzw.
Excel Datei verbergen, was bei uns der Fall ist. Allerdings sollten wir
davon nur selten Gebrauch machen, denn es könnte immer sein das die
Daten im Internet modifiziert oder unter der vorherigen URL nicht mehr
auffindbar sind. Daher wollen wir die gewünschten Daten, welche wir zur
Analyse benötigen, immer in einem Unterordner data
abspeichern und dann aus diesem Ordner einlesen. So stellen wir sicher,
dass wir immer auf die Daten zurückgreifen können, auch wenn diese aus
dem Netz gelöscht oder modifiziert werden.
Daten können innerhalb von R mit dem Befehl
download.file() heruntergeladen werden:
# Zuerst sollten Sie prüfen ob der Unterordner "data" bereits bei ihnen exisitert, und falls er nicht existiert sollten Sie diesen erstellen.
# Dies können Sie beispielsweise mit dem folgenden Befehl machen, wenn der Ordner schon existiert wird eine Warnmeldung ausgegeben:
dir.create(file.path(".", "data"))
# Durch die if-Bedingung prüfen Sie, ob die Datei bereits im "data"-Ordner vorhanden ist
# Verschuldung (gibt es leider nur aus dem Jahr 2017 und keine neueren!)
if (!file.exists("./data/Schulden_2017.xlsx")){
download.file("https://www.statistikportal.de/sites/default/files/2018-11/Schulden_2018.xlsx", "./data/Schulden_2017.xlsx")
}
# Arbeitslose aus dem Jahr 2017
if (!file.exists("./data/Arbeitslose_2017.xlsx.zip")){
download.file("https://statistik.arbeitsagentur.de/Statistikdaten/Detail/201712/iiia4/gem-jz/gem-jz-dlk-0-201712-zip.zip", "./data/Arbeitslose_2017.xlsx.zip")
}
# Link für die Daten von September 2022: https://statistik.arbeitsagentur.de/Statistikdaten/Detail/Aktuell/iiia4/gemeinde-arbeitslose-quoten/arbeitslose-quoten-dlk-0-zip.zip
# BIP pro Gemeinde aus dem Jahr 2021
if (!file.exists("./data/BIP_2021.xlsx")){
download.file("https://www.statistikportal.de/sites/default/files/2022-07/vgrdl_r2b1_bs2021.xlsx", "./data/BIP_2021.xlsx")
}Die Daten zur Verschuldung wollen wir absichtlich nicht unter “Schulden_2018.xlsx” abspeichern, sondern unter “Schulden_2017.xlsx”, da die Tabelle zwar in 2018 generiert wurde, sich aber auf das Jahr 2017 bezieht.
Die if-Bedingung prüft ob der angegebene Datensatz
bereits in unserem “data” Ordner enthalten ist. Wenn dies der Fall ist,
so werden sie nicht mehr erneut heruntergeladen.
Wir haben hier auch die Daten für die Arbeitslosenquote aus dem jahr 2017 heruntergeladen, da wir die passenden Informationen zur Pro-Kopf Verschuldung der Gemeinden aus nur aus dem Jahr 2017 online erhalten. Ich habe ihnen die Links zum neueren Datensätzen für die Arbeitslosigkeit in den R Chunk geschrieben. Für das BIP haben wir die neueste Datenreihe aus dem Jahr 2021 bezogen da wir hier Paneldaten haben und keine Querschnittsdaten (was dies genau heißt wird im Laufe der Case-Study erläutert).
Im nächsten Schritt sollten wir die Daten in R einlesen. Beim Download haben wir schon an den URLs und auch an den heruntergeladenen Dateien gesehen, dass es sich bei zwei Downloads um ZIP-Archive (BIP und Anzahl an Arbeitslosen) handelt. Diese ZIP-Archive können wir mittels R extrahieren, diese anschließend einlesen und die extrahierte Datei wieder löschen. Dies hat den Vorteil, dass Sie ihre Dateien platzsparend auf ihrer Festplatte abspeichern und nur bei Bedarf entsprechend entpacken.
Im ersten Schritt wollen wir uns mit den Daten zu den Arbeitslosen beschäftigen und die in dem ZIP-Archiv enthaltenen Dateien in R einlesen.
# Öffnen des ZIP-Archivs
alo_name <- as.character(unzip("./data/Arbeitslose_2017.xlsx.zip", list = TRUE)$Name)
unzip("./data/Arbeitslose_2017.xlsx.zip", alo_name)Ok, nun haben wir die Daten entzipped und sehen, dass es sich um eine Excel-Datei handelt. Doch diese hat sehr viele unterschiedliche Tabellenblätter. Wie wissen wir, welches Tabellenblatt für uns von Interesse ist?
Dies können wir zum Einen mit excel_sheets herausfinden,
wenn die Tabellenblätter gut benannt sind:
excel_sheets(alo_name)## [1] "Deckblatt" "Impressum" "Inhalt"
## [4] "Hinweis" "5" "6"
## [7] "7" "8" "9"
## [10] "Statistik-Infoseite"Da die Tabellenblätter jedoch nicht unbedingt vielsagend beschriftet sind sollten wir das Tabellenblatt “Inhalt” einlesen und uns dieses anschauen. Eventuell werden wir hier schlauer.
alo_inhalt <- read_xlsx(alo_name, sheet = "Inhalt")
head(alo_inhalt, 15)## # A tibble: 15 × 4
## ...1 ...2 ...3 Arbeitslose nach …¹
## <chr> <chr> <lgl> <chr>
## 1 <NA> <NA> NA <NA>
## 2 Inhaltsverzeichnis <NA> NA <NA>
## 3 <NA> <NA> NA <NA>
## 4 Berichtsmonat: Jahreszahlen 2017 <NA> NA <NA>
## 5 <NA> <NA> NA <NA>
## 6 Arbeitslose nach Gemeinden <NA> NA <NA>
## 7 <NA> Impressum NA 2
## 8 <NA> Inhaltsverzeichnis NA 3
## 9 <NA> Hinweise NA 4
## 10 Zugang <NA> NA <NA>
## 11 <NA> Insgesamt NA 5
## 12 Bestand <NA> NA <NA>
## 13 <NA> Insgesamt NA 6
## 14 <NA> Männer NA 7
## 15 <NA> Frauen NA 8
## # … with abbreviated variable name
## # ¹`Arbeitslose nach Gemeinden Jahreszahlen 2017`Hier erhalten wir einen Überblick über die Tabellenblätter und wo
welche Informationen abgespeichert sind. Da wir uns für den Bestand an
Arbeitslosen interessieren und hier nicht nach Frauen und Männern
unterscheiden möchten, ist das Tabellenblatt 6 für uns das
richtige.
Alternative: Schauen Sie sich die Excel-Datei in Excel oder LibreOffice an und entscheiden Sie dann, welches Tabellenblatt Sie einlesen möchten.
Nun wissen wir, welches Tabellenblatt die für uns wichtige Information enthält:
alo <- read_xlsx(alo_name, sheet="6")
head(alo,10)## # A tibble: 10 × 32
## ...1 ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10 ...11 ...12 ...13
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 Best… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 2 Länd… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 3 Beri… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 4 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 5 <NA> <NA> "Rec… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> "Rec…
## 6 <NA> "Bun… "ins… "aus… <NA> <NA> <NA> daru… "aus… <NA> <NA> <NA> "ins…
## 7 <NA> "Reg… <NA> "unt… unte… "50 … "55 … <NA> "unt… unte… "50 … "55 … <NA>
## 8 Schl… "Gem… "1" "2" 3 "4" "5" 6 "7" 8 "9" "10" "11"
## 9 <NA> "Deu… "253… "478… 2306… "839… "529… 6552… "140… 71133 "136… "732… "855…
## 10 01 "Sch… "924… "207… 9703 "301… "187… 18688 "437" 2297 "347… "190… "309…
## # … with 19 more variables: ...14 <chr>, ...15 <chr>, ...16 <chr>, ...17 <chr>,
## # ...18 <chr>, ...19 <chr>, ...20 <chr>, ...21 <chr>, ...22 <chr>,
## # ...23 <chr>, ...24 <chr>, ...25 <chr>, ...26 <chr>, ...27 <chr>,
## # ...28 <chr>, ...29 <chr>, ...30 <chr>, ...31 <chr>,
## # `Arbeitslose nach Gemeinden Jahreszahlen 2017` <chr>Ok. Hier ist es wohl nicht vorteilhaft von der ersten Zeile ab die Informationen aus dem Tabellenblatt einzulesen. So wie es aussieht sind in den ersten 3 Zeilen Informationen zum Tabellenblatt und dem Berichtsjahr enthalten, dann kommt eine leere Zeile und dann kommen die eigentlichen Spaltenbeschriftungen. Diese sind dann jedoch wiederum in 4 Zeilen unterteilt. Was sind denn hier nun die Spaltenüberschriften, d.h. die Variablennamen im Datensatz?
Hierzu überlegen wir uns folgendes:
Welche Information benötigen wir aus der Tabelle
Wie können wir die von uns benötigte Information möglichst einfach extrahieren
In unserem Fall benötigen wir die Information zur Anzahl aller
Arbeitslosen pro Gemeinde, d.h. uns interessiert Spalte
...3. Weiterhin benötigen wir den Schlüssel um
diese Datenquelle später mit anderen Datenquellen verbinden zu können.
Außerdem wäre der Name der Gemeinde noch eine wichtig Information. Der
einfachste Weg an diese Information zu gelangen ist die ersten acht
Zeilen abzuschneiden und die Daten erst ab dort einzulesen. Anschließend
behalten wir nur die ersten 3 Spalten, da uns nur diese
interessieren.
Das wollen wir nun machen:
# Daten einlesen von Tabellenblatt 6, ohne die ersten 8 Zeilen
alo <- read_xlsx(alo_name, sheet = "6", skip = 8)
# Die entzippte Datei wieder löschen
unlink(alo_name)
# Nun beschränken wir uns auf die ersten drei Spalten und benennen die Spalte `Schlüssel` noch in "Regionalschluessel", wie auch die Spalte `1` in "alo" um
# Weiterhin löschen wir alle Zeilen, für die "alo" auf NA gesetzt ist und die erste Zeile, die alle Arbeitslosen in ganz Deutschland beinhaltet
# Wir speichern den Datensatz als `data_alo` ab
data_alo <- alo %>%
select(c(`Schlüssel`, Gemeinde, `1`)) %>%
rename(Regionalschluessel = `Schlüssel`,
alo = `1`) %>%
filter(!is.na(alo) & Gemeinde!= "Deutschland")Nun sollten wir noch die Daten auf Konsistenz prüfen. D.h. machen die Angaben Sinn und sind die Daten in sich konsistent? Hierfür sollten wir zum Einen externe Datenquellen untersuchen und zum Anderen die Daten intern prüfen.
Wir haben hier sehr feingranulare Informationen über die Arbeitslosenzahl in 2017 vorliegen, jedoch können wir die Daten auch auf eine höhere Ebene aggregieren und damit leicht mit anderen Quellen vergleichen. Dies wollen wir hier tun:
Regionalschluessel. Wir
müssen hier beachten, dass die Regionalschluessel in der
Klasse character vorliegen, d.h. als Strings und nicht als
Zahl. Deshalb können wir die Anzahl an “Buchstaben” für jeden
Regionalschluessel zählen. Dies geschieht über den Befehl
nchar() (number of characters)check_alo_bundesland <- data_alo %>%
filter(nchar(Regionalschluessel) == 2) %>%
rename(bundesland = Regionalschluessel)
check_alo_bundesland## # A tibble: 16 × 3
## bundesland Gemeinde alo
## <chr> <chr> <dbl>
## 1 01 Schleswig-Holstein 92434
## 2 02 Hamburg 69248
## 3 03 Niedersachsen 244260
## 4 04 Bremen 35687
## 5 05 Nordrhein-Westfalen 701219
## 6 06 Hessen 166286
## 7 07 Rheinland-Pfalz 106299
## 8 08 Baden-Württemberg 212837
## 9 09 Bayern 231353
## 10 10 Saarland 34672
## 11 11 Berlin 168991
## 12 12 Brandenburg 92648
## 13 13 Mecklenburg-Vorpommern 70982
## 14 14 Sachsen 140348
## 15 15 Sachsen-Anhalt 96960
## 16 16 Thüringen 68614Wenn wir die Überprüfung mit der anderen Tabelle der Bundesagentur für Arbeit machen, dann sind beide Datenreihen identisch (lediglich Hessen weicht um eine Person ab).
Nun wollen wir noch die interne Konsistenz überprüfen. Hierfür berechnen wir die Anzahl an Arbeitslosen für jedes Bundesland als Summe der Arbeitslosen einer jeden Gemeinde.
# Nur Gemeindedaten nutzen, dann auf Bundeslandebende die Summe aus den Gemeindedaten berechnen
alo_meta <- data_alo %>%
filter(nchar(Regionalschluessel) == 8) %>%
mutate(landkreis = str_extract(Regionalschluessel, "^.{5}"),
bundesland = str_extract(Regionalschluessel, "^.{2}"))
alo_bundesland <- alo_meta %>%
group_by(bundesland) %>%
summarise(total_alo = sum(alo))
alo_landkreis <- alo_meta %>%
group_by(landkreis) %>%
summarise(total_alo = sum(alo)) %>%
rename(Regionalschluessel = landkreis)Um einen besseren Überblick zu erhalten können wir unsere berechneten und die von der Agentur für Arbeit angegebenen Werte miteinander verbinden und die Differenz zwischen den beiden Tabellen berechnen:
check_consitency <- left_join(check_alo_bundesland, alo_bundesland, by = "bundesland")
check_consitency <- check_consitency %>%
mutate(diff = alo - total_alo)
check_consitency## # A tibble: 16 × 5
## bundesland Gemeinde alo total_alo diff
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 01 Schleswig-Holstein 92434 92449 -15
## 2 02 Hamburg 69248 69248 0
## 3 03 Niedersachsen 244260 244277 -17
## 4 04 Bremen 35687 35687 0
## 5 05 Nordrhein-Westfalen 701219 701212 7
## 6 06 Hessen 166286 166296 -10
## 7 07 Rheinland-Pfalz 106299 106287 12
## 8 08 Baden-Württemberg 212837 212835 2
## 9 09 Bayern 231353 231355 -2
## 10 10 Saarland 34672 34675 -3
## 11 11 Berlin 168991 168991 0
## 12 12 Brandenburg 92648 92644 4
## 13 13 Mecklenburg-Vorpommern 70982 70989 -7
## 14 14 Sachsen 140348 140348 0
## 15 15 Sachsen-Anhalt 96960 96960 0
## 16 16 Thüringen 68614 68609 5Es scheint so, als ob es tatsächlich kleinere Differenzen zwischen den von der Agentur für Arbeit auf Bundeslandebene postulierten Zahlen zur Arbeitslosigkeit und den auf Basis der einzelnen Gemeinden berechneten Werten gibt. Diese Unterschiede sind jedoch marginal (Werte zwischen [-17,12]) und werden von uns im folgenden nicht näher untersucht.
Der nächste Datensatz beinhaltet die Pro-Kopf-Verschuldung der deutschen Gemeinden. Hier handelt es sich wieder um Querschnittsdaten auf Gemeindeebene aus dem Jahr 2017.
Diesen Datensatz können wir von der Homepage des Statistischen Bundesamtes direkt als Excel-Tabelle herunterladen und müssen kein ZIP-Archiv entpacken. Allerdings sehen wir sehr schnell, das auch dieser Datensatz seine Tücken beim Einlesen bereithält, insbesondere wenn wir schauen, welche Tabellenblätter für unsere Analyse relevant sind:
excel_sheets("./data/Schulden_2017.xlsx")## [1] "Titel" "Impressum" "Inhalt"
## [4] "Abkürzungen" "Erläuterungen" "SH"
## [7] "NI" "NW" "HE"
## [10] "RP" "BW" "BY"
## [13] "SL" "BB" "MV"
## [16] "SN" "ST" "TH"
## [19] "Statistische Ämter"Nun sind nicht mehr alle Informationen in einem Tabellenblatt enthalten, sondern jedes Bundesland hat sein eigenes Tabellenblatt bekommen. Sprich, wir müssen eine Möglichkeit finden alle Tabellenblätter nacheinander einzulesen und zu verarbeiten.
Dies wollen wir mit einer for-Schleife lösen, doch
zuerst schauen wir uns an, welche Informationen wir aus den
Tabellenblättern benötigen:
sh <- read_xlsx("./data/Schulden_2017.xlsx", sheet = "SH")
head(sh,20)## # A tibble: 20 × 16
## Zurück zu…¹ ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10 ...11 ...12
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 2 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 3 "Tabelle 1… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 4 "nach Höhe… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 5 "Regional-… Geme… Verw… "Ein… Schu… "Sch… Schu… <NA> <NA> <NA> <NA> <NA>
## 6 <NA> <NA> <NA> <NA> <NA> <NA> zusa… Schu… ante… <NA> <NA> <NA>
## 7 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> zusa… davo… <NA> <NA>
## 8 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> 100% 50% … unte…
## 9 <NA> <NA> <NA> <NA> EUR <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 10 <NA> <NA> <NA> <NA> 1 "2" 3 4 5 6 7 8
## 11 "010010000… Flen… krei… "877… 5082… "579… 2979… 1126… 1853… 1822… 2906… 2072…
## 12 "010020000… Kiel… krei… "247… 9488… "383… 5632… 5626… 5701… 4437… 0 1264…
## 13 "010030000… Lübe… krei… "216… 1206… "556… 6014… 5938… 7606… 7027… 0 5788…
## 14 "010040000… Neum… krei… "787… 4260… "540… 1233… 1152… 8176… 8103… 0 72975
## 15 "01051" Krei… Krei… "{13… 6517… "487… 4309… 4302… 72975 0 0 72975
## 16 "010510011… Brun… amts… "127… 3493… "273… 5707… 5195… 5125… 0 0 5125…
## 17 "010510044… Heid… amts… "215… 3705… "172… 2237… 1999… 2375… 0 1863… 5125…
## 18 "010515163" Amts… Amts… "{15… 1047… "66.… 1047… 9742… 72975 0 0 72975
## 19 "010515163… Aver… amts… "576" 1197… "207… 7796… 6325… 1471… 0 0 1471…
## 20 "010515163… Bric… amts… "219" 4962… "226… 3336… 1865… 1471… 0 0 1471…
## # … with 4 more variables: ...13 <chr>, ...14 <chr>,
## # `Zurück zum Inhalt...15` <chr>, ...16 <chr>, and abbreviated variable name
## # ¹`Zurück zum Inhalt...1`Für uns wichtig sind die Infos bzgl. des “Regionalschlüssels”, der “Gemeindename”, die “Einwohner” und die “Schulden des öffentlichen Bereichs insgesamt”. Zur Überprüfung unserer Ergebnisse nehmen wir noch die “Schulden je Einwohner” mit in unseren Datensatz auf, d.h. die ersten sechs Spalten. Weiterhin stehen unsere Variablenbezeichnungen in Zeile 5, d.h. wir ignorieren die ersten 4 Zeilen beim Einlesen.
Der Übersicht halber wollen wir noch eine Spalte hinzufügen, welche den Namen des Tabellenblattes enthält, welches wir gerade eingelesen haben.
# Einlesen des Tabellenblattes "SH" ohne die ersten 5 Zeilen und nur die Spalten 1-6
schulden_individuell <- read_xlsx("./data/Schulden_2017.xlsx", sheet = "SH", skip = 5)[1:6]
# Umbenennen der ersten 6 Spalten
colnames(schulden_individuell) <- c("Regionalschluessel", "Gemeinde",
"Verwaltungsform", "Einwohner", "Schulden_gesamt", "Schulden_pro_kopf")
# Zusätzliche Spalte hinzufügen mit dem Namen des Tabellenblattes
schulden_individuell$Bundesland <- "SH"Ok, nun haben wir die Daten für Schleswig-Holstein eingelesen und
können mit einer for-Schleife alle weiteren Bundesländer
(Tabellenblätter) in der gleichen Form durchgehen:
# Daten mit for-Schleife einlesen (Struktur gleich wie im vorherigen Chunk)
sheet_names <- excel_sheets("./data/Schulden_2017.xlsx")
# Einlesen der Tabellenblätter 7-18 (alle Bundesländer)
sheet_read <- sheet_names[7:18]
for (i in 1:length(sheet_read)){
tmp <- read_xlsx("./data/Schulden_2017.xlsx", sheet = sheet_read[i], skip = 5)[1:6]
tmp$Bundesland <- sheet_read[i]
colnames(tmp) <- c("Regionalschluessel", "Gemeinde", "Verwaltungsform",
"Einwohner", "Schulden_gesamt", "Schulden_pro_kopf", "Bundesland")
# Daten aller weiteren Tabellenblätter unter den aktuellen Datensatz anheften
schulden_individuell <- bind_rows(schulden_individuell, tmp)
}head(schulden_individuell,30)## # A tibble: 30 × 7
## Regionalschluessel Gemeinde Verwa…¹ Einwo…² Schul…³ Schul…⁴ Bunde…⁵
## <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> <NA> <NA> <NA> <NA> <NA> SH
## 2 <NA> <NA> <NA> <NA> <NA> <NA> SH
## 3 <NA> <NA> <NA> <NA> <NA> <NA> SH
## 4 <NA> <NA> <NA> <NA> EUR <NA> SH
## 5 <NA> <NA> <NA> <NA> 1 2 SH
## 6 010010000000 Flensburg, Stadt kreisf… 87770 508281… 5791.06 SH
## 7 010020000000 Kiel, Landeshaupt… kreisf… 247135 948848… 3839.39 SH
## 8 010030000000 Lübeck, Hansestadt kreisf… 216739 120662… 5567.16 SH
## 9 010040000000 Neumünster, Stadt kreisf… 78759 426019… 5409.15 SH
## 10 01051 Kreisverwaltung D… Kreisv… {133 6… 651790… 487.56 SH
## # … with 20 more rows, and abbreviated variable names ¹Verwaltungsform,
## # ²Einwohner, ³Schulden_gesamt, ⁴Schulden_pro_kopf, ⁵BundeslandWir sehen, es gibt immer noch einige Probleme:
Die Werte unserer Variablen stehen nicht direkt unter dem Variablennamen, das ist für uns nicht optimal und ist der Anordnung in der Excel Datei geschuldet.
NAs im Regionalschlüssel entfernen (kein Regionalschlüssel
bedeutet keine Zuordnung zu einer Region und damit für uns nicht
nachvollziehbar).Die Variablen “Einwohner”, “Schulden_gesamt” und
“Schulden_pro_Kopf” sind alle als character hinterlegt
(<chr> unter dem Variablennamen in der vorherigen
Tabelle), wir wollen diese jedoch in numerischer Form um Berechnungen
durchführen zu können
character kann z.B. in Zeile
28 beobachtet werden. Hier wurden geschweifte Klammern verwendet um die
Summe aller Variablen eines Amtsgebiets, Landkreis, Region etc. zu
kennzeichnen.Anschließend wollen wir noch den landkreis als die
ersten 5 Zeichen im Regionalschlüssel definieren.
# Die Daten wurden noch nicht schön eingelesen, in der Excel Tabelle
# waren die Variablennamen über mehrere Reihen gezogen, dies müssen wir noch ausgleichen
schulden_bereinigt <- schulden_individuell %>%
filter(!is.na(Regionalschluessel)) %>%
mutate(Schulden_gesamt = as.numeric(Schulden_gesamt),
Einwohner = as.numeric(Einwohner),
Schulden_pro_kopf = as.numeric(Schulden_pro_kopf)) %>%
mutate(landkreis = str_extract(Regionalschluessel, "^.{5}"),
landkreis = ifelse(landkreis == "10575", "01057", landkreis)) #bei der Gemeinde Selent wurde in der Excel Tabelle die "0" vergessen, dies wird hier behobenEs wurden immer noch einige NAs erzeugt. Diese wollen
wir uns noch näher anschauen:
filter(schulden_bereinigt, is.na(Einwohner))## # A tibble: 2,400 × 8
## Regionalschluessel Gemeinde Verwa…¹ Einwo…² Schul…³ Schul…⁴ Bunde…⁵ landk…⁶
## <chr> <chr> <chr> <dbl> <dbl> <dbl> <chr> <chr>
## 1 01051 Kreisve… Kreisv… NA 6.52e7 488. SH 01051
## 2 010515163 Amtsver… Amtsve… NA 1.05e6 66.9 SH 01051
## 3 010515163_Summe(Amt) Burg-Sa… Amtsge… NA NA NA SH 01051
## 4 010515166 Amtsver… Amtsve… NA 3.65e6 278. SH 01051
## 5 010515166_Summe(Amt) Marne-N… Amtsge… NA NA NA SH 01051
## 6 010515169 Amtsver… Amtsve… NA 8.63e6 465. SH 01051
## 7 010515169_Summe(Amt) Eider Amtsge… NA NA NA SH 01051
## 8 010515172 Amtsver… Amtsve… NA 1.87e6 118. SH 01051
## 9 010515172_Summe(Amt) Heider … Amtsge… NA NA NA SH 01051
## 10 010515175 Amtsver… Amtsve… NA 8.96e5 38.4 SH 01051
## # … with 2,390 more rows, and abbreviated variable names ¹Verwaltungsform,
## # ²Einwohner, ³Schulden_gesamt, ⁴Schulden_pro_kopf, ⁵Bundesland, ⁶landkreisWir müssen wohl noch mehr ausschließen als nur NAs beim
Regionalschlüssel, insgesamt 2400 Einträge bei denen die Variable
“Einwohner” nicht vorhanden ist. Wir hatten bereits gesehen, dass die
Summe aller Einwohner eines Landkreisen mit { Zahl } in der
Excel-Datei hervorgehoben wird. Wenn wir hier in R eine Typumwandlung
erzwingen, dann kann R mit den {} nichts anfangen und gibt
uns deshalb ein NA aus. Wir können hier alle Einträge, bei
denen die Einwohner ein NA stehen haben, löschen, da wir
die Daten selbst auf Basis der Informationen zu den Gemeinden des
Landkreises berechnen können.
schulden_bereinigt <- schulden_bereinigt %>%
filter( !is.na( Einwohner ) )Um die interne Validität unserer Daten beurteilen zu können wollen
wir im ersten Schritt eine Variable Schulden_pro_Kopf_new
generieren, welche die Schulden_pro_Kopf von Hand
berechnet. Wie schon im Abschnitt
Variablen umformen erwähnt, müssen wir hierfür jedoch erst folgendes
beachten, bevor wir Berechnungen durchführen können:
str_remove_all), als auch die Leerzeichen innerhalb der
Zahlen (z.B. 15 653), was wir mit gsub("[[:space:]]")
erreichen. Tun wir das nicht, so würden wir wieder NAs im
Datensatz erhalten.str_remove_all nur angewendet, wenn tatsächlich geschweifte
Klammern vorhanden sind# Erstellen der Vergleichstabelle
schulden_consistency <- schulden_individuell %>%
filter( !is.na(Einwohner) & !is.na(Regionalschluessel) ) %>%
mutate(Schulden_gesamt = ifelse(is.na(as.numeric(Schulden_gesamt))==TRUE,
as.numeric(gsub("[[:space:]]", "", str_remove_all(Schulden_gesamt, "[{}]"))),
as.numeric(Schulden_gesamt)),
Schulden_pro_kopf = ifelse(is.na(as.numeric(Schulden_pro_kopf))==TRUE,
as.numeric(gsub("[[:space:]]", "", str_remove_all(Schulden_pro_kopf, "[{}]"))),
as.numeric(Schulden_pro_kopf)),
Einwohner_num = ifelse(is.na(as.numeric(Einwohner))==TRUE,
as.numeric(gsub("[[:space:]]", "", str_remove_all(Einwohner, "[{}]"))),
as.numeric(Einwohner)),
Schulden_pro_kopf_new = round(Schulden_gesamt / Einwohner_num,2)) %>%
mutate(landkreis = str_extract(Regionalschluessel, "^.{5}"),
differenz = Schulden_pro_kopf - Schulden_pro_kopf_new) Nun können wir uns anschauen, ob die von uns berechneten und die vom Statistischen Bundesamt angegebenen Werte zu den “Schulden_pro_Kopf” signifikant voneinander abweichen:
# range(schulden_consistency$differenz)
# oder schöne skim
skim_without_charts(schulden_consistency$differenz)| Name | schulden_consistency$diff… |
| Number of rows | 13421 |
| Number of columns | 1 |
| _______________________ | |
| Column type frequency: | |
| numeric | 1 |
| ________________________ | |
| Group variables | None |
Variable type: numeric
| skim_variable | n_missing | complete_rate | mean | sd | p0 | p25 | p50 | p75 | p100 |
|---|---|---|---|---|---|---|---|---|---|
| data | 12 | 1 | 0 | 0.09 | -0.49 | 0 | 0 | 0 | 0.5 |
Die Differenzen liegen zwischen +/- 50 Cent und können vermutlich auf Rundungsfehler zurückgeführt werden. D.h. hier können wir die vom statistischen Bundesamt herausgegebenen Berechnungen auf Gemeindeebene verifizieren. Die angegebenen 12 nicht verfügbaren Werte sollten wir noch untersuchen, da hier keine Differenz berechnet werden konnte:
filter(schulden_consistency, is.na(differenz))## # A tibble: 12 × 11
## Regionalsch…¹ Gemei…² Verwa…³ Einwo…⁴ Schul…⁵ Schul…⁶ Bunde…⁷ Einwo…⁸ Schul…⁹
## <chr> <chr> <chr> <chr> <dbl> <dbl> <chr> <dbl> <dbl>
## 1 033519501_Su… Lohhei… Samtge… {768} NA NA NI 768 NA
## 2 033589501_Su… Osterh… Samtge… {2 884} NA NA NI 2884 NA
## 3 052 Landsc… Landsc… X 7.37e8 NA NW NA NA
## 4 056 Landsc… Landsc… X 6.72e8 NA NW NA NA
## 5 058 Region… Kommun… X 1.32e8 NA NW NA NA
## 6 067 Verwal… Landes… X 3.00e7 NA HE NA NA
## 7 074 Bezirk… Bezirk… X 1.40e8 NA RP NA NA
## 8 081a Landes… Landes… X 5.32e6 NA BW NA NA
## 9 081b Kommun… Landes… X 0 NA BW NA NA
## 10 091785127_Su… Allers… Verwal… {7 190} NA NA BY 7190 NA
## 11 095725512_Su… Aurach… Verwal… {4 368} NA NA BY 4368 NA
## 12 144 Kommun… Landes… X 1.00e7 NA SN NA NA
## # … with 2 more variables: landkreis <chr>, differenz <dbl>, and abbreviated
## # variable names ¹Regionalschluessel, ²Gemeinde, ³Verwaltungsform,
## # ⁴Einwohner, ⁵Schulden_gesamt, ⁶Schulden_pro_kopf, ⁷Bundesland,
## # ⁸Einwohner_num, ⁹Schulden_pro_kopf_newHier gibt es meist keine Angaben zu den Einwohnern, oder keine Angaben zu den Schulden. Der Großteil der angegebenen Verwaltungsformen sind Verbände und interessieren uns nicht. Daher müssen wir den fehlenden Werten keine gesonderte Beachtung schenken.
In einem weiteren Konsistenzcheck wollen wir die durchschnittliche Verschuldung pro Kopf auf Landkreisebene selbst berechnen und diese mit den vom Statistischen Bundesamt angegebenen Werten in der Tabelle abgleichen.
Hierfür entnehmen wir der Tabelle zuerst alle Informationen bzgl. Anzahl der “Einwohner”, “Schulden_gesamt” und “Schulden_pro_Kopf” für die Landkreise. Im Datensatz sehen wir, dass die Regionalschluessel für Landkreise die Worte “Summe” und “Kreis” enthalten, wenn das Statistische Bundesamt die Daten auf Landkreisebene aggregiert. Das wollen wir im folgenden nutzen:
# Wir filtern alle Reihen heraus, welche "_Summe" oder "Kreis" im Regionalschlüssel aufweisen
# Anschließend berechnen wir die durchschnittliche Verschuldung auf Landkreisebene
avg_versch_kreis <- schulden_consistency %>%
filter(str_detect(Regionalschluessel, "_Summe") & str_detect(Regionalschluessel, "Kreis")) %>%
group_by(landkreis, Gemeinde) %>%
summarise(avg_verschuldung = Schulden_pro_kopf, einwohner = Einwohner_num,
Gesamtschuld = Schulden_gesamt) %>%
arrange(desc(avg_verschuldung))
# Hier berechnen wir die Daten selbst
avg_versch_kreis_calc <- schulden_consistency %>%
# Ersetze Einwohner_num mit 0 für alle Regionalschlüssel kleiner als 12
mutate(Einwohner_num = ifelse(nchar(Regionalschluessel)<12,0, Einwohner_num)) %>%
# Nur Gemeinden betrachten
filter(nchar(Regionalschluessel)>=5 & str_detect(Regionalschluessel, "_Summe")==FALSE) %>%
# Auf Landkreisebene gruppieren
group_by(landkreis) %>%
summarise(einwohner_calc = sum(Einwohner_num), Gesamtschuld_calc = sum(Schulden_gesamt),
avg_verschuldung_calc = round(Gesamtschuld_calc/einwohner_calc,2)) %>%
arrange(desc(avg_verschuldung_calc))
# Verbinde beide Datensätze und berechne ob es siginfikante Abweichungen zwischen
# den ausgegebenen und berechneten Werten gibt
new <- left_join(avg_versch_kreis, avg_versch_kreis_calc, by="landkreis") %>%
mutate(differenz = avg_verschuldung - avg_verschuldung_calc) %>%
arrange( desc(differenz) )# Ergebnis anschauen
#range(new$differenz)
# oder mit skim
skim_without_charts(new$differenz)| Name | new$differenz |
| Number of rows | 294 |
| Number of columns | 1 |
| _______________________ | |
| Column type frequency: | |
| numeric | 1 |
| ________________________ | |
| Group variables | None |
Variable type: numeric
| skim_variable | n_missing | complete_rate | mean | sd | p0 | p25 | p50 | p75 | p100 |
|---|---|---|---|---|---|---|---|---|---|
| data | 0 | 1 | 0.1 | 1.81 | -0.49 | -0.22 | -0.01 | 0.23 | 30.58 |
Die Differenzen liegen hier zwischen -49 Cent bis +30,58 Euro und können vermutlich auf Rundungsfehler zurückgeführt werden. D.h. hier können wir die vom statistischen Bundesamt herausgegebenen Berechnungen auf Landkreisebene verifizieren. Der Landkreis Plön bildet eine Ausnahme, dies liegt aber vermutlich an der Zuordnung einzelner Teilgemeinden zum Landkreis, welche sich in 2007 geändert hat (siehe Fußnote 2 im Excel-Tabellenblatt “SH”).
Im nächsten Schritt wollen wir uns die Daten zum Bruttoinlandsprodukt einzelner Landkreise anschauen und diese in R einlesen. Wir haben diese direkt als Excel Datei heruntergeladen und können die Datei direkt in R einlesen. Neben dem BIP beziehen wir aus diesem Excel-File die Anzahl an Erwerbstätigen, mit denen wir später die Arbeitslosenquote berechnen können und die Anzahl an Einwohner, mit denen wir das BIP-pro-Kopf berechnen können.
Folgende Schritte wollen wir in einem Chunk erledigen:
Betrachten der Daten
Die ersten vier Zeilen benötigen wir nicht
Die letzte Zeile enthält eine kurze Beschreibung die wir nicht
benötigen -> Vorgehen: Behalte alle Zeilen, bei der
Lfd. Nr. numerisch ist
Die folgenden Variablen benötigen wir nicht für unsere Analyse
und können entfernt werden: Lfd. Nr., EU-Code,
NUTS 1, NUTS 2, NUTS 3,
Land, Gebietseinheit
# Blatt 1.1 einlesen und die ersten 4 Zeilen skippen
bip_name <- "./data/BIP_2021.xlsx"
bip <- read_xlsx(bip_name, sheet="1.1", skip = 4)
erwerb <- read_xlsx(bip_name, sheet="3.1", skip = 4)
einwohner <- read_xlsx(bip_name, sheet = "5.", skip = 4)
# Zeile löschen in der die `Lfd. Nr.` nicht nummerisch ist
# Zusätzliche Spalten löschen
bip_wide <- bip %>%
filter(is.na(as.numeric(`Lfd. Nr.`))==FALSE) %>%
select(-c(`Lfd. Nr.`, `EU-Code`, `NUTS 1`, `NUTS 2`, `NUTS 3`, Land, Gebietseinheit)) %>%
rename(Regionalschluessel = `Regional-schlüssel`)
# Zeile löschen in der die `Lfd. Nr.` nicht nummerisch ist
# Zusätzliche Spalten löschen
erwerb_wide <- erwerb %>%
filter(is.na(as.numeric(`Lfd. Nr.`))==FALSE) %>%
select(-c(`Lfd. Nr.`, `EU-Code`, `NUTS 1`, `NUTS 2`, `NUTS 3`, Land, Gebietseinheit)) %>%
rename(Regionalschluessel = `Regional-schlüssel`)
einwohner_wide <- einwohner %>%
filter(is.na(as.numeric(`Lfd. Nr.`))==FALSE) %>%
select(-c(`Lfd. Nr.`, `EU-Code`, `NUTS 1`, `NUTS 2`, `NUTS 3`, Land, Gebietseinheit)) %>%
rename(Regionalschluessel = `Regional-schlüssel`)
head(bip_wide)## # A tibble: 6 × 29
## Region…¹ `1992` `1994` `1995` `1996` `1997` `1998` `1999` `2000` `2001` `2002`
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl>
## 1 08 25586… 26264… 27174… 27677… 28219… 29109… 30072… 3.09e5 3.23e5 3.26e5
## 2 081 11097… 11160… 11528… 11678… 12086… 12384… 12779… 1.30e5 1.38e5 1.38e5
## 3 08111 32946… 31736… 32281… 32802… 34339… 33553… 35048… 3.53e4 3.84e4 3.97e4
## 4 08115 12090… 11833… 11937… 12097… 13919… 13679… 14424… 1.39e4 1.53e4 1.47e4
## 5 08116 12275… 12482… 12748… 13169… 13284… 13952… 14192… 1.44e4 1.55e4 1.48e4
## 6 08117 5062.… 5180.… 5447.… 5643.… 5667.… 5838.… 5920.… 6.00e3 6.05e3 6.10e3
## # … with 18 more variables: `2003` <dbl>, `2004` <dbl>, `2005` <dbl>,
## # `2006` <dbl>, `2007` <dbl>, `2008` <dbl>, `2009` <dbl>, `2010` <dbl>,
## # `2011` <dbl>, `2012` <dbl>, `2013` <dbl>, `2014` <dbl>, `2015` <dbl>,
## # `2016` <dbl>, `2017` <dbl>, `2018` <dbl>, `2019` <dbl>, `2020` <dbl>, and
## # abbreviated variable name ¹RegionalschluesselDieser Datensatz ist ein sogenanntes Panel. In den vorherigen Datensätzen zur Anzahl der Arbeitslosen und der Pro-Kopf-Verschuldung hatten wir Querschnittsdaten (d.h. Daten nur das Jahr 2017) gegeben. Nun haben wir die Entwicklung des BIP, die Anzahl an Erwerbstätigen und die Anzahl an Einwohnern seit 1992 bis 2017 für alle Landkreise in Deutschland.
long-Format überführenAllerdings sind die Datensätze im wide-Format, d.h.
nicht tidy und damit nicht so, wie wir ihn gerne hätten.
Erinnern wir uns noch an die Bedingungen damit ein Datensätzen
tidy ist?
Im nächsten Schritt wollen wir den Datensatz nun ins
long-Format überführen und nutzen hierfür die Funktion
pivot_longer:
bip_long <- pivot_longer(bip_wide, cols = c("1992":"2017") , names_to = "Jahr", values_to = "BIP")
# Produziert den folgenden Fehler:
# Fehler: Can't combine `1992` <character> and `2000` <double>.Leider ist es hier nicht möglich die Datensätze direkt in das
long-Format zu überführen, insbesondere da die Klassen der
Variablen 1992 bis 1999 character sind und ab 2000 dann
double. Dies sagt uns die erscheinende Fehlermeldung:
Fehler: Can’t combine 1992 2000
Da wir wissen, dass das BIP, die Anzahl an Erwerbstätigen und die
Anzahl an Einwohnern normalerweise numerisch wiedergegeben wird, ist
wohl die Klasse double korrekt und wir sollten die Spalten
von 1992 bis 1999 entsprechend umformatieren.
#BIP von 1992 - 1999 umformen (als numerische Variable)
bip_double <- bip_wide %>%
select(`1992`:`1999`) %>%
mutate_if(is.character, as.double)
# Erwerbstätige von 1992 - 1999 umformen (als numerische Variable)
erwerb_double <- erwerb_wide %>%
select(`1992`:`1999`) %>%
mutate_if(is.character, as.double)
# Einwohner von 1992 - 1999 umformen (als numerische Variable)
einwohner_double <- einwohner_wide %>%
select(`1992`:`1999`) %>%
mutate_if(is.character, as.double)Wir bekommen hier eine Warnmeldung das NAs bei der
Umwandlung erzeugt wurden. Derartige Warnungen sollten wir beachten und
auf den Grund gehen. Nur so wissen wir, ob die Warnung für uns später
unbeabsichtigte Auswirkungen hat.
Hierfür verbinden wir den neuen Datensatz bip_double mit
unserem bisher bestehenden bip_wide und betrachten die
Spalten in denen bip_double NAs enthält:
bip_wide_test <- bip_wide %>%
bind_cols(bip_double)
head(filter(bip_wide_test, is.na(`1994...31`)))## # A tibble: 6 × 36
## Region…¹ 1992.…² 1994.…³ 1995.…⁴ 1996.…⁵ 1997.…⁶ 1998.…⁷ 1999.…⁸ `2000` `2001`
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl>
## 1 13003 . . . . . . . 4767. 4550.
## 2 13004 . . . . . . . 2611. 2695.
## 3 13071 . . . . . . . 5269. 5373.
## 4 13072 . . . . . . . 3570. 3617.
## 5 13073 . . . . . . . 3608. 3631.
## 6 13074 . . . . . . . 2393. 2453.
## # … with 26 more variables: `2002` <dbl>, `2003` <dbl>, `2004` <dbl>,
## # `2005` <dbl>, `2006` <dbl>, `2007` <dbl>, `2008` <dbl>, `2009` <dbl>,
## # `2010` <dbl>, `2011` <dbl>, `2012` <dbl>, `2013` <dbl>, `2014` <dbl>,
## # `2015` <dbl>, `2016` <dbl>, `2017` <dbl>, `2018` <dbl>, `2019` <dbl>,
## # `2020` <dbl>, `1992...30` <dbl>, `1994...31` <dbl>, `1995...32` <dbl>,
## # `1996...33` <dbl>, `1997...34` <dbl>, `1998...35` <dbl>, `1999...36` <dbl>,
## # and abbreviated variable names ¹Regionalschluessel, ²`1992...2`, …Hier sehen wir bereits warum die Klasse der Variablen
1992 bis 1999 character war und
nicht double. Für diese Jahre gab es für einige Regionen
(Mecklenburg-Vorpommern und Niedersachen) keine Angaben zum BIP, den
Erwerbstätigen oder den Einwohnern und daher wurden in der Excel Tabelle
- eingefügt. Daher ist für uns die Umwandlung zu
NA folgerichtig und wir können die Warnmeldung ignorieren
und nur die transformierten Variablen mit der Klasse double
verwenden:
bip_wide <- bip_wide %>%
select(-(`1992`:`1999`)) %>%
bind_cols(bip_double)
erwerb_wide <- erwerb_wide %>%
select(-(`1992`:`1999`)) %>%
bind_cols(erwerb_double)
einwohner_wide <- einwohner_wide %>%
select(-(`1992`:`1999`)) %>%
bind_cols(einwohner_double) Nun können wir den Datensatz ins long-Format
transferieren und nach dem Jahr sortieren. Da die Einwohner und
Erwerbstätigen in 1000 Personen angegeben sind multiplizieren wir unsere
Erwerbstätigen und Einwohner mit 1000. Das BIP ist in 1 Mio. Euro
angegeben, daher die Multiplikation mit 1 Mio.:
# BIP ins long-Format
bip_long <- pivot_longer(bip_wide, cols = c("2000":"1999") , names_to = "Jahr", values_to = "bip") %>%
mutate( Jahr = as.numeric(Jahr),
bip = bip * 1000000) %>%
arrange( Jahr )
# Anzahl der Erwerbstätigen ins long-Format
erwerb_long <- pivot_longer(erwerb_wide, cols = c("2000":"1999") , names_to = "Jahr", values_to = "erw") %>%
mutate( Jahr = as.numeric(Jahr),
erw = erw * 1000) %>%
arrange( Jahr )
# Anzahl der Einwohner ins long-Format
einwohner_long <- pivot_longer(einwohner_wide, cols = c("2000":"1999") , names_to = "Jahr", values_to = "einwohner") %>%
mutate( Jahr = as.numeric(Jahr),
einwohner = einwohner * 1000) %>%
arrange( Jahr )Wir haben bereits in dem Abschnitt “Pro-Kopf Verschuldung” die Einwohner zur Berechnung der Pro-Kopf-Verschuldung für 2017 eingelesen. Für eine längerfristige Betrachtung konnten wir nun mit dem BIP Datensatz die Anzahl der Einwohner von 1992 bis 2021 einlesen. In diesem Konsistenzcheck wollen wir untersuchen ob die Anzahl der Einwohner aus beiden Datenquellen in 2017 identisch ist. Hierbei ist es natürlich wichtig, wann die Daten zur Einwohnerzahl erhoben wurden und deshalb könnten diese auch geringfügig abweichen. Aber dem gehen wir nun auf den Grund:
schulden_check <- schulden_bereinigt %>%
group_by(landkreis) %>%
summarise( Schulden_pro_kopf_lk = sum(Schulden_gesamt)/sum(Einwohner), Einwohner = sum(Einwohner), Schulden_gesamt = sum(Schulden_gesamt)) %>%
rename(Regionalschluessel = landkreis)
einwohner_check <- left_join(schulden_check, filter(einwohner_long, Jahr == 2017), by=c("Regionalschluessel")) %>%
mutate(diff = Einwohner - einwohner) %>%
arrange(desc(diff))
einwohner_check## # A tibble: 397 × 7
## Regionalschluessel Schulden_pro_kopf_lk Einwoh…¹ Schul…² Jahr einwo…³ diff
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 09162 3878. 1470351 5.70e9 2017 1460170 10181
## 2 03460 701. 143088 1.00e8 2017 140106 2982
## 3 03256 1090. 123231 1.34e8 2017 121487 1744
## 4 03251 642. 217183 1.39e8 2017 215547 1636
## 5 08315 1132. 262785 2.98e8 2017 261411 1374
## 6 03453 1082. 168233 1.82e8 2017 166928 1305
## 7 03458 493. 130551 6.43e7 2017 129704 847.
## 8 05978 4264. 394633 1.68e9 2017 393902 731
## 9 08436 1140. 283098 3.23e8 2017 282446 652
## 10 16074 2610. 84345 2.20e8 2017 83758 587
## # … with 387 more rows, and abbreviated variable names ¹Einwohner,
## # ²Schulden_gesamt, ³einwohnerDie Differenz liegt immer bei +/- 2000 Einwohnern, was im Toleranzbereich bei den großen Städte mit mehr als 120.000 Einwohnern ist. Der einzige extreme Ausreiser ist die Landeshauptstadt München. Hier gehen die Einwohnerzahlen um 10 181 Personen auseinander. Laut Wikipedia hat sich jedoch auch die Einwohnerzahl zwischen dem 31.12.2017 und dem 31.12.2018 um 15 000 Personen erhöht. Wenn die erste Quelle die Einwohnerzahlen von Anfang/Mitte 2018 hat und die zweite eher von Anfang/Mitte 2017 dann ist diese Abweichung also durchaus denkbar. Schauen wir uns hierzu die Quellen etwas näher an: Veröffentlichungsdatum der BIP Zahlen ist immer im August des jeweiligen jahres, d.h. im August 2018, die Zahlen zu den Einkommen aus dem Report zu den Integrierten Schulden der Gemeinden stammen vom 30.06.2017, d.h. die Abweichungen in den Einwohnerzahlen könnten gut im Zeitpunkt der Erhebung der Einwohnerzahlen begründet sein. Daher werden wir den Differenzen nicht weiter nachgehen.
Um die langfristigen Entwicklungen des BIP pro Kopf zu visualisieren werden wir die dazugehörigen Einwohnerzahlen aus der Datenquelle zum BIP verwenden. Für die Pro-Kopf-Verschuldung jedoch die in der “Integrierten Schulden der Gemeinden” angegebenen Einwohnerzahl.
Für eine spätere Visualisierung der Daten mittels einer Deutschlandkarte sollten wir uns noch Informationen zu den einzelnen Verwaltunsgrenzen als SHAPE-File herunterladen. Diese Informationen sind über das OpenData Portal des Bundesamts für Kartographie und Geodäsie verfügbar.
Die Dokumentation der Daten sollten wir uns immer zuerst anschauen, bevor wir die Datenquelle herunterladen. Dies gilt nicht nur für die Geodaten, sondern allgemein für alle Datenreihen.
Wir extrahieren uns hier die Informationen zu den Grenzen der
Gemeinden, Verwaltungseinheiten, Landkreise und Bundesländer und
speichern diese jeweils entsprechend ab. Um Geometriedaten einzulesen
und diese später schön als Karte darstellen zu können müssen wir hier
die Funktion st_read aus dem sf-Paket
verwenden:
Da wir nur die Informationen zur Geometrie, z.B. der Landkreisgrenzen möchten, können wir die anderen Variablen auch aus dem Datensätz löschen. Wir behalten den Regionalschlüssel (ARS), den Namen des Kreises/der Gemeinde (GEN) und die Geometrie (geometry).
Wir müssen uns zusätzlich noch etwas mit der Dokumentation des Kartenmaterials beschäftigen. Da wir nur die Verwaltungseinheiten ohne die Nord- und Ostsee und ohne den Bodensee darstellen möchten, so müssen wir noch auf GF = 4 filtern, wie auf Seite 9 der Dokumentation beschrieben wird (tun wir dies nicht, so hätten wir die Nordseegebiete doppelt drin):
Grundsätzlich gilt: Jede Verwaltungseinheit besitzt genau einen Attributsatz mit dem GF-Wert 4. Zusätzlich kann eine Verwaltungseinheit einen Attributsatz mit dem GF-Wert 2 besitzen.
# Auf GF == 4 filtern und ARS als String speichern (ist aktuell als factor abgespeichert)
landkreise <- landkreise %>%
filter( GF==4 ) %>%
select(ARS, GEN, geometry) %>%
mutate(Regionalschluessel = as.character(ARS))
gemeinden <- gemeinden %>%
filter( GF==4 ) %>%
select(ARS, GEN, geometry) %>%
mutate(Regionalschluessel = as.character(ARS))
bundesland <- bundesland %>%
filter( GF==4 ) %>%
select(ARS, GEN, geometry) %>%
mutate(Regionalschluessel = as.character(ARS))In diesem letzten Abschnitt möchten wir alles für die nächsten Schritte der Case Study vorbereiten. Genauer: Wir wollen nicht nur die Informationen aus den einzelnen Datensätzen, sondern am Besten einen kombinierten Datensatz analysieren! Hierfür müssen wir zuerst die Informationen zur Verschuldung auf Landkreisebene aggregieren und die Daten zum BIP auf das Jahr 2017 einschränken. Anschließend können wir die Datensätze anhand des Regionalschlüssels miteinander verbinden.
Weiterhin wollen wir die geografischen Daten separat abspeichern und bei Bedarf anhand des Regionalschlüssels zu unserem Datensatz hinzumergen. Der Regionalschlüssel dient uns hierbei als eindeutige Identifikation der jeweiligen Gemeinde.
# Schulden auf Landkreisebene
schulden_kombi <- schulden_bereinigt %>%
group_by(landkreis) %>%
summarise( Schulden_pro_kopf_lk = sum(Schulden_gesamt)/sum(Einwohner), Einwohner = sum(Einwohner), Schulden_gesamt = sum(Schulden_gesamt)) %>%
rename(Regionalschluessel = landkreis)
# Anzahl an Erwerbstätigen für das Jahr 2017
erwerb_kombi <- erwerb_long %>%
filter(nchar(Regionalschluessel) == 5 & Jahr == 2017) %>%
select(-Jahr)
# Anzahl an Einwohner für das Jahr 2017
einwohner_kombi <- einwohner_long %>%
filter(nchar(Regionalschluessel) == 5 & Jahr == 2017) %>%
select(-Jahr)
# Namen der Landkreise
landkreis_name <- landkreise %>%
st_drop_geometry() %>%
select(-ARS) %>%
mutate(bundesland = str_extract(Regionalschluessel, "^.{2}")) %>%
rename(landkreis_name = GEN)
# Namen der Bundesländer
bundesland_name <- bundesland %>%
st_drop_geometry() %>%
select(-ARS) %>%
rename(bundesland = Regionalschluessel,
bundesland_name = GEN)
# Anzahl der Einwohner mit dem BIP verbinden um das BIP pro Kopf berechnen zu können
bip_zeitreihe <- left_join(bip_long, einwohner_long, by=c("Regionalschluessel", "Jahr")) %>%
mutate(bip_pro_kopf = bip / einwohner)
# BIP auf Landkreisebene im Jahr 2017
bip_kombi <- bip_zeitreihe %>%
filter(nchar(Regionalschluessel) == 5 & Jahr == 2017) %>%
select(-c(Jahr, einwohner))
# Datensätze zusammenführen
# Basisdatensatz -> Arbeitslosenzahlen pro Landkreis
# Name der Landkreise zumergen
daten1 <- left_join(alo_landkreis, landkreis_name, by = "Regionalschluessel")
# Namen der Bundesländer zumergen
daten1 <- daten1 %>% mutate(bundesland = str_extract(Regionalschluessel, "^.{2}"))
daten1 <- left_join(daten1, bundesland_name, by = "bundesland")
# Schulden zumergen
daten2 <- left_join(daten1, schulden_kombi, by = "Regionalschluessel")
# BIP zumergen
daten3 <- left_join(daten2, bip_kombi, by = "Regionalschluessel")
# Zahl der Erwerbstätigen zumergen
gesamtdaten <- left_join(daten3, erwerb_kombi, by = "Regionalschluessel")
saveRDS(gesamtdaten, "data/gesamtdaten.rds")
saveRDS(schulden_bereinigt, "data/schulden_bereinigt.rds")
saveRDS(bip_zeitreihe, "data/bip_zeitreihe.rds")
saveRDS(bundesland, "data/bundesland.rds")
saveRDS(gemeinden, "data/gemeinden.rds")
saveRDS(landkreise, "data/landkreise.rds")Laden Sie sich das durchschnittliche Arbeitnehmerentgelt pro Arbeitnehmer und Landkreis auf der Seite der Statistischen Ämter des Bundes und der Länder herunter und lesen Sie diesen in R ein.
Finden Sie in dem heruntergeladenen Datensatz heraus, was der Unterschied zwischen Arbeitnehmerentgelt und Bruttolöhne- und Gehälter ist.
Lesen Sie die für Sie relevante Tabelle Bruttolöhne- und Gehälter in R ein.
Bereinigen Sie die Tabelle, d.h. der Datensatz sollte danach
tidy sein.
Berechnen Sie die Bruttolöhne pro Bundesland mit den Bruttolöhnen der einzelnen Landkreise als Konsistenzcheck.
Vergleichen Sie ihren Datensatz mit dem auf Github bereitgestellten Datensatz (“einkommen.rds”). Stimmen diese überein?
Verbinden Sie die Informationen zu den durchschnittlichen Einkommen mit dem gesamtdatensatz aus dem vorherigen Abschnitt.