Abb. 1: Links die Ausgangstabelle der Statistik der Bundesagentur für Arbeit (https://statistik.arbeitsagentur.de/), rechts die Tabelle im Zielformat

Hinzu kommt, dass die Daten über das oben verlinkte Online-Portal nur jeweils für einen einzelnen Erhebungsmonat bereitgestellt werden. Möchte man Daten aus mehreren Monaten zu einer Zeitreihe verknüpfen, steht man daher vor der Aufgabe, die entsprechenden Dateien einzeln herunterzuladen und in einem zeitaufwendigen (und fehleranfälligen) Prozess jeweils nach dem exakt gleichen Schema in eine Datenbanktabelle umzuformen. Wird dabei manuell vorgegangen, kann diese Aufgabe ab einer gewissen Anzahl von Tabellen durchaus mehrere Stunden Zeit in Anspruch nehmen - Zeit, die bei der Analyse der Daten wohl sinnvoller investiert wäre und die wir mit selbstentwickelten Skripten zu großen Teilen einsparen können.

Für das Erstellen unseres Skripts nutzen wir in diesem Beispiel eine browserbasierte Open Source Software namens Jupyter Notebook. Der zweite Teil des Tutorials leitet sie Schritt für Schritt durch den Installationsprozess und erklärt die grundlegenden Bedienungsfunktionen.

Das Jupyter Notebook ist eine interaktive Entwicklungsumgebung u.a. für die Programmiersprache Python, die aufgrund ihrer eingängigen Syntax in den vergangenen Jahren eine rasche Verbreitung in datenintensiven Tätigkeitsfeldern gefunden hat. (Quelle)  Jupyter Notebooks erlauben es dem Anwender, ihren Python-Code in interaktiven Notebooks zu schreiben, zu kommentieren und auszuführen. Das Besondere daran: Das Ergebnis jedes einzelnen Code-Bausteins - wir benutzen im weiteren Verlauf den Begriff input-Feld - wird dem Anwender unmittelbar nach der Ausführung in einem output-Feld angezeigt:

In [1]:

Out [1]:

Diese intuitive und responsive Form der Befehlseingabe erleichtert insbesondere Nutzern ohne bisherige Programmiererfahrung den Einstieg in die Erstellung eigener Skripte. Ein weiterer Vorteil: Obwohl es sich um eine browserbasierte Software handelt, die auf jedem Endgerät installiert werden kann, werden die Notebooks als lokale Dateien mit der Endung .ipynb auf der Festplatte des Anwenders gespeichert. (Quelle)

Alternative Entwicklungsumgebunden für Python, die sich ebenfalls für das Entwickeln von Datenaufbereitsskripten eignen, finden Sie unter diesem Link.

Nachdem wir die Software installiert und - wie im Video beschrieben - ein neues Notebook auf unserer lokalen Festplatte angelegt haben, können wir damit beginnen, unsere Daten in das Notebook zu laden.

Doch bevor wir loslegen, importieren wir zunächst die Python-Bibliotheken, mit denen wir im weiteren Verlauf arbeiten werden:

• Die Bibliothek pandas enthält alle notwendigen Werkzeuge zur Transformation unseres Datensatzes
• Die Bibliothek xlrd ermöglicht uns den unkomplizierten Import von Excel-Dateien

In [2]:

In einem ersten Schritt laden wir nun unsere Rohdaten in das Notebook. Wir haben schon im Vorfeld einen kurzen Blick in die Exceldatei geworfen und dabei bemerkt, dass es sich um eine Arbeitsmappe mit mehreren Datenblättern handelt. Wir benötigen für unsere Zwecke nur eines dieser Datenblätter und werden dies beim Import entsprechend berücksichtigen:

Über den Befehl pandas.read_excel() lesen wir das in der Excel-Datei but-d-0-201909-xlsx.xlsx enthaltene Datenblatt 2 Leistungsarten aus und speichern es innerhalb unseres Notebooks als ein Tabellenobjekt mit dem Namen tabelle. Mit dem kleinen Zusatz na.values=['.','*'] sorgen wir außerdem dafür, dass alle Punkte und Sterne innerhalb der Tabelle schon beim Dateiimport mit dem Wert "NaN" (Pandas' Standardzeichen für fehlende Werte) ersetzt werden. Wenn wir die fertige Tabelle später als .csv-Datei speichern, werden die Zellen mit einem "NaN"-Wert automatisch in leere Zellen umgewandelt. Wenn die Unterscheidung in fehlende und anonymisierte Werte erhalten bleiben soll oder der Anwender anders mit ihnen verfahren möchte, kann der entsprechende Zusatz entfernt werden.

Wichtige Anmerkung für den Dateiimport: Unsere Excel-Datei und das Notebook, mit dem wir arbeiten, müssen sich in demselben Verzeichnis befinden. Andernfalls muss in den Klammern nicht bloß der Dateiname, sondern der genaue Dateipfad zur Exceldatei angegeben werden.

Zusammengefasst lautet der Befehl für den Datenimport in unserem Fall also folgendermaßen:

In [3]:

Als nächstes möchten wir uns einen ersten Eindruck über den Aufbau der Tabelle verschaffen. Mit dem Befehl tabelle.head(20) lassen wir uns deshalb die ersten 20 Zeilen unserer Tabelle anzeigen:

In [4]:

Out [4]:

Schon auf den ersten Blick sehen wir eine sehr unaufgeräumte Tabelle mit vielen leeren Zellen, Sonderzeichen, überflüssigen Zeilen, unbenannten Spaltenköpfen usw. Wir merken uns diese Beobachtungen genau, denn auf ihrer Grundlage werden wir später unsere Tabelle umwandeln.

Dasselbe machen wir jetzt noch mit dem Tabellenende. Der Befehl tabelle.tail(10) zeigt uns die letzten 10 Zeilen der Tabelle an:

In [5]:

Out [5]: 

Auch am Tabellenende fallen uns fünf Zeilen auf, die für unsere finale Datenbanktabelle nicht benötigt werden und die wir ebenfalls werden entfernen müssen.

Insgesamt haben wir nun ausreichend Informationen gesammelt, um unsere Tabelle im nächsten Abschnitt grob für die weitere Verarbeitung vorzubereiten.

In einem ersten Schritt zur Datenaufbereitung löschen wir nun zunächst eine Reihe überflüssiger Zeilen, um die Tabelle etwas übersichtlicher zu gestalten. Hierzu bietet Python zwei verschiedene Befehle an, die jeweils unterschiedlichen Zwecken dienen:

Große Anzahl nicht benötigter Zeilen löschen

Der Befehl .iloc[] wird verwendet, wenn eine Tabelle grob auf einen bestimmten Zeilenbereich "zugeschnitten" werden soll. In unserem Beispiel sind dies die Zeilen 7 bis 432, in denen unsere Messwerte enthalten sind. Die Informationen in den Zeilen davor und danach sind für unsere Zwecke überflüssig. Der Befehl .iloc[7:432] kann also gelesen werden als: "Nur den Zeilenbereich 7 bis 432 übernehmen".

Wie auch im weiteren Verlauf unseres Notebooks speichern wir das Ergebnis dieser Operation in einem neuen Tabellenobjekt (der tabelle1), damit unsere Ausgangstabelle nicht überschrieben wird:

In [6]:

Out [6]: 

425 rows × 7 columns

Einzelne überflüssige Zeilen löschen

Der Befehl .drop() hingegen wird für feinere Anpassungen verwendet, also für das Löschen einzelner Zeilen. In unserem Fall sind dies die Zeilen 9 und 10, die keinerlei Werte enthalten. Der Zusatz axis=0 ist wichtig, da sich mit demselben Befehl auch ganze Spalten löschen lassen (nämlich mit dem Zusatz axis=1).

In [7]:

Out [7]: 

Die Tabelle ist damit schon ein wenig übersichtlicher geworden, überflüssige Zeilen am Anfang und am Ende wurden entfernt.

Nicht benötigte Spalten löschen

Als nächstes nehmen wir uns die Spalten vor und löschen in einem ersten Schritt all jene Spalten, die wir für unsere Zieltabelle nicht benötigen:

• Die Spalte "Unnamed: 1" enthält einen Regionalcode, den wir nicht weiter benötigen (alternativ könnte auch der Regionalcode beibehalten und stattdessen die Spalte "Unnamed: 0" mit den ausgeschriebenen Regionsbezeichnungen gelöscht werden).
• Die Spalte "Unnamed: 3" enthält die aggregierten Werte ohne Unterscheidung nach Altersgruppen. Diese würden in unserer Zieltabelle stören und werden deshalb ebenfalls entfernt. Wichtig: Auf diesem Wege landen wir im Ergebnis bei einer Zieltabelle, bei der (aufgrund fehlender oder anonymisierter Werte in einzelnen Zeilen) nicht für jede Gebietseinheit davon ausgegangen werden kann, dass sich die Anzahl der Leistungsberechtigten in den drei Altersgruppen zur Gesamtanzahl der tatsächlich Leistungsberechtigten summiert. Beim Laden der Daten in unsere Datenbank sollte diese Information unbedingt dokumentiert werden. Außerdem empfiehlt es sich, in den Einstellungsoptionen unserer Datenbanksoftware festzulegen, dass die Daten nicht über die Altersgruppen aggregiert werden können. Ein leicht abgewandeltes Skript, das dieses Problem auf alternative Weise behebt, finden Sie ganz am Ende dieses Notebooks.

Mit dem Befehl .drop(columns = []) löschen wir die genannten Spalten aus der Tabelle:

In [8]:

Out [8]: 

Die groben Aufräumarbeiten sind damit abgeschlossen. Wir können uns nun daran begeben, unsere Daten in das gewünschte Zielformat umzuformen.

An dieser Stelle muss nun der Anwender entscheiden, wie seine Tabelle im Ergebnis aufgebaut sein soll und welche Daten er in die fertige Tabelle übernehmen möchte. Wir entscheiden uns in diesem Beispiel dafür, die Daten zum "Bestand Leistungsberechtigte SGB II unter 25 Jahre" zu löschen und bloß die Daten zu den "Leistungsberechtigten mit Anspruch auf mindestens eine Leistung zu Bildung und Teilhabe" nach Altersgruppen zu übernehmen.

Weitere überflüssige Spalten löschen

Mit dem schon bekannten Befehl .drop() löschen wir dazu als erstes die nicht länger benötigte Spalte "Unnamed: 2" :

In [9]:

Out [9]: 

Benötigte Spalten umbenennen

Mit dem Befehl .rename() können wir nun, nachdem wir uns über den Aufbau der Zieltabelle im Klaren sind, außerdem unsere Spaltenköpfe umbenennen. Die bestehenden Spaltentitel wurden beim Dateiimport automatisch vergeben und haben bislang eher als Platzhalter fungiert.

Um genau zu steuern, welche Spalten wie umbenannt werden, spezifizieren wir unseren Befehl .rename() mit einem sogenannten "Wörterbuch", also einer Reihe von Wortpaaren nach dem Schema {"Alter Spaltenname":"Neuer Spaltenname",...}:

In [10]:

Out [10]: 

Weitere überflüssige Zeilen löschen

Nachdem wir nun die Information, welche Spalte die Daten zu welcher Altersgruppe enthält, in die Spaltenköpfe übernommen haben, werden die drei Zeilen an den Indexpositionen 7, 8 und 11 nicht länger benötigt und können mit dem Befehl .drop() ebenfalls entfernt werden:

In [11]:

Out [11]: 

Das Ergebnis sieht bereits vielversprechend aus. Ein letzter Schritt, bevor wir uns an die Herstellung des gewünschten Zielformats begeben, besteht nun darin, unsere Daten mit einer Spalte für die Monatsangabe zu ergänzen. Denn wie eingangs gesagt, planen wir in diesem Beispielszenario, die fertig aufbereiteten Tabellen aus den einzelnen Monaten anschließend zu einer Zeitreihe zu verknüpfen.

Daten anreichern: Spalte einfügen und automatisch füllen

Da unsere Ausgangstabelle ohnehin nur Daten aus einem Monat enthielt, war darin keine gesonderte Spalte für den Erhebungsmonat vorgesehen. Mit dem Befehl .insert() können wir diese nachträglich einfügen und durchgehend mit dem Wert "09_2019" füllen. Die 1 in den Klammern hinter dem Befehl legt dabei fest, dass die neue Spalte an der Position 1 (also rechts neben der Spalte "Region" an der Position 0) eingefügt werden soll :

In [12]:

Out [12]: 

Umwandlung vom "breiten" ins "lange" Tabellenformat

Jetzt wird es interessant, denn wir beginnen nun damit, unser Daten in das "lange" Tabellenformat umzuwandeln: Im Ergebnis möchten wir eine Spalte mit unseren Messwerten erhalten (die spätere Kennzahlenspalte) und je eine weitere Spalte für jedes Differenzierungsmerkmal, nach dem unsere Daten vorliegen (die späteren "Schlüsselspalten").

Die dazu notwendige Umwandlungsoperation lässt sich schematisch folgendermaßen darstellen: Aus einer "breiten" Ausgangstabelle mit einer Spalte je Altersgruppe ...

... soll eine "lange" Zieltabelle hergestellt werden, welche die Ausprägungen des Merkmals "Altersgruppe" in einer Spalte zusammenfasst:

Daten in das "lange" Tabellenformat überführen

Manuell ist dieser Vorgang schon für eine Tabelle recht mühsam durchzuführen (von der Fehleranfälligkeit ganz abgesehen). Wenn aber, wie in unserem Beispiel, gleich mehrere Tabellen (nämlich eine pro Erhebungsmonat) einzeln nacheinander umgeformt werden müssen, kann dies schnell sehr viel Zeit beanspruchen - Zeit, welche die meisten Monitorer lieber für die gründliche Analyse ihrer Daten aufwenden würden.

Zum Glück lässt sich dieser Prozess mit Python sehr leicht automatisieren. Im Grunde genügen hierfür vier Zeilen Code:

1. In einem ersten Schritt erstellen wir eine Liste mit den Namen von all denjenigen Merkmalsspalten, die bereits im richtigen ("langen") Format vorliegen und von der folgenden Umformung nicht berührt werden sollen. In unserem Beispiel also die Spalten "Region" und "Monat":

In [13]:

     2. Mit dem Befehl set_index() werden die Spalten in dieser Liste nun zum Tabellenindex erklärt und dienen uns fortan als "Anker" für die Umformung:

In [14]:

     3. In einem dritten Schritt werden mit dem Befehl .stack() die drei Spalten mit den Altersgruppen untereinander gestapelt (von englisch to stack: stapeln).
         Mit dem Zusatz dropna=False verhindern wir, dass dabei Zeilen mit fehlenden Werten standardmäßig gelöscht werden:

In [15]:

     4. Abschließend wird mittels .reset_index() der im zweiten Schritt erstellte Index wieder in einen normalen, numerierten Tabellenindex zurückgesetzt:

In [16]:

Out [16]: 

Fertig! Liegen diese vier Code-Bausteine einmal vorbereitet in der Schublade, brauchen Tabellenabschnitte nicht länger manuell in Excel hin- und hergeschoben werden, sondern können innerhalb von Sekunden automatisch in das "lange" Format gebracht werden.

Im Prinzip trennen uns jetzt bloß noch die falsch benannten Spaltenköpfe von der gewünschten Zieltabelle. Wir greifen erneut zu .rename() und erhalten im Ergebnis eine Tabelle, die so bereits als CSV-Datei exportiert werden könnte:

In [17]:

Out [17]: 

Sonderzeichen suchen und ersetzen

In einem vorletzten Schritt ersetzen wir jetzt noch in der Spalte "Region" alle Umlaute wie "ä" oder "ü" durch zwei einfache Vokale und verhindern dadurch, dass es später Probleme bei der Dateispeicherung im .csv-Format geben wird. Wir nutzen hierzu erneut den Befehl .replace() in Kombination mit einem Wörterbuch, das die Wortpaare für die "Suchen-Ersetzen"-Operation enthält:

In [18]:

Um das Ergebnis zu überprüfen, lassen wir uns mittels .unique() die distinkten Einträge in der Spalte "Region" anzeigen:

In [19]:

Out [19]: 

Als letzten Schritt vor dem Datei-Export gehen wir in unserem Beispiel davon aus, dass die fertige Datei nicht sämtliche Daten enthalten soll, sondern nur jene für die Kreise und kreisfreien Städte im Bundesland Rheinland-Pfalz. Kurz: Wir wollen die Tabelle vor dem Speichern filtern.

Wenn sämtliche Daten in der Tabelle übernommen werden sollen, könnte dieser Schritt theoretisch entfallen. Für die Weiterverarbeitung der Daten in komBi sollte jedoch in jedem Fall darauf geachtet werden, dass die Daten in der fertigen Tabelle nur auf der niedrigsten verfügbaren Raumebene vorliegen.

Tabelle nach bestimmten Werten filtern

Python bietet eine Vielzahl von Methoden an, um Tabellen zu filtern - je nach dem, ob sehr komplexe oder eher allgemeine Filteranweisungen formuliert werden, ob die gefilterten Werte gelöscht oder bloß "ausgeblendet" werden sollen etc.

In unserem Beispiel möchten wir eine relativ große Anzahl an Werten (nämlich die Namen der 36 Kreise und kreisfreien Städte in Rheinland-Pfalz) als Filterkriterien auf die Spalte "Region" anwenden. Wir entscheiden uns deshalb dafür, diese Werte zunächst in einer Liste namens liste_rlp zu speichern und mit dem Befehl .isin() anschließend den Abgleich zwischen der Liste und der Tabellenspalte "Region" zu vollziehen.

Im Ergebnis erhalten wir eine gefilterte Tabelle, die bloß noch die jene Zeilen enthält, bei denen es eine Übereinstimmung zwischen den Einträgen der liste_rlp und den Werten der Tabellenspalte "Region" gab:

In [20]:

Fertige Tabelle als csv-Datei exportieren

Abschließend wird die fertige Tabelle mit dem Befehl .to_csv() exportiert. Der Zusatz index=False sorgt dafür, dass der nummerierte Tabellenindex nicht in die finale csv-Datei übernommen wird, während der Ausdruck sep = ";" das Semikolon als das Trennungszeichen für die Datenfelder der csv-Datei definiert.

Nach Ausführung des Befehls erscheint die Tabelle sofort als CSV-Datei mit dem Namen "but_2019.csv" in unserem Notebook-Verzeichnis:

In [21]:

Skripte werden vorrangig dann entwickelt, wenn es darum geht, ...

• eine große Anzahl nahezu identisch aufgebauter Tabellen auf einmal umzuwandeln
• die Aufbereitung eines Datensatzes zu automatisieren, der fortlaufend mit neuen Daten aktualisiert wird

In beiden Fällen rentiert sich der Zeitaufwand für die Skriptentwicklung dadurch, dass das Skript mehrfach und/oder fortlaufend verwendet wird. Um bei unserem Beispiel zu bleiben: Liegt das Skript erstmal vor, braucht man beim Erscheinen der aktuellen Monatszahlen zu den BuT-Leistungsberechtigen bloß noch minimale Änderungen am Skript vorzunehmen und kann die neue Datei anschließend innerhalb von Sekunden in das definierte Zielformat umwandeln.

Um dabei möglichst effizient vorzugehen, werden sich die meisten Anwender ihr Skript nicht in der oben dargelegten Form - mit ausführlicher Kommentierung und auf viele kurze Blöcke aufgeteilt - speichern, sondern in einer zusammengefassten Form, die bloß kurze Hinweise auf die anzupassenden Parameter enthält. Unten finden Sie deshalb den oben kleinteilig besprochenen Code (abzüglich der Befehle, die uns die Zwischenergebnisse anzeigen) nochmal in der Gesamtschau:

In [22]:

Das oben zusammengefasste Skript kann nun verwendet werden, um die heruntergeladenen Excel-Dateien mit den Daten der einzelnen Erhebungszeiträume rasch in das gewünschte, einheitliche Zielformat umzuwandeln. Die einzeln gespeicherten CSV-Dateien können anschließend in einer finalen Tabelle zusammengefasst und als Indikator in komBi angelegt werden.

Im Abschnitt Grobe Aufräumarbeiten: Nicht benötigte Zeilen und Spalten löschen wurde auf das Problem hingewiesen, dass die fertige Tabelle aufgrund fehlender und/oder anonymisierter Werte in einigen Fällen keine zuverlässige Aggregation über die Altergsgruppen erlaubt: Die Summe der Leistungsberechtigten in den drei Altersgruppen ergibt nicht für jede Kommune die tatsächliche Gesamtzahl der Leistungsberechtigten. Bei der Weiterverarbeitung der Daten in komBi sollte dieser Hinweis unbedingt dokumentiert werden.

Eine alternative Lösung besteht darin, der Tabelle in der Spalte "Altersgruppen" eine künstliche Ausprägung namens "nz" (Abkürzung für "nicht zuzuordnen") hinzuzufügen, in der die Anzahl der fehlenden und anonymisierten Werte (also die Differenz zwischen der Gesamtzahl und der Summe der drei Altergruppen) berechnet wird. Die Summe dieser vier Ausprägungen (drei Altersgruppen + "nz") ergibt dann in jedem Fall die korrekte Gesamtzahl, was eine Aggregation über die Altersgruppen prinzipiell möglich macht.

Wir übernehmen dazu einfach den oben entwickelten Code bis zur Erstellung der "zieltabelle2" und achten darauf, in diesem Fall die Spalte mit den Gesamtwerten vorerst nicht zu entfernen:

In [23]:

Out [23]:

Noch bevor wir die Tabelle in das "flache" Zielformat umwandeln, erweitern wir dann unsere Tabelle um eine weitere Spalte, in der wir die Differenz zwischen der Spalte "Insgesamt" und den Spalten der drei Altersgruppen berechnen:

In [24]:

Out [24]: 

Anschließend fahren wir weitgehend genauso fort, wie mit der ursprünglichen Tabelle: Die Monatsspalte wird ergänzt, die Tabelle ins "lange" Zielformat umgewandelt und abschließend gefiltert:

In [25]:

Out [25]:

Das Ergebnis lässt sich im obigen Tabellenausschnitt am Beispiel der kreisfreien Stadt Zweibrücken überprüfen (Zeile 727): Während in der ersten Variante unserer Tabelle ersichtlich wurde, dass die Daten für diese Raumeinheit nicht nach Altersgruppen differenziert vorliegen, wird in dieser zweiten Tabellenvariante über den Schlüssel "nz" die Gesamtzahl der Leistungsberechtigten aufgeführt.

Auch diese alternative Variante unseres Skripts fassen wir abschließend in einer einzelnen Zelle zusammen:

In [26]: