{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lernfall 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datensatz laden"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Vertragsnummer</th>\n",
       "      <th>Jahr</th>\n",
       "      <th>Vertragsalter</th>\n",
       "      <th>Status</th>\n",
       "      <th>Marktzins</th>\n",
       "      <th>Tarifzins</th>\n",
       "      <th>Guthaben</th>\n",
       "      <th>Sparbeitrag</th>\n",
       "      <th>Beruf</th>\n",
       "      <th>Alter</th>\n",
       "      <th>p</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>gekuendigt</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>3933.85</td>\n",
       "      <td>3619.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>48</td>\n",
       "      <td>0.154465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>523.93</td>\n",
       "      <td>482.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>28</td>\n",
       "      <td>0.154465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9998</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>7281.81</td>\n",
       "      <td>6699.0</td>\n",
       "      <td>selbstaendig</td>\n",
       "      <td>22</td>\n",
       "      <td>0.231475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9997</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8905.79</td>\n",
       "      <td>8193.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>26</td>\n",
       "      <td>0.154465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9996</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8566.65</td>\n",
       "      <td>7881.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>30</td>\n",
       "      <td>0.154465</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Vertragsnummer  Jahr  Vertragsalter      Status  Marktzins  Tarifzins  \\\n",
       "0           10000     1              1  gekuendigt        8.7        8.7   \n",
       "1            9999     1              1       aktiv        8.7        8.7   \n",
       "2            9998     1              1       aktiv        8.7        8.7   \n",
       "3            9997     1              1       aktiv        8.7        8.7   \n",
       "4            9996     1              1       aktiv        8.7        8.7   \n",
       "\n",
       "   Guthaben  Sparbeitrag         Beruf  Alter         p  \n",
       "0   3933.85       3619.0    angestellt     48  0.154465  \n",
       "1    523.93        482.0    angestellt     28  0.154465  \n",
       "2   7281.81       6699.0  selbstaendig     22  0.231475  \n",
       "3   8905.79       8193.0    angestellt     26  0.154465  \n",
       "4   8566.65       7881.0    angestellt     30  0.154465  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = pd.read_csv('VertragsdatenABCAG.csv', sep=',', decimal='.') # Daten einlesen\n",
    "\n",
    "# Erste 5 Zeilen anzeigen\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Klassifizierung der Features\n",
    "\n",
    "### Stetige (metrische) Variablen\n",
    "- Marktzins\n",
    "- Tarifzins\n",
    "- Guthaben\n",
    "- Sparbeitrag\n",
    "- Alter des Vertragsinhabers\n",
    "\n",
    "### Ordinale Variablen\n",
    "- Vertragsalter\n",
    "- Jahr\n",
    "\n",
    "### Nominale Variablen\n",
    "- Status (aktiv, Vertragsende, verstorben, gekündigt)\n",
    "- Beruf (angestellt, arbeitslos, selbstständig)\n",
    "- Vertrag (Vertragsnummer) --> Unique Identifier\n",
    "\n",
    "### Sonstige\n",
    "- p (technische Systemvariable)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Visualisierung der Daten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Vertragsnummer</th>\n",
       "      <th>Jahr</th>\n",
       "      <th>Vertragsalter</th>\n",
       "      <th>Status</th>\n",
       "      <th>Marktzins</th>\n",
       "      <th>Tarifzins</th>\n",
       "      <th>Guthaben</th>\n",
       "      <th>Sparbeitrag</th>\n",
       "      <th>Beruf</th>\n",
       "      <th>Alter</th>\n",
       "      <th>p</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>232632</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>3.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>78124.99</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>47</td>\n",
       "      <td>0.000017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276777</th>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.7</td>\n",
       "      <td>93491.77</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>48</td>\n",
       "      <td>0.000083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28187</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>5.4</td>\n",
       "      <td>8.7</td>\n",
       "      <td>17885.40</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>selbstaendig</td>\n",
       "      <td>42</td>\n",
       "      <td>0.001744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348753</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>Vertragsende</td>\n",
       "      <td>5.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>128352.37</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>50</td>\n",
       "      <td>0.000184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318077</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>7.2</td>\n",
       "      <td>8.7</td>\n",
       "      <td>110195.46</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>49</td>\n",
       "      <td>0.003872</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Vertragsnummer  Jahr  Vertragsalter        Status  Marktzins  \\\n",
       "232632               1     7              7         aktiv        3.3   \n",
       "276777               1     8              8         aktiv        4.5   \n",
       "28187                1     2              2         aktiv        5.4   \n",
       "348753               1    10             10  Vertragsende        5.3   \n",
       "318077               1     9              9         aktiv        7.2   \n",
       "\n",
       "        Tarifzins   Guthaben  Sparbeitrag         Beruf  Alter         p  \n",
       "232632        8.7   78124.99       7884.0    angestellt     47  0.000017  \n",
       "276777        8.7   93491.77       7884.0    angestellt     48  0.000083  \n",
       "28187         8.7   17885.40       7884.0  selbstaendig     42  0.001744  \n",
       "348753        8.7  128352.37       7884.0    angestellt     50  0.000184  \n",
       "318077        8.7  110195.46       7884.0    angestellt     49  0.003872  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sortieung der Verträge nach Vertragsnummer\n",
    "data = data.sort_values(by='Vertragsnummer')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Anzahl der Dateninstanzen: 348755\n",
      "Anzahl der Features: 11\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 138236 characters]",
      "text/plain": [
       "<Figure size 2000x1500 with 9 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Anzahl der Zeilen und Spalten\n",
    "print(\"Anzahl der Dateninstanzen: %d\" % data.shape[0])\n",
    "print(\"Anzahl der Features: %d\" % data.shape[1])\n",
    "\n",
    "\n",
    "\n",
    "# Histogramm aller numerischen Spalten\n",
    "data.hist(bins=50, figsize=(20,15))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 281116 characters]",
      "text/plain": [
       "<Figure size 2000x1500 with 81 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Korrelation der numerischen Spalten als Scatterplot\n",
    "from pandas.plotting import scatter_matrix\n",
    "\n",
    "scatter_matrix(data, figsize=(20,15))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "aktiv           269283\n",
      "gekuendigt       69321\n",
      "Vertragsende      7441\n",
      "verstorben        2710\n",
      "Name: Status, dtype: int64\n",
      "aktiv           0.772127\n",
      "gekuendigt      0.198767\n",
      "Vertragsende    0.021336\n",
      "verstorben      0.007770\n",
      "Name: Status, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Status der Vertraege: aktiv, gekuendigt, verstorben, Vertragsende\n",
    "print(data['Status'].value_counts())\n",
    "\n",
    "# relative Häufigkeiten\n",
    "print(data['Status'].value_counts(normalize=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 39940 characters]",
      "text/plain": [
       "<Figure size 800x800 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Pie chart fuer 'Status der Vertraege'\n",
    "status_counts = data['Status'].value_counts()\n",
    "status_counts.plot.pie(autopct='%1.1f%%', figsize=(8, 8), startangle=90, title='Status der Verträge')\n",
    "plt.ylabel('')  # Remove y-axis label\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "angestellt      259880\n",
      "selbstaendig     76240\n",
      "arbeitslos       12635\n",
      "Name: Beruf, dtype: int64\n",
      "angestellt      0.745165\n",
      "selbstaendig    0.218606\n",
      "arbeitslos      0.036229\n",
      "Name: Beruf, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Beruf der Kunden: angestellte, arbeistlos, selbststaendig\n",
    "print(data['Beruf'].value_counts())\n",
    "\n",
    "# relative Häufigkeiten\n",
    "print(data['Beruf'].value_counts(normalize=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 35788 characters]",
      "text/plain": [
       "<Figure size 800x800 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Pie chart fuer 'Beruf der Kunden'\n",
    "status_counts = data['Beruf'].value_counts()\n",
    "status_counts.plot.pie(autopct='%1.1f%%', figsize=(8, 8), startangle=90, title='Beruf der Kunden')\n",
    "plt.ylabel('')  # Remove y-axis label\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Vertragsnummer</th>\n",
       "      <th>Jahr</th>\n",
       "      <th>Vertragsalter</th>\n",
       "      <th>Status</th>\n",
       "      <th>Marktzins</th>\n",
       "      <th>Tarifzins</th>\n",
       "      <th>Guthaben</th>\n",
       "      <th>Sparbeitrag</th>\n",
       "      <th>Beruf</th>\n",
       "      <th>Alter</th>\n",
       "      <th>p</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9999</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8569.91</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>selbstaendig</td>\n",
       "      <td>41</td>\n",
       "      <td>2.314752e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28187</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>5.4</td>\n",
       "      <td>8.7</td>\n",
       "      <td>17885.40</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>selbstaendig</td>\n",
       "      <td>42</td>\n",
       "      <td>1.743701e-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54466</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>3.1</td>\n",
       "      <td>8.7</td>\n",
       "      <td>28011.34</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>selbstaendig</td>\n",
       "      <td>43</td>\n",
       "      <td>4.539787e-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88624</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>1.2</td>\n",
       "      <td>8.7</td>\n",
       "      <td>39018.23</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>arbeitslos</td>\n",
       "      <td>44</td>\n",
       "      <td>5.844521e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130582</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>0.5</td>\n",
       "      <td>8.7</td>\n",
       "      <td>50982.72</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>45</td>\n",
       "      <td>3.736298e-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179800</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>1.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>63988.12</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>46</td>\n",
       "      <td>1.015630e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232632</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>3.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>78124.99</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>47</td>\n",
       "      <td>1.670142e-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276777</th>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.7</td>\n",
       "      <td>93491.77</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>48</td>\n",
       "      <td>8.271722e-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318077</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>aktiv</td>\n",
       "      <td>7.2</td>\n",
       "      <td>8.7</td>\n",
       "      <td>110195.46</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>49</td>\n",
       "      <td>3.872403e-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348753</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>Vertragsende</td>\n",
       "      <td>5.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>128352.37</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>angestellt</td>\n",
       "      <td>50</td>\n",
       "      <td>1.840719e-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Vertragsnummer  Jahr  Vertragsalter        Status  Marktzins  \\\n",
       "9999                 1     1              1         aktiv        8.7   \n",
       "28187                1     2              2         aktiv        5.4   \n",
       "54466                1     3              3         aktiv        3.1   \n",
       "88624                1     4              4         aktiv        1.2   \n",
       "130582               1     5              5         aktiv        0.5   \n",
       "179800               1     6              6         aktiv        1.3   \n",
       "232632               1     7              7         aktiv        3.3   \n",
       "276777               1     8              8         aktiv        4.5   \n",
       "318077               1     9              9         aktiv        7.2   \n",
       "348753               1    10             10  Vertragsende        5.3   \n",
       "\n",
       "        Tarifzins   Guthaben  Sparbeitrag         Beruf  Alter             p  \n",
       "9999          8.7    8569.91       7884.0  selbstaendig     41  2.314752e-01  \n",
       "28187         8.7   17885.40       7884.0  selbstaendig     42  1.743701e-03  \n",
       "54466         8.7   28011.34       7884.0  selbstaendig     43  4.539787e-05  \n",
       "88624         8.7   39018.23       7884.0    arbeitslos     44  5.844521e-06  \n",
       "130582        8.7   50982.72       7884.0    angestellt     45  3.736298e-07  \n",
       "179800        8.7   63988.12       7884.0    angestellt     46  1.015630e-06  \n",
       "232632        8.7   78124.99       7884.0    angestellt     47  1.670142e-05  \n",
       "276777        8.7   93491.77       7884.0    angestellt     48  8.271722e-05  \n",
       "318077        8.7  110195.46       7884.0    angestellt     49  3.872403e-03  \n",
       "348753        8.7  128352.37       7884.0    angestellt     50  1.840719e-04  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Anzeigen eines Vertrags welches mehrfach vorkommt --> Vertragsverlauf\n",
    "data[data['Vertragsnummer'] == 1].sort_values(by='Jahr')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aufbereitung der Daten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Vertragsnummer</th>\n",
       "      <th>Jahr</th>\n",
       "      <th>Vertragsalter</th>\n",
       "      <th>Marktzins</th>\n",
       "      <th>Tarifzins</th>\n",
       "      <th>Guthaben</th>\n",
       "      <th>Sparbeitrag</th>\n",
       "      <th>Alter</th>\n",
       "      <th>p</th>\n",
       "      <th>Status_Vertragsende</th>\n",
       "      <th>Status_aktiv</th>\n",
       "      <th>Status_gekuendigt</th>\n",
       "      <th>Status_verstorben</th>\n",
       "      <th>Beruf_angestellt</th>\n",
       "      <th>Beruf_arbeitslos</th>\n",
       "      <th>Beruf_selbstaendig</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>232632</th>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>78124.99</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>47</td>\n",
       "      <td>0.000017</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>276777</th>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>4.5</td>\n",
       "      <td>8.7</td>\n",
       "      <td>93491.77</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>48</td>\n",
       "      <td>0.000083</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28187</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5.4</td>\n",
       "      <td>8.7</td>\n",
       "      <td>17885.40</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>42</td>\n",
       "      <td>0.001744</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348753</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>5.3</td>\n",
       "      <td>8.7</td>\n",
       "      <td>128352.37</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>50</td>\n",
       "      <td>0.000184</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318077</th>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>7.2</td>\n",
       "      <td>8.7</td>\n",
       "      <td>110195.46</td>\n",
       "      <td>7884.0</td>\n",
       "      <td>49</td>\n",
       "      <td>0.003872</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Vertragsnummer  Jahr  Vertragsalter  Marktzins  Tarifzins   Guthaben  \\\n",
       "232632               1     7              7        3.3        8.7   78124.99   \n",
       "276777               1     8              8        4.5        8.7   93491.77   \n",
       "28187                1     2              2        5.4        8.7   17885.40   \n",
       "348753               1    10             10        5.3        8.7  128352.37   \n",
       "318077               1     9              9        7.2        8.7  110195.46   \n",
       "\n",
       "        Sparbeitrag  Alter         p  Status_Vertragsende  Status_aktiv  \\\n",
       "232632       7884.0     47  0.000017                    0             1   \n",
       "276777       7884.0     48  0.000083                    0             1   \n",
       "28187        7884.0     42  0.001744                    0             1   \n",
       "348753       7884.0     50  0.000184                    1             0   \n",
       "318077       7884.0     49  0.003872                    0             1   \n",
       "\n",
       "        Status_gekuendigt  Status_verstorben  Beruf_angestellt  \\\n",
       "232632                  0                  0                 1   \n",
       "276777                  0                  0                 1   \n",
       "28187                   0                  0                 0   \n",
       "348753                  0                  0                 1   \n",
       "318077                  0                  0                 1   \n",
       "\n",
       "        Beruf_arbeitslos  Beruf_selbstaendig  \n",
       "232632                 0                   0  \n",
       "276777                 0                   0  \n",
       "28187                  0                   1  \n",
       "348753                 0                   0  \n",
       "318077                 0                   0  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One hot encoding fuer 'Status' und 'Beruf'\n",
    "data = pd.get_dummies(data, columns=['Status', 'Beruf'])\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Berechnung der relativen Kündigungshäufigkeit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 27348 characters]",
      "text/plain": [
       "<Figure size 800x800 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Kuchendiagramm fuer \"Status_gekuendigt\" vs andere Stati\n",
    "status_counts = data['Status_gekuendigt'].value_counts()\n",
    "status_counts.plot.pie(autopct='%1.1f%%', figsize=(8, 8), startangle=90, title='Status: gekündigt')\n",
    "plt.ylabel('')  # Remove y-axis label\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Relative Kündigungshäufigkeit: 0.1988\n"
     ]
    }
   ],
   "source": [
    "# Berechnung der relativen Kuenidgungshaeufigkeit\n",
    "relative_kuendigung = data['Status_gekuendigt'].sum() / data.shape[0]\n",
    "print(\"Relative Kündigungshäufigkeit: %.4f\" % relative_kuendigung)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 31804 characters]",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Relative Kündigungshäufigkeit pro Jahr\n",
    "data[\"relative_kuendigung\"] = data.groupby(\"Jahr\")[\"Status_gekuendigt\"].transform(\"mean\")\n",
    "\n",
    "# Plot der relativen Kündigungshäufigkeit pro Jahr als Liniendiagramm\n",
    "data.groupby(\"Jahr\")[\"relative_kuendigung\"].mean().plot(kind='line', title='Relative Kündigungshäufigkeit pro Jahr')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Beziehung der relativen Kündigungshaeufigkeit zu den anderen Variablen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Status_aktiv          -0.317932\n",
      "Status_Vertragsende   -0.115765\n",
      "Beruf_angestellt      -0.019444\n",
      "Status_verstorben     -0.017349\n",
      "Beruf_arbeitslos      -0.001902\n",
      "Sparbeitrag           -0.001637\n",
      "Beruf_selbstaendig     0.021361\n",
      "Tarifzins              0.021862\n",
      "Alter                  0.058741\n",
      "Guthaben               0.191328\n",
      "Vertragsnummer         0.248774\n",
      "Vertragsalter          0.276110\n",
      "Status_gekuendigt      0.379910\n",
      "p                      0.547724\n",
      "Jahr                   0.564083\n",
      "Marktzins              0.616633\n",
      "relative_kuendigung    1.000000\n",
      "Name: relative_kuendigung, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Korrelation der numerischen Spalten mit der jährlichen relativen Kündigungshäufigkeit\n",
    "correlation = data.corr()[\"relative_kuendigung\"].sort_values()\n",
    "print(correlation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 55008 characters]",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Visualisierung der Korrelationen\n",
    "correlation.plot(kind='bar', title='Korrelation mit relativer Kündigungshäufigkeit')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unterschied: Relative Kündigungshäufigkeit vs. Kündigungswahrscheinlichkeit\n",
    "\n",
    "- **Relative Kündigungshäufigkeit**:\n",
    "Dies ist ein beobachteter, aggregierter Kennwert, der den Anteil der tatsächlich gekündigten Verträge innerhalb einer bestimmten Gruppe (z. B. pro Jahr) angibt. Er wird direkt aus den historischen Daten abgeleitet (z. B. 200 Kündigungen bei 10.000 Verträgen = 2 %).\n",
    "\n",
    "- **Kündigungswahrscheinlichkeit**:\n",
    "Dies ist ein prognostizierter Wert, der angibt, wie wahrscheinlich es ist, dass ein einzelner Kunde seinen Vertrag kündigt. Die Kündigungswahrscheinlichkeit wird oft aus statistischen Modellen (z. B. logistischer Regression) geschätzt und berücksichtigt dabei neben der reinen Kündigungsrate auch weitere Einflussfaktoren (wie Alter des Kunden, Beruf, Marktzins etc.)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
