notebooks/preprocessing/preprocessing_one_day.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **🔍 Exploration and Data Preprocessing of a Single Day**\n",
    "\n",
    "In this notebook, we explore and preprocess the data of a single day. We use the data of the 3rd of March 2023 as an example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **💽 Download the Data and Investigate the Columns**"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data is available as a CSV file. We will use the `pandas` library to read the data into a `DataFrame` and keep only the columns that we are interested in. The data contains the following columns:\n",
    "| Column Name | Type | Description |\n",
    "| --- | --- | --- |\n",
    "| `BETRIEBSTAG` | Date (DD.MM.YYYY) | Date of the journey |\n",
    "| `FAHRT_BEZEICHNER` | String | Identifier of the journey (see https://opentransportdata.swiss/en/cookbook/fahrt-id-journeyref/) |\n",
    "| `BETREIBER_ID` | String | Identifier of the operator |\n",
    "| `BETREIBER_ABK` | String | Abbreviation of the operator |\n",
    "| `BETREIBER_NAME` | String | Name of the operator |\n",
    "| `PRODUKT_ID` | Category | Identifier of the product |\n",
    "| `LINIEN_ID` | String | Identifier of the line |\n",
    "| `LINIEN_TEXT` | String | Name of the line |\n",
    "| `UMLAUF_ID` | String | Identifier of the circuit (see https://opentransportdata.swiss/en/cookbook/umlauf/)|\n",
    "| `VERKEHRSMITTEL_TEXT` | String | Type of transport |\n",
    "| `ZUSATZFAHRT_TF` | Boolean | Whether the journey is an additional one |\n",
    "| `FAELLT_AUS_TF` | Boolean | Whether the journey is cancelled |\n",
    "| `BPUIC` | String | Identifier of the stop (see https://didok.ch/en/glossary-stop-points-location-codes/) |\n",
    "| `HALTESTELLEN_NAME` | String | Name of the stop |\n",
    "| `ANKUNFTSZEIT` | DateTime (DD.MM.YYYY HH:MM:SS) | Arrival time at the stop |\n",
    "| `AN_PROGNOSE` | DateTime (DD.MM.YYYY HH:MM:SS) | Arrival time at the stop (predicted) |\n",
    "| `AN_PROGNOSE_STATUS` | Category | Status of the predicted arrival time |\n",
    "| `ABFAHRTSZEIT` | DateTime (DD.MM.YYYY HH:MM:SS) | Departure time from the stop |\n",
    "| `AB_PROGNOSE` | DateTime (DD.MM.YYYY HH:MM:SS) | Departure time from the stop (predicted) |\n",
    "| `AB_PROGNOSE_STATUS` | Category | Status of the predicted departure time |\n",
    "| `DURCHFAHRT_TF` | Boolean | Whether the stop is a through stop |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# False if you want to download the data from the internet\n",
    "LOCAL_DOWNLOAD = True\n",
    "DATE_FILE = '2023-03-10' # Format YYYY-MM-DD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the data\n",
    "DATA_LINK = f\"https://opentransportdata.swiss/dataset/0edc74a3-ad4d-486e-8657-f8f3b34a0979/resource/9de83058-0365-481d-85fa-a8477709b9fa/download/{DATE_FILE}_istdaten.csv\"\n",
    "DATA_FILE = f\"data/{DATE_FILE}_istdaten.csv\"\n",
    "DATA_PATH = DATA_FILE if LOCAL_DOWNLOAD else DATA_LINK\n",
    "transport_data = pd.read_csv(\n",
    "    DATA_PATH, \n",
    "    sep=';',\n",
    "    parse_dates=['BETRIEBSTAG', 'ANKUNFTSZEIT', 'AN_PROGNOSE', 'ABFAHRTSZEIT', 'AB_PROGNOSE'],\n",
    "    dtype={\n",
    "        'FAHRT_BEZEICHNER': 'string',\n",
    "        'BETRIEBER_ID': 'string',\n",
    "        'BETREIBER_ABK': 'string',\n",
    "        'BETREIBER_NAME': 'string',\n",
    "        'PRODUKT_ID': 'category',\n",
    "        'LINIEN_ID': 'string',\n",
    "        'LINIEN_TEXT': 'string',\n",
    "        'UMLAUF_ID': 'string',\n",
    "        'VERKEHRSMITTEL_TEXT': 'string',\n",
    "        'ZUSATZFAHRT_TF': 'boolean',\n",
    "        'FAELLT_AUS_TF': 'boolean',\n",
    "        'BPUIC': 'int',\n",
    "        'HALTESTELLEN_NAME': 'string',\n",
    "        'AN_PROGNOSE_STATUS': 'category',\n",
    "        'AB_PROGNOSE_STATUS': 'category',\n",
    "        'DURCHFAHRT_TF': 'boolean'\n",
    "    },\n",
    "    dayfirst=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>BETRIEBSTAG</th>\n",
       "      <th>FAHRT_BEZEICHNER</th>\n",
       "      <th>BETREIBER_ID</th>\n",
       "      <th>BETREIBER_ABK</th>\n",
       "      <th>BETREIBER_NAME</th>\n",
       "      <th>PRODUKT_ID</th>\n",
       "      <th>LINIEN_ID</th>\n",
       "      <th>LINIEN_TEXT</th>\n",
       "      <th>UMLAUF_ID</th>\n",
       "      <th>VERKEHRSMITTEL_TEXT</th>\n",
       "      <th>...</th>\n",
       "      <th>FAELLT_AUS_TF</th>\n",
       "      <th>BPUIC</th>\n",
       "      <th>HALTESTELLEN_NAME</th>\n",
       "      <th>ANKUNFTSZEIT</th>\n",
       "      <th>AN_PROGNOSE</th>\n",
       "      <th>AN_PROGNOSE_STATUS</th>\n",
       "      <th>ABFAHRTSZEIT</th>\n",
       "      <th>AB_PROGNOSE</th>\n",
       "      <th>AB_PROGNOSE_STATUS</th>\n",
       "      <th>DURCHFAHRT_TF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17230:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17230</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>2023-03-10 04:59:00</td>\n",
       "      <td>2023-03-10 04:59:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17233:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17233</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-03-10 06:07:00</td>\n",
       "      <td>2023-03-10 06:07:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17234:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17234</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>2023-03-10 05:56:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>UNBEKANNT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>UNBEKANNT</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17235:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17235</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-03-10 06:43:00</td>\n",
       "      <td>2023-03-10 06:43:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17236:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17236</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>2023-03-10 06:33:00</td>\n",
       "      <td>2023-03-10 06:37:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  BETRIEBSTAG     FAHRT_BEZEICHNER BETREIBER_ID BETREIBER_ABK  \\\n",
       "0  2023-03-10  80:800631:17230:000    80:800631            DB   \n",
       "1  2023-03-10  80:800631:17233:000    80:800631            DB   \n",
       "2  2023-03-10  80:800631:17234:000    80:800631            DB   \n",
       "3  2023-03-10  80:800631:17235:000    80:800631            DB   \n",
       "4  2023-03-10  80:800631:17236:000    80:800631            DB   \n",
       "\n",
       "                  BETREIBER_NAME PRODUKT_ID LINIEN_ID LINIEN_TEXT UMLAUF_ID  \\\n",
       "0  DB Regio AG Baden-Württemberg        Zug     17230          RB      <NA>   \n",
       "1  DB Regio AG Baden-Württemberg        Zug     17233          RB      <NA>   \n",
       "2  DB Regio AG Baden-Württemberg        Zug     17234          RB      <NA>   \n",
       "3  DB Regio AG Baden-Württemberg        Zug     17235          RB      <NA>   \n",
       "4  DB Regio AG Baden-Württemberg        Zug     17236          RB      <NA>   \n",
       "\n",
       "  VERKEHRSMITTEL_TEXT  ...  FAELLT_AUS_TF    BPUIC  HALTESTELLEN_NAME  \\\n",
       "0                  RB  ...          False  8500090       Basel Bad Bf   \n",
       "1                  RB  ...          False  8500090       Basel Bad Bf   \n",
       "2                  RB  ...           True  8500090       Basel Bad Bf   \n",
       "3                  RB  ...          False  8500090       Basel Bad Bf   \n",
       "4                  RB  ...          False  8500090       Basel Bad Bf   \n",
       "\n",
       "         ANKUNFTSZEIT         AN_PROGNOSE AN_PROGNOSE_STATUS  \\\n",
       "0 2023-03-10 04:59:00 2023-03-10 04:59:00           PROGNOSE   \n",
       "1                 NaT                 NaT                NaN   \n",
       "2 2023-03-10 05:56:00                 NaT          UNBEKANNT   \n",
       "3                 NaT                 NaT                NaN   \n",
       "4 2023-03-10 06:33:00 2023-03-10 06:37:00           PROGNOSE   \n",
       "\n",
       "         ABFAHRTSZEIT         AB_PROGNOSE AB_PROGNOSE_STATUS DURCHFAHRT_TF  \n",
       "0                 NaT                 NaT                NaN         False  \n",
       "1 2023-03-10 06:07:00 2023-03-10 06:07:00           PROGNOSE         False  \n",
       "2                 NaT                 NaT          UNBEKANNT         False  \n",
       "3 2023-03-10 06:43:00 2023-03-10 06:43:00           PROGNOSE         False  \n",
       "4                 NaT                 NaT                NaN         False  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BETRIEBSTAG            datetime64[ns]\n",
       "FAHRT_BEZEICHNER               string\n",
       "BETREIBER_ID                   object\n",
       "BETREIBER_ABK                  string\n",
       "BETREIBER_NAME                 string\n",
       "PRODUKT_ID                   category\n",
       "LINIEN_ID                      string\n",
       "LINIEN_TEXT                    string\n",
       "UMLAUF_ID                      string\n",
       "VERKEHRSMITTEL_TEXT            string\n",
       "ZUSATZFAHRT_TF                boolean\n",
       "FAELLT_AUS_TF                 boolean\n",
       "BPUIC                           int32\n",
       "HALTESTELLEN_NAME              string\n",
       "ANKUNFTSZEIT           datetime64[ns]\n",
       "AN_PROGNOSE            datetime64[ns]\n",
       "AN_PROGNOSE_STATUS           category\n",
       "ABFAHRTSZEIT           datetime64[ns]\n",
       "AB_PROGNOSE            datetime64[ns]\n",
       "AB_PROGNOSE_STATUS           category\n",
       "DURCHFAHRT_TF                 boolean\n",
       "dtype: object"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data.dtypes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **💬 Translate Column Names**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>date</th>\n",
       "      <th>trip_id</th>\n",
       "      <th>operator_id</th>\n",
       "      <th>operator_abbreviation</th>\n",
       "      <th>operator_name</th>\n",
       "      <th>product_id</th>\n",
       "      <th>line_id</th>\n",
       "      <th>line_text</th>\n",
       "      <th>circuit_id</th>\n",
       "      <th>transport_type</th>\n",
       "      <th>...</th>\n",
       "      <th>is_cancelled</th>\n",
       "      <th>stop_id</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>arrival_forecast</th>\n",
       "      <th>arrival_forecast_status</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>departure_forecast</th>\n",
       "      <th>departure_forecast_status</th>\n",
       "      <th>is_through_trip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17230:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17230</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>2023-03-10 04:59:00</td>\n",
       "      <td>2023-03-10 04:59:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17233:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17233</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-03-10 06:07:00</td>\n",
       "      <td>2023-03-10 06:07:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17234:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17234</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>2023-03-10 05:56:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>UNBEKANNT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>UNBEKANNT</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17235:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17235</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-03-10 06:43:00</td>\n",
       "      <td>2023-03-10 06:43:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-03-10</td>\n",
       "      <td>80:800631:17236:000</td>\n",
       "      <td>80:800631</td>\n",
       "      <td>DB</td>\n",
       "      <td>DB Regio AG Baden-Württemberg</td>\n",
       "      <td>Zug</td>\n",
       "      <td>17236</td>\n",
       "      <td>RB</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>RB</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>8500090</td>\n",
       "      <td>Basel Bad Bf</td>\n",
       "      <td>2023-03-10 06:33:00</td>\n",
       "      <td>2023-03-10 06:37:00</td>\n",
       "      <td>PROGNOSE</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date              trip_id operator_id operator_abbreviation  \\\n",
       "0 2023-03-10  80:800631:17230:000   80:800631                    DB   \n",
       "1 2023-03-10  80:800631:17233:000   80:800631                    DB   \n",
       "2 2023-03-10  80:800631:17234:000   80:800631                    DB   \n",
       "3 2023-03-10  80:800631:17235:000   80:800631                    DB   \n",
       "4 2023-03-10  80:800631:17236:000   80:800631                    DB   \n",
       "\n",
       "                   operator_name product_id line_id line_text circuit_id  \\\n",
       "0  DB Regio AG Baden-Württemberg        Zug   17230        RB       <NA>   \n",
       "1  DB Regio AG Baden-Württemberg        Zug   17233        RB       <NA>   \n",
       "2  DB Regio AG Baden-Württemberg        Zug   17234        RB       <NA>   \n",
       "3  DB Regio AG Baden-Württemberg        Zug   17235        RB       <NA>   \n",
       "4  DB Regio AG Baden-Württemberg        Zug   17236        RB       <NA>   \n",
       "\n",
       "  transport_type  ...  is_cancelled  stop_id     stop_name  \\\n",
       "0             RB  ...         False  8500090  Basel Bad Bf   \n",
       "1             RB  ...         False  8500090  Basel Bad Bf   \n",
       "2             RB  ...          True  8500090  Basel Bad Bf   \n",
       "3             RB  ...         False  8500090  Basel Bad Bf   \n",
       "4             RB  ...         False  8500090  Basel Bad Bf   \n",
       "\n",
       "         arrival_time    arrival_forecast arrival_forecast_status  \\\n",
       "0 2023-03-10 04:59:00 2023-03-10 04:59:00                PROGNOSE   \n",
       "1                 NaT                 NaT                     NaN   \n",
       "2 2023-03-10 05:56:00                 NaT               UNBEKANNT   \n",
       "3                 NaT                 NaT                     NaN   \n",
       "4 2023-03-10 06:33:00 2023-03-10 06:37:00                PROGNOSE   \n",
       "\n",
       "       departure_time  departure_forecast departure_forecast_status  \\\n",
       "0                 NaT                 NaT                       NaN   \n",
       "1 2023-03-10 06:07:00 2023-03-10 06:07:00                  PROGNOSE   \n",
       "2                 NaT                 NaT                 UNBEKANNT   \n",
       "3 2023-03-10 06:43:00 2023-03-10 06:43:00                  PROGNOSE   \n",
       "4                 NaT                 NaT                       NaN   \n",
       "\n",
       "  is_through_trip  \n",
       "0           False  \n",
       "1           False  \n",
       "2           False  \n",
       "3           False  \n",
       "4           False  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "translations = {\n",
    "    'BETRIEBSTAG': 'date',\n",
    "    'FAHRT_BEZEICHNER': 'trip_id',\n",
    "    'BETREIBER_ID': 'operator_id',\n",
    "    'BETREIBER_ABK': 'operator_abbreviation',\n",
    "    'BETREIBER_NAME': 'operator_name',\n",
    "    'PRODUKT_ID': 'product_id',\n",
    "    'LINIEN_ID': 'line_id',\n",
    "    'LINIEN_TEXT': 'line_text',\n",
    "    'UMLAUF_ID': 'circuit_id',\n",
    "    'VERKEHRSMITTEL_TEXT': 'transport_type',\n",
    "    'ZUSATZFAHRT_TF': 'is_additional_trip',\n",
    "    'FAELLT_AUS_TF': 'is_cancelled',\n",
    "    'BPUIC': 'stop_id',\n",
    "    'HALTESTELLEN_NAME': 'stop_name',\n",
    "    'ANKUNFTSZEIT': 'arrival_time',\n",
    "    'AN_PROGNOSE': 'arrival_forecast',\n",
    "    'AN_PROGNOSE_STATUS': 'arrival_forecast_status',\n",
    "    'ABFAHRTSZEIT': 'departure_time',\n",
    "    'AB_PROGNOSE': 'departure_forecast',\n",
    "    'AB_PROGNOSE_STATUS': 'departure_forecast_status',\n",
    "    'DURCHFAHRT_TF': 'is_through_trip'\n",
    "}\n",
    "\n",
    "transport_data = transport_data.rename(columns=translations)\n",
    "transport_data.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **🧼 Clean Data and Analyse Missing Values**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in the dataset: 2,347,175\n"
     ]
    }
   ],
   "source": [
    "n_rows_before = transport_data.shape[0]\n",
    "print(f\"Number of rows in the dataset: {n_rows_before:,}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of total missing values in the dataset: 2,268,631\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>Number of missing values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_id</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>operator_id</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>operator_abbreviation</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>operator_name</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_id</th>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>line_id</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>line_text</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circuit_id</th>\n",
       "      <td>1209833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transport_type</th>\n",
       "      <td>7493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_additional_trip</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_cancelled</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_id</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stop_name</th>\n",
       "      <td>158734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>arrival_time</th>\n",
       "      <td>139571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>arrival_forecast</th>\n",
       "      <td>167560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>arrival_forecast_status</th>\n",
       "      <td>139195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>departure_time</th>\n",
       "      <td>139226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>departure_forecast</th>\n",
       "      <td>168000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>departure_forecast_status</th>\n",
       "      <td>138886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_through_trip</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Number of missing values\n",
       "date                                              0\n",
       "trip_id                                           0\n",
       "operator_id                                       0\n",
       "operator_abbreviation                             0\n",
       "operator_name                                     0\n",
       "product_id                                      133\n",
       "line_id                                           0\n",
       "line_text                                         0\n",
       "circuit_id                                  1209833\n",
       "transport_type                                 7493\n",
       "is_additional_trip                                0\n",
       "is_cancelled                                      0\n",
       "stop_id                                           0\n",
       "stop_name                                    158734\n",
       "arrival_time                                 139571\n",
       "arrival_forecast                             167560\n",
       "arrival_forecast_status                      139195\n",
       "departure_time                               139226\n",
       "departure_forecast                           168000\n",
       "departure_forecast_status                    138886\n",
       "is_through_trip                                   0"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of missing values per column\n",
    "print(f\"Number of total missing values in the dataset: {transport_data.isna().sum().sum():,}\")\n",
    "pd.DataFrame(transport_data.isna().sum(), columns=['Number of missing values'])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **1️⃣ product_id**\n",
    "\n",
    "The product IDs in the dataset represent various modes of public transportation available in Switzerland:\n",
    "\n",
    "- **Bus**: Refers to bus services that connect different towns, cities, and suburbs, providing an essential mode of public transportation across the country.\n",
    "- **Train**: Refers to trains or railway services, which are a crucial part of the Swiss public transport system, connecting cities, towns, and regions.\n",
    "- **Rack_railway**: Refers to cogwheel trains, a unique type of railway that uses a toothed rack rail to climb steep gradients, often found in mountainous regions of Switzerland.\n",
    "- **Metro**: Refers to urban rapid transit systems that provide high-capacity public transport within cities, such as the Lausanne Metro.\n",
    "- **Boat**: Refers to passenger ferries and boat services on Swiss lakes, connecting towns and tourist attractions along the lakeshores.\n",
    "- **Tram**: Refers to tram or light rail systems found in several Swiss cities, offering an efficient and eco-friendly mode of transportation within urban areas.\n",
    "\n",
    "We decide to do the following:\n",
    "\n",
    "- Delete all rows that have missing values in the `product_id` column.\n",
    "- Translate the product IDs into English.\n",
    "- Merge the `Bus` and `BUS` categories into a new single category `Bus`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 133 rows with missing values in product_id.\n"
     ]
    }
   ],
   "source": [
    "# Remove rows with missing values in product_id\n",
    "print(f\"Removing {transport_data['product_id'].isna().sum():,} rows with missing values in product_id.\")\n",
    "transport_data = transport_data[transport_data['product_id'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge Bus and BUS into Bus\n",
    "transport_data['product_id'] = transport_data['product_id'].str.replace('BUS', 'Bus')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Translate product_id\n",
    "transport_data['product_id'] = transport_data['product_id'].replace({\n",
    "    'Zahnradbahn': 'Rack_railway',\n",
    "    'Schiff': 'Boat',\n",
    "    'Zug': 'Train',\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Train', 'Bus', 'Rack_railway', 'Metro', 'Boat', 'Tram']\n"
     ]
    }
   ],
   "source": [
    "print(transport_data['product_id'].unique().tolist())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **2️⃣ transport_type**\n",
    "\n",
    "Transport types refer to the different modes of transportation available for travel, such as trains, buses, trams, and mountain railways. In Switzerland, various transport types are available for passengers to travel across different regions, from regional and intercity trains to buses and trams. Here is a list of some common transport types and their corresponding abbreviations in Switzerland:\n",
    "\n",
    "- **B** (`Bus`): Local and regional bus services, often integrated with the train network.\n",
    "- **BAT** (`Bootsanlegestelle`): Boat services offering scenic and leisurely journeys between towns and tourist attractions along the lakeshores.\n",
    "- _**BN** (`Bus`): Regional bus services in Switzerland, connecting smaller towns, villages, and specific points of interest._\n",
    "- _**Bus** (`Bus`): Local and regional bus services, often integrated with the train network._\n",
    "- **CAR** (`Car`): Specific bus or shuttle service that connects key points in Luzern.\n",
    "- **CC** (`Cogwheel Train`): Special types of trains designed to operate on steep gradients using a toothed rack rail system.\n",
    "- **EC** (`EuroCity`): International long-distance trains connecting Switzerland with neighboring countries.\n",
    "- **EXB** (`Expressbus`): Express bus services typically offer faster connections between key points within a region by having fewer stops than regular buses.\n",
    "- **EXT** (`Extrazug`): Extra trains or special services added to accommodate increased demand during peak travel periods or special events.\n",
    "- **FLX** (`Flixtrain`): Long-distance train service offering an affordable alternative to the more established train operators like Deutsche Bahn.\n",
    "- **IC** (`InterCity`): Long-distance express trains connecting major Swiss cities.\n",
    "- **ICE** (`InterCity-Express`): High-speed trains operating between Switzerland and Germany\n",
    "- **IR** (`InterRegio`): Regional express trains connecting larger cities and regions in Switzerland.\n",
    "- **IRE** (`InterRegio-Express`):  Trains that connect within a region and are faster than regular regional trains because they make fewer stops.\n",
    "- **M** (`Métro`): Urban rapid transit systems found in larger cities\n",
    "- **NJ** (`Nightjet`): Overnight train services operated by Austrian Federal Railways, connecting various cities in Europe, including Switzerland.\n",
    "- **PE** (`Panorama Express`): Trains offer passengers a unique and scenic experience through the Swiss Alps, focusing on picturesque landscapes and stunning views from the train's panoramic windows.\n",
    "- **R** (`Regio`): Regional trains connecting smaller towns and cities.\n",
    "- **RB** (`RegionalBahn`): Local, regional trains that stop at every station along the route.\n",
    "- **RE** (`RegioExpress`): Faster regional trains with limited stops.\n",
    "- **RJX** (`Railjet Express`): Trains connecting Austria with several neighboring countries, including Switzerland.\n",
    "- **S** (`S-Bahn`): Suburban rail network operating in and around major Swiss cities.\n",
    "- _**SN** (`S-Bahn`): Suburban rail network operating in and around major Swiss cities._\n",
    "- **T** (`Tram`): Urban light rail systems found in several Swiss cities.\n",
    "- **TER** (`Transport Express Régional`): Train service operated by SNCF, the French National Railway Company\n",
    "- **TGV** (`Train à Grande Vitesse`): High-speed trains operating between Switzerland and France.\n",
    "- _**TN** (`Tram`): Urban light rail systems found in several Swiss cities._\n",
    "- **Z** (`Zug`): Train service.\n",
    "\n",
    "We decide to do the following:\n",
    "\n",
    "- Delete all rows that have missing values in the `transport_type` column.\n",
    "- Merge the `B`, `BN` and `Bus` transport types into a new single `B` transport type.\n",
    "- Merge the `T` and `TN` transport types into a new single `T` transport type.\n",
    "- Merge the `S` and `SN` transport types into a new single `S` transport type.\n",
    "- Rename `Zug` to `Z`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 7,493 rows with missing values in transport_type.\n"
     ]
    }
   ],
   "source": [
    "# Remove rows with missing values in product_id\n",
    "print(f\"Removing {transport_data['transport_type'].isna().sum():,} rows with missing values in transport_type.\")\n",
    "transport_data = transport_data[transport_data['transport_type'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge B, BN and Bus into B\n",
    "transport_data['transport_type'] = transport_data['transport_type'].str.replace('BN', 'B')\n",
    "transport_data['transport_type'] = transport_data['transport_type'].str.replace('Bus', 'B')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge T and TN into T\n",
    "transport_data['transport_type'] = transport_data['transport_type'].str.replace('TN', 'T')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge S and SN into S\n",
    "transport_data['transport_type'] = transport_data['transport_type'].str.replace('SN', 'S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename Zug to Z\n",
    "transport_data['transport_type'] = transport_data['transport_type'].str.replace('Zug', 'Z')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['RB', 'IRE', 'RE', 'ICE', 'FLX', 'S', 'B', 'R', 'IC', 'EC', 'IR', 'RJX', 'TER', 'EXT', 'NJ', 'TGV', 'CC', 'M', 'BAT', 'PE', 'T', 'CAR', 'EXB', 'Z']\n"
     ]
    }
   ],
   "source": [
    "print(transport_data['transport_type'].unique().tolist())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **3️⃣ stop_name and new stop coordinates**\n",
    "\n",
    "We aim to fill in the missing `stop_name` values in the `transport_data` dataset by using an external dataset, `bav_data`, obtained from the _Swiss Open Transport Data_ repository [here](https://opentransportdata.swiss/fr/dataset/bav_liste). Moreover, we add latitude and longitude coordinates (CH1903+ / LV95) to the `transport_data` dataset using the `bav_data` dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "LOCAL_DOWNLOAD = True\n",
    "BAV_LINK = \"https://opentransportdata.swiss/dataset/b9d607ba-4ff5-43a6-ac83-293f454df1fd/resource/9fa965a0-b152-42c5-b1d1-839731e8500b/download/bav_list_current_timetable.xlsx\"\n",
    "BAV_FILE = \"data/bav_list_current_timetable.xlsx\"\n",
    "BAV_PATH = BAV_FILE if LOCAL_DOWNLOAD else BAV_LINK\n",
    "bav_data = pd.read_excel(BAV_PATH, skiprows=[0, 1, 3], usecols=[0, 4, 23, 24])\n",
    "bav_data = bav_data.rename(columns= {\n",
    "    'Dienststellen-\\nNummer siebenstellig': 'stop_id',\n",
    "    'Name \\n(Dst-Bezeichnung)': 'stop_name',\n",
    "    'E-Koordinate': 'stop_lon',\n",
    "    'N-Koordinate': 'stop_lat'\n",
    "})\n",
    "\n",
    "# delete rows with missing values\n",
    "bav_data = bav_data[bav_data['stop_id'].notna()]\n",
    "\n",
    "# Convert columns to correct data types\n",
    "bav_data['stop_id'] = bav_data['stop_id'].astype('int')\n",
    "bav_data['stop_name'] = bav_data['stop_name'].astype('string')\n",
    "bav_data['stop_lon'] = bav_data['stop_lon'].astype('int')\n",
    "bav_data['stop_lat'] = bav_data['stop_lat'].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28540"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bav_data.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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>stop_id</th>\n",
       "      <th>stop_name</th>\n",
       "      <th>stop_lon</th>\n",
       "      <th>stop_lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8506013</td>\n",
       "      <td>Aadorf</td>\n",
       "      <td>2710378</td>\n",
       "      <td>1260736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8573363</td>\n",
       "      <td>Aadorf, Bahnhof</td>\n",
       "      <td>2710335</td>\n",
       "      <td>1260772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8576958</td>\n",
       "      <td>Aadorf, Matthofstrasse</td>\n",
       "      <td>2710483</td>\n",
       "      <td>1260410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8506853</td>\n",
       "      <td>Aadorf, Morgental</td>\n",
       "      <td>2709830</td>\n",
       "      <td>1261377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8573362</td>\n",
       "      <td>Aadorf, Zentrum</td>\n",
       "      <td>2710079</td>\n",
       "      <td>1261059</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   stop_id               stop_name  stop_lon  stop_lat\n",
       "0  8506013                  Aadorf   2710378   1260736\n",
       "1  8573363         Aadorf, Bahnhof   2710335   1260772\n",
       "2  8576958  Aadorf, Matthofstrasse   2710483   1260410\n",
       "3  8506853       Aadorf, Morgental   2709830   1261377\n",
       "4  8573362         Aadorf, Zentrum   2710079   1261059"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bav_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows with missing values in stop_name: 151,241\n"
     ]
    }
   ],
   "source": [
    "print(f\"Number of rows with missing values in stop_name: {transport_data['stop_name'].isna().sum():,}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 316 rows with missing values in stop_lon.\n",
      "Removing 0 rows with missing values in stop_lat.\n"
     ]
    }
   ],
   "source": [
    "# Merge transport_data and bav_data\n",
    "transport_data = transport_data.merge(bav_data, left_on='stop_id', right_on='stop_id', how='left')\n",
    "\n",
    "# Get name from BAV if missing in transport_data\n",
    "transport_data['stop_name'] = transport_data['stop_name_x'].fillna(transport_data['stop_name_y'])\n",
    "transport_data = transport_data.drop(columns=['stop_name_x', 'stop_name_y'])\n",
    "\n",
    "# Delete rows where coordinates of stop are missing\n",
    "print(f\"Removing {transport_data['stop_lon'].isna().sum():,} rows with missing values in stop_lon.\")\n",
    "transport_data = transport_data[transport_data['stop_lon'].notna()]\n",
    "print(f\"Removing {transport_data['stop_lat'].isna().sum():,} rows with missing values in stop_lat.\")\n",
    "transport_data = transport_data[transport_data['stop_lat'].notna()]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **4️⃣ arrival_time, arrival_forecast and arrival_forecast_status**\n",
    "\n",
    "The field `arrival_time` described the theoretical arrival time of transportation at a stop. The field `arrival_forecast` describes the actual arrival time of transportation at a stop. The field `arrival_forecase_status` is an enumeration type field that describes the status of a transportation stop's arrival time. The possible values for this field are:\n",
    "\n",
    "- **ESTIMATED**: The actual arrival time has been estimated after the stop has been passed.\n",
    "- **FORECAST**: This stop is a forecast and has not yet been confirmed.\n",
    "- **REAL**: The effective actual arrival time has been confirmed.\n",
    "- ~~**UNKNOWN**: The status of the arrival time is unknown.~~\n",
    "\n",
    "We decide to do the following:\n",
    "\n",
    "- Translate the `arrival_forecast_status` values into English.\n",
    "- Delete all rows that have missing values in both `arrival_time` and `departure_time`. This should never happen, but for the sake of completeness, we delete these rows.\n",
    "- Remove rows that have the same data on all columns except `arrival_forecast`, `arrival_forecast_status`, `departure_forecast` and `departure_forecast_status`.\n",
    "- Delete all rows that have `UNKNOWN` values in the `arrival_forecast_status` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['PROGNOSE', nan, 'UNBEKANNT', 'REAL', 'GESCHAETZT']"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data['arrival_forecast_status'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "transport_data['arrival_forecast_status'] = transport_data['arrival_forecast_status'].replace({\n",
    "    'PROGNOSE': 'FORECAST',\n",
    "    'UNBEKANNT': 'UNKNOWN',\n",
    "    'REAL': 'REAL',\n",
    "    'GESCHAETZT': 'ESTIMATED',\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['FORECAST', nan, 'UNKNOWN', 'REAL', 'ESTIMATED']"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data['arrival_forecast_status'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 0 rows where both arrival_time and departure_time are missing.\n"
     ]
    }
   ],
   "source": [
    "# Delete rows where both arrival_time and departure_time are missing, for completeness\n",
    "print(f\"Removing {transport_data[(transport_data['arrival_time'].isna()) & (transport_data['departure_time'].isna())].shape[0]:,} rows where both arrival_time and departure_time are missing.\")\n",
    "transport_data = transport_data[(transport_data['arrival_time'].notna()) | (transport_data['departure_time'].notna())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 630 duplicate rows.\n"
     ]
    }
   ],
   "source": [
    "# Delete duplicates when not taking into account arrival_forecast, arrival_forecast_status, departure_forecast, departure_forecast_status\n",
    "columns_to_compare = transport_data.columns.difference(['arrival_forecast', 'arrival_forecast_status', 'departure_forecast', 'departure_forecast_status'])\n",
    "duplicates = transport_data.duplicated(subset=columns_to_compare, keep='first')\n",
    "print(f\"Removing {duplicates.sum():,} duplicate rows.\")\n",
    "transport_data = transport_data[~duplicates]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 28,510 rows with arrival_forecast_status == UNKNOWN.\n"
     ]
    }
   ],
   "source": [
    "print(f\"Removing {transport_data[transport_data['arrival_forecast_status'] == 'UNKNOWN'].shape[0]:,} rows with arrival_forecast_status == UNKNOWN.\")\n",
    "transport_data = transport_data[transport_data['arrival_forecast_status'] != 'UNKNOWN']\n",
    "transport_data['arrival_forecast_status'] = transport_data['arrival_forecast_status'].cat.remove_unused_categories()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 167 rows where the arrival_forecast is missing, but the arrival_forecast_status and arrival_time are not.\n"
     ]
    }
   ],
   "source": [
    "# Remove rows where the arrival_forecast is missing, but the arrival_forecast_status and arrival_time are not\n",
    "print(f\"Removing {transport_data[(transport_data['arrival_time'].notna()) & (transport_data['arrival_forecast'].isna()) & (transport_data['arrival_forecast_status'].notna())].shape[0]:,} rows where the arrival_forecast is missing, but the arrival_forecast_status and arrival_time are not.\")\n",
    "transport_data = transport_data[(transport_data['arrival_time'].isna()) | (transport_data['arrival_forecast'].notna()) | (transport_data['arrival_forecast_status'].isna())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['FORECAST', nan, 'REAL', 'ESTIMATED']"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data['arrival_forecast_status'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAmkAAAG7CAYAAACYbwJrAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjcuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/bCgiHAAAACXBIWXMAAA9hAAAPYQGoP6dpAABU3klEQVR4nO3dd1QU198G8Gdpi4CiKIgIgl1QiggWBLGholhijSW2aEw0wa5YsQZ7x5LEqMESk6hEjR0xYomVIgK2iBULig2Vtvf9wx/zumJhFbKj+3zO4Rz3TvvO7iw83jtFIYQQICIiIiJZ0dN2AURERESUF0MaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaadXq1auhUChgbGyMK1eu5JnesGFD1KhRQwuVAQcOHIBCocAff/yhle1rKjk5Ga1atYKFhQUUCgWGDBmi7ZJIAw4ODujdu3ehbkOhUGDSpEnvnE/XjqWbN29i0qRJiImJKbB17tixI1/v9dusX78eCxYsKJB66ONkoO0CiAAgIyMD48ePR1hYmLZL+WgNHToUx44dw88//wxra2uUKVNG2yWRBrZs2YJixYppuwwAuncs3bx5E5MnT4aDgwPc3NwKZJ07duxAaGjoBwW19evXIz4+/pMPyfRmDGkkCy1atMD69esxYsQIuLq6aruc/9SzZ89gbGwMhULxQeuJj49H7dq10a5dO42XFULg+fPnKFKkyAfV8LF79uxZobwHT58+hYmJyVu3WbNmzQLf7vv6kGPpdXJycpCdnQ2lUlkg6yPSFRzuJFkYNWoUSpYsidGjR791vuTkZCgUCqxevTrPtFeHciZNmgSFQoG4uDh06tQJ5ubmsLCwwLBhw5CdnY1z586hRYsWKFq0KBwcHDBr1qzXbvP58+cYNmwYrK2tUaRIEfj6+iI6OjrPfCdPnkSbNm1gYWEBY2Nj1KxZE7/99pvaPLnDu3v27EHfvn1haWkJExMTZGRkvHGfr169ih49esDKygpKpRKOjo6YO3cuVCoVgP8flr148SJ27twJhUIBhUKB5OTkN65ToVDg22+/xfLly+Ho6AilUok1a9YAAA4dOoQmTZqgaNGiMDExgZeXF/766y9p2UePHsHAwACzZ8+W2lJTU6Gnpwdzc3NkZ2dL7YGBgbC0tIQQAgAQHR2NgIAAaV9sbGzQqlUrXL9+/Y21Av8/7B0VFYW6deuiSJEiKFu2LCZMmICcnBy1eTMzMzFt2jRUq1YNSqUSlpaW6NOnD+7evas2n4ODAwICArB582bUrFkTxsbGmDx58htr2Lt3L9q2bQtbW1sYGxujUqVKGDBgAFJTU9Xmyz3uTp8+jY4dO6JEiRKoWLHiO7f58nDn3bt3YWRkhAkTJuSpIykpCQqFAosWLZLmHThwIJycnGBmZgYrKys0btwYUVFRb31PX+ddx9K7jkXg/7+js2bNwrRp01C+fHkolUpERkYCyN/3BABu3LiBr776CnZ2djAyMoKNjQ06duyI27dvA3jxvRw+fDjc3Nyk73a9evXw559/5lnX77//jjp16sDc3BwmJiaoUKEC+vbtK+2zp6cnAKBPnz7SPr+tB+zp06cYMWIEypcvD2NjY1hYWMDDwwMbNmwAAPTu3RuhoaEAIK3v5fcxNDQUDRo0gJWVFUxNTeHs7IxZs2YhKytL2kbDhg3x119/4cqVK2rrePlzOnDggFpdr/v9+O+//+Lzzz+HjY0NlEolSpcujSZNmhTo0C4VHvakkSwULVoU48ePx+DBg7F//340bty4wNbduXNn9OjRAwMGDMDevXulX4b79u3DwIEDMWLECKxfvx6jR49GpUqV0L59e7Xlx44dC3d3d/z00094+PAhJk2ahIYNGyI6OhoVKlQAAERGRqJFixaoU6cOli9fDnNzc/z666/o0qULnj59mudco759+6JVq1YICwtDeno6DA0NX1v73bt34eXlhczMTEydOhUODg7Yvn07RowYgUuXLmHp0qVwd3fH0aNH8dlnn6FixYqYM2cOALxziCo8PBxRUVGYOHEirK2tYWVlhb///ht+fn5wcXHBypUroVQqsXTpUrRu3RobNmxAly5dUKxYMXh6emLfvn0YOXIkACAiIgJKpRKPHz/G8ePH4eXlBQDYt28fGjduDIVCgfT0dPj5+aF8+fIIDQ1F6dKlcevWLURGRuLx48fv/Bxv3bqFzz//HEFBQZgyZQr++usvTJs2DWlpaViyZAkAQKVSoW3btoiKisKoUaPg5eWFK1euIDg4GA0bNsTJkyfVespOnz6NxMREjB8/HuXLl4epqekbt3/p0iXUq1cP/fr1g7m5OZKTkzFv3jx4e3vjzJkzeT7D9u3b4/PPP8fXX3+N9PR0jbZpaWmJgIAArFmzBpMnT4ae3v//f3rVqlUwMjJC9+7dAQD3798HAAQHB8Pa2hpPnjzBli1b0LBhQ0RERKBhw4bvfG9zve1Yys+x+LJFixahSpUqmDNnDooVK4bKlSvn+3ty48YNeHp6IisrC2PHjoWLiwvu3buH3bt3Iy0tDaVLl0ZGRgbu37+PESNGoGzZssjMzMS+ffvQvn17rFq1Cj179gQAHD16FF26dEGXLl0wadIk6fzX/fv3S/u8atUq9OnTB+PHj0erVq0AALa2tm98n4YNG4awsDBMmzYNNWvWRHp6OuLj43Hv3j0AwIQJE5Ceno4//vgDR48elZbL/U5eunQJ3bp1Q/ny5WFkZITY2FhMnz4dSUlJ+PnnnwEAS5cuxVdffYVLly5hy5Yt+f4MX9WyZUvk5ORg1qxZKFeuHFJTU3HkyBE8ePDgvddJ/yFBpEWrVq0SAMSJEydERkaGqFChgvDw8BAqlUoIIYSvr6+oXr26NP/ly5cFALFq1ao86wIggoODpdfBwcECgJg7d67afG5ubgKA2Lx5s9SWlZUlLC0tRfv27aW2yMhIAUC4u7tL9QghRHJysjA0NBT9+vWT2qpVqyZq1qwpsrKy1LYVEBAgypQpI3JyctT2t2fPnvl6f4KCggQAcezYMbX2b775RigUCnHu3Dmpzd7eXrRq1Spf6wUgzM3Nxf3799Xa69atK6ysrMTjx4+ltuzsbFGjRg1ha2srvQ/jx48XRYoUEc+fPxdCCNGvXz/RokUL4eLiIiZPniyEEOLGjRsCgPjhhx+EEEKcPHlSABDh4eH5qvFlvr6+AoD4888/1dr79+8v9PT0xJUrV4QQQmzYsEEAEJs2bVKb78SJEwKAWLp0qdRmb28v9PX11d7D/FKpVCIrK0tcuXIlT125x93EiRPzLPe2bdrb24tevXpJr7du3SoAiD179kht2dnZwsbGRnTo0OGNtWVnZ4usrCzRpEkT8dlnn6lNe/U78iavO5byeyzmfkcrVqwoMjMz1ebN7/ekb9++wtDQUCQkJLyz1ly5+/3ll1+KmjVrSu1z5swRAMSDBw/euGzu8fG63yuvU6NGDdGuXbu3zjNo0CCRnz+xOTk5IisrS/zyyy9CX19f7TvZqlUrYW9vn2eZ3N9NkZGRau2v/n5MTU0VAMSCBQveWQfJE4c7STaMjIwwbdo0nDx58rXDH+8rICBA7bWjoyMUCgX8/f2lNgMDA1SqVOm1V5h269ZN7Xwxe3t7eHl5ScM3Fy9eRFJSktSzkZ2dLf20bNkSKSkpOHfunNo6O3TokK/a9+/fDycnJ9SuXVutvXfv3hBCSL0B76Nx48YoUaKE9Do9PR3Hjh1Dx44dYWZmJrXr6+vjiy++wPXr16X9aNKkCZ49e4YjR44AeNFj5ufnh6ZNm2Lv3r1SGwA0bdoUAFCpUiWUKFECo0ePxvLly5GQkKBRvUWLFkWbNm3U2rp16waVSoWDBw8CALZv347ixYujdevWap+Dm5sbrK2t8wwPubi4oEqVKvna/p07d/D111/Dzs4OBgYGMDQ0hL29PQAgMTExz/xv+ozzu01/f39YW1tj1apVUtvu3btx8+ZNaagu1/Lly+Hu7g5jY2OptoiIiNfW9b40PRbbtGmj1ruoyfdk586daNSoERwdHd9a0++//4769evDzMxM2u+VK1eq7XfuUGbnzp3x22+/4caNG+//JvxP7dq1sXPnTgQFBeHAgQN49uyZRstHR0ejTZs2KFmyJPT19WFoaIiePXsiJycH58+f/+D6cllYWKBixYqYPXs25s2bh+joaLWhaZI/hjSSlc8//xzu7u4YN26c2vkZH8LCwkLttZGREUxMTGBsbJyn/fnz53mWt7a2fm1b7tBG7jkyI0aMgKGhodrPwIEDASDPeUv5vVru3r17r53XxsZGmv6+Xl1vWloahBD52p6XlxdMTEywb98+XLx4EcnJyVJIO3bsGJ48eYJ9+/ahQoUKKF++PADA3Nwcf//9N9zc3DB27FhUr14dNjY2CA4OztdnXbp06TxtuZ/Ny5/FgwcPYGRklOezuHXr1nt/DiqVCs2aNcPmzZsxatQoRERE4Pjx4/jnn38A4LV/pN+07vxu08DAAF988QW2bNkiDU2tXr0aZcqUQfPmzaX55s2bh2+++QZ16tTBpk2b8M8//+DEiRNo0aKFxuHhbTQ9Fl+dV5Pvyd27d9863AgAmzdvRufOnVG2bFmsXbsWR48exYkTJ9C3b1+173GDBg0QHh6O7Oxs9OzZE7a2tqhRo4Z0/tj7WLRoEUaPHo3w8HA0atQIFhYWaNeuHS5cuPDOZa9evQofHx/cuHEDCxcuRFRUFE6cOCGdw1aQn5lCoUBERASaN2+OWbNmwd3dHZaWlggMDMzXKQakfTwnjWRFoVBg5syZ8PPzww8//JBnem6wevVE+w8JK+9y69at17aVLFkSAFCqVCkAwJgxY/Kcz5aratWqaq/zeyVnyZIlkZKSkqf95s2batt+H6/WUKJECejp6eVre0ZGRvD29sa+fftga2sLa2trODs7S+foHThwABEREXl6MZ2dnfHrr79CCIG4uDisXr0aU6ZMQZEiRRAUFPTWenP/yL8s97N5+bMoWbIkdu3a9dp1FC1a9K3vwZvEx8cjNjYWq1evRq9evaT2ixcvvnGZN61bk6t4+/Tpg9mzZ0vnbW3duhVDhgyBvr6+NM/atWvRsGFDLFu2TG3Zgv4jrOmx+Op+avI9sbS0fOfFJGvXrkX58uWxceNGtW297iKctm3bom3btsjIyMA///yDkJAQdOvWDQ4ODqhXr95bt/M6pqammDx5MiZPnozbt29LvWqtW7dGUlLSW5cNDw9Heno6Nm/eLPXEAtDoRP43/R589T8hwIue/5UrVwIAzp8/j99++w2TJk1CZmYmli9fnu9tknawJ41kp2nTpvDz88OUKVPw5MkTtWmlS5eGsbEx4uLi1Npfd0VXQdmwYYN0dSIAXLlyBUeOHJFOyK5atSoqV66M2NhYeHh4vPbn1XCQX02aNEFCQgJOnz6t1v7LL79AoVCgUaNG771frzI1NUWdOnWwefNmtf/Nq1QqrF27Fra2tmrDdE2bNsWpU6ewadMmaUjT1NQUdevWxeLFi3Hz5k2p/VUKhQKurq6YP38+ihcvnmf/Xufx48fYunWrWtv69euhp6eHBg0aAHgxtH3v3j3k5OS89nN4NSznV24IePUWEitWrHiv9eWXo6Mj6tSpg1WrVmH9+vXIyMhAnz598tT2al1xcXFqJ6wXhA89FjX5nvj7+yMyMjLPaQIvUygUMDIyUgtot27deuvvAqVSCV9fX8ycORMApKu0c9+/9+nFKl26NHr37o2uXbvi3LlzePr06VvX+bpjSQiBH3/88bX1vq4mBwcHAMjze/DV78erqlSpgvHjx8PZ2Tlf3znSPvakkSzNnDkTtWrVwp07d1C9enWpXaFQoEePHvj5559RsWJFuLq64vjx41i/fn2h1XLnzh189tln6N+/Px4+fIjg4GAYGxtjzJgx0jwrVqyAv78/mjdvjt69e6Ns2bK4f/8+EhMTcfr0afz+++/vte2hQ4fil19+QatWrTBlyhTY29vjr7/+wtKlS/HNN9/k+3yq/AoJCYGfnx8aNWqEESNGwMjICEuXLkV8fDw2bNig9gexSZMmyMnJQUREhHT7DuBFeAsODoZCoVC7Snf79u1YunQp2rVrhwoVKkAIgc2bN+PBgwfw8/N7Z20lS5bEN998g6tXr6JKlSrYsWMHfvzxR3zzzTcoV64cgBfD5evWrUPLli0xePBg1K5dG4aGhrh+/ToiIyPRtm1bfPbZZxq/L9WqVUPFihURFBQEIQQsLCywbds26fy7wtS3b18MGDAAN2/ehJeXV56gGRAQgKlTpyI4OBi+vr44d+4cpkyZgvLly6vdDuVDFcSxmN/vyZQpU7Bz5040aNAAY8eOhbOzMx48eIBdu3Zh2LBhqFatmnQrk4EDB6Jjx464du0apk6dijJlyqgNO06cOBHXr19HkyZNYGtriwcPHmDhwoUwNDSEr68vAKBixYooUqQI1q1bB0dHR5iZmcHGxkYayn1VnTp1EBAQABcXF5QoUQKJiYkICwtDvXr1pPvhOTs7A3jxu8zf3x/6+vpwcXGBn58fjIyM0LVrV4waNQrPnz/HsmXLkJaWlmc7zs7O2Lx5M5YtW4ZatWpBT08PHh4esLa2RtOmTRESEoISJUrA3t4eERER2Lx5s9rycXFx+Pbbb9GpUydUrlwZRkZG2L9/P+Li4t7Zc00yob1rFojUr+58Vbdu3QQAtas7hRDi4cOHol+/fqJ06dLC1NRUtG7dWiQnJ7/x6s67d++qLd+rVy9hamqaZ3uvXkmaewVVWFiYCAwMFJaWlkKpVAofHx9x8uTJPMvHxsaKzp07CysrK2FoaCisra1F48aNxfLly/O1v29y5coV0a1bN1GyZElhaGgoqlatKmbPni1dCZdL06s7Bw0a9NppUVFRonHjxsLU1FQUKVJE1K1bV2zbti3PfCqVSpQqVUoAEDdu3JDaDx8+LF0V+7KkpCTRtWtXUbFiRVGkSBFhbm4uateuLVavXv3OenM/mwMHDggPDw+hVCpFmTJlxNixY/NcKZiVlSXmzJkjXF1dhbGxsTAzMxPVqlUTAwYMEBcuXJDm0+T9EkKIhIQE4efnJ4oWLSpKlCghOnXqJK5evZrv4+5d23z16s5cDx8+FEWKFBEAxI8//phnekZGhhgxYoQoW7asMDY2Fu7u7iI8PFz06tUrz5WBr9b6Jm+qMz/HYu4VhrNnz37tuvPzPRFCiGvXrom+ffsKa2trYWhoKGxsbETnzp3F7du3pXlmzJghHBwchFKpFI6OjuLHH3+U3v9c27dvF/7+/qJs2bLCyMhIWFlZiZYtW4qoqCi17W3YsEFUq1ZNGBoavvN9CgoKEh4eHqJEiRJCqVSKChUqiKFDh4rU1FRpnoyMDNGvXz9haWkpFAqFACAuX74shBBi27Zt0vFZtmxZMXLkSLFz5848V2zev39fdOzYURQvXlxaR66UlBTRsWNHYWFhIczNzUWPHj2kK6hzr+68ffu26N27t6hWrZowNTUVZmZmwsXFRcyfP19kZ2e/cf9IPhRCvDSOQ0QkQw0bNkRqairi4+O1XQoR0X+G56QRERERyRBDGhEREZEMcbiTiIiISIbYk0ZEREQkQwxpRERERDLEkEZEREQkQzp/M1uVSoWbN2+iaNGiGj2uhYiIiEhTQgg8fvwYNjY20NN7e1+Zzoe0mzdvws7OTttlEBERkQ65du0abG1t3zqPzoa00NBQhIaGSo9NuXbtGooVK6blqoiIiOhT9ujRI9jZ2eXrmc46fwuOR48ewdzcHA8fPmRIIyIiokKlSe7ghQNEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMqSzD1j/2DkE/aXtEnRC8oxW2i6BiIh0FHvSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGTokwhply9fRqNGjeDk5ARnZ2ekp6druyQiIiKiD2Kg7QIKQu/evTFt2jT4+Pjg/v37UCqV2i6JiIiI6IN89CHt7NmzMDQ0hI+PDwDAwsJCyxURERERfTitD3cePHgQrVu3ho2NDRQKBcLDw/PMs3TpUpQvXx7GxsaoVasWoqKipGkXLlyAmZkZ2rRpA3d3d3z//ff/YfVEREREhUPrIS09PR2urq5YsmTJa6dv3LgRQ4YMwbhx4xAdHQ0fHx/4+/vj6tWrAICsrCxERUUhNDQUR48exd69e7F3797/cheIiIiICpzWQ5q/vz+mTZuG9u3bv3b6vHnz8OWXX6Jfv35wdHTEggULYGdnh2XLlgEAbG1t4enpCTs7OyiVSrRs2RIxMTFv3F5GRgYePXqk9kNEREQkN1oPaW+TmZmJU6dOoVmzZmrtzZo1w5EjRwAAnp6euH37NtLS0qBSqXDw4EE4Ojq+cZ0hISEwNzeXfuzs7Ap1H4iIiIjeh6xDWmpqKnJyclC6dGm19tKlS+PWrVsAAAMDA3z//fdo0KABXFxcULlyZQQEBLxxnWPGjMHDhw+ln2vXrhXqPhARERG9j4/i6k6FQqH2Wgih1ubv7w9/f/98rUupVPIWHURERCR7su5JK1WqFPT19aVes1x37tzJ07tGRERE9CmRdUgzMjJCrVq18lytuXfvXnh5eWmpKiIiIqLCp/XhzidPnuDixYvS68uXLyMmJgYWFhYoV64chg0bhi+++AIeHh6oV68efvjhB1y9ehVff/21FqsmIiIiKlxaD2knT55Eo0aNpNfDhg0DAPTq1QurV69Gly5dcO/ePUyZMgUpKSmoUaMGduzYAXt7e22VTERERFTotB7SGjZsCCHEW+cZOHAgBg4cWKDbDQ0NRWhoKHJycgp0vUREREQFQdbnpBWmQYMGISEhASdOnNB2KURERER56GxIIyIiIpIzhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGdLZkBYaGgonJyd4enpquxQiIiKiPHQ2pPE+aURERCRnOhvSiIiIiOSMIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGRIZ0Mab2ZLREREcqazIY03syUiIiI509mQRkRERCRnDGlEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMqSzIY1PHCAiIiI509mQxicOEBERkZzpbEgjIiIikjOGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiEDbRegLaGhoQgNDUVOTo62SyEiAA5Bf2m7BJ2QPKOVtksgonzS2Z40PmCdiIiI5ExnQxoRERGRnDGkEREREckQQxoRERGRDDGkEREREcmQxiFt165dOHTokPQ6NDQUbm5u6NatG9LS0gq0OCIiIiJdpXFIGzlyJB49egQAOHPmDIYPH46WLVvi33//xbBhwwq8QCIiIiJdpPF90i5fvgwnJycAwKZNmxAQEIDvv/8ep0+fRsuWLQu8QCIiIiJdpHFPmpGREZ4+fQoA2LdvH5o1awYAsLCwkHrYiIiIiOjDaNyT5u3tjWHDhqF+/fo4fvw4Nm7cCAA4f/48bG1tC7xAIiIiIl2kcU/akiVLYGBggD/++APLli1D2bJlAQA7d+5EixYtCrxAIiIiIl2kcU9auXLlsH379jzt8+fPL5CCiIiIiOg9etK6d++OH3/8ERcuXCiMeoiIiIgI7xHSzMzMMHfuXFStWhU2Njbo2rUrli9fjqSkpMKoj4iIiEgnaRzSVqxYgaSkJNy8eRPz5s2Dubk5Fi5ciOrVq6NMmTKFUWOhCA0NhZOTEzw9PbVdChEREVEe7/1YqKJFi6JEiRIoUaIEihcvDgMDA1hbWxdkbYVq0KBBSEhIwIkTJ7RdChEREVEeGoe00aNHo27duihVqhTGjx+PzMxMjBkzBrdv30Z0dHRh1EhERESkczS+unP27NmwtLREcHAw2rZtC0dHx8Koi4iIiEinaRzSoqOj8ffff+PAgQOYO3cu9PX14evri4YNG6Jhw4YMbUREREQFQOOQ5urqCldXVwQGBgIAYmNjsWDBAgQGBkKlUiEnJ6fAiyQiIiLSNRqHNOBFb9qBAwdw4MABREVF4dGjR3Bzc0OjRo0Kuj4iIiIinaRxSCtRogSePHkCV1dXNGzYEP3790eDBg1QrFixwqiPiIiISCdpHNLCwsIYyoiIiIgKmcYhLSAgQPr39evXoVAopIesExEREVHB0Pg+aSqVClOmTIG5uTns7e1Rrlw5FC9eHFOnToVKpSqMGomIiIh0jsY9aePGjcPKlSsxY8YM1K9fH0IIHD58GJMmTcLz588xffr0wqiTiIiISKdoHNLWrFmDn376CW3atJHaXF1dUbZsWQwcOJAhjYiIiKgAaDzcef/+fVSrVi1Pe7Vq1XD//v0CKYqIiIhI12kc0lxdXbFkyZI87UuWLIGrq2uBFEVERESk6zQe7pw1axZatWqFffv2oV69elAoFDhy5AiuXbuGHTt2FEaNRERERDpH4540X19fnD9/Hp999hkePHiA+/fvo3379jh37hx8fHwKo0YiIiIinaNRT1pWVhaaNWuGFStW8AIBIiIiokKkUU+aoaEh4uPjoVAoCqseIiIiIsJ7DHf27NkTK1euLIxa/lOhoaFwcnKCp6entkshIiIiykPjCwcyMzPx008/Ye/evfDw8ICpqana9Hnz5hVYcYVp0KBBGDRoEB49egRzc3Ntl0NERESkRuOQFh8fD3d3dwDA+fPn1aZxGJSIiIioYGgc0iIjIwujDiIiIiJ6icbnpBERERFR4WNIIyIiIpIhhjQiIiIiGWJIIyIiIpKhfIU0d3d3pKWlAQCmTJmCp0+fFmpRRERERLouXyEtMTER6enpAIDJkyfjyZMnhVoUERERka7L1y043Nzc0KdPH3h7e0MIgTlz5sDMzOy1806cOLFACyQiIiLSRfkKaatXr0ZwcDC2b98OhUKBnTt3wsAg76IKhYIhjYiIiKgA5CukVa1aFb/++isAQE9PDxEREbCysirUwoiIiIh0mcZPHFCpVIVRBxERERG9ROOQBgCXLl3CggULkJiYCIVCAUdHRwwePBgVK1Ys6PqIiIiIdJLG90nbvXs3nJyccPz4cbi4uKBGjRo4duwYqlevjr179xZGjUREREQ6R+OetKCgIAwdOhQzZszI0z569Gj4+fkVWHFEREREukrjnrTExER8+eWXedr79u2LhISEAimKiIiISNdpHNIsLS0RExOTpz0mJoZXfBIREREVEI2HO/v374+vvvoK//77L7y8vKBQKHDo0CHMnDkTw4cPL4waiYiIiHSOxiFtwoQJKFq0KObOnYsxY8YAAGxsbDBp0iQEBgYWeIFEREREukjjkKZQKDB06FAMHToUjx8/BgAULVq0wAsjIiIi0mXvdZ+0XAxnRERERIVD4wsHiIiIiKjwMaQRERERyRBDGhEREZEMaRTSsrKy0KhRI5w/f76w6iEiIiIiaBjSDA0NER8fD4VCUVj1/GdCQ0Ph5OQET09PbZdCRERElIfGw509e/bEypUrC6OW/9SgQYOQkJCAEydOaLsUIiIiojw0vgVHZmYmfvrpJ+zduxceHh4wNTVVmz5v3rwCK46IiIhIV2kc0uLj4+Hu7g4Aec5N+xSGQYmIiIjkQOOQFhkZWRh1EBEREdFL3vsWHBcvXsTu3bvx7NkzAIAQosCKIiIiItJ1Goe0e/fuoUmTJqhSpQpatmyJlJQUAEC/fv0wfPjwAi+QiIiISBdpHNKGDh0KQ0NDXL16FSYmJlJ7ly5dsGvXrgItjoiIiEhXaXxO2p49e7B7927Y2tqqtVeuXBlXrlwpsMKIiIiIdJnGPWnp6elqPWi5UlNToVQqC6QoIiIiIl2ncUhr0KABfvnlF+m1QqGASqXC7Nmz0ahRowItjoiIiEhXaTzcOXv2bDRs2BAnT55EZmYmRo0ahbNnz+L+/fs4fPhwYdRIREREpHM07klzcnJCXFwcateuDT8/P6Snp6N9+/aIjo5GxYoVC6NGIiIiIp2jcU8aAFhbW2Py5MkFXQsRERER/c97hbS0tDSsXLkSiYmJUCgUcHR0RJ8+fWBhYVHQ9RERERHpJI2HO//++2+UL18eixYtQlpaGu7fv49FixahfPny+PvvvwujRiIiIiKdo3FP2qBBg9C5c2csW7YM+vr6AICcnBwMHDgQgwYNQnx8fIEXSURERKRrNO5Ju3TpEoYPHy4FNADQ19fHsGHDcOnSpQItjoiIiEhXaRzS3N3dkZiYmKc9MTERbm5uBVETERERkc7L13BnXFyc9O/AwEAMHjwYFy9eRN26dQEA//zzD0JDQzFjxozCqZKIiIhIx+QrpLm5uUGhUEAIIbWNGjUqz3zdunVDly5dCq46IiIiIh2Vr5B2+fLlwq6DiIiIiF6Sr5Bmb29f2HUQERER0Uve62a2N27cwOHDh3Hnzh2oVCq1aYGBgQVSGBEREZEu0zikrVq1Cl9//TWMjIxQsmRJKBQKaZpCoWBIIyIiIioAGoe0iRMnYuLEiRgzZgz09DS+gwcRERER5YPGKevp06f4/PPPGdCIiIiICpHGSevLL7/E77//Xhi1EBEREdH/aDzcGRISgoCAAOzatQvOzs4wNDRUmz5v3rwCK46IiIhIV2kc0r7//nvs3r0bVatWBYA8Fw4QERER0YfTOKTNmzcPP//8M3r37l0I5RARERER8B7npCmVStSvX78waiEiIiKi/9E4pA0ePBiLFy8ujFqIiIiI6H80Hu48fvw49u/fj+3bt6N69ep5LhzYvHlzgRVHREREpKs0DmnFixdH+/btC6MWIiIiIvqf93osFBEREREVLj42gIiIiEiGNO5JK1++/Fvvh/bvv/9+UEFERERE9B4hbciQIWqvs7KyEB0djV27dmHkyJEFVRcRERGRTtM4pA0ePPi17aGhoTh58uQHF0REREREBXhOmr+/PzZt2lRQqyMiIiLSaQUW0v744w9YWFgU1Oo0YmBgADc3N7i5uaFfv35aqYGIiIioIGk83FmzZk21CweEELh16xbu3r2LpUuXFmhx+VW8eHHExMRoZdtEREREhUHjkNauXTu113p6erC0tETDhg1RrVq1gqqLiIiISKdpHNKCg4MLtICDBw9i9uzZOHXqFFJSUrBly5Y8QXDp0qWYPXs2UlJSUL16dSxYsAA+Pj7S9EePHqFWrVooUqQIpk+fDl9f3wKtkYiIiOi/pvWb2aanp8PV1RVLlix57fSNGzdiyJAhGDduHKKjo+Hj4wN/f39cvXpVmic5ORmnTp3C8uXL0bNnTzx69Oi/Kp+IiIioUOS7J01PT++tN7EFAIVCgezsbI0K8Pf3h7+//xunz5s3D19++aV0QcCCBQuwe/duLFu2DCEhIQAAGxsbAECNGjXg5OSE8+fPw8PD47Xry8jIQEZGhvSagY6IiIjkKN8hbcuWLW+cduTIESxevBhCiAIpKldmZiZOnTqFoKAgtfZmzZrhyJEjAIC0tDSYmJhAqVTi+vXrSEhIQIUKFd64zpCQEEyePLlA6yQiIiIqaPkOaW3bts3TlpSUhDFjxmDbtm3o3r07pk6dWqDFpaamIicnB6VLl1ZrL126NG7dugUASExMxIABA6SevoULF771ViBjxozBsGHDpNePHj2CnZ1dgdZNRERE9KE0vnAAAG7evIng4GCsWbMGzZs3R0xMDGrUqFHQtUleHWYVQkhtXl5eOHPmTL7XpVQqoVQqC7Q+IiIiooKm0YUDDx8+xOjRo1GpUiWcPXsWERER2LZtW6EFtFKlSkFfX1/qNct1586dPL1rRERERJ+SfIe0WbNmoUKFCti+fTs2bNiAI0eOqN0GozAYGRmhVq1a2Lt3r1r73r174eXlVajbJiIiItKmfA93BgUFoUiRIqhUqRLWrFmDNWvWvHa+zZs3a1TAkydPcPHiRen15cuXERMTAwsLC5QrVw7Dhg3DF198AQ8PD9SrVw8//PADrl69iq+//lqj7RARERF9TPId0nr27PnOW3C8j5MnT6JRo0bS69yT+nv16oXVq1ejS5cuuHfvHqZMmYKUlBTUqFEDO3bsgL29fYHXQkRERCQX+Q5pq1evLpQCGjZs+M5bdwwcOBADBw4s0O2GhoYiNDQUOTk5BbpeIiIiooKg9ScOaMugQYOQkJCAEydOaLsUIiIiojx0NqQRERERyRlDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEM6WxICw0NhZOTEzw9PbVdChEREVEeOhvSeJ80IiIikjOdDWlEREREcsaQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMqSzIY03syUiIiI509mQxpvZEhERkZzpbEgjIiIikjOGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiGGNCIiIiIZ0tmQxicOEBERkZzpbEjjEweIiIhIznQ2pBERERHJGUMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQzpbEjjszuJiIhIznQ2pPHZnURERCRnOhvSiIiIiOSMIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGRIZ0NaaGgonJyc4Onpqe1SiIiIiPLQ2ZA2aNAgJCQk4MSJE9ouhYiIiCgPnQ1pRERERHLGkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQzob0kJDQ+Hk5ARPT09tl0JERESUh86GtEGDBiEhIQEnTpzQdilEREREeehsSCMiIiKSM4Y0IiIiIhliSCMiIiKSIYY0IiIiIhliSCMiIiKSIYY0IiIiIhliSCMiIiKSIYY0IiIiIhky0HYBREREnxqHoL+0XYLOSJ7RStslFBr2pBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQzp/APWhRAAgEePHmm5Es2oMp5quwSd8LEdFx8zHtP/DR7T/w0ez/+dj+2Yzq03N3+8jULkZ65P2PXr12FnZ6ftMoiIiEiHXLt2Dba2tm+dR+dDmkqlws2bN1G0aFEoFAptl/PJevToEezs7HDt2jUUK1ZM2+UQfTAe0/Sp4TH93xBC4PHjx7CxsYGe3tvPOtP54U49Pb13JlkqOMWKFeOXnz4pPKbpU8NjuvCZm5vnaz5eOEBEREQkQwxpRERERDLEkEb/CaVSieDgYCiVSm2XQlQgeEzTp4bHtPzo/IUDRERERHLEnjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyKSiejoaPz+++/aLoOoUPBmEppjSCMikoHY2FjUqlULx48f13YpRIUi9/nYDGv5x/ukkdYJIfhwe9JpZ86cQZ06dTBq1ChMmjRJ2+UQFYh79+4hKSkJUVFRMDExQZMmTVCpUiUolUqoVKp3PlycGNJIi548eQIzMzMADGqku5KSkuDt7Y2mTZvi119/BcDvA338EhMTMXDgQDx79gxXrlzB06dPYWJigh49emD8+PEwNzdnUMsHvjukFSkpKejatSvCwsIAvOgG5/8XSNfExMTA3d0dGRkZiI6OxsGDB5Gdnc2ARh+12NhYeHt7w83NDUuXLsWFCxdw8eJF+Pj4YOPGjRg1ahQeP37MgJYPfIdIK+7cuYPs7GysWrUKv/32GwAGNdItcXFx8PDwwLhx4/D48WNYWlqiV69eOHLkCFQqlbbLI3ovZ8+eRb169TBkyBDMnz8f7u7uMDMzg6WlJdauXYuWLVtiy5Yt0u99ejsOd5LWnD59GjNnzkRKSgq+/fZbdO7cGQDUusCzs7ORmJgIZ2dnbZZKVKCeP3+OLl26wMXFBVOnTpXavb29cePGDaxZswbe3t7saaCPyr1791CvXj2YmZnh6NGjaueevfx73c3NDSVKlEBkZKSWK5Y//gag/1xuL4G7uztGjRqFMmXKYMmSJdL/rPT09CCEQGZmJoYOHYrBgwfj4cOH2iyZqMDcuXMHT548QVhYmBTQsrKyAACHDh1C2bJl0atXLxw6dIg9avRRKVmyJPz9/aFUKhESEoLU1FTp97menh4yMzMBAN9++y0uXbqEa9eucfTkHRjS6D9x/fp1XL9+HU+fPoWenh6ys7MBALVq1VILarn3iFKpVBg+fDhWrFiBuXPnwtzcXJvlExWIhIQEtG3bFj169MD9+/eldkNDQ+k78XJQO3z4MHJycrRVLlG+xMfHY8OGDQCAhQsXwtfXF9u2bcPixYtx7949KBQKqFQqGBkZAQAuXrwIS0tL2NjY8PzLd2BIo0KXkpKCSpUqITAwEN26dUNMTAwyMjKk6bVq1cKIESNQpkwZLF68GBs2bEBQUBBWrlyJY8eOoWbNmlqsnqhgxMfHw8fHB97e3ggKCoKDg4PadAMDA7UeNQcHBwQEBODYsWNaqJYof2JjY+Hh4YF///1XapsxYwaaNm2K7du3S0Etd8gzPT0dN2/eRLNmzQDwnmnvYqDtAujT9+TJExgaGsLZ2Rl6enpo164dfH194e3tjf79+0MIAU9PTwwdOhSLFi3CN998g2fPnuHo0aMMaPRJuH37Nj7//HN89dVXCAkJUZv28rk6hoaGyMzMhJGRESIjI+Hv7w8rKyttlEz0TrGxsfDy8sLw4cMxbtw4tWkzZ87E6NGjsW3bNgghEBgYiJIlSyIkJAQHDx7Evn37oK+vr6XKPx4MaVSohBCoXLkyJk2ahNjYWPzyyy+oW7cukpKSEBwcjN9//x2+vr4YNmwY6tatC3NzcxgaGmL06NFwcnLSdvlEBeLff/+FmZkZBg4cKN0DLTo6GsePH0dYWBjq16+Ppk2bws/PD0ZGRsjKyoKhoSF27typ7dKJXisuLg5eXl4YMmQIpk+fLrVv3LgRZcuWhbe3N2bOnImgoCBs374dJiYmuHPnDpYvX45Dhw6hUqVKWqz+48GrO6lQ5PYO5P5B2r17N8aNG4d169ahatWqAIDWrVsjOjoalpaWuHPnDjp27IjvvvsO5cuX5/+w6JOybds2tGvXDmfOnIGTkxNWrlyJtWvXIjU1FRUrVkRsbCwcHR2xbNky2Nvba7tcore6desW3N3d4erqqvYfie+//x6LFi3Cjh07ULNmTel8s7Fjx+KHH35ARkYG/v77b7i7u2ur9I8Oz0mjApeYmIjly5fj+fPn0pe0efPmsLa2xoQJEwAAvXv3xunTp/Hnn3/in3/+QY8ePRAfHw99fX0GNPrk1K1bF61atUKDBg3g4+ODgQMHwsfHBz/99BPCw8Oxbt067Nq1C2fPntV2qURv9eTJEzx8+BBeXl5ISUlBeHg4gBfnoc2bNw9r1qyBu7s7FAqFdNHL999/j2HDhuHkyZMMaBpiTxoVqNjYWNSsWRNz5szBsGHDAAA5OTnQ19fH33//jblz5yI1NRXJycn4888/4enpKS378mOiiD4FLz/e6dSpU4iMjMS1a9fQp08fuLi4SOeiXbhwAV26dMGiRYvg7e2tzZKJ3ujUqVPw9/fH6dOnkZqaikWLFuHkyZOoXr069u/fj7CwMOmCgFxHjx5FvXr1tFTxx4/npFGBiYmJQf369TFmzBgpoAGQ/hBVr14dt27dwpkzZ3D+/HnY2dkB+P+hUQY0+hQkJSVh37596NOnDwwNDaXbDtSqVQu1atV67TJr1qyBSqVC5cqV/8tSifItNjYWjRo1Qp8+fWBrawtbW1t89913WLx4MTZu3Ijhw4ejWbNm0tWaCoUC48aNQ0hICG7fvo1SpUrxdhvvQxAVgDNnzggTExMRHBys1v7LL7+I48ePi5ycHCGEEHv27BEODg7i8OHDWqiSqHA9efJEWFlZCYVCITp27CgCAwNFUlKS2jxZWVnSv+Pi4sTIkSOFubm5iImJ+a/LJcqXhIQEYWZmJsaMGSOEECI7O1uaFh0dLfr27SucnJzEH3/8IbWPHz9emJqaihMnTvzn9X5K2JNGH+z27duoX78+vL29ERwcLLXPnDkTY8aMwT///CP1plWsWBH29vaIiIiAl5eXtkomKhSmpqYYMmQIMjMzUa5cOURFRaF27dr46quv4Onpic6dO8PA4MWv3R9++AGbN29GamoqDh48CBcXFy1XT5RXbGwsGjduDIVCgRYtWkAIAX19fek0Fjc3N3z33XcQQmDChAkwMzNDfHw8Zs+ejcOHD7+x95jySdspkT4NAQEBombNmiIsLEwIIcScOXNEyZIlxZ49e4QQQqhUKmneUaNGibJly4qnT59qpVaiwrRy5UpRt25d8fDhQyGEENu3bxcTJkwQCoVC9OvXT/z0009CCCFOnz4tduzYIa5fv67NconeKDo6WpiYmIjvvvtO+Pr6Ch8fH7Fjxw7p9/mrPWr9+/cXSqVS6Ovri5MnT2qr7E8KLxygAtOhQwdcunQJVatWxd69e7Flyxb4+vqqnTy9Z88e2NrawtzcHGXLltVyxUSFo2nTprCzs8OqVasAvPhuHD9+HJ6enrhy5QquXr2KxYsXo1OnTryamWQpOTkZTk5OGDRoEGbPno27d++iTZs2MDQ0xJgxY9CiRQvpCs7cY/jkyZNYs2YNBg4cCEdHRy3vwaeBIY3eS3JyMsLDw5Geno5q1aqhQ4cOAICuXbti48aNGDlyJEJCQtTulTZ27FisW7cOhw8fhq2trZb3gOjDpaam4sKFCzAwMICzszOUSiUUCgV+/fVXbNq0Cb/99ht69+6N3bt349ChQ7C3t8eVK1cwb948BAYGolq1atreBSI1ub+vDxw4gBs3bqB79+5SELtz5w7atm0LQ0NDjB07Fs2bN88T1DIyMqBUKrW8F58OhjTSWFxcHAICAmBvb4+bN2/i1q1bmD17NgYOHAjgRVCLj4/HyJEj0bFjR5iYmGDixImYPXs2Dh48qHbbDaKP1dmzZ9G/f38YGxujXr16GDNmjHSF8q1bt1C7dm1kZWXByMgI4eHhfMQZfRQePXqEYsWKSa9zr77Pzs6GgYGBWo/am4IaFSCtDbTSRyk2NlaYmJiIoKAgkZGRIWJiYkT16tVFjRo11M6t6dChg3S1T1BQkDA2NuY5CvTJiI+PFxYWFmL8+PEiOTlZbVrulcxr1qwRdnZ24s8//9RGiUQaS0hIEJaWlmLgwIEiMjJSPHr0SG16ZmamEEKIO3fuiLp164pGjRqJP//8U+2cYypYfOIA5du1a9fQpEkTtGrVCiEhITAyMoKrqytKly6NtLQ06OvrIz09HQDwxx9/wMPDA506dcKSJUt4lQ99MtLS0vD111+jY8eOmDp1qvQYJ5VKBeD/7wvo7OyMYsWK4fHjxwAg3X2dSK6io6NhYGCAs2fPYtmyZfD29sa+ffvw77//AgAMDQ2hUqlgaWmJrVu3IiUlBcuXL8fTp0+1XPmni7fgoHzLyclB+fLlkZGRgcOHD6N+/foICQlBZGQkXFxc0KtXL6hUKnh4eKB3795YunQprKys0KtXL9SoUUPb5RMViKtXryI1NRXdu3dXa88NZ7nDQzVr1kSbNm0QGBiIVq1aoXjx4lqolij/PD090aJFC/Ts2RPOzs4IDg5GcHAwlEolPvvsM3To0AE2NjYAAEtLSxw8eBDp6ekwNTXVcuWfLp6TRhq5cOECAgMDYWRkBCsrK/z5559Yvnw5vL29ce7cOSQlJWHu3Lm4f/8+XF1dsWPHDhgaGmq7bKIC8+uvv+Kbb75BUlISSpcuLYWyl2VlZeGff/5BdnY2xowZg02bNvFqZpKtl4/hL774Ardv38aePXsAvBhBadiwIS5fvoxmzZrB3t4ew4YNg62tLcPZf4DDnaSRypUrY+HChXj27BnWrVuHUaNGoX379rCysoKPjw/69++PkydPSuGNAY0+Benp6dJwZZkyZfDw4UOcOnUKAPIENAAIDQ3F+vXr4e3tjb/++osBjWTpwYMHAF4cw5mZmQBePAz90aNH2L17NwBg4sSJUCgU2L17N1q1aoWDBw+iVatWyM7O1lbZOoUhjTRWpUoVLFu2DD4+Pti/fz8OHTokTcvOzoaZmRnq1auHihUrarFKooJx+vRpdOzYEVeuXAHw4qkZHh4e+P7773H27FkAec83u3r1KszMzKCnp4eSJUv+5zUTvcu5c+fg6emJGTNmAACMjIygUqlQokQJlCtXDocOHUKfPn2we/dubNiwAX5+fvjuu++QmJiIv//+G+bm5lreA93AkEbvpWLFiliyZAmEEJg2bRoOHz4MANIjb4g+BbGxsahfvz6cnJxQoUIFAICtrS26d++OpKQkjB8/HnFxcdKtBx48eICxY8di48aN6NevH29JQLKUnZ2N0NBQXLp0CZs2bcLUqVMBvOhRMzMzQ2BgIKZPn46//voL+/fvl26blHt2FHuG/zs8J40+yIULFzBs2DCkpqZi/vz5qFu3rrZLIioQsbGxqFevHoYOHYrp06fnmT516lQsX74c2dnZ6NGjB1JTU/HkyRMcPnwYO3fu5H3RSNbCwsIwcOBA9OrVC2fOnEHz5s0xduxYAC+G97/++muYmppi2bJlEEK8dlifCh/fdfoglStXxuzZs2Fraytd9UP0sTtz5gx8fX3zBLSJEyeif//+AIAJEyZg+fLl6Ny5M/bt24fLly/D0dERUVFRDGgkW7nnkn3xxRfw8/PDo0eP4OHhgd9//x0hISEAAFNTU3h4eGDz5s24du0aA5oWsSeNCkRmZiaMjIy0XQbRBxNCoFGjRjh48CDOnTuHypUrAwBmzpyJmTNnIiwsDK1atVJb5unTpzAxMVF7Ti2RnLz8JAEhBIQQCA0NRVxcHCZMmIDFixdjz5496Ny5M8aNGwcAqFSpEry9vbF69WotVq7bGNKIiF7x4MED1K5dGxYWFggPD8fq1asxe/ZsbNiwAc2aNVObNysrS7qKmSGN5OjcuXNo27YtXFxcMH78eJQqVQo2Nja4ceMG3N3dMW/ePLRr1w6TJk1CREQEOnfujKCgIKxYsQKNGzeW/qNC/z2e5U1E9BIhBIoXL47jx4/Dzc0N1atXh0KhkALay/eUWrZsGYoWLYoePXoAAAMayU5GRgYWLVqE8+fP4+LFizAyMsLVq1cxaNAgdOjQATNmzMCePXvQpUsXfPvtt9DT08OyZctgYWGBAQMGaLt8nceQRkQ67969e0hJSYFKpUKNGjWgUChQvHhxxMbGwsfHB+np6ShXrpxaQJs4cSKmTZsm3YaDSI6USiW6desGPT09HD16FA4ODmjWrBlGjhyJrVu3Ijk5GQ8ePEBiYiKcnZ0xaNAgKJVKNG3aVNulE3jhABHpuPj4eLRs2RKtW7dGQEAAvvzyS+nkanNzc0RFRUGlUqFXr16Ij48HAEyePBlz5szBiRMn4OjoqM3yiV4rOTkZEyZMAADUr18fXbt2hZubGzZv3gwnJyecOnUK7dq1Q05ODhITE5GcnAwAKFeuHIKDg6VbzpB2sSeNiHRWbGwsvL29MWDAALRt2xabNm3C0qVLUbNmTQQGBiIzMxPm5uaIjo6W2qpUqYKwsDAcOnQItWrV0vYuEOWRk5ODrVu34qeffkJGRgZmzZoFLy8v6OvrIzMzE1988QXmzZuHTp064bPPPkNCQgJcXFykcyp5fz/5YEgjIp107tw51K9fH0OGDMG0adMAAPb29li+fDkuXboEANIVy8WLF0d0dDQqV66MgwcP4vTp03Bzc9NW6URvdOrUKURERKBnz554/vw51qxZg5ycHMydOxd16tSBQqHA0qVLMXz4cDx9+hQdOnSAi4vLa59BS9rHkEZEOkelUmHmzJkwNjaGs7Oz1L5u3TpkZmbi33//xaxZs2BlZYX27dvDyMgIxYsXx6VLl5CamsqhIJKl2NhY1KlTB99++y2sra3Rt29fqFQqhIWFAQDmzp2L2rVrS/NPmTIFQgh07NiRAU2meAsOItJJ169fx5AhQ3D37l0MHz4cCQkJmDlzJgYOHIiaNWti7dq1SElJwdWrV1G5cmV888036Nq1q7bLJnqt+Ph41KlTB6NHj8bEiROlocvU1FT89NNPCAsLQ4sWLTB37lwAwIkTJzBjxgzcvHkTe/fuhampKa9OliGGNCLSWVevXpUeGn3jxg2Eh4fDz88PwIvzevT19bFixQqcPn0agwcPhpOTk5YrJsorISEBDRo0gJOTE/bt2yc9LB148TzONwW106dPw9ramk+LkTGGNCLSCffu3cOdO3eQkJAAR0dHFC9eHDY2Nrh27RqGDRuGCxcuYNSoUejWrRsA9ZvUZmdnw8CAZ4eQ/OQ+Y9be3h5CCAwaNAjdu3eHhYWF9ED0l3vUfv31V9StWxfLly/XcuWUHxyEJqJPXnx8PFq1aoXPPvsMXbt2Ra1ateDn54f9+/fDzs4O8+fPh4ODA1asWIE1a9YAAAwNDZGTkwMADGgkS/Hx8ahZsyZGjx6NxMRENG/eHPPnz8e6deuQlpYmDV8KIVCqVCn0798fbdq0QWxsLG7fvq3l6ik/2JNGRJ+0hIQE1KtXDwMGDEDHjh1Ro0YNrFq1CuvXr8fJkyexceNGtGvXDleuXMHgwYPx+PFjdO7cmXdbJ1nLzs7GpEmTUKRIEelZmwAwePBgbNu2DUOHDkWPHj1QokQJtR61+/fvQwiBkiVLaqt00gBDGhF9stLT09G5c2dUqlQJCxcuVHu25okTJzBu3DgcOXIER44cgYuLC27cuIEvvvgCJiYmWLduHczNzbW8B0R53bx5E7dv34adnR1KlSoFQH1I/l1BjT4eHO4kok9WTk4Orly5ggYNGkhtuSdUe3p6IigoCMWLF8eqVaugUqlQtmxZrFu3DsuXL2dAI1k6e/YsWrVqhaCgIFy+fFlqNzAwkIbnFy5ciNatW2P+/PlYv3497t27B4VCwYD2EeKJFkT0yXn69ClMTExw8+ZNJCQkoESJEtI0PT09qUetcePG8PT0xIkTJ6Cnp4ecnByUKVNGi5UTvdnZs2fh7e2N/v37o0ePHnBxcVGbrq+vL13wsnDhQujr62PcuHEwNDREv379eC+0jxA/MSL6pJw6dQouLi5ITk5GqVKlYGdnh02bNuHx48dST4JCoZB61MqUKQMTExMA4ONwSLbu37+Pfv36oX///pg1a5ZaQHv69Kk0nGloaIjMzEwAwLx58/DNN9+gcePGDGgfKX5qRPTJiI2NRaNGjRAQEAAHBweUKlUKfn5+WL16Nfbv3y/98QIg/dG6d++e9IgnnqJLcvXw4UM8e/YMXbp0kdqOHDmCkJAQODs7o127dliwYAGAF48zy87OBgCEhISgUqVK2iiZCgBDGhF9EuLi4uDl5YXvvvsOCxYskAJXSEgIGjVqhAEDBuDXX3+Vbj3w5MkTTJw4Efv370e/fv0A8KRqkp8LFy4gMjISKpUKcXFxuHbtGgBg6dKlGDJkCHbs2IFWrVohOzsbYWFhiIiIAMDbxnwq+CkS0Ufv2rVraNKkCQICAjB9+nQALwLX/PnzcfPmTcyZMwdDhgxBnz59YGtrC3t7e+jr6+PixYvYvXs3qlSpouU9IMorJiYG3t7emDlzJho1aoTAwEC0b98ejo6OuHjxIiZPngx/f3+4urri2rVrcHNzw9mzZ9GkSRNtl04FhCGNiD56OTk5KF++PJ4/f47Dhw+jfv36mDFjBr7//nts27YN1apVw65du/Djjz/i4sWLuHPnDurWrYtmzZqhfPny2i6fKI/Y2FjUr18fgYGBGDRoEABg+vTpaNGiBS5duoTmzZtLw5hCCJiYmMDZ2RmlS5fWZtlUwHifNCL6JFy4cAGBgYEwMjJC6dKl8eeffyIsLAzNmjWDSqXiidP00YiLi0O9evUwZMgQqWcYAHbv3o0aNWqgbNmyeZaZMGECNm7ciIiICNjZ2f2X5VIhYkgjok/G+fPn8e233+LQoUOYOnUqhg8fLk17+Ua2RHJ17do1uLu7o3Hjxti4caPUPnXqVPzwww/Ys2cPHB0dpfaoqCj8+eefWLlyJSIjI6WLYOjTwP9aEtEno0qVKli2bBl8fHwQERGBQ4cOabskIo28OnQPADNmzMCiRYvw448/qgW0LVu2YPLkyTh27BgOHjzIgPYJYk8aEX1ycoc+hRCYMGEC6tevr+2SiPLt1aH78PBwrF27Fs2aNVObb/fu3ahUqRKKFi0KKysrLVVLhYk9aUT0yalcuTIWLVoEQ0NDjBgxAv/884+2SyLKt8qVK2PhwoV49uwZ1q5di9GjR6NZs2YQQki3lgkODka/fv1QsmRJBrRPGEMaEX2SKleujNmzZ8PW1hY2NjbaLodII68O3UdFRUnP35w4cSJmzpyJ8PBwFC9eXNulUiHicCcRfdIyMzNhZGSk7TKI3svLQ/chISHYu3cvgoODcejQIdSqVUvb5VEhY0gjIiKSsQsXLmDYsGE4fvw40tLScPToUQY0HcHhTiIiIhmrXLky5syZg7p16yI6OpoBTYewJ42IiOgjkJWVBUNDQ22XQf8hhjQiIiIiGeJwJxEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEVCoVCgfDw8AJb34EDB6BQKPDgwYMCWycRkZwxpBGR5MiRI9DX10eLFi0+eF0pKSnw9/cvgKryz8HBQXoIde6Pra3tf1pDQevduzfatWv3zvnu3LmDAQMGoFy5clAqlbC2tkbz5s1x9OhRaZ73Dc4ODg5YsGCBxssR0Ycx0HYBRCQfP//8M7777jv89NNPuHr1KsqVK/fGeYUQyMnJgYGB+q+R3AeaW1tbF3a5rzVlyhT0799feq2vr//e6/qY7vDeoUMHZGVlYc2aNahQoQJu376NiIgI3L9/X9ulEdH7EkREQognT56IokWLiqSkJNGlSxcxefJktemRkZECgNi1a5eoVauWMDQ0FPv37xe+vr5i0KBBYujQoaJkyZKiQYMGQgghAIgtW7YIIYSoW7euGD16tNr67ty5IwwMDMT+/fuFEEKEhYWJWrVqCTMzM1G6dGnRtWtXcfv27TzbT0tLe+M+2Nvbi/nz579x+tKlS0WFChWEoaGhqFKlivjll1/UpgMQy5YtE23atBEmJiZi4sSJQgghtm7dKtzd3YVSqRTly5cXkyZNEllZWdJyaWlpon///sLKykoolUpRvXp1sW3bNiGEEKmpqeLzzz8XZcuWFUWKFBE1atQQ69evV9vu77//LmrUqCGMjY2FhYWFaNKkiXjy5IkIDg4WANR+IiMj8+xXWlqaACAOHDjw1vfm5fXY29sLIYS4ePGiaNOmjbCyshKmpqbCw8ND7N27V1rO19c3Tw1CCBEcHCxcXV3VtjF//nxpvUK8+Mw8PT2FiYmJMDc3F15eXiI5OfmNNRKROg53EhEAYOPGjahatSqqVq2KHj16YNWqVRCveWrcqFGjEBISgsTERLi4uAAA1qxZAwMDAxw+fBgrVqzIs0z37t2xYcMGtfVt3LgRpUuXhq+vL4AXPXBTp05FbGwswsPDcfnyZfTu3bvA9m/Lli0YPHgwhg8fjvj4eAwYMAB9+vRBZGSk2nzBwcFo27Ytzpw5g759+2L37t3o0aMHAgMDkZCQgBUrVmD16tWYPn06AEClUsHf3x9HjhzB2rVrkZCQgBkzZkg9eM+fP0etWrWwfft2xMfH46uvvsIXX3yBY8eOAXgxLNy1a1f07dsXiYmJOHDgANq3bw8hBEaMGIHOnTujRYsWSElJQUpKCry8vPLsm5mZGczMzBAeHo6MjIzX7v+JEycAAKtWrUJKSor0+smTJ2jZsiX27duH6OhoNG/eHK1bt8bVq1cBAJs3b4atrS2mTJki1ZAf2dnZaNeuHXx9fREXF4ejR4/iq6++gkKhyNfyRAT2pBHRC15eXmLBggVCCCGysrJEqVKl1HpUcnuywsPD1Zbz9fUVbm5uedaHl3rScnvNDh48KE2vV6+eGDly5BvrOX78uAAgHj9+rLb9d/WkGRkZCVNTU+ln4cKF0v71799fbf5OnTqJli1bqtU8ZMgQtXl8fHzE999/r9YWFhYmypQpI4QQYvfu3UJPT0+cO3fujXW9qmXLlmL48OFCCCFOnTolALyxh6lXr16ibdu271znH3/8IUqUKCGMjY2Fl5eXGDNmjIiNjVWb5+XP5G2cnJzE4sWLpdev66F8V0/avXv33tm7R0Rvx540IsK5c+dw/PhxfP755wAAAwMDdOnSBT///HOeeT08PPLV9jJLS0v4+flh3bp1AIDLly/j6NGj6N69uzRPdHQ02rZtC3t7exQtWhQNGzYEAKlHJ79GjhyJmJgY6adnz54AgMTERNSvX19t3vr16yMxMfGt+3Lq1ClMmTJF6q0yMzND//79kZKSgqdPnyImJga2traoUqXKa+vJycnB9OnT4eLigpIlS8LMzAx79uyR9svV1RVNmjSBs7MzOnXqhB9//BFpaWka7TPw4py0mzdvYuvWrWjevDkOHDgAd3d3rF69+q3LpaenY9SoUXByckLx4sVhZmaGpKQkjd/3V1lYWKB3795Sz9zChQvz3QtHRC8wpBERVq5ciezsbJQtWxYGBgYwMDDAsmXLsHnz5jyBwdTUNM/yr2t7Vffu3fHHH38gKysL69evR/Xq1eHq6grgRVBo1qwZzMzMsHbtWpw4cQJbtmwB8GIYVBOlSpVCpUqVpJ/ixYtL014dahNC5Gl7dV9UKhUmT56sFvzOnDmDCxcuwNjYGEWKFHlrPXPnzsX8+fMxatQo7N+/HzExMWjevLm0X/r6+ti7dy927twJJycnLF68GFWrVsXly5c12m8AMDY2hp+fHyZOnIgjR46gd+/eCA4OfusyI0eOxKZNmzB9+nRERUUhJiYGzs7O73zf9fT08gyHZ2Vlqb1etWoVjh49Ci8vL2zcuBFVqlTBP//8o/F+EekqhjQiHZednY1ffvkFc+fOVQsisbGxsLe3l3q/PlS7du3w/Plz7Nq1C+vXr0ePHj2kaUlJSUhNTcWMGTPg4+ODatWq4c6dOwWy3VyOjo44dOiQWtuRI0fg6Oj41uXc3d1x7tw5teCX+6OnpwcXFxdcv34d58+ff+3yUVFRaNu2LXr06AFXV1dUqFABFy5cUJtHoVCgfv36mDx5MqKjo2FkZCSFVCMjI+Tk5LzXPjs5OSE9PV16bWhomGddUVFR6N27Nz777DM4OzvD2toaycnJavO8rgZLS0vcunVLLajFxMTkqaFmzZoYM2YMjhw5gho1amD9+vXvtS9Euoi34CDScdu3b0daWhq+/PJLmJubq03r2LEjVq5ciW+//faDt2Nqaoq2bdtiwoQJSExMRLdu3aRp5cqVg5GRERYvXoyvv/4a8fHxmDp16gdv82UjR45E586d4e7ujiZNmmDbtm3YvHkz9u3b99blJk6ciICAANjZ2aFTp07Q09NDXFwczpw5g2nTpsHX1xcNGjRAhw4dMG/ePFSqVAlJSUlQKBRo0aIFKlWqhE2bNuHIkSMoUaIE5s2bh1u3bknh8NixY4iIiECzZs1gZWWFY8eO4e7du9J0BwcH7N69G+fOnUPJkiVhbm6e57Yg9+7dQ6dOndC3b1+4uLigaNGiOHnyJGbNmoW2bdtK8zk4OCAiIgL169eHUqlEiRIlUKlSJWzevBmtW7eGQqHAhAkToFKp1Nbv4OCAgwcP4vPPP4dSqUSpUqXQsGFD3L17F7NmzULHjh2xa9cu7Ny5E8WKFQPwYkj7hx9+QJs2bWBjY4Nz587h/Pnz0vAzEeWDls+JIyItCwgIUDt5/mW5J7WfOnXqjSfu+/r6isGDB+dZFq85Sf2vv/4SAKTbdLxs/fr1wsHBQSiVSlGvXj2xdetWAUBER0cLIf67W3C87sT6Xbt2CS8vL1GkSBFRrFgxUbt2bfHDDz9I0+/duyf69OkjSpYsKYyNjUWNGjXE9u3bpWlt27YVZmZmwsrKSowfP1707NlTuhggISFBNG/eXFhaWgqlUimqVKmidtL+nTt3hJ+fnzAzM3vjLTieP38ugoKChLu7uzA3NxcmJiaiatWqYvz48eLp06fSfFu3bhWVKlUSBgYG0gn+ly9fFo0aNRJFihQRdnZ2YsmSJXk+06NHjwoXFxehVCrFy382li1bJuzs7ISpqano2bOnmD59urTeW7duiXbt2okyZcoIIyMjYW9vLyZOnChycnLe+PkQkTqFEK+5xp6IiIiItIrnpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQz9H0BUwaHE7W7bAAAAAElFTkSuQmCC",
      "text/plain": [
       "<Figure size 700x400 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "value_counts = transport_data['arrival_forecast_status'].value_counts()\n",
    "plt.figure(figsize=(7, 4))\n",
    "value_counts.plot(kind='bar', rot=45)\n",
    "plt.title('Number of rows per arrival forecast status')\n",
    "plt.xlabel('Arrival Forecast Status')\n",
    "plt.ylabel('Number of rows')\n",
    "plt.yscale('log')\n",
    "plt.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **5️⃣ departure_time, departure_forecast and departure_forecast_status**\n",
    "\n",
    "The field `departure_time` described the theoretical departure time of transportation at a stop. The field `departure_forecast` describes the actual departure time of transportation at a stop. The field `departure_forecase_status` is an enumeration type field that describes the status of a transportation stop's arrival time. The possible values for this field are:\n",
    "\n",
    "- **ESTIMATED**: The actual departure time has been estimated after the stop has been passed.\n",
    "- **FORECAST**: This stop is a forecast and has not yet been confirmed.\n",
    "- **REAL**: The effective actual departure time has been confirmed.\n",
    "- ~~**UNKNOWN**: The status of the departure time is unknown.~~\n",
    "\n",
    "We decide to do the following:\n",
    "\n",
    "- Translate the `departure_forecast_status` values into English.\n",
    "- Delete all rows that have `UNKNOWN` values in the `departure_forecast_status` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[nan, 'PROGNOSE', 'REAL', 'UNBEKANNT', 'GESCHAETZT']"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data['departure_forecast_status'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "transport_data['departure_forecast_status'] = transport_data['departure_forecast_status'].replace({\n",
    "    'PROGNOSE': 'FORECAST',\n",
    "    'UNBEKANNT': 'UNKNOWN',\n",
    "    'REAL': 'REAL',\n",
    "    'GESCHAETZT': 'ESTIMATED',\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[nan, 'FORECAST', 'REAL', 'UNKNOWN', 'ESTIMATED']"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data['departure_forecast_status'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 2,701 rows with departure_forecast_status == UNKNOWN.\n"
     ]
    }
   ],
   "source": [
    "print(f\"Removing {transport_data[transport_data['departure_forecast_status'] == 'UNKNOWN'].shape[0]:,} rows with departure_forecast_status == UNKNOWN.\")\n",
    "transport_data = transport_data[transport_data['departure_forecast_status'] != 'UNKNOWN']\n",
    "transport_data['departure_forecast_status'] = transport_data['departure_forecast_status'].cat.remove_unused_categories()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removing 208 rows where the departure_forecast is missing, but the departure_forecast_status and departure_time are not.\n"
     ]
    }
   ],
   "source": [
    "# Remove rows where the departure_forecast is missing, but the departure_forecast_status and departure_time are not\n",
    "print(f\"Removing {transport_data[(transport_data['departure_time'].notna()) & (transport_data['departure_forecast'].isna()) & (transport_data['departure_forecast_status'].notna())].shape[0]:,} rows where the departure_forecast is missing, but the departure_forecast_status and departure_time are not.\")\n",
    "transport_data = transport_data[(transport_data['departure_time'].isna()) | (transport_data['departure_forecast'].notna()) | (transport_data['departure_forecast_status'].isna())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[nan, 'FORECAST', 'REAL', 'ESTIMATED']"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data['departure_forecast_status'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAmkAAAG7CAYAAACYbwJrAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjcuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/bCgiHAAAACXBIWXMAAA9hAAAPYQGoP6dpAABXQElEQVR4nO3dd1QU198G8GfpTUEURQSRKCooRQRFxYJdsUWNxl6iSZSIvRewROw1ICYxaohREzUae0XFFhuCCPaGigUbAkrb+/7hy/xcQWUNZEf2+ZzDSfbO7Mx3dmbh8d4pCiGEABERERHJio6mCyAiIiKi3BjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSqECsWrUKCoUCRkZGuHXrVq7pjRo1QvXq1TVQGXDw4EEoFAps2LBBI+tX182bN+Hn5wdLS0soFAoMGzZM0yVphUaNGqFRo0aaLuOD7t27h6CgIJw7d07TpeRJ247fwtgfO3bsQFBQ0L9axu+//45FixYVSD2kOQxpVKDS09MxadIkTZfxSRs+fDj++ecf/PLLLzh+/DiGDx+u6ZJIRu7du4epU6fKNqRp2/FbGPtjx44dmDp16r9aBkNa0aCn6QKoaGnZsiV+//13jBo1Cm5ubpou5z/18uVLGBkZQaFQ/KvlxMbGolatWujQoYPa7xVC4NWrVzA2Nv5XNXzqXr58WeQ+g+zsbGRlZRX68g0NDf/Vcv7N8VuYdRF9itiTRgVqzJgxKFmyJMaOHfve+W7evAmFQoFVq1blmqZQKFS6+oOCgqBQKBATE4MvvvgC5ubmsLS0xIgRI5CVlYVLly6hZcuWKFasGCpUqIA5c+bkuc5Xr15hxIgRsLa2hrGxMRo2bIioqKhc850+fRrt2rWDpaUljIyMUKNGDfzxxx8q8+QM7+7Zswf9+/eHlZUVTExMkJ6e/s5tvn37Nnr27InSpUvD0NAQTk5OmD9/PpRKJYD/DctevXoVO3fuhEKhgEKhwM2bN9+5TIVCge+++w5hYWFwcnKCoaEhVq9eDQA4cuQImjRpgmLFisHExAR169bF9u3bpfcmJydDT08Pc+fOldqSkpKgo6MDc3NzlUAQEBAAKysrCCEAAFFRUWjTpo20LTY2NvDz88OdO3feWSvwv2HvyMhIeHt7w9jYGOXKlcPkyZORnZ2tMm9GRgZmzJiBqlWrwtDQEFZWVujXrx8ePXqkMl+FChXQpk0bbNq0CTVq1ICRkdF7eyGEEJgzZw7s7e1hZGQEDw8P7Ny5M895k5OTMWrUKDg4OMDAwADlypXDsGHDkJqaqjJfzn5Yvnw5KleuDENDQzg7O2PdunUq8z169AiDBw+Gs7MzzMzMULp0aTRu3BiRkZEq8+V8P+bMmYMZM2bAwcEBhoaGiIiIgJeXFwCgX79+0jGS831515Bt3759UaFChXwtH8jfd+BtHzp+P3T8F2Rdd+/exddffw07OzsYGBjAxsYGnTt3xoMHDwC8/l0wcuRIuLu7S79P6tSpgy1btuRa1p9//onatWvD3NwcJiYm+Oyzz9C/f39pm9+3P/KSlpYmHVNGRkawtLSEp6cn1q5dK+2rkJAQAJCW9+bnGBISggYNGqB06dIwNTWFi4sL5syZg8zMTGkdjRo1wvbt23Hr1i2VZby5nw4ePKhSV16/k69fv44vv/wSNjY2MDQ0RJkyZdCkSRPZ9uIWRexJowJVrFgxTJo0CUOHDsWBAwfQuHHjAlt2ly5d0LNnT3zzzTfYu3ev9Itp3759GDx4MEaNGoXff/8dY8eORaVKldCxY0eV90+YMAEeHh74+eef8fz5cwQFBaFRo0aIiorCZ599BgCIiIhAy5YtUbt2bYSFhcHc3Bzr1q1D165dkZaWhr59+6oss3///vDz80N4eDhSU1Ohr6+fZ+2PHj1C3bp1kZGRgenTp6NChQrYtm0bRo0ahWvXriE0NBQeHh44fvw4Pv/8c1SsWBHz5s0DAJQtW/a9n8vmzZsRGRmJKVOmwNraGqVLl8ahQ4fQrFkzuLq6YsWKFTA0NERoaCjatm2LtWvXomvXrihevDi8vLywb98+jB49GgCwf/9+GBoa4sWLFzh58iTq1q0LANi3bx8aN24MhUKB1NRUNGvWDA4ODggJCUGZMmVw//59RERE4MWLFx/cj/fv38eXX36JcePGYdq0adi+fTtmzJiBp0+f4ocffgAAKJVKtG/fHpGRkRgzZgzq1q2LW7duITAwEI0aNcLp06dVesrOnj2L+Ph4TJo0CQ4ODjA1NX3n+qdOnYqpU6fiq6++QufOnZGQkICBAwciOzsbVapUkeZLS0tDw4YNcefOHUyYMAGurq64cOECpkyZgvPnz2Pfvn0qvaZ///03IiIiMG3aNJiamiI0NBTdunWDnp4eOnfuDAB48uQJACAwMBDW1tZISUnBX3/9hUaNGmH//v25AtaSJUtQuXJlzJs3D8WLF0eZMmWwcuVK9OvXD5MmTYKfnx8AwNbW9oOfe17eXr6jo6Pa34Ec7zt+83P8F1Rdd+/ehZeXFzIzM6X99vjxY+zevRtPnz5FmTJlkJ6ejidPnmDUqFEoV64cMjIysG/fPnTs2BErV65E7969AQDHjx9H165d0bVrVwQFBUnn3B44cEDaZnX3x4gRIxAeHo4ZM2agRo0aSE1NRWxsLB4/fgwAmDx5MlJTU7FhwwYcP35cel/O74Fr166he/fu0j8coqOj8f333+PixYv45ZdfAAChoaH4+uuvce3aNfz111/qHBIqWrdujezsbMyZMwfly5dHUlISjh07hmfPnn30MklNgqgArFy5UgAQp06dEunp6eKzzz4Tnp6eQqlUCiGEaNiwoahWrZo0/40bNwQAsXLlylzLAiACAwOl14GBgQKAmD9/vsp87u7uAoDYtGmT1JaZmSmsrKxEx44dpbaIiAgBQHh4eEj1CCHEzZs3hb6+vhgwYIDUVrVqVVGjRg2RmZmpsq42bdqIsmXLiuzsbJXt7d27d74+n3HjxgkA4p9//lFpHzRokFAoFOLSpUtSm729vfDz88vXcgEIc3Nz8eTJE5V2b29vUbp0afHixQupLSsrS1SvXl3Y2tpKn8OkSZOEsbGxePXqlRBCiAEDBoiWLVsKV1dXMXXqVCGEEHfv3hUAxI8//iiEEOL06dMCgNi8eXO+anxTw4YNBQCxZcsWlfaBAwcKHR0dcevWLSGEEGvXrhUAxMaNG1XmO3XqlAAgQkNDpTZ7e3uhq6ur8hm+y9OnT4WRkZH4/PPPVdqPHj0qAIiGDRtKbcHBwUJHR0ecOnVKZd4NGzYIAGLHjh1SGwBhbGws7t+/L7VlZWWJqlWrikqVKr2znqysLJGZmSmaNGmiUlPO96NixYoiIyMjz88gr+9Ow4YNVbYhR58+fYS9vX2+lp/f78C75HX85vf4L4i6+vfvL/T19UVcXNx763xTzn746quvRI0aNaT2efPmCQDi2bNn73zv+/ZHXqpXry46dOjw3nn8/f1Ffv48Z2dni8zMTPHrr78KXV1dld8Dfn5+Kvs8R87vw4iICJX2t38nJyUlCQBi0aJFH6yDCg+HO6nAGRgYYMaMGTh9+vQHh0jU0aZNG5XXTk5OUCgUaNWqldSmp6eHSpUq5XmFaffu3VV6Puzt7VG3bl1pKOXq1au4ePEievToAQDIysqSflq3bo3ExERcunRJZZmdOnXKV+0HDhyAs7MzatWqpdLet29fCCGkf5l/jMaNG6NEiRLS69TUVPzzzz/o3LkzzMzMpHZdXV306tULd+7ckbajSZMmePnyJY4dOwbgdY9Zs2bN0LRpU+zdu1dqA4CmTZsCACpVqoQSJUpg7NixCAsLQ1xcnFr1FitWDO3atVNp6969O5RKJQ4fPgwA2LZtGywsLNC2bVuV/eDu7g5ra+tcQzWurq6oXLnyB9d9/PhxvHr1StrHOerWrQt7e3uVtm3btqF69epwd3dXqaFFixZ5Dhc1adIEZcqUkV7r6uqia9euuHr1qsowcFhYGDw8PGBkZAQ9PT3o6+tj//79iI+Pz1Vvu3bt3tk7WxDeXv7HfAfyQ93j/9/UtXPnTvj6+sLJyem9Nf3555+oV68ezMzMpP2wYsUKlf2QM5TZpUsX/PHHH7h7967a2/62WrVqYefOnRg3bhwOHjyIly9fqvX+qKgotGvXDiVLloSuri709fXRu3dvZGdn4/Lly/+6vhyWlpaoWLEi5s6diwULFiAqKkplaJr+GwxpVCi+/PJLeHh4YOLEiSrnSvwblpaWKq8NDAxgYmICIyOjXO2vXr3K9X5ra+s823KGGXLOVxk1ahT09fVVfgYPHgzg9Tlbb/rQUGSOx48f5zmvjY2NNP1jvb3cp0+fQgiRr/XVrVsXJiYm2LdvH65evYqbN29KIe2ff/5BSkoK9u3bh88++wwODg4AAHNzcxw6dAju7u6YMGECqlWrBhsbGwQGBuZrX78ZZHLk7Js398WzZ89gYGCQa1/cv3//X+2HN9eXVw05Hjx4gJiYmFzrL1asGIQQuWp43zJz1rtgwQIMGjQItWvXxsaNG3HixAmcOnUKLVu2zPOPdX6362O9vfyP+Q7kh7rH/7+p69GjRx8c/t20aRO6dOmCcuXK4bfffsPx48dx6tQp9O/fX+V3R4MGDbB582ZkZWWhd+/esLW1RfXq1aXzxz7GkiVLMHbsWGzevBm+vr6wtLREhw4dcOXKlQ++9/bt26hfvz7u3r2LxYsXIzIyEqdOnZLOYVM38L2PQqHA/v370aJFC8yZMwceHh6wsrJCQEBAvk5roILBc9KoUCgUCsyePRvNmjXDjz/+mGt6TrB6+0T7fxNWPuT+/ft5tpUsWRIAUKpUKQDA+PHjc53PluPNc5YA5PtKzpIlSyIxMTFX+71791TW/THerqFEiRLQ0dHJ1/oMDAzg4+ODffv2wdbWFtbW1nBxcZHO0Tt48CD279+fqxfTxcUF69atgxACMTExWLVqFaZNmwZjY2OMGzfuvfXm/MF9U86+eXNflCxZErt27cpzGcWKFXvvZ/AuOct/17Hw5sn1pUqVgrGxsXSez9ve3mfvWuab6/3tt9/QqFEjLFu2TGW+d/3RU/dKYSMjIzx//jxX+7uC1dvL/5jvQH6oe/z/m7qsrKw+eAHLb7/9BgcHB6xfv15lXXld+NO+fXu0b98e6enpOHHiBIKDg9G9e3dUqFABderUee968mJqaiqdF/ngwQOpV61t27a4ePHie9+7efNmpKamYtOmTSo9v+qcyP+u3715HSP29vZYsWIFAODy5cv4448/EBQUhIyMDISFheV7nfTx2JNGhaZp06Zo1qwZpk2bhpSUFJVpZcqUgZGREWJiYlTa87q6qqCsXbtWujoRAG7duoVjx45JJ2tXqVIFjo6OiI6OhqenZ54/b4eD/GrSpAni4uJw9uxZlfZff/0VCoUCvr6+H71dbzM1NUXt2rWxadMmlX9ZK5VK/Pbbb7C1tVUZGmzatCnOnDmDjRs3SkOapqam8Pb2xtKlS3Hv3j2p/W0KhQJubm5YuHAhLCwscm1fXl68eIG///5bpe3333+Hjo4OGjRoAOD10Pbjx4+RnZ2d5374mKAAAN7e3jAyMsKaNWtU2o8dO5ZriLxNmza4du0aSpYsmWcNbwY64PVFF28G0OzsbKxfvx4VK1aUenYUCkWuW0nExMSonCD+ITnvz6vXpEKFCrh8+bLKH+DHjx9Lw9kfUljfgX97/KtTV6tWrRAREfHeYVmFQgEDAwOVgHb//v33/v4xNDREw4YNMXv2bACQrgx/3/74kDJlyqBv377o1q0bLl26hLS0tPcuM6feN48hIQR++umnPOt91zECINfv3re/k2+rXLkyJk2aBBcXl3x9z6lgsCeNCtXs2bNRs2ZNPHz4ENWqVZPaFQoFevbsiV9++QUVK1aEm5sbTp48id9//73Qann48CE+//xzDBw4EM+fP0dgYCCMjIwwfvx4aZ7ly5ejVatWaNGiBfr27Yty5crhyZMniI+Px9mzZ/Hnn39+1LqHDx+OX3/9FX5+fpg2bRrs7e2xfft2hIaGYtCgQfk6n0odwcHBaNasGXx9fTFq1CgYGBggNDQUsbGxWLt2rcofpyZNmiA7Oxv79++Xbt8BvA5vgYGBUCgUKlfpbtu2DaGhoejQoQM+++wzCCGwadMmPHv2DM2aNftgbSVLlsSgQYNw+/ZtVK5cGTt27MBPP/2EQYMGoXz58gBeD5evWbMGrVu3xtChQ1GrVi3o6+vjzp07iIiIQPv27fH555+r/bmUKFECo0aNwowZMzBgwAB88cUXSEhIQFBQUK7hymHDhmHjxo1o0KABhg8fDldXVyiVSty+fRt79uzByJEjUbt2bWn+UqVKoXHjxpg8ebJ0defFixdVbsPRpk0bTJ8+HYGBgWjYsCEuXbqEadOmwcHBId/3QKtYsSKMjY2xZs0aODk5wczMDDY2NrCxsUGvXr2wfPly9OzZEwMHDsTjx48xZ84cFC9ePN+fUWF8Bwri+M9vXdOmTcPOnTvRoEEDTJgwAS4uLnj27Bl27dqFESNGoGrVqtItWwYPHixd4Tt9+nSULVtWZdhxypQpuHPnDpo0aQJbW1s8e/YMixcvhr6+Pho2bAjg/fsjL7Vr10abNm3g6uqKEiVKID4+HuHh4ahTpw5MTEwAvO6pBl7//mzVqhV0dXXh6uqKZs2awcDAAN26dcOYMWPw6tUrLFu2DE+fPs21HhcXF2zatAnLli1DzZo1oaOjA09PT1hbW6Np06YIDg5GiRIlYG9vj/3792PTpk0q74+JicF3332HL774Ao6OjjAwMMCBAwcQExPzwd5yKkCau2aBipI3r+58W/fu3QUAlas7hRDi+fPnYsCAAaJMmTLC1NRUtG3bVty8efOdV3c+evRI5f19+vQRpqamudb39pWkOVczhYeHi4CAAGFlZSUMDQ1F/fr1xenTp3O9Pzo6WnTp0kWULl1a6OvrC2tra9G4cWMRFhaWr+19l1u3bonu3buLkiVLCn19fVGlShUxd+7cXFfLqXt1p7+/f57TIiMjRePGjYWpqakwNjYW3t7eYuvWrbnmUyqVolSpUgKAuHv3rtSec8Wjh4eHyvwXL14U3bp1ExUrVhTGxsbC3Nxc1KpVS6xateqD9ebsm4MHDwpPT09haGgoypYtKyZMmJDrqr3MzEwxb9484ebmJoyMjISZmZmoWrWq+Oabb8SVK1ek+dT5vHK2Nzg4WNjZ2QkDAwPh6uoqtm7dmueVkSkpKWLSpEmiSpUqwsDAQJibmwsXFxcxfPhwlSs5c/ZDaGioqFixotDX1xdVq1YVa9asUVleenq6GDVqlChXrpwwMjISHh4eYvPmze+8+nLu3Ll5bsPatWtF1apVhb6+fq7vy+rVq4WTk5MwMjISzs7OYv369WovPz/fgXd51/7Iz/FfUHUlJCSI/v37C2tra6Gvry9sbGxEly5dxIMHD6R5Zs2aJSpUqCAMDQ2Fk5OT+Omnn6TfNTm2bdsmWrVqJcqVKycMDAxE6dKlRevWrUVkZKTK+t63P942btw44enpKUqUKCEMDQ3FZ599JoYPHy6SkpKkedLT08WAAQOElZWVUCgUAoC4ceOGEEKIrVu3St+JcuXKidGjR4udO3fmumLzyZMnonPnzsLCwkJaRo7ExETRuXNnYWlpKczNzUXPnj2lq7Zzru588OCB6Nu3r6hataowNTUVZmZmwtXVVSxcuFBkZWW9c/uoYCmEeGP8h4ioEDVq1AhJSUmIjY3VdCkFSqFQwN/fX7rPGxFRQeA5aUREREQyxJBGREREJEMc7iQiIiKSIfakEREREckQQxoRERGRDDGkEREREcmQ1t/MVqlU4t69eyhWrJjaj2AhIiIiUocQAi9evICNjQ10dN7fV6b1Ie3evXuws7PTdBlERESkRRISEqRHxr2L1oe0nOe9JSQkqPXoFCIiIiJ1JScnw87OLl/PwdX6kJYzxFm8eHGGNCIiIvpP5OcUK144QERERCRDWhvSQkJC4OzsDC8vL02XQkRERJSL1j9xIDk5Gebm5nj+/DmHO4mIiKhQqZM7tLYnjYiIiEjOGNKIiIiIZIghjYiIiEiGGNKIiIiIZIghjYiIiEiGGNKIiIiIZIghjYiIiEiGGNKIiIiIZEjrn935qaowbrumS9AKN2f5aboEIiLSUuxJIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGSoSIe3GjRvw9fWFs7MzXFxckJqaqumSiIiIiP4VPU0XUBD69u2LGTNmoH79+njy5AkMDQ01XRIRERHRv/LJh7QLFy5AX18f9evXBwBYWlpquCIiIiKif0/jw52HDx9G27ZtYWNjA4VCgc2bN+eaJzQ0FA4ODjAyMkLNmjURGRkpTbty5QrMzMzQrl07eHh4YObMmf9h9URERESFQ+MhLTU1FW5ubvjhhx/ynL5+/XoMGzYMEydORFRUFOrXr49WrVrh9u3bAIDMzExERkYiJCQEx48fx969e7F3797/chOIiIiICpzGQ1qrVq0wY8YMdOzYMc/pCxYswFdffYUBAwbAyckJixYtgp2dHZYtWwYAsLW1hZeXF+zs7GBoaIjWrVvj3Llz71xfeno6kpOTVX6IiIiI5EbjIe19MjIycObMGTRv3lylvXnz5jh27BgAwMvLCw8ePMDTp0+hVCpx+PBhODk5vXOZwcHBMDc3l37s7OwKdRuIiIiIPoasQ1pSUhKys7NRpkwZlfYyZcrg/v37AAA9PT3MnDkTDRo0gKurKxwdHdGmTZt3LnP8+PF4/vy59JOQkFCo20BERET0MT6JqzsVCoXKayGESlurVq3QqlWrfC3L0NCQt+ggIiIi2ZN1T1qpUqWgq6sr9ZrlePjwYa7eNSIiIqKiRNYhzcDAADVr1sx1tebevXtRt25dDVVFREREVPg0PtyZkpKCq1evSq9v3LiBc+fOwdLSEuXLl8eIESPQq1cveHp6ok6dOvjxxx9x+/ZtfPvttxqsmoiIiKhwaTyknT59Gr6+vtLrESNGAAD69OmDVatWoWvXrnj8+DGmTZuGxMREVK9eHTt27IC9vf2/Wm9ISAhCQkKQnZ39r5ZDREREVBgUQgih6SI0KTk5Gebm5nj+/DmKFy+u6XLyrcK47ZouQSvcnOWn6RKIiKgIUSd3yPqcNCIiIiJtxZBGREREJEMMaUREREQyxJBGREREJEMMaUREREQypLUhLSQkBM7OzvDy8tJ0KURERES5aG1I8/f3R1xcHE6dOqXpUoiIiIhy0dqQRkRERCRnDGlEREREMsSQRkRERCRDDGlEREREMsSQRkRERCRDDGlEREREMqS1IY33SSMiIiI509qQxvukERERkZxpbUgjIiIikjOGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiGGNCIiIiIZYkgjIiIikiGtDWm8mS0RERHJmdaGNN7MloiIiORMa0MaERERkZwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQxpbUjjY6GIiIhIzrQ2pPGxUERERCRnWhvSiIiIiOSMIY2IiIhIhvQ0XQAREQBUGLdd0yVohZuz/DRdAhHlE3vSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhrQ2pPEB60RERCRnWhvS+IB1IiIikjO1Q9quXbtw5MgR6XVISAjc3d3RvXt3PH36tECLIyIiItJWaoe00aNHIzk5GQBw/vx5jBw5Eq1bt8b169cxYsSIAi+QiIiISBup/YD1GzduwNnZGQCwceNGtGnTBjNnzsTZs2fRunXrAi+QiIiISBup3ZNmYGCAtLQ0AMC+ffvQvHlzAIClpaXUw0ZERERE/47aPWk+Pj4YMWIE6tWrh5MnT2L9+vUAgMuXL8PW1rbACyQiIiLSRmr3pP3www/Q09PDhg0bsGzZMpQrVw4AsHPnTrRs2bLACyQiIiLSRmr3pJUvXx7btm3L1b5w4cICKYiIiIiIPqInrUePHvjpp59w5cqVwqiHiIiIiPARIc3MzAzz589HlSpVYGNjg27duiEsLAwXL14sjPqIiIiItJLaIW358uW4ePEi7t27hwULFsDc3ByLFy9GtWrVULZs2cKokYiIiEjrfPRjoYoVK4YSJUqgRIkSsLCwgJ6eHqytrQuyNiIiIiKtpXZIGzt2LLy9vVGqVClMmjQJGRkZGD9+PB48eICoqKjCqJGIiIhI66h9defcuXNhZWWFwMBAtG/fHk5OToVRFxEREZFWUzukRUVF4dChQzh48CDmz58PXV1dNGzYEI0aNUKjRo0Y2oiIiIgKgNohzc3NDW5ubggICAAAREdHY9GiRQgICIBSqUR2dnaBF0lERESkbdQOacDr3rSDBw/i4MGDiIyMRHJyMtzd3eHr61vQ9RWakJAQhISEMFQSERGRLKkd0kqUKIGUlBS4ubmhUaNGGDhwIBo0aIDixYsXRn2Fxt/fH/7+/khOToa5ubmmyyEiIiJSoXZICw8P/yRDGREREdGnRO2Q1qZNG+n/79y5A4VCIT1knYiIiIgKhtr3SVMqlZg2bRrMzc1hb2+P8uXLw8LCAtOnT4dSqSyMGomIiIi0jto9aRMnTsSKFSswa9Ys1KtXD0IIHD16FEFBQXj16hW+//77wqiTiIiISKuoHdJWr16Nn3/+Ge3atZPa3NzcUK5cOQwePJghjYiIiKgAqD3c+eTJE1StWjVXe9WqVfHkyZMCKYqIiIhI26kd0tzc3PDDDz/kav/hhx/g5uZWIEURERERaTu1hzvnzJkDPz8/7Nu3D3Xq1IFCocCxY8eQkJCAHTt2FEaNRERERFpH7Z60hg0b4vLly/j888/x7NkzPHnyBB07dsSlS5dQv379wqiRiIiISOuo1ZOWmZmJ5s2bY/ny5bxAgIiIiKgQqdWTpq+vj9jYWCgUisKqh4iIiIjwEcOdvXv3xooVKwqjFiIiIiL6f2pfOJCRkYGff/4Ze/fuhaenJ0xNTVWmL1iwoMCKIyIiItJWaoe02NhYeHh4AAAuX76sMo3DoEREREQFQ+2QFhERURh1EBEREdEb1D4njYiIiIgKH0MaERERkQwxpBERERHJEEMaERERkQzlK6R5eHjg6dOnAIBp06YhLS2tUIsiIiIi0nb5Cmnx8fFITU0FAEydOhUpKSmFWhQRERGRtsvXLTjc3d3Rr18/+Pj4QAiBefPmwczMLM95p0yZUqAFEhEREWmjfIW0VatWITAwENu2bYNCocDOnTuhp5f7rQqF4pMJaSEhIQgJCUF2dramSyEiIiLKJV8hrUqVKli3bh0AQEdHB/v370fp0qULtbDC5u/vD39/fyQnJ8Pc3FzT5RARERGpUPuJA0qlsjDqICIiIqI3qB3SAODatWtYtGgR4uPjoVAo4OTkhKFDh6JixYoFXR8RERGRVlL7Pmm7d++Gs7MzTp48CVdXV1SvXh3//PMPqlWrhr179xZGjURERERaR+2etHHjxmH48OGYNWtWrvaxY8eiWbNmBVYcERERkbZSuyctPj4eX331Va72/v37Iy4urkCKIiIiItJ2aoc0KysrnDt3Llf7uXPnPvkrPomIiIjkQu3hzoEDB+Lrr7/G9evXUbduXSgUChw5cgSzZ8/GyJEjC6NGIiIiIq2jdkibPHkyihUrhvnz52P8+PEAABsbGwQFBSEgIKDACyQiIiLSRmqHNIVCgeHDh2P48OF48eIFAKBYsWIFXhgRERGRNvuo+6TlYDgjIiIiKhxqXzhARERERIWPIY2IiIhIhhjSiIiIiGRIrZCWmZkJX19fXL58ubDqISIiIiKoGdL09fURGxsLhUJRWPUQERERET5iuLN3795YsWJFYdRCRERERP9P7VtwZGRk4Oeff8bevXvh6ekJU1NTlekLFiwosOKIiIiItJXaIS02NhYeHh4AkOvcNA6DEhERERUMtUNaREREYdRBRERERG/46FtwXL16Fbt378bLly8BAEKIAiuKiIiISNupHdIeP36MJk2aoHLlymjdujUSExMBAAMGDMDIkSMLvEAiIiIibaR2SBs+fDj09fVx+/ZtmJiYSO1du3bFrl27CrQ4IiIiIm2l9jlpe/bswe7du2Fra6vS7ujoiFu3bhVYYURERETaTO2etNTUVJUetBxJSUkwNDQskKKIiIiItJ3aIa1Bgwb49ddfpdcKhQJKpRJz586Fr69vgRZHREREpK3UHu6cO3cuGjVqhNOnTyMjIwNjxozBhQsX8OTJExw9erQwaiQiIiLSOmr3pDk7OyMmJga1atVCs2bNkJqaio4dOyIqKgoVK1YsjBqJiIiItI7aPWkAYG1tjalTpxZ0LURERET0/z4qpD19+hQrVqxAfHw8FAoFnJyc0K9fP1haWhZ0fURERERaSe3hzkOHDsHBwQFLlizB06dP8eTJEyxZsgQODg44dOhQYdRIREREpHXU7knz9/dHly5dsGzZMujq6gIAsrOzMXjwYPj7+yM2NrbAiyQiIiLSNmr3pF27dg0jR46UAhoA6OrqYsSIEbh27VqBFkdERESkrdQOaR4eHoiPj8/VHh8fD3d394KoSW16enpwd3eHu7s7BgwYoJEaiIiIiApSvoY7Y2JipP8PCAjA0KFDcfXqVXh7ewMATpw4gZCQEMyaNatwqvwACwsLnDt3TiPrJiIiIioM+Qpp7u7uUCgUEEJIbWPGjMk1X/fu3dG1a9eCq46IiIhIS+VruPPGjRu4fv06bty48d6f69evq13A4cOH0bZtW9jY2EChUGDz5s255gkNDYWDgwOMjIxQs2ZNREZGqkxPTk5GzZo14ePjwytMiYiIqEjIV0+avb19oRWQmpoKNzc39OvXD506dco1ff369Rg2bBhCQ0NRr149LF++HK1atUJcXBzKly8PALh58yZsbGwQGxsLPz8/nD9/HsWLFy+0momIiIgK20fdzPbu3bs4evQoHj58CKVSqTItICBArWW1atUKrVq1euf0BQsW4KuvvpIuCFi0aBF2796NZcuWITg4GABgY2MDAKhevTqcnZ1x+fJleHp65rm89PR0pKenS6+Tk5PVqpeIiIjov6B2SFu5ciW+/fZbGBgYoGTJklAoFNI0hUKhdkh7n4yMDJw5cwbjxo1TaW/evDmOHTsG4PXTD0xMTGBoaIg7d+4gLi4On3322TuXGRwczEdaERERkeypHdKmTJmCKVOmYPz48dDRUfsOHmpJSkpCdnY2ypQpo9JepkwZ3L9/H8DrW39888030NHRgUKhwOLFi9/7eKrx48djxIgR0uvk5GTY2dkVzgYQERERfSS1Q1paWhq+/PLLQg9ob3qztw4AhBBSW926dXH+/Pl8L8vQ0BCGhoYFWh8RERFRQVM7aX311Vf4888/C6OWXEqVKgVdXV2p1yzHw4cPc/WuERERERUlavekBQcHo02bNti1axdcXFygr6+vMn3BggUFVpyBgQFq1qyJvXv34vPPP5fa9+7di/bt2xfYeoiIiIjkRu2QNnPmTOzevRtVqlQBgFwXDqgrJSUFV69elV7fuHED586dg6WlJcqXL48RI0agV69e8PT0RJ06dfDjjz/i9u3b+Pbbb9VeFxEREdGnQu2QtmDBAvzyyy/o27dvgRRw+vRp+Pr6Sq9zTurv06cPVq1aha5du+Lx48eYNm0aEhMTUb16dezYsaNQ791GREREpGlqhzRDQ0PUq1evwApo1KiRyuOm8jJ48GAMHjy4wNYJACEhIQgJCUF2dnaBLpeIiIioIKh94cDQoUOxdOnSwqjlP+Xv74+4uDicOnVK06UQERER5aJ2T9rJkydx4MABbNu2DdWqVct14cCmTZsKrDgiIiIibaV2SLOwsEDHjh0LoxYiIiIi+n8f9VgoIiIiIipc/91jA4iIiIgo39TuSXNwcHjv/dCuX7/+rwoiIiIioo8IacOGDVN5nZmZiaioKOzatQujR48uqLoKHW/BQURERHKmdkgbOnRonu0hISE4ffr0vy7ov+Lv7w9/f38kJyfD3Nxc0+UQERERqSiwc9JatWqFjRs3FtTiiIiIiLRagYW0DRs2wNLSsqAWR0RERKTV1B7urFGjhsqFA0II3L9/H48ePUJoaGiBFkdERESkrdQOaR06dFB5raOjAysrKzRq1AhVq1YtqLqIiIiItJraIS0wMLAw6iAiIiKiN2jtzWxDQkLg7OwMLy8vTZdCRERElEu+Q5qOjg50dXXf+6Onp3bHnMb4+/sjLi4Op06d0nQpRERERLnkO1X99ddf75x27NgxLF26FEKIAimKiIiISNvlO6S1b98+V9vFixcxfvx4bN26FT169MD06dMLtDgiIiIibfVR56Tdu3cPAwcOhKurK7KysnDu3DmsXr0a5cuXL+j6iIiIiLSSWiHt+fPnGDt2LCpVqoQLFy5g//792Lp1K6pXr15Y9RERERFppXwPd86ZMwezZ8+GtbU11q5dm+fwJxEREREVjHyHtHHjxsHY2BiVKlXC6tWrsXr16jzn27RpU4EVR0RERKSt8h3SevfurfI4KCIiIiIqPPkOaatWrSrEMoiIiIjoTXziAJ84QERERDKktSGNTxwgIiIiOdPakEZEREQkZwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDLEkEZEREQkQwxpRERERDKktSGNTxwgIiIiOdPakMYnDhAREZGcaW1IIyIiIpIzhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhhjQiIiIiGWJIIyIiIpIhrQ1pfMA6ERERyZnWhjQ+YJ2IiIjkTGtDGhEREZGcMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMaW1ICwkJgbOzM7y8vDRdChEREVEuWhvS/P39ERcXh1OnTmm6FCIiIqJctDakEREREckZQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDGltSAsJCYGzszO8vLw0XQoRERFRLlob0vz9/REXF4dTp05puhQiIiKiXLQ2pBERERHJGUMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJEEMaERERkQwxpBERERHJkJ6mCyAiIipqKozbrukStMbNWX6aLqHQsCeNiIiISIYY0oiIiIhkiCGNiIiISIYY0oiIiIhkiCGNiIiISIYY0oiIiIhkiCGNiIiISIYY0oiIiIhkiCGNiIiISIYY0oiIiIhkiCGNiIiISIYY0oiIiIhkSOsfsC6EAAAkJydruBL1KNPTNF2CVvjUjotPGY/p/waP6f8Gj+f/zqd2TOfUm5M/3kch8jNXEXbnzh3Y2dlpugwiIiLSIgkJCbC1tX3vPFof0pRKJe7du4dixYpBoVBoupwiKzk5GXZ2dkhISEDx4sU1XQ7Rv8ZjmooaHtP/DSEEXrx4ARsbG+jovP+sM60f7tTR0flgkqWCU7x4cX75qUjhMU1FDY/pwmdubp6v+XjhABEREZEMMaQRERERyRBDGv0nDA0NERgYCENDQ02XQlQgeExTUcNjWn60/sIBIiIiIjliTxoRERGRDDGkEREREckQQxoRERGRDDGkEREREckQQxoRERGRDDGkERHJRFRUFP78809Nl0FUKHgzCfUxpBERyUB0dDRq1qyJkydParoUokKR83xshrX8433SSOOEEHy4PWm18+fPo3bt2hgzZgyCgoI0XQ5RgXj8+DEuXryIyMhImJiYoEmTJqhUqRIMDQ2hVCo/+HBxYkgjDUpJSYGZmRkABjXSXhcvXoSPjw+aNm2KdevWAeD3gT598fHxGDx4MF6+fIlbt24hLS0NJiYm6NmzJyZNmgRzc3MGtXzgp0MakZiYiG7duiE8PBzA625w/nuBtM25c+fg4eGB9PR0REVF4fDhw8jKymJAo09adHQ0fHx84O7ujtDQUFy5cgVXr15F/fr1sX79eowZMwYvXrxgQMsHfkKkEQ8fPkRWVhZWrlyJP/74AwCDGmmXmJgYeHp6YuLEiXjx4gWsrKzQp08fHDt2DEqlUtPlEX2UCxcuoE6dOhg2bBgWLlwIDw8PmJmZwcrKCr/99htat26Nv/76S/q9T+/H4U7SmLNnz2L27NlITEzEd999hy5dugCAShd4VlYW4uPj4eLioslSiQrUq1ev0LVrV7i6umL69OlSu4+PD+7evYvVq1fDx8eHPQ30SXn8+DHq1KkDMzMzHD9+XOXcszd/r7u7u6NEiRKIiIjQcMXyx98A9J/L6SXw8PDAmDFjULZsWfzwww/Sv6x0dHQghEBGRgaGDx+OoUOH4vnz55osmajAPHz4ECkpKQgPD5cCWmZmJgDgyJEjKFeuHPr06YMjR46wR40+KSVLlkSrVq1gaGiI4OBgJCUlSb/PdXR0kJGRAQD47rvvcO3aNSQkJHD05AMY0ug/cefOHdy5cwdpaWnQ0dFBVlYWAKBmzZoqQS3nHlFKpRIjR47E8uXLMX/+fJibm2uyfKICERcXh/bt26Nnz5548uSJ1K6vry99J94MakePHkV2dramyiXKl9jYWKxduxYAsHjxYjRs2BBbt27F0qVL8fjxYygUCiiVShgYGAAArl69CisrK9jY2PD8yw9gSKNCl5iYiEqVKiEgIADdu3fHuXPnkJ6eLk2vWbMmRo0ahbJly2Lp0qVYu3Ytxo0bhxUrVuCff/5BjRo1NFg9UcGIjY1F/fr14ePjg3HjxqFChQoq0/X09FR61CpUqIA2bdrgn3/+0UC1RPkTHR0NT09PXL9+XWqbNWsWmjZtim3btklBLWfIMzU1Fffu3UPz5s0B8J5pH6Kn6QKo6EtJSYG+vj5cXFygo6ODDh06oGHDhvDx8cHAgQMhhICXlxeGDx+OJUuWYNCgQXj58iWOHz/OgEZFwoMHD/Dll1/i66+/RnBwsMq0N8/V0dfXR0ZGBgwMDBAREYFWrVqhdOnSmiiZ6IOio6NRt25djBw5EhMnTlSZNnv2bIwdOxZbt26FEAIBAQEoWbIkgoODcfjwYezbtw+6uroaqvzTwZBGhUoIAUdHRwQFBSE6Ohq//vorvL29cfHiRQQGBuLPP/9Ew4YNMWLECHh7e8Pc3Bz6+voYO3YsnJ2dNV0+UYG4fv06zMzMMHjwYOkeaFFRUTh58iTCw8NRr149NG3aFM2aNYOBgQEyMzOhr6+PnTt3arp0ojzFxMSgbt26GDZsGL7//nupff369ShXrhx8fHwwe/ZsjBs3Dtu2bYOJiQkePnyIsLAwHDlyBJUqVdJg9Z8OXt1JhSKndyDnD9Lu3bsxceJErFmzBlWqVAEAtG3bFlFRUbCyssLDhw/RuXNnDBkyBA4ODvwXFhUpW7duRYcOHXD+/Hk4OztjxYoV+O2335CUlISKFSsiOjoaTk5OWLZsGezt7TVdLtF73b9/Hx4eHnBzc1P5h8TMmTOxZMkS7NixAzVq1JDON5swYQJ+/PFHpKen49ChQ/Dw8NBU6Z8cnpNGBS4+Ph5hYWF49eqV9CVt0aIFrK2tMXnyZABA3759cfbsWWzZsgUnTpxAz549ERsbC11dXQY0KnK8vb3h5+eHBg0aoH79+hg8eDDq16+Pn3/+GZs3b8aaNWuwa9cuXLhwQdOlEr1XSkoKnj9/jrp16yIxMRGbN28G8Po8tAULFmD16tXw8PCAQqGQLnqZOXMmRowYgdOnTzOgqYk9aVSgoqOjUaNGDcybNw8jRowAAGRnZ0NXVxeHDh3C/PnzkZSUhJs3b2LLli3w8vKS3vvmY6KIioI3H+905swZREREICEhAf369YOrq6t0LtqVK1fQtWtXLFmyBD4+Pposmeidzpw5g1atWuHs2bNISkrCkiVLcPr0aVSrVg0HDhxAeHi4dEFAjuPHj6NOnToaqvjTx3PSqMCcO3cO9erVw/jx46WABkD6Q1StWjXcv38f58+fx+XLl2FnZwfgf0OjDGhUFFy8eBH79u1Dv379oK+vL912oGbNmqhZs2ae71m9ejWUSiUcHR3/y1KJ8i06Ohq+vr7o168fbG1tYWtriyFDhmDp0qVYv349Ro4ciebNm0tXayoUCkycOBHBwcF48OABSpUqxdttfAxBVADOnz8vTExMRGBgoEr7r7/+Kk6ePCmys7OFEELs2bNHVKhQQRw9elQDVRIVrpSUFFG6dGmhUChE586dRUBAgLh48aLKPJmZmdL/x8TEiNGjRwtzc3Nx7ty5/7pconyJi4sTZmZmYvz48UIIIbKysqRpUVFRon///sLZ2Vls2LBBap80aZIwNTUVp06d+s/rLUrYk0b/2oMHD1CvXj34+PggMDBQap89ezbGjx+PEydOSL1pFStWhL29Pfbv34+6detqqmSiQmFqaophw4YhIyMD5cuXR2RkJGrVqoWvv/4aXl5e6NKlC/T0Xv/a/fHHH7Fp0yYkJSXh8OHDcHV11XD1RLlFR0ejcePGUCgUaNmyJYQQ0NXVlU5jcXd3x5AhQyCEwOTJk2FmZobY2FjMnTsXR48efWfvMeWTplMiFQ1t2rQRNWrUEOHh4UIIIebNmydKliwp9uzZI4QQQqlUSvOOGTNGlCtXTqSlpWmkVqLCtGLFCuHt7S2eP38uhBBi27ZtYvLkyUKhUIgBAwaIn3/+WQghxNmzZ8WOHTvEnTt3NFku0TtFRUUJExMTMWTIENGwYUNRv359sWPHDun3+ds9agMHDhSGhoZCV1dXnD59WlNlFym8cIAKTKdOnXDt2jVUqVIFe/fuxV9//YWGDRuqnDy9Z88e2NrawtzcHOXKldNwxUSFo2nTprCzs8PKlSsBvP5unDx5El5eXrh16xZu376NpUuX4osvvuDVzCRLN2/ehLOzM/z9/TF37lw8evQI7dq1g76+PsaPH4+WLVtKV3DmHMOnT5/G6tWrMXjwYDg5OWl4C4oGhjT6KDdv3sTmzZuRmpqKqlWrolOnTgCAbt26Yf369Rg9ejSCg4NV7pU2YcIErFmzBkePHoWtra2Gt4Do30tKSsKVK1egp6cHFxcXGBoaQqFQYN26ddi4cSP++OMP9O3bF7t378aRI0dgb2+PW7duYcGCBQgICEDVqlU1vQlEKnJ+Xx88eBB3795Fjx49pCD28OFDtG/fHvr6+pgwYQJatGiRK6ilp6fD0NBQw1tRdDCkkdpiYmLQpk0b2Nvb4969e7h//z7mzp2LwYMHA3gd1GJjYzF69Gh07twZJiYmmDJlCubOnYvDhw+r3HaD6FN14cIFDBw4EEZGRqhTpw7Gjx8vXaF8//591KpVC5mZmTAwMMDmzZv5iDP6JCQnJ6N48eLS65yr77OysqCnp6fSo/auoEYFSGMDrfRJio6OFiYmJmLcuHEiPT1dnDt3TlSrVk1Ur15d5dyaTp06SVf7jBs3ThgZGfEcBSoyYmNjhaWlpZg0aZK4efOmyrScK5lXr14t7OzsxJYtWzRRIpHa4uLihJWVlRg8eLCIiIgQycnJKtMzMjKEEEI8fPhQeHt7C19fX7FlyxaVc46pYPGJA5RvCQkJaNKkCfz8/BAcHAwDAwO4ubmhTJkyePr0KXR1dZGamgoA2LBhAzw9PfHFF1/ghx9+4FU+VGQ8ffoU3377LTp37ozp06dLj3FSKpUA/ndfQBcXFxQvXhwvXrwAAOnu60RyFRUVBT09PVy4cAHLli2Dj48P9u3bh+vXrwMA9PX1oVQqYWVlhb///huJiYkICwtDWlqahisvungLDsq37OxsODg4ID09HUePHkW9evUQHByMiIgIuLq6ok+fPlAqlfD09ETfvn0RGhqK0qVLo0+fPqhevbqmyycqELdv30ZSUhJ69Oih0p4TznKGh2rUqIF27dohICAAfn5+sLCw0EC1RPnn5eWFli1bonfv3nBxcUFgYCACAwNhaGiIzz//HJ06dYKNjQ0AwMrKCocPH0ZqaipMTU01XHnRxXPSSC1XrlxBQEAADAwMULp0aWzZsgVhYWHw8fHBpUuXcPHiRcyfPx9PnjyBm5sbduzYAX19fU2XTVRg1q1bh0GDBuHixYsoU6aMFMrelJmZiRMnTiArKwvjx4/Hxo0beTUzydabx3CvXr3w4MED7NmzB8DrEZRGjRrhxo0baN68Oezt7TFixAjY2toynP0HONxJanF0dMTixYvx8uVLrFmzBmPGjEHHjh1RunRp1K9fHwMHDsTp06el8MaARkVBamqqNFxZtmxZPH/+HGfOnAGAXAENAEJCQvD777/Dx8cH27dvZ0AjWXr27BmA18dwRkYGgNcPQ09OTsbu3bsBAFOmTIFCocDu3bvh5+eHw4cPw8/PD1lZWZoqW6swpJHaKleujGXLlqF+/fo4cOAAjhw5Ik3LysqCmZkZ6tSpg4oVK2qwSqKCcfbsWXTu3Bm3bt0C8PqpGZ6enpg5cyYuXLgAIPf5Zrdv34aZmRl0dHRQsmTJ/7xmog+5dOkSvLy8MGvWLACAgYEBlEolSpQogfLly+PIkSPo168fdu/ejbVr16JZs2YYMmQI4uPjcejQIZibm2t4C7QDQxp9lIoVK+KHH36AEAIzZszA0aNHAUB65A1RURAdHY169erB2dkZn332GQDA1tYWPXr0wMWLFzFp0iTExMRItx549uwZJkyYgPXr12PAgAG8JQHJUlZWFkJCQnDt2jVs3LgR06dPB/C6R83MzAwBAQH4/vvvsX37dhw4cEC6bVLO2VHsGf7v8Jw0+leuXLmCESNGICkpCQsXLoS3t7emSyIqENHR0ahTpw6GDx+O77//Ptf06dOnIywsDFlZWejZsyeSkpKQkpKCo0ePYufOnbwvGslaeHg4Bg8ejD59+uD8+fNo0aIFJkyYAOD18P63334LU1NTLFu2DEKIPIf1qfDxU6d/xdHREXPnzoWtra101Q/Rp+78+fNo2LBhroA2ZcoUDBw4EAAwefJkhIWFoUuXLti3bx9u3LgBJycnREZGMqCRbOWcS9arVy80a9YMycnJ8PT0xJ9//ong4GAAgKmpKTw9PbFp0yYkJCQwoGkQe9KoQGRkZMDAwEDTZRD9a0II+Pr64vDhw7h06RIcHR0BALNnz8bs2bMRHh4OPz8/lfekpaXBxMRE5Tm1RHLy5pMEhBAQQiAkJAQxMTGYPHkyli5dij179qBLly6YOHEiAKBSpUrw8fHBqlWrNFi5dmNIIyJ6y7Nnz1CrVi1YWlpi8+bNWLVqFebOnYu1a9eiefPmKvNmZmZKVzEzpJEcXbp0Ce3bt4erqysmTZqEUqVKwcbGBnfv3oWHhwcWLFiADh06ICgoCPv370eXLl0wbtw4LF++HI0bN5b+oUL/PZ7lTUT0BiEELCwscPLkSbi7u6NatWpQKBRSQHvznlLLli1DsWLF0LNnTwBgQCPZSU9Px5IlS3D58mVcvXoVBgYGuH37Nvz9/dGpUyfMmjULe/bsQdeuXfHdd99BR0cHy5Ytg6WlJb755htNl6/1GNKISOs9fvwYiYmJUCqVqF69OhQKBSwsLBAdHY369esjNTUV5cuXVwloU6ZMwYwZM6TbcBDJkaGhIbp37w4dHR0cP34cFSpUQPPmzTF69Gj8/fffuHnzJp49e4b4+Hi4uLjA398fhoaGaNq0qaZLJ/DCASLScrGxsWjdujXatm2LNm3a4KuvvpJOrjY3N0dkZCSUSiX69OmD2NhYAMDUqVMxb948nDp1Ck5OTposnyhPN2/exOTJkwEA9erVQ7du3eDu7o5NmzbB2dkZZ86cQYcOHZCdnY34+HjcvHkTAFC+fHkEBgZKt5whzWJPGhFprejoaPj4+OCbb75B+/btsXHjRoSGhqJGjRoICAhARkYGzM3NERUVJbVVrlwZ4eHhOHLkCGrWrKnpTSDKJTs7G3///Td+/vlnpKenY86cOahbty50dXWRkZGBXr16YcGCBfjiiy/w+eefIy4uDq6urtI5lby/n3wwpBGRVrp06RLq1auHYcOGYcaMGQAAe3t7hIWF4dq1awAgXbFsYWGBqKgoODo64vDhwzh79izc3d01VTrRO505cwb79+9H79698erVK6xevRrZ2dmYP38+ateuDYVCgdDQUIwcORJpaWno1KkTXF1d83wGLWkeQxoRaR2lUonZs2fDyMgILi4uUvuaNWuQkZGB69evY86cOShdujQ6duwIAwMDWFhY4Nq1a0hKSuJQEMlSdHQ0ateuje+++w7W1tbo378/lEolwsPDAQDz589HrVq1pPmnTZsGIQQ6d+7MgCZTvAUHEWmlO3fuYNiwYXj06BFGjhyJuLg4zJ49G4MHD0aNGjXw22+/ITExEbdv34ajoyMGDRqEbt26abpsojzFxsaidu3aGDt2LKZMmSINXSYlJeHnn39GeHg4WrZsifnz5wMATp06hVmzZuHevXvYu3cvTE1NeXWyDDGkEZHWun37tvTQ6Lt372Lz5s1o1qwZgNfn9ejq6mL58uU4e/Yshg4dCmdnZw1XTJRbXFwcGjRoAGdnZ+zbt096WDrw+nmc7wpqZ8+ehbW1NZ8WI2MMaUSkFR4/foyHDx8iLi4OTk5OsLCwgI2NDRISEjBixAhcuXIFY8aMQffu3QGo3qQ2KysLeno8O4TkJ+cZs/b29hBCwN/fHz169IClpaX0QPQ3e9TWrVsHb29vhIWFabhyyg8OQhNRkRcbGws/Pz98/vnn6NatG2rWrIlmzZrhwIEDsLOzw8KFC1GhQgUsX74cq1evBgDo6+sjOzsbABjQSJZiY2NRo0YNjB07FvHx8WjRogUWLlyINWvW4OnTp9LwpRACpUqVwsCBA9GuXTtER0fjwYMHGq6e8oM9aURUpMXFxaFOnTr45ptv0LlzZ1SvXh0rV67E77//jtOnT2P9+vXo0KEDbt26haFDh+LFixfo0qUL77ZOspaVlYWgoCAYGxtLz9oEgKFDh2Lr1q0YPnw4evbsiRIlSqj0qD158gRCCJQsWVJTpZMaGNKIqMhKTU1Fly5dUKlSJSxevFjl2ZqnTp3CxIkTcezYMRw7dgyurq64e/cuevXqBRMTE6xZswbm5uYa3gKi3O7du4cHDx7Azs4OpUqVAqA6JP+hoEafDg53ElGRlZ2djVu3bqFBgwZSW84J1V5eXhg3bhwsLCywcuVKKJVKlCtXDmvWrEFYWBgDGsnShQsX4Ofnh3HjxuHGjRtSu56enjQ8v3jxYrRt2xYLFy7E77//jsePH0OhUDCgfYJ4ogURFTlpaWkwMTHBvXv3EBcXhxIlSkjTdHR0pB61xo0bw8vLC6dOnYKOjg6ys7NRtmxZDVZO9G4XLlyAj48PBg4ciJ49e8LV1VVluq6urnTBy+LFi6Grq4uJEydCX18fAwYM4L3QPkHcY0RUpJw5cwaurq64efMmSpUqBTs7O2zcuBEvXryQehIUCoXUo1a2bFmYmJgAAB+HQ7L15MkTDBgwAAMHDsScOXNUAlpaWpo0nKmvr4+MjAwAwIIFCzBo0CA0btyYAe0Txb1GREVGdHQ0fH190aZNG1SoUAGlSpVCs2bNsGrVKhw4cED64wVA+qP1+PFj6RFPPEWX5Or58+d4+fIlunbtKrUdO3YMwcHBcHFxQYcOHbBo0SIArx9nlpWVBQAIDg5GpUqVNFEyFQCGNCIqEmJiYlC3bl0MGTIEixYtkgJXcHAwfH198c0332DdunXSrQdSUlIwZcoUHDhwAAMGDADAk6pJfq5cuYKIiAgolUrExMQgISEBABAaGophw4Zhx44d8PPzQ1ZWFsLDw7F//34AvG1MUcG9SESfvISEBDRp0gRt2rTB999/D+B14Fq4cCHu3buHefPmYdiwYejXrx9sbW1hb28PXV1dXL16Fbt370blypU1vAVEuZ07dw4+Pj6YPXs2fH19ERAQgI4dO8LJyQlXr17F1KlT0apVK7i5uSEhIQHu7u64cOECmjRpounSqYAwpBHRJy87OxsODg549eoVjh49inr16mHWrFmYOXMmtm7diqpVq2LXrl346aefcPXqVTx8+BDe3t5o3rw5HBwcNF0+US7R0dGoV68eAgIC4O/vDwD4/vvv0bJlS1y7dg0tWrSQhjGFEDAxMYGLiwvKlCmjybKpgPE+aURUJFy5cgUBAQEwMDBAmTJlsGXLFoSHh6N58+ZQKpU8cZo+GTExMahTpw6GDRsm9QwDwO7du1G9enWUK1cu13smT56M9evXY//+/bCzs/svy6VCxJBGREXG5cuX8d133+HIkSOYPn06Ro4cKU1780a2RHKVkJAADw8PNG7cGOvXr5fap0+fjh9//BF79uyBk5OT1B4ZGYktW7ZgxYoViIiIkC6CoaKB/7QkoiKjcuXKWLZsGerXr4/9+/fjyJEjmi6JSC1vD90DwKxZs7BkyRL89NNPKgHtr7/+wtSpU/HPP//g8OHDDGhFEHvSiKjIyRn6FEJg8uTJqFevnqZLIsq3t4fuN2/ejN9++w3NmzdXmW/37t2oVKkSihUrhtKlS2uoWipM7EkjoiLH0dERS5Ysgb6+PkaNGoUTJ05ouiSifHN0dMTixYvx8uVL/Pbbbxg7diyaN28OIYR0a5nAwEAMGDAAJUuWZEArwhjSiKhIcnR0xNy5c2FrawsbGxtNl0OklreH7iMjI6Xnb06ZMgWzZ8/G5s2bYWFhoelSqRBxuJOIirSMjAwYGBhougyij/Lm0H1wcDD27t2LwMBAHDlyBDVr1tR0eVTIGNKIiIhk7MqVKxgxYgROnjyJp0+f4vjx4wxoWoLDnURERDLm6OiIefPmwdvbG1FRUQxoWoQ9aURERJ+AzMxM6Ovra7oM+g8xpBERERHJEIc7iYiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2IiIhIhhjSiIiIiGSIIY2oCOrbt6/0MGZ9fX2UKVMGzZo1wy+//AKlUqnp8iQHDx6EQqHAs2fPNFZDUFCQ9Fm9+bNv3z6N1fRvqfO5Ll++HG5ubjA1NYWFhQVq1KiB2bNnS9P79u2LDh06qF1DUFAQ3N3d1X4fEf2PnqYLIKLC0bJlS6xcuRLZ2dl48OABdu3ahaFDh2LDhg34+++/oaen2a9/ZmZmgS/vY+/GXq1atVyhzNLS8qOW9Sk90H3FihUYMWIElixZgoYNGyI9PR0xMTGIi4vTdGlEBACCiIqcPn36iPbt2+dq379/vwAgfvrpJ6nt2bNnYuDAgcLKykoUK1ZM+Pr6inPnzknTAwMDhZubmwgLCxO2trbC2NhYdO7cWTx9+lSa5+TJk6Jp06aiZMmSonjx4qJBgwbizJkzKusGIJYtWybatWsnTExMRO/evQUAlZ8+ffoIIYSwt7cXCxcuVHm/m5ubCAwMfOfypkyZIoQQ4u+//xYeHh7C0NBQODg4iKCgIJGZmfnOzypn+94lJiZG+Pr6CiMjI2FpaSkGDhwoXrx4IU3P+axnzpwpypYtK+zt7YUQQty5c0d06dJFWFhYCEtLS9GuXTtx48YNlWWvWLFCODs7CwMDA2FtbS38/f2lafPnzxfVq1cXJiYmwtbWVgwaNEhlvTdv3hRt2rQRFhYWwsTERDg7O4vt27eLGzduvPNzfVv79u1F37593/vZvL2siIgIIYQQY8aMEY6OjsLY2Fg4ODiISZMmiYyMDCGEECtXrsz1vpUrV0q1RUVFSet4+vSpynKfPHkiunfvLkqVKiWMjIxEpUqVxC+//PLOGomKMg53EmmRxo0bw83NDZs2bQIACCHg5+eH+/fvY8eOHThz5gw8PDzQpEkTPHnyRHrf1atX8ccff2Dr1q3YtWsXzp07B39/f2n6ixcv0KdPH0RGRuLEiRNwdHRE69at8eLFC5X1BwYGon379jh//jymTZuGjRs3AgAuXbqExMRELF68WK3teXN5/fv3x+7du9GzZ08EBAQgLi4Oy5cvx6pVq/D9999/1OeVlpaGli1bokSJEjh16hT+/PNP7Nu3D999953KfPv370d8fDz27t2Lbdu2IS0tDb6+vjAzM8Phw4dx5MgRmJmZoWXLlsjIyAAALFu2DP7+/vj6669x/vx5/P3336hUqZK0TB0dHSxZsgSxsbFYvXo1Dhw4gDFjxkjT/f39kZ6ejsOHD+P8+fOYPXs2zMzMYGdnl+/P1draGidOnMCtW7fynD5q1Ch06dIFLVu2RGJiIhITE1G3bl0AQLFixbBq1SrExcVh8eLF+Omnn7Bw4UIAQNeuXTFy5EhUq1ZNel/Xrl3z9ZlPnjwZcXFx2LlzJ+Lj47Fs2TKUKlUqX+8lKnI0nRKJqOC9qydNCCG6du0qnJychBCve9aKFy8uXr16pTJPxYoVxfLly4UQr3tTdHV1RUJCgjR9586dQkdHRyQmJua5jqysLFGsWDGxdetWqQ2AGDZsmMp8ERERAoBKr5wQ+e9Je3t59evXFzNnzlRpCw8PF2XLls2zzpzt09HREaamptKPl5eXEEKIH3/8UZQoUUKkpKRI82/fvl3o6OiI+/fvCyFef9ZlypQR6enp0jwrVqwQVapUEUqlUmpLT08XxsbGYvfu3UIIIWxsbMTEiRPfWdfb/vjjD1GyZEnptYuLiwgKCspz3nd9rm+7d++e8Pb2FgBE5cqVRZ8+fcT69etFdna2NM/7jqU3zZkzR9SsWVN6nVcPZX560tq2bSv69ev3wfURaQOek0akZYQQUCgUAIAzZ84gJSUFJUuWVJnn5cuXuHbtmvS6fPnysLW1lV7XqVMHSqUSly5dgrW1NR4+fIgpU6bgwIEDePDgAbKzs5GWlobbt2+rLNfT07NAt+Xt5Z05cwanTp1S6TnLzs7Gq1evkJaWBhMTkzyXU6VKFfz999/Sa0NDQwBAfHy8dFJ9jnr16knbXqZMGQCAi4uLynloZ86cwdWrV1GsWDGV9bx69QrXrl3Dw4cPce/ePTRp0uSd2xYREYGZM2ciLi4OycnJyMrKwqtXr5CamgpTU1MEBARg0KBB2LNnD5o2bYpOnTrB1dX1Qx+ZirJly+L48eOIjY3FoUOHcOzYMfTp0wc///wzdu3aBR2ddw+2bNiwAYsWLcLVq1eRkpKCrKwsFC9eXK3152XQoEHo1KkTzp49i+bNm6NDhw5S7x2RtmFII9Iy8fHxcHBwAAAolUqULVsWBw8ezDWfhYXFO5eRE/Jy/tu3b188evQIixYtgr29PQwNDVGnTh1paC/Hm2HnfXR0dCCEUGnL60KDt5enVCoxdepUdOzYMde8RkZG71yfgYGBylBjjjcD7dvebM+rjpo1a2LNmjW53mdlZfXe8AMAt27dQuvWrfHtt99i+vTpsLS0xJEjR/DVV19Jn8OAAQPQokULbN++HXv27EFwcDDmz5+PIUOGvHfZealevTqqV68Of39/HDlyBPXr18ehQ4fg6+ub5/wnTpzAl19+ialTp6JFixYwNzfHunXrMH/+/PeuJ2e739y3b+/XVq1a4datW9i+fTv27duHJk2awN/fH/PmzVN7u4g+dQxpRFrkwIEDOH/+PIYPHw4A8PDwwP3796Gnp4cKFSq88323b9/GvXv3YGNjAwA4fvw4dHR0ULlyZQBAZGQkQkND0bp1awBAQkICkpKSPlhPTu9Tdna2SruVlRUSExOl18nJybhx48YHl+fh4YFLly7lGbg+hrOzM1avXi31XgHA0aNHVbb9XXWsX78epUuXfmfvUoUKFbB///48g9Dp06eRlZWF+fPnS8Hmjz/+yDWfnZ0dvv32W3z77bcYP348fvrpJwwZMuSdn2t+txkAUlNTAbzeR28v5+jRo7C3t8fEiROltrfPa8vrfVZWVgCAxMRE1KhRAwBw7ty5XDVYWVmhb9++6Nu3L+rXr4/Ro0czpJFW4oUDREVUeno67t+/j7t37+Ls2bOYOXMm2rdvjzZt2qB3794AgKZNm6JOnTro0KEDdu/ejZs3b+LYsWOYNGkSTp8+LS3LyMgIffr0QXR0NCIjIxEQEIAuXbrA2toaAFCpUiWEh4cjPj4e//zzD3r06AFjY+MP1mhvbw+FQoFt27bh0aNHSElJAfD6Aofw8HBERkYiNjYWffr0ga6u7geXN2XKFPz6668ICgrChQsXEB8fj/Xr12PSpEkf8xGiR48e0rbHxsYiIiICQ4YMQa9evaShzne9r1SpUmjfvj0iIyNx48YNHDp0CEOHDsWdO3cAvL6P2Pz587FkyRJcuXIFZ8+exdKlSwEAFStWRFZWFpYuXYrr168jPDwcYWFhKusYNmwYdu/ejRs3buDs2bM4cOAAnJyc3vu5vm3QoEGYPn06jh49ilu3buHEiRPo3bs3rKysUKdOHQCvw2RMTAwuXbqEpKQkZGZmolKlSrh9+zbWrVuHa9euYcmSJfjrr79Ull2hQgXcuHED586dQ1JSEtLT02FsbAxvb2/MmjULcXFxOHz4cK59M2XKFGzZsgVXr17FhQsXsG3bNmm7iLSOZk+JI6LC0KdPH+nWB3p6esLKyko0bdpU/PLLLyonhQshRHJyshgyZIiwsbER+vr6ws7OTvTo0UPcvn1bCPG/E8BDQ0OFjY2NMDIyEh07dhRPnjyRlnH27Fnh6ekpDA0NhaOjo/jzzz9znfwPQPz111+5ap02bZqwtrYWCoVCulXE8+fPRZcuXUTx4sWFnZ2dWLVqVZ4XDuS1vF27dom6desKY2NjUbx4cVGrVi3x448/vvOzKqhbcLwtMTFR9O7dW5QqVUoYGhqKzz77TAwcOFA8f/5cmicsLExUqVJF6Ovri7Jly4ohQ4ZI0xYsWCDKli0rjI2NRYsWLcSvv/6qcjHAd999JypWrCgMDQ2FlZWV6NWrl0hKSnrv5/q2DRs2iNatW4uyZcsKAwMDYWNjIzp16iRiYmKkeR4+fCiaNWsmzMzMVE7wHz16tChZsqQwMzMTXbt2FQsXLhTm5ubS+169eiU6deokLCwspFtwCCFEXFyc8Pb2FsbGxsLd3V3s2bNHZbnTp08XTk5OwtjYWFhaWor27duL69evv3P/EBVlCiHeOvGDiOgNQUFB2Lx5c57DUkREVHg43ElEREQkQwxpRERERDLE4U4iIiIiGWJPGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEMMaQRERERyRBDGhEREZEM/R9LAmyMfH/NQwAAAABJRU5ErkJggg==",
      "text/plain": [
       "<Figure size 700x400 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "value_counts = transport_data['departure_forecast_status'].value_counts()\n",
    "plt.figure(figsize=(7, 4))\n",
    "value_counts.plot(kind='bar', rot=45)\n",
    "plt.title('Number of rows per departure forecast status')\n",
    "plt.xlabel('Departure Forecast Status')\n",
    "plt.ylabel('Number of rows')\n",
    "plt.yscale('log')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of total missing values in the dataset: 2,000,580\n",
      "Number of missing values per column: \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "date                               0\n",
       "trip_id                            0\n",
       "operator_id                        0\n",
       "operator_abbreviation              0\n",
       "operator_name                      0\n",
       "product_id                         0\n",
       "line_id                            0\n",
       "line_text                          0\n",
       "circuit_id                   1181718\n",
       "transport_type                     0\n",
       "is_additional_trip                 0\n",
       "is_cancelled                       0\n",
       "stop_id                            0\n",
       "arrival_time                  136383\n",
       "arrival_forecast              136383\n",
       "arrival_forecast_status       136383\n",
       "departure_time                136571\n",
       "departure_forecast            136571\n",
       "departure_forecast_status     136571\n",
       "is_through_trip                    0\n",
       "stop_lon                           0\n",
       "stop_lat                           0\n",
       "stop_name                          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get number of missing values per column\n",
    "print(f\"Number of total missing values in the dataset: {transport_data.isna().sum().sum():,}\")\n",
    "print(f\"Number of missing values per column: \")\n",
    "transport_data.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **🤔 Edit Inconsistent Entries**\n",
    "\n",
    "For entries where the `arrival_forecast` happens after the `departure_forecast`, we replace the `arrival_forecast` with the `departure_forecast` value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For entries where the `arrival_forecast` happens after the `departure_forecast`, we replace the `arrival_forecast` with the `departure_forecast` value.\n",
    "transport_data = transport_data \\\n",
    "    .assign(\n",
    "        arrival_forecast=\n",
    "            transport_data['arrival_forecast'].where(transport_data['arrival_forecast'] <= transport_data['departure_forecast'], \n",
    "            transport_data['departure_forecast'])\n",
    "    )"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **💾 Extract Operator Data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "OPERATOR_DATA_PATH = f'data/processed/{DATE_FILE}_operators.csv'\n",
    "transport_data[['operator_id', 'operator_abbreviation', 'operator_name']] \\\n",
    "    .set_index('operator_id') \\\n",
    "    .drop_duplicates() \\\n",
    "    .to_csv(OPERATOR_DATA_PATH)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **🗑️ Remove Unnecessary Columns**\n",
    "\n",
    "We will remove the following columns:\n",
    "- `circuit_id`: Circuits start whenever the vehicle drives out of the depot/garage and end when it drives back in again. More than half of the values are missing and we will not use it in our analysis.\n",
    "- `line_id`: We will not rely on this column to identify the line of a transportation. We will use `trip_id` instead, which accounts for the direction of travel.\n",
    "- `operator_id`: This value is contained within `trip_id`, we can safely remove it and if needed, easily extract it again.\n",
    "- `operator_abbreviation` and `operator_name`: We created a dataset that maps each operator to its abbreviation and name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "useless_columns = ['circuit_id', 'line_id', 'operator_id', 'operator_abbreviation', 'operator_name']\n",
    "transport_data = transport_data.drop(columns=useless_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                         datetime64[ns]\n",
       "trip_id                              string\n",
       "product_id                           object\n",
       "line_text                            string\n",
       "transport_type                       string\n",
       "is_additional_trip                  boolean\n",
       "is_cancelled                        boolean\n",
       "stop_id                               int32\n",
       "arrival_time                 datetime64[ns]\n",
       "arrival_forecast             datetime64[ns]\n",
       "arrival_forecast_status            category\n",
       "departure_time               datetime64[ns]\n",
       "departure_forecast           datetime64[ns]\n",
       "departure_forecast_status          category\n",
       "is_through_trip                     boolean\n",
       "stop_lon                            float64\n",
       "stop_lat                            float64\n",
       "stop_name                            string\n",
       "dtype: object"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data.dtypes"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **💾 Save Cleaned Data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "transport_data['product_id'] = transport_data['product_id'].astype('category')\n",
    "transport_data['line_text'] = transport_data['line_text'].astype('category')\n",
    "transport_data['transport_type'] = transport_data['transport_type'].astype('category')\n",
    "transport_data['stop_lon'] = transport_data['stop_lon'].astype('int32')\n",
    "transport_data['stop_lat'] = transport_data['stop_lat'].astype('int32')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                         datetime64[ns]\n",
       "trip_id                              string\n",
       "product_id                         category\n",
       "line_text                          category\n",
       "transport_type                     category\n",
       "is_additional_trip                  boolean\n",
       "is_cancelled                        boolean\n",
       "stop_id                               int32\n",
       "arrival_time                 datetime64[ns]\n",
       "arrival_forecast             datetime64[ns]\n",
       "arrival_forecast_status            category\n",
       "departure_time               datetime64[ns]\n",
       "departure_forecast           datetime64[ns]\n",
       "departure_forecast_status          category\n",
       "is_through_trip                     boolean\n",
       "stop_lon                              int32\n",
       "stop_lat                              int32\n",
       "stop_name                            string\n",
       "dtype: object"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows before cleaning: 2,347,175\n",
      "Number of rows after cleaning: 2,307,017\n",
      "Ratio of rows after cleaning: 98.3%\n"
     ]
    }
   ],
   "source": [
    "n_rows_after = transport_data.shape[0]\n",
    "print(f\"Number of rows before cleaning: {n_rows_before:,}\")\n",
    "print(f\"Number of rows after cleaning: {n_rows_after:,}\")\n",
    "print(f\"Ratio of rows after cleaning: {n_rows_after / n_rows_before:.1%}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "CLEANED_DATA_PATH = f\"data/processed/{DATE_FILE}_istdaten_cleaned.parquet\"\n",
    "transport_data.to_parquet(CLEANED_DATA_PATH)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "data_viz",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}