Case Study für Deutschland

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.

Ziele der Case Study

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.

Daten beschaffen

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.

• Die Informationen über die Verschuldung der Gemeinden finden wir auf den Seiten des Statistischen Bundesamts im Report: Integrierte Schulden der Gemeinden und Gemeindeverbände.
• Die Informationen zur Arbeitslosigkeit auf Verwaltungsgemeinschaftsebene finden wir auf den Seiten der Bundesagentur für Arbeit.
• Die Informationen zum BIP auf Landkreisebene finden wir auf den Seiten der Statistischen Ämter des Bundes und der Länder.

Nötige Pakete laden

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)

Daten herunterladen

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"))

## Warning in dir.create(file.path(".", "data")): './data' existiert bereits

# 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 2021
if (!file.exists("./data/Schulden_2021.xlsx")){
  download.file("https://www.statistikportal.de/sites/default/files/2022-11/Integrierte_Schulden_der_Gemeinden_und_Gemeindeverbaende_2022_Tabellenband.xlsx", "./data/Schulden_2021.xlsx")
}

# Arbeitslose aus dem Jahr 2021
#Zu finden unter: https://statistik.arbeitsagentur.de/SiteGlobals/Forms/Suche/Einzelheftsuche_Formular.html?topic_f=gemeinde-arbeitslose-quoten
if (!file.exists("./data/Arbeitslose_2021.xlsx.zip")){
  download.file("https://statistik.arbeitsagentur.de/Statistikdaten/Detail/202112/iiia4/gemeinde-arbeitslose-quoten/arbeitslose-quoten-dlk-0-202112-zip.zip?__blob=publicationFile&v=1", "./data/Arbeitslose_2021.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

# BIP pro Gemeinde aus dem Jahr 2022
if (!file.exists("./data/BIP_2022.xlsx")){
  download.file("https://www.statistikportal.de/sites/default/files/2023-07/vgrdl_r2b1_bs2022.xlsx", "./data/BIP_2022.xlsx")
}

Die Daten zur Verschuldung wollen wir unter “Schulden_2021.xlsx”, da die Tabelle zwar in 2022 generiert wurde, sich aber auf das Jahr 2021 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 2021 heruntergeladen, da wir die passenden Informationen zur Pro-Kopf Verschuldung der Gemeinden aus nur aus dem Jahr 2021 online erhalten. Die Daten zur Verschuldung werden alle 4 Jahre vom Statistischen Bundesamt aktualisiert.

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 2022 bezogen da wir hier Paneldaten haben und keine Querschnittsdaten (was dies genau heißt wird im Laufe der Case-Study erläutert).

Daten einlesen

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.

Anzahl an Arbeitslosen

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.

ZIP-Archiv entpacken

# Ö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_2021.xlsx.zip", list = TRUE)$Name)
alo_name <- alo_name[1]
unzip("./data/Arbeitslose_2021.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, 15)

## # A tibble: 15 × 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

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 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.

Entpackte Datei einlesen

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… 43831 43862 43891 43922 43952 43983 44013 44044 44075 44105
## 10 Regi… 2020  2021  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?

Spezifizieren welche Spalten eingelesen werden sollen

Hierzu überlegen wir uns folgendes:

• Welche Information benötigen wir aus der Tabelle

  • Die Anzahl aller Arbeitslosen pro Gemeinde (d.h. SGB II und III gemeinsam) aus dem Jahr 2021
  • Die Anzahl der Arbeitslosen pro Gemeinde für einen bestimmten Rechtskreis (z.B. nur SGB II)
  • Die Anzahl der Arbeitslosen pro Gemeinde für einen bestimmten Rechtskreis und ein bestimmtes Alter (z.B. SGB II alle unter 25 Jahre)

• 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 2021, d.h. uns interessiert Spalte ...3. Weiterhin benötigen wir eine eindeutig zuzuordenden “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))

## Warning: There was 1 warning in `mutate()`.
## ℹ In argument: `alo = as.numeric(...3)`.
## Caused by warning:
## ! NAs durch Umwandlung erzeugt

# Die ersten drei Zeilen beinhalten keine nutzlichen Informationen, d.h. wir löschen diese
data_alo <- data_alo[-c(1,2),]

Konsistenzcheck

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 2021 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:

• Zunächst lassen wir uns die Anzahl an Arbeitslosen für jedes Bundesland in 2021 ausgeben. In unserem Datensatz sind dies alle Datenpunkte mit einem zweistelligen 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)
• Nun sollten wir eine andere Datenquelle heranziehen und die Informationen gegenchecken. Bspw. könnten wir die Anzahl der Arbeitslosen für das Jahr 2021 unterteilt nach Ländern heranziehen. (Tabellenblatt 8)

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      88865.
##  2 02         Hamburg                 80395.
##  3 03         Niedersachsen          243021.
##  4 04         Bremen                  39292.
##  5 05         Nordrhein-Westfalen    718220.
##  6 06         Hessen                 178086.
##  7 07         Rheinland-Pfalz        112137.
##  8 08         Baden-Württemberg      247774.
##  9 09         Bayern                 262186.
## 10 10         Saarland                36156.
## 11 11         Berlin                 198401.
## 12 12         Brandenburg             78463.
## 13 13         Mecklenburg-Vorpommern  62410.
## 14 14         Sachsen                124743.
## 15 15         Sachsen-Anhalt          81093.
## 16 16         Thüringen               62249.

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(as.numeric(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      88865.    88865.     0
##  2 02         Hamburg                 80395.    80395.     0
##  3 03         Niedersachsen          243021.   243021.     0
##  4 04         Bremen                  39292.    39292.     0
##  5 05         Nordrhein-Westfalen    718220.   718220.     0
##  6 06         Hessen                 178086.   178086.     0
##  7 07         Rheinland-Pfalz        112137.   112137.     0
##  8 08         Baden-Württemberg      247774.   247774.     0
##  9 09         Bayern                 262186.   262186.     0
## 10 10         Saarland                36156.    36156.     0
## 11 11         Berlin                 198401.   198401.     0
## 12 12         Brandenburg             78463.    78463.     0
## 13 13         Mecklenburg-Vorpommern  62410.    62410.     0
## 14 14         Sachsen                124743.   124743.     0
## 15 15         Sachsen-Anhalt          81093.    81093.     0
## 16 16         Thüringen               62249.    62249.     0

Unsere Analysen zeigen, dass es keine Differenzen zwischen den Daten gibt, die von der Bundesagentur für Arbeit auf Landes- und Bundesebene veröffentlichen.

Pro-Kopf Verschuldung

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_2021.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"

Mehrere Tabellenblätter einlesen

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_2021.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… "899… 4545… 0     "505… 2310… -9.5  4625…
## 12 "010020000000"          Kiel… krei… "245… 1039… 6.1   "422… 5534… 6.7   5531…
## 13 "010030000000"          Lübe… krei… "215… 1090… 1.2   "507… 4305… -5.9  4256…
## 14 "010040000000"          Neum… krei… "796… 4532… 5.2   "568… 1141… 10.6  1029…
## 15 "01051"                 Krei… Krei… "{13… 4296… -14.8 "322… 1954… -20.7 1952…
## 16 "010510011011"          Brun… amts… "123… 5464… 0.5   "443… 2206… 0     1994…
## 17 "010510044044"          Heid… amts… "215… 4285… -5.4  "199… 2508… -9.6  2146…
## 18 "010515163"             Amts… Amts… "{15… 8503… -4.2  "54.… 8503… -4.2  8302…
## 19 "010515163003"          Aver… amts… "554" 1917… 13.6  "346… 1300… 10.1… 5971…
## 20 "010515163010"          Bric… amts… "200" 1176… 22    "588… 8898… 17    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ü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-7
schulden_individuell <- read_xlsx("./data/Schulden_2021.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 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_2021.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_2021.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)
}

Variablen umformen

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… 89949     454539445.9700…
##  7 010020000000       Kiel, Landeshau… kreisfreie Sta… 245841    1039095143.430…
##  8 010030000000       Lübeck, Hansest… kreisfreie Sta… 215051    1090890891.450…
##  9 010040000000       Neumünster, Sta… kreisfreie Sta… 79683     453215674.2999…
## 10 01051              Kreisverwaltung… Kreisverwaltung {133 401} 42967438.62000…
## # ℹ 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.

  • Dies können wir am einfachsten bereinigen indem wir alle 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

  • Der Grund für die Klasse 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.
  • Im ersten Schritt wollen wir diese Summen einfach ignorieren da wir die jeweiligen Summen auch selbst berechnen können.

• 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}")) %>%
  #manche Landkreise haben keine Infos zu den Einwohnern, diese entfernen wir
  filter( !is.na( Einwohner )) %>%
  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: 0 × 8
## # ℹ 8 variables: Regionalschluessel <chr>, Gemeinde <chr>,
## #   Verwaltungsform <chr>, Einwohner <dbl>, Schulden_gesamt <dbl>,
## #   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 %>%
  filter( !is.na( Einwohner ) )

Konsistenzcheck

Berechnung der Schulden pro Kopf von Hand

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:

• Wir müssen die geschweiften Klammern entfernen (mit 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.
• Durch die ifelse Bedingung wird der Befehl 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)

Data summary

Name	schulden_consistency$diff…
Number of rows	13114
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.

Vergleich der Schulden pro Kopf auf Landkreisebene

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)

Data summary

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.52	9.01	-154.44	-0.26	0	0.27	0.5

Die Differenzen liegen hier zwischen -154,44 Euro bis +0,50 Euro (im Durchschnitt bei -0,52 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 Lüchow-Dannenberg bildet eine Ausnahme mit -154,44 Euro.

Bruttoinlandsprodukt

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.

Nur einzelne Spalten einlesen

Folgende Schritte wollen wir in einem Chunk erledigen:

• Betrachten der Daten

  • Tabellenblatt “1.1” ist für unsere Analyse ausschlaggebend (für das BIP)
  • Tabellenblatt “3.1” ist für die Anzahl an Erwerbstätigen ausschlaggebend
  • Tabellenblatt “5” ist für die Anzahl an Einwohnern ausschlaggebend

• 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_2022.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 × 30
##   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
## # ℹ 21 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>

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 2021) gegeben. Nun haben wir die Entwicklung des BIP, die Anzahl an Erwerbstätigen und die Anzahl an Einwohnern seit 1992 bis 2021 für alle Landkreise in Deutschland.

Daten in das long-Format überführen

Allerdings 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":"2021") , 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 and 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...31`)))

## # A tibble: 6 × 37
##   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              .          .          .          .          .         
## # ℹ 31 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>, `1992...31` <dbl>, `1994...32` <dbl>, `1995...33` <dbl>,
## #   `1996...34` <dbl>, `1997...35` <dbl>, `1998...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 )

Konsistenzchecks

Wir haben bereits in dem Abschnitt “Pro-Kopf Verschuldung” die Einwohner zur Berechnung der Pro-Kopf-Verschuldung für 2021 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 2021 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 == 2021), by=c("Regionalschluessel")) %>%
  mutate(diff = Einwohner - einwohner) %>%
  arrange(desc(diff))

skim_without_charts(einwohner_check$diff)

Data summary

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	-30.9	2045.41	-6498	-194	-51	68	38719

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 16063                             3005.    159507      479301801.  2021
##  2 16066                             2520.    123603      311417890.  2021
##  3 16072                             3246.     56792      184342541.  2021
##  4 16054                             3887.     36182      140637349.  2021
##  5 03453                             1229.    175106      215124067.  2021
##  6 07131                             1664.    130836      217753384.  2021
##  7 08315                             2229.    266344      593631714.  2021
##  8 03458                              644.    132650       85434739.  2021
##  9 08125                             1209.    347729      420368560.  2021
## 10 01055                             2405.    202229      486299418.  2021
## 11 03454                              880.    330545      290945029.  2021
## 12 03451                              882.    126437      111561230.  2021
## 13 09564                             7243.    513452     3718958718.  2021
## 14 01054                             3206.    167710      537632139.  2021
## 15 13073                             3052.    225994      689657051.  2021
## 16 01060                             1943.    279547      543143122.  2021
## 17 09779                             1115.    134941      150404373.  2021
## 18 09776                             3376.     82492      278533668.  2021
## 19 08317                             2654.    433807     1151535035.  2021
## 20 03353                              416.    257024      106920639.  2021
## # ℹ 2 more variables: einwohner <dbl>, diff <dbl>

Die Differenz liegt im Durchschnitt bei 31 Einwohnern, was im Toleranzbereich bei den großen Städte mit mehr als 120.000 Einwohnern ist. Der einzige extreme Ausreiser ist der Wartburgkreis. Hier gehen die Einwohnerzahlen um 38 719 Personen auseinander. Laut Wikipedia waren die Daten bis 2020 ohne die Kreisfreie Stadt Eisenach. Dadurch gab es laut Wikipedia einen Sprung von 40 933 Personen, die ab 2021 zu den Einwohnern gezählt wurden, 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.

Kartenmaterial hinzufügen (optional)

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))

Datensätze zusammenführen

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 2021 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 2021
erwerb_kombi <- erwerb_long %>%
  filter(nchar(Regionalschluessel) == 5 & Jahr == 2021) %>%
  select(-Jahr)

# Anzahl an Einwohner für das Jahr 2021
einwohner_kombi <- einwohner_long %>%
  filter(nchar(Regionalschluessel) == 5 & Jahr == 2021) %>%
  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 == 2021) %>%
  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")

Übungsaufgaben

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.

1. Finden Sie in dem heruntergeladenen Datensatz heraus, was der Unterschied zwischen Arbeitnehmerentgelt und Bruttolöhne- und Gehälter ist.

2. Lesen Sie die für Sie relevante Tabelle Bruttolöhne- und Gehälter in R ein.

3. Bereinigen Sie die Tabelle, d.h. der Datensatz sollte danach tidy sein.

4. Berechnen Sie die Bruttolöhne pro Bundesland mit den Bruttolöhnen der einzelnen Landkreise als Konsistenzcheck.

5. Vergleichen Sie ihren Datensatz mit dem auf Github bereitgestellten Datensatz (“einkommen.rds”). Stimmen diese überein?

6. Verbinden Sie die Informationen zu den durchschnittlichen Einkommen mit dem gesamtdatensatz aus dem vorherigen Abschnitt.