{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "72dba059",
   "metadata": {},
   "source": [
    "# Lernfall 4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4105df59",
   "metadata": {},
   "source": [
    "## Datensatz laden"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d7a53682",
   "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": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = pd.read_csv('../3_lernfall/VertragsdatenABCAG.csv', sep=',', decimal='.') # Daten einlesen\n",
    "\n",
    "# Erste 5 Zeilen anzeigen\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3ee9779",
   "metadata": {},
   "source": [
    "## Dummies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ef9e13fc",
   "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": 28,
     "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": "a6a24941",
   "metadata": {},
   "source": [
    "## Korrelationen darstellen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "81f1e895",
   "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": "76057188",
   "metadata": {},
   "source": [
    "## Train und Test Split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "15f15627",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "data = data.drop(['Vertragsnummer', 'Status_aktiv', 'Status_verstorben', 'p'], axis=1)\n",
    "X_train, X_test, y_train, y_test = train_test_split(data.drop(['Status_gekuendigt'], axis=1), data['Status_gekuendigt'], test_size=0.2, random_state=11)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "081a8c31",
   "metadata": {},
   "source": [
    "## Multikolinearität mit VIF prüfen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "59a84f11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 35764 characters]",
      "text/plain": [
       "<Figure size 1000x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Multikolinearität mit VIF prüfen\n",
    "from statsmodels.stats.outliers_influence import variance_inflation_factor\n",
    "\n",
    "# VIF für alle Merkmale berechnen\n",
    "vif_data = pd.DataFrame()\n",
    "vif_data['feature'] = X_train.columns\n",
    "vif_data['VIF'] = [variance_inflation_factor(X_train.values, i) for i in range(X_train.shape[1])]\n",
    "vif_data = vif_data.sort_values(by='VIF', ascending=False)\n",
    "\n",
    "# VIF-Werte mit Balkenplot darstellen\n",
    "plt.figure(figsize=(10, 6))\n",
    "sns.barplot(x=vif_data['feature'], y=vif_data['VIF'])\n",
    "plt.xticks(rotation=90)\n",
    "plt.title('VIF-Werte der Merkmale')\n",
    "plt.ylabel('VIF')\n",
    "plt.xlabel('Merkmale')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4b9dc3a",
   "metadata": {},
   "source": [
    "## Löschen der Variablen mit hohem VIF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "8cf50a4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loeschen von Merkmalen mit hohem VIF: > 10\n",
    "for feature in vif_data['feature'][vif_data['VIF'] > 10]:\n",
    "    X_train = X_train.drop(feature, axis=1)\n",
    "    X_test = X_test.drop(feature, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0360323",
   "metadata": {},
   "source": [
    "## Multiples lineares Regressionsmodell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "71580cbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean Squared Error: 0.11601394197589698\n",
      "R^2 Score: 0.27326238891781685\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 36148 characters]",
      "text/plain": [
       "<Figure size 1000x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "\n",
    "model = LinearRegression()\n",
    "model.fit(X_train, y_train)\n",
    "\n",
    "# Vorhersagen auf dem Testdatensatz\n",
    "y_pred = model.predict(X_test)\n",
    "\n",
    "# Modellbewertung\n",
    "mse = mean_squared_error(y_test, y_pred)\n",
    "r2 = r2_score(y_test, y_pred)\n",
    "print(f'Mean Squared Error: {mse}')\n",
    "print(f'R^2 Score: {r2}')\n",
    "\n",
    "# Feature Importances\n",
    "importances = model.coef_\n",
    "feature_importances = pd.DataFrame({'Feature': X_train.columns, 'Importance': importances})\n",
    "feature_importances = feature_importances.sort_values(by='Importance', ascending=False)\n",
    "\n",
    "# Feature Importances als Balkenplot darstellen\n",
    "plt.figure(figsize=(10, 6))\n",
    "sns.barplot(x=feature_importances['Feature'], y=feature_importances['Importance'])\n",
    "plt.xticks(rotation=90)\n",
    "plt.title('Feature Importances')\n",
    "plt.ylabel('Importance')\n",
    "plt.xlabel('Merkmale')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80ac4986",
   "metadata": {},
   "source": [
    "## Backwards Selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df4f5ff2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Feature \"Sparbeitrag\" mit p-value 0.2170 wird entfernt\n",
      "Feature \"Guthaben\" mit p-value 0.4518 wird entfernt\n",
      "Mean Squared Error (Selected Features): 0.11601088142214849\n",
      "R^2 Score (Selected Features): 0.2732815609197494\n"
     ]
    }
   ],
   "source": [
    "# Backwards Selection der Variablen\n",
    "import statsmodels.api as sm\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "\n",
    "# Funktion zur Durchführung der Backward Selection\n",
    "def backward_selection(X, y, significance_level=0.05):\n",
    "    num_vars = len(X.columns)\n",
    "    for i in range(num_vars):\n",
    "        X_with_const = sm.add_constant(X)\n",
    "        p_values = sm.OLS(y, X_with_const).fit().pvalues[1:]\n",
    "        max_p_value = p_values.max()\n",
    "        if max_p_value > significance_level:\n",
    "            feature_to_remove = p_values.idxmax()\n",
    "            print(f'Feature \"{feature_to_remove}\" mit p-value {max_p_value:.4f} wird entfernt')\n",
    "            X = X.drop(feature_to_remove, axis=1)\n",
    "    return X\n",
    "\n",
    "# Backward Selection durchführen\n",
    "X_train_selected = backward_selection(X_train, y_train)\n",
    "X_test_selected = X_test[X_train_selected.columns]\n",
    "\n",
    "# Modell mit den ausgewählten Merkmalen trainieren\n",
    "model_selected = LinearRegression()\n",
    "model_selected.fit(X_train_selected, y_train)\n",
    "\n",
    "# Vorhersagen auf dem Testdatensatz\n",
    "y_pred_selected = model_selected.predict(X_test_selected)\n",
    "\n",
    "# Modellbewertung\n",
    "mse_selected = mean_squared_error(y_test, y_pred_selected)\n",
    "r2_selected = r2_score(y_test, y_pred_selected)\n",
    "print(f'Mean Squared Error (Selected Features): {mse_selected}')\n",
    "print(f'R^2 Score (Selected Features): {r2_selected}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dafd48c",
   "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
}
