long-Format überführenAngenommen 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 existiert, 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
# Die neuesten Daten zur Verschuldung auf Landkreisebene stammen aus dem Jahr 2022
if (!file.exists("./data/Schulden_2022.xlsx")){
download.file("https://www.statistikportal.de/sites/default/files/2024-03/Integrierte_Schulden_der_Gemeinden_und_Gemeindeverbaende_2022_Tabellenband.xlsx", "./data/Schulden_2022.xlsx")
}
# Arbeitslose aus dem Jahr 2022
#Zu finden unter: https://statistik.arbeitsagentur.de/SiteGlobals/Forms/Suche/Einzelheftsuche_Formular.html?topic_f=gemeinde-arbeitslose-quoten
if (!file.exists("./data/Arbeitslose_2022.xlsx.zip")){
download.file("https://statistik.arbeitsagentur.de/Statistikdaten/Detail/202212/iiia4/gemeinde-arbeitslose-quoten/arbeitslose-quoten-dlk-0-202212-zip.zip?__blob=publicationFile&v=1", "./data/Arbeitslose_2022.xlsx.zip")
}
# Link für die aktuellen Daten zur Arbeitslosigkeit: https://statistik.arbeitsagentur.de/Statistikdaten/Detail/Aktuell/iiia4/gemeinde-arbeitslose-quoten/arbeitslose-quoten-dlk-0-zip.zip?__blob=publicationFile&v=1
# BIP pro Gemeinde aus dem Jahr 2023
if (!file.exists("./data/BIP_2023.xlsx")){
download.file("https://www.statistikportal.de/sites/default/files/2024-07/vgrdl_r2b1_bs2023.xlsx", "./data/BIP_2023.xlsx")
}Die Daten zur Verschuldung wollen wir unter “Schulden_2022.xlsx”, da die Tabelle zwar im März 2024 veröffentlicht wurde, sich aber auf das Jahr 2022 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 2022 heruntergeladen, da wir die passenden Informationen zur Pro-Kopf Verschuldung der Gemeinden nur aus dem Jahr 2022 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 von August 2023 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
# Es sind zwei Tabellen in dem ZIP Archiv, wir interessieren uns für die Anzahl der Arbeitslosen und wählen diese mit dem kleinen [1] aus
alo_name <- as.character(unzip("./data/Arbeitslose_2022.xlsx.zip", list = TRUE)$Name)
alo_name <- alo_name[1]
unzip("./data/Arbeitslose_2022.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" "Inhaltsverzeichnis"
## [4] "Übersicht_Kreise" "Gesamt" "SGB_III"
## [7] "SGB_II" "Männer" "Frauen"
## [10] "Deutsche" "Ausländer" "AGR15u25"
## [13] "AGR55plus" "AGR55u65" "Langzeitarbeitslos"
## [16] "schwerbehindert" "Hinweis_Alo_Asu" "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 = "Inhaltsverzeichnis")
head(alo_inhalt, 20)## # A tibble: 20 × 1
## Inhaltsverzeichnis
## <chr>
## 1 <NA>
## 2 <NA>
## 3 Arbeitslose - Zeitreihe
## 4 <NA>
## 5 <NA>
## 6 Tabelle
## 7 Bestand an Arbeitslosen
## 8 Kreiszusammenfassung
## 9 Übersicht nach Kreisen
## 10 <NA>
## 11 Insgesamt
## 12 Rechtskreis
## 13 SGB III
## 14 SGB II
## 15 Geschlecht
## 16 Männer
## 17 Frauen
## 18 Staatsangehörigkeit
## 19 Deutsche
## 20 AusländerHier 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 oder
Staatsangehörigkeit unterscheiden möchten, ist das Tabellenblatt
Gesamt 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="Gesamt")
head(alo,10)## # A tibble: 10 × 27
## ...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 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 2 Best… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 3 Länd… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 4 Zeit… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 5 Rech… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 6 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 7 <NA> <NA> <NA> Aus … <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 8 <NA> Jahr… <NA> Rech… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 9 <NA> Jahr… Jahr… 44197 44228 44256 44287 44317 44348 44378 44409 44440 44470
## 10 Regi… 2021 2022 1 2 3 4 5 6 7 8 9 10
## # ℹ 14 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>,
## # Arbeitsmarktstatistik <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 5 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 durchschnittlichen
Anzahl aller Arbeitslosen pro Gemeinde aus dem Jahr 2022, d.h. uns
interessiert Spalte ...3. Weiterhin benötigen wir eine
eindeutig zuzuordnende “Gemeinde-ID” 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 "Gesamt", ohne die ersten 8 Zeilen
alo <- read_xlsx(alo_name, sheet = "Gesamt", skip = 8)
# Die entzippte Datei wieder löschen
unlink(alo_name)
# Nun beschränken wir uns auf die erste und dritte Spalte und trennen den Namen der Region (welcher in Spalte `...1` gelistet wird) und deren "Gemeinde-ID", dem sogenannten "Regionalschluessel" voneinander.
# Die Spalte 3 wollen wir anschließend in "alo" umbenennen
# 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(`...1`, Jahresdurchschnitte, `...3`)) |>
mutate(Regionalschluessel = str_extract(`...1`, "[[:digit:]]+"),
Gemeinde = str_extract(`...1`, "[A-Z].*")) |>
mutate(alo = as.numeric(`...3`)) |>
select(-c(`...1`, Jahresdurchschnitte, `...3`)) |>
filter(!is.na(alo))
# Die ersten drei Zeilen beinhalten keine nutzlichen Informationen, d.h. wir löschen diese
data_alo <- data_alo[-c(1,2),]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 2022 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 81564.
## 2 02 Hamburg 73800.
## 3 03 Niedersachsen 230553.
## 4 04 Bremen 37214.
## 5 05 Nordrhein-Westfalen 668502.
## 6 06 Hessen 164492.
## 7 07 Rheinland-Pfalz 102515.
## 8 08 Baden-Württemberg 223119.
## 9 09 Bayern 235850.
## 10 10 Saarland 33017.
## 11 11 Berlin 179327.
## 12 12 Brandenburg 74242
## 13 13 Mecklenburg-Vorpommern 59571.
## 14 14 Sachsen 118216.
## 15 15 Sachsen-Anhalt 77978.
## 16 16 Thüringen 58172.Wenn wir die Überprüfung mit der anderen Tabelle der Bundesagentur für Arbeit machen, dann sind beide Datenreihen identisch.
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 81564. 81564. 0
## 2 02 Hamburg 73800. 73800. 0
## 3 03 Niedersachsen 230553. 230553. 0
## 4 04 Bremen 37214. 37214. -7.28e-12
## 5 05 Nordrhein-Westfalen 668502. 668502. 0
## 6 06 Hessen 164492. 164492. 0
## 7 07 Rheinland-Pfalz 102515. 102515. 0
## 8 08 Baden-Württemberg 223119. 223119. 0
## 9 09 Bayern 235850. 235850. 0
## 10 10 Saarland 33017. 33017. 0
## 11 11 Berlin 179327. 179327. 0
## 12 12 Brandenburg 74242 74242 0
## 13 13 Mecklenburg-Vorpommern 59571. 59571. 0
## 14 14 Sachsen 118216. 118216. 0
## 15 15 Sachsen-Anhalt 77978. 77978. 0
## 16 16 Thüringen 58172. 58172. 0Unsere Analysen zeigen, dass es keine Differenzen zwischen den Daten gibt, die von der Bundesagentur für Arbeit auf Landes- und Bundesebene veröffentlichen.
Der nächste Datensatz beinhaltet die Pro-Kopf-Verschuldung der deutschen Gemeinden. Hier handelt es sich wieder um Querschnittsdaten auf Gemeindeebene aus dem Jahr 2022.
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_2022.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 Schleife lösen, doch zuerst schauen wir uns an, welche Informationen wir aus den Tabellenblättern benötigen:
sh <- read_xlsx("./data/Schulden_2022.xlsx", sheet = "SH")
head(sh,20)## # A tibble: 20 × 21
## `Zurück zum Inhalt...1` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 2 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 3 "Tabelle 1: Schulde… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 4 "nach Höhe der Beteili… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 5 "Regional-\r\nschlüsse… Geme… Verw… "Ein… Schu… Verä… "Sch… Schu… <NA> <NA>
## 6 <NA> <NA> <NA> <NA> <NA> <NA> <NA> zusa… Verä… Schu…
## 7 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 8 <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 9 <NA> <NA> <NA> <NA> EUR % "EUR" <NA> % EUR
## 10 <NA> <NA> <NA> <NA> 1 2 "3" 4 5 6
## 11 "010010000000" Flen… krei… "919… 4617… 1.6 "501… 2230… -3.3 3571…
## 12 "010020000000" Kiel… krei… "246… 1101… 5.2 "446… 5924… 7.1 5921…
## 13 "010030000000" Lübe… krei… "217… 9835… -7.5 "451… 3582… -16.8 3549…
## 14 "010040000000" Neum… krei… "798… 5036… 11 "630… 1245… 9.1 1142…
## 15 "01051" Krei… Krei… "{13… 5767… 31 "427… 3696… 79.4… 3693…
## 16 "010510011011" Brun… amts… "125… 5788… 11.2 "462… 2522… 14.3 2245…
## 17 "010510044044" Heid… amts… "219… 4920… 14.7 "224… 3055… 21.8 2637…
## 18 "010515163" Amts… Amts… "{15… 8252… -3 "52.… 8252… -3 7942…
## 19 "010515163003" Aver… amts… "572" 2247… 12.1 "392… 1503… 15.7 5883…
## 20 "010515163010" Bric… amts… "202" 1435… 21.6 "710… 1101… 23.9 1865…
## # ℹ 11 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>, ...14 <chr>,
## # ...15 <chr>, ...16 <chr>, ...17 <chr>, ...18 <chr>, ...19 <chr>,
## # `Zurück zum Inhalt...20` <chr>, ...21 <chr>Für uns wichtig sind die Infos bzgl. des “Regionalschlüssel”, 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-7
schulden_individuell <- read_xlsx("./data/Schulden_2022.xlsx", sheet = "SH", skip = 5)[1:7]
# Umbenennen der ersten 7 Spalten
colnames(schulden_individuell) <- c("Regionalschluessel", "Gemeinde",
"Verwaltungsform", "Einwohner", "Schulden_gesamt", "Veraenderung_Vorjahr", "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 für alle anderen Daten gleich vorgehen.
In den nächsten drei Chunks sehen Sie unterschiedliche Varianten um
die Daten einzulesen. In der ersten Variante nutzen wir eine
for-Schleife um alle Bundesländer (Tabellenblätter) in der
gleichen Form durchzugehen (diese Art der Schleife kennen Sie vermutlich
aus anderen Informatik-Veranstaltungen/Programmiersprachen):
# Daten mit for-Schleife einlesen (Struktur gleich wie im vorherigen Chunk)
sheet_names <- excel_sheets("./data/Schulden_2022.xlsx")
# Einlesen der Tabellenblätter 6-18 (alle Bundesländer)
sheet_read <- sheet_names[6:18]
schulden_individuell <- NULL
for (i in 1:length(sheet_read)){
tmp <- read_xlsx("./data/Schulden_2022.xlsx", sheet = sheet_read[i], skip = 5)[1:7]
tmp$Bundesland <- sheet_read[i]
colnames(tmp) <- c("Regionalschluessel", "Gemeinde", "Verwaltungsform",
"Einwohner", "Schulden_gesamt", "Veraenderung_Vorjahr", "Schulden_pro_kopf", "Bundesland")
# Daten aller weiteren Tabellenblätter unter den aktuellen Datensatz anheften
schulden_individuell <- bind_rows(schulden_individuell, tmp)
}Die zweite Möglichkeit wäre es die Funktion sapply() zu nutzen. D.h.
Sie nutzen die funktionale Programmierung in R, welche meist deutlich
effizienter ist als eine for-Schleife:
# Daten mit sapply()-Schleife einlesen (Struktur gleich wie im vorherigen Chunk)
sheet_names <- excel_sheets("./data/Schulden_2022.xlsx")
# Einlesen der Tabellenblätter 6-18 (alle Bundesländer)
sheet_read <- sheet_names[6:18]
# sapply() anstelle der for-Schleife verwenden
schulden_individuell <- bind_rows(
sapply(sheet_read, function(sheet) {
tmp <- read_xlsx("./data/Schulden_2022.xlsx", sheet = sheet, skip = 5)[1:7]
tmp$Bundesland <- sheet
colnames(tmp) <- c("Regionalschluessel", "Gemeinde", "Verwaltungsform",
"Einwohner", "Schulden_gesamt", "Veraenderung_Vorjahr", "Schulden_pro_kopf", "Bundesland")
return(tmp)
}, simplify = FALSE)
)In einer dritten Möglichkeit nutzen Sie das Paket purrr
(welches Teil der tidyverse-Familie ist) und dort die
Funktion map_df() um die Daten vektorisiert einzulesen.
Dieser vektorisierte Ansatz mit map_df() aus dem
purrr-Paket ist eine besonders einfache und effiziente
Methode, um mehrere Excel-Tabellen gleichzeitig einzulesen und zu
verarbeiten. Anstatt jede Tabelle einzeln durchzugehen, wird hier der
gleiche Vorgang auf alle Tabellenblätter auf einmal angewendet. Dadurch
sparen Sie sich die Verwendung von Schleifen, und der Code wird
einfacher und übersichtlicher. Außerdem passt dieser Ansatz gut in das
tidyverse-Ökosystem, das speziell für Datenanalysen optimiert ist.
# Daten mit map_df() eine Vektorisierte Form um die Daten einzulesen (Struktur gleich wie im vorherigen Chunk)
sheet_names <- excel_sheets("./data/Schulden_2022.xlsx")
# Einlesen der Tabellenblätter 6-18 (alle Bundesländer)
sheet_read <- sheet_names[6:18]
# Vektorisierte Form mit purrr::map_df() um die unterschiedlichen Tabellenblätter einzulesen und die Daten zu einem Datensatz zusammenzufügen
schulden_individuell <- map_df(sheet_read, function(sheet) {
tmp <- read_xlsx("./data/Schulden_2022.xlsx", sheet = sheet, skip = 5)[1:7]
tmp$Bundesland <- sheet
colnames(tmp) <- c("Regionalschluessel", "Gemeinde", "Verwaltungsform",
"Einwohner", "Schulden_gesamt", "Veraenderung_Vorjahr", "Schulden_pro_kopf", "Bundesland")
return(tmp)
})head(schulden_individuell,30)## # A tibble: 30 × 8
## Regionalschluessel Gemeinde Verwaltungsform Einwohner Schulden_gesamt
## <chr> <chr> <chr> <chr> <chr>
## 1 <NA> <NA> <NA> <NA> <NA>
## 2 <NA> <NA> <NA> <NA> <NA>
## 3 <NA> <NA> <NA> <NA> <NA>
## 4 <NA> <NA> <NA> <NA> EUR
## 5 <NA> <NA> <NA> <NA> 1
## 6 010010000000 Flensburg, Stadt kreisfreie Sta… 91992 461784578.9700…
## 7 010020000000 Kiel, Landeshau… kreisfreie Sta… 246712 1101148771.960…
## 8 010030000000 Lübeck, Hansest… kreisfreie Sta… 217799 983573069.7400…
## 9 010040000000 Neumünster, Sta… kreisfreie Sta… 79889 503685790.8199…
## 10 01051 Kreisverwaltung… Kreisverwaltung {135 009} 57672347.28999…
## # ℹ 20 more rows
## # ℹ 3 more variables: Veraenderung_Vorjahr <chr>, Schulden_pro_kopf <chr>,
## # Bundesland <chr>Wir 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.Die “Veraenderung_Vorjahr” ist für unsere weitere Analyse nicht relevant, daher wollen wir diese aus dem Datensatz entfernen
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}")) |>
select(-Veraenderung_Vorjahr)Es wurden immer noch einige NAs erzeugt. Diese wollen
wir uns noch näher anschauen:
filter(schulden_bereinigt, is.na(Einwohner))## # A tibble: 2,367 × 8
## Regionalschluessel Gemeinde Verwaltungsform Einwohner Schulden_gesamt
## <chr> <chr> <chr> <dbl> <dbl>
## 1 01051 Kreisverwaltu… Kreisverwaltung NA 57672347.
## 2 010515163 Amtsverwaltun… Amtsverwaltung NA 825202.
## 3 010515163_Summe(Amt) Amt Burg-St. … Amtsgebiet NA NA
## 4 010515166 Amtsverwaltun… Amtsverwaltung NA 6821008.
## 5 010515166_Summe(Amt) Amt Marne-Nor… Amtsgebiet NA NA
## 6 010515169 Amtsverwaltun… Amtsverwaltung NA 11164461.
## 7 010515169_Summe(Amt) Amt KLG Eider Amtsgebiet NA NA
## 8 010515172 Amtsverwaltun… Amtsverwaltung NA 1192621.
## 9 010515172_Summe(Amt) Amt KLG Heide… Amtsgebiet NA NA
## 10 010515175 Amtsverwaltun… Amtsverwaltung NA 3877702.
## # ℹ 2,357 more rows
## # ℹ 3 more variables: Schulden_pro_kopf <dbl>, Bundesland <chr>,
## # landkreis <chr>Wir 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 |>
#manche Landkreise haben keine Infos zu den Einwohnern, diese entfernen wir
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:
gsub("[{}]")), 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. Wir tun dies hier in einem Befehl
gsub("[[:space:]{}]")# Erstellen der Vergleichstabelle
schulden_consistency <- schulden_individuell |>
filter(!is.na(Einwohner) & !is.na(Regionalschluessel)) |>
# Wir wenden die Funktion über die angegebenen Variablen an und nutzen dafür across())
mutate(across(c(Schulden_gesamt, Schulden_pro_kopf, Einwohner),
# Die Funktionen, die wir anwenden sind gsub() und str_remove_all()
~ as.numeric(gsub("[[:space:]{}]", "",.))),
Schulden_pro_kopf_new = round(Schulden_gesamt / Einwohner, 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 | 13108 |
| 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 | 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.
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,
Gesamtschuld = Schulden_gesamt,
# Keine Gruppierung mehr im Output durch:
.groups = 'drop') |>
arrange(desc(avg_verschuldung))
# Hier berechnen wir die Daten selbst
avg_versch_kreis_calc <- schulden_consistency |>
# Ersetze Einwohner mit 0 für alle Regionalschlüssel kleiner als 12
mutate(Einwohner = ifelse(nchar(Regionalschluessel)<12,0, Einwohner)) |>
# Nur Gemeinden betrachten
filter(nchar(Regionalschluessel)>=5 & str_detect(Regionalschluessel, "_Summe")==FALSE) |>
# Auf Landkreisebene gruppieren
group_by(landkreis) |>
summarise(einwohner_calc = sum(Einwohner, na.rm = TRUE),
Gesamtschuld_calc = sum(Schulden_gesamt, na.rm = TRUE),
avg_verschuldung_calc = round(Gesamtschuld_calc/einwohner_calc,2),
# Keine Gruppierung mehr im Output durch:
.groups = 'drop') |>
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.01 | 0.28 | -0.49 | -0.21 | 0 | 0.27 | 0.5 |
Die Differenzen liegen hier zwischen -0,49 Euro bis +0,50 Euro (im Durchschnitt bei 0,01 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.
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 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,
diese 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_2023.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 × 31
## Regionalschluessel `1992` `1994` `1995` `1996` `1997` `1998` `1999` `2000`
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
## 1 08 255866.41… 26264… 27174… 27677… 28219… 29109… 30072… 3.09e5
## 2 081 110977.071 11160… 11528… 11678… 12086… 12384… 12779… 1.30e5
## 3 08111 32946.883… 31736… 32281… 32802… 34339… 33553… 35048… 3.53e4
## 4 08115 12090.93 11833… 11937… 12097… 13919… 13679… 14424… 1.39e4
## 5 08116 12275.605 12482… 12748… 13169… 13284… 13952… 14192… 1.44e4
## 6 08117 5062.0370… 5180.… 5447.… 5643.… 5667.… 5838.… 5920.… 6.00e3
## # ℹ 22 more variables: `2001` <dbl>, `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>, `2021` <dbl>, `2022` <dbl>Dieser 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 2022) gegeben. Nun haben wir die Entwicklung des BIP, die Anzahl an Erwerbstätigen und die Anzahl an Einwohnern seit 1992 bis 2022 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":"2022") , 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(`1992...32`)))## # A tibble: 6 × 38
## Regionalschluessel `1992...2` `1994...3` `1995...4` `1996...5` `1997...6`
## <chr> <chr> <chr> <chr> <chr> <chr>
## 1 13003 . . . . .
## 2 13004 . . . . .
## 3 13071 . . . . .
## 4 13072 . . . . .
## 5 13073 . . . . .
## 6 13074 . . . . .
## # ℹ 32 more variables: `1998...7` <chr>, `1999...8` <chr>, `2000` <dbl>,
## # `2001` <dbl>, `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>,
## # `2021` <dbl>, `2022` <dbl>, `1992...32` <dbl>, `1994...33` <dbl>,
## # `1995...34` <dbl>, `1996...35` <dbl>, `1997...36` <dbl>, …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 2022 eingelesen. Für eine längerfristige Betrachtung konnten wir nun mit dem BIP Datensatz die Anzahl der Einwohner von 1992 bis 2022 einlesen. In diesem Konsistenzcheck wollen wir untersuchen ob die Anzahl der Einwohner aus beiden Datenquellen in 2022 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),
.groups = 'drop') |>
rename(Regionalschluessel = landkreis)
einwohner_check <- left_join(schulden_check, filter(einwohner_long, Jahr == 2022), by=c("Regionalschluessel")) |>
mutate(diff = Einwohner - einwohner) |>
arrange(desc(diff))
skim_without_charts(einwohner_check$diff)| Name | einwohner_check$diff |
| Number of rows | 396 |
| 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 | 684.03 | 787.1 | -1997 | 276.75 | 523 | 905.5 | 8833 |
head(arrange(einwohner_check, -diff),20)## # A tibble: 20 × 7
## Regionalschluessel Schulden_pro_kopf_lk Einwohner Schulden_gesamt Jahr
## <chr> <dbl> <dbl> <dbl> <dbl>
## 1 09162 5633. 1508933 8500326561. 2022
## 2 09564 7597. 522711 3971241943. 2022
## 3 03241 5463. 1169979 6391943023. 2022
## 4 06412 11342. 770112 8734900741. 2022
## 5 03358 1331. 147668 196478264. 2022
## 6 14713 4552. 612378 2787281253. 2022
## 7 05913 7177. 592900 4255524935. 2022
## 8 08111 2969. 632165 1877209318. 2022
## 9 03459 1423. 366423 521417956. 2022
## 10 03453 1200. 178273 213957474. 2022
## 11 05315 7854. 1081167 8491108807. 2022
## 12 08226 2213. 554352 1226728637. 2022
## 13 09184 2220. 354990 788159564. 2022
## 14 05766 2666. 350459 934150509. 2022
## 15 05162 3645. 456937 1665629822. 2022
## 16 05382 4147. 606489 2515087619. 2022
## 17 05314 7403. 336064 2487877086. 2022
## 18 06414 7228. 282903 2044814832. 2022
## 19 05158 3398. 488980 1661579594. 2022
## 20 06633 1834. 240831 441671172. 2022
## # ℹ 2 more variables: einwohner <dbl>, diff <dbl>Die Differenz liegt im Durchschnitt bei 684 Einwohnern, was im Toleranzbereich bei den großen Städte mit mehr als 120.000 Einwohnern ist. Die Abweichungen in den Einwohnerzahlen könnten gut im Zeitpunkt der Erhebung der Einwohnerzahlen begründet sein (August 2023 beim BIP und Ende Dezember 2022 bei den Daten zur Verschuldung). 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 2022 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 2022
erwerb_kombi <- erwerb_long |>
filter(nchar(Regionalschluessel) == 5 & Jahr == 2022) |>
select(-Jahr)
# Anzahl an Einwohner für das Jahr 2022
einwohner_kombi <- einwohner_long |>
filter(nchar(Regionalschluessel) == 5 & Jahr == 2022) |>
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 2021
bip_kombi <- bip_zeitreihe |>
filter(nchar(Regionalschluessel) == 5 & Jahr == 2022) |>
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.