How To Remotely Send R and Python Execution to SQL Server from Jupyter Notebooks

Introduction

Did you know that you can execute R and Python code remotely in SQL Server from Jupyter Notebooks or any IDE? Machine Learning Services in SQL Server eliminates the need to move data around. Instead of transferring large and sensitive data over the network or losing accuracy on ML training with sample csv files, you can have your R/Python code execute within your database. You can work in Jupyter Notebooks, RStudio, PyCharm, VSCode, Visual Studio, wherever you want, and then send function execution to SQL Server bringing intelligence to where your data lives.

This tutorial will show you an example of how you can send your python code from Juptyter notebooks to execute within SQL Server. The same principles apply to R and any other IDE as well. If you prefer to learn through videos, this tutorial is also published on YouTube here:


 

Environment Setup Prerequisites

  1. Install ML Services on SQL Server

In order for R or Python to execute within SQL, you first need the Machine Learning Services feature installed and configured. See this how-to guide.

  1. Install RevoscalePy via Microsoft’s Python Client

In order to send Python execution to SQL from Jupyter Notebooks, you need to use Microsoft’s RevoscalePy package. To get RevoscalePy, download and install Microsoft’s ML Services Python Client. Documentation Page or Direct Download Link (for Windows).

After downloading, open powershell as an administrator and navigate to the download folder. Start the installation with this command (feel free to customize the install folder): .\Install-PyForMLS.ps1 -InstallFolder “C:\Program Files\MicrosoftPythonClient”

Be patient while the installation can take a little while. Once installed navigate to the new path you installed in. Let’s make an empty folder and open Jupyter Notebooks: mkdir JupyterNotebooks; cd JupyterNotebooks; ..\Scripts\jupyter-notebook

Create a new notebook with the Python 3 interpreter:

 

To test if everything is setup, import revoscalepy in the first cell and execute. If there are no error messages you are ready to move forward.

Database Setup (Required for this tutorial only)

For the rest of the tutorial you can clone this Jupyter Notebook from Github if you don’t want to copy paste all of the code. This database setup is a one time step to ensure you have the same data as this tutorial. You don’t need to perform any of these setup steps to use your own data.

  1. Create a database

Modify the connection string for your server and use pyodbc to create a new database.

  1. Import Iris sample from SkLearn

Iris is a popular dataset for beginner data science tutorials. It is included by default in sklearn package.

  1. Use RecoscalePy APIs to create a table and load the Iris data

(You can also do this with pyodbc, sqlalchemy or other packages)

Define a Function to Send to SQL Server

Write any python code you want to execute in SQL. In this example we are creating a scatter matrix on the iris dataset and only returning the bytestream of the .png back to Jupyter Notebooks to render on our client.

Send execution to SQL

Now that we are finally set up, check out how easy sending remote execution really is! First, import revoscalepy. Create a sql_compute_context, and then send the execution of any function seamlessly to SQL Server with RxExec. No raw data had to be transferred from SQL to the Jupyter Notebook. All computation happened within the database and only the image file was returned to be displayed.

While this example is trivial with the Iris dataset, imagine the additional scale, performance, and security capabilities that you now unlocked. You can use any of the latest open source R/Python packages to build Deep Learning and AI applications on large amounts of data in SQL Server. We also offer leading edge, high-performance algorithms in Microsoft’s RevoScaleR and RevoScalePy APIs. Using these with the latest innovations in the open source world allows you to bring unparalleled selection, performance, and scale to your applications.

Learn More

Check out SQL Machine Learning Services Documentation to learn how you can easily deploy your R/Python code with SQL stored procedures making them accessible in your ETL processes or to any application. Train and store machine learning models in your database bringing intelligence to where your data lives.

Other YouTube Tutorials:

Bringing intelligence to where data lives: Python & R embedded in T-SQL

Introduction

Did you know that you can write R and Python code within your T-SQL statements? Machine Learning Services in SQL Server eliminates the need for data movement. Instead of transferring large and sensitive data over the network or losing accuracy with sample csv files, you can have your R/Python code execute within your database. Easily deploy your R/Python code with SQL stored procedures making them accessible in your ETL processes or to any application. Train and store machine learning models in your database bringing intelligence to where your data lives.

You can install and run any of the latest open source R/Python packages to build Deep Learning and AI applications on large amounts of data in SQL Server. We also offer leading edge, high-performance algorithms in Microsoft’s RevoScaleR and RevoScalePy APIs. Using these with the latest innovations in the open source world allows you to bring unparalleled selection, performance, and scale to your applications.

If you are excited to try out SQL Server Machine Learning Services, check out the hands on tutorial below. If you do not have Machine Learning Services installed in SQL Server,you will first want to follow the getting started tutorial I published here: 

How-To Tutorial

In this tutorial, I will cover the basics of how to Execute R and Python in T-SQL statements. If you prefer learning through videos, I also published the tutorial on YouTube.

Basics

Open up SQL Server Management Studio and make a connection to your server. Open a new query and paste this basic example: (While I use Python in these samples, you can do everything with R as well)

Sp_execute_external_script is a special system stored procedure that enables R and Python execution in SQL Server. There is a “language” parameter that allows us to choose between Python and R. There is a “script” parameter where we can paste R or Python code. If you do not see an output print 7, go back and review the setup steps in this article.

Parameter Introduction

Now that we discussed a basic example, let’s start adding more pieces:

Machine Learning Services provides more natural communications between SQL and R/Python with an input data parameter that accepts any SQL query. The input parameter name is called “input_data_1”.
You can see in the python code that there are default variables defined to pass data between Python and SQL. The default variable names are “OutputDataSet” and “InputDataSet” You can change these default names like this example:

As you executed these examples, you might have noticed that they each return a result with “(No column name)”? You can specify a name for the columns that are returned by adding the WITH RESULT SETS clause to the end of the statement which is a comma separated list of columns and their datatypes.

Input/Output Data Types

Alright, let’s discuss a little more about the input/output data types used between SQL and Python. Your input SQL SELECT statement passes a “Dataframe” to python relying on the Python Pandas package. Your output from Python back to SQL also needs to be in a Pandas Dataframe object. If you need to convert scalar values into a dataframe here is an example:

Variables c and d are both scalar values, which you can add to a pandas Series if you like, and then convert them to a pandas dataframe. This one shows a little bit more complicated example, go read up on the python pandas package documentation for more details and examples:

You now know the basics to execute Python in T-SQL!

Did you know you can also write your R and Python code in your favorite IDE like RStudio and Jupyter Notebooks and then remotely send the execution of that code to SQL Server? Check out these documentation links to learn more: https://aka.ms/R-RemoteSQLExecution https://aka.ms/PythonRemoteSQLExecution

Check out the SQL Server Machine Learning Services documentation page for more documentation, samples, and solutions. Check out these E2E tutorials on github as well.

Would love to hear from you! Leave a comment below to ask a question, or start a discussion!

Analyse der Netzwerktopologie des Internets auf Basis des IPv4-Protokolls

Wie kommen Daten die man via Internet quer durch die Welt sendet eigentlich an ihr Ziel? Welchen Weg nehmen beispielsweise die Datenpakete, wenn ich von mir zu Hause eine Datei an meinen Nachbarn ein Haus weiter sende? Wie groß ist der “Umweg”, den die Daten nehmen? Und macht es eigentlich einen Unterschied, ob ich www.google.de, www.google.com oder www.google.nl aufrufe, oder gehen alle Suchanfragen sowieso an dasselbe Ziel?

Fragen wie diese lassen sich durch eine Kombination von Tools wie traceroute oder tracepath und geoiplookup beantworten und unter Verwendung des Python-Paketes geoplotlib sogar graphisch auf einer Weltkarte darstellen. Die so gewonnenen Ergebnisse zeigen Teile der Netzwerktopologie des Internets auf und führen zu interessanten, teils unerwarteten Erkenntnissen.

Ziel dieses Artikels soll sein, ein möglichst einfaches Tutorial zum selber mitbasteln bereit zu stellen. Die einzelnen Schritte die hierfür notwendig sind, werden möglichst einfach verständlich dargestellt und erklärt, trotzdem sind zum vollständigen Verständnis grundlegende Kenntnisse in Python sowie der Kommandozeile hilfreich. Er richtet sich aber auch an alle, die sich einfach einmal etwas in ihrer virtuellen Umgebung „umschauen“ möchten oder einfach nur an den Ergebnissen interessiert sind, ohne sich mit den Details und wie diese umgesetzt werden, auseinander setzen zu wollen.  Am Ende des Artikels werden die einzelnen Skripte des Projekts als zip-Datei bereitgestellt.

Hinweis: Diese Anleitung bezieht sich auf ein Linux-System und wurde unter Ubuntu getestet. Windows-User können beispielsweise mit dem Befehl tracert (als Ersatz für traceroute) ähnliche Ergebnisse erziehlen, jedoch muss dann das Parsing der IP-Adressen abgeändert werden.

1. Grundsätzliches Erkunden der Route, die ein Datenpaket nimmt

Hierfür wird ein Programm wie traceroute, tracepath oder nmap benötigt, welches durch Versenden von „abgelaufenen Datenpaketen“ die Hosts „auf dem Weg“ zum Ziel dazu bringt, ihre IPv4-Adresse zurück zu geben. In diesem Artikel wird beispielhaft traceroute verwendet, da dieses unter den meisten Linux-Versionen bereits zur „Grundausstattung“ gehört und somit für diesen Schritt keine weitere Software installiert werden muss. Die Verwendung von traceroute folgt der Syntax:

Als Ziel muss hier die IP-Adresse bzw. der Domainname des Zielrechners angegeben werden. Ein Beispiel soll dies vereinfachen:

Im Beispiel wird die Route zum Hostrechner mit der Domain www.google.de ermittelt. In der ersten Spalte der Ausgabe ist die Nummer des jeweiligen „Hops“ zu sehen. Wichtig ist insbesondere die zweite Spalte, welche die IPv4-Adresse des jeweiligen Rechners auf dem Weg zum Ziel darstellt. Die folgenden Spalten enthalten weitere Informationen wie Antwortzeiten der jeweiligen Server und die IP-Adressen der Folge-Server.

Um die Ausgabe in eine Form umzuwandeln, welche später einfacher von Python gelesen werden kann, muss diese noch ausgelesen werden (Parsing). zuerst soll die erste Zeile der Ausgabe herausgeschnitten werden, da diese zwar informativ, jedoch kein Teil der eigentlichen Route ist. Dies kann sehr einfach durchgeführt werden, indem die Ausgabe des traceroute-Befehls an einen Befehl wie beispielsweise sed „gepiped“ (also weitergeleitet) wird. Die dabei entstehende Pipe sieht dann wie folgt aus:

Um bei unserem Beispiel mit der Route zu www.google.de zu bleiben, sieht der Befehl und die Entsprechende Ausgabe wie folgt aus:

Anschließend soll die zweite Spalte der Ausgabe herausgeschnitten werden. Dies ist am einfachsten mit dem Befehl awk zu bewerkstelligen. Das Prinzip dahinter ist das gleiche wie im obigen Schritt: die Ausgabe des vorherigen Befehls wird dem Befehl awk als Eingabe weitergeleitet, womit der gesamte Befehl nun wie folgt aussieht:

Bezogen auf das google-Beispiel sehen Ein- und Ausgabe nun so aus:

Im letzten Schritt sollen die einzelnen IP-Adressen durch Leerzeichen getrennt in eine einzelne Zeile geschrieben werden. Sinn dieses Schrittes ist, dass später viele Zielrechner nacheinander aus einer Datei eingelesen werden können und jede Route zu einem Zielrechner als eine einzelne Zeile in eine Zieldatei geschrieben wird.
Auch dieser Schritt funktioniert ähnlich wie die obigen Schritte, indem die Ausgabe des letzten Schrittes an einen weiteren Befehl weitergeleitet wird, der diese Funktion erfüllt. Dieser Schritt könnte wieder mit dem Befehl sed durchgeführt werden, da aber nur ein einzelnes Zeichen (nämlich das Zeilenumbruch-Zeichen bzw. Newline) durch ein Leerzeichen ersetzt werden soll, wird hier aufgrund der einfacheren Syntax der Befehl tr verwendet.
Der fertige Befehl sieht nun wie folgt aus:

Oder im fertigen Beispiel mit www.google.de:

Hiermit ist das Parsen abgeschlossen und die fertige Ausgabe kann nun in eine Ergebnisdatei geschrieben werden. Um automatisch viele Zielrechner aus einer Datei einzulesen und alle gefundenen Routen in eine Zieldatei zu schreiben, wird der obige Befehl in eine Schleife „verpackt“ welche die Zielrechner Zeile für Zeile aus der Datei zieladressen.txt ausliest und die gefundenen Routen ebenso Zeile für Zeile in die Datei routen.csv schreibt. Die Datei routen.csv kann später zur Ermittlung verschiedener Informationen zu den gefunden IP-Adressen einfach mit einem Python-Skript eingelesen und geparst werden.

In diesem Artikel wird das fertige Skript ohne weitere Erklärung in der beiliegenden zip-Datei bereitgestellt. Wen die genaue Funktionsweise der Schleife interessiert, sei angehalten sich generell über die Funktionsweise von Shellskripten einzulesen, da dies den Rahmen des Artikels sprengen würde.

Dieses Skript benötigt die Datei zieladressen.txt welche wie folgt aussehen muss (anstatt Domainnamen können auch direkt IPv4-Adressen verwendet werden):

2. Sammeln von (Geo-)Informationen zu bestimmten IPv4-Adressen

Die gefundenen IPv4-Adressen können anschließend mit dem Befehl geoiplookup oder über die Internetseite http://geoiplookup.net/ relativ genau (meißtens auf Städteniveau) lokalisiert werden. Dies funktioniert, da einzelne Subnets in der Regel bestimmten Regionen und Internetprovidern zugeordnet sind.

Der Befehl geoiplookup greift hierbei auf eine vorher installierte und lokal gespeicherte Datenbank zu, welche je nach installierter Version als Country- oder City-Edition vorliegt. Da geoiplookup nicht zu den Standartbordmitteln unter Linux gehört und um die weiteren Schritte auch Benutzern anderer Betriebssysteme zu ermöglichen, wird hier nur ein kurzes Beispiel der Benutzung dieses Befehls und dessen Ausgabe gegeben und im weiteren die Online-Abfrage mittels eines Python-Skriptes beschrieben.

Die Internetseite http://geoiplookup.net bietet einen Onlineservice welcher Geo- und weitere Informationen zu gegebenen IPv4-Adressen bereitstellt. Öffnet man die Seite ohne Angabe einer IP-Adresse in einem Browser, so erhält man die entsprechenden Informationen über die eigene IP-Adresse. (Achtung: die Verwendung eines Proxies oder gar Tor führt zwangsläufig zu falschen Ergebnissen.)

Da die Seite auch über eine API (also eine automatisierte Abfrageschnittstelle) unter der Adresse “http://api.geoiplookup.net/?query=${IPADRESSE}” verfügt, kann man die entsprechenden Informationen zu den IP-Adressen mittels eines Pythonskriptes abfragen und auswerten. Als Antwort erhält man eine XML‑Datei welche beispielsweise folgendermaßen aussieht:

Diese kann im Browser z. B. unter der Adresse http://api.geoiplookup.net/?query=77.20.253.87 aufgerufen werden (oder unter: http://api.geoiplookup.net/ für die eigene Adresse).

Um die hierin enthaltenen Informationen mit Hilfe von Python auszulesen lässt sich ElementTree aus aus dem Modul xml.etree, das in der Python-Standartbibliothek vorhanden ist, verwenden. Dies wird im beiliegenden Skript mit der Funktion get_hostinfo() bewerkstelligt:

Diese parst die XML-Datei automatisch zu einem Python-DefaultDict das dann die entsprechenden Informationen enthält (das DefaultDict wird verwendet da normale Python Dictionaries zu Fehlern führen, wenn nicht gesetzte Werte abgefragt werden). Die Ausgabe der Funktion sieht dann wie folgt aus:

3. Plotten der gefundenen Routen mit geoplotlib auf einer Weltkarte

Wichtig für das anschließende Plotten ist hierbei die Geolocation also ‘latitude’ und ‘longitude’. Mit den Werten kann man anschließend die mit traceroute gefundenen Pfade als Basemap plotten. Dies funktioniert mit der Funktion drawroutes2map():

Der Plot einer Verbindungsanfrage an www.google.de aus Berlin sieht beispielsweise folgendermaßen aus:

Hier wird deutlich, dass Datenpakete durchaus nicht immer den kürzesten Weg nehmen, sondern teilweise rund um die Welt gesendet werden (Deutschland – USA – Sydney(!) – USA), bevor sie an ihrem Ziel ankommen und dass das Ziel einer Verbindung zu einer Domain mit der Endung „de“ nicht unbedingt in Deutschland liegen muss.

Mit Default-Einstellungen werden von der Funktion drawroutes2map() alle Routen in zufälligen Farben geplottet, welche in der Datei routen.csv gefunden werden.

Lässt man viele Routen plotten wird hierbei die Netzwerkstruktur deutlich, über die die Daten im Internet verteilt werden. Auf dem obigen Plot kann man recht gut erkennen, dass die meisten Internetseiten in Europa oder den USA gehostet werden, einige noch in China und Japan, dagegen beispielsweise Afrika praktisch unbedeutend ist.

Auf dem nächsten Plot wiederum ist zu erkennen, dass es tatsächlich eine Art “Hotspots” gibt über die fast alle Daten laufen, wie z. B. Frankfurt am Main, Zürich und Madrid.

4. Schematische Darstellung der Routen als directed Graph mit graphviz

Mit graphviz lassen sich schematische Graphen darstellen. Mit dem Paket pygraphviz existiert hiefür auch eine Python-Anbindung. Die schematische Darstellung als Graph ist in vielen Fällen deutlich übersichtlicher als die Darstellung auf einer Weltkarte und die Topologie des Netzwerkes wird besser sichtbar.

Die entsprechende Python-Funktion, die alle Routen aus der Datei routes.csv als geplotteten Graph ausgibt ist drawroutes2graph():

Die Funktion schreibt den erstellten Graph in der Dot-Language in die Datei routes.dot und erstellt zwei verschiedene visuelle Darstellungen als png-Dateien.

Da mit der Funktion get_hostinfo() auch weitere Informationen zu den jeweiligen IP-Adressen verfügbar sind  können diese auch visuell im Graph dargestellt werden. So sind in der folgenden Darstellung Hosts in verschiedenen Ländern in unterschiedlichen Farben dargestellt. (Deutschland in grün, USA in rot, Spanien in gelb, Schweiz in blau, China in magenta und alle übrigen Länder und Hosts ohne Länderinformation in schwarz).

Diese Art der Darstellung vereint damit die Vorteile der schematischen Darstellung mit der Geoinformation zu den jeweiligen Hosts. Aus der Grafik lässt sich beispielsweise sehr gut erkennen, dass, trotz oft vieler Zwischenstationen innerhalb eines Landes, Landesgrenzen überschreitende Verbindungen relativ selten sind.

Auch interessant ist, dass das Netzwerk durchaus Maschen aufweist – mit anderen Worten: Dass ein und dieselbe Station bei verschiedenen Verbindungsanfragen über verschiedene Zwischenstationen angesprochen wird und Daten, die von Punkt A nach Punkt B gesendet werden, nicht immer denselben Weg nehmen.

5. Schlussfolgerung

Was kann man hieraus denn nun letztendlich an Erkenntnissen ziehen? Zum einen natürlich, wie Daten via Internet über viele Zwischenstationen rund um die Welt gesendet und hierbei mit jeder Station neu sortiert werden. Vor allem aber auch, dass mit dem entsprechenden Know-How und etwas Kreativität mit bemerkenswert wenig Code bereits Unmengen an Daten gesammelt, geordnet und ausgewertet werden können. Alle möglichen Daten werden in unserer heutigen Welt gespeichert und sind zu einem nicht unbeträchtlichen Teil auch für jeden, der weiß, wer diese Daten hat oder wie man sie selber ermitteln kann, verfügbar und oft lassen sich hier interessante Einblicke in die Funktionsweise unserer Welt gewinnen.

Lineare Regression in Python mit Scitkit-Learn

Die lineare Regressionsanalyse ist ein häufiger Einstieg ins maschinelle Lernen um stetige Werte vorherzusagen (Prediction bzw. Prädiktion). Hinter der Regression steht oftmals die Methode der kleinsten Fehlerquadrate und die hat mehr als eine mathematische Methode zur Lösungsfindung (Gradientenverfahren und Normalengleichung). Alternativ kann auch die Maximum Likelihood-Methode zur Regression verwendet werden. Wir wollen uns in diesem Artikel nicht auf die Mathematik konzentrieren, sondern uns direkt an die Anwendung mit Python Scikit-Learn machen:

Haupt-Lernziele:

  • Einführung in Machine Learning mit Scikit-Learn
  • Lineare Regression mit Scikit-Learn

Neben-Lernziele:

  • Datenvorbereitung (Data Preparation) mit Pandas und Scikit-Learn
  • Datenvisualisierung mit der Matplotlib direkt und indirekt (über Pandas)

Was wir inhaltlich tun:

Der Versuch einer Vorhersage eines Fahrzeugpreises auf Basis einer quantitativ-messbaren Eigenschaft eines Fahrzeuges.


Die Daten als Download

Für dieses Beispiel verwende ich die Datei “Automobil_data.txt” von Kaggle.com. Die Daten lassen sich über folgenden Link downloaden, nur leider wird ein (kostenloser) Account benötigt:
https://www.kaggle.com/toramky/automobile-dataset/downloads/automobile-dataset.zip
Sollte der Download-Link unerwartet mal nicht mehr funktionieren, freue ich mich über einen Hinweis als Kommentar 🙂

Die Entwicklungsumgebung

Ich verwende hier die Python-Distribution Anaconda 3 und als Entwicklungs-Umgebung Spyder (in Anaconda enthalten). Genauso gut funktionieren jedoch auch Jupyter Notebook, Eclipse mit PyDev oder direkt die IPython QT-Console.


Zuerst einmal müssen wir die Daten in unsere Python-Session laden und werden einige Transformationen durchführen müssen. Wir starten zunächst mit dem Importieren von drei Bibliotheken NumPy und Pandas, deren Bedeutung ich nicht weiter erläutern werde, somit voraussetze.

Wir nutzen die Pandas-Bibliothek, um die “Automobile_data.txt” in ein pd.DataFrame zu laden.

Schauen wir uns dann die ersten fünf Zeilen in IPython via dataSet.head().

Hinweis: Der Datensatz hat viele Spalten, so dass diese in der Darstellung mit einem Backslash \ umgebrochen werden.

Gleich noch eine weitere Ausgabe dataSet.info(), die uns etwas über die Beschaffenheit der importierten Daten verrät:

Einige Spalten entsprechen hinsichtlich des Datentypes nicht der Erwartung. Für die Spalten ‘horsepower’ und ‘peak-rpm’ würde ich eine Ganzzahl (Integer) erwarten, für ‘price’ hingegen eine Fließkommazahl (Float), allerdings sind die drei Spalten als Object deklariert. Mit Trick 17 im Data Science, der Anzeige der Minimum- und Maximum-Werte einer zu untersuchenden Datenreihe, kommen wir dem Übeltäter schnell auf die Schliche:

Datenbereinigung

Für eine Regressionsanalyse benötigen wir nummerische Werte (intervall- oder ratioskaliert), diese möchten wir auch durch richtige Datentypen-Deklaration herstellen. Nun wird eine Konvertierung in den gewünschten Datentyp jedoch an den (mit ‘?’ aufgefüllten) Datenlücken scheitern.

Schauen wir uns doch einmal die Datenreihen an, in denen in der Spalte ‘peak-rpm’ Fragezeichen stehen:

Zwei Datenreihen sind vorhanden, bei denen ‘peak-rpm’ mit einem ‘?’ aufgefüllt wurde. Nun könnten wir diese Datenreihen einfach rauslöschen. Oder mit sinnvollen (im Sinne von wahrscheinlichen) Werten auffüllen. Vermutlichen haben beide Einträge – beide sind OHC-Motoren mit 4 Zylindern – eine ähnliche Drehzahl-Angabe wie vergleichbare Motoren. Mit folgendem Quellcode, gruppieren wir die Spalten ‘engine-type’ und ‘num-of-cylinders’ und bilden für diese Klassen den arithmetischen Mittelwert (.mean()) für die ‘peak-rpm’.

Und schauen wir uns das Ergebnis an:

Ein Vier-Zylinder-OHC-Motor hat demnach durchschnittlich einen Drehzahl-Peak von 5155 Umdrehungen pro Minute. Ohne nun (fahrlässigerweise) auf die Verteilung in dieser Klasse zu achten, nehmen wir einfach diesen Schätzwert, um die zwei fehlende Datenpunkte zu ersetzen.

Wir möchten jedoch die Original-Daten erhalten und legen ein neues DataSet (dataSet_c) an, in welches wir die Korrekturen vornehmen:

Nun können wir die fehlenden Peak-RPM-Einträge mit unserem Schätzwert ersetzen:

Was bei einer Drehzahl-Angabe noch funktionieren mag, ist für anderen Spalten bereits etwas schwieriger: Die beiden Spalten ‘price’ und ‘horsepower’ sind ebenfalls vom Typ Object, da sie ‘?’ enthalten. Verzichten wir einfach auf die betroffenen Zeilen:

Datenvisualisierung mit Pandas

Wir wollen uns nicht lange vom eigentlichen Ziel ablenken, dennoch nutzen wir die Visualisierungsfähigkeiten der Pandas-Library (welche die Matplotlib inkludiert), um uns dann die Anzahlen an Einträgen nach Hersteller der Fahrzeuge (Spalte ‘make’) anzeigen zu lassen:

Oder die durchschnittliche PS-Zahl nach Hersteller:

Vorbereitung der Regressionsanalyse

Nun kommen wir endlich zur Regressionsanalyse, die wir mit Scikit-Learn umsetzen möchten. Die Regressionsanalyse können wir nur mit intervall- oder ratioskalierten Datenspalten betreiben, daher beschränken wir uns auf diese. Die “price”-Spalte nehmen wir jedoch heraus und setzen sie als unsere Zielgröße fest.

Interessant ist zudem die Betrachtung vorab, wie die einzelnen nummerischen Attribute untereinander korrelieren. Dafür nehmen wir auch die ‘price’-Spalte wieder in die Betrachtung hinein und hinterlegen auch eine Farbskala mit dem Preis (höhere Preise, hellere Farben).

Die lineare Korrelation ist hier sehr interessant, da wir auch nur eine lineare Regression beabsichtigen.

Wie man in dieser Scatter-Matrix recht gut erkennen kann, scheinen einige Größen-Paare nahezu perfekt zu korrelieren, andere nicht.

Korrelation…

  • …nahezu perfekt linear: highway-mpg vs city-mpg (mpg = Miles per Gallon)
  • … eher nicht gegeben: highway-mpg vs height
  • … nicht linear, dafür aber nicht-linear: highway-mpg vs price

Nun, wir wollen den Preis eines Fahrzeuges vorhersagen, wenn wir eine andere quantitative Größe gegeben haben. Auf den Preis bezogen, erscheint mir die Motorleistung (Horsepower) einigermaßen linear zu korrelieren. Versuchen wir hier die lineare Regression und setzen somit die Spalte ‘horsepower’ als X und ‘price’ als y fest.

Die gängige Konvention ist übrigens, X groß zu schreiben, weil hier auch mehrere x-Dimensionen enthalten sein dürfen (multivariate Regression). y hingegen, ist stets nur eine Zielgröße (eine Dimension).

Die lineare Regression ist ein überwachtes Verfahren des maschinellen Lernens, somit müssen wir unsere Prädiktionsergebnisse mit Test-Daten testen, die nicht für das Training verwendet werden dürfen. Scitkit-Learn (oder kurz: sklearn) bietet hierfür eine Funktion an, die uns das Aufteilen der Daten abnimmt:

Zu beachten ist dabei, dass die Daten vor dem Aufteilen in Trainings- und Testdaten gut zu durchmischen sind. Auch dies übernimmt die train_test_split-Funktion für uns, nur sollte man im Hinterkopf behalten, dass die Ergebnisse (auf Grund der Zufallsauswahl) nach jedem Durchlauf immer wieder etwas anders aussehen.

Lineare Regression mit Scikit-Learn

Nun kommen wir zur Durchführung der linearen Regression mit Scitkit-Learn, die sich in drei Zeilen trainieren lässt:

Aber Vorsicht! Bevor wir eine Prädiktion durchführen, wollen wir festlegen, wie wir die Güte der Prädiktion bewerten wollen. Die gängigsten Messungen für eine lineare Regression sind der MSE und R².

MSE = \frac{\sum_{i=1}^n (y_i - \hat{y_i})^2}{n}

Ein großer MSE ist schlecht, ein kleiner gut.

R^2 = 1 - \frac{MSE}{Var(y)}= \frac{\frac{1}{n} \cdot \sum_{i=1}^n (y_i - \hat{y_i})^2}{\frac{1}{n} \cdot \sum_{i=1}^n (y_i - \hat{\mu_y})^2}

Ein kleines R² ist schlecht, ein großes R² gut. Ein R² = 1.0 wäre theoretisch perfekt (da der Fehler = 0.00 wäre), jedoch in der Praxis unmöglich, da dieser nur bei absolut perfekter Korrelation auftreten würde. Die Klasse LinearRegression hat eine R²-Messmethode implementiert (score(x, y)).

Die Ausgabe (ein Beispiel!):

Nach jedem Durchlauf ändert sich mit der Datenaufteilung (train_test_split()) das Modell etwas und auch R² schwankt um eine gewisse Bandbreite. Berauschend sind die Ergebnisse dabei nicht, und wenn wir uns die Regressionsgerade einmal ansehen, wird auch klar, warum:

Bei kleineren Leistungsbereichen, etwa bis 100 PS, ist die Preis-Varianz noch annehmbar gering, doch bei höheren Leistungsbereichen ist die Spannweite deutlich größer. (Nachträgliche Anmerkung vom 06.05.2018: relativ betrachtet, bleibt der Fehler über alle Wertebereiche ungefähr gleich [relativer Fehler]. Die absoluten Fehlerwerte haben jedoch bei größeren x-Werten so eine Varianz der möglichen y-Werte, dass keine befriedigenden Prädiktionen zu erwarten sind.)

Egal wie wir eine Gerade in diese Punktwolke legen, wir werden keine befriedigende Fehlergröße erhalten.

Nehmen wir einmal eine andere Spalte für X, bei der wir vor allem eine nicht-lineare Korrelation erkannt haben: “highway-mpg”

Wenn wir dann das Training wiederholen:

Die R²-Werte sind nicht gerade berauschend, und das erklärt sich auch leicht, wenn wir die Trainings- und Testdaten sowie die gelernte Funktionsgerade visualisieren:

Die Gerade lässt sich nicht wirklich gut durch diese Punktwolke legen, da letztere eher eine Kurve als eine Gerade bildet. Im Grunde könnte eine Gerade noch einigermaßen gut in den Bereich von 22 bis 43 mpg passen und vermutlich annehmbare Ergebnisse liefern. Die Wertebereiche darunter und darüber jedoch verzerren zu sehr und sorgen zudem dafür, dass die Gerade auch innerhalb des mittleren Bereiches zu weit nach oben verschoben ist (ggf. könnte hier eine Ridge-/Lasso-Regression helfen).

Richtig gute Vorhersagen über nicht-lineare Verhältnisse können jedoch nur mit einer nicht-linearen Regression erreicht werden.

Nicht-lineare Regression mit Scikit-Learn

Nicht-lineare Regressionsanalysen erlauben es uns, nicht-lineare korrelierende Werte-Paare als Funktion zu erlernen. Im folgenden Scatter-Plot sehen wir zum einen die gewohnte lineare Regressionsgerade (y = a * x + b) in rot, eine polinominale Regressionskurve dritten Grades (y = a * x³ + b * x² + c * x + d) in violet sowie einen Entscheidungsweg einer Entscheidungsbaum-Regression in gelb.

Nicht-lineare Regressionsanalysen passen sich dem Verlauf der Punktwolke sehr viel besser an und können somit in der Regel auch sehr gute Vorhersageergebnisse liefern. Ich ziehe hier nun jedoch einen Gedankenstrich, liefere aber den Quellcode für die lineare Regression als auch für die beiden nicht-linearen Regressionen mit:

Python Script Regression via Scikit-Learn

Weitere Anmerkungen

  • Bibliotheken wie Scitkit-Learn erlauben es, machinelle Lernverfahren schnell und unkompliziert anwenden zu können. Allerdings sollte man auch verstehen, wei diese Verfahren im Hintergrund mathematisch arbeiten. Diese Bibliotheken befreien uns also nicht gänzlich von der grauen Theorie.
  • Statt der “reinen” lineare Regression (LinearRegression()) können auch eine Ridge-Regression (Ridge()), Lasso-Regression (Lasso()) oder eine Kombination aus beiden als sogenannte ElasticNet-Regression (ElasticNet()). Bei diesen kann über Parametern gesteuert werden, wie stark Ausreißer in den Daten berücksichtigt werden sollen.
  • Vor einer Regression sollten die Werte skaliert werden, idealerweise durch Standardisierung der Werte (sklearn.preprocessing.StandardScaler()) oder durch Normierung (sklearn.preprocessing.Normalizer()).
  • Wir haben hier nur zwei-dimensional betrachtet. In der Praxis ist das jedoch selten ausreichend, auch der Fahrzeug-Preis ist weder von der Motor-Leistung, noch von dem Kraftstoffverbrauch alleine abhängig – Es nehmen viele Größen auf den Preis Einfluss, somit benötigen wir multivariate Regressionsanalysen.

Entropie – Und andere Maße für Unreinheit in Daten

Dieser Artikel ist Teil 1 von 4 der Artikelserie Maschinelles Lernen mit Entscheidungsbaumverfahren.

Hierarchische Klassifikationsmodelle, zu denen das Entscheidungsbaumverfahren (Decision Tree) zählt, zerlegen eine Datenmenge iterativ oder rekursiv mit dem Ziel, die Zielwerte (Klassen) im Rahmen des Lernens (Trainingsphase des überwachten Lernens) möglichst gut zu bereiningen, also eindeutige Klassenzuordnungen für bestimmte Eigenschaften in den Features zu erhalten. Die Zerlegung der Daten erfolgt über einen Informationsgewinn, der für die Klassifikation mit einem Maß der Unreinheit berechnet wird (im nächsten Artikel der Serie werden wir die Entropie berechnen!) Read more

Der Blick für das Wesentliche: Die Merkmalsselektion

In vielen Wissensbasen werden Datensätze durch sehr große Merkmalsräume beschrieben. Während der Generierung einer Wissensbasis wird versucht jedes mögliche Merkmal zu erfassen, um einen Datensatz möglichst genau zu beschreiben. Dabei muss aber nicht jedes Merkmal einen nachhaltigen Wert für das Predictive Modelling darstellen. Ein Klassifikator arbeitet mit reduziertem Merkmalsraum nicht nur schneller, sondern in der Regel auch weitaus effizienter. Oftmals erweist sich ein automatischer Ansatz der Merkmalsselektion besser, als ein manueller, da durchaus Zusammenhänge existieren können, die wir selbst so nicht identifizieren können.

Die Theorie: Merkmalsselektion

Automatische Merkmalsselektionsverfahren unterscheiden 3 verschiedene Arten: Filter, Wrapper und Embedded Methods. Einen guten Überblick über Filter- und Wrapper-Verfahren bieten Kumari et al. in ihrer Arbeit “Filter versus wrapper feature subset selection in large dimensionality micro array: A review” (Download als PDF).

Der Filter-Ansatz bewertet die Merkmale unabhängig des Klassifikators. Dabei werden univariate und multivariate Methoden unterschieden. Univariate Methoden bewerten die Merkmale separat, während der multivariate Ansatz mehrere Merkmale kombiniert. Für jedes Merkmal bzw. jedes Merkmalspaar wird ein statistischer Wert berechnet, der die Eignung der Merkmale für die Klassifikation angibt. Mithilfe eines Schwellwertes werden dann geeignete Merkmale herausgefiltert. Der Filter-Ansatz bietet eine schnelle und, aufgrund der geringen Komplexität, leicht skalierbare Lösung für die Merkmalsselektion. Der Nachteil von Filter-Selektoren besteht in der Missachtung der Abhängigkeiten zwischen den Merkmalen. So werden redundante Merkmale ähnlich bewertet und verzerren später die Erfolgsrate des Klassifikators. Bekannte Beispiele für Filter-Selektoren sind unter anderem die Euklidische Distanz und der Chi-2-Test.

Der Wrapper-Ansatz verbindet die Merkmalsbewertung mit einem Klassifikator. Innerhalb des Merkmalsraumes werden verschiedene Teilmengen von Merkmalen generiert und mithilfe eines trainierten Klassifikators getestet. Um alle möglichen Teilmengen des Merkmalsraumes zu identifizieren, wird der Klassifikator mit einem Suchalgorithmus kombiniert. Da der Merkmalsraum mit Zunahme der Anzahl der Merkmale exponentiell steigt, werden heuristische Suchmethoden für die Suche nach optimalen Teilmengen genutzt. Im Gegensatz zu den Filtern können hier redundante Merkmale abgefangen werden. Die Nutzung eines Klassifikators zur Bewertung der Teilmengen ist zugleich Vor- und Nachteil. Da die generierte Teilmenge auf einen speziellen Klassifikator zugeschnitten wird, ist nicht gewährleistet, dass die Menge auch für andere Klassifikatoren optimal ist. Somit ist dieser Ansatz zumeist abhängig vom gewählten Klassifikator. Zudem benötigt der Wrapper-Ansatz eine viel höhere Rechenzeit. Wrapper-Selektoren werden beispielsweise durch Genetische Algorithmen und Sequentielle Forward/Backward-Selektoren vertreten.

Embedded-Ansätze stellen eine Sonderform der Wrapper-Methode da. Allerdings werden Merkmalssuche und Klassifikatoren-Training nicht getrennt. Die Suche der optimalen Teilmenge ist hier im Modelltraining eingebettet. Dadurch liefern Embedded-Ansätze die gleichen Vorteile wie die Wrapper-Methoden, während die Rechenzeit dabei erheblich gesenkt werden kann. Der reduzierte Merkmalsraum ist aber auch hier vom jeweiligen Klassifikator abhängig. Klassifikatoren, die den Embedded-Ansatz ermöglichen sind beispielsweise der Random-Forest oder die Support-Vector-Maschine.

Entwicklungsgrundlage

Analog zum letzten Tutorial wird hier Python(x,y) und die Datenbasis „Human Activity Recognition Using Smartphones“ genutzt. Die Datenbasis beruht auf erfassten Sensordaten eines Smartphones während speziellen menschlichen Aktivitäten: Laufen, Treppen hinaufsteigen, Treppen herabsteigen, Sitzen, Stehen und Liegen. Auf den Aufzeichnungen von Gyroskop und Accelerometer wurden mehrere Merkmale erhoben. Die Datenmenge, alle zugehörigen Daten und die Beschreibung der Daten sind frei verfügbar.

(https://archive.ics.uci.edu/ml/datasets/Human+Activity+Recognition+Using+Smartphones)

Alle Daten liegen im Textformat vor. Für ein effizienteres Arbeiten mit der Datenbasis wurden diese im Vorfeld in das csv-Dateiformat überführt.

Python-Bibliotheken

Alle für das Data Mining relevanten Bibliotheken sind in Python(x,y) bereits enthalten. Für die Umsetzung werden folgende Bibliotheken genutzt:

Die Bibliotheken NumPy und Pandas unterstützen die Arbeit mit verschiedenen Datenstrukturen und scikit-learn umfasst alle Funktionen des maschinellen Lernens.

Daten vorbereiten

Vor der Anwendung der einzelnen Verfahren werden die Daten vorbereitet. Das Data Frame wird eingelesen, die Klassen in numerische Labels überführt und das Datenfeld in Merkmale (X) und Klassenspalte (y) separiert. Weiterhin wird die informationslose Spalte subject entfernt.

1. Verfahren: RFECV

Der RFECV (Recursive Feature Elimination with Cross Validation) ist ein Vertreter des Wrapper-Ansatzes. In diesem Beispiel wird die Merkmalsselektion mit einem Support Vector Klassifikator kombiniert. Der RFECV berechnet ein Ranking über die einzelnen Merkmale. Dabei bestimmt der Selektor selbst die optimale Menge der Merkmale. Alle Merkmale mit Platz 1 im Ranking bilden den optimalen Merkmalsraum.

2. Verfahren: Random Forest-Klassifikator

Der Random-Forest-Klassifikator gehört zu den Modellen, die einen Embedded-Ansatz ermöglichen. Während des Klassifikatoren-Trainings wird jedem Merkmal ein Wert zugeordnet. Je höher der Wert, desto bedeutsamer das Merkmal. Allerdings ist hier eine manuelle Filterung notwendig, da anders als beim RFECV kein internes Optimum ermittelt wird. Mithilfe eines geeigneten Schwellwertes können die zu wählenden Merkmale bestimmt werden. In diesem Beispiel werden alle Merkmale selektiert, die eine Wichtung größer dem Mittelwert erhalten.

3. Verfahren: Select K Best

Das Select K Best-Verfahren gehört den Filter-Ansätzen an. Daher kommt hier anders als bei den anderen beiden Verfahren kein Klassifikator zum Einsatz. Auch in diesem Verfahren wird für jedes Merkmal ein Wert berechnet, der die Wichtigkeit des Merkmals beziffert. Für die Berechnung der Werte können verschiedene Methoden verwendet werden. In diesem Beispiel wird eine Varianzanalyse genutzt (Parameter f_classif). Auch hier wird mithilfe eines manuellen Schwellwertes der reduzierte Merkmalsraum bestimmt.

Ergebnisse

Für die Bewertung der einzelnen Selektionsverfahren werden die einzelnen Verfahren in den Data-Mining-Prozess (siehe vorheriges Tutorial: Einstieg in das maschinelle Lernen mit Python(x,y)) integriert. Die nachfolgende Tabelle veranschaulicht die Ergebnisse der Klassifikation der einzelnen Verfahren.

 

Selektionsverfahren

Anzahl der Merkmale

Erfolgsrate Klassifikation

Ohne

561

93,96%

RFECV

314

94,03%

Random Forest

118

90,43%

Select K Best

186

92,30%

 

Durch den RFECV konnte das Ergebnis der Klassifikation leicht verbessert werden. Die anderen Selektionsverfahren, die auch deutlich weniger Merkmale nutzen, verschlechtern das Ergebnis sogar. Dies liegt vor allem an der manuellen Regulierung des Schwellwertes.

Einstieg in das Maschinelle Lernen mit Python(x,y)

Python(x,y) ist eine Python-Distribution, die speziell für wissenschaftliche Arbeiten entwickelt wurde. Es umfasst neben der Programmiersprache auch die Entwicklungsumgebung Spyder und eine Reihe integrierter Python-Bibliotheken. Mithilfe von Python(x,y) kann eine Vielzahl von Interessensbereichen bearbeitet werden. Dazu zählen unter anderem Bildverarbeitung oder auch das maschinelle Lernen. Das All-in-One-Setup für Python(x,y) ist für alle gängigen Betriebssysteme online erhältlich. Read more

Numerical Python – Einführung in wissenschaftliches Rechnen mit NumPy

NumPy steht für Numerical Python und ist eines der bekanntesten Pakete für alle Python-Programmierer mit wissenschaftlichen Hintergrund. Von persönlichen Kontakten erfuhr ich, dass NumPy heute in der Astrophysik fast genauso verwendet wird wie auch von sogenannten Quants im Investment-Banking. Das NumPy-Paket ist sicherlich ein Grundstein des Erfolges für Python in der Wissenschaft und für den häufigen Einsatz für die Implementierung von Algorihtmen des maschinellen Lernens in Python.

Die zentrale Datenstruktur in NumPy ist das mehrdimensionale Array. Dieses n-dimensionale Array (ndarray) ist eine sehr mächtige Datenstruktur und verwende ich beispielsweise in meinem Artikel über den k-Nächste-Nachbarn-Algorithmus. Die Besonderheit des NumPy-Arrays ist, dass es ein mehrdimensionaler Container für homogene Daten ist. Ein Datentyp gilt also für das gesamte Array, nicht nur für bestimmte Zeilen oder Spalten!

Read more

Wahrscheinlichkeitsverteilungen – Zentralen Grenzwertsatz verstehen mit Pyhton

Wahrscheinlichkeitsverteilung sind im Data Science ein wichtiges Handwerkszeug. Während in der Mathevorlesung die Dynamik dieser Verteilungen nur durch wildes Tafelgekritzel schwierig erlebbar zu machen ist, können wir mit Programmierkenntnissen (in diesem Fall wieder mit Python) eine kleine Testumgebung für solche Verteilungen erstellen, um ein Gefühl dafür zu entwickeln, wie unterschiedlich diese auf verschiedene Wahrscheinlichkeitswerte, Varianz und Mengen an Datenpunkten reagieren und wann sie untereinander annäherungsweise ersetzbar sind – der zentrale Grenzwertsatz. Den Schwerpunkt lege ich in diesem Artikel auf die Binominal- und Normalverteilung.

Für die folgenden Beispiele werden folgende Python-Bibliotheken benötigt:

Read more

ABC-XYZ-Analyse

Die ABC-XYZ-Analyse ist eine aussagekräftige Analyse für die Strategiefindung in der Warenwirtschaft und Logistik bzw. im Supply Chain Management. Die Analyse basiert auf der Vorstellung einer Pareto-Verteilung, die darauf hindeutet, dass oftmals eine kleine Menge eines großen Ganzen einen unverhältnismäßig großen Einfluss auf eben dieses große Ganze hat.

Die ABC-XYZ-Analyse beinhaltet im ersten Schritt eine ABC- und im zweiten Schritt eine XYZ-Analyse. Im dritten Schritt werden die Ergebnisse in einer Matrix zusammengeführt. In diesem Artikel erläutere ich nicht, wofür eine ABC-XYZ-Analyse dient und wie die Ergebnisse zu interpretieren sind, hier kann ich jedoch auf einen älteren Artikel “ABC-XYZ-Analyse” – www.der-wirtschaftsingenieur.de vom 3. Mai 2011 von mir verweisen, der vorher lesenswert ist, wenn kein Vorwissen zur ABC-XYZ-Analyse vorhanden ist.

Die Vorarbeit

Für die ABC- und XYZ-Analyse benötigen wir folgende Python-Bibliotheken:

Wir laden die EKPO-Tabelle in ein DataFrame (Datenstruktur der Pandas-Bibliothek):

Die Datei stammt aus einem SAP-Testsystem und steht hier zum Download bereit:

csv-icon

SAP.EKPO

Wir benötigen daraus nur folgende Zeilen:

Jetzt kommt der erste Kniff: Das Feld “MENGE” im SAP beschreibt die Menge in der jeweiligen Mengeneinheit (z. B. Stück, Meter oder Liter). Da wir hier jedoch nicht den genauen Verbrauch vorliegen haben, sondern nur die Einkaufsmenge (indirekt gemessener Verbrauch), sollten wir die Menge pro Preiseinheit “PEINH” berücksichtigen, denn nach dieser Preiseinheitsmenge erfolgt der Einkauf.

Für die Preiseinheitsmenge ein Beispiel:
Sie kaufen sicherlich pro Einkauf keine 3 Rollen Toilettenpapier, sondern eine oder mehrere Packungen Toilettenpapier. Wenn Sie zwei Packung Toilettenpapier für jeweils 2 Euro kaufen, die jeweils 10 Rollen beinhalten, ist die Preiseinheit = 10 und die Preiseinheitsmenge => 20 gekaufte Toilettenrollen / 10 Rollen pro Packung = 2 Packungen Toilettenpapier.

Nun haben wir also unsere für den Einkauf relevante Mengeneinheit. Jetzt sortieren wir diese Materialeinkäufe primär nach dem Umsatzvolumen “NETWR” absteigend (und sekundär nach der Preiseinheitsmenge aufsteigend, allerdings spielt das keine große Rolle):

Einige Störfaktoren müssen noch bereinigt werden. Erstens sollen Einträge mit Preisen oder Umsätzen in Höhe von 0,00 Euro nicht mehr auftauchen:

Zweitens gibt es Einkäufe, die ein Material ohne Materialnummer und/oder ohne Materialklasse haben. Bei einer Zusammenfassung (Aggregation) über die Materialnummer oder die Materialklasse würden sich diese “leeren” Einträge als NULL-Eintrag bündeln. Das wollen wir vermeiden, indem wir alle NULL-Einträge mit jeweils unterschiedlichen Zufallszahlen auffüllen.

ABC – Analyse:

Nun geht es an die eigentliche ABC-Analyse, dafür müssen wir die Gruppierung der Materialien vornehmen. Gleich vorweg: Dies sollte man eigentlich über die einzelnen Materialnummern machen, da dies jedoch in der Visualisierung (auf Grund der hohen Anzahl und Vielfältigkeit) etwas aufwändiger ist, machen wir es über die Materialklassen. Wir gehen dabei einfach davon aus, dass die Materialklassen relativ homogene Materialien zusammenfassen und somit auch das Verbrauchs-/Einkaufverhalten innerhalb einer Gruppe nicht sonderlich viel Abweichung aufweist.


Nun können wir uns ganz im Sinne der ABC-Analyse die typische Pareto-Verteilung der kumulierten Umsätze (Umsatzgrößen absteigend sortiert) ansehen:

abc_analyse_sap_netwr_menge_kumulierte_kurve_pareto

Die X-Achse zeigt die Materialklassen von links nach rechts in der Sortierung nach dem Umsatzvolumen (größester Umsatz links, kleinster Umsatz rechts). Die Y-Achse zeigt den Betrag der Umsatzhöhe (Euro) bzw. der Menge (Preiseinheitsmenge). Die Kurve der Menge ist mit Vorsicht zu bewerten, da primär nach dem Umsatz und nicht nach der Menge sortiert wurde.

Klassifikation:

Nun kommen wir zur Klassifikation. Hier machen wir es uns sehr einfach: Wir gehen einfach davon aus, dass 80% des Wertbeitrages aller Umsätze von etwa 20% der Materialien (hier: Materialklassen) umfassen und klassifizieren daher über feste relative Größen:

Hinweis:
Intelligenter wird so eine Klassifikation, wenn wir den steilsten Anstieg innerhalb der kumulierten Volumen (die zuvor gezeigte Kurve) ermitteln und danach die Grenzen für die A-, B-, C-Klassen festlegen.

Optional: Farben für die Klassen festlegen (für die nachfolgende Visualisierung)

Jetzt Aggregieren wir über die ABC-Gruppe:

Das Ergebnis:

Schauen wir uns nun die Verteilung der Werte und Mengen zwischen den Klassen A, B und C an:

 

abc_analyse_gruppen_vergleich

Es ist recht gut erkennbar, dass die Gruppe A deutlich mehr Umsatzvolumen (also Wertbeitrag) als die Gruppen B und C hat. Allerdings hat sie auch eine höhere Bestellmenge, wie jedoch nicht proportional von C über B zu A ansteigt wie das Umsatzvolumen.

Nachfolgend sehen wir die Klassifikation nochmal nicht kumuliert über die Umsatzvolumen der Materialien (Materialklassen):

abc_analyse_sap_netwr

XYZ – Analyse

Für die XYZ-Analyse berechnen wir den arithmetischen Mittelwert, die Standardabweichung und die Summe aller Mengen pro Materialklasse [‘MATKL’] (oder alternativ, der einzelnen Materialnummern [‘MATNR’]) über eine Aggregation: 

Die XYZ-Analyse soll aufzeigen, welche Materialien (hier: Materialklassen) in stabilen Mengen verbraucht (hier: eingekauft) werden und welche größere Schwankungen hinsichtlich der Verbrauchsmenge (hier: Einkaufsmenge) aufweisen. Dazu berechnen wir den Variationskoeffizienten:

Variationskoeffizient = \frac{Standardabweichung}{Mittelwert}

Wir berechnen diesen Variationskoeffizienten und sortieren das DataFrame nach diesem aufsteigend:

Klassifikation:

Nun klassifizieren wir die Materialien (Materialklassen) über den Variationskoeffizienten in XYZ-Klassen. Dabei gehen wir davon aus, dass Materialien/Materialklassen, die einen Variationskoeffizienten von bis zu 70% des Maximalwertes aufweisen, in die Y-Klasse fallen. Solche, die nur maximal 20% des Maximalwertes aufweisen, fallen in die X-Klasse:

Auch hier gilt analog zur ABC-Analyse: Intelligente Klassifikation erfolgt über die Analyse der Kurve der kumulierten Variationskoeffizienten. Die Grenzen der Klassen sollten idealerweise zwischen den steilsten Anstiegen (bzw. die größten Wertedifferenzen) zwischen den Werten der kumulierten Variationskoeffizienten-Liste gezogen werden.

Optional: Farben fürs Plotten setzen.

Jetzt schauen wir uns mal die Verteilung der Materialien hinsichtlich des Variationskoeffizienten an:

xyz_analyse_sap_matkl_menge

Die meisten Materialklassen haben einen recht niedrigen Variationskoeffizienten, sind im Einkauf (und daher vermutlich auch im Verbrauch) recht stabil. Die Materialklasse 0004 hingegen ist einigen Mengenschwankungen unterworfen. In der ABC-Analyse ist diese Materialklasse 0004 als B-Gruppe klassifiziert.

ABC-XYZ-Analyse

Nun möchten wir also die zuvor erstellte ABC-Klassifikation mit der XYZ-Klassifikation zusammen bringen.

Dafür fügen wir die beiden Pandas.DataFrame über den Index (hier die Materialklasse ‘MATKL’, im anderen Fall das Material ‘MATNR’) zusammen:

Die Zusammenfassung als Kreuztabelle:

Für die Interpretation dieser Ergebnisse verweise ich erneut auf den Artikel bei der-wirtschaftsingenieur.de.