W04.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8nNqAgcnZofH"
   },
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "3Z75Yk8VK6Ke",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "import io"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XCUwKqxCZuVO"
   },
   "source": [
    "# Utils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "2MBuQHN4NFb8"
   },
   "outputs": [],
   "source": [
    "def load_dataset(url: str, sep=None, dtype=None, decode=None, sheet=None, verbose: int = 0):\n",
    "    \"\"\"\n",
    "    Load dataset function by request\n",
    "    --------------------------------\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    url : str\n",
    "        Valid url of the dataframe.\n",
    "    sep : None\n",
    "        Delimiter to be used.\n",
    "    dtype : None\n",
    "        Data type for data or columns.\n",
    "    decode : None\n",
    "        decode type.\n",
    "    sheet : None\n",
    "        sheet name of the excel file\n",
    "    verbose :  int = 0\n",
    "        0 or 1. Verbosity mode. 0 = csv, 1 = xlsx.\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    dataframe : pd.core.frame.DataFrame\n",
    "        Pandas dataframe from the xlsx or csv requested file.\n",
    "    \"\"\"\n",
    "    def requests_csv(url: str, decode: None):\n",
    "        return (requests.get(url).content).decode(decode)\n",
    "\n",
    "    if verbose == 0:\n",
    "        return pd.read_csv(io.StringIO(requests_csv(url, decode)), sep=sep, dtype=dtype)\n",
    "    elif verbose == 1:\n",
    "        excel_file = pd.ExcelFile(url)\n",
    "        return excel_file.parse(sheet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "Uz9KmOcfZwNi"
   },
   "outputs": [],
   "source": [
    "def lambda_apply_V(df: pd.core.frame.DataFrame, subset: str, v1: int = 1, v2: int = 1, elseV: str = '0'):\n",
    "    \"\"\"Apply changes to the requires values using a simple lambda function\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    df : pd.core.frame.DataFrame\n",
    "        pandas dataframe target.\n",
    "    subset :  str\n",
    "        column target.\n",
    "    v1, v2 : int\n",
    "        values to be used in lambda: `df[subset].apply(lambda x: v1 if x == v2 else elseV)`.\n",
    "    elseV : str\n",
    "        else value, the dataset will require data type normalization.\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    df : pd.core.frame.DataFrame\n",
    "        Pandas dataframe.\n",
    "    \"\"\"\n",
    "    return df[subset].apply(lambda x: v1 if x == v2 else elseV)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "HWu6yfOrq_5V"
   },
   "outputs": [],
   "source": [
    "def lambda_mean_V(df: pd.core.frame.DataFrame, subset: str, subset_target: str, v1=None, v2=None,\n",
    "                  verbose: int = 0, subset_threshold: str = 'sanz_adj'):\n",
    "    \"\"\"Mean value adjusted comparision required using and retrieving `subset_target`.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    df : pd.code.frame.DataFrame\n",
    "        Pandas Dataframe.\n",
    "    subset : str\n",
    "        main column\n",
    "    v1, v2 : None\n",
    "        Values\n",
    "    subset_target : str\n",
    "        target column\n",
    "    verbose : int\n",
    "        0 or 1. Verbosity mode. \n",
    "        Different requirements using mean function.   \n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    DataFrame mean value according to the verbosity mode selected.\n",
    "    \"\"\"\n",
    "    if verbose == 0:\n",
    "        return df[df[subset] == v1][subset_target].mean()\n",
    "    elif verbose == 1:\n",
    "        return df[(df[subset] == v1) & (df[subset_target] != v2)][subset_threshold].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "q9lH73lSK6Kb"
   },
   "source": [
    "# Data Preparation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "id": "s0SjkunCK6Kf",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "W04_ex = load_dataset(\"https://github.com/Y4rd13/ILIAS/blob/main/datasets/W04_Association.xlsx?raw=true\", sheet=\"hst\", verbose=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 241
    },
    "id": "kQ6HV-r0K6Kg",
    "outputId": "0b0def5a-aa60-4f57-92e5-0c249a576118",
    "scrolled": true
   },
   "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>Unnamed: 0</th>\n",
       "      <th>bsz</th>\n",
       "      <th>VersNR</th>\n",
       "      <th>Vermittler</th>\n",
       "      <th>von</th>\n",
       "      <th>bis</th>\n",
       "      <th>wkz</th>\n",
       "      <th>sfr</th>\n",
       "      <th>taja</th>\n",
       "      <th>szlj</th>\n",
       "      <th>...</th>\n",
       "      <th>tsn</th>\n",
       "      <th>ezuJJJJ</th>\n",
       "      <th>nutzart</th>\n",
       "      <th>nutzkreis</th>\n",
       "      <th>gar</th>\n",
       "      <th>Wohngeb</th>\n",
       "      <th>PLZHalter</th>\n",
       "      <th>VNGeschle</th>\n",
       "      <th>KZRabSchutz</th>\n",
       "      <th>BeglF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>5fbf17e3442744ced0e3ccda244f16bd123a118702ad1f...</td>\n",
       "      <td>1727d2c592180aa82286e77bc3e8fe33811f20cd92e28b...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>122018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2002.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>f0c503761b7eafc6a7faf07a92714a774ca51fe7bf18fc...</td>\n",
       "      <td>048cc7d1430d3c5ec0380b0221bbffdde775dfbe806cc8...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>392018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>ADE</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>52078.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>a47e2e3e4353a538542826e69004ef971d23a044070dd9...</td>\n",
       "      <td>d257f91ba3ea3bfff168ab0fc06c0261663c36a4220b33...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>252018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>AZP</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>63477.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>5d3c3822c07bfe93ede8d473960b6e72f6703f2eec411f...</td>\n",
       "      <td>a2eb70645d686d45dc071c22788e5afa1d4e4b43b26fba...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>202018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>999</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35781.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>109237a2a52e5a8be83c68f093f7cb2aeb871c88bf026c...</td>\n",
       "      <td>2ceee4ad20193f2da03bf0c51be2b10eae7c410cdfddb5...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>252018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>999</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33428.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  bsz                                             VersNR  \\\n",
       "0           0   12  5fbf17e3442744ced0e3ccda244f16bd123a118702ad1f...   \n",
       "1           1   12  f0c503761b7eafc6a7faf07a92714a774ca51fe7bf18fc...   \n",
       "2           2   12  a47e2e3e4353a538542826e69004ef971d23a044070dd9...   \n",
       "3           3   12  5d3c3822c07bfe93ede8d473960b6e72f6703f2eec411f...   \n",
       "4           4   12  109237a2a52e5a8be83c68f093f7cb2aeb871c88bf026c...   \n",
       "\n",
       "                                          Vermittler         von         bis  \\\n",
       "0  1727d2c592180aa82286e77bc3e8fe33811f20cd92e28b...  01.01.2018  31.12.2018   \n",
       "1  048cc7d1430d3c5ec0380b0221bbffdde775dfbe806cc8...  01.01.2018  31.12.2018   \n",
       "2  d257f91ba3ea3bfff168ab0fc06c0261663c36a4220b33...  01.01.2018  31.12.2018   \n",
       "3  a2eb70645d686d45dc071c22788e5afa1d4e4b43b26fba...  01.01.2018  31.12.2018   \n",
       "4  2ceee4ad20193f2da03bf0c51be2b10eae7c410cdfddb5...  01.01.2018  31.12.2018   \n",
       "\n",
       "     wkz  sfr      taja  szlj  ...  tsn ezuJJJJ  nutzart  nutzkreis  gar  \\\n",
       "0  112.0   20  122018.0     0  ...  NaN  2002.0      0.0        0.0  0.0   \n",
       "1  112.0   20  392018.0     0  ...  ADE  2017.0      2.0       55.0  1.0   \n",
       "2  112.0   20  252018.0     0  ...  AZP  2012.0      3.0        0.0  1.0   \n",
       "3  112.0   20  202018.0     0  ...  999  2008.0      1.0       55.0  1.0   \n",
       "4  112.0   20  252018.0     0  ...  999  2009.0      0.0        0.0  0.0   \n",
       "\n",
       "  Wohngeb  PLZHalter  VNGeschle  KZRabSchutz BeglF  \n",
       "0     0.0        0.0        0.0          0.0   0.0  \n",
       "1     9.0    52078.0        3.0          0.0   0.0  \n",
       "2     0.0    63477.0        3.0          0.0   0.0  \n",
       "3     0.0    35781.0        1.0          0.0   0.0  \n",
       "4     0.0    33428.0        0.0          0.0   0.0  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "VW_data = load_dataset(url=\"https://media.githubusercontent.com/media/Y4rd13/ILIAS/main/datasets/Volkswohl_Dataset.csv\", sep=';', dtype={'hst': str}, decode='utf-8', verbose=0)\n",
    "VW_data .head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "id": "Tpzzq0COK6Ki"
   },
   "outputs": [],
   "source": [
    "#Create three new dummy columns „KH“, „VK“ and „TK“ containing 0 or 1, if „bsz“ is equal to 11, 12 and 13, respectively\n",
    "VW_data=VW_data.assign(KH=0)\n",
    "VW_data=VW_data.assign(VK=0)\n",
    "VW_data=VW_data.assign(TK=0)\n",
    "VW_data.loc[VW_data[\"bsz\"] == 11,\"KH\"] = 1\n",
    "VW_data.loc[VW_data[\"bsz\"] == 12,\"VK\"] = 1\n",
    "VW_data.loc[VW_data[\"bsz\"] == 13,\"TK\"] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "UVsK77pyK6Ki",
    "outputId": "a72e75ef-2dc9-4b65-caea-f81aba5d9658"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1.  , 0.83, 0.75, 0.42, 0.58, 0.33, 0.66, 0.25, 0.5 , 0.91, 0.08,\n",
       "       0.17, 0.  ,  nan, 0.24])"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the column „je“ into data type „float“. You need to replace the comma by a dot beforehand (e.g. „0,83“ to „0.83“)\n",
    "VW_data['je'] = VW_data[\"je\"].apply(str)\n",
    "VW_data['je'] = VW_data[\"je\"].apply(lambda x: x.replace(',', '.'))\n",
    "VW_data['je'] = VW_data[\"je\"].apply(float)\n",
    "VW_data[\"je\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "id": "hA4Z1IcvK6Kj"
   },
   "outputs": [],
   "source": [
    "# Create a new column „sfr_group“ based on column „sfr“ in order to create 9 aggregated bins:\n",
    "# [1, 20), [20, 30), [30, 40), [40, 50), [50, 60), [60, 70), [70, 80), [80, 90), [90, 110) In this case [30,40) contains all numbers between 30-39\n",
    "agg_bins = [1, 20, 30, 40, 50, 60, 70, 80, 90, 110]\n",
    "sfr_val = VW_data[\"sfr\"].copy()\n",
    "VW_data[\"sfr_group\"] = pd.cut(sfr_val, agg_bins, right=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "ckQnKvSsK6Kj",
    "outputId": "597cb6f7-f9c0-4933-db2c-633cea365ae4"
   },
   "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>vngeb</th>\n",
       "      <th>vngeb_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25.01.1964</td>\n",
       "      <td>1964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05.05.1980</td>\n",
       "      <td>1980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25.09.1967</td>\n",
       "      <td>1967</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        vngeb vngeb_year\n",
       "0  25.01.1964       1964\n",
       "1         NaN          0\n",
       "2         NaN          0\n",
       "3  05.05.1980       1980\n",
       "4  25.09.1967       1967"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new column „vngeb_year“ based on „vngeb“ indicating only the year of birth. Replace all missing values by 0\n",
    "year = list(pd.Series(VW_data[\"vngeb\"].fillna(0).astype(str).values))\n",
    "for i, v in enumerate(year):\n",
    "        if (v != \"0\"):\n",
    "            year[i]=v.split(\".\")[2]\n",
    "        else:\n",
    "            year[i]=\"0\"\n",
    "\n",
    "VW_data[\"vngeb_year\"] = pd.Series(year)\n",
    "VW_data[[\"vngeb\",\"vngeb_year\"]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "1yA91hYeK6Kk",
    "outputId": "0f5452aa-52ce-450b-e798-5982d29bec0f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1956.,    0., 1987., 1976., 1964., 1969., 1963., 1988., 1971.,\n",
       "       1975., 1979., 1980., 1989., 1965., 1968., 1982., 1942., 1958.,\n",
       "       1990., 1974., 1997., 1947., 1949., 1943., 1981., 1962., 1927.,\n",
       "       1970., 1961., 1945., 1953., 1973., 1977., 1954., 1985., 1959.,\n",
       "       1991., 1948., 1940., 1967., 1966., 1960., 1957., 1950., 1946.,\n",
       "       1952., 1935., 1955., 1993., 1944., 1951., 1939., 1996., 1983.,\n",
       "       1972., 1992., 1986., 1978., 1934., 1937., 1930., 1936., 1999.,\n",
       "       1931., 1995., 1941., 1938., 1994., 1933., 1932., 1984., 2000.,\n",
       "       1998., 1926., 1929., 2001., 1928., 2008., 1925., 1924., 1914.,\n",
       "       1919., 2014., 1923., 1922.])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace all values below 1900 in column „fahrgebjahr“ by 0\n",
    "VW_data[\"fahrgebjahr\"].where(VW_data[\"fahrgebjahr\"] > 1900, 0, inplace=True)\n",
    "VW_data[\"fahrgebjahr\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "id": "w-F4CoNxK6Kk"
   },
   "outputs": [],
   "source": [
    "# Replace all values below 1900 in column „ezuJJJJ“ by np.nan\n",
    "VW_data[\"ezuJJJJ\"].where(VW_data[\"ezuJJJJ\"] > 1900, np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 592
    },
    "id": "LqCEKXeaK6Kl",
    "outputId": "6a37f7b7-d478-4e30-b979-539a1d6c278d"
   },
   "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>bsz</th>\n",
       "      <th>VersNR</th>\n",
       "      <th>Vermittler</th>\n",
       "      <th>von</th>\n",
       "      <th>bis</th>\n",
       "      <th>wkz</th>\n",
       "      <th>sfr</th>\n",
       "      <th>taja</th>\n",
       "      <th>szlj</th>\n",
       "      <th>sres</th>\n",
       "      <th>...</th>\n",
       "      <th>PLZHalter</th>\n",
       "      <th>VNGeschle</th>\n",
       "      <th>KZRabSchutz</th>\n",
       "      <th>BeglF</th>\n",
       "      <th>KH</th>\n",
       "      <th>VK</th>\n",
       "      <th>TK</th>\n",
       "      <th>sfr_group</th>\n",
       "      <th>vngeb_year</th>\n",
       "      <th>ezu_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>5fbf17e3442744ced0e3ccda244f16bd123a118702ad1f...</td>\n",
       "      <td>1727d2c592180aa82286e77bc3e8fe33811f20cd92e28b...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>122018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>1964</td>\n",
       "      <td>[2000.0, 2005.0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>f0c503761b7eafc6a7faf07a92714a774ca51fe7bf18fc...</td>\n",
       "      <td>048cc7d1430d3c5ec0380b0221bbffdde775dfbe806cc8...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>392018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>52078.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>0</td>\n",
       "      <td>[2015.0, 2020.0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>a47e2e3e4353a538542826e69004ef971d23a044070dd9...</td>\n",
       "      <td>d257f91ba3ea3bfff168ab0fc06c0261663c36a4220b33...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>252018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>63477.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>0</td>\n",
       "      <td>[2010.0, 2015.0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>5d3c3822c07bfe93ede8d473960b6e72f6703f2eec411f...</td>\n",
       "      <td>a2eb70645d686d45dc071c22788e5afa1d4e4b43b26fba...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>202018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>35781.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>1980</td>\n",
       "      <td>[2005.0, 2010.0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>109237a2a52e5a8be83c68f093f7cb2aeb871c88bf026c...</td>\n",
       "      <td>2ceee4ad20193f2da03bf0c51be2b10eae7c410cdfddb5...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>252018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>33428.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>1967</td>\n",
       "      <td>[2005.0, 2010.0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651681</th>\n",
       "      <td>12</td>\n",
       "      <td>762c753bd091e3e142f88a8080024b0d5444bcd51f6e00...</td>\n",
       "      <td>71beb4a3aefe6928f5f27b3edc4ab469df827572f8f75e...</td>\n",
       "      <td>10.12.2010</td>\n",
       "      <td>23.12.2014</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>222014.0</td>\n",
       "      <td>4015</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651682</th>\n",
       "      <td>12</td>\n",
       "      <td>e77e867a81297890c6e8545b60bc631d07dd3bb1aae590...</td>\n",
       "      <td>d032ee4ee49430aaae6e5ef8f8eae7bd2a0380c6f85aa5...</td>\n",
       "      <td>18.01.2012</td>\n",
       "      <td>29.12.2014</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>242014.0</td>\n",
       "      <td>375</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651683</th>\n",
       "      <td>12</td>\n",
       "      <td>0dbb6b3719f127e454ae58631b91fc20bd9f85651857ce...</td>\n",
       "      <td>16da84e431140cd481463dc8116b0c8f9d0293b2897611...</td>\n",
       "      <td>17.09.2009</td>\n",
       "      <td>23.12.2014</td>\n",
       "      <td>112.0</td>\n",
       "      <td>74</td>\n",
       "      <td>192014.0</td>\n",
       "      <td>925</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[70.0, 80.0)</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651684</th>\n",
       "      <td>12</td>\n",
       "      <td>f40a7679aca33dcbfdbb48516b8774850fee823860df48...</td>\n",
       "      <td>631ebaa55324598d6cddc2c35b5cc20e3e45810c9765c5...</td>\n",
       "      <td>25.04.2013</td>\n",
       "      <td>14.11.2014</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>252014.0</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>[20.0, 30.0)</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651685</th>\n",
       "      <td>12</td>\n",
       "      <td>05e70df09fa5679093e6a7753dbb26496e358f4d41c7a2...</td>\n",
       "      <td>9556baf5f871d9ca19774cb81e8d7f218cd1e086d71636...</td>\n",
       "      <td>31.10.2014</td>\n",
       "      <td>14.12.2014</td>\n",
       "      <td>112.0</td>\n",
       "      <td>0</td>\n",
       "      <td>252014.0</td>\n",
       "      <td>3685</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>651686 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        bsz                                             VersNR  \\\n",
       "0        12  5fbf17e3442744ced0e3ccda244f16bd123a118702ad1f...   \n",
       "1        12  f0c503761b7eafc6a7faf07a92714a774ca51fe7bf18fc...   \n",
       "2        12  a47e2e3e4353a538542826e69004ef971d23a044070dd9...   \n",
       "3        12  5d3c3822c07bfe93ede8d473960b6e72f6703f2eec411f...   \n",
       "4        12  109237a2a52e5a8be83c68f093f7cb2aeb871c88bf026c...   \n",
       "...     ...                                                ...   \n",
       "651681   12  762c753bd091e3e142f88a8080024b0d5444bcd51f6e00...   \n",
       "651682   12  e77e867a81297890c6e8545b60bc631d07dd3bb1aae590...   \n",
       "651683   12  0dbb6b3719f127e454ae58631b91fc20bd9f85651857ce...   \n",
       "651684   12  f40a7679aca33dcbfdbb48516b8774850fee823860df48...   \n",
       "651685   12  05e70df09fa5679093e6a7753dbb26496e358f4d41c7a2...   \n",
       "\n",
       "                                               Vermittler         von  \\\n",
       "0       1727d2c592180aa82286e77bc3e8fe33811f20cd92e28b...  01.01.2018   \n",
       "1       048cc7d1430d3c5ec0380b0221bbffdde775dfbe806cc8...  01.01.2018   \n",
       "2       d257f91ba3ea3bfff168ab0fc06c0261663c36a4220b33...  01.01.2018   \n",
       "3       a2eb70645d686d45dc071c22788e5afa1d4e4b43b26fba...  01.01.2018   \n",
       "4       2ceee4ad20193f2da03bf0c51be2b10eae7c410cdfddb5...  01.01.2018   \n",
       "...                                                   ...         ...   \n",
       "651681  71beb4a3aefe6928f5f27b3edc4ab469df827572f8f75e...  10.12.2010   \n",
       "651682  d032ee4ee49430aaae6e5ef8f8eae7bd2a0380c6f85aa5...  18.01.2012   \n",
       "651683  16da84e431140cd481463dc8116b0c8f9d0293b2897611...  17.09.2009   \n",
       "651684  631ebaa55324598d6cddc2c35b5cc20e3e45810c9765c5...  25.04.2013   \n",
       "651685  9556baf5f871d9ca19774cb81e8d7f218cd1e086d71636...  31.10.2014   \n",
       "\n",
       "               bis    wkz  sfr      taja  szlj  sres  ...  PLZHalter  \\\n",
       "0       31.12.2018  112.0   20  122018.0     0  2500  ...        0.0   \n",
       "1       31.12.2018  112.0   20  392018.0     0     0  ...    52078.0   \n",
       "2       31.12.2018  112.0   20  252018.0     0     0  ...    63477.0   \n",
       "3       31.12.2018  112.0   20  202018.0     0     0  ...    35781.0   \n",
       "4       31.12.2018  112.0   20  252018.0     0     0  ...    33428.0   \n",
       "...            ...    ...  ...       ...   ...   ...  ...        ...   \n",
       "651681  23.12.2014  112.0   20  222014.0  4015     0  ...        NaN   \n",
       "651682  29.12.2014  112.0   20  242014.0   375     0  ...        NaN   \n",
       "651683  23.12.2014  112.0   74  192014.0   925     0  ...        NaN   \n",
       "651684  14.11.2014  112.0   20  252014.0    30     0  ...        NaN   \n",
       "651685  14.12.2014  112.0    0  252014.0  3685     0  ...        NaN   \n",
       "\n",
       "        VNGeschle  KZRabSchutz  BeglF KH  VK  TK     sfr_group vngeb_year  \\\n",
       "0             0.0          0.0    0.0  0   1   0  [20.0, 30.0)       1964   \n",
       "1             3.0          0.0    0.0  0   1   0  [20.0, 30.0)          0   \n",
       "2             3.0          0.0    0.0  0   1   0  [20.0, 30.0)          0   \n",
       "3             1.0          0.0    0.0  0   1   0  [20.0, 30.0)       1980   \n",
       "4             0.0          0.0    0.0  0   1   0  [20.0, 30.0)       1967   \n",
       "...           ...          ...    ... ..  ..  ..           ...        ...   \n",
       "651681        NaN          NaN    NaN  0   1   0  [20.0, 30.0)          0   \n",
       "651682        NaN          NaN    NaN  0   1   0  [20.0, 30.0)          0   \n",
       "651683        NaN          NaN    NaN  0   1   0  [70.0, 80.0)          0   \n",
       "651684        NaN          NaN    NaN  0   1   0  [20.0, 30.0)          0   \n",
       "651685        NaN          NaN    NaN  0   1   0           NaN          0   \n",
       "\n",
       "               ezu_group  \n",
       "0       [2000.0, 2005.0)  \n",
       "1       [2015.0, 2020.0)  \n",
       "2       [2010.0, 2015.0)  \n",
       "3       [2005.0, 2010.0)  \n",
       "4       [2005.0, 2010.0)  \n",
       "...                  ...  \n",
       "651681               NaN  \n",
       "651682               NaN  \n",
       "651683               NaN  \n",
       "651684               NaN  \n",
       "651685               NaN  \n",
       "\n",
       "[651686 rows x 35 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new column „ezu_group“ based on column „ezuJJJJ“ in order to create 5 aggregated bins: \"1950-99\",\"2000-04\",\"2005-09\",\"2010-14\",\"2015-18„\n",
    "ez_ = VW_data[\"ezuJJJJ\"]\n",
    "ez_bins = [1950, 2000, 2005, 2010, 2015, 2020]\n",
    "\n",
    "VW_data[\"ezu_group\"] = pd.cut(ez_, ez_bins, right=False)\n",
    "VW_data.drop(VW_data.filter(regex=\"Unnamed\"),axis=1, inplace=True) # Unnamed removed\n",
    "VW_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "fpqJr13dK6Kl",
    "outputId": "717a4415-0568-4ebe-f28d-29ddadcf7753"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 0.  2.  3.  1. nan]\n",
      "[ 0.  2.  1. nan]\n"
     ]
    }
   ],
   "source": [
    "# In column „nutzart“ replace value 3 by 0\n",
    "print(VW_data[\"nutzart\"].unique())\n",
    "VW_data[\"nutzart\"].replace(3, 0, inplace=True)\n",
    "print(VW_data[\"nutzart\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "X2BqVynSK6Km",
    "outputId": "b560bf41-3dd6-4acd-996f-3cf93692fd92",
    "scrolled": true
   },
   "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>nutzart</th>\n",
       "      <th>Private</th>\n",
       "      <th>Commercial</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nutzart Private Commercial\n",
       "0      0.0       0          0\n",
       "1      2.0       0          1\n",
       "2      0.0       0          0\n",
       "3      1.0       1          0\n",
       "4      0.0       0          0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create two new dummy columns „Private“ and „Commercial“ containing 0 or 1, if „nutzart“ is equal to 1 and 2, respectively\n",
    "VW_data[\"Private\"]    = lambda_apply_V(df=VW_data, subset='nutzart', v2=1)\n",
    "VW_data[\"Commercial\"] = lambda_apply_V(df=VW_data, subset='nutzart', v2=2)\n",
    "VW_data[['nutzart', \"Private\", \"Commercial\"]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "f18vRfZ5K6Km",
    "outputId": "606ea332-cc56-45cd-c313-b69a990f17a4",
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 0. 55. 52. 51. 54.  1.  3.  9. 16.  7. 11.  5.  8. 17. 31.  4. 19.  2.\n",
      " 27. 15. 13. 20. 21. nan  6. 12. 23.]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([ 0., 55., 52., 51., 54., nan])"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# In column „nutzkreis“ replace all values between 1 and 50 by np.nan\n",
    "print(VW_data[\"nutzkreis\"].unique())\n",
    "VW_data[\"nutzkreis\"] = np.where(VW_data['nutzkreis'].between(1,50), np.nan, VW_data['nutzkreis'])\n",
    "VW_data[\"nutzkreis\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "id": "Yk8g0zMGK6Kn"
   },
   "outputs": [],
   "source": [
    "# Create three new dummy columns „DriverMale“, „DriverFemale“ and „DriverFirm“ containing 0 or 1, if „fahrgeschl“ is equal to 1, 2 and 3, respectively\n",
    "VW_data[\"DriverMale\"]   = lambda_apply_V(df=VW_data, subset='fahrgeschl', v2=1)\n",
    "VW_data[\"DriverFemale\"] = lambda_apply_V(df=VW_data, subset='fahrgeschl', v2=2)\n",
    "VW_data[\"DriverFirm\"]   = lambda_apply_V(df=VW_data, subset='fahrgeschl', v2=3)\n",
    "\n",
    "VW_data = VW_data.astype(dtype=dict(VW_data.dtypes)) # dtype normalization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "k1jbofCHK6Kn",
    "outputId": "43b7675e-3ebe-4658-b2e8-fbf36fbc341f",
    "scrolled": true
   },
   "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>gar</th>\n",
       "      <th>gar_new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gar      gar_new\n",
       "0  0.0  Gar_Unknown\n",
       "1  1.0  Gar_Unknown\n",
       "2  1.0  Gar_Unknown\n",
       "3  1.0  Gar_Unknown\n",
       "4  0.0  Gar_Unknown"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new column „gar_new“ based on „gar“ replacing 0,1 by „Gar_Unknown“, 2 by „Gar_No“ and 3,4,5,6 by „Gar_Yes“\n",
    "VW_data[\"gar_new\"] = VW_data[\"gar\"].replace([0, 1], \"Gar_Unknown\").replace(2, \"Gar_No\").replace([3,4,5,6], \"Gar_Yes\")\n",
    "VW_data[['gar',\"gar_new\"]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "id": "k-1jU_nxK6Kn"
   },
   "outputs": [],
   "source": [
    "# Create a new column „ Wohngeb_new“ based on „ Wohngeb“ replacing 0 by „Property_Unknown“, 1,2,3 by „Property_Yes“ and 9 by „Property_No“\n",
    "VW_data[\"Wohngeb_new\"] = VW_data[\"Wohngeb\"].replace(0, \"Property_Unknown\").replace([1, 2, 3], \"Property_Yes\").replace(9, \"Property_No\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "id": "T4nzg9RVK6Ko"
   },
   "outputs": [],
   "source": [
    "# In column „hst“ replace everything that is not a number by np.nan\n",
    "VW_data[\"hst\"] = pd.to_numeric(VW_data[\"hst\"], errors=\"coerce\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "yrvrqRm0K6Ko",
    "outputId": "6a2040d9-08c1-478e-b074-6df1abbe2e98"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         5013.0\n",
       "1         1844.0\n",
       "2         3333.0\n",
       "3         2062.0\n",
       "4         9999.0\n",
       "           ...  \n",
       "651681       NaN\n",
       "651682       NaN\n",
       "651683       NaN\n",
       "651684       NaN\n",
       "651685       NaN\n",
       "Name: hst, Length: 651686, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert „hst“ to numeric data type\n",
    "pd.to_numeric(VW_data[\"hst\"], errors='ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 235
    },
    "id": "sTfyViECK6Kp",
    "outputId": "dc8999f8-0db7-4848-8630-88c74dbc953a",
    "scrolled": false
   },
   "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>Car</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hst</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BMW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Daimler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Opel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Opel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Car\n",
       "hst         \n",
       "5        BMW\n",
       "9    Daimler\n",
       "35      Opel\n",
       "36       NaN\n",
       "39      Opel"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the association provided in file „ W04_Association.xlsx“ to create a new column „hst_group“ based on „hst“ containing \n",
    "# the names of the car manufacturers („BMW“, „Audi“, „VW“, ...). Everything that cannot be associated should be called „Other“\n",
    "W04_ex.set_index('hst', inplace=True)\n",
    "W04_ex.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "gkeW7ZtpK6Kp",
    "outputId": "8bed7acd-241a-4191-f1ca-9b3e8f06687e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Toyota', 'Opel', 'Renault', nan, 'Daimler', 'Chrysler', 'Smart',\n",
       "       'VW', 'BMW', 'Ford', 'Seat', 'GM', 'Hyundai', 'Saab', 'Citroen',\n",
       "       'Audi', 'Suzuki', 'Volvo', 'Nissan', 'Fiat', 'Skoda', 'Daihatsu',\n",
       "       'Peugeot', 'Mazda', 'Honda', 'Kia', 'Mitsubishi', 'Dacia',\n",
       "       'Porsche', 'Fuji', 'Landrover', 'Jaguar', 'Other'], dtype=object)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hst_group = []\n",
    "for i in VW_data[\"hst\"]:\n",
    "    if pd.isna(i):\n",
    "        hst_group.append('Other')\n",
    "    elif int(i) in list(W04_ex.index):\n",
    "        hst_group.append(W04_ex.loc[int(i), 'Car'])\n",
    "    else:\n",
    "        hst_group.append('Other')\n",
    "\n",
    "VW_data[\"hst_group\"] = hst_group\n",
    "VW_data[\"hst_group\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 309
    },
    "id": "k0t7x8ONK6Kq",
    "outputId": "054e9fa0-e919-4b53-d2ba-286e5e1f1ea2"
   },
   "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>bsz</th>\n",
       "      <th>VersNR</th>\n",
       "      <th>Vermittler</th>\n",
       "      <th>von</th>\n",
       "      <th>bis</th>\n",
       "      <th>wkz</th>\n",
       "      <th>sfr</th>\n",
       "      <th>taja</th>\n",
       "      <th>szlj</th>\n",
       "      <th>sres</th>\n",
       "      <th>...</th>\n",
       "      <th>DriverFemale</th>\n",
       "      <th>DriverFirm</th>\n",
       "      <th>gar_new</th>\n",
       "      <th>Wohngeb_new</th>\n",
       "      <th>hst_group</th>\n",
       "      <th>sanz_adj</th>\n",
       "      <th>vbeitr_adj</th>\n",
       "      <th>szlj_adj</th>\n",
       "      <th>sres_adj</th>\n",
       "      <th>saufw_adj</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>5fbf17e3442744ced0e3ccda244f16bd123a118702ad1f...</td>\n",
       "      <td>1727d2c592180aa82286e77bc3e8fe33811f20cd92e28b...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>122018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "      <td>Property_Unknown</td>\n",
       "      <td>Toyota</td>\n",
       "      <td>1.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2275.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>f0c503761b7eafc6a7faf07a92714a774ca51fe7bf18fc...</td>\n",
       "      <td>048cc7d1430d3c5ec0380b0221bbffdde775dfbe806cc8...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>392018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "      <td>Property_No</td>\n",
       "      <td>Opel</td>\n",
       "      <td>0.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>a47e2e3e4353a538542826e69004ef971d23a044070dd9...</td>\n",
       "      <td>d257f91ba3ea3bfff168ab0fc06c0261663c36a4220b33...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>252018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "      <td>Property_Unknown</td>\n",
       "      <td>Renault</td>\n",
       "      <td>0.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>5d3c3822c07bfe93ede8d473960b6e72f6703f2eec411f...</td>\n",
       "      <td>a2eb70645d686d45dc071c22788e5afa1d4e4b43b26fba...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>202018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "      <td>Property_Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>670.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>109237a2a52e5a8be83c68f093f7cb2aeb871c88bf026c...</td>\n",
       "      <td>2ceee4ad20193f2da03bf0c51be2b10eae7c410cdfddb5...</td>\n",
       "      <td>01.01.2018</td>\n",
       "      <td>31.12.2018</td>\n",
       "      <td>112.0</td>\n",
       "      <td>20</td>\n",
       "      <td>252018.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Gar_Unknown</td>\n",
       "      <td>Property_Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   bsz                                             VersNR  \\\n",
       "0   12  5fbf17e3442744ced0e3ccda244f16bd123a118702ad1f...   \n",
       "1   12  f0c503761b7eafc6a7faf07a92714a774ca51fe7bf18fc...   \n",
       "2   12  a47e2e3e4353a538542826e69004ef971d23a044070dd9...   \n",
       "3   12  5d3c3822c07bfe93ede8d473960b6e72f6703f2eec411f...   \n",
       "4   12  109237a2a52e5a8be83c68f093f7cb2aeb871c88bf026c...   \n",
       "\n",
       "                                          Vermittler         von         bis  \\\n",
       "0  1727d2c592180aa82286e77bc3e8fe33811f20cd92e28b...  01.01.2018  31.12.2018   \n",
       "1  048cc7d1430d3c5ec0380b0221bbffdde775dfbe806cc8...  01.01.2018  31.12.2018   \n",
       "2  d257f91ba3ea3bfff168ab0fc06c0261663c36a4220b33...  01.01.2018  31.12.2018   \n",
       "3  a2eb70645d686d45dc071c22788e5afa1d4e4b43b26fba...  01.01.2018  31.12.2018   \n",
       "4  2ceee4ad20193f2da03bf0c51be2b10eae7c410cdfddb5...  01.01.2018  31.12.2018   \n",
       "\n",
       "     wkz  sfr      taja  szlj  sres  ...  DriverFemale  DriverFirm  \\\n",
       "0  112.0   20  122018.0     0  2500  ...             0           0   \n",
       "1  112.0   20  392018.0     0     0  ...             0           1   \n",
       "2  112.0   20  252018.0     0     0  ...             0           0   \n",
       "3  112.0   20  202018.0     0     0  ...             0           1   \n",
       "4  112.0   20  252018.0     0     0  ...             0           0   \n",
       "\n",
       "       gar_new       Wohngeb_new hst_group  sanz_adj  vbeitr_adj  szlj_adj  \\\n",
       "0  Gar_Unknown  Property_Unknown    Toyota       1.0       210.0       0.0   \n",
       "1  Gar_Unknown       Property_No      Opel       0.0       510.0       0.0   \n",
       "2  Gar_Unknown  Property_Unknown   Renault       0.0       140.0       0.0   \n",
       "3  Gar_Unknown  Property_Unknown       NaN       0.0       670.0       0.0   \n",
       "4  Gar_Unknown  Property_Unknown       NaN       0.0       340.0       0.0   \n",
       "\n",
       "   sres_adj saufw_adj  \n",
       "0    2500.0    2275.0  \n",
       "1       0.0       0.0  \n",
       "2       0.0       0.0  \n",
       "3       0.0       0.0  \n",
       "4       0.0       0.0  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate new columns „sanz_adj“, „vbeitr_adj“, „szlj_adj“, „sres_adj“ and „saufw_adj“ by dividing the respective columns by „je“, \n",
    "# e.g. szlj_adj = szlj / je\n",
    "for i in ['sanz', 'vbeitr', 'szlj', 'sres', 'saufw']:\n",
    "    VW_data[f'{i}_adj'] = VW_data[i] / VW_data['je']\n",
    "\n",
    "VW_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lhEPdvHnK6Kr"
   },
   "source": [
    "# Endresults"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "r5zbTiaFK6Kr",
    "outputId": "80bb73b3-b1d2-456c-f8fe-d787404aa1e0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "562594"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number passanger cars(wkz112)\n",
    "a1 = len(VW_data[VW_data[\"wkz\"] == 112])\n",
    "a1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "x0KWNp_SK6Kr",
    "outputId": "486ddba3-a032-4dda-95b3-27ae35c0b508"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39068"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number motorcycles/scooters (wkz3)\n",
    "a2 = len(VW_data[VW_data[\"wkz\"] == 3])\n",
    "a2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "DRXghsV8K6Ks",
    "outputId": "16740c8a-9197-4ce1-c961-a53400609ca4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13324"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#number Trailers factory / private (wkz581)\n",
    "a3= len(VW_data[VW_data[\"wkz\"] == 581])\n",
    "a3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 295
    },
    "id": "1v3WnlQ2K6Ks",
    "outputId": "47a4b186-76d8-464e-f6a8-d6e0f80dab59"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYoAAAEFCAYAAAD9mKAdAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAY70lEQVR4nO3df7DddX3n8edLogyKIEhkMaENFdSiXVGyAcfu1A4uoG3FtjgNzkjWocZxsZXW7iy47dDVsotdLbtOKy5dIuC6IsVaqIoYwW7HVoFA0YiIREGIIEZDEduKBt/7x/dzpye3N58k95yTm2uej5kz93s/5/t5n+/5+brf7+fzPTdVhSRJO/KEhd4ASdLezaCQJHUZFJKkLoNCktRlUEiSugwKSVLXkoXegEk77LDDasWKFQu9GZK0qNx6663frqqlc133YxcUK1asYMOGDQu9GZK0qCT5+o6u89CTJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0/difc7ciKcz+2W+vfe+EvTGlLJGlxcY9CktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6dhoUSY5M8ukkdya5I8mbW/vvJ/lGktvb5RUjfc5LsinJXUlOGWk/PsnGdt27k6S175/kQ639piQrRvqsSXJ3u6yZ6L2XJO3UrvzP7G3AW6rqtiRPBW5Nsr5dd1FVvXN05STHAquB5wHPBD6V5NlV9ThwMbAW+BzwceBU4DrgLODhqjo6yWrgHcCvJTkUOB9YCVS77Wur6uHx7rYkaVftdI+iqh6sqtva8qPAncCyTpfTgCur6rGqugfYBKxKcgRwUFV9tqoKuAJ41Uify9vy1cBJbW/jFGB9VW1t4bCeIVwkSXvIbo1RtENCLwRuak1vSvKFJOuSHNLalgH3j3Tb3NqWteXZ7dv1qaptwCPA0zu1JEl7yC4HRZIDgQ8D51TVdxkOIz0LOA54EHjXzKpzdK9O+3z7jG7b2iQbkmzYsmVL725IknbTLgVFkicyhMQHqurPAarqoap6vKp+BPwpsKqtvhk4cqT7cuCB1r58jvbt+iRZAhwMbO3U2k5VXVJVK6tq5dKlS3flLkmSdtGuzHoKcClwZ1X90Uj7ESOr/TLwxbZ8LbC6zWQ6CjgGuLmqHgQeTXJiq3kmcM1In5kZTacDN7ZxjOuBk5Mc0g5tndzaJEl7yK7MenoJ8FpgY5LbW9tbgTOSHMdwKOhe4A0AVXVHkquALzHMmDq7zXgCeCNwGXAAw2yn61r7pcD7k2xi2JNY3WptTfJ24Ja23tuqaut87qgkaX52GhRV9RnmHiv4eKfPBcAFc7RvAJ4/R/v3gVfvoNY6YN3OtlOSNB2emS1J6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHUZFJKkLoNCktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR17TQokhyZ5NNJ7kxyR5I3t/ZDk6xPcnf7echIn/OSbEpyV5JTRtqPT7KxXffuJGnt+yf5UGu/KcmKkT5r2m3cnWTNRO+9JGmndmWPYhvwlqr6aeBE4OwkxwLnAjdU1THADe132nWrgecBpwLvSbJfq3UxsBY4pl1Obe1nAQ9X1dHARcA7Wq1DgfOBE4BVwPmjgSRJmr6dBkVVPVhVt7XlR4E7gWXAacDlbbXLgVe15dOAK6vqsaq6B9gErEpyBHBQVX22qgq4YlafmVpXAye1vY1TgPVVtbWqHgbW88/hIknaA3ZrjKIdEnohcBNweFU9CEOYAM9oqy0D7h/ptrm1LWvLs9u361NV24BHgKd3akmS9pBdDookBwIfBs6pqu/2Vp2jrTrt8+0zum1rk2xIsmHLli2dTZMk7a5dCookT2QIiQ9U1Z+35ofa4STaz2+19s3AkSPdlwMPtPblc7Rv1yfJEuBgYGun1naq6pKqWllVK5cuXbord0mStIt2ZdZTgEuBO6vqj0auuhaYmYW0BrhmpH11m8l0FMOg9c3t8NSjSU5sNc+c1Wem1unAjW0c43rg5CSHtEHsk1ubJGkPWbIL67wEeC2wMcntre2twIXAVUnOAu4DXg1QVXckuQr4EsOMqbOr6vHW743AZcABwHXtAkMQvT/JJoY9idWt1tYkbwduaeu9raq2zu+uSpLmY6dBUVWfYe6xAoCTdtDnAuCCOdo3AM+fo/37tKCZ47p1wLqdbackaTo8M1uS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHUZFJKkLoNCktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnq2mlQJFmX5FtJvjjS9vtJvpHk9nZ5xch15yXZlOSuJKeMtB+fZGO77t1J0tr3T/Kh1n5TkhUjfdYkubtd1kzsXkuSdtmu7FFcBpw6R/tFVXVcu3wcIMmxwGrgea3Pe5Ls19a/GFgLHNMuMzXPAh6uqqOBi4B3tFqHAucDJwCrgPOTHLLb91CSNJadBkVV/TWwdRfrnQZcWVWPVdU9wCZgVZIjgIOq6rNVVcAVwKtG+lzelq8GTmp7G6cA66tqa1U9DKxn7sCSJE3ROGMUb0ryhXZoauYv/WXA/SPrbG5ty9ry7Pbt+lTVNuAR4OmdWpKkPWi+QXEx8CzgOOBB4F2tPXOsW532+fbZTpK1STYk2bBly5bOZkuSdte8gqKqHqqqx6vqR8CfMowhwPBX/5Ejqy4HHmjty+do365PkiXAwQyHunZUa67tuaSqVlbVyqVLl87nLkmSdmBeQdHGHGb8MjAzI+paYHWbyXQUw6D1zVX1IPBokhPb+MOZwDUjfWZmNJ0O3NjGMa4HTk5ySDu0dXJrkyTtQUt2tkKSDwIvBQ5LsplhJtJLkxzHcCjoXuANAFV1R5KrgC8B24Czq+rxVuqNDDOoDgCuaxeAS4H3J9nEsCexutXamuTtwC1tvbdV1a4OqkuSJmSnQVFVZ8zRfGln/QuAC+Zo3wA8f4727wOv3kGtdcC6nW2jJGl6PDNbktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHUZFJKkLoNCktRlUEiSugwKSVKXQSFJ6tppUCRZl+RbSb440nZokvVJ7m4/Dxm57rwkm5LcleSUkfbjk2xs1707SVr7/kk+1NpvSrJipM+adht3J1kzsXstSdplu7JHcRlw6qy2c4EbquoY4Ib2O0mOBVYDz2t93pNkv9bnYmAtcEy7zNQ8C3i4qo4GLgLe0WodCpwPnACsAs4fDSRJ0p6x06Coqr8Gts5qPg24vC1fDrxqpP3Kqnqsqu4BNgGrkhwBHFRVn62qAq6Y1Wem1tXASW1v4xRgfVVtraqHgfX8y8CSJE3ZfMcoDq+qBwHaz2e09mXA/SPrbW5ty9ry7Pbt+lTVNuAR4OmdWv9CkrVJNiTZsGXLlnneJUnSXCY9mJ052qrTPt8+2zdWXVJVK6tq5dKlS3dpQyVJu2a+QfFQO5xE+/mt1r4ZOHJkveXAA619+Rzt2/VJsgQ4mOFQ145qSZL2oPkGxbXAzCykNcA1I+2r20ymoxgGrW9uh6ceTXJiG384c1afmVqnAze2cYzrgZOTHNIGsU9ubZKkPWjJzlZI8kHgpcBhSTYzzES6ELgqyVnAfcCrAarqjiRXAV8CtgFnV9XjrdQbGWZQHQBc1y4AlwLvT7KJYU9idau1NcnbgVvaem+rqtmD6pKkKdtpUFTVGTu46qQdrH8BcMEc7RuA58/R/n1a0Mxx3Tpg3c62UZI0PZ6ZLUnqMigkSV0GhSSpy6CQJHUZFJKkLoNCktRlUEiSugwKSVKXQSFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeoyKCRJXQaFJKnLoJAkdRkUkqQug0KS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHWNFRRJ7k2yMcntSTa0tkOTrE9yd/t5yMj65yXZlOSuJKeMtB/f6mxK8u4kae37J/lQa78pyYpxtleStPsmsUfx81V1XFWtbL+fC9xQVccAN7TfSXIssBp4HnAq8J4k+7U+FwNrgWPa5dTWfhbwcFUdDVwEvGMC2ytJ2g3TOPR0GnB5W74ceNVI+5VV9VhV3QNsAlYlOQI4qKo+W1UFXDGrz0ytq4GTZvY2JEl7xrhBUcAnk9yaZG1rO7yqHgRoP5/R2pcB94/03dzalrXl2e3b9amqbcAjwNNnb0SStUk2JNmwZcuWMe+SJGnUkjH7v6SqHkjyDGB9ki931p1rT6A67b0+2zdUXQJcArBy5cp/cb0kaf7GCoqqeqD9/FaSjwCrgIeSHFFVD7bDSt9qq28Gjhzpvhx4oLUvn6N9tM/mJEuAg4Gt42zztKw492O7tf69F/7ClLZEkiZr3oeekjwlyVNnloGTgS8C1wJr2mprgGva8rXA6jaT6SiGQeub2+GpR5Oc2MYfzpzVZ6bW6cCNbRxDkrSHjLNHcTjwkTa2vAT4v1X1iSS3AFclOQu4D3g1QFXdkeQq4EvANuDsqnq81XojcBlwAHBduwBcCrw/ySaGPYnVY2yvJGke5h0UVfU14AVztH8HOGkHfS4ALpijfQPw/Dnav08LGknSwvDMbElS17iznrSHOFguaaG4RyFJ6jIoJEldBoUkqcugkCR1GRSSpC6DQpLUZVBIkroMCklSl0EhSeryzGwBnvktaccMCu0RBpG0eBkU+rFgEEnTY1BIu8Ag0r7MwWxJUpd7FNJewD0W7c0MCmkfYBBpHB56kiR1uUchaWzT3mNxj2hhGRSS9mmG0M556EmS1GVQSJK6DApJUpdBIUnqMigkSV0GhSSpy6CQJHUtiqBIcmqSu5JsSnLuQm+PJO1L9vqgSLIf8CfAy4FjgTOSHLuwWyVJ+469PiiAVcCmqvpaVf0AuBI4bYG3SZL2Gamqhd6GriSnA6dW1a+3318LnFBVbxpZZy2wtv36HOCu3biJw4BvT2hzrW996//41F/M2z6f+j9ZVUvnumIxfNdT5mjbLt2q6hLgknkVTzZU1cr59LW+9a3/41t/MW/7pOsvhkNPm4EjR35fDjywQNsiSfucxRAUtwDHJDkqyZOA1cC1C7xNkrTP2OsPPVXVtiRvAq4H9gPWVdUdE7yJeR2ysr71rf9jX38xb/tE6+/1g9mSpIW1GA49SZIWkEEhSeoyKCRJXQaFJKnLoJiwJKuS/Ju2fGyS307yignVPiHJQW35gCT/JclfJnlHkoMnUP83kxy58zUnI8nPtsfn5AnWfG6Sk5IcOKv91Endxqy6h06ozpOSnJnkZe331yT54yRnJ3ni3l5/jtubxnM7tffWTm73dROsdXiSFyV5YZLDJ1V3B7d1xcRq7auzntqTtIzhLO8HquqhCdQ8n+HLC5cA64ETgL8CXgZcX1UXjFn/DuAFbcrwJcA/AlcDJ7X2Xxmz/iPAPwBfBT4I/FlVbRmn5qz6N1fVqrb8euBs4CPAycBfVtWFY9b/zVbzTuA44M1VdU277raqetGY9X+3qv6gLR8L/AXwRIZvD/i1qrppjNofYHjdPBn4e+BA4M8ZnttU1Zoxt33a9af93E71vbWT276vqn5izBrHAe8FDga+0ZqXMzwX/6Gqbhuz/uxzywL8PHAjQFW9cpz6VNU+dWH4APkcw4fJp9rly63tRWPW3shwrseTge8CB7X2A4AvTGDb7xxZvm3WdbdPoP7fMexlngxcCmwBPgGsAZ46ifojy7cAS9vyU4CNE6i/ETiwLa8ANjCExXa3PUb920aWPwa8vC2vAv52zNpfaD+XAA8B+7XfM6HXzrTr74nndprvrS/s4LIReGwC9W9n+I662e0nAp+fxGsT+D/AS4Gfaz8fbMs/N279vf6Euym4DHhDzfrrL8mJwPuAF4xRe1tVPQ78Y5KvVtV3Aarqn5L8aIy6M76Y5HVV9T7g80lWVtWGJM8GfjiB+lVVPwI+CXyyHZJ4OXAG8E5gzi8M2w1PSHIIQxil2t5KVf1Dkm1j1obhw+97rea9SV4KXJ3kJ5n7O8PG8cyquq7d1s1JDhiz3hPaNw88heHD8GBgK7A/w17LuPZE/Wk+t9N+bx0OnAI8PKs9wN9OoP5TZn/mAFTV55I8ZQL1VwJvBv4z8B+r6vYk/1RV/28CtffJoJjmE/aDJE+uqn8Ejp9pbOMHk3gx/zrwP5P8LsO3Qn42yf3A/e26cW33YVpVP2T4upRrJ/BBCMOH063tdirJv6qqb7bxhEl8kH8zyXFVdTtAVX0vyS8C64CfmUD9n2q7+AGWjzzXMP6H7aUMe7b7MbzZ/yzJ1xj+4rxyzNp7ov60n9tpv7c+yrA3evvsK5L81QTqX5fkY8AVDO9XGL7D7kyGvfaxtD/wLkryZ+3nQ0zw832fG6NI8m7gWcz9hN1TI19fPo/a+1fVY3O0HwYcUVUb51t7Vr2nAj/F8ELYXBMYX2l1n11VX5lErd283ScDh1fVPWPWWc7wl+c357juJVX1N2PW/7lZTbe2MDocOL2q/mTM+s8EqKoHkjyN4fj7fVV18zh191T9HdzmpJ7bPfLemqYkL2f4XzrLGMJzM3BtVX18Crf1C8BLquqtE6m3rwUF7PEn7NCq2jrpuq320QyHyu6sqi9N4zZGbuvAmcM6i0mSV1bVovwSyWm8dqYxiaPVfVpV/f0kanVuY0lVbWvLBwLPBb42yccoyVKGQeZtDH84LrrX/FSMO8jhZbsBpZcwDJLfwTArYz3wNYY9lxdPoP6ngcPa8muBrwD/m2HA7TemfN/um0CNf80waeB+hi8sO2TkupsnUP9XZl1+FfjmzO9TfnzWjtn/d0eWj23P7T3AvcwxCDqP+scxpUkcrf62VvMs4GlTeHz/PfCd9ri8vL2vbmivpTMmUP/Ytv2bgB8AN7XH/zLg4L35tdNq/MxU31vTfAAW22UCb/ab2xP2YoYxhJ9t7S8C/mYC2/fFkeVbgKe35SczmZkfv72Dy1uArROo/xngVOBpwO+0QH1Wu+7vJlB/G8Ox5nUMExPeBzzafq6b8mvnDWP2n9qMqlbn9rkCh8nNutkI/CLwgfaBfg3DvwQ4YEKP70aG/9h2FMOsp5nXzeETeu1/DnjOyGN+eVt+PXD13vzaaTWm+t7aFweze8YddHtitWOlSbZU1WcAquq2CQ0G/zDJsqr6BvA9hnMeAB5jGKQc138F/jvDB+5skzg588Cqmhm4e2eSW4FPZPj3tpM4Bvpi4EKGEH1vVVWSl1bVJE+Yei7/fNiyGP6J1rVV9b8mdRtMfkYVTH/WzQ+r6qPAR9v2/hJDUPxJkuur6jVj1n+8qr4NfDvJ96rqqwBV9VAykQltB1TVXa3mzUne25b/NMlvTeIGpvzamep7y6DY3g/G7D/6YXrerOueNGZtgN9imLb6YYa/GG5M8gng3zL81Tyu24C/qKpbZ1+RZCKzqpIcXFWPAFTVp5P8KvBhYOwznKvqliT/DvgNhsfmPzGZAAKg1TuDYZbQzADwcuCDSa6s8U4qm+aMKpjyrBtG/siqqn8CrgKuarOSXjWB+vcl+W/AU4EvJ3kXwwmDL2M4X2BcX03yewyHs36FYQ+MNkV87M/JKb922k1M7721Tw5m78i4Z2AmeSXwqZE3+Ez7s4Bfrao/nMA2Hgy8Bng2bdYTcE1VfXkCtZ8DfKf95Tb7usNrzIHPJK9hGHz83Kz2nwB+r6peP079WTWfCfwPYGVV/dSEan4FeF4N04ZH258E3FFVx4xRe6ozqtptTG0SR5Lfqap3jlunU/8ghrO9C/hjhnMeXgd8HfiDqhorLNossLcyjFV8Hriwqh5t77efnv2anUf9qb12Wp2pvrf2uaBI8oUdXQU8u6r235Pbo8UjyZeBU6rq67PafxL4ZFU9Z2G2THu7xf7a2Re/FPBwht3tX5rj8p1p3WiStdOqbf09Vv8c4IYk1yW5pF0+wXC44s0TqD+nRfLYWL/vHBbgtQOT2f59cYxi2mdg7sikv0LC+nu4flV9IsPXpaxi+8M3t9Tw9RLTstc/NtbvW8DXDkxg+/e5Q0/T1pnZcKf1F3/9aVrsj431F9Y0t39fPPQ0NW1mw5UMCX4zwzTNMMxsONf6i7v+NC32x8b6C2vq2z/uiRhetjvp5SsM51LMbn8ScLf1F3d9XzvW31sv095+9ygm60fAM+doP4LJfMOl9Re2/jQt9sfG+gtrqtu/Lw5mT9M5DDMb7uafT2r6CeBoYN7fSmv9vab+NJ3D4n5srL+wzmGK2+9g9oQleQJTnNlg/YWtP02L/bGx/sKa5vYbFJKkLscoJEldBoUkqcugkCR1GRSSpC6DQpLU9f8BrkSsS6iXhBIAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Plotting wjz112 values\n",
    "VW_data[VW_data[\"wkz\"] == 112]['je'].value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "hzUoWSVgK6Ks",
    "outputId": "c33d1f83-b450-4e49-db61-2e7142855921",
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.08507915122937015"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of claims (sanz) per contract per year, Contract term as share of year (je) of 0.9\n",
    "cars = VW_data[VW_data[\"wkz\"] == 112]\n",
    "b1 = lambda_mean_V(df=cars, subset='je', v1=.91, subset_target='sanz')\n",
    "b1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "fEUX5xEaK6Kt",
    "outputId": "b014879a-8b51-4fda-a3b8-becdd1018ca0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.06779927301811742"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of claims (sanz) per contract per year, Contract term as share of year (je) of 1.0\n",
    "b2 = lambda_mean_V(df=cars, subset='je', v1=1.0, subset_target='sanz')\n",
    "b2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "qgH5AHksK6Kt",
    "outputId": "5639de81-4dc1-4207-be09-5d541a000029"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.06957108017946388"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of adjusted claims (sanz/je) per contract per year for mandatory motor liability (Haftpflicht, bsz=11)\n",
    "c1 = lambda_mean_V(df=cars, subset='bsz', v1=11, v2=0, subset_target='je', verbose=1)\n",
    "c1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "sqVygdjjK6Ku",
    "outputId": "08cbf1f2-e36e-4e46-fa6d-813f94e0eea5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.14897410956407023"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of adjusted claims (sanz/je) per contract per year for fully comprehensive insurance (Vollkasko, bsz=12)\n",
    "c2 = lambda_mean_V(df=cars, subset='bsz', v1=12, v2=0, subset_target='je', verbose=1)\n",
    "c2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "UgEHK26wK6Ku",
    "outputId": "b47caa20-0df2-4191-e636-cf5c9a391f5a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "59589"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of adjusted claims (sanz/je) per contract per year cases!!! for partially comprehensive insurance (Teilkasko, bsz=13)\n",
    "c3 = len(cars[(cars['bsz'] == 13)]['sanz_adj'])\n",
    "c3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "id": "HlZFGWHxK6Kv"
   },
   "outputs": [],
   "source": [
    "cars_B = cars[cars['je'] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Y3UxJIbpK6Kv",
    "outputId": "2c775b27-1aeb-4165-cc86-bb63fe012f21"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.050448773601993714"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of claims (sanz) per contract per year for mandatory motor liability (Haftpflicht, bsz=11)\n",
    "d1 = lambda_mean_V(df=cars_B, subset='bsz', v1=11, subset_target='sanz')\n",
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "mIK15mK7K6Kw",
    "outputId": "dbfe84a0-ef4c-4ed6-8466-c9b9002eb52c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.1079995869996673"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of claims (sanz) per contract per year for fully comprehensive insurance (Vollkasko, bsz=12)\n",
    "d2 = lambda_mean_V(df=cars_B, subset='bsz', v1=12, subset_target='sanz')\n",
    "d2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "l2nlVYKjK6Kw",
    "outputId": "381fae30-7361-4752-a785-8f52edfdc2b1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31851"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of claims (sanz) per contract per year cases!!! for partially comprehensive insurance (Teilkasko, bsz=13)\n",
    "d3 = len(cars_B[cars_B['bsz']==13]['sanz'])\n",
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "id": "bvF8kjYuK6Kx"
   },
   "outputs": [],
   "source": [
    "cars_B_11 = cars_B[cars_B['bsz'] == 11]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "s3l0KqPYK6Kx",
    "outputId": "cb38a446-4b22-4d91-86ee-ec281de656da"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "318.4917451730249"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average payment per contract, in EUR per year, Premium earned (vbeitr) for male\n",
    "e1 = lambda_mean_V(df=cars_B_11, subset='fahrgeschl', v1=1, subset_target='vbeitr')\n",
    "e1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "XZbBg1OcK6Kx",
    "outputId": "10827baf-05a1-4f23-c45a-b94e67c410bd"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "210.78231176370156"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average payment per contract, in EUR per year, Claims incurred (saufw) for female\n",
    "e2 = lambda_mean_V(df=cars_B_11, subset='fahrgeschl', v1=2, subset_target='saufw')\n",
    "e2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "X565JMwOK6Ky",
    "outputId": "56c075af-dc51-47e3-f751-b3acc38721d3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "69.69506430264333"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average payment per contract, in EUR per year, Claims paid (szlj) for companies\n",
    "e3 = lambda_mean_V(df=cars_B_11, subset='fahrgeschl', v1=3, subset_target='szlj')\n",
    "e3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "id": "nlHfjnjtK6Ky"
   },
   "outputs": [],
   "source": [
    "cars_12 = cars[cars['bsz'] == 12]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "OhVyEaEQK6Ky",
    "outputId": "e4f6b226-6ff1-4b37-9014-c15b95d29aa8"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "340.2120844254537"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average adjusted payment per contract, in EUR per year, Premium earned (vbeitr/je) for companies\n",
    "f1 = lambda_mean_V(df=cars_12, subset='fahrgeschl', v1=3, subset_target='vbeitr_adj')\n",
    "f1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ZESFBNUhK6Kz",
    "outputId": "019cbb25-cb77-46f8-8bd1-3d47b5ab0e7a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "391.6169535256922"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average adjusted payment per contract, in EUR per year, Claims incurred (saufw/je) for male\n",
    "f2 = lambda_mean_V(df=cars_12, subset='fahrgeschl', v1=1, v2=0, subset_target='je', subset_threshold='saufw_adj', verbose=1)\n",
    "f2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ypqKvWl6K6Kz",
    "outputId": "068d86a8-668a-41e6-8e03-aa57835c1d05"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "259.0593717858947"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average adjusted payment per contract, in EUR per year, Claims paid (szlj/je) for companies\n",
    "f3 = lambda_mean_V(df=cars_12, subset='fahrgeschl', v1=3, v2=0, subset_target='je', subset_threshold='szlj_adj', verbose=1)\n",
    "f3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "teHaNASPK6Kz",
    "outputId": "33acfe0f-6d43-406a-ab67-e34f6d66ee37",
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-46-3dd2e8bebb1e>:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  cars_12[\"sfr_group\"] = cars_12[\"sfr_group\"].astype(str)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0         [20, 30)\n",
       "1         [20, 30)\n",
       "2         [20, 30)\n",
       "3         [20, 30)\n",
       "4         [20, 30)\n",
       "            ...   \n",
       "651681    [20, 30)\n",
       "651682    [20, 30)\n",
       "651683    [70, 80)\n",
       "651684    [20, 30)\n",
       "651685         NaN\n",
       "Name: sfr_group, Length: 168974, dtype: object"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cars_12[\"sfr_group\"] = cars_12[\"sfr_group\"].astype(str)\n",
    "cars_12[\"sfr_group\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "l88pjBUQK6K0",
    "outputId": "e70b8aeb-5d3e-451a-88ec-2c297c550ddb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "373.3372472854312"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average adjusted payment per contract, in EUR per year, Claims paid (szlj/je) [20,30)\n",
    "\n",
    "# Observation: for other of the team members it works putting '[20.0, 30.0)' instead of '[20, 30)'\n",
    "g1 = cars_12[(cars_12[\"sfr_group\"]=='[20, 30)') & (cars_12['je']!=0)]['szlj_adj'].mean()\n",
    "g1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "R116fFNSK6K0",
    "outputId": "4f7a7551-627d-426a-bc36-c7b33d4e2ba3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "213.1067460154662"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average adjusted payment per contract, in EUR per year, Premium earned (vbeitr/je) [90,110)\n",
    "g2 = cars_12[(cars_12[\"sfr_group\"]=='[90, 110)') & (cars_12['je']!=0)]['vbeitr_adj'].mean()\n",
    "g2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "id": "BzF8tY7rK6K0"
   },
   "outputs": [],
   "source": [
    "cars_B_13 = cars_B[cars_B['bsz']==13]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "KUVv6_sBK6K1",
    "outputId": "26e54b65-2b5f-465c-f966-f170a543f1f3"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-50-cf57e04720bb>:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  cars_B_13[\"ezu_group\"] = cars_B_13[\"ezu_group\"].astype(str)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "281       [1950, 2000)\n",
       "282       [2000, 2005)\n",
       "283       [1950, 2000)\n",
       "284       [1950, 2000)\n",
       "285       [1950, 2000)\n",
       "              ...     \n",
       "632659    [2000, 2005)\n",
       "632662    [2000, 2005)\n",
       "632663    [1950, 2000)\n",
       "632666    [2005, 2010)\n",
       "632667    [1950, 2000)\n",
       "Name: ezu_group, Length: 31851, dtype: object"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cars_B_13[\"ezu_group\"] = cars_B_13[\"ezu_group\"].astype(str)\n",
    "cars_B_13[\"ezu_group\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "KcDzpGKzK6K1",
    "outputId": "46588975-729e-4709-9a74-c9c23e392947"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "126.75471698113208"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average payment per contract, in EUR per year, Premium earned (vbeitr) [2015, 2020)\n",
    "h1 = cars_B_13[cars_B_13[\"ezu_group\"]=='[2015, 2020)']['vbeitr'].mean()\n",
    "h1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "2MtYdbuxK6K1",
    "outputId": "64f1798f-3eb2-4196-db99-5f0c61029de2"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "43.57172436640715"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average payment per contract, in EUR per year, Claims incurred (saufw)[2005, 2010)\n",
    "h2 = cars_B_13[cars_B_13[\"ezu_group\"]=='[2005, 2010)']['saufw'].mean()\n",
    "h2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "7adOxUz2K6K2",
    "outputId": "3bb40430-3fee-4e33-e18d-e86c87af9d93"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55.15094339622642"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average payment per contract, in EUR per year, Claims paid (szlj) [2015, 2020)\n",
    "h3 = cars_B_13[cars_B_13[\"ezu_group\"]=='[2015, 2020)']['szlj'].mean()\n",
    "h3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 514
    },
    "id": "Gz8KL1hmK6LJ",
    "outputId": "ef94cd20-4951-4834-d9f0-a96507b7ad3a",
    "scrolled": true
   },
   "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>hst_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>VW</th>\n",
       "      <td>80163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BMW</th>\n",
       "      <td>57113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Opel</th>\n",
       "      <td>43555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daimler</th>\n",
       "      <td>39754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford</th>\n",
       "      <td>30792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Audi</th>\n",
       "      <td>30450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Skoda</th>\n",
       "      <td>17809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Renault</th>\n",
       "      <td>16284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Toyota</th>\n",
       "      <td>14028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hyundai</th>\n",
       "      <td>11259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Peugeot</th>\n",
       "      <td>10174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nissan</th>\n",
       "      <td>9259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiat</th>\n",
       "      <td>8489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mazda</th>\n",
       "      <td>8260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Seat</th>\n",
       "      <td>7735</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         hst_group\n",
       "VW           80163\n",
       "BMW          57113\n",
       "Opel         43555\n",
       "Daimler      39754\n",
       "Ford         30792\n",
       "Audi         30450\n",
       "Skoda        17809\n",
       "Renault      16284\n",
       "Toyota       14028\n",
       "Hyundai      11259\n",
       "Peugeot      10174\n",
       "Nissan        9259\n",
       "Fiat          8489\n",
       "Mazda         8260\n",
       "Seat          7735"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_manufacturers = cars[\"hst_group\"].value_counts()[:15]\n",
    "top_manufacturers.to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "id": "74DBipwPK6LK"
   },
   "outputs": [],
   "source": [
    "cars_12_top15 = cars_12[(cars_12[\"hst_group\"].isin(list(top_manufacturers.index))) & (cars_12['je']!=0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "id": "0laffLaxK6LK",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "top = cars_12_top15[[\"hst_group\", 'sanz_adj']].groupby(\"hst_group\").mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 545
    },
    "id": "05t3lozsK6LK",
    "outputId": "e1a81b74-5b30-44af-a4db-905be81930a8"
   },
   "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>sanz_adj</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hst_group</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BMW</th>\n",
       "      <td>0.218931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Audi</th>\n",
       "      <td>0.208936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Daimler</th>\n",
       "      <td>0.159125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VW</th>\n",
       "      <td>0.154990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mazda</th>\n",
       "      <td>0.150986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Seat</th>\n",
       "      <td>0.147104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Skoda</th>\n",
       "      <td>0.142410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford</th>\n",
       "      <td>0.135517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Renault</th>\n",
       "      <td>0.123331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Opel</th>\n",
       "      <td>0.117761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Peugeot</th>\n",
       "      <td>0.116423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nissan</th>\n",
       "      <td>0.113324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Toyota</th>\n",
       "      <td>0.112373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hyundai</th>\n",
       "      <td>0.112344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiat</th>\n",
       "      <td>0.095790</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           sanz_adj\n",
       "hst_group          \n",
       "BMW        0.218931\n",
       "Audi       0.208936\n",
       "Daimler    0.159125\n",
       "VW         0.154990\n",
       "Mazda      0.150986\n",
       "Seat       0.147104\n",
       "Skoda      0.142410\n",
       "Ford       0.135517\n",
       "Renault    0.123331\n",
       "Opel       0.117761\n",
       "Peugeot    0.116423\n",
       "Nissan     0.113324\n",
       "Toyota     0.112373\n",
       "Hyundai    0.112344\n",
       "Fiat       0.095790"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top.sort_values('sanz_adj', inplace=True, ascending=False)\n",
    "top"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "id": "Uvmi4cMQK6LL",
    "outputId": "30a50c18-b371-405c-ad82-0b87790780f2"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'BMW'"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of adjusted claims (sanz/je) per contract per year, hst_group, highest claim brand (0.219)\n",
    "i1 = list(top.index)[0]\n",
    "i1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "id": "1MFKDLXTK6LL",
    "outputId": "87510a2b-4c40-47cd-87ac-81658e1937e7"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Fiat'"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of adjusted claims (sanz/je) per contract per year, hst_group, lowest claim brand (0.096)\n",
    "i2 = list(top.index)[-1]\n",
    "i2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "jcjF-N4YK6LL",
    "outputId": "76abb40c-f653-4b52-b7b8-a5ea9b78586d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.20893597434152358"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of adjusted claims (sanz/je) per contract per year, hst_group, second highest claim \n",
    "i3 = list(top.values)[1][0]\n",
    "i3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "_bl11RJDK6LM",
    "outputId": "19fa091b-4c58-4eb9-f5ae-fa720e9037c3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.11234447986870348"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Average number of adjusted claims (sanz/je) per contract per year, hst_group, second lowest claim \n",
    "i4 = list(top.values)[-2][0]\n",
    "i4"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "W04_.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}