src/Q5b_deep_learning_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5d635d26-265f-470e-b9e5-aa5c919f18b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import csv\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import math\n",
    "import geopy\n",
    "from geopy.geocoders import Nominatim\n",
    "from geopy.geocoders import Photon\n",
    "from sklearn import preprocessing\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.metrics import mean_absolute_error, mean_squared_error"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bc80e39-3284-4b04-8ec0-0eb9d124f5b5",
   "metadata": {},
   "source": [
    "## LOADING TRAINING AND TESTING DATASETS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9b81d2f-fede-4e6b-933a-22cb8de733cf",
   "metadata": {},
   "source": [
    "Define some useful functions for fetching the datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "c7959150-ab0f-4b19-8b7a-250eb126e903",
   "metadata": {},
   "outputs": [],
   "source": [
    "#function that replaces incorrect characters with the correct utf-8 characters\n",
    "def correct_misinterpreted_characters(text):\n",
    "    replacements = {\n",
    "        \"Ã¼\": \"ü\",\n",
    "        \"Ã¶\": \"ö\",\n",
    "        \"Ã¤\": \"ä\",\n",
    "        \"Ã\": \"Ä\"\n",
    "    }\n",
    "    for wrong, correct in replacements.items():\n",
    "        text = text.replace(wrong, correct)\n",
    "    return text\n",
    "\n",
    "# Function to correct the misinterpreted characters in the CSV file content\n",
    "def correct_csv_content(csv_content):\n",
    "    corrected_content = []\n",
    "    for row in csv_content:\n",
    "        corrected_row = [correct_misinterpreted_characters(cell) for cell in row]\n",
    "        corrected_content.append(corrected_row)\n",
    "    return corrected_content\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "cedd7d7b-1273-4d27-b7ee-9eee5d291e14",
   "metadata": {},
   "outputs": [],
   "source": [
    "#returns the data of a specific year, corrects the encoding of the year 2022 (might be better solutions)\n",
    "#removes missing data (completely random missing data, ones that fail to evaluate)\n",
    "def import_data(year, unwanted_columns=[]):\n",
    "    path = \"data\\\\\"+str(year)\n",
    "    df_reisende = pd.read_csv(path+'\\REISENDE.csv',sep=\";\",encoding = \"utf-8\")\n",
    "    df_linie = pd.read_csv(path+'\\LINIE.csv',sep=\";\",encoding = \"utf-8\")\n",
    "\n",
    "    #correcting characters on year 2022\n",
    "    if year == 2022:\n",
    "        with open(path+'\\HALTESTELLEN.csv', 'r', encoding='utf-8') as f:\n",
    "            csv_content = list(csv.reader(f, delimiter=';'))\n",
    "            corrected_content = correct_csv_content(csv_content[1:])\n",
    "            df_haltestellen = pd.DataFrame(corrected_content[1:], columns=csv_content[0])\n",
    "        df_haltestellen['Haltestellen_Id'] = df_haltestellen['Haltestellen_Id'].astype(int)\n",
    "    else:\n",
    "        df_haltestellen = pd.read_csv(path+'\\HALTESTELLEN.csv',sep=\";\",encoding = \"utf-8\")\n",
    "\n",
    "    # Merge into one table\n",
    "    merged_df = df_reisende.merge(df_haltestellen, left_on='Haltestellen_Id', right_on='Haltestellen_Id')\n",
    "    merged_df = merged_df.merge(df_haltestellen, left_on='Nach_Hst_Id', right_on='Haltestellen_Id', suffixes=('_from','_to'))\n",
    "    merged_df = merged_df.merge(df_linie[[\"Linien_Id\", \"VSYS\"]], left_on='Linien_Id', right_on='Linien_Id') #to obtain the vehicle type\n",
    "    \n",
    "    #finally drop unwanted columns\n",
    "    merged_df.dropna(inplace=True)\n",
    "    if len(unwanted_columns) > 0:\n",
    "        merged_df.drop(unwanted_columns, axis=1, inplace=True)\n",
    "    \n",
    "    return merged_df.dropna(), df_haltestellen, df_linie"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59c624b0-b267-4c92-a875-afbaceeb1836",
   "metadata": {},
   "source": [
    "#### Let's see the percentage of missing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "52d2f3e1-5ea6-4f6b-b1fe-e8c3ef83b883",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Year 2015 with percentage of missing data:5.42%\n",
      "Year 2016 with percentage of missing data:5.46%\n",
      "Year 2017 with percentage of missing data:5.35%\n",
      "Year 2018 with percentage of missing data:5.22%\n",
      "Year 2019 with percentage of missing data:5.47%\n",
      "Year 2020 with percentage of missing data:5.82%\n",
      "Year 2021 with percentage of missing data:5.02%\n",
      "Year 2022 with percentage of missing data:5.35%\n",
      "Year 2023 with percentage of missing data:5.36%\n"
     ]
    }
   ],
   "source": [
    "for i in range(2015, 2024):\n",
    "    dftmp=pd.read_csv(f'data/{i}/REISENDE.csv',sep=\";\",encoding = \"utf-8\")\n",
    "    original_size=len(dftmp)\n",
    "    \n",
    "    dfnan=dftmp[dftmp.isna().any(axis=1)]\n",
    "    nan_size=len(dfnan)\n",
    "    \n",
    "    print(f'Year {i} with percentage of missing data:{nan_size/original_size*100:.2f}%')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96c5c68c-63e6-43e6-a3ef-6d05508b1e2f",
   "metadata": {},
   "source": [
    "Define unwanted columns for train and test datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6bccb379-b768-40d1-b4d2-fcf6427ce4c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "unwanted_columns=[\n",
    " 'Anzahl_Messungen',\n",
    " 'Aussteiger',\n",
    " 'Distanz',\n",
    " 'Einsteiger',\n",
    " 'Haltestellen_Id_from',\n",
    " 'Haltestellen_Id_to',\n",
    " 'Haltestellennummer_from',\n",
    " 'Haltestellennummer_to',\n",
    " 'Haltestellenlangname_from',\n",
    " 'Haltestellenlangname_to',                \n",
    " 'ID_Abschnitt',\n",
    " 'Linien_Id',\n",
    " 'Nach_Hst_Id',\n",
    " 'Nachtnetz',\n",
    " 'Plan_Fahrt_Id',\n",
    " 'Richtung',\n",
    " 'Sequenz',\n",
    " 'Tagtyp_Id',\n",
    " 'Tage_DTV']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ebf127d-50ce-4dca-83ac-ac5bf31ac46d",
   "metadata": {},
   "source": [
    "Now fetsch the training and testing datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "4c7547f9-fe8c-4555-9ce7-c59422c52732",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting dataset of year 2022 as training set and 2023 as testing set\n",
    "df_train, stops_train, lines_train=import_data(2022, unwanted_columns)\n",
    "df_test, stops_test, lines_test=import_data(2023, unwanted_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "24ea3331-ce16-4ba3-bdfc-446fbd971eae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# find common stops and stops that are added in 2023 using set intersection\n",
    "set_stop_train=set(stops_train[\"Haltestellenkurzname\"])\n",
    "set_stop_test=set(stops_test[\"Haltestellenkurzname\"])\n",
    "                                                                    #to include stops that are added in 2023\n",
    "common_stops=set_stop_train.intersection(set_stop_test).union(set_stop_test.difference(set_stop_train))\n",
    "\n",
    "#find common linenames using set intersection\n",
    "set_lines_train=set(lines_train[\"Linienname\"])\n",
    "set_lines_test=set(lines_train[\"Linienname\"])\n",
    "common_lines=set_lines_train.intersection(set_lines_test)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e985e89a-f319-480c-9412-be9721b3079f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#filter train dataset on...\n",
    "    #stops\n",
    "df_filtered_train = df_train[df_train['Haltestellenkurzname_from'].isin(common_stops)]\n",
    "df_filtered_train = df_train[df_train['Haltestellenkurzname_to'].isin(common_stops)]\n",
    "    #line names\n",
    "df_filtered_train = df_train[df_train['Linienname'].isin(common_lines)]\n",
    "\n",
    "#filter test dataset on...\n",
    "    #stops\n",
    "df_filtered_test = df_test[df_test['Haltestellenkurzname_from'].isin(common_stops)]\n",
    "df_filtered_test = df_test[df_test['Haltestellenkurzname_to'].isin(common_stops)]\n",
    "    #line names\n",
    "df_filtered_test = df_test[df_test['Linienname'].isin(common_lines)]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1d9b7cc-cbb9-4291-9af4-9ae1ac5007c2",
   "metadata": {},
   "source": [
    "## CONVERTING TIME TO BINS OF 30 MINUTES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "845c09ac-bed0-46fd-846a-c1465363f9c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\4095238205.py:15: 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",
      "  df_filtered_test[\"FZ_AB\"]=df_filtered_test[\"FZ_AB\"].apply(convert_time_to_bins)\n"
     ]
    }
   ],
   "source": [
    "def convert_time_to_bins(time):\n",
    "    # Convert time to hours\n",
    "    hour, minute, _ = map(int, time.split(':'))\n",
    "    time_in_hours = hour + minute / 60\n",
    "    \n",
    "    # Calculate time modulo 24\n",
    "    time_modulo_24 = time_in_hours % 24\n",
    "    \n",
    "    # Calculate bin index\n",
    "    bin_index = int(time_modulo_24 * 2) / 2  # Round to nearest half-hour\n",
    "    \n",
    "    return bin_index\n",
    "    \n",
    "df_filtered_train[\"FZ_AB\"]=df_filtered_train[\"FZ_AB\"].apply(convert_time_to_bins)\n",
    "df_filtered_test[\"FZ_AB\"]=df_filtered_test[\"FZ_AB\"].apply(convert_time_to_bins)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "922a052d-c723-4d46-bed9-6880013ac318",
   "metadata": {},
   "source": [
    "## CONVERT TAG DAYS TO BOOLEAN VALUES, TO KNOW THE DAY TYPES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "53bd10d2-7eed-4936-a937-bc6137a0b29d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Linienname</th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>Haltestellenkurzname_from</th>\n",
       "      <th>Haltestellenkurzname_to</th>\n",
       "      <th>VSYS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>78</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3.22000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>78</td>\n",
       "      <td>21.5</td>\n",
       "      <td>2.83636</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>78</td>\n",
       "      <td>21.5</td>\n",
       "      <td>3.10417</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>78</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3.06897</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>78</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.57143</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974678</th>\n",
       "      <td>25</td>\n",
       "      <td>22.0</td>\n",
       "      <td>4.86957</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974679</th>\n",
       "      <td>25</td>\n",
       "      <td>22.5</td>\n",
       "      <td>4.60000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974680</th>\n",
       "      <td>25</td>\n",
       "      <td>22.5</td>\n",
       "      <td>4.08696</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974681</th>\n",
       "      <td>25</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1.81818</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974682</th>\n",
       "      <td>25</td>\n",
       "      <td>23.5</td>\n",
       "      <td>2.25000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>972924 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Linienname  FZ_AB  Besetzung  Tage_DWV  Tage_SA  Tage_SO  Tage_SA_N  \\\n",
       "0               78   21.0    3.22000      True    False    False      False   \n",
       "1               78   21.5    2.83636      True    False    False      False   \n",
       "2               78   21.5    3.10417      True    False    False      False   \n",
       "3               78   22.0    3.06897      True    False    False      False   \n",
       "4               78   22.0    2.57143      True    False    False      False   \n",
       "...            ...    ...        ...       ...      ...      ...        ...   \n",
       "974678          25   22.0    4.86957     False     True    False      False   \n",
       "974679          25   22.5    4.60000     False     True    False      False   \n",
       "974680          25   22.5    4.08696     False     True    False      False   \n",
       "974681          25   23.0    1.81818     False     True    False      False   \n",
       "974682          25   23.5    2.25000     False     True    False      False   \n",
       "\n",
       "        Tage_SO_N Haltestellenkurzname_from Haltestellenkurzname_to VSYS  \n",
       "0           False                      GRUN                    BALT    B  \n",
       "1           False                      GRUN                    BALT    B  \n",
       "2           False                      GRUN                    BALT    B  \n",
       "3           False                      GRUN                    BALT    B  \n",
       "4           False                      GRUN                    BALT    B  \n",
       "...           ...                       ...                     ...  ...  \n",
       "974678      False                      WDOL                    BDOL   SB  \n",
       "974679      False                      WDOL                    BDOL   SB  \n",
       "974680      False                      WDOL                    BDOL   SB  \n",
       "974681      False                      WDOL                    BDOL   SB  \n",
       "974682      False                      WDOL                    BDOL   SB  \n",
       "\n",
       "[972924 rows x 11 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert the tag days to boolean values, to know which is the day type, this operation is similar to the result of a one-hot encoding\n",
    "\n",
    "def division(n):\n",
    "    return False if n == 0 else True\n",
    "\n",
    "df_filtered_train['Tage_DWV']=df_filtered_train['Tage_DWV'].apply(division)\n",
    "df_filtered_train['Tage_SA']=df_filtered_train['Tage_SA'].apply(division)\n",
    "df_filtered_train['Tage_SO']=df_filtered_train['Tage_SO'].apply(division)\n",
    "df_filtered_train['Tage_SA_N']=df_filtered_train['Tage_SA_N'].apply(division)\n",
    "df_filtered_train['Tage_SO_N']=df_filtered_train['Tage_SO_N'].apply(division)\n",
    "df_filtered_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "d19961e1-afa9-4d1e-ba0b-f5bb28562af5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\3078013300.py:2: 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",
      "  df_filtered_test['Tage_DWV']=df_filtered_test['Tage_DWV'].apply(division)\n",
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\3078013300.py:3: 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",
      "  df_filtered_test['Tage_SA']=df_filtered_test['Tage_SA'].apply(division)\n",
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\3078013300.py:4: 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",
      "  df_filtered_test['Tage_SO']=df_filtered_test['Tage_SO'].apply(division)\n",
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\3078013300.py:5: 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",
      "  df_filtered_test['Tage_SA_N']=df_filtered_test['Tage_SA_N'].apply(division)\n",
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\3078013300.py:6: 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",
      "  df_filtered_test['Tage_SO_N']=df_filtered_test['Tage_SO_N'].apply(division)\n"
     ]
    },
    {
     "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>Linienname</th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>Haltestellenkurzname_from</th>\n",
       "      <th>Haltestellenkurzname_to</th>\n",
       "      <th>VSYS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.44444</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.60000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.50000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.10000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0.50000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149500</th>\n",
       "      <td>315</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.97826</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>KIND</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>BL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149501</th>\n",
       "      <td>315</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.05556</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149502</th>\n",
       "      <td>315</td>\n",
       "      <td>15.5</td>\n",
       "      <td>6.93182</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149503</th>\n",
       "      <td>315</td>\n",
       "      <td>16.5</td>\n",
       "      <td>4.26667</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149504</th>\n",
       "      <td>315</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.80000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1140359 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Linienname  FZ_AB  Besetzung  Tage_DWV  Tage_SA  Tage_SO  Tage_SA_N  \\\n",
       "0                15    9.5    0.44444     False    False     True      False   \n",
       "1                15   10.0    1.60000     False    False     True      False   \n",
       "2                15    5.0    0.50000      True    False    False      False   \n",
       "3                15    5.0    0.10000      True    False    False      False   \n",
       "4                15    5.5    0.50000      True    False    False      False   \n",
       "...             ...    ...        ...       ...      ...      ...        ...   \n",
       "1149500         315   13.0    5.97826      True    False    False      False   \n",
       "1149501         315   12.0   11.05556      True    False    False      False   \n",
       "1149502         315   15.5    6.93182      True    False    False      False   \n",
       "1149503         315   16.5    4.26667      True    False    False      False   \n",
       "1149504         315   17.0    3.80000      True    False    False      False   \n",
       "\n",
       "         Tage_SO_N Haltestellenkurzname_from Haltestellenkurzname_to VSYS  \n",
       "0            False                      MIBU                    HIRS    T  \n",
       "1            False                      MIBU                    HIRS    T  \n",
       "2            False                      MIBU                    HIRS    T  \n",
       "3            False                      MIBU                    HIRS    T  \n",
       "4            False                      MIBU                    HIRS    T  \n",
       "...            ...                       ...                     ...  ...  \n",
       "1149500      False                      KIND                    EIHH   BL  \n",
       "1149501      False                      EIHH                    KIND   BL  \n",
       "1149502      False                      EIHH                    KIND   BL  \n",
       "1149503      False                      EIHH                    KIND   BL  \n",
       "1149504      False                      EIHH                    KIND   BL  \n",
       "\n",
       "[1140359 rows x 11 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#same of test set\n",
    "df_filtered_test['Tage_DWV']=df_filtered_test['Tage_DWV'].apply(division)\n",
    "df_filtered_test['Tage_SA']=df_filtered_test['Tage_SA'].apply(division)\n",
    "df_filtered_test['Tage_SO']=df_filtered_test['Tage_SO'].apply(division)\n",
    "df_filtered_test['Tage_SA_N']=df_filtered_test['Tage_SA_N'].apply(division)\n",
    "df_filtered_test['Tage_SO_N']=df_filtered_test['Tage_SO_N'].apply(division)\n",
    "df_filtered_test"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf22739a-71c4-4a08-9d68-23ec76fda6f7",
   "metadata": {},
   "source": [
    "## DUMMIFY LINENAME TO BOOLEAN VALUES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "45a2891a-5a23-44e6-ad14-e493b88296db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>Haltestellenkurzname_from</th>\n",
       "      <th>Haltestellenkurzname_to</th>\n",
       "      <th>VSYS</th>\n",
       "      <th>...</th>\n",
       "      <th>Line_743</th>\n",
       "      <th>Line_744</th>\n",
       "      <th>Line_745</th>\n",
       "      <th>Line_751</th>\n",
       "      <th>Line_753</th>\n",
       "      <th>Line_910</th>\n",
       "      <th>Line_912</th>\n",
       "      <th>Line_916</th>\n",
       "      <th>Line_918</th>\n",
       "      <th>Line_919</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.0</td>\n",
       "      <td>3.22000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21.5</td>\n",
       "      <td>2.83636</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.5</td>\n",
       "      <td>3.10417</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22.0</td>\n",
       "      <td>3.06897</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22.0</td>\n",
       "      <td>2.57143</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>GRUN</td>\n",
       "      <td>BALT</td>\n",
       "      <td>B</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</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>974678</th>\n",
       "      <td>22.0</td>\n",
       "      <td>4.86957</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974679</th>\n",
       "      <td>22.5</td>\n",
       "      <td>4.60000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974680</th>\n",
       "      <td>22.5</td>\n",
       "      <td>4.08696</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974681</th>\n",
       "      <td>23.0</td>\n",
       "      <td>1.81818</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974682</th>\n",
       "      <td>23.5</td>\n",
       "      <td>2.25000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>WDOL</td>\n",
       "      <td>BDOL</td>\n",
       "      <td>SB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>972924 rows × 117 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        FZ_AB  Besetzung  Tage_DWV  Tage_SA  Tage_SO  Tage_SA_N  Tage_SO_N  \\\n",
       "0        21.0    3.22000      True    False    False      False      False   \n",
       "1        21.5    2.83636      True    False    False      False      False   \n",
       "2        21.5    3.10417      True    False    False      False      False   \n",
       "3        22.0    3.06897      True    False    False      False      False   \n",
       "4        22.0    2.57143      True    False    False      False      False   \n",
       "...       ...        ...       ...      ...      ...        ...        ...   \n",
       "974678   22.0    4.86957     False     True    False      False      False   \n",
       "974679   22.5    4.60000     False     True    False      False      False   \n",
       "974680   22.5    4.08696     False     True    False      False      False   \n",
       "974681   23.0    1.81818     False     True    False      False      False   \n",
       "974682   23.5    2.25000     False     True    False      False      False   \n",
       "\n",
       "       Haltestellenkurzname_from Haltestellenkurzname_to VSYS  ...  Line_743  \\\n",
       "0                           GRUN                    BALT    B  ...     False   \n",
       "1                           GRUN                    BALT    B  ...     False   \n",
       "2                           GRUN                    BALT    B  ...     False   \n",
       "3                           GRUN                    BALT    B  ...     False   \n",
       "4                           GRUN                    BALT    B  ...     False   \n",
       "...                          ...                     ...  ...  ...       ...   \n",
       "974678                      WDOL                    BDOL   SB  ...     False   \n",
       "974679                      WDOL                    BDOL   SB  ...     False   \n",
       "974680                      WDOL                    BDOL   SB  ...     False   \n",
       "974681                      WDOL                    BDOL   SB  ...     False   \n",
       "974682                      WDOL                    BDOL   SB  ...     False   \n",
       "\n",
       "        Line_744  Line_745  Line_751  Line_753  Line_910  Line_912  Line_916  \\\n",
       "0          False     False     False     False     False     False     False   \n",
       "1          False     False     False     False     False     False     False   \n",
       "2          False     False     False     False     False     False     False   \n",
       "3          False     False     False     False     False     False     False   \n",
       "4          False     False     False     False     False     False     False   \n",
       "...          ...       ...       ...       ...       ...       ...       ...   \n",
       "974678     False     False     False     False     False     False     False   \n",
       "974679     False     False     False     False     False     False     False   \n",
       "974680     False     False     False     False     False     False     False   \n",
       "974681     False     False     False     False     False     False     False   \n",
       "974682     False     False     False     False     False     False     False   \n",
       "\n",
       "        Line_918  Line_919  \n",
       "0          False     False  \n",
       "1          False     False  \n",
       "2          False     False  \n",
       "3          False     False  \n",
       "4          False     False  \n",
       "...          ...       ...  \n",
       "974678     False     False  \n",
       "974679     False     False  \n",
       "974680     False     False  \n",
       "974681     False     False  \n",
       "974682     False     False  \n",
       "\n",
       "[972924 rows x 117 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert LInename to numerical values\n",
    "df_filtered_train = pd.get_dummies(df_filtered_train, columns=['Linienname'], prefix='Line')\n",
    "df_filtered_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "18e413b0-355b-4b86-817f-0991c135860c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>Haltestellenkurzname_from</th>\n",
       "      <th>Haltestellenkurzname_to</th>\n",
       "      <th>VSYS</th>\n",
       "      <th>...</th>\n",
       "      <th>Line_743</th>\n",
       "      <th>Line_744</th>\n",
       "      <th>Line_745</th>\n",
       "      <th>Line_751</th>\n",
       "      <th>Line_753</th>\n",
       "      <th>Line_910</th>\n",
       "      <th>Line_912</th>\n",
       "      <th>Line_916</th>\n",
       "      <th>Line_918</th>\n",
       "      <th>Line_919</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.5</td>\n",
       "      <td>0.44444</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.0</td>\n",
       "      <td>1.60000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>0.50000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.0</td>\n",
       "      <td>0.10000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.5</td>\n",
       "      <td>0.50000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>MIBU</td>\n",
       "      <td>HIRS</td>\n",
       "      <td>T</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</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>1149500</th>\n",
       "      <td>13.0</td>\n",
       "      <td>5.97826</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>KIND</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>BL</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149501</th>\n",
       "      <td>12.0</td>\n",
       "      <td>11.05556</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149502</th>\n",
       "      <td>15.5</td>\n",
       "      <td>6.93182</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149503</th>\n",
       "      <td>16.5</td>\n",
       "      <td>4.26667</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149504</th>\n",
       "      <td>17.0</td>\n",
       "      <td>3.80000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>EIHH</td>\n",
       "      <td>KIND</td>\n",
       "      <td>BL</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1140359 rows × 114 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         FZ_AB  Besetzung  Tage_DWV  Tage_SA  Tage_SO  Tage_SA_N  Tage_SO_N  \\\n",
       "0          9.5    0.44444     False    False     True      False      False   \n",
       "1         10.0    1.60000     False    False     True      False      False   \n",
       "2          5.0    0.50000      True    False    False      False      False   \n",
       "3          5.0    0.10000      True    False    False      False      False   \n",
       "4          5.5    0.50000      True    False    False      False      False   \n",
       "...        ...        ...       ...      ...      ...        ...        ...   \n",
       "1149500   13.0    5.97826      True    False    False      False      False   \n",
       "1149501   12.0   11.05556      True    False    False      False      False   \n",
       "1149502   15.5    6.93182      True    False    False      False      False   \n",
       "1149503   16.5    4.26667      True    False    False      False      False   \n",
       "1149504   17.0    3.80000      True    False    False      False      False   \n",
       "\n",
       "        Haltestellenkurzname_from Haltestellenkurzname_to VSYS  ...  Line_743  \\\n",
       "0                            MIBU                    HIRS    T  ...     False   \n",
       "1                            MIBU                    HIRS    T  ...     False   \n",
       "2                            MIBU                    HIRS    T  ...     False   \n",
       "3                            MIBU                    HIRS    T  ...     False   \n",
       "4                            MIBU                    HIRS    T  ...     False   \n",
       "...                           ...                     ...  ...  ...       ...   \n",
       "1149500                      KIND                    EIHH   BL  ...     False   \n",
       "1149501                      EIHH                    KIND   BL  ...     False   \n",
       "1149502                      EIHH                    KIND   BL  ...     False   \n",
       "1149503                      EIHH                    KIND   BL  ...     False   \n",
       "1149504                      EIHH                    KIND   BL  ...     False   \n",
       "\n",
       "         Line_744  Line_745  Line_751  Line_753  Line_910  Line_912  Line_916  \\\n",
       "0           False     False     False     False     False     False     False   \n",
       "1           False     False     False     False     False     False     False   \n",
       "2           False     False     False     False     False     False     False   \n",
       "3           False     False     False     False     False     False     False   \n",
       "4           False     False     False     False     False     False     False   \n",
       "...           ...       ...       ...       ...       ...       ...       ...   \n",
       "1149500     False     False     False     False     False     False     False   \n",
       "1149501     False     False     False     False     False     False     False   \n",
       "1149502     False     False     False     False     False     False     False   \n",
       "1149503     False     False     False     False     False     False     False   \n",
       "1149504     False     False     False     False     False     False     False   \n",
       "\n",
       "         Line_918  Line_919  \n",
       "0           False     False  \n",
       "1           False     False  \n",
       "2           False     False  \n",
       "3           False     False  \n",
       "4           False     False  \n",
       "...           ...       ...  \n",
       "1149500     False     False  \n",
       "1149501     False     False  \n",
       "1149502     False     False  \n",
       "1149503     False     False  \n",
       "1149504     False     False  \n",
       "\n",
       "[1140359 rows x 114 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#now the same for test set\n",
    "df_filtered_test = pd.get_dummies(df_filtered_test, columns=['Linienname'], prefix='Line')\n",
    "df_filtered_test"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e30cf6ab-2de4-4e5e-a7fc-a4817a25b8b2",
   "metadata": {},
   "source": [
    "## ADD GEOLOGICAL COORDINATES TO HALTESTELLEN FROM AND HALTESTELLEN TO"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f987bdc-267f-4a04-8a05-3ea82d64137a",
   "metadata": {},
   "source": [
    "#### RETRIEVING GPS COORDINATES FOR ALL STOPS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "312fc131-3854-4167-8019-bd850d8c1de4",
   "metadata": {},
   "source": [
    "First we find all possible stops that are in both train and test datasets.\n",
    "\n",
    "We use haltestellen-kurzname to reference to keep a better consistency across different years,\n",
    "and we use haltestellen-langname specifically to look for the GPS coordinates. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "a479281d-1acf-40e0-9854-b2bc7c25272e",
   "metadata": {},
   "outputs": [],
   "source": [
    "set_stops_train = {(a, b) for a, b in zip(stops_train['Haltestellenkurzname'], stops_train['Haltestellenlangname'])}\n",
    "set_stops_test = {(a, b) for a, b in zip(stops_test['Haltestellenkurzname'], stops_test['Haltestellenlangname'])}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "9cb63e3c-0c16-42b8-86b5-3a7b61992822",
   "metadata": {},
   "outputs": [],
   "source": [
    "stops_train_test=set_stops_train.union(set_stops_test)\n",
    "df_stops_train_test=pd.DataFrame(stops_train_test, columns=[\"Haltestellenkurzname\", \"Haltestellenlangname\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "05c79e95-616c-400e-88c6-ec28366e11ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "#import stops with coordinates\n",
    "df_stops_with_coordinates=pd.read_csv(\"data/stops.csv\")[[\"Haltestellenlangname\",\"GPS_Latitude\",\"GPS_Longitude\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c8b5c2d4-5e7d-4812-a457-a98cf572b085",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_stops=pd.merge(df_stops_train_test, df_stops_with_coordinates, on='Haltestellenlangname', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "e197d913-4ae1-4c14-99a9-4f71bd5eae2f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Haltestellenkurzname</th>\n",
       "      <th>Haltestellenlangname</th>\n",
       "      <th>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>WALBIR</td>\n",
       "      <td>Waldegg, Birmensdorferstrasse</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ZUMI99</td>\n",
       "      <td>Zumikon</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>KLAE07</td>\n",
       "      <td>Kloten, Lägernstrasse</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>VBIR</td>\n",
       "      <td>Villmergen, Birkenweg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>BIRWSL</td>\n",
       "      <td>Birmensdorf ZH, Sternen/WSL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>WIDDOR</td>\n",
       "      <td>Widen, Dorf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>BERMAT</td>\n",
       "      <td>Berikon, Mattenhof</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>800</th>\n",
       "      <td>BALD01</td>\n",
       "      <td>Adliswil, Baldernstrasse</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>802</th>\n",
       "      <td>SVOR</td>\n",
       "      <td>Sarmenstorf, Vorstadt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>810</th>\n",
       "      <td>TITL</td>\n",
       "      <td>Zürich, Titlisstrasse</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Haltestellenkurzname           Haltestellenlangname  GPS_Latitude  \\\n",
       "8                 WALBIR  Waldegg, Birmensdorferstrasse           NaN   \n",
       "11                ZUMI99                        Zumikon           NaN   \n",
       "12                KLAE07          Kloten, Lägernstrasse           NaN   \n",
       "20                  VBIR          Villmergen, Birkenweg           NaN   \n",
       "23                BIRWSL    Birmensdorf ZH, Sternen/WSL           NaN   \n",
       "..                   ...                            ...           ...   \n",
       "795               WIDDOR                    Widen, Dorf           NaN   \n",
       "799               BERMAT             Berikon, Mattenhof           NaN   \n",
       "800               BALD01       Adliswil, Baldernstrasse           NaN   \n",
       "802                 SVOR          Sarmenstorf, Vorstadt           NaN   \n",
       "810                 TITL          Zürich, Titlisstrasse           NaN   \n",
       "\n",
       "     GPS_Longitude  \n",
       "8              NaN  \n",
       "11             NaN  \n",
       "12             NaN  \n",
       "20             NaN  \n",
       "23             NaN  \n",
       "..             ...  \n",
       "795            NaN  \n",
       "799            NaN  \n",
       "800            NaN  \n",
       "802            NaN  \n",
       "810            NaN  \n",
       "\n",
       "[160 rows x 4 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all_stops[df_all_stops[\"GPS_Latitude\"].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "b32c91fc-0689-4def-b9c8-b26ad0b35afa",
   "metadata": {},
   "outputs": [],
   "source": [
    "#now find the latitude and longitude of those without, from stops.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d71c8a52-4f3c-4ed3-9d47-fc2d59077f94",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waldegg, Birmensdorferstrasse:  47.3688877 8.4644298\n",
      "Zumikon:  47.3324061 8.6238228\n",
      "Kloten, Lägernstrasse:  47.4468522 8.5768629\n",
      "Villmergen, Birkenweg:  47.3513653 8.2505702\n",
      "Birmensdorf ZH, Sternen/WSL:  47.360094 8.456101\n",
      "Oetwil a.d.L., Schweizäcker:  47.4245207 8.402553\n",
      "Dietikon, Wasserburg:  47.3988505 8.4298643\n",
      "Oberwil-Lieli, Dreispitz:  47.3372025 8.3866316\n",
      "Wohlen AG, Freiämterhof:  47.349128 8.2639433\n",
      "Villmergen, Feldblumenweg:  47.3496055 8.2470707\n",
      "Waltikon:  47.3354762 8.6188248\n",
      "Zürich, Zielweg:  47.3588812 8.4973394\n",
      "Bremgarten AG, Bahnhof:  47.351740649999996 8.346632609937817\n",
      "Zollikerberg, Sennhof:  47.3462027 8.6171916\n",
      "Zürich, Stodolastrasse:  47.3572507 8.590993\n",
      "Scheuren:  47.3225026 8.6606431\n",
      "Wohlen AG, Coop:  47.3501784 8.2709925\n",
      "Wohlen AG, Haldenschulen:  47.3506379 8.2801625\n",
      "Zürich Flughafen, Werft:  47.4453771 8.5703427\n",
      "Hilfikon, Sandbüel:  47.3318031 8.8643112\n",
      "Kloten, Neubrunnenstrasse:  47.4452507 8.5761344\n",
      "Zollikerberg, Station/Q treff:  47.3465737 8.6036099\n",
      "Zufikon, Hammergut:  47.3554135 8.3536735\n",
      "Zürich, Obere Hornhalde:  47.3315647 8.5381935\n",
      "Kloten, Bramen:  47.4434432 8.5787059\n",
      "Uitikon, Dorf:  47.3703972 8.4545428\n",
      "Zürich, Rigiblick:  47.3269708 8.8324028\n",
      "Zürich, Depot1 Zentralwerkst:  47.3929441 8.4960383\n",
      "Zürich, Waldhaus Dolder:  47.3732569 8.5686173\n",
      "Zürich, Löwenbräu:  47.388655 8.526282304743749\n",
      "Wohlen AG, Migros:  47.3460031 8.2906737\n",
      "Adliswil, Sunnau:  47.3266377 8.5296222\n",
      "Bremgarten AG, West:  47.3514029 8.3465191\n",
      "Zürich, Spyristeig:  47.3869698 8.5572896\n",
      "Zürich, Trichtenhausenfussweg:  47.3536759 8.5996724\n",
      "Kloten, Balsberg Hardacher:  47.4403914 8.5715817\n",
      "Kilchberg ZH, Alterszentrum:  47.3198942 8.5407831\n",
      "Bremgarten AG, Meyerhof:  47.3471049 8.3401913\n",
      "Zufikon, Station:  47.3324061 8.6238228\n",
      "Emmat:  47.2940807 8.7025197\n",
      "Hinteregg:  47.3074289 8.6834648\n",
      "Dietikon, Schöneggstrasse:  47.3973641 8.4212655\n",
      "Berikon, Kirche:  47.270475 8.936761\n",
      "Oetwil an der Limmat, Zentrum:  47.4277883 8.3941989\n",
      "Sarmenstorf, Altersheim:  47.2542972 8.7703432\n",
      "Zürich, Strickhof:  47.3953639 8.549735457540397\n",
      "Zollikerberg, Trichtenhauserm.:  47.3505965 8.601827\n",
      "Berikon, Kesslernmatt:  47.3648327 8.3653157\n",
      "Bremgarten AG, Bibenlos:  47.353963 8.3501613\n",
      "Zürich, Hertersteg:  47.3666319 8.5244536\n",
      "Oberwil-Lieli, Englisächer:  47.3430396 8.4039273\n",
      "Glattpark, Lindbergh-Allee:  47.4237407 8.5630298\n",
      "Zürich, Hardturm:  47.3904153 8.510923678130677\n",
      "Adliswil, Poststrasse:  47.3139206 8.5229783\n",
      "Zürich, Krönleinstrasse:  47.3826648 8.5602044\n",
      "Villmergen, Zentrum:  47.3739351 8.551061422763539\n",
      "Zürich, Segeten:  47.3553542 8.5929858\n",
      "Urdorf, Urdorf-Nord:  47.393475 8.4178342\n",
      "Sarmenstorf, Schulhaus:  47.3610472 8.5314\n",
      "Berikon, Kreisschule:  47.270475 8.936761\n",
      "Dietikon, Stoffelbach:  47.3948235 8.3976934\n",
      "Zürich, Letzibach:  47.39004344999999 8.493187903364442\n",
      "Esslingen:  47.28919 8.7096296\n",
      "Spreitenbach, IKEA:  47.4073309 8.3859492\n",
      "Binz bei Maur, Gassacher:  47.3572101 8.6279409\n",
      "Widen, Heinrüti:  47.361362 8.3538561\n",
      "Glattpark, Lindberghplatz:  47.4250631 8.5603476\n",
      "Wohlen AG, Bahnhof:  47.3477099 8.2711437\n",
      "Wohlen AG, Bünzstrasse:  47.3512057 8.2750148\n",
      "Zürich, Flobotstrasse:  47.3796814 8.569329\n",
      "Zürich, Riedbach:  47.414744 8.5594243\n",
      "Zürich, Friedhof Uetliberg:  47.3587639 8.5006138\n",
      "Reppischhof, Station:  47.3823005 8.3952719\n",
      "Dietikon, Bergfrieden:  47.3969554 8.3984875\n",
      "Langwies ZH:  47.6558745 8.6537748\n",
      "Adliswil, Schulhaus Kopfholz:  47.3086685 8.5351369\n",
      "Oetwil an der Limmat, Gässliacker:  47.4279122 8.394719\n",
      "Spreitenbach, Raiacker:  47.4140179 8.3751535\n",
      "Maiacher:  47.3296147 8.6304618\n",
      "Glattpark, Chavez-Allee:  47.4222639 8.5616101\n",
      "Zürich, Sädlenweg:  47.3670801 8.4890055\n",
      "Rudolfstetten, Station:  47.38282055 8.50373866657526\n",
      "Kilchberg ZH, Neuweid:  47.3156002 8.5398319\n",
      "Dübendorf, Alterszentrum:  47.387957 8.6201571\n",
      "Zürich, Kindersp. (ab 10/2024):  47.3940517 8.534611951333504\n",
      "Waldegg, Post:  47.3683361 8.4622544\n",
      "Zürich, Germaniastrasse:  47.3875473 8.5537701\n",
      "Kilchberg ZH, Lätten:  47.3266724 8.5347483\n",
      "Wädenswil, Garage Rütihof:  47.2381582 8.6351814\n",
      "Zürich, Heubeeriweg:  47.3826221 8.5655871\n",
      "Spreitenbach, Glattler:  47.4221728 8.3622187\n",
      "Zürich, Friesenberghalde:  47.3615598 8.5015099\n",
      "Neuhaus bei Hinteregg:  47.3127233 8.6741608\n",
      "Birmensdorf ZH, Bahnhof Süd:  47.35776 8.43649\n",
      "Sarmenstorf, Kirche:  47.3138241 8.2522311\n",
      "Zürich, Hermetschloo:  47.39467 8.4734385\n",
      "Adliswil, Rifertstrasse:  47.3097946 8.5343439\n",
      "Zollikon, Rotfluhstrasse:  47.3376964 8.5768857\n",
      "Zürich, Kantonalbank:  47.3679686 8.5391319\n",
      "Kloten, Waldeggweg:  47.4419715 8.577384\n",
      "Adliswil, Wanneten-/Haldenstr.:  47.3057179 8.5222862\n",
      "Birmensdorf ZH, Obere Risi:  47.3594478 8.4507624\n",
      "Zollikon, Niederhofenrain:  47.3480586 8.576776\n",
      "Zürich, Schäppiweg:  47.3898532 8.5495662\n",
      "Oberwil-Lieli, Im Moos:  47.3376509 8.3985357\n",
      "Rudolfstetten, Hofacker:  47.3661732 8.3779711\n",
      "Birmensdorf ZH, Bahnhof:  47.35776 8.43649\n",
      "Egg:  47.2990667 8.6884584\n",
      "Forch, Depot Forchbahn:  47.3256184 8.6486223\n",
      "Zollikerberg:  47.3456637 8.5993998\n",
      "Zürich, Zentrum Witikon:  47.3584165 8.5865222\n",
      "Zürich, Bahnhof Wollishofen:  47.3468565 8.5329137\n",
      "Adliswil, Zopf:  47.3175281 8.5161903\n",
      "Waldegg, Leuen:  47.3630711 8.4590962\n",
      "Adliswil, Feldblumenstrasse:  47.3207245 8.512521\n",
      "Villmergen, Oberdorf:  47.3451978 8.2444282\n",
      "Zürich, Wiesliacher:  47.3541369 8.5988645\n",
      "Spital Zollikerberg:  47.3482758 8.5959678\n",
      "Zürich, Buchholz:  47.3590809 8.7265975\n",
      "Bremgarten AG, Obertorplatz:  47.3509039 8.3436426\n",
      "Dietikon, Zentrum:  47.40864445 8.462370378565646\n",
      "Hilfikon, Dorf:  47.3335281 8.2465462\n",
      "Zürich, Buchzelgstrasse:  47.3600811 8.5903307\n",
      "Zollikerberg, Hasenbart:  47.349264 8.5951434\n",
      "Zollikerberg, Spital:  47.3482758 8.5959678\n",
      "Bremgarten AG, Kaserne:  47.3514029 8.3465191\n",
      "Forch:  47.3255509 8.6468061\n",
      "Zürich, Balgrist Ost:  47.3547172 8.5750731\n",
      "Oberwil-Lieli, Bündtenmättli:  47.3384961 8.3825922\n",
      "Bremgarten AG, Waage:  47.3514029 8.3465191\n",
      "Waldburg:  47.3471946 8.5943953\n",
      "Zürich, Leutschenpark:  47.418086450000004 8.556198095537749\n",
      "Schlieren, Geissweid:  47.3977136 8.4443897\n",
      "Berikon-Widen, Bahnhof:  47.3620655 8.3672168\n",
      "Glattbrugg, Riethofstrasse:  47.4373634 8.5661666\n",
      "Zürich, Wollishoferplatz:  47.338561350000006 8.530466672410563\n",
      "Zürich, Depot Dolderbahn:  19.4493084 -99.201634\n",
      "Zürich, Klinik Hirslanden:  47.3510846 8.5768502\n",
      "Adliswil, Bodenacker:  47.316227 8.5168594\n",
      "Birmensdorf ZH, Untere Risi:  47.3598193 8.4469522\n",
      "Zürich, Schanzackerstrasse:  47.3901534 8.5472627\n",
      "Glattpark, Wright-Strasse:  47.4203399 8.5589456\n",
      "Zürich, Langensteinenstrasse:  47.3935409 8.5475042\n",
      "Dübendorf, Am Ring:  47.4020328 8.6074914\n",
      "Zürich, Bhf. Wollishofen/Werft:  47.348167 8.5343455\n",
      "Berikon, Stalden:  47.3502793 8.3741621\n",
      "Bremgarten AG, Stockweier:  47.3514029 8.3465191\n",
      "Adliswil, Landolt-Junker-Str.:  47.3035354 8.5186553\n",
      "Adliswil, Büchel:  47.3001035 8.5205602\n",
      "Adliswil, Dietlimoos:  47.3194409 8.5328455\n",
      "Neue Forch:  47.3255601 8.6402029\n",
      "Zürich, Trichtisal:  47.3546959 8.6029297\n",
      "Oberwil-Lieli, Lieli Dorf:  47.3357884 8.3866115\n",
      "Widen, Dorf:  47.2316942 8.5974962\n",
      "Berikon, Mattenhof:  47.3433143 8.3819697\n",
      "Adliswil, Baldernstrasse:  47.3211606 8.5154114\n",
      "Sarmenstorf, Vorstadt:  47.3157902 8.2451872\n",
      "Zürich, Titlisstrasse:  47.3693685 8.5646167\n"
     ]
    }
   ],
   "source": [
    "# initialize Nominatim geocoder\n",
    "geolocator = Photon(user_agent=\"geopy_project\")\n",
    "\n",
    "# function for rows that have null GPS coordinates\n",
    "def geocoord_of(row):\n",
    "    coordinates = geolocator.geocode(row[\"Haltestellenlangname\"]+\", Zurich\")\n",
    "    if coordinates is not None:\n",
    "        print(row[\"Haltestellenlangname\"]+\": \",coordinates.latitude, coordinates.longitude)\n",
    "        return coordinates.latitude, coordinates.longitude\n",
    "    else:\n",
    "        return None, None  # Return None values if geocoding fails\n",
    "\n",
    "# apply the function only to rows where column GPS latitude (or longitude, whicever of the two) is NaN\n",
    "df_all_stops[['GPS_Latitude', 'GPS_Longitude']] = df_all_stops.apply(lambda row: geocoord_of(row) if pd.isnull(row['GPS_Latitude']) else (row['GPS_Latitude'], row['GPS_Longitude']), axis=1, result_type='expand')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "9f77eb0e-16e8-4716-ab2e-0e0e10065000",
   "metadata": {},
   "outputs": [],
   "source": [
    "#finding stops which the process of fetching geological coordinates failed "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "d8715404-60f5-4201-8556-48c7ecd9d886",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Haltestellenkurzname</th>\n",
       "      <th>Haltestellenlangname</th>\n",
       "      <th>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>ENGEPAU</td>\n",
       "      <td>Zürich, Bhf Enge Pausenplatz</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>BWST</td>\n",
       "      <td>Zch, Bhf.Wollishofen/Staubstr.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Haltestellenkurzname            Haltestellenlangname  GPS_Latitude  \\\n",
       "52               ENGEPAU    Zürich, Bhf Enge Pausenplatz           NaN   \n",
       "169                 BWST  Zch, Bhf.Wollishofen/Staubstr.           NaN   \n",
       "\n",
       "     GPS_Longitude  \n",
       "52             NaN  \n",
       "169            NaN  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all_stops[df_all_stops[\"GPS_Latitude\"].isna() ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "021725bf-a439-4837-959d-0d72836248d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#we discovered that the stop \"Zürich, Bhf Enge Pausenplatz\" was never referenced in the Reisende.csv table in both 2022 and 2023\n",
    "#so we remove it\n",
    "df_all_stops.drop(52, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "f993c7d5-2421-48b1-98af-3e6f1fb5a9bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Haltestellenkurzname</th>\n",
       "      <th>Haltestellenlangname</th>\n",
       "      <th>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>BWST</td>\n",
       "      <td>Zch, Bhf.Wollishofen/Staubstr.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Haltestellenkurzname            Haltestellenlangname  GPS_Latitude  \\\n",
       "169                 BWST  Zch, Bhf.Wollishofen/Staubstr.           NaN   \n",
       "\n",
       "     GPS_Longitude  \n",
       "169            NaN  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df_all_stops[df_all_stops[\"GPS_Latitude\"].isna() ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "d862ddd6-22eb-4ea0-8c6d-b0295654aea8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#now manually insert the coordinates of \"Zch, Bhf.Wollishofen/Staubstr\", with coordinates 47.34703438638321, 8.532916780705717\n",
    "df_all_stops.at[169, 'GPS_Latitude'] = 47.34703438638321\n",
    "df_all_stops.at[169, 'GPS_Longitude'] = 8.532916780705717"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "1c5115b7-fa74-4d87-8fe8-0e285fba2e73",
   "metadata": {
    "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>Haltestellenkurzname</th>\n",
       "      <th>Haltestellenlangname</th>\n",
       "      <th>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Haltestellenkurzname, Haltestellenlangname, GPS_Latitude, GPS_Longitude]\n",
       "Index: []"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#verify that there is no non values left in geological coordinates\n",
    "df_all_stops[df_all_stops[\"GPS_Latitude\"].isna() ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "95820388-4cf5-44b2-b71a-fb4d7d9bd672",
   "metadata": {},
   "outputs": [],
   "source": [
    "#now we can drop the columns Haltestellenlangname from the df_all_stops table\n",
    "df_all_stops.drop(columns=[\"Haltestellenlangname\"],inplace=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "009b5ccf-9154-40cf-b028-230e4bbc4643",
   "metadata": {},
   "source": [
    "#### ADDING GPS COORDINATES TO THE DATASETS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "85420366-84f8-4278-ad03-9b56eba65c4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#now we can merge the reisende table (df_filtered_train and df_filtered_test) with df_all_Stops, to get all the coordinates.\n",
    "df_filtered_train_ready = pd.merge(df_filtered_train, df_all_stops, left_on='Haltestellenkurzname_from', right_on='Haltestellenkurzname')\n",
    "df_filtered_train_ready.rename(columns={'GPS_Latitude': 'GPS_Latitude_From', 'GPS_Longitude': 'GPS_Longitude_From'}, inplace=True)\n",
    "df_filtered_train_ready = pd.merge(df_filtered_train_ready, df_all_stops, left_on='Haltestellenkurzname_to', right_on='Haltestellenkurzname')\n",
    "df_filtered_train_ready.rename(columns={'GPS_Latitude': 'GPS_Latitude_To', 'GPS_Longitude': 'GPS_Longitude_To'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "c3f8e20a-a09f-4a3e-ba7b-f6ce7605907e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filtered_train_ready.drop(columns=['Haltestellenkurzname_from'], inplace=True)\n",
    "df_filtered_train_ready.drop(columns=['Haltestellenkurzname_to'], inplace=True)\n",
    "df_filtered_train_ready.drop(columns=['Haltestellenkurzname_x'], inplace=True)\n",
    "df_filtered_train_ready.drop(columns=['Haltestellenkurzname_y'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "648b13b4-77e5-4cdb-9e6e-b78452c2097f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>VSYS</th>\n",
       "      <th>Line_2</th>\n",
       "      <th>Line_3</th>\n",
       "      <th>...</th>\n",
       "      <th>Line_753</th>\n",
       "      <th>Line_910</th>\n",
       "      <th>Line_912</th>\n",
       "      <th>Line_916</th>\n",
       "      <th>Line_918</th>\n",
       "      <th>Line_919</th>\n",
       "      <th>GPS_Latitude_From</th>\n",
       "      <th>GPS_Longitude_From</th>\n",
       "      <th>GPS_Latitude_To</th>\n",
       "      <th>GPS_Longitude_To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.0</td>\n",
       "      <td>3.22000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>47.390838</td>\n",
       "      <td>8.488814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21.5</td>\n",
       "      <td>2.83636</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>47.390838</td>\n",
       "      <td>8.488814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.5</td>\n",
       "      <td>3.10417</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>47.390838</td>\n",
       "      <td>8.488814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22.0</td>\n",
       "      <td>3.06897</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>47.390838</td>\n",
       "      <td>8.488814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22.0</td>\n",
       "      <td>2.57143</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>47.390838</td>\n",
       "      <td>8.488814</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>972919</th>\n",
       "      <td>22.0</td>\n",
       "      <td>4.86957</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>47.373789</td>\n",
       "      <td>8.576132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972920</th>\n",
       "      <td>22.5</td>\n",
       "      <td>4.60000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>47.373789</td>\n",
       "      <td>8.576132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972921</th>\n",
       "      <td>22.5</td>\n",
       "      <td>4.08696</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>47.373789</td>\n",
       "      <td>8.576132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972922</th>\n",
       "      <td>23.0</td>\n",
       "      <td>1.81818</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>47.373789</td>\n",
       "      <td>8.576132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972923</th>\n",
       "      <td>23.5</td>\n",
       "      <td>2.25000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>47.373789</td>\n",
       "      <td>8.576132</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>972924 rows × 119 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        FZ_AB  Besetzung  Tage_DWV  Tage_SA  Tage_SO  Tage_SA_N  Tage_SO_N  \\\n",
       "0        21.0    3.22000      True    False    False      False      False   \n",
       "1        21.5    2.83636      True    False    False      False      False   \n",
       "2        21.5    3.10417      True    False    False      False      False   \n",
       "3        22.0    3.06897      True    False    False      False      False   \n",
       "4        22.0    2.57143      True    False    False      False      False   \n",
       "...       ...        ...       ...      ...      ...        ...        ...   \n",
       "972919   22.0    4.86957     False     True    False      False      False   \n",
       "972920   22.5    4.60000     False     True    False      False      False   \n",
       "972921   22.5    4.08696     False     True    False      False      False   \n",
       "972922   23.0    1.81818     False     True    False      False      False   \n",
       "972923   23.5    2.25000     False     True    False      False      False   \n",
       "\n",
       "       VSYS  Line_2  Line_3  ...  Line_753  Line_910  Line_912  Line_916  \\\n",
       "0         B   False   False  ...     False     False     False     False   \n",
       "1         B   False   False  ...     False     False     False     False   \n",
       "2         B   False   False  ...     False     False     False     False   \n",
       "3         B   False   False  ...     False     False     False     False   \n",
       "4         B   False   False  ...     False     False     False     False   \n",
       "...     ...     ...     ...  ...       ...       ...       ...       ...   \n",
       "972919   SB   False   False  ...     False     False     False     False   \n",
       "972920   SB   False   False  ...     False     False     False     False   \n",
       "972921   SB   False   False  ...     False     False     False     False   \n",
       "972922   SB   False   False  ...     False     False     False     False   \n",
       "972923   SB   False   False  ...     False     False     False     False   \n",
       "\n",
       "        Line_918  Line_919  GPS_Latitude_From  GPS_Longitude_From  \\\n",
       "0          False     False          47.394903            8.489476   \n",
       "1          False     False          47.394903            8.489476   \n",
       "2          False     False          47.394903            8.489476   \n",
       "3          False     False          47.394903            8.489476   \n",
       "4          False     False          47.394903            8.489476   \n",
       "...          ...       ...                ...                 ...   \n",
       "972919     False     False          47.373257            8.568617   \n",
       "972920     False     False          47.373257            8.568617   \n",
       "972921     False     False          47.373257            8.568617   \n",
       "972922     False     False          47.373257            8.568617   \n",
       "972923     False     False          47.373257            8.568617   \n",
       "\n",
       "        GPS_Latitude_To  GPS_Longitude_To  \n",
       "0             47.390838          8.488814  \n",
       "1             47.390838          8.488814  \n",
       "2             47.390838          8.488814  \n",
       "3             47.390838          8.488814  \n",
       "4             47.390838          8.488814  \n",
       "...                 ...               ...  \n",
       "972919        47.373789          8.576132  \n",
       "972920        47.373789          8.576132  \n",
       "972921        47.373789          8.576132  \n",
       "972922        47.373789          8.576132  \n",
       "972923        47.373789          8.576132  \n",
       "\n",
       "[972924 rows x 119 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered_train_ready"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "1f330910-c1a2-48e3-8f76-32964145f278",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>VSYS</th>\n",
       "      <th>Line_2</th>\n",
       "      <th>Line_3</th>\n",
       "      <th>...</th>\n",
       "      <th>Line_753</th>\n",
       "      <th>Line_910</th>\n",
       "      <th>Line_912</th>\n",
       "      <th>Line_916</th>\n",
       "      <th>Line_918</th>\n",
       "      <th>Line_919</th>\n",
       "      <th>GPS_Latitude_From</th>\n",
       "      <th>GPS_Longitude_From</th>\n",
       "      <th>GPS_Latitude_To</th>\n",
       "      <th>GPS_Longitude_To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.5</td>\n",
       "      <td>0.44444</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>T</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.397778</td>\n",
       "      <td>8.541750</td>\n",
       "      <td>47.400252</td>\n",
       "      <td>8.543470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.0</td>\n",
       "      <td>1.60000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>T</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.397778</td>\n",
       "      <td>8.541750</td>\n",
       "      <td>47.400252</td>\n",
       "      <td>8.543470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>0.50000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>T</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.397778</td>\n",
       "      <td>8.541750</td>\n",
       "      <td>47.400252</td>\n",
       "      <td>8.543470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.0</td>\n",
       "      <td>0.10000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>T</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.397778</td>\n",
       "      <td>8.541750</td>\n",
       "      <td>47.400252</td>\n",
       "      <td>8.543470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.5</td>\n",
       "      <td>0.50000</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>T</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>47.397778</td>\n",
       "      <td>8.541750</td>\n",
       "      <td>47.400252</td>\n",
       "      <td>8.543470</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>1140354</th>\n",
       "      <td>15.5</td>\n",
       "      <td>4.06250</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>BP</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>47.311703</td>\n",
       "      <td>8.588525</td>\n",
       "      <td>47.313352</td>\n",
       "      <td>8.588639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1140355</th>\n",
       "      <td>16.0</td>\n",
       "      <td>4.50000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>BP</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>47.311703</td>\n",
       "      <td>8.588525</td>\n",
       "      <td>47.313352</td>\n",
       "      <td>8.588639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1140356</th>\n",
       "      <td>16.5</td>\n",
       "      <td>3.41176</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>BP</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>47.311703</td>\n",
       "      <td>8.588525</td>\n",
       "      <td>47.313352</td>\n",
       "      <td>8.588639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1140357</th>\n",
       "      <td>17.0</td>\n",
       "      <td>2.88235</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>BP</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>47.311703</td>\n",
       "      <td>8.588525</td>\n",
       "      <td>47.313352</td>\n",
       "      <td>8.588639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1140358</th>\n",
       "      <td>17.5</td>\n",
       "      <td>3.88889</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>BP</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>47.311703</td>\n",
       "      <td>8.588525</td>\n",
       "      <td>47.313352</td>\n",
       "      <td>8.588639</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1140359 rows × 116 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         FZ_AB  Besetzung  Tage_DWV  Tage_SA  Tage_SO  Tage_SA_N  Tage_SO_N  \\\n",
       "0          9.5    0.44444     False    False     True      False      False   \n",
       "1         10.0    1.60000     False    False     True      False      False   \n",
       "2          5.0    0.50000      True    False    False      False      False   \n",
       "3          5.0    0.10000      True    False    False      False      False   \n",
       "4          5.5    0.50000      True    False    False      False      False   \n",
       "...        ...        ...       ...      ...      ...        ...        ...   \n",
       "1140354   15.5    4.06250     False     True    False      False      False   \n",
       "1140355   16.0    4.50000     False     True    False      False      False   \n",
       "1140356   16.5    3.41176     False     True    False      False      False   \n",
       "1140357   17.0    2.88235     False     True    False      False      False   \n",
       "1140358   17.5    3.88889     False     True    False      False      False   \n",
       "\n",
       "        VSYS  Line_2  Line_3  ...  Line_753  Line_910  Line_912  Line_916  \\\n",
       "0          T   False   False  ...     False     False     False     False   \n",
       "1          T   False   False  ...     False     False     False     False   \n",
       "2          T   False   False  ...     False     False     False     False   \n",
       "3          T   False   False  ...     False     False     False     False   \n",
       "4          T   False   False  ...     False     False     False     False   \n",
       "...      ...     ...     ...  ...       ...       ...       ...       ...   \n",
       "1140354   BP   False   False  ...     False     False     False     False   \n",
       "1140355   BP   False   False  ...     False     False     False     False   \n",
       "1140356   BP   False   False  ...     False     False     False     False   \n",
       "1140357   BP   False   False  ...     False     False     False     False   \n",
       "1140358   BP   False   False  ...     False     False     False     False   \n",
       "\n",
       "         Line_918  Line_919  GPS_Latitude_From  GPS_Longitude_From  \\\n",
       "0           False     False          47.397778            8.541750   \n",
       "1           False     False          47.397778            8.541750   \n",
       "2           False     False          47.397778            8.541750   \n",
       "3           False     False          47.397778            8.541750   \n",
       "4           False     False          47.397778            8.541750   \n",
       "...           ...       ...                ...                 ...   \n",
       "1140354      True     False          47.311703            8.588525   \n",
       "1140355      True     False          47.311703            8.588525   \n",
       "1140356      True     False          47.311703            8.588525   \n",
       "1140357      True     False          47.311703            8.588525   \n",
       "1140358      True     False          47.311703            8.588525   \n",
       "\n",
       "         GPS_Latitude_To  GPS_Longitude_To  \n",
       "0              47.400252          8.543470  \n",
       "1              47.400252          8.543470  \n",
       "2              47.400252          8.543470  \n",
       "3              47.400252          8.543470  \n",
       "4              47.400252          8.543470  \n",
       "...                  ...               ...  \n",
       "1140354        47.313352          8.588639  \n",
       "1140355        47.313352          8.588639  \n",
       "1140356        47.313352          8.588639  \n",
       "1140357        47.313352          8.588639  \n",
       "1140358        47.313352          8.588639  \n",
       "\n",
       "[1140359 rows x 116 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# #now do the same with test data\n",
    "\n",
    "# #now we can merge the reisende table (df_filtered_train and df_filtered_test) with df_all_Stops, to get all the coordinates.\n",
    "df_filtered_test_ready = pd.merge(df_filtered_test, df_all_stops, left_on='Haltestellenkurzname_from', right_on='Haltestellenkurzname')\n",
    "df_filtered_test_ready.rename(columns={'GPS_Latitude': 'GPS_Latitude_From', 'GPS_Longitude': 'GPS_Longitude_From'}, inplace=True)\n",
    "df_filtered_test_ready = pd.merge(df_filtered_test_ready, df_all_stops, left_on='Haltestellenkurzname_to', right_on='Haltestellenkurzname')\n",
    "df_filtered_test_ready.rename(columns={'GPS_Latitude': 'GPS_Latitude_To', 'GPS_Longitude': 'GPS_Longitude_To'}, inplace=True)\n",
    "\n",
    "df_filtered_test_ready.drop(columns=['Haltestellenkurzname_from'], inplace=True)\n",
    "df_filtered_test_ready.drop(columns=['Haltestellenkurzname_to'], inplace=True)\n",
    "df_filtered_test_ready.drop(columns=['Haltestellenkurzname_x'], inplace=True)\n",
    "df_filtered_test_ready.drop(columns=['Haltestellenkurzname_y'], inplace=True)\n",
    "\n",
    "df_filtered_test_ready"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "bb9be3e2-f5be-4085-9718-cc705707a0d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filtered_train_ready.to_csv(\"2022_training.csv\", sep=';', encoding='utf-8')\n",
    "df_filtered_test_ready.to_csv(\"2023_testing.csv\", sep=';', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "9ee736e9-7b26-4c38-8b2b-be9ac23f0ebb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>VSYS</th>\n",
       "      <th>GPS_Latitude_From</th>\n",
       "      <th>GPS_Longitude_From</th>\n",
       "      <th>Line_2</th>\n",
       "      <th>Line_3</th>\n",
       "      <th>Line_753</th>\n",
       "      <th>Line_910</th>\n",
       "      <th>Line_912</th>\n",
       "      <th>Line_916</th>\n",
       "      <th>Line_918</th>\n",
       "      <th>Line_919</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.0</td>\n",
       "      <td>3.22000</td>\n",
       "      <td>True</td>\n",
       "      <td>B</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21.5</td>\n",
       "      <td>2.83636</td>\n",
       "      <td>True</td>\n",
       "      <td>B</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.5</td>\n",
       "      <td>3.10417</td>\n",
       "      <td>True</td>\n",
       "      <td>B</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22.0</td>\n",
       "      <td>3.06897</td>\n",
       "      <td>True</td>\n",
       "      <td>B</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22.0</td>\n",
       "      <td>2.57143</td>\n",
       "      <td>True</td>\n",
       "      <td>B</td>\n",
       "      <td>47.394903</td>\n",
       "      <td>8.489476</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972919</th>\n",
       "      <td>22.0</td>\n",
       "      <td>4.86957</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972920</th>\n",
       "      <td>22.5</td>\n",
       "      <td>4.60000</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972921</th>\n",
       "      <td>22.5</td>\n",
       "      <td>4.08696</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972922</th>\n",
       "      <td>23.0</td>\n",
       "      <td>1.81818</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972923</th>\n",
       "      <td>23.5</td>\n",
       "      <td>2.25000</td>\n",
       "      <td>False</td>\n",
       "      <td>SB</td>\n",
       "      <td>47.373257</td>\n",
       "      <td>8.568617</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>972924 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        FZ_AB  Besetzung  Tage_DWV VSYS  GPS_Latitude_From  \\\n",
       "0        21.0    3.22000      True    B          47.394903   \n",
       "1        21.5    2.83636      True    B          47.394903   \n",
       "2        21.5    3.10417      True    B          47.394903   \n",
       "3        22.0    3.06897      True    B          47.394903   \n",
       "4        22.0    2.57143      True    B          47.394903   \n",
       "...       ...        ...       ...  ...                ...   \n",
       "972919   22.0    4.86957     False   SB          47.373257   \n",
       "972920   22.5    4.60000     False   SB          47.373257   \n",
       "972921   22.5    4.08696     False   SB          47.373257   \n",
       "972922   23.0    1.81818     False   SB          47.373257   \n",
       "972923   23.5    2.25000     False   SB          47.373257   \n",
       "\n",
       "        GPS_Longitude_From  Line_2  Line_3  Line_753  Line_910  Line_912  \\\n",
       "0                 8.489476   False   False     False     False     False   \n",
       "1                 8.489476   False   False     False     False     False   \n",
       "2                 8.489476   False   False     False     False     False   \n",
       "3                 8.489476   False   False     False     False     False   \n",
       "4                 8.489476   False   False     False     False     False   \n",
       "...                    ...     ...     ...       ...       ...       ...   \n",
       "972919            8.568617   False   False     False     False     False   \n",
       "972920            8.568617   False   False     False     False     False   \n",
       "972921            8.568617   False   False     False     False     False   \n",
       "972922            8.568617   False   False     False     False     False   \n",
       "972923            8.568617   False   False     False     False     False   \n",
       "\n",
       "        Line_916  Line_918  Line_919  \n",
       "0          False     False     False  \n",
       "1          False     False     False  \n",
       "2          False     False     False  \n",
       "3          False     False     False  \n",
       "4          False     False     False  \n",
       "...          ...       ...       ...  \n",
       "972919     False     False     False  \n",
       "972920     False     False     False  \n",
       "972921     False     False     False  \n",
       "972922     False     False     False  \n",
       "972923     False     False     False  \n",
       "\n",
       "[972924 rows x 14 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered_train_ready[['FZ_AB','Besetzung','Tage_DWV','VSYS','GPS_Latitude_From','GPS_Longitude_From','Line_2','Line_3','Line_753','Line_910','Line_912','Line_916','Line_918','Line_919']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "3d6897b8-3190-4179-b39f-c62da795cb6d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['FZ_AB', 'Besetzung', 'Tage_DWV', 'Tage_SA', 'Tage_SO', 'Tage_SA_N',\n",
      "       'Tage_SO_N', 'VSYS', 'Line_2', 'Line_3',\n",
      "       ...\n",
      "       'Line_753', 'Line_910', 'Line_912', 'Line_916', 'Line_918', 'Line_919',\n",
      "       'GPS_Latitude_From', 'GPS_Longitude_From', 'GPS_Latitude_To',\n",
      "       'GPS_Longitude_To'],\n",
      "      dtype='object', length=119)\n"
     ]
    }
   ],
   "source": [
    "print(df_filtered_train_ready.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d03cd04-97ea-43f1-8b2c-983fb9d3f040",
   "metadata": {},
   "source": [
    "## NORMALIZING AND INITIAL TESTING WITH LINEAR REGRESSION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "e6845ec9-68dc-4f9b-baa7-92960fd45b9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "trainset=df_filtered_train_ready\n",
    "testset=df_filtered_test_ready\n",
    "\n",
    "# Too large (particularly for svr), keep only tram\n",
    "trainset = trainset[trainset['VSYS'] == 'T']\n",
    "testset = testset[testset['VSYS'] ==  'T']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "330c033a-2d08-46ce-aaa6-761921b8cf59",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\2697619568.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  trainset.dropna(subset=trainset.columns.values, inplace=True)\n",
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\2697619568.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  testset.dropna(subset=testset.columns.values, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "trainset.dropna(subset=trainset.columns.values, inplace=True)\n",
    "testset.dropna(subset=testset.columns.values, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "59beee0d-82ce-46bd-893d-3f7424535371",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\625823002.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  trainset.drop(columns=['Line_303','Line_308','Line_311','Line_331'], axis=1, inplace=True)\n",
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\625823002.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  testset.drop(columns=['Line_332'], axis=1, inplace=True)\n"
     ]
    }
   ],
   "source": [
    "trainset.drop(columns=['Line_303','Line_308','Line_311','Line_331'], axis=1, inplace=True)\n",
    "testset.drop(columns=['Line_332'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "5783299a-88c3-43e6-a5ff-9d702f14b7b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\3741256183.py:6: 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",
      "  trainset[columns_to_normalize] = scaler.transform(trainset[columns_to_normalize])\n",
      "C:\\Users\\TUF\\AppData\\Local\\Temp\\ipykernel_20184\\3741256183.py:7: 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",
      "  testset[columns_to_normalize] = scaler.transform(testset[columns_to_normalize])\n"
     ]
    }
   ],
   "source": [
    "scaler = StandardScaler()\n",
    "columns_to_normalize = ['FZ_AB', 'GPS_Latitude_From', 'GPS_Latitude_To', 'GPS_Longitude_From', 'GPS_Longitude_To', 'Besetzung' ]\n",
    "scaler.fit(trainset[columns_to_normalize])\n",
    "\n",
    "# Transform both training and test sets\n",
    "trainset[columns_to_normalize] = scaler.transform(trainset[columns_to_normalize])\n",
    "testset[columns_to_normalize] = scaler.transform(testset[columns_to_normalize])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "9c832d94-ba56-4abd-9c6d-f43038b4dcb7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>Line_2</th>\n",
       "      <th>Line_3</th>\n",
       "      <th>Line_4</th>\n",
       "      <th>Line_5</th>\n",
       "      <th>...</th>\n",
       "      <th>Line_753</th>\n",
       "      <th>Line_910</th>\n",
       "      <th>Line_912</th>\n",
       "      <th>Line_916</th>\n",
       "      <th>Line_918</th>\n",
       "      <th>Line_919</th>\n",
       "      <th>GPS_Latitude_From</th>\n",
       "      <th>GPS_Longitude_From</th>\n",
       "      <th>GPS_Latitude_To</th>\n",
       "      <th>GPS_Longitude_To</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7288</th>\n",
       "      <td>0.825894</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.720755</td>\n",
       "      <td>-1.979474</td>\n",
       "      <td>0.790881</td>\n",
       "      <td>-2.115646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7289</th>\n",
       "      <td>0.825894</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.720755</td>\n",
       "      <td>-1.979474</td>\n",
       "      <td>0.790881</td>\n",
       "      <td>-2.115646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7290</th>\n",
       "      <td>0.915639</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.720755</td>\n",
       "      <td>-1.979474</td>\n",
       "      <td>0.790881</td>\n",
       "      <td>-2.115646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7291</th>\n",
       "      <td>0.915639</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.720755</td>\n",
       "      <td>-1.979474</td>\n",
       "      <td>0.790881</td>\n",
       "      <td>-2.115646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7292</th>\n",
       "      <td>0.646403</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0.720755</td>\n",
       "      <td>-1.979474</td>\n",
       "      <td>0.790881</td>\n",
       "      <td>-2.115646</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 113 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         FZ_AB  Tage_DWV  Tage_SA  Tage_SO  Tage_SA_N  Tage_SO_N  Line_2  \\\n",
       "7288  0.825894     False     True    False      False      False   False   \n",
       "7289  0.825894     False     True    False      False      False   False   \n",
       "7290  0.915639     False     True    False      False      False   False   \n",
       "7291  0.915639     False     True    False      False      False   False   \n",
       "7292  0.646403      True    False    False      False      False   False   \n",
       "\n",
       "      Line_3  Line_4  Line_5  ...  Line_753  Line_910  Line_912  Line_916  \\\n",
       "7288   False   False   False  ...     False     False     False     False   \n",
       "7289   False   False   False  ...     False     False     False     False   \n",
       "7290   False   False   False  ...     False     False     False     False   \n",
       "7291   False   False   False  ...     False     False     False     False   \n",
       "7292   False   False   False  ...     False     False     False     False   \n",
       "\n",
       "      Line_918  Line_919  GPS_Latitude_From  GPS_Longitude_From  \\\n",
       "7288     False     False           0.720755           -1.979474   \n",
       "7289     False     False           0.720755           -1.979474   \n",
       "7290     False     False           0.720755           -1.979474   \n",
       "7291     False     False           0.720755           -1.979474   \n",
       "7292     False     False           0.720755           -1.979474   \n",
       "\n",
       "      GPS_Latitude_To  GPS_Longitude_To  \n",
       "7288         0.790881         -2.115646  \n",
       "7289         0.790881         -2.115646  \n",
       "7290         0.790881         -2.115646  \n",
       "7291         0.790881         -2.115646  \n",
       "7292         0.790881         -2.115646  \n",
       "\n",
       "[5 rows x 113 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_train = trainset.drop(columns=['VSYS', 'Besetzung'])\n",
    "y_train = trainset['Besetzung']\n",
    "X_test = testset.drop(columns=['VSYS', 'Besetzung'])\n",
    "y_test = testset['Besetzung']\n",
    "X_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "329f0dfc-4341-4bc7-9211-f9546a984783",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MAE: 0.8134882894111866\n",
      "MSE: 1.1619050458174205\n",
      "MAPE: 242.25472578631044%\n"
     ]
    }
   ],
   "source": [
    "# Create and train the linear regression model\n",
    "model = LinearRegression()\n",
    "model.fit(X_train, y_train)\n",
    "\n",
    "# Make predictions\n",
    "y_pred = model.predict(X_test)\n",
    "\n",
    "# Calculate errors\n",
    "mae = mean_absolute_error(y_test, y_pred)\n",
    "mse = mean_squared_error(y_test, y_pred)\n",
    "mape = np.mean(np.abs((y_test - y_pred) / y_test)) * 100\n",
    "\n",
    "print(f\"MAE: {mae}\")\n",
    "print(f\"MSE: {mse}\")\n",
    "print(f\"MAPE: {mape}%\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}