Mit Datenaustausch haben wir täglich zu tun und hierbei begegnen uns ständig Schwierigkeiten. Im ersten Teil von Stolpersteine des täglichen Datenaustausches ging es um die Ursachen für Widersprüchlichkeiten in Daten. Dort haben wir uns damit auseinandergesetzt, wie verschiedene Zahlenformate, Textformate und Dateiformate zu Problemen in der Verarbeitung führen. Anhand von verschiedenen Dateiformaten haben wir Praxis-Beispiele vorgestellt, wie man diesen Schwierigkeiten vorbeugen kann und so den ständigen Datentransfer deutlich effizienter gestalten kann. Sollten Sie den ersten Teil versäumt haben, können Sie ihn hier noch mal nachlesen. In diesem zweiten Teil lesen Sie nun mehr zum konkreten Umgang mit den Daten. Nach einem Einstieg mit allgemeinen Techniken werde ich Ihnen Lösungswege mit Code-Beispielen zeigen, die sich an Leser mit zumindest leichten Programmier-Kenntnissen richten.
Technische Basis
Sie haben die Daten, die Sie interessieren, soweit identifiziert und möchten nun den Eingang Ihrer Daten weitgehend automatisieren?
Die Basis für die Werkzeuge meiner Wahl ist hierbei die Skriptsprache Python in Version 3.x. Welche Version genau spielt dabei eine eher untergeordnete Rolle. Es gibt alle verwendeten Module auch für Python 2, allerdings hat dieses einige Nachteile besonders im Umgang mit Strings unterschiedlichen Encodings. Ein Rundum-sorglos-Paket, das Python und die gängigsten Module für Datenverarbeitung bereits beinhaltet und so einen schnellen Einstieg ermöglicht, ist Anaconda. Damit ausgerüstet stellen wir uns den Widrigkeiten des Alltags in der Datenanalyse:
Der Werkzeugkasten
pandas
Die Python-Bibliothek pandas steht im Zentrum praktisch jeder Datenanalyse in Python. Sie beinhaltet neben statistischen Funktionen auch Datenformate, die tabellarische Daten hochperformant verarbeiten können, Schnittstellen für zahlreiche Datenquellen sowie eine ganze Reihe von Funktionen zur Bereinigung von Daten. Über die Möglichkeiten von pandas lassen sich ganze Bücher schreiben, jedoch beschränke ich mich hier auf einige wenige Features, die den Umgang mit problematischen Daten erleichtern. Der wichtigste Datentyp in pandas ist das DataFrame, das tabellarische Daten repräsentiert, und in dem jede Spalte einen bestimmten Datentyp beinhaltet.
Beispiel: CSV einlesen
Man erhält eine CSV-Datei folgenden Inhaltes (hier als multiline-String dargestellt):
csv_content = ''' spalte_1;spalte_2;spalte_3 1,5;2016-07-01;N/A 2,14;16.07.2023;4 '''
Die Schwierigkeiten hier sind:
- Spalte 1: Fließkommazahlen mit Komma als Dezimaltrennzeichen.
- Spalte 2: unterschiedliche Datumsformate
- Spalte 3: Fehlender Wert
Die Funktion, um mit pandas CSV-Inhalte zu verarbeiten, heißt read_csv, und sie kommt von Haus aus mit zahlreichen Parametern, um alle diese Schwierigkeiten zu umgehen:
import pandas as pd import numpy as np from io import StringIO fstream = StringIO(csv_content) table = pd.read_csv(fstream, delimiter=';', decimal=',', parse_dates=[1], dtype={'spalte_1':np.float64, 'spalte_3':np.float64}) print(table) print() print(table.dtypes)
ergibt:
spalte_1 spalte_2 spalte_3 0 1.50 2016-07-01 NaN 1 2.14 2023-07-16 4 spalte_1 float64 spalte_2 datetime64[ns] spalte_3 float64 dtype: object
Also das erwünschte Resultat. StringIO wird hier nur verwendet, um den CSV-Inhalt nicht erst in eine Datei schreiben zu müssen und hat mit der pandas-Funktionalität nichts zu tun. Die benutzten Keyword-Argumente von read_csv im Überblick:
- delimiter setzt das Zeichen zur Unterteilung der Spalten.
- decimal setzt das Dezimaltrennzeichen, womit Spalte 1 ausschließlich aus validen Fließkommazahlen besteht und auch so interpretiert wird.
- parse_dates ist eine Liste der Spaltenindices (beginnend mit 0!), die als Datum interpretiert werden sollen, womit Spalte 2 auch korrekt interpretiert wird.
- dtype setzt den Datentyp einzelner Spalten explizit, d.h. wären Spalten 1 oder 3 nicht als Fließkommazahl interpretierbar, würde ein Fehler geworfen.
Die Funktion hat zahlreiche weitere Argumente, um auf verschiedenste Eigenheiten zu reagieren. Ein Blick in die Dokumentation lohnt sich. Und: Das Problem des fehlenden Wertes musste gar nicht adressiert werden, da pandas bestimmte Werte automatisch als fehlend interpretiert.
Beispiel: Excel einlesen
Analog zu CSV-Dateien liest pandas auch tabellarische Daten aus Excel-Dateien mit der Funktion read_excel, wobei sich mittels Argumenten festlegen lässt, welche Sheets und Spalten als DataFrame eingelesen werden. Der Umfang ist etwas kleiner als bei read_csv, da Excel intern bereits Datentypen unterscheidet und die Spalten des DataFrames diese übernehmen. Ausnahme ist allerdings, wenn Zahlen als Textfeld gespeichert sind, denn dann ist Handarbeit gefragt.
Zahlkonvertierung
Konversion von String zu Zahl erfolgt mit der Funktion to_numeric. Zur Demonstration lesen wir das CSV-Objekt noch einmal ein, aber ohne das decimal-Argument:
fstream = StringIO(csv_content) table_from_excel = pd.read_csv(fstream, delimiter=';', parse_dates=[1], dtype={'spalte_3':np.float64})
Die erste Spalte ist nun als String interpretiert, so als wenn man eine Textspalte von Excel importiert. to_numeric könnte die Spalte direkt konvertieren, allerdings muss das Dezimaltrennzeichen in diesem Fall erst durch einen Punkt ersetzt werden:
table_from_excel.spalte_1 = pd.to_numeric(table_from_excel.spalte_1.str.replace(',', '.')) table_from_excel.spalte_1 0 1.50 1 2.14 Name: spalte_1, dtype: float64
Datumskonvertierung
Das selbe Problem gilt es zu lösen, wenn Datumsfelder als Strings eingelesen wurden:
fstream = StringIO(csv_content) date_column = pd.read_csv(fstream, delimiter=';')['spalte_2'] print(date_column) 0 2016-07-01 1 16.07.2023 Name: spalte_2, dtype: object
String-Spalten haben in pandas den dtype object. Die Funktion zur Datumskonvertierung heißt to_datetime und operiert wie to_numeric auf der ganzen Spalte:
pd.to_datetime(date_column) 0 2016-07-01 1 2023-07-16 Name: spalte_2, dtype: datetime64[ns]
Zu bemerken gibt es noch, dass numpy, auf dem die Datenstrukturen von pandas basieren, nicht zwischen Date und Datetime unterscheidet, d.h. bei Zeitangaben mit Datum und Uhrzeit wird dieselbe Methode verwendet.
Beautiful Soup
Manchmal werden Daten nur über eine Webseite bereit gestellt – ohne API. Man muss also erst die entsprechende Seite herunterladen, und sich dann durch den Quelltext hangeln, bis man seine Information zwischen unzähligen <table>-Tags etc. gefunden hat. Viel Spaß!
Okay, es ist eigentlich halb so wild, wenn man die richtigen Tools hat: Um HTTP-Inhalte zu laden, ist die Requests-Library der einfachste Weg. Ab dann muss man sich durch den HTML-Tag-Dschungel schlagen, wo Beautiful Soup dein bester Freund wird: Kein Ärger mit Encoding, da automatisch zu Unicode/UTF-8 konvertiert wird, und die Möglichkeit den kompletten Baum hierarchisch nach bestimmten Tags und Inhalten zu durchsuchen, ähnlich zu den Suchfunktionen in dokumentbasierten Datenbanken.
Beispiel: Daten aus HTML
Versuchen wir, das aktuelle Wetter in München von worldtimeserver.com zu parsen:
import requests from bs4 import BeautifulSoup url = 'http://www.worldtimeserver.com/current_time_in_DE.aspx?city=Munich' response = requests.get(url) parser = BeautifulSoup(response.content)
parser ist nun ein BeautifulSoup-Objekt, das mit allerlei Methoden durchsucht werden kann. Schaut man in den Quelltext, stößt man darauf, dass die Wetterinformation in einem Bereich (div) steckt, der eine Headline (h2) mit Inhalt „Weather Conditions“ hat:
<div class="grp"> <h2 style="text-align: left"> Current Weather Conditions in Munich </h2> <div class="desc"> <span style="font-size: 24pt"> <img alt="Passing clouds. Cool." id="skyicon" src="images/weather/v2_lg/14.gif" style="width:50px; height: 50px; padding-right:10px;vertical-align:middle;float:left;"/> Passing clouds. Cool. 48°F / 9°C </span> <br/> Conditions updated at Tue, 21 Feb 2017 22:42:08 CET </div> <div class="more"> <a href="weather_in_DE.aspx?forecastid=gn6559171&city=Munich"> click for forecast and more </a> <br/> </div> </div>
Man könnte sich die ganze Hierarchie entlang hangeln, aber dann wäre das Skript nicht gewappnet für den Fall kleiner Änderungen am Layout. Stattdessen sucht man nach eben dieser Headline, und arbeitet sich von dessen parent-div vor:
import re headline = parser.find('h2', text=re.compile('Weather Conditions')) parent_div = headline.find_parent('div') parent_div.find('div', class_='desc').span.text 'Passing clouds. Cool. 48°F / 9°C'
Ziel erreicht. Auf das Modul re wird im Abschnitt Reguläre Ausdrücke noch ausführlicher eingegangen. Hinweis: In neueren Versionen von Beautiful Soup wurde das Argument text der find-Methode in string umbenannt.
xlrd
Dass pandas die eierlegende Wollmilchsau für tabellarische Daten ist, wissen wir schon, aber was tut man, wenn Partner auf die Idee kommen, verschachtelte Daten in Excel-Dateien zu übermitteln, statt in JSON oder XML? Ein Excel-Sheet, bei dem Hierarchien mit eingerückten Spalten definiert werden, ist als Tabelle eingelesen nutzlos. Hierbei hilft xlrd, das auf die Elemente von Excel-Dateien zugreifen kann:
Beispiel: Excel mit Zwischenüberschriften
Wir haben folgendes Excel-Sheet mit Zwischenüberschriften für Name und ID einzelner Haendler:
Die Reihen ohne Inhalt in der zweiten Spalte läuten einen neuen Händler ein. Mit xlrd können wir über die Reihen iterieren und diese Information gesondert behandelt und der tabellarischen Information zufügen:
import xlrd # sheet-Objekt aus Datei einlesen sheet = xlrd.open_workbook('nested_excel.xlsx').sheet_by_index(0) # Helfervariablen content = [] current_merchant_name = None current_merchant_id = None header = None # ueber die Reihen iterieren for i_row in range(sheet.nrows): row = sheet.row(i_row) # header separat behandeln if header is None: header = [field.value for field in row] continue # Pruefen, ob die zweite Zelle Inhalt hat und ggf. den Haendler updaten if row[1].ctype == 0: if current_merchant_name is None: current_merchant_name = row[0].value elif current_merchant_id is None: current_merchant_id = row[0].value else: current_merchant_name = row[0].value current_merchant_id = None continue # Werte einlesen und in ein dictionary verpacken data = dict(zip(header, [field.value for field in row])) data.update({'merchant_name': current_merchant_name, 'merchant_id': current_merchant_id}) content.append(data) # Ausgabe als Dataframe print(pd.DataFrame(content)) Anzahl Einzelpreis Produkt merchant_id merchant_name 0 3 5.5 Produkt_A ID001 Haendler A 1 1 4.2 Produkt_B ID001 Haendler A 2 7 3.8 Produkt_C ID001 Haendler A 3 3 5.5 Produkt_A ID002 Haendler B 4 1 4.2 Produkt_B ID002 Haendler B 5 7 3.8 Produkt_C ID002 Haendler B 6 3 5.5 Produkt_A ID003 Haendler C 7 1 4.2 Produkt_B ID003 Haendler C 8 7 3.8 Produkt_C ID003 Haendler C
Reguläre Ausdrücke
Manchmal versteckt sich die gesuchte Information in einem Textfeld, steht aber nicht alleine, sondern mit zusätzlichen Zeichen. Dies kann beispielsweise eine Datumsangabe sein, die sich in einem Dateinamen versteckt; oder eine Telefonnummer, die mal mit Bindestrichen oder Leerzeichen garniert wird und mal mit “0” oder “+49” beginnt. Man kann nun versuchen, mit unzähligen WENN-Bedingungen eine Logik zu bauen, die daraus möglichst zuverlässig die gewünschten Werte zieht – oder man geht den zuverlässigsten Weg und benutzt reguläre Ausdrücke. Diese – im Englischen “regular expressions”, “RegExp” oder “regex” genannt – beschreiben ein Zeichenmuster, nach dem man beliebige Zeichenketten durchsuchen kann. In Python sind sie als re-Modul Teil der Standardbibliothek.
Dieses Prinzip ist sehr mächtig und kann vielen Leuten ihre Arbeit erleichtern, scheitert aber oft daran, dass die Ausdrücke im ersten Kontakt sehr kryptisch wirken und potentielle Nutzer abschrecken.
Beispiel: Steuernummern von PAYMILL
Wir extrahieren Steuernummer, Umsatzsteuer-ID und Handelsregisternummer von PAYMILL von http://www.paymill.org/de/impressum und machen uns dabei die Systematik der einzelnen Nummern zunutze:
- Steuernummer: 123/123/12345
- Umsatzsteuernummer: DE123456789 oder 123456789
- Handelsregisternummer: HRB 1234(…)
Wenn man diese in einem Fließtext sucht, wie geht man vor? Bestimmt gibt es kreative Lösungen, die mit etwas Aufwand zum Ziel führen, aber die schnellste Lösung sind vermutlich reguläre Ausdrücke:
-
d{3}/d{3}/d{5} -
(DE)?s?[0-9]{9} -
HRB d{4}d*
import re import requests from bs4 import BeautifulSoup response = requests.get('http://www.paymill.org/de/impressum') parser = BeautifulSoup(response.content) pattern_stnr = r'd{3}/d{3}/d{5}' pattern_ustnr = r'(DE)?s?d{9}' pattern_hrnr = r'HRB d{4}d*' print([x.group(0) for x in re.finditer(pattern_stnr, parser.text)]) print([x.group(0) for x in re.finditer(pattern_ustnr, parser.text)]) print([x.group(0) for x in re.finditer(pattern_hrnr, parser.text)]) ['143/169/70894'] ['DE308345749'] ['HRB 226526']
Die Erläuterung von links nach rechts jeweils:
- Steuernummer: Drei („{3}“) Ziffern („d“), ein Schrägstrich, drei Ziffern, Schrägstrich, fünf Ziffern.
- Umsatzsteuernummer: Die Zeichenfolge beginnt optional (“(x)?”) mit “DE”, dann ein optionaler Whitespace („s?“), darauf folgen Folgen Ziffern (“[0-9]”) und zwar 9 Stück (“{9}”). “[0-9]” ist eine alternative Schreibweise für Ziffern, verglichen mit „d“. In eckigen Klammern gibt man dabei alle erlaubten Zeichen an.
- Handelsregisternummer: „HRB“, Leerzeichen, vier („x{4}“) Ziffern („d“), beliebig viele („x*“) Ziffern
Dieser Artikel kann nur an der Oberfläche dessen kratzen, was mit regulären Ausdrücken möglich ist, aber seien Sie sich dieser Technik bewusst, und riskieren Sie die Auseinandersetzung damit, es kann sich sehr lohnen! Zu vielen Standardformaten findet man auch per Websuche bereits passende Ausdrücke.
Weiterführende Informationen:
- allgemeine Einführung
- Seine Fähigkeiten unter Beweis stellen kann man in einer Runde RegexGolf
- Oder beim Regex-Kreuzworträtsel
Weitere Helferlein
Jupyter Notebooks
Ein Problem beim automatisierten Importieren von Daten ist, dass es viele Spezialfälle gibt, die sich anfangs kaum überblicken lassen. Man kann also kein fertiges Programm schreiben in der Annahme, dass es daraufhin seinen Dienst ohne weiteres verrichtet. Gerade wenn man noch nicht mit den Datenstrukturen vertraut ist, entsteht der Programmablauf oft Schritt für Schritt, was bei ausführbaren Programmen ein eher unhandliches Verfahren ist. Hier kommen die Jupyter Notebooks ins Spiel:
Da der Python-Code nicht vorkompiliert werden muss, lässt er sich zeilenweise ausführen, wofür die Notebooks ein komfortables Interface bereitstellen. Dabei wird Code in Blöcken geschrieben und in beliebiger Reihenfolge ausgeführt, bei Bedarf auch mehrfach mit kontinuierlichen Korrekturen und Verbesserungen. Damit lässt sich z.B. der Zustand und Inhalt eines Datenobjektes an einer beliebigen Stelle einsehen und manipulieren. Die dadurch erreichte Flexibilität ermöglicht es, auf viele unerwartete Besonderheiten der Daten schnell zu reagieren.
Ein weiteres Plus ist der Zugang über den Browser, womit das Notebook auch beispielsweise auf einem Server laufen kann, der eine besonders gute Anbindung zu den benötigten Daten hat.
Wenn Sie Anaconda installiert haben, starten Sie den Dienst mit
jupyter notebook
und öffnen Sie die angezeigte URL im Browser Ihrer Wahl.
Airflow
Dieses Tool ist klar optional: Solange man nur eine Handvoll Skripte überwachen muss, benötigt man keinen zusätzlichen Serverdienst. Mit steigender Anzahl lohnt es sich aber sehr wohl, Ausführung und Protokollierung zentral zu steuern. Airflow, das ursprünglich von Airbnb entwickelt wurde, ist selbst in Python geschrieben und verwaltet periodische Programmausführungen inklusive Protokollierung, Historie, Web-Interface, Alarmen und Abhängigkeiten untereinander. Bei PAYMILL werden damit über 100 Unterprogramme, organisiert in über 20 Abhängigkeitsbäumen, für die tägliche Datenverarbeitung verwaltet.
Fazit
Mit den Erkenntnissen aus Teil 1 der Stolpersteine des alltäglichen Datenaustausches und den Praxis-Tipps aus diesem Artikel besitzen Sie nun das Rüstzeug, die Probleme beim Datentransfer zu identifizieren, zu vermeiden und Ihren Dateneingang zu automatisieren. Ich hoffe, Ihnen damit weitergeholfen zu haben und freue mich auf Ihr Feedback im Kommentarfeld.
