src/Q5_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b327293e-5eaa-4190-bd37-385dfb0aa827",
   "metadata": {},
   "source": [
    "# Preparing the datasets as input\n",
    "\n",
    "### Merge dataframes to have:\n",
    "- Feature variables: Time (FZ_AB), Richtung (1/2), Day type (Tage_SA/SO/SA_N/SO_N or weekday) GPS (Latitude/Longitude) and Vehicle_type (Tram, bus, trolleybus, seilbahn, forchbahn, nachtnetz)\n",
    "- Target variables: Number of passengers (Besetzung) or Occupancy or number of Freeseats.\n",
    "\n",
    "Tagtype probably will also be nice to include but eventually not included because the remarks from VBZ is not understandable.\n",
    "Vehicle type, direction (richtung), day type attributes could also be incorporated by running the optional cells.\n",
    "\n",
    "### Data downloaded from:\n",
    "https://data.stadt-zuerich.ch/dataset/vbz_fahrgastzahlen_ogd\n",
    "and\n",
    "https://data.stadt-zuerich.ch/dataset/vbz_fahrzeiten_ogd\n",
    "\n",
    "### This code is modified from: \n",
    "https://github.com/Chocobosaurus/uzh-data-science-project/blob/main/src/VBZ_Geospatial_Analysis.ipynb\n",
    "and\n",
    "https://github.com/VerkehrsbetriebeZuerich/vbz-flow-concept/blob/master/data-treatment-jupyter/vbz-jupyter.ipynb\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ad6adcf6-0aac-4640-a430-69450ce5398b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import math\n",
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d048bcb0-9df3-4991-9dbd-eabbd2285b86",
   "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>Tagtyp_Id</th>\n",
       "      <th>Linien_Id</th>\n",
       "      <th>Linienname</th>\n",
       "      <th>Plan_Fahrt_Id</th>\n",
       "      <th>Richtung</th>\n",
       "      <th>Sequenz</th>\n",
       "      <th>Haltestellen_Id</th>\n",
       "      <th>Nach_Hst_Id</th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Anzahl_Messungen</th>\n",
       "      <th>...</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Distanz</th>\n",
       "      <th>Tage_DTV</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Nachtnetz</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>ID_Abschnitt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>73477</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>190</td>\n",
       "      <td>165.0</td>\n",
       "      <td>17:45:30</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>83.2</td>\n",
       "      <td>459.0</td>\n",
       "      <td>34.15</td>\n",
       "      <td>34.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>19000165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>73477</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>165</td>\n",
       "      <td>166.0</td>\n",
       "      <td>17:47:30</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>83.4</td>\n",
       "      <td>186.0</td>\n",
       "      <td>34.15</td>\n",
       "      <td>34.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16500166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>73477</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>166</td>\n",
       "      <td>167.0</td>\n",
       "      <td>17:48:36</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>86.4</td>\n",
       "      <td>281.0</td>\n",
       "      <td>34.15</td>\n",
       "      <td>34.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16600167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>73477</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>167</td>\n",
       "      <td>168.0</td>\n",
       "      <td>17:50:30</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>87.8</td>\n",
       "      <td>277.0</td>\n",
       "      <td>34.15</td>\n",
       "      <td>34.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16700168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>73477</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>168</td>\n",
       "      <td>216.0</td>\n",
       "      <td>17:52:30</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>67.8</td>\n",
       "      <td>250.0</td>\n",
       "      <td>34.15</td>\n",
       "      <td>34.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16800216</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Tagtyp_Id  Linien_Id  Linienname  Plan_Fahrt_Id  Richtung  Sequenz  \\\n",
       "0          5         18           2          73477         1       14   \n",
       "1          5         18           2          73477         1       15   \n",
       "2          5         18           2          73477         1       16   \n",
       "3          5         18           2          73477         1       17   \n",
       "4          5         18           2          73477         1       18   \n",
       "\n",
       "   Haltestellen_Id  Nach_Hst_Id     FZ_AB  Anzahl_Messungen  ...  Besetzung  \\\n",
       "0              190        165.0  17:45:30                 5  ...       83.2   \n",
       "1              165        166.0  17:47:30                 5  ...       83.4   \n",
       "2              166        167.0  17:48:36                 5  ...       86.4   \n",
       "3              167        168.0  17:50:30                 5  ...       87.8   \n",
       "4              168        216.0  17:52:30                 5  ...       67.8   \n",
       "\n",
       "   Distanz  Tage_DTV  Tage_DWV  Tage_SA  Tage_SO  Nachtnetz  Tage_SA_N  \\\n",
       "0    459.0     34.15     34.15      0.0      0.0          0          0   \n",
       "1    186.0     34.15     34.15      0.0      0.0          0          0   \n",
       "2    281.0     34.15     34.15      0.0      0.0          0          0   \n",
       "3    277.0     34.15     34.15      0.0      0.0          0          0   \n",
       "4    250.0     34.15     34.15      0.0      0.0          0          0   \n",
       "\n",
       "   Tage_SO_N  ID_Abschnitt  \n",
       "0          0      19000165  \n",
       "1          0      16500166  \n",
       "2          0      16600167  \n",
       "3          0      16700168  \n",
       "4          0      16800216  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data directory\n",
    "datadir = ('data/fahrgastzahlen_2019')\n",
    "\n",
    "full_data = pd.read_csv(os.path.join(datadir, 'REISENDE.csv'), encoding = \"utf-8\", sep=';')\n",
    "full_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f1c38506-f3a5-4365-9c72-8ec592260997",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the file that contains GPS info and halt_id\n",
    "haltep = pd.read_csv(os.path.join(datadir, 'haltepunkt.csv'))\n",
    "\n",
    "# Load the file that contains Halt ID and name: Haltestellen_Id, Haltestellenkurzname\n",
    "haltest = pd.read_csv(os.path.join(datadir, 'HALTESTELLEN.csv'),encoding = \"utf-8\", sep=';')\n",
    "\n",
    "# Load the file that contains Haltestellenkurzname and halt_id\n",
    "haltest_link = pd.read_csv(os.path.join(datadir, 'haltestelle.csv'),encoding = \"utf-8\")\n",
    "\n",
    "# Load the file that contains capacity info = SITZPLAETZE and Plan_Fahrt_Id\n",
    "# GEFAESSGROESSE.csv not available pre-2020, copied 2020 data to use\n",
    "capacities = pd.read_csv(os.path.join(datadir, 'GEFAESSGROESSE.csv'), sep=';')\n",
    "\n",
    "# Load the file that contains Linie_Id and Vehicle type\n",
    "linie = pd.read_csv(os.path.join(datadir, 'LINIE.csv'), sep = ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6bc653bc-c951-4544-aa57-3898f833bd5f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "haltepunkte = haltep.copy()\n",
    "haltestellen_link = haltest_link.copy()\n",
    "haltestellen = haltest.copy()\n",
    "\n",
    "# GPS from 'haltepunkte merged to VBZ 'haltestelle' by halt_id, then haltestelle is merged to 'haltestellen' by haltestellenkurzname\n",
    "# Use only activ stops, keep only halt_id and GPS,\n",
    "# then group by id and compute the mean of GPS\n",
    "haltepunkte = haltepunkte[haltepunkte['halt_punkt_ist_aktiv'] == True]\n",
    "haltepunkte = haltepunkte.drop(columns=['halt_punkt_ist_aktiv','GPS_Bearing','halt_punkt_id','halt_punkt_diva'])\n",
    "haltepunkte = haltepunkte.groupby(['halt_id']).mean()[['GPS_Latitude','GPS_Longitude']].reset_index()\n",
    "haltepunkte['halt_id'].duplicated().sum()\n",
    "# no duplicated items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "4c508ff1-550d-4c02-8cc5-328414d75138",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only halt_id and halt_kurz\n",
    "haltestellen_link = haltestellen_link.drop(columns=['halt_diva', 'halt_ist_aktiv', 'halt_lang'])\n",
    "haltestellen = haltestellen.drop(columns=['Haltestellennummer'])\n",
    "\n",
    "halttemp = haltestellen_link.merge(haltepunkte, on='halt_id')\n",
    "\n",
    "stops = halttemp.merge(haltestellen, left_on='halt_kurz', right_on='Haltestellenkurzname')\n",
    "stops = stops.drop(columns=['halt_kurz', 'halt_id', 'Haltestellenkurzname', 'Haltestellenlangname'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "80bdf0f7-ab1a-4605-bf25-57fec10a4d8a",
   "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>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "      <th>Haltestellen_Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>47.452271</td>\n",
       "      <td>8.571438</td>\n",
       "      <td>349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>47.450151</td>\n",
       "      <td>8.563879</td>\n",
       "      <td>348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>47.294990</td>\n",
       "      <td>8.564286</td>\n",
       "      <td>424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>47.370167</td>\n",
       "      <td>8.513776</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>47.437911</td>\n",
       "      <td>8.562140</td>\n",
       "      <td>346</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   GPS_Latitude  GPS_Longitude  Haltestellen_Id\n",
       "0     47.452271       8.571438              349\n",
       "1     47.450151       8.563879              348\n",
       "2     47.294990       8.564286              424\n",
       "3     47.370167       8.513776              185\n",
       "4     47.437911       8.562140              346"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "af310849-2a98-4910-8d17-b5e0bf22af78",
   "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>Plan_Fahrt_Id</th>\n",
       "      <th>SITZPLAETZE</th>\n",
       "      <th>KAP_1m2</th>\n",
       "      <th>KAP_2m2</th>\n",
       "      <th>KAP_3m2</th>\n",
       "      <th>KAP_4m2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>26.0</td>\n",
       "      <td>39.5</td>\n",
       "      <td>53.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>26.0</td>\n",
       "      <td>39.5</td>\n",
       "      <td>53.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>39.5</td>\n",
       "      <td>53.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>26.0</td>\n",
       "      <td>39.5</td>\n",
       "      <td>53.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>26.0</td>\n",
       "      <td>39.5</td>\n",
       "      <td>53.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Plan_Fahrt_Id  SITZPLAETZE  KAP_1m2  KAP_2m2  KAP_3m2  KAP_4m2\n",
       "0              4         26.0     39.5     53.0     66.5     80.0\n",
       "1              5         26.0     39.5     53.0     66.5     80.0\n",
       "2              6         26.0     39.5     53.0     66.5     80.0\n",
       "3              7         26.0     39.5     53.0     66.5     80.0\n",
       "4              8         26.0     39.5     53.0     66.5     80.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "capacities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "31204e93-7a89-46c6-ad75-6f93ba821170",
   "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>Tagtyp_Id</th>\n",
       "      <th>Linien_Id</th>\n",
       "      <th>Linienname</th>\n",
       "      <th>Plan_Fahrt_Id</th>\n",
       "      <th>Richtung</th>\n",
       "      <th>Sequenz</th>\n",
       "      <th>Haltestellen_Id</th>\n",
       "      <th>Nach_Hst_Id</th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Anzahl_Messungen</th>\n",
       "      <th>...</th>\n",
       "      <th>Nachtnetz</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>ID_Abschnitt</th>\n",
       "      <th>SITZPLAETZE</th>\n",
       "      <th>KAP_1m2</th>\n",
       "      <th>KAP_2m2</th>\n",
       "      <th>KAP_3m2</th>\n",
       "      <th>KAP_4m2</th>\n",
       "      <th>Occupancy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>184</td>\n",
       "      <td>235.0</td>\n",
       "      <td>07:58:24</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18400235</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.098542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>235</td>\n",
       "      <td>236.0</td>\n",
       "      <td>07:59:42</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23500236</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.090208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>236</td>\n",
       "      <td>237.0</td>\n",
       "      <td>08:01:18</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23600237</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.071458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>237</td>\n",
       "      <td>238.0</td>\n",
       "      <td>08:02:18</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23700238</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.065937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>238</td>\n",
       "      <td>239.0</td>\n",
       "      <td>08:03:06</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23800239</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.058333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Tagtyp_Id  Linien_Id  Linienname  Plan_Fahrt_Id  Richtung  Sequenz  \\\n",
       "0          3         14          72           4574         1       12   \n",
       "1          3         14          72           4574         1       13   \n",
       "2          3         14          72           4574         1       14   \n",
       "3          3         14          72           4574         1       15   \n",
       "4          3         14          72           4574         1       16   \n",
       "\n",
       "   Haltestellen_Id  Nach_Hst_Id     FZ_AB  Anzahl_Messungen  ...  Nachtnetz  \\\n",
       "0              184        235.0  07:58:24                15  ...          0   \n",
       "1              235        236.0  07:59:42                15  ...          0   \n",
       "2              236        237.0  08:01:18                15  ...          0   \n",
       "3              237        238.0  08:02:18                15  ...          0   \n",
       "4              238        239.0  08:03:06                15  ...          0   \n",
       "\n",
       "   Tage_SA_N  Tage_SO_N  ID_Abschnitt  SITZPLAETZE  KAP_1m2  KAP_2m2  KAP_3m2  \\\n",
       "0          0          0      18400235         96.0    132.0    168.0    204.0   \n",
       "1          0          0      23500236         96.0    132.0    168.0    204.0   \n",
       "2          0          0      23600237         96.0    132.0    168.0    204.0   \n",
       "3          0          0      23700238         96.0    132.0    168.0    204.0   \n",
       "4          0          0      23800239         96.0    132.0    168.0    204.0   \n",
       "\n",
       "   KAP_4m2  Occupancy  \n",
       "0    240.0   0.098542  \n",
       "1    240.0   0.090208  \n",
       "2    240.0   0.071458  \n",
       "3    240.0   0.065937  \n",
       "4    240.0   0.058333  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge capacity data\n",
    "mergedata=full_data.copy()\n",
    "data = mergedata.merge(capacities, on = 'Plan_Fahrt_Id')\n",
    "\n",
    "# Calculate Occupancy and Freeseats values\n",
    "data['Occupancy'] = data['Besetzung'] / data['SITZPLAETZE']\n",
    "# Freeseats no longer calculated here but to be restored by SITZPLAETZE * 1-Occupancy\n",
    "#data['Freeseats'] = data['SITZPLAETZE'] - data['Besetzung']\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "7695bc18-f9aa-4c12-a529-49453f8eb12d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['00',\n",
       " '01',\n",
       " '02',\n",
       " '03',\n",
       " '04',\n",
       " '05',\n",
       " '06',\n",
       " '07',\n",
       " '08',\n",
       " '09',\n",
       " '10',\n",
       " '11',\n",
       " '12',\n",
       " '13',\n",
       " '14',\n",
       " '15',\n",
       " '16',\n",
       " '17',\n",
       " '18',\n",
       " '19',\n",
       " '20',\n",
       " '21',\n",
       " '22',\n",
       " '23']"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fix the 4-28 hour expression to 0-23\n",
    "def replace_hour(hour_str):\n",
    "    hour = int(hour_str)\n",
    "    if hour >= 24:\n",
    "        return str(hour % 24).zfill(2)  # Wrap the hour back to 0-23 and pad with leading zero if necessary\n",
    "    else:\n",
    "        return str(hour).zfill(2)\n",
    "\n",
    "data['FZ_AB'] = data['FZ_AB'].str.replace(r'^(\\d{2})', lambda x: replace_hour(x.group(1)), regex=True)\n",
    "\n",
    "sorted(data.FZ_AB.str.slice(stop=2).unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "db2f0e85-f00e-410b-825a-6a2668ee3460",
   "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>Tagtyp_Id</th>\n",
       "      <th>Linien_Id</th>\n",
       "      <th>Linienname</th>\n",
       "      <th>Plan_Fahrt_Id</th>\n",
       "      <th>Richtung</th>\n",
       "      <th>Sequenz</th>\n",
       "      <th>Haltestellen_Id</th>\n",
       "      <th>Nach_Hst_Id</th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Anzahl_Messungen</th>\n",
       "      <th>...</th>\n",
       "      <th>Nachtnetz</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>ID_Abschnitt</th>\n",
       "      <th>SITZPLAETZE</th>\n",
       "      <th>KAP_1m2</th>\n",
       "      <th>KAP_2m2</th>\n",
       "      <th>KAP_3m2</th>\n",
       "      <th>KAP_4m2</th>\n",
       "      <th>Occupancy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>184</td>\n",
       "      <td>235.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18400235</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.098542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>235</td>\n",
       "      <td>236.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23500236</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.090208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>236</td>\n",
       "      <td>237.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23600237</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.071458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>237</td>\n",
       "      <td>238.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23700238</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.065937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>238</td>\n",
       "      <td>239.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23800239</td>\n",
       "      <td>96.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>0.058333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Tagtyp_Id  Linien_Id  Linienname  Plan_Fahrt_Id  Richtung  Sequenz  \\\n",
       "0          3         14          72           4574         1       12   \n",
       "1          3         14          72           4574         1       13   \n",
       "2          3         14          72           4574         1       14   \n",
       "3          3         14          72           4574         1       15   \n",
       "4          3         14          72           4574         1       16   \n",
       "\n",
       "   Haltestellen_Id  Nach_Hst_Id  FZ_AB  Anzahl_Messungen  ...  Nachtnetz  \\\n",
       "0              184        235.0    8.0                15  ...          0   \n",
       "1              235        236.0    8.0                15  ...          0   \n",
       "2              236        237.0    8.0                15  ...          0   \n",
       "3              237        238.0    8.0                15  ...          0   \n",
       "4              238        239.0    8.0                15  ...          0   \n",
       "\n",
       "   Tage_SA_N  Tage_SO_N  ID_Abschnitt  SITZPLAETZE  KAP_1m2  KAP_2m2  KAP_3m2  \\\n",
       "0          0          0      18400235         96.0    132.0    168.0    204.0   \n",
       "1          0          0      23500236         96.0    132.0    168.0    204.0   \n",
       "2          0          0      23600237         96.0    132.0    168.0    204.0   \n",
       "3          0          0      23700238         96.0    132.0    168.0    204.0   \n",
       "4          0          0      23800239         96.0    132.0    168.0    204.0   \n",
       "\n",
       "   KAP_4m2  Occupancy  \n",
       "0    240.0   0.098542  \n",
       "1    240.0   0.090208  \n",
       "2    240.0   0.071458  \n",
       "3    240.0   0.065937  \n",
       "4    240.0   0.058333  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert hours from time_obj to 0.5 hour slots\n",
    "def time_to_float(time_str):\n",
    "\n",
    "    time_obj = pd.to_datetime(time_str, format=\"%H:%M:%S\")\n",
    "\n",
    "    hour = time_obj.hour\n",
    "    minute = time_obj.minute\n",
    "\n",
    "    fractional_hours = hour + minute / 60.0\n",
    "    rounded_hours = round(fractional_hours * 2) / 2  \n",
    "    return rounded_hours\n",
    "\n",
    "data['FZ_AB'] = data['FZ_AB'].apply(time_to_float)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bcf37686-703d-422a-9075-5f2f16df91a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = data.copy()\n",
    "sum(df[\"Haltestellen_Id\"].isna())\n",
    "# No NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f9ce34f2-13ee-42c0-bb01-0272d340a631",
   "metadata": {},
   "outputs": [],
   "source": [
    "#######################################################################\n",
    "# Option: \n",
    "# Merge vehicle type with df, merge all region bus routes as simply bus\n",
    "linie = linie.drop(columns = ['Linienname', 'Linienname_Fahrgastauskunft'])\n",
    "df = df.merge(linie, on = 'Linien_Id')\n",
    "\n",
    "def assignvehicletype(row):\n",
    "    val = None\n",
    "    if row['VSYS'] == 'B' or row['VSYS'] == 'BL' or row['VSYS'] == 'BZ' or row['VSYS'] == 'BP' or row['VSYS'] == 'BG':\n",
    "        val= 'B'\n",
    "    else:\n",
    "        return row['VSYS']            \n",
    "    return val\n",
    "    \n",
    "df['Vehicle_type'] = df.apply(assignvehicletype, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "989c27ee-9c72-4f7c-9f95-b3587c4a769c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns=[ 'Linienname',\n",
    "       'Sequenz', 'Nach_Hst_Id', 'Anzahl_Messungen', 'Einsteiger', 'Aussteiger','Distanz',\n",
    "       'Tage_DTV', 'Tage_DWV', 'ID_Abschnitt', 'KAP_1m2', 'KAP_2m2',\n",
    "       'KAP_3m2', 'KAP_4m2', 'VSYS']) # Keep Tagtyp_Id for now\n",
    "\n",
    "# found NaN with terminal stops\n",
    "df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "068a3dc8-68d0-4d5c-98c8-cf51d0818020",
   "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>Tagtyp_Id</th>\n",
       "      <th>Linien_Id</th>\n",
       "      <th>Linienname</th>\n",
       "      <th>Plan_Fahrt_Id</th>\n",
       "      <th>Richtung</th>\n",
       "      <th>Sequenz</th>\n",
       "      <th>Haltestellen_Id</th>\n",
       "      <th>Nach_Hst_Id</th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Anzahl_Messungen</th>\n",
       "      <th>...</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Distanz</th>\n",
       "      <th>Tage_DTV</th>\n",
       "      <th>Tage_DWV</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Nachtnetz</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>ID_Abschnitt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>184</td>\n",
       "      <td>235.0</td>\n",
       "      <td>07:58:24</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>9.46</td>\n",
       "      <td>544.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18400235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>235</td>\n",
       "      <td>236.0</td>\n",
       "      <td>07:59:42</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>8.66</td>\n",
       "      <td>647.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23500236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>236</td>\n",
       "      <td>237.0</td>\n",
       "      <td>08:01:18</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>6.86</td>\n",
       "      <td>224.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23600237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>237</td>\n",
       "      <td>238.0</td>\n",
       "      <td>08:02:18</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>6.33</td>\n",
       "      <td>257.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23700238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>72</td>\n",
       "      <td>4574</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>238</td>\n",
       "      <td>239.0</td>\n",
       "      <td>08:03:06</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>5.60</td>\n",
       "      <td>503.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23800239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Tagtyp_Id  Linien_Id  Linienname  Plan_Fahrt_Id  Richtung  Sequenz  \\\n",
       "51          3         14          72           4574         1       12   \n",
       "52          3         14          72           4574         1       13   \n",
       "53          3         14          72           4574         1       14   \n",
       "54          3         14          72           4574         1       15   \n",
       "55          3         14          72           4574         1       16   \n",
       "\n",
       "    Haltestellen_Id  Nach_Hst_Id     FZ_AB  Anzahl_Messungen  ...  Besetzung  \\\n",
       "51              184        235.0  07:58:24                15  ...       9.46   \n",
       "52              235        236.0  07:59:42                15  ...       8.66   \n",
       "53              236        237.0  08:01:18                15  ...       6.86   \n",
       "54              237        238.0  08:02:18                15  ...       6.33   \n",
       "55              238        239.0  08:03:06                15  ...       5.60   \n",
       "\n",
       "    Distanz  Tage_DTV  Tage_DWV  Tage_SA  Tage_SO  Nachtnetz  Tage_SA_N  \\\n",
       "51    544.0      62.0       0.0      0.0     62.0          0          0   \n",
       "52    647.0      62.0       0.0      0.0     62.0          0          0   \n",
       "53    224.0      62.0       0.0      0.0     62.0          0          0   \n",
       "54    257.0      62.0       0.0      0.0     62.0          0          0   \n",
       "55    503.0      62.0       0.0      0.0     62.0          0          0   \n",
       "\n",
       "    Tage_SO_N  ID_Abschnitt  \n",
       "51          0      18400235  \n",
       "52          0      23500236  \n",
       "53          0      23600237  \n",
       "54          0      23700238  \n",
       "55          0      23800239  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mergedata[mergedata['Linien_Id'] == 14].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d3da76c5-e3d9-4b96-9ac5-1aae635c988c",
   "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>Linien_Id</th>\n",
       "      <th>Richtung</th>\n",
       "      <th>FZ_AB</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Nachtnetz</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>SITZPLAETZE</th>\n",
       "      <th>Occupancy</th>\n",
       "      <th>Vehicle_type</th>\n",
       "      <th>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.46</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>0.098542</td>\n",
       "      <td>TR</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>TR</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>8.5</td>\n",
       "      <td>12.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.264167</td>\n",
       "      <td>TR</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>8.5</td>\n",
       "      <td>14.72</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.460000</td>\n",
       "      <td>TR</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>5.5</td>\n",
       "      <td>6.87</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.143125</td>\n",
       "      <td>TR</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Linien_Id  Richtung  FZ_AB  Besetzung  Tage_SA  Tage_SO  Nachtnetz  \\\n",
       "0         14         1    8.0       9.46      0.0     62.0          0   \n",
       "1         14         1    8.0      10.00      0.0     62.0          0   \n",
       "2         14         1    8.5      12.68      0.0     62.0          0   \n",
       "3         14         1    8.5      14.72      0.0     62.0          0   \n",
       "4         14         1    5.5       6.87      0.0      0.0          0   \n",
       "\n",
       "   Tage_SA_N  Tage_SO_N  SITZPLAETZE  Occupancy Vehicle_type  GPS_Latitude  \\\n",
       "0          0          0         96.0   0.098542           TR     47.369997   \n",
       "1          0          0         32.0   0.312500           TR     47.369997   \n",
       "2          0          0         48.0   0.264167           TR     47.369997   \n",
       "3          0          0         32.0   0.460000           TR     47.369997   \n",
       "4          0          0         48.0   0.143125           TR     47.369997   \n",
       "\n",
       "   GPS_Longitude  \n",
       "0       8.519147  \n",
       "1       8.519147  \n",
       "2       8.519147  \n",
       "3       8.519147  \n",
       "4       8.519147  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge GPS from stops with df\n",
    "df = df.merge(stops, on = 'Haltestellen_Id')\n",
    "df = df.drop(columns=['Plan_Fahrt_Id', 'Haltestellen_Id'])\n",
    "# Tagtyp makes sense to use, but cannot read the annot table\n",
    "df = df.drop(columns=['Tagtyp_Id'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "0bd0394c-d87a-46aa-baf1-d43d795e9caf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([4.87931e+05, 8.48530e+04, 1.74170e+04, 4.21100e+03, 1.01800e+03,\n",
       "        3.01000e+02, 9.00000e+01, 2.10000e+01, 3.00000e+00, 1.00000e+00]),\n",
       " array([0.        , 0.87023077, 1.74046154, 2.61069231, 3.48092308,\n",
       "        4.35115385, 5.22138462, 6.09161538, 6.96184615, 7.83207692,\n",
       "        8.70230769]),\n",
       " <BarContainer object of 10 artists>)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAkIAAAGdCAYAAAD+JxxnAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjguMCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy81sbWrAAAACXBIWXMAAA9hAAAPYQGoP6dpAAApFklEQVR4nO3dcVTV933/8RdBuUEG36EEbq7SSbceJ0GTBnoQTYOrgmagy9nOtMHchVPHkolSBi5Kul9jPC2ocZgNjrT27NTNJCV/GLacqQxqVw1TlBBZwGibc6IFA4hNrxd19KL4/f2R8j29oihqepXP83HOPad8v2/u/XyhLc/zvff7Ncy2bVsAAAAGeiDUCwAAAAgVQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsSaEegH3uqtXr6q7u1vR0dEKCwsL9XIAAMAtsG1bFy5ckMfj0QMP3Pi8DyF0E93d3UpMTAz1MgAAwG3o6urStGnTbrifELqJ6OhoSZ/9IGNiYkK8GgAAcCv6+/uVmJjo/B2/EULoJobfDouJiSGEAAC4z9zsYy18WBoAABhrTCG0YcMGhYWFBT3cbrez37ZtbdiwQR6PR5GRkZo/f76OHz8e9ByBQEBr1qxRXFycoqKitHTpUp05cyZoxufzyev1yrIsWZYlr9er8+fPB810dnZqyZIlioqKUlxcnIqKijQ4OBg0097erszMTEVGRmrq1KnauHGjbNseyyEDAIBxbMxnhB555BH19PQ4j/b2dmffli1bVFlZqerqarW0tMjtdisrK0sXLlxwZoqLi1VXV6fa2lo1NTXp4sWLys3N1dDQkDOTl5entrY21dfXq76+Xm1tbfJ6vc7+oaEh5eTk6NKlS2pqalJtba12796t0tJSZ6a/v19ZWVnyeDxqaWlRVVWVtm7dqsrKyjH/kAAAwDhlj8HLL79sP/roo9fdd/XqVdvtdtubNm1ytv3617+2Lcuyv/e979m2bdvnz5+3J06caNfW1jozn3zyif3AAw/Y9fX1tm3b9ocffmhLspubm52Zw4cP25LskydP2rZt23v37rUfeOAB+5NPPnFmfvSjH9kul8v2+/22bdv29u3bbcuy7F//+tfOTEVFhe3xeOyrV6/e8jH7/X5bkvO8AADg3nerf7/HfEboo48+ksfjUVJSkr7+9a/r448/liSdOnVKvb29ys7OdmZdLpcyMzN16NAhSVJra6suX74cNOPxeJSSkuLMHD58WJZlKT093ZmZM2eOLMsKmklJSZHH43FmFi1apEAgoNbWVmcmMzNTLpcraKa7u1unT58e62EDAIBxaEwhlJ6ern/7t3/Tf/3Xf+kHP/iBent7NXfuXH366afq7e2VJCUkJAR9T0JCgrOvt7dXERERio2NHXUmPj5+xGvHx8cHzVz7OrGxsYqIiBh1Zvjr4ZnrCQQC6u/vD3oAAIDxaUyXzz/11FPOf541a5YyMjL0h3/4h/rXf/1XzZkzR9LIy9Rs277ppWvXzlxv/m7M2L/5oPRo66moqNArr7wy6noBAMD4cEeXz0dFRWnWrFn66KOPnKvHrj3b0tfX55yJcbvdGhwclM/nG3Xm7NmzI17r3LlzQTPXvo7P59Ply5dHnenr65M08qzVbysrK5Pf73ceXV1do/8QAADAfeuOQigQCOjEiRN6+OGHlZSUJLfbrcbGRmf/4OCgDhw4oLlz50qSUlNTNXHixKCZnp4edXR0ODMZGRny+/06evSoM3PkyBH5/f6gmY6ODvX09DgzDQ0NcrlcSk1NdWYOHjwYdEl9Q0ODPB6Ppk+ffsNjcrlczs0TuYkiAADj3Fg+gV1aWmr/9Kc/tT/++GO7ubnZzs3NtaOjo+3Tp0/btm3bmzZtsi3Lst9++227vb3dfuaZZ+yHH37Y7u/vd57jhRdesKdNm2b/+Mc/tt9//337a1/7mv3oo4/aV65ccWYWL15sz5492z58+LB9+PBhe9asWXZubq6z/8qVK3ZKSoq9YMEC+/3337d//OMf29OmTbNXr17tzJw/f95OSEiwn3nmGbu9vd1+++237ZiYGHvr1q1jOWSuGgMA4D50q3+/xxRCy5cvtx9++GF74sSJtsfjsf/8z//cPn78uLP/6tWr9ssvv2y73W7b5XLZTz75pN3e3h70HAMDA/bq1avtyZMn25GRkXZubq7d2dkZNPPpp5/aK1assKOjo+3o6Gh7xYoVts/nC5r5xS9+Yefk5NiRkZH25MmT7dWrVwddKm/btv3BBx/YX/3qV22Xy2W73W57w4YNY7p03rYJIQAA7ke3+vc7zLa51fJo+vv7ZVmW/H4/b5MBAHCfuNW/3/xbYwAAwFiEEAAAMNaY7iOEu2/6+j2hXsKYnd6UE+olAABwV3BGCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLHuKIQqKioUFham4uJiZ5tt29qwYYM8Ho8iIyM1f/58HT9+POj7AoGA1qxZo7i4OEVFRWnp0qU6c+ZM0IzP55PX65VlWbIsS16vV+fPnw+a6ezs1JIlSxQVFaW4uDgVFRVpcHAwaKa9vV2ZmZmKjIzU1KlTtXHjRtm2fSeHDQAAxonbDqGWlhbt2LFDs2fPDtq+ZcsWVVZWqrq6Wi0tLXK73crKytKFCxecmeLiYtXV1am2tlZNTU26ePGicnNzNTQ05Mzk5eWpra1N9fX1qq+vV1tbm7xer7N/aGhIOTk5unTpkpqamlRbW6vdu3ertLTUmenv71dWVpY8Ho9aWlpUVVWlrVu3qrKy8nYPGwAAjCNh9m2cHrl48aIef/xxbd++Xd/5znf02GOP6bXXXpNt2/J4PCouLta6deskfXb2JyEhQZs3b9bzzz8vv9+vhx56SLt27dLy5cslSd3d3UpMTNTevXu1aNEinThxQsnJyWpublZ6erokqbm5WRkZGTp58qRmzJihffv2KTc3V11dXfJ4PJKk2tpa5efnq6+vTzExMaqpqVFZWZnOnj0rl8slSdq0aZOqqqp05swZhYWF3fRY+/v7ZVmW/H6/YmJixvqjuqnp6/fc9ef8vJ3elBPqJQAAMKpb/ft9W2eECgsLlZOTo4ULFwZtP3XqlHp7e5Wdne1sc7lcyszM1KFDhyRJra2tunz5ctCMx+NRSkqKM3P48GFZluVEkCTNmTNHlmUFzaSkpDgRJEmLFi1SIBBQa2urM5OZmelE0PBMd3e3Tp8+fd1jCwQC6u/vD3oAAIDxacwhVFtbq/fff18VFRUj9vX29kqSEhISgrYnJCQ4+3p7exUREaHY2NhRZ+Lj40c8f3x8fNDMta8TGxuriIiIUWeGvx6euVZFRYXzuSTLspSYmHjdOQAAcP8bUwh1dXXpm9/8pl5//XU9+OCDN5y79i0n27Zv+jbUtTPXm78bM8PvBN5oPWVlZfL7/c6jq6tr1HUDAID715hCqLW1VX19fUpNTdWECRM0YcIEHThwQP/8z/+sCRMm3PBsS19fn7PP7XZrcHBQPp9v1JmzZ8+OeP1z584FzVz7Oj6fT5cvXx51pq+vT9LIs1bDXC6XYmJigh4AAGB8GlMILViwQO3t7Wpra3MeaWlpWrFihdra2vTFL35RbrdbjY2NzvcMDg7qwIEDmjt3riQpNTVVEydODJrp6elRR0eHM5ORkSG/36+jR486M0eOHJHf7w+a6ejoUE9PjzPT0NAgl8ul1NRUZ+bgwYNBl9Q3NDTI4/Fo+vTpYzl0AAAwDk0Yy3B0dLRSUlKCtkVFRWnKlCnO9uLiYpWXl+tLX/qSvvSlL6m8vFyTJk1SXl6eJMmyLK1cuVKlpaWaMmWKJk+erLVr12rWrFnOh69nzpypxYsXq6CgQN///vclSX/zN3+j3NxczZgxQ5KUnZ2t5ORkeb1evfrqq/rVr36ltWvXqqCgwDmLk5eXp1deeUX5+fl66aWX9NFHH6m8vFzf/va3b+mKMQAAML6NKYRuxYsvvqiBgQGtWrVKPp9P6enpamhoUHR0tDOzbds2TZgwQcuWLdPAwIAWLFignTt3Kjw83Jl54403VFRU5FxdtnTpUlVXVzv7w8PDtWfPHq1atUrz5s1TZGSk8vLytHXrVmfGsiw1NjaqsLBQaWlpio2NVUlJiUpKSu72YQMAgPvQbd1HyCTcR2gk7iMEALjXfa73EQIAABgPCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxxhRCNTU1mj17tmJiYhQTE6OMjAzt27fP2W/btjZs2CCPx6PIyEjNnz9fx48fD3qOQCCgNWvWKC4uTlFRUVq6dKnOnDkTNOPz+eT1emVZlizLktfr1fnz54NmOjs7tWTJEkVFRSkuLk5FRUUaHBwMmmlvb1dmZqYiIyM1depUbdy4UbZtj+WQAQDAODamEJo2bZo2bdqk9957T++9956+9rWv6c/+7M+c2NmyZYsqKytVXV2tlpYWud1uZWVl6cKFC85zFBcXq66uTrW1tWpqatLFixeVm5uroaEhZyYvL09tbW2qr69XfX292tra5PV6nf1DQ0PKycnRpUuX1NTUpNraWu3evVulpaXOTH9/v7KysuTxeNTS0qKqqipt3bpVlZWVt/3DAgAA40uYfYenSCZPnqxXX31V3/jGN+TxeFRcXKx169ZJ+uzsT0JCgjZv3qznn39efr9fDz30kHbt2qXly5dLkrq7u5WYmKi9e/dq0aJFOnHihJKTk9Xc3Kz09HRJUnNzszIyMnTy5EnNmDFD+/btU25urrq6uuTxeCRJtbW1ys/PV19fn2JiYlRTU6OysjKdPXtWLpdLkrRp0yZVVVXpzJkzCgsLu6Xj6+/vl2VZ8vv9iomJuZMf1XVNX7/nrj/n5+30ppxQLwEAgFHd6t/v2/6M0NDQkGpra3Xp0iVlZGTo1KlT6u3tVXZ2tjPjcrmUmZmpQ4cOSZJaW1t1+fLloBmPx6OUlBRn5vDhw7Isy4kgSZozZ44sywqaSUlJcSJIkhYtWqRAIKDW1lZnJjMz04mg4Znu7m6dPn36hscVCATU398f9AAAAOPTmEOovb1dv/d7vyeXy6UXXnhBdXV1Sk5OVm9vryQpISEhaD4hIcHZ19vbq4iICMXGxo46Ex8fP+J14+Pjg2aufZ3Y2FhFRESMOjP89fDM9VRUVDifTbIsS4mJiaP/QAAAwH1rzCE0Y8YMtbW1qbm5WX/7t3+r5557Th9++KGz/9q3nGzbvunbUNfOXG/+bswMvws42nrKysrk9/udR1dX16hrBwAA968xh1BERIT+6I/+SGlpaaqoqNCjjz6qf/qnf5Lb7ZY08mxLX1+fcybG7XZrcHBQPp9v1JmzZ8+OeN1z584FzVz7Oj6fT5cvXx51pq+vT9LIs1a/zeVyOVfFDT8AAMD4dMf3EbJtW4FAQElJSXK73WpsbHT2DQ4O6sCBA5o7d64kKTU1VRMnTgya6enpUUdHhzOTkZEhv9+vo0ePOjNHjhyR3+8Pmuno6FBPT48z09DQIJfLpdTUVGfm4MGDQZfUNzQ0yOPxaPr06Xd62AAAYBwYUwi99NJLevfdd3X69Gm1t7frW9/6ln76059qxYoVCgsLU3FxscrLy1VXV6eOjg7l5+dr0qRJysvLkyRZlqWVK1eqtLRU+/fv17Fjx/Tss89q1qxZWrhwoSRp5syZWrx4sQoKCtTc3Kzm5mYVFBQoNzdXM2bMkCRlZ2crOTlZXq9Xx44d0/79+7V27VoVFBQ4Z3Dy8vLkcrmUn5+vjo4O1dXVqby8XCUlJbd8xRgAABjfJoxl+OzZs/J6verp6ZFlWZo9e7bq6+uVlZUlSXrxxRc1MDCgVatWyefzKT09XQ0NDYqOjnaeY9u2bZowYYKWLVumgYEBLViwQDt37lR4eLgz88Ybb6ioqMi5umzp0qWqrq529oeHh2vPnj1atWqV5s2bp8jISOXl5Wnr1q3OjGVZamxsVGFhodLS0hQbG6uSkhKVlJTc3k8KAACMO3d8H6HxjvsIjcR9hAAA97rP/T5CAAAA9ztCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMYihAAAgLEIIQAAYKwxhVBFRYW+8pWvKDo6WvHx8Xr66af1s5/9LGjGtm1t2LBBHo9HkZGRmj9/vo4fPx40EwgEtGbNGsXFxSkqKkpLly7VmTNngmZ8Pp+8Xq8sy5JlWfJ6vTp//nzQTGdnp5YsWaKoqCjFxcWpqKhIg4ODQTPt7e3KzMxUZGSkpk6dqo0bN8q27bEcNgAAGKfGFEIHDhxQYWGhmpub1djYqCtXrig7O1uXLl1yZrZs2aLKykpVV1erpaVFbrdbWVlZunDhgjNTXFysuro61dbWqqmpSRcvXlRubq6Ghoacmby8PLW1tam+vl719fVqa2uT1+t19g8NDSknJ0eXLl1SU1OTamtrtXv3bpWWljoz/f39ysrKksfjUUtLi6qqqrR161ZVVlbe1g8LAACML2H2HZweOXfunOLj43XgwAE9+eSTsm1bHo9HxcXFWrdunaTPzv4kJCRo8+bNev755+X3+/XQQw9p165dWr58uSSpu7tbiYmJ2rt3rxYtWqQTJ04oOTlZzc3NSk9PlyQ1NzcrIyNDJ0+e1IwZM7Rv3z7l5uaqq6tLHo9HklRbW6v8/Hz19fUpJiZGNTU1Kisr09mzZ+VyuSRJmzZtUlVVlc6cOaOwsLCbHmN/f78sy5Lf71dMTMzt/qhuaPr6PXf9OT9vpzflhHoJAACM6lb/ft/RZ4T8fr8kafLkyZKkU6dOqbe3V9nZ2c6My+VSZmamDh06JElqbW3V5cuXg2Y8Ho9SUlKcmcOHD8uyLCeCJGnOnDmyLCtoJiUlxYkgSVq0aJECgYBaW1udmczMTCeChme6u7t1+vTp6x5TIBBQf39/0AMAAIxPtx1Ctm2rpKRETzzxhFJSUiRJvb29kqSEhISg2YSEBGdfb2+vIiIiFBsbO+pMfHz8iNeMj48Pmrn2dWJjYxURETHqzPDXwzPXqqiocD6XZFmWEhMTb/KTAAAA96vbDqHVq1frgw8+0I9+9KMR+659y8m27Zu+DXXtzPXm78bM8DuBN1pPWVmZ/H6/8+jq6hp13QAA4P51WyG0Zs0avfPOO/rv//5vTZs2zdnudrsljTzb0tfX55yJcbvdGhwclM/nG3Xm7NmzI1733LlzQTPXvo7P59Ply5dHnenr65M08qzVMJfLpZiYmKAHAAAYn8YUQrZta/Xq1Xr77bf1k5/8RElJSUH7k5KS5Ha71djY6GwbHBzUgQMHNHfuXElSamqqJk6cGDTT09Ojjo4OZyYjI0N+v19Hjx51Zo4cOSK/3x8009HRoZ6eHmemoaFBLpdLqampzszBgweDLqlvaGiQx+PR9OnTx3LoAABgHBpTCBUWFur111/Xm2++qejoaPX29qq3t1cDAwOSPnu7qbi4WOXl5aqrq1NHR4fy8/M1adIk5eXlSZIsy9LKlStVWlqq/fv369ixY3r22Wc1a9YsLVy4UJI0c+ZMLV68WAUFBWpublZzc7MKCgqUm5urGTNmSJKys7OVnJwsr9erY8eOaf/+/Vq7dq0KCgqcszh5eXlyuVzKz89XR0eH6urqVF5erpKSklu6YgwAAIxvE8YyXFNTI0maP39+0PYf/vCHys/PlyS9+OKLGhgY0KpVq+Tz+ZSenq6GhgZFR0c789u2bdOECRO0bNkyDQwMaMGCBdq5c6fCw8OdmTfeeENFRUXO1WVLly5VdXW1sz88PFx79uzRqlWrNG/ePEVGRiovL09bt251ZizLUmNjowoLC5WWlqbY2FiVlJSopKRkLIcNAADGqTu6j5AJuI/QSNxHCABwr/ud3EcIAADgfkYIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjDXmEDp48KCWLFkij8ejsLAw/fu//3vQftu2tWHDBnk8HkVGRmr+/Pk6fvx40EwgENCaNWsUFxenqKgoLV26VGfOnAma8fl88nq9sixLlmXJ6/Xq/PnzQTOdnZ1asmSJoqKiFBcXp6KiIg0ODgbNtLe3KzMzU5GRkZo6dao2btwo27bHetgAAGAcGnMIXbp0SY8++qiqq6uvu3/Lli2qrKxUdXW1Wlpa5Ha7lZWVpQsXLjgzxcXFqqurU21trZqamnTx4kXl5uZqaGjImcnLy1NbW5vq6+tVX1+vtrY2eb1eZ//Q0JBycnJ06dIlNTU1qba2Vrt371Zpaakz09/fr6ysLHk8HrW0tKiqqkpbt25VZWXlWA8bAACMQ2H2HZweCQsLU11dnZ5++mlJn50N8ng8Ki4u1rp16yR9dvYnISFBmzdv1vPPPy+/36+HHnpIu3bt0vLlyyVJ3d3dSkxM1N69e7Vo0SKdOHFCycnJam5uVnp6uiSpublZGRkZOnnypGbMmKF9+/YpNzdXXV1d8ng8kqTa2lrl5+err69PMTExqqmpUVlZmc6ePSuXyyVJ2rRpk6qqqnTmzBmFhYXd9Bj7+/tlWZb8fr9iYmJu90d1Q9PX77nrz/l5O70pJ9RLAABgVLf69/uufkbo1KlT6u3tVXZ2trPN5XIpMzNThw4dkiS1trbq8uXLQTMej0cpKSnOzOHDh2VZlhNBkjRnzhxZlhU0k5KS4kSQJC1atEiBQECtra3OTGZmphNBwzPd3d06ffr0dY8hEAiov78/6AEAAManuxpCvb29kqSEhISg7QkJCc6+3t5eRUREKDY2dtSZ+Pj4Ec8fHx8fNHPt68TGxioiImLUmeGvh2euVVFR4XwuybIsJSYm3vzAAQDAfelzuWrs2recbNu+6dtQ185cb/5uzAy/E3ij9ZSVlcnv9zuPrq6uUdcNAADuX3c1hNxut6SRZ1v6+vqcMzFut1uDg4Py+Xyjzpw9e3bE8587dy5o5trX8fl8unz58qgzfX19kkaetRrmcrkUExMT9AAAAOPTXQ2hpKQkud1uNTY2OtsGBwd14MABzZ07V5KUmpqqiRMnBs309PSoo6PDmcnIyJDf79fRo0edmSNHjsjv9wfNdHR0qKenx5lpaGiQy+VSamqqM3Pw4MGgS+obGhrk8Xg0ffr0u3noAADgPjTmELp48aLa2trU1tYm6bMPSLe1tamzs1NhYWEqLi5WeXm56urq1NHRofz8fE2aNEl5eXmSJMuytHLlSpWWlmr//v06duyYnn32Wc2aNUsLFy6UJM2cOVOLFy9WQUGBmpub1dzcrIKCAuXm5mrGjBmSpOzsbCUnJ8vr9erYsWPav3+/1q5dq4KCAucsTl5enlwul/Lz89XR0aG6ujqVl5erpKTklq4YAwAA49uEsX7De++9pz/5kz9xvi4pKZEkPffcc9q5c6defPFFDQwMaNWqVfL5fEpPT1dDQ4Oio6Od79m2bZsmTJigZcuWaWBgQAsWLNDOnTsVHh7uzLzxxhsqKipyri5bunRp0L2LwsPDtWfPHq1atUrz5s1TZGSk8vLytHXrVmfGsiw1NjaqsLBQaWlpio2NVUlJibNmAABgtju6j5AJuI/QSNxHCABwrwvJfYQAAADuJ4QQAAAwFiEEAACMRQgBAABjjfmqMYAPeAMAxgvOCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIxFCAEAAGMRQgAAwFiEEAAAMBYhBAAAjEUIAQAAYxFCAADAWIQQAAAwFiEEAACMRQgBAABjEUIAAMBYhBAAADAWIQQAAIw1IdQLAH4Xpq/fE+oljNnpTTmhXgIAjHucEQIAAMYihAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLCNCaPv27UpKStKDDz6o1NRUvfvuu6FeEgAAuAeM+xsqvvXWWyouLtb27ds1b948ff/739dTTz2lDz/8UF/4whdCvTzghrgJJAB8/sb9GaHKykqtXLlSf/3Xf62ZM2fqtddeU2JiompqakK9NAAAEGLj+ozQ4OCgWltbtX79+qDt2dnZOnTo0HW/JxAIKBAIOF/7/X5JUn9//+eyxquB//tcnhcIhc/rfycAMFbD/39k2/aoc+M6hH75y19qaGhICQkJQdsTEhLU29t73e+pqKjQK6+8MmJ7YmLi57JGYDyxXgv1CgAg2IULF2RZ1g33j+sQGhYWFhb0tW3bI7YNKysrU0lJifP11atX9atf/UpTpky54ffcrv7+fiUmJqqrq0sxMTF39blxZ/jd3Nv4/dy7+N3c20z6/di2rQsXLsjj8Yw6N65DKC4uTuHh4SPO/vT19Y04SzTM5XLJ5XIFbfv93//9z2uJkqSYmJhx/1/I+xW/m3sbv597F7+be5spv5/RzgQNG9cflo6IiFBqaqoaGxuDtjc2Nmru3LkhWhUAALhXjOszQpJUUlIir9ertLQ0ZWRkaMeOHers7NQLL7wQ6qUBAIAQG/chtHz5cn366afauHGjenp6lJKSor179+oP/uAPQr00uVwuvfzyyyPeikPo8bu5t/H7uXfxu7m38fsZKcy+2XVlAAAA49S4/owQAADAaAghAABgLEIIAAAYixACAADGIoRCZPv27UpKStKDDz6o1NRUvfvuu6FeEvTZP7Hyla98RdHR0YqPj9fTTz+tn/3sZ6FeFq6joqJCYWFhKi4uDvVS8BuffPKJnn32WU2ZMkWTJk3SY489ptbW1lAvC5KuXLmif/iHf1BSUpIiIyP1xS9+URs3btTVq1dDvbSQI4RC4K233lJxcbG+9a1v6dixY/rqV7+qp556Sp2dnaFemvEOHDigwsJCNTc3q7GxUVeuXFF2drYuXboU6qXht7S0tGjHjh2aPXt2qJeC3/D5fJo3b54mTpyoffv26cMPP9Q//uM/fu535set2bx5s773ve+purpaJ06c0JYtW/Tqq6+qqqoq1EsLOS6fD4H09HQ9/vjjqqmpcbbNnDlTTz/9tCoqKkK4Mlzr3Llzio+P14EDB/Tkk0+GejmQdPHiRT3++OPavn27vvOd7+ixxx7Ta6+9FuplGW/9+vX6n//5H85u36Nyc3OVkJCgf/mXf3G2/cVf/IUmTZqkXbt2hXBloccZod+xwcFBtba2Kjs7O2h7dna2Dh06FKJV4Ub8fr8kafLkySFeCYYVFhYqJydHCxcuDPVS8FveeecdpaWl6S//8i8VHx+vL3/5y/rBD34Q6mXhN5544gnt379fP//5zyVJ//u//6umpib96Z/+aYhXFnrj/s7S95pf/vKXGhoaGvGPviYkJIz4x2ERWrZtq6SkRE888YRSUlJCvRxIqq2t1fvvv6+WlpZQLwXX+Pjjj1VTU6OSkhK99NJLOnr0qIqKiuRyufRXf/VXoV6e8datWye/368//uM/Vnh4uIaGhvTd735XzzzzTKiXFnKEUIiEhYUFfW3b9ohtCK3Vq1frgw8+UFNTU6iXAkldXV365je/qYaGBj344IOhXg6ucfXqVaWlpam8vFyS9OUvf1nHjx9XTU0NIXQPeOutt/T666/rzTff1COPPKK2tjYVFxfL4/HoueeeC/XyQooQ+h2Li4tTeHj4iLM/fX19I84SIXTWrFmjd955RwcPHtS0adNCvRxIam1tVV9fn1JTU51tQ0NDOnjwoKqrqxUIBBQeHh7CFZrt4YcfVnJyctC2mTNnavfu3SFaEX7b3//932v9+vX6+te/LkmaNWuWfvGLX6iiosL4EOIzQr9jERERSk1NVWNjY9D2xsZGzZ07N0SrwjDbtrV69Wq9/fbb+slPfqKkpKRQLwm/sWDBArW3t6utrc15pKWlacWKFWprayOCQmzevHkjbjXx85///J74B64h/d///Z8eeCD4T354eDiXz4szQiFRUlIir9ertLQ0ZWRkaMeOHers7NQLL7wQ6qUZr7CwUG+++ab+4z/+Q9HR0c6ZO8uyFBkZGeLVmS06OnrEZ7WioqI0ZcoUPsN1D/i7v/s7zZ07V+Xl5Vq2bJmOHj2qHTt2aMeOHaFeGiQtWbJE3/3ud/WFL3xBjzzyiI4dO6bKykp94xvfCPXSQo7L50Nk+/bt2rJli3p6epSSkqJt27ZxefY94Eaf0/rhD3+o/Pz83+1icFPz58/n8vl7yH/+53+qrKxMH330kZKSklRSUqKCgoJQLwuSLly4oP/3//6f6urq1NfXJ4/Ho2eeeUbf/va3FREREerlhRQhBAAAjMVnhAAAgLEIIQAAYCxCCAAAGIsQAgAAxiKEAACAsQghAABgLEIIAAAYixACAADGIoQAAICxCCEAAGAsQggAABiLEAIAAMb6/1uAsGdkxHsPAAAAAElFTkSuQmCC",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Check if Occupancy distribution makes sense\n",
    "plt.hist(df['Occupancy'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6871a960-c43e-461e-b050-1e26688c8551",
   "metadata": {},
   "outputs": [],
   "source": [
    "#####################################################################\n",
    "# Option: \n",
    "# Change weekday/weekend attribute to binary values\n",
    "def assign_binary_value(df, column_name):\n",
    "    binary_values = []\n",
    "    for index, row in df.iterrows():\n",
    "        if row[column_name] != 0:\n",
    "            binary_values.append(1)\n",
    "        else:\n",
    "            binary_values.append(0)\n",
    "    df[column_name] = binary_values  \n",
    "    return df\n",
    "\n",
    "# df = assign_binary_value(df, 'Tage_SA')\n",
    "# df = assign_binary_value(df, 'Tage_SO')\n",
    "# df = assign_binary_value(df, 'Tage_SA_N')\n",
    "# df = assign_binary_value(df, 'Tage_SO_N')\n",
    "# df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "053338d9-df39-4ed9-8560-5daea60f5341",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create weekday attribute\n",
    "def assigndaytype(row):\n",
    "    if row['Tage_SA'] == 0:\n",
    "        if row['Tage_SO'] == 0:\n",
    "            if row['Tage_SA_N'] == 0:\n",
    "                if row['Tage_SO_N'] == 0:\n",
    "                    if row['Nachtnetz'] == 0:\n",
    "                        val=1\n",
    "                    else:\n",
    "                        val=0\n",
    "                else:\n",
    "                        val=0\n",
    "            else:\n",
    "                        val=0\n",
    "        else:\n",
    "                        val=0\n",
    "    else:\n",
    "                        val=0\n",
    "    return val\n",
    "    \n",
    "df['Weekday'] = df.apply(assigndaytype, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e5f72a25-e4e4-445a-8749-a33781ce17ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check if Nachtnetz correspond to Tage_SA_N or Tage_SO_N\n",
    "def filter_non_zero_values(dataset, column_a, column_b):\n",
    "    filtered_data = []\n",
    "    for index, row in dataset.iterrows():\n",
    "        if row[column_a] != 0 or row[column_b] != 0:\n",
    "            filtered_data.append(row)\n",
    "    return pd.DataFrame(filtered_data)\n",
    "\n",
    "dataset = df  # Your dataset\n",
    "column_a = 'Tage_SA_N'\n",
    "column_b = 'Tage_SO_N'\n",
    "# filtered_data = filter_non_zero_values(dataset, column_a, column_b)\n",
    "# filtered_data.Nachtnetz.unique()\n",
    "\n",
    "# So if Tage_SA_N or Tage_SO_N is sampled, Nachtnetz attribute is set to 1\n",
    "# But if the two above are both 0, sometimes Nachtnetz is also 1 because of weekday night traffic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "05ca178f-a469-4ed4-8661-d1b6d36c010c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'FZ_AB': 'Time', 'SITZPLAETZE': 'Capacity'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a5c431de-901e-48da-a4d6-601c3bd63783",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 595846 entries, 0 to 595845\n",
      "Data columns (total 14 columns):\n",
      " #   Column         Non-Null Count   Dtype  \n",
      "---  ------         --------------   -----  \n",
      " 0   Richtung       595846 non-null  int64  \n",
      " 1   Time           595846 non-null  float64\n",
      " 2   Besetzung      595846 non-null  float64\n",
      " 3   Tage_SA        595846 non-null  float64\n",
      " 4   Tage_SO        595846 non-null  float64\n",
      " 5   Nachtnetz      595846 non-null  int64  \n",
      " 6   Tage_SA_N      595846 non-null  int64  \n",
      " 7   Tage_SO_N      595846 non-null  int64  \n",
      " 8   Capacity       595846 non-null  float64\n",
      " 9   Occupancy      595846 non-null  float64\n",
      " 10  Vehicle_type   595846 non-null  object \n",
      " 11  GPS_Latitude   595846 non-null  float64\n",
      " 12  GPS_Longitude  595846 non-null  float64\n",
      " 13  Weekday        595846 non-null  int64  \n",
      "dtypes: float64(8), int64(5), object(1)\n",
      "memory usage: 63.6+ MB\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>Richtung</th>\n",
       "      <th>Time</th>\n",
       "      <th>Besetzung</th>\n",
       "      <th>Tage_SA</th>\n",
       "      <th>Tage_SO</th>\n",
       "      <th>Nachtnetz</th>\n",
       "      <th>Tage_SA_N</th>\n",
       "      <th>Tage_SO_N</th>\n",
       "      <th>Capacity</th>\n",
       "      <th>Occupancy</th>\n",
       "      <th>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "      <th>Weekday</th>\n",
       "      <th>Vehicle_type_B</th>\n",
       "      <th>Vehicle_type_FB</th>\n",
       "      <th>Vehicle_type_N</th>\n",
       "      <th>Vehicle_type_SB</th>\n",
       "      <th>Vehicle_type_T</th>\n",
       "      <th>Vehicle_type_TR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.46</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>0.098542</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>8.5</td>\n",
       "      <td>12.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.264167</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>8.5</td>\n",
       "      <td>14.72</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.460000</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5.5</td>\n",
       "      <td>6.87</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.143125</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>5.5</td>\n",
       "      <td>12.77</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.141889</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>6.54</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.109000</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>5.5</td>\n",
       "      <td>10.06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.111778</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.178125</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.086111</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>11.25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>6.5</td>\n",
       "      <td>15.02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>0.341364</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2</td>\n",
       "      <td>6.5</td>\n",
       "      <td>17.72</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.196889</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2</td>\n",
       "      <td>6.5</td>\n",
       "      <td>12.31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.205167</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2</td>\n",
       "      <td>7.0</td>\n",
       "      <td>22.51</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.703438</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2</td>\n",
       "      <td>7.0</td>\n",
       "      <td>21.61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.450208</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2</td>\n",
       "      <td>7.0</td>\n",
       "      <td>38.42</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.426889</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2</td>\n",
       "      <td>7.5</td>\n",
       "      <td>36.08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.751667</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2</td>\n",
       "      <td>7.5</td>\n",
       "      <td>50.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>1.153409</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2</td>\n",
       "      <td>7.5</td>\n",
       "      <td>52.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.587333</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Richtung  Time  Besetzung  Tage_SA  Tage_SO  Nachtnetz  Tage_SA_N  \\\n",
       "0          1   8.0       9.46      0.0     62.0          0          0   \n",
       "1          1   8.0      10.00      0.0     62.0          0          0   \n",
       "2          1   8.5      12.68      0.0     62.0          0          0   \n",
       "3          1   8.5      14.72      0.0     62.0          0          0   \n",
       "4          1   5.5       6.87      0.0      0.0          0          0   \n",
       "5          1   5.5      12.77      0.0      0.0          0          0   \n",
       "6          1   6.5       6.54      0.0      0.0          0          0   \n",
       "7          2   5.5      10.06      0.0      0.0          0          0   \n",
       "8          2   6.0       8.55      0.0      0.0          0          0   \n",
       "9          2   6.0       7.75      0.0      0.0          0          0   \n",
       "10         2   6.0      11.25      0.0      0.0          0          0   \n",
       "11         2   6.5      15.02      0.0      0.0          0          0   \n",
       "12         2   6.5      17.72      0.0      0.0          0          0   \n",
       "13         2   6.5      12.31      0.0      0.0          0          0   \n",
       "14         2   7.0      22.51      0.0      0.0          0          0   \n",
       "15         2   7.0      21.61      0.0      0.0          0          0   \n",
       "16         2   7.0      38.42      0.0      0.0          0          0   \n",
       "17         2   7.5      36.08      0.0      0.0          0          0   \n",
       "18         2   7.5      50.75      0.0      0.0          0          0   \n",
       "19         2   7.5      52.86      0.0      0.0          0          0   \n",
       "\n",
       "    Tage_SO_N  Capacity  Occupancy  GPS_Latitude  GPS_Longitude  Weekday  \\\n",
       "0           0      96.0   0.098542     47.369997       8.519147        0   \n",
       "1           0      32.0   0.312500     47.369997       8.519147        0   \n",
       "2           0      48.0   0.264167     47.369997       8.519147        0   \n",
       "3           0      32.0   0.460000     47.369997       8.519147        0   \n",
       "4           0      48.0   0.143125     47.369997       8.519147        1   \n",
       "5           0      90.0   0.141889     47.369997       8.519147        1   \n",
       "6           0      60.0   0.109000     47.369997       8.519147        1   \n",
       "7           0      90.0   0.111778     47.369997       8.519147        1   \n",
       "8           0      48.0   0.178125     47.369997       8.519147        1   \n",
       "9           0      90.0   0.086111     47.369997       8.519147        1   \n",
       "10          0      90.0   0.125000     47.369997       8.519147        1   \n",
       "11          0      44.0   0.341364     47.369997       8.519147        1   \n",
       "12          0      90.0   0.196889     47.369997       8.519147        1   \n",
       "13          0      60.0   0.205167     47.369997       8.519147        1   \n",
       "14          0      32.0   0.703438     47.369997       8.519147        1   \n",
       "15          0      48.0   0.450208     47.369997       8.519147        1   \n",
       "16          0      90.0   0.426889     47.369997       8.519147        1   \n",
       "17          0      48.0   0.751667     47.369997       8.519147        1   \n",
       "18          0      44.0   1.153409     47.369997       8.519147        1   \n",
       "19          0      90.0   0.587333     47.369997       8.519147        1   \n",
       "\n",
       "    Vehicle_type_B  Vehicle_type_FB  Vehicle_type_N  Vehicle_type_SB  \\\n",
       "0            False            False           False            False   \n",
       "1            False            False           False            False   \n",
       "2            False            False           False            False   \n",
       "3            False            False           False            False   \n",
       "4            False            False           False            False   \n",
       "5            False            False           False            False   \n",
       "6            False            False           False            False   \n",
       "7            False            False           False            False   \n",
       "8            False            False           False            False   \n",
       "9            False            False           False            False   \n",
       "10           False            False           False            False   \n",
       "11           False            False           False            False   \n",
       "12           False            False           False            False   \n",
       "13           False            False           False            False   \n",
       "14           False            False           False            False   \n",
       "15           False            False           False            False   \n",
       "16           False            False           False            False   \n",
       "17           False            False           False            False   \n",
       "18           False            False           False            False   \n",
       "19           False            False           False            False   \n",
       "\n",
       "    Vehicle_type_T  Vehicle_type_TR  \n",
       "0            False             True  \n",
       "1            False             True  \n",
       "2            False             True  \n",
       "3            False             True  \n",
       "4            False             True  \n",
       "5            False             True  \n",
       "6            False             True  \n",
       "7            False             True  \n",
       "8            False             True  \n",
       "9            False             True  \n",
       "10           False             True  \n",
       "11           False             True  \n",
       "12           False             True  \n",
       "13           False             True  \n",
       "14           False             True  \n",
       "15           False             True  \n",
       "16           False             True  \n",
       "17           False             True  \n",
       "18           False             True  \n",
       "19           False             True  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#######################################################################\n",
    "# Optional if include these feature variables: Richtung or Vehicle_type\n",
    "# One-hot encoding for categorical values\n",
    "df.info()\n",
    "df = pd.get_dummies(df, columns=['Vehicle_type'])\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ce3d3c18-bb71-43e7-b327-11d511204dd8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 595846 entries, 0 to 595845\n",
      "Data columns (total 13 columns):\n",
      " #   Column           Non-Null Count   Dtype  \n",
      "---  ------           --------------   -----  \n",
      " 0   Time             595846 non-null  float64\n",
      " 1   Nachtnetz        595846 non-null  int64  \n",
      " 2   Capacity         595846 non-null  float64\n",
      " 3   Occupancy        595846 non-null  float64\n",
      " 4   GPS_Latitude     595846 non-null  float64\n",
      " 5   GPS_Longitude    595846 non-null  float64\n",
      " 6   Weekday          595846 non-null  int64  \n",
      " 7   Vehicle_type_B   595846 non-null  bool   \n",
      " 8   Vehicle_type_FB  595846 non-null  bool   \n",
      " 9   Vehicle_type_N   595846 non-null  bool   \n",
      " 10  Vehicle_type_SB  595846 non-null  bool   \n",
      " 11  Vehicle_type_T   595846 non-null  bool   \n",
      " 12  Vehicle_type_TR  595846 non-null  bool   \n",
      "dtypes: bool(6), float64(5), int64(2)\n",
      "memory usage: 35.2 MB\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>Time</th>\n",
       "      <th>Nachtnetz</th>\n",
       "      <th>Capacity</th>\n",
       "      <th>Occupancy</th>\n",
       "      <th>GPS_Latitude</th>\n",
       "      <th>GPS_Longitude</th>\n",
       "      <th>Weekday</th>\n",
       "      <th>Vehicle_type_B</th>\n",
       "      <th>Vehicle_type_FB</th>\n",
       "      <th>Vehicle_type_N</th>\n",
       "      <th>Vehicle_type_SB</th>\n",
       "      <th>Vehicle_type_T</th>\n",
       "      <th>Vehicle_type_TR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.0</td>\n",
       "      <td>0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>0.098542</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.0</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.312500</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8.5</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.264167</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8.5</td>\n",
       "      <td>0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.460000</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.143125</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.141889</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6.5</td>\n",
       "      <td>0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.109000</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.111778</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.178125</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.086111</td>\n",
       "      <td>47.369997</td>\n",
       "      <td>8.519147</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Time  Nachtnetz  Capacity  Occupancy  GPS_Latitude  GPS_Longitude  Weekday  \\\n",
       "0   8.0          0      96.0   0.098542     47.369997       8.519147        0   \n",
       "1   8.0          0      32.0   0.312500     47.369997       8.519147        0   \n",
       "2   8.5          0      48.0   0.264167     47.369997       8.519147        0   \n",
       "3   8.5          0      32.0   0.460000     47.369997       8.519147        0   \n",
       "4   5.5          0      48.0   0.143125     47.369997       8.519147        1   \n",
       "5   5.5          0      90.0   0.141889     47.369997       8.519147        1   \n",
       "6   6.5          0      60.0   0.109000     47.369997       8.519147        1   \n",
       "7   5.5          0      90.0   0.111778     47.369997       8.519147        1   \n",
       "8   6.0          0      48.0   0.178125     47.369997       8.519147        1   \n",
       "9   6.0          0      90.0   0.086111     47.369997       8.519147        1   \n",
       "\n",
       "   Vehicle_type_B  Vehicle_type_FB  Vehicle_type_N  Vehicle_type_SB  \\\n",
       "0           False            False           False            False   \n",
       "1           False            False           False            False   \n",
       "2           False            False           False            False   \n",
       "3           False            False           False            False   \n",
       "4           False            False           False            False   \n",
       "5           False            False           False            False   \n",
       "6           False            False           False            False   \n",
       "7           False            False           False            False   \n",
       "8           False            False           False            False   \n",
       "9           False            False           False            False   \n",
       "\n",
       "   Vehicle_type_T  Vehicle_type_TR  \n",
       "0           False             True  \n",
       "1           False             True  \n",
       "2           False             True  \n",
       "3           False             True  \n",
       "4           False             True  \n",
       "5           False             True  \n",
       "6           False             True  \n",
       "7           False             True  \n",
       "8           False             True  \n",
       "9           False             True  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop more attributes to shrink the data\n",
    "df = df.drop(columns=['Richtung', 'Tage_SA', 'Besetzung', 'Tage_SA', 'Tage_SO', 'Tage_SA_N', 'Tage_SO_N'])\n",
    "df.info()\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "13b3cc3e-6035-46f4-b7a7-9e978315ffd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(os.path.join(datadir, 'cleaned.csv'), index=False)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}