{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5ae37cf6",
   "metadata": {},
   "source": [
    "# Lernfall 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8933b8ed",
   "metadata": {},
   "source": [
    "## Datensatz laden"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "77a4a87d",
   "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": 51,
     "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",
   "id": "a7f17d3f",
   "metadata": {},
   "source": [
    "## Transformieren der Daten in numerische Form"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "9bc5ef44",
   "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>Beruf_arbeitslos</th>\n",
       "      <th>Beruf_selbstaendig</th>\n",
       "      <th>Status_aktiv</th>\n",
       "      <th>Status_gekuendigt</th>\n",
       "      <th>Status_verstorben</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>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>3933.85</td>\n",
       "      <td>3619.0</td>\n",
       "      <td>48</td>\n",
       "      <td>0.154465</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>523.93</td>\n",
       "      <td>482.0</td>\n",
       "      <td>28</td>\n",
       "      <td>0.154465</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>2</th>\n",
       "      <td>9998</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>7281.81</td>\n",
       "      <td>6699.0</td>\n",
       "      <td>22</td>\n",
       "      <td>0.231475</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9997</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8905.79</td>\n",
       "      <td>8193.0</td>\n",
       "      <td>26</td>\n",
       "      <td>0.154465</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>4</th>\n",
       "      <td>9996</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8.7</td>\n",
       "      <td>8566.65</td>\n",
       "      <td>7881.0</td>\n",
       "      <td>30</td>\n",
       "      <td>0.154465</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",
       "0           10000     1              1        8.7        8.7   3933.85   \n",
       "1            9999     1              1        8.7        8.7    523.93   \n",
       "2            9998     1              1        8.7        8.7   7281.81   \n",
       "3            9997     1              1        8.7        8.7   8905.79   \n",
       "4            9996     1              1        8.7        8.7   8566.65   \n",
       "\n",
       "   Sparbeitrag  Alter         p  Beruf_arbeitslos  Beruf_selbstaendig  \\\n",
       "0       3619.0     48  0.154465                 0                   0   \n",
       "1        482.0     28  0.154465                 0                   0   \n",
       "2       6699.0     22  0.231475                 0                   1   \n",
       "3       8193.0     26  0.154465                 0                   0   \n",
       "4       7881.0     30  0.154465                 0                   0   \n",
       "\n",
       "   Status_aktiv  Status_gekuendigt  Status_verstorben  \n",
       "0             0                  1                  0  \n",
       "1             1                  0                  0  \n",
       "2             1                  0                  0  \n",
       "3             1                  0                  0  \n",
       "4             1                  0                  0  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One hot encoding für die Spalten \"Beruf\" und \"Status\"\n",
    "data = pd.get_dummies(data, columns=['Beruf', 'Status'], drop_first=True)\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd160830",
   "metadata": {},
   "source": [
    "## Korrelationen darstellen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "13eb3384",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 42948 characters]",
      "text/plain": [
       "<Figure size 1000x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Pearson-Korrelation berechnen: Zielvariable ist \"Status_gekuendigt\"\n",
    "# \"Vertragsnummer\", \"Status_aktiv\", \"Status_verstorben\" und \"p\" sind nicht relevant für die Korrelation\n",
    "correlation_matrix = data.corr(method='pearson')\n",
    "correlation_with_target = correlation_matrix['Status_gekuendigt'].drop(['Status_gekuendigt', 'Vertragsnummer', 'Status_aktiv', 'Status_verstorben', 'p'])\n",
    "correlation_with_target = correlation_with_target.sort_values(ascending=False)\n",
    "\n",
    "# Korrelationen als Balkenplot darstellen\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "plt.figure(figsize=(10, 6))\n",
    "sns.barplot(x=correlation_with_target.index, y=correlation_with_target.values)\n",
    "plt.xticks(rotation=90)\n",
    "plt.title('Korrelationen mit der Zielvariable \"Status_gekuendigt\"')\n",
    "plt.ylabel('Korrelation')\n",
    "plt.xlabel('Merkmale')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49fc8beb",
   "metadata": {},
   "source": [
    "## Datenaufteilung in Training und Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "dca7a8df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Anzahl der Trainingsdaten: 279004\n",
      "Anzahl der Testdaten: 69751\n"
     ]
    }
   ],
   "source": [
    "# Daten in Trainings- und Testdaten aufteilen\n",
    "from sklearn.model_selection import train_test_split\n",
    "X_train, X_test, y_train, y_test = train_test_split(data, data['Status_gekuendigt'], test_size=0.2, random_state=11)\n",
    "print(f\"Anzahl der Trainingsdaten: {len(X_train)}\")\n",
    "print(f\"Anzahl der Testdaten: {len(X_test)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50b9306d",
   "metadata": {},
   "source": [
    "## Erstellung von Einfachregressionsmodellen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "4edb7ff2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:      Status_gekuendigt   R-squared:                       0.055\n",
      "Model:                            OLS   Adj. R-squared:                  0.055\n",
      "Method:                 Least Squares   F-statistic:                 1.618e+04\n",
      "Date:                Wed, 09 Apr 2025   Prob (F-statistic):               0.00\n",
      "Time:                        11:54:44   Log-Likelihood:            -1.3165e+05\n",
      "No. Observations:              279004   AIC:                         2.633e+05\n",
      "Df Residuals:                  279002   BIC:                         2.633e+05\n",
      "Df Model:                           1                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "==============================================================================\n",
      "                 coef    std err          t      P>|t|      [0.025      0.975]\n",
      "------------------------------------------------------------------------------\n",
      "const          0.0562      0.001     41.979      0.000       0.054       0.059\n",
      "Marktzins      0.0405      0.000    127.196      0.000       0.040       0.041\n",
      "==============================================================================\n",
      "Omnibus:                    52590.458   Durbin-Watson:                   1.998\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):            89166.145\n",
      "Skew:                           1.379   Prob(JB):                         0.00\n",
      "Kurtosis:                       3.250   Cond. No.                         7.97\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n",
      "Mean Squared Error (MSE): 0.15083167048286217\n",
      "R^2: 0.05515625091832621\n"
     ]
    }
   ],
   "source": [
    "# Einfachregressionsmodell mit scikit-learn: Feature = \"Marktzins\", Target = \"Status_gekuendigt\"\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "import numpy as np\n",
    "X_train_simple = X_train[['Marktzins']]\n",
    "X_test_simple = X_test[['Marktzins']]\n",
    "y_train_simple = y_train\n",
    "y_test_simple = y_test\n",
    "model_simple = LinearRegression()\n",
    "model_simple.fit(X_train_simple, y_train_simple)\n",
    "y_pred_simple = model_simple.predict(X_test_simple)\n",
    "\n",
    "# Modellbewertung mit statistischem Guetemass R^2, p-Werte und MSE\n",
    "from statsmodels.api import OLS, add_constant\n",
    "X_train_simple_const = add_constant(X_train_simple)\n",
    "X_test_simple_const = add_constant(X_test_simple)\n",
    "model_simple_ols = OLS(y_train_simple, X_train_simple_const).fit()\n",
    "print(model_simple_ols.summary())\n",
    "mse_simple = mean_squared_error(y_test_simple, y_pred_simple)\n",
    "r2_simple = r2_score(y_test_simple, y_pred_simple)\n",
    "print(f\"Mean Squared Error (MSE): {mse_simple}\")\n",
    "print(f\"R^2: {r2_simple}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "8b2f87a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 48480 characters]",
      "text/plain": [
       "<Figure size 1000x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Durchschnittlicher Status_gekuendigt für verschiedene Marktzinswerte\n",
    "status_gekuendigt_mean = X_train.groupby('Marktzins')['Status_gekuendigt'].mean().reset_index()\n",
    "\n",
    "\n",
    "# Plotten der Regressionsgerade und der Durchschnittswerte\n",
    "plt.figure(figsize=(10, 6))\n",
    "plt.plot(X_test_simple, y_pred_simple, color='red', label='Regressionsgerade')\n",
    "plt.scatter(status_gekuendigt_mean['Marktzins'], status_gekuendigt_mean['Status_gekuendigt'], color='green', label='Durchschnittswerte')\n",
    "plt.title('Einfachregression: Status_gekuendigt vs. Marktzins')\n",
    "plt.xlabel('Marktzins')\n",
    "plt.ylabel('Status_gekuendigt')\n",
    "plt.legend()\n",
    "plt.tight_layout()\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "caa4c1bb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:      Status_gekuendigt   R-squared:                       0.108\n",
      "Model:                            OLS   Adj. R-squared:                  0.108\n",
      "Method:                 Least Squares   F-statistic:                 3.383e+04\n",
      "Date:                Wed, 09 Apr 2025   Prob (F-statistic):               0.00\n",
      "Time:                        11:54:45   Log-Likelihood:            -1.2355e+05\n",
      "No. Observations:              279004   AIC:                         2.471e+05\n",
      "Df Residuals:                  279002   BIC:                         2.471e+05\n",
      "Df Model:                           1                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "==============================================================================\n",
      "                 coef    std err          t      P>|t|      [0.025      0.975]\n",
      "------------------------------------------------------------------------------\n",
      "const          0.4233      0.001    299.224      0.000       0.421       0.426\n",
      "Tarifzins     -0.0473      0.000   -183.922      0.000      -0.048      -0.047\n",
      "==============================================================================\n",
      "Omnibus:                    43458.038   Durbin-Watson:                   1.999\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):            67917.781\n",
      "Skew:                           1.208   Prob(JB):                         0.00\n",
      "Kurtosis:                       3.056   Cond. No.                         11.2\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n",
      "Mean Squared Error (MSE): 0.14247416326793394\n",
      "R^2: 0.10750956918796062\n"
     ]
    }
   ],
   "source": [
    "# Einfachregressionsmodell mit scikit-learn: Feature = \"Tarifzins\", Target = \"Status_gekuendigt\"\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "import numpy as np\n",
    "X_train_simple = X_train[['Tarifzins']]\n",
    "X_test_simple = X_test[['Tarifzins']]\n",
    "y_train_simple = y_train\n",
    "y_test_simple = y_test\n",
    "model_simple = LinearRegression()\n",
    "model_simple.fit(X_train_simple, y_train_simple)\n",
    "y_pred_simple = model_simple.predict(X_test_simple)\n",
    "\n",
    "# Modellbewertung mit statistischem Guetemass R^2, p-Werte und MSE\n",
    "from statsmodels.api import OLS, add_constant\n",
    "X_train_simple_const = add_constant(X_train_simple)\n",
    "X_test_simple_const = add_constant(X_test_simple)\n",
    "model_simple_ols = OLS(y_train_simple, X_train_simple_const).fit()\n",
    "print(model_simple_ols.summary())\n",
    "mse_simple = mean_squared_error(y_test_simple, y_pred_simple)\n",
    "r2_simple = r2_score(y_test_simple, y_pred_simple)\n",
    "print(f\"Mean Squared Error (MSE): {mse_simple}\")\n",
    "print(f\"R^2: {r2_simple}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "67964cdc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 50044 characters]",
      "text/plain": [
       "<Figure size 1000x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Durchschnittlicher Status_gekuendigt für verschiedene Tarifzinswerte\n",
    "status_gekuendigt_mean = X_train.groupby('Tarifzins')['Status_gekuendigt'].mean().reset_index()\n",
    "\n",
    "\n",
    "# Plotten der Regressionsgerade und der Durchschnittswerte\n",
    "plt.figure(figsize=(10, 6))\n",
    "plt.plot(X_test_simple, y_pred_simple, color='red', label='Regressionsgerade')\n",
    "plt.scatter(status_gekuendigt_mean['Tarifzins'], status_gekuendigt_mean['Status_gekuendigt'], color='green', label='Durchschnittswerte')\n",
    "plt.title('Einfachregression: Status_gekuendigt vs. Tarifzins')\n",
    "plt.xlabel('Tarifzins')\n",
    "plt.ylabel('Status_gekuendigt')\n",
    "plt.legend()\n",
    "plt.tight_layout()\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "11d7a9ab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:      Status_gekuendigt   R-squared:                       0.046\n",
      "Model:                            OLS   Adj. R-squared:                  0.046\n",
      "Method:                 Least Squares   F-statistic:                 1.351e+04\n",
      "Date:                Wed, 09 Apr 2025   Prob (F-statistic):               0.00\n",
      "Time:                        11:54:45   Log-Likelihood:            -1.3291e+05\n",
      "No. Observations:              279004   AIC:                         2.658e+05\n",
      "Df Residuals:                  279002   BIC:                         2.659e+05\n",
      "Df Model:                           1                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "==============================================================================\n",
      "                 coef    std err          t      P>|t|      [0.025      0.975]\n",
      "------------------------------------------------------------------------------\n",
      "const         -0.0223      0.002    -10.941      0.000      -0.026      -0.018\n",
      "Jahr           0.0355      0.000    116.220      0.000       0.035       0.036\n",
      "==============================================================================\n",
      "Omnibus:                    53045.477   Durbin-Watson:                   1.999\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):            90506.856\n",
      "Skew:                           1.391   Prob(JB):                         0.00\n",
      "Kurtosis:                       3.214   Cond. No.                         18.8\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n",
      "Mean Squared Error (MSE): 0.15246656201925388\n",
      "R^2: 0.04491492001188524\n"
     ]
    }
   ],
   "source": [
    "# Einfachregressionsmodell mit scikit-learn: Feature = \"Jahr\", Target = \"Status_gekuendigt\"\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "import numpy as np\n",
    "X_train_simple = X_train[['Jahr']]\n",
    "X_test_simple = X_test[['Jahr']]\n",
    "y_train_simple = y_train\n",
    "y_test_simple = y_test\n",
    "model_simple = LinearRegression()\n",
    "model_simple.fit(X_train_simple, y_train_simple)\n",
    "y_pred_simple = model_simple.predict(X_test_simple)\n",
    "\n",
    "# Modellbewertung mit statistischem Guetemass R^2, p-Werte und MSE\n",
    "from statsmodels.api import OLS, add_constant\n",
    "X_train_simple_const = add_constant(X_train_simple)\n",
    "X_test_simple_const = add_constant(X_test_simple)\n",
    "model_simple_ols = OLS(y_train_simple, X_train_simple_const).fit()\n",
    "print(model_simple_ols.summary())\n",
    "mse_simple = mean_squared_error(y_test_simple, y_pred_simple)\n",
    "r2_simple = r2_score(y_test_simple, y_pred_simple)\n",
    "print(f\"Mean Squared Error (MSE): {mse_simple}\")\n",
    "print(f\"R^2: {r2_simple}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "ed8a93cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 46320 characters]",
      "text/plain": [
       "<Figure size 1000x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Durchschnittlicher Status_gekuendigt für verschiedene Jahre\n",
    "status_gekuendigt_mean = X_train.groupby('Jahr')['Status_gekuendigt'].mean().reset_index()\n",
    "\n",
    "\n",
    "# Plotten der Regressionsgerade und der Durchschnittswerte\n",
    "plt.figure(figsize=(10, 6))\n",
    "plt.plot(X_test_simple, y_pred_simple, color='red', label='Regressionsgerade')\n",
    "plt.scatter(status_gekuendigt_mean['Jahr'], status_gekuendigt_mean['Status_gekuendigt'], color='green', label='Durchschnittswerte')\n",
    "plt.title('Einfachregression: Status_gekuendigt vs. Jahr')\n",
    "plt.xlabel('Jahr')\n",
    "plt.ylabel('Status_gekuendigt')\n",
    "plt.legend()\n",
    "plt.tight_layout()\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3079b781",
   "metadata": {},
   "source": [
    "## Übersicht der Modelle\n",
    "\n",
    "### Marktzins-Modell\n",
    "\n",
    "- **R²:** ca. 0,055 (5,5 % der Varianz erklärt)  \n",
    "- **Mean Squared Error (MSE):** ca. 0,151  \n",
    "- **Koeffizient:** +0,0405  \n",
    "- **p-Wert:** 0,000  \n",
    "\n",
    "*Kommentar:*  \n",
    "Das Modell zeigt einen signifikanten Zusammenhang zwischen dem Marktzins und dem Kündigungsstatus, der Erklärungsgrad ist jedoch moderat.\n",
    "\n",
    "---\n",
    "\n",
    "### Tarifzins-Modell\n",
    "\n",
    "- **R²:** ca. 0,108 (10,8 % der Varianz erklärt)  \n",
    "- **Mean Squared Error (MSE):** ca. 0,142  \n",
    "- **Koeffizient:** –0,0473  \n",
    "- **p-Wert:** 0,000  \n",
    "\n",
    "*Kommentar:*  \n",
    "Dieses Modell liefert den höchsten Erklärungsgrad und den niedrigsten MSE der drei Varianten. Der negative Koeffizient deutet darauf hin, dass ein höherer Tarifzins mit einem geringeren Kündigungsrisiko assoziiert ist. Insgesamt ist das Tarifzins-Modell am aussagekräftigsten.\n",
    "\n",
    "---\n",
    "\n",
    "### Jahr-Modell\n",
    "\n",
    "- **R²:** ca. 0,046 (4,6 % der Varianz erklärt)  \n",
    "- **Mean Squared Error (MSE):** ca. 0,152  \n",
    "- **Koeffizient:** +0,0355  \n",
    "- **p-Wert:** 0,000  \n",
    "\n",
    "*Kommentar:*  \n",
    "Das Jahr-Modell weist den geringsten Erklärungsgrad und den höchsten MSE auf, weshalb es im Vergleich zu den anderen beiden Modellen weniger informativ erscheint.\n",
    "\n",
    "---\n",
    "\n",
    "## Zusammenfassung\n",
    "\n",
    "- **Statistische Signifikanz:** Alle Modelle zeigen signifikante Zusammenhänge (p < 0,001).\n",
    "- **Bester Erklärungsgrad:** Das Tarifzins-Modell (R² ca. 10,8 %).\n",
    "- **Niedrigster MSE:** Ebenfalls das Tarifzins-Modell (MSE ca. 0,142).\n",
    "\n",
    "Das **Tarifzins-Modell** ist demnach am aussagekräftigsten, während das **Jahr-Modell** den geringsten Beitrag zur Erklärung der Varianz im Kündigungsstatus liefert."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc23f104",
   "metadata": {},
   "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": 5
}
