Data_Prep.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TSk57-gBuSqS"
      },
      "source": [
        "# TTC Subway Delay Data Prep"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6b3MuhR_uSqT"
      },
      "source": [
        "## Download Data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a3PfkFUWuSqV"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "import re\n",
        "import json\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "\n",
        "import openpyxl"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1zJNuvvh2gTV",
        "outputId": "165c1010-26a2-4d0c-da47-8cb2ca80c9bf"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#concat data from all years\n",
        "dm_data_2018 = pd.read_excel('/content/drive/MyDrive/subway_eda/ttc-subway-delay-data-2018.xlsx', sheet_name=None)\n",
        "dm_data_concat_2018 = pd.concat(dm_data_2018.values())\n",
        "dm_data_2019 = pd.read_excel('/content/drive/MyDrive/subway_eda/ttc-subway-delay-data-2019.xlsx', sheet_name=None)\n",
        "dm_data_concat_2019 = pd.concat(dm_data_2019.values())\n",
        "dm_data_2020 = pd.read_excel('/content/drive/MyDrive/subway_eda/ttc-subway-delay-data-2020.xlsx', sheet_name=None)\n",
        "dm_data_concat_2020 = pd.concat(dm_data_2020.values())\n",
        "dm_data_2021 = pd.read_excel('/content/drive/MyDrive/subway_eda/ttc-subway-delay-data-2021.xlsx', sheet_name=None)\n",
        "dm_data_concat_2021 = pd.concat(dm_data_2021.values())\n",
        "dm_data_2022 = pd.read_excel('/content/drive/MyDrive/subway_eda/ttc-subway-delay-data-2022.xlsx', sheet_name=None)\n",
        "dm_data_concat_2022 = pd.concat(dm_data_2022.values())\n",
        "frames = [dm_data_concat_2022,dm_data_concat_2021,dm_data_concat_2020,dm_data_concat_2019,dm_data_concat_2018]\n",
        "concat_frame = pd.concat(frames)\n",
        "concat_frame.reset_index(drop=True) "
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "ypbLgpmV4xl3",
        "outputId": "baa824f1-ee21-4a98-826f-f6ce6596f8ed"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            Date   Time       Day                 Station   Code  Min Delay  \\\n",
              "0     2022-01-01  15:59  Saturday   LAWRENCE EAST STATION   SRDP          0   \n",
              "1     2022-01-01  02:23  Saturday      SPADINA BD STATION   MUIS          0   \n",
              "2     2022-01-01  22:00  Saturday  KENNEDY SRT STATION TO    MRO          0   \n",
              "3     2022-01-01  02:28  Saturday      VAUGHAN MC STATION   MUIS          0   \n",
              "4     2022-01-01  02:34  Saturday        EGLINTON STATION  MUATC          0   \n",
              "...          ...    ...       ...                     ...    ...        ...   \n",
              "79520 2018-12-31  22:34    Monday          DUNDAS STATION  PUMST          0   \n",
              "79521 2018-12-31  22:36    Monday          DUNDAS STATION   SUDP          3   \n",
              "79522 2018-12-31  23:08    Monday           BLOOR STATION  MUIRS          0   \n",
              "79523 2018-12-31  23:21    Monday    QUEEN'S PARK STATION   TUSC          0   \n",
              "79524 2018-12-31  23:32    Monday        ST CLAIR STATION  MUPAA          0   \n",
              "\n",
              "       Min Gap Bound Line  Vehicle  \n",
              "0            0     N  SRT     3023  \n",
              "1            0   NaN   BD        0  \n",
              "2            0   NaN  SRT        0  \n",
              "3            0   NaN   YU        0  \n",
              "4            0     S   YU     5981  \n",
              "...        ...   ...  ...      ...  \n",
              "79520        0   NaN   YU        0  \n",
              "79521        6     N   YU     5881  \n",
              "79522        0   NaN   YU        0  \n",
              "79523        0     S   YU     5841  \n",
              "79524        0     S   YU     6061  \n",
              "\n",
              "[79525 rows x 10 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-85fe616f-d4ff-4832-8e4e-f193790e678a\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Time</th>\n",
              "      <th>Day</th>\n",
              "      <th>Station</th>\n",
              "      <th>Code</th>\n",
              "      <th>Min Delay</th>\n",
              "      <th>Min Gap</th>\n",
              "      <th>Bound</th>\n",
              "      <th>Line</th>\n",
              "      <th>Vehicle</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>15:59</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "      <td>SRDP</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3023</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:23</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>SPADINA BD STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>BD</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>22:00</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>KENNEDY SRT STATION TO</td>\n",
              "      <td>MRO</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>SRT</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:28</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>VAUGHAN MC STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:34</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "      <td>MUATC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5981</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79520</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:34</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>PUMST</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79521</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:36</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>SUDP</td>\n",
              "      <td>3</td>\n",
              "      <td>6</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5881</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79522</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:08</td>\n",
              "      <td>Monday</td>\n",
              "      <td>BLOOR STATION</td>\n",
              "      <td>MUIRS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79523</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:21</td>\n",
              "      <td>Monday</td>\n",
              "      <td>QUEEN'S PARK STATION</td>\n",
              "      <td>TUSC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5841</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79524</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:32</td>\n",
              "      <td>Monday</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "      <td>MUPAA</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>6061</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>79525 rows × 10 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-85fe616f-d4ff-4832-8e4e-f193790e678a')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-85fe616f-d4ff-4832-8e4e-f193790e678a button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-85fe616f-d4ff-4832-8e4e-f193790e678a');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Delay Code to Delay description"
      ],
      "metadata": {
        "id": "vlqrLFCgs0nJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "mapping = pd.read_excel('/content/drive/MyDrive/subway_eda/ttc-subway-delay-codes.xlsx',sheet_name = None)\n",
        "mapping_all = pd.concat(mapping.values())"
      ],
      "metadata": {
        "id": "qP72-4l4wYNn"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#desc for code\n",
        "\n",
        "\n",
        "#renaming columns\n",
        "mapping_all = mapping_all.drop(columns = ['Unnamed: 0', 'Unnamed: 1', 'Unnamed: 4', 'Unnamed: 5'], index = 0)\n",
        "mapping_all.columns = ['code_sub', 'desc_sub', 'code_srt', 'desc_srt']\n",
        "\n",
        "mapping_all = pd.concat([\n",
        "    mapping_all[['code_sub', 'desc_sub']].rename(columns = {'code_sub': 'Code', 'desc_sub': 'Desc'}), \n",
        "    mapping_all[['code_srt', 'desc_srt']].rename(columns = {'code_srt': 'Code', 'desc_srt': 'Desc'})\n",
        "])\n",
        "\n",
        "mapping_all.Code = mapping_all.Code.str.strip()\n",
        "mapping_all = mapping_all.dropna().reset_index(drop = True)\n",
        "\n",
        "mapping_all.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "2YwF_GR9KtX_",
        "outputId": "003507f9-c084-45b1-898a-59be7af6a96f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "    Code                 Desc\n",
              "0   EUAC     Air Conditioning\n",
              "1   EUAL  Alternating Current\n",
              "2  EUATC   ATC RC&S Equipment\n",
              "3   EUBK               Brakes\n",
              "4   EUBO                 Body"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-eca53b60-bced-429a-a019-2bbdd642c539\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Code</th>\n",
              "      <th>Desc</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>EUAC</td>\n",
              "      <td>Air Conditioning</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>EUAL</td>\n",
              "      <td>Alternating Current</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>EUATC</td>\n",
              "      <td>ATC RC&amp;S Equipment</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>EUBK</td>\n",
              "      <td>Brakes</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>EUBO</td>\n",
              "      <td>Body</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-eca53b60-bced-429a-a019-2bbdd642c539')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-eca53b60-bced-429a-a019-2bbdd642c539 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-eca53b60-bced-429a-a019-2bbdd642c539');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged = concat_frame.merge(\n",
        "    mapping_all, \n",
        "    on = 'Code', \n",
        "    how = 'left'\n",
        ")\n",
        "\n",
        "print(df_merged.shape)\n",
        "print(df_merged.head())"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BoyL_wHhMuRL",
        "outputId": "79df937b-0388-4a4c-dea4-eedaec2d700a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(79525, 11)\n",
            "        Date   Time       Day                 Station   Code  Min Delay  \\\n",
            "0 2022-01-01  15:59  Saturday   LAWRENCE EAST STATION   SRDP          0   \n",
            "1 2022-01-01  02:23  Saturday      SPADINA BD STATION   MUIS          0   \n",
            "2 2022-01-01  22:00  Saturday  KENNEDY SRT STATION TO    MRO          0   \n",
            "3 2022-01-01  02:28  Saturday      VAUGHAN MC STATION   MUIS          0   \n",
            "4 2022-01-01  02:34  Saturday        EGLINTON STATION  MUATC          0   \n",
            "\n",
            "   Min Gap Bound Line  Vehicle  \\\n",
            "0        0     N  SRT     3023   \n",
            "1        0   NaN   BD        0   \n",
            "2        0   NaN  SRT        0   \n",
            "3        0   NaN   YU        0   \n",
            "4        0     S   YU     5981   \n",
            "\n",
            "                                                Desc  \n",
            "0                                  Disorderly Patron  \n",
            "1  Injured or ill Customer (In Station) - Transpo...  \n",
            "2                                Miscellaneous Other  \n",
            "3  Injured or ill Customer (In Station) - Transpo...  \n",
            "4                                        ATC Project  \n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged.isnull().sum()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wPEbnU-CNGQR",
        "outputId": "32a49782-6ad1-4b03-bcd1-da43e86f20ac"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Date             0\n",
              "Time             0\n",
              "Day              0\n",
              "Station          0\n",
              "Code             0\n",
              "Min Delay        0\n",
              "Min Gap          0\n",
              "Bound        19233\n",
              "Line           269\n",
              "Vehicle          0\n",
              "Desc          1526\n",
              "dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged.groupby('Line').apply(\n",
        "    lambda x: pd.Series({\n",
        "        'pct_missing_bound': x['Bound'].isnull().mean(),\n",
        "        'n_obs': x.shape[0]})\n",
        ").sort_values(by = 'n_obs', ascending = False)[:10]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 394
        },
        "id": "oO6uu-i4OTNw",
        "outputId": "d01bac50-0632-43ba-c85a-c1ed5fe4b4cb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "         pct_missing_bound    n_obs\n",
              "Line                               \n",
              "YU                0.206263  40429.0\n",
              "BD                0.236082  31146.0\n",
              "SRT               0.320521   3226.0\n",
              "SHP               0.222182   2750.0\n",
              "YU/BD             0.994795   1537.0\n",
              "YU / BD           1.000000     66.0\n",
              "YUS               0.518519     27.0\n",
              "YU/ BD            1.000000     14.0\n",
              "999               1.000000      8.0\n",
              "BD/YU             1.000000      6.0"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-a6ea0f9a-cf2e-45eb-adbf-761659873cc5\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>pct_missing_bound</th>\n",
              "      <th>n_obs</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Line</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>YU</th>\n",
              "      <td>0.206263</td>\n",
              "      <td>40429.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>BD</th>\n",
              "      <td>0.236082</td>\n",
              "      <td>31146.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SRT</th>\n",
              "      <td>0.320521</td>\n",
              "      <td>3226.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>SHP</th>\n",
              "      <td>0.222182</td>\n",
              "      <td>2750.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>YU/BD</th>\n",
              "      <td>0.994795</td>\n",
              "      <td>1537.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>YU / BD</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>66.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>YUS</th>\n",
              "      <td>0.518519</td>\n",
              "      <td>27.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>YU/ BD</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>14.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>999</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>BD/YU</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>6.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a6ea0f9a-cf2e-45eb-adbf-761659873cc5')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-a6ea0f9a-cf2e-45eb-adbf-761659873cc5 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-a6ea0f9a-cf2e-45eb-adbf-761659873cc5');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Delay Reason Descriptions"
      ],
      "metadata": {
        "id": "g36k2GeJuTWf"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged[df_merged.Desc.isnull()].Code.value_counts()[:10]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0Jirr4PkQFpU",
        "outputId": "aec3c0c0-e033-40b4-9b5f-20428554716b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "MUNCA    1311\n",
              "PUEO       85\n",
              "TRNCA      66\n",
              "TUNCA      23\n",
              "PUEWZ      17\n",
              "MUPF        8\n",
              "XXXXX       4\n",
              "PUEME       3\n",
              "MFO         2\n",
              "SUPD        1\n",
              "Name: Code, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged = df_merged.fillna('')"
      ],
      "metadata": {
        "id": "ua8bkPyQQT3O"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Line info"
      ],
      "metadata": {
        "id": "kpqFVJyyuc4P"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged.Line.value_counts()[:30]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "j9XN-Y1AQXHo",
        "outputId": "0c9ab8be-8b2e-44b7-8414-b0791c6ae4ee"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "YU                        40429\n",
              "BD                        31146\n",
              "SRT                        3226\n",
              "SHP                        2750\n",
              "YU/BD                      1537\n",
              "                            269\n",
              "YU / BD                      66\n",
              "YUS                          27\n",
              "YU/ BD                       14\n",
              "999                           8\n",
              "BD/YU                         6\n",
              "YU-BD                         4\n",
              "YU & BD                       3\n",
              "B/D                           2\n",
              "16 MCCOWAN                    2\n",
              "SHEP                          2\n",
              "35 JANE                       2\n",
              "YUS/BD                        2\n",
              "YU - BD                       2\n",
              "YU\\BD                         1\n",
              "46 MARTIN GROVE               1\n",
              "RT                            1\n",
              "BLOOR-DANFORTH                1\n",
              "134 PROGRESS                  1\n",
              "BLOOR DANFORTH LINES          1\n",
              "985 SHEPPARD EAST EXPR        1\n",
              "22 COXWELL                    1\n",
              "100 FLEMINGDON PARK           1\n",
              "YU LINE                       1\n",
              "YONGE UNIVERSITY SERVI        1\n",
              "Name: Line, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 54
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged['line_cleaned'] = df_merged.Line\n",
        "\n",
        "df_merged = df_merged[~df_merged.line_cleaned.str.contains('^[0-9]')]\n",
        "print(df_merged.shape)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "02CpeDiOQfLk",
        "outputId": "6676e8b9-6866-4c33-9544-c7b107858559"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(79499, 12)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 468
        },
        "id": "1cS0XP5m70UP",
        "outputId": "d72c6cd6-81a1-4cf9-d619-3e8cbad3e865"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            Date   Time       Day                 Station   Code  Min Delay  \\\n",
              "0     2022-01-01  15:59  Saturday   LAWRENCE EAST STATION   SRDP          0   \n",
              "1     2022-01-01  02:23  Saturday      SPADINA BD STATION   MUIS          0   \n",
              "2     2022-01-01  22:00  Saturday  KENNEDY SRT STATION TO    MRO          0   \n",
              "3     2022-01-01  02:28  Saturday      VAUGHAN MC STATION   MUIS          0   \n",
              "4     2022-01-01  02:34  Saturday        EGLINTON STATION  MUATC          0   \n",
              "...          ...    ...       ...                     ...    ...        ...   \n",
              "79520 2018-12-31  22:34    Monday          DUNDAS STATION  PUMST          0   \n",
              "79521 2018-12-31  22:36    Monday          DUNDAS STATION   SUDP          3   \n",
              "79522 2018-12-31  23:08    Monday           BLOOR STATION  MUIRS          0   \n",
              "79523 2018-12-31  23:21    Monday    QUEEN'S PARK STATION   TUSC          0   \n",
              "79524 2018-12-31  23:32    Monday        ST CLAIR STATION  MUPAA          0   \n",
              "\n",
              "       Min Gap Bound Line  Vehicle  \\\n",
              "0            0     N  SRT     3023   \n",
              "1            0         BD        0   \n",
              "2            0        SRT        0   \n",
              "3            0         YU        0   \n",
              "4            0     S   YU     5981   \n",
              "...        ...   ...  ...      ...   \n",
              "79520        0         YU        0   \n",
              "79521        6     N   YU     5881   \n",
              "79522        0         YU        0   \n",
              "79523        0     S   YU     5841   \n",
              "79524        0     S   YU     6061   \n",
              "\n",
              "                                                    Desc line_cleaned  \n",
              "0                                      Disorderly Patron          SRT  \n",
              "1      Injured or ill Customer (In Station) - Transpo...           BD  \n",
              "2                                    Miscellaneous Other          SRT  \n",
              "3      Injured or ill Customer (In Station) - Transpo...           YU  \n",
              "4                                            ATC Project           YU  \n",
              "...                                                  ...          ...  \n",
              "79520                         Station Stairway Incident            YU  \n",
              "79521                                  Disorderly Patron           YU  \n",
              "79522  Injured or ill Customer (In Station) - Medical...           YU  \n",
              "79523                              Operator Overspeeding           YU  \n",
              "79524  Passenger Assistance Alarm Activated - No Trou...           YU  \n",
              "\n",
              "[79499 rows x 12 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-01e1d9e8-7396-4e56-9cb2-5fa9b05870aa\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Time</th>\n",
              "      <th>Day</th>\n",
              "      <th>Station</th>\n",
              "      <th>Code</th>\n",
              "      <th>Min Delay</th>\n",
              "      <th>Min Gap</th>\n",
              "      <th>Bound</th>\n",
              "      <th>Line</th>\n",
              "      <th>Vehicle</th>\n",
              "      <th>Desc</th>\n",
              "      <th>line_cleaned</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>15:59</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "      <td>SRDP</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3023</td>\n",
              "      <td>Disorderly Patron</td>\n",
              "      <td>SRT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:23</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>SPADINA BD STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>BD</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>BD</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>22:00</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>KENNEDY SRT STATION TO</td>\n",
              "      <td>MRO</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>SRT</td>\n",
              "      <td>0</td>\n",
              "      <td>Miscellaneous Other</td>\n",
              "      <td>SRT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:28</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>VAUGHAN MC STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:34</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "      <td>MUATC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5981</td>\n",
              "      <td>ATC Project</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79520</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:34</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>PUMST</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Station Stairway Incident</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79521</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:36</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>SUDP</td>\n",
              "      <td>3</td>\n",
              "      <td>6</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5881</td>\n",
              "      <td>Disorderly Patron</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79522</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:08</td>\n",
              "      <td>Monday</td>\n",
              "      <td>BLOOR STATION</td>\n",
              "      <td>MUIRS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Medical...</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79523</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:21</td>\n",
              "      <td>Monday</td>\n",
              "      <td>QUEEN'S PARK STATION</td>\n",
              "      <td>TUSC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5841</td>\n",
              "      <td>Operator Overspeeding</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79524</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:32</td>\n",
              "      <td>Monday</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "      <td>MUPAA</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>6061</td>\n",
              "      <td>Passenger Assistance Alarm Activated - No Trou...</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>79499 rows × 12 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-01e1d9e8-7396-4e56-9cb2-5fa9b05870aa')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-01e1d9e8-7396-4e56-9cb2-5fa9b05870aa button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-01e1d9e8-7396-4e56-9cb2-5fa9b05870aa');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 56
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        " ## values are mapped to a consistent naming convention\n",
        " with station at both lines add line1_line2 ex YU_BD"
      ],
      "metadata": {
        "id": "gR1i_9cTvHAT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import json\n",
        "with open(\"/content/drive/MyDrive/subway_eda/line_spelling_mapping.json\", \"r\") as f:\n",
        "    line_spelling_mapping = json.load(f)"
      ],
      "metadata": {
        "id": "L2Df8alPyMeh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pVLyqbgSuSql",
        "outputId": "7d108b70-aab0-4e8e-f419-f0876a187720",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['SRT', 'BD', 'YU', 'YU_BD', 'SHP', ''], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 58
        }
      ],
      "source": [
        "df_merged.line_cleaned = list(map(\n",
        "    lambda x: line_spelling_mapping.get(x, x), \n",
        "    df_merged.line_cleaned\n",
        "))\n",
        "\n",
        "df_merged.line_cleaned.unique()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 468
        },
        "id": "Son6e0Cn66Ub",
        "outputId": "4f8050c8-2a64-4683-e023-480bc306ee84"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            Date   Time       Day                 Station   Code  Min Delay  \\\n",
              "0     2022-01-01  15:59  Saturday   LAWRENCE EAST STATION   SRDP          0   \n",
              "1     2022-01-01  02:23  Saturday      SPADINA BD STATION   MUIS          0   \n",
              "2     2022-01-01  22:00  Saturday  KENNEDY SRT STATION TO    MRO          0   \n",
              "3     2022-01-01  02:28  Saturday      VAUGHAN MC STATION   MUIS          0   \n",
              "4     2022-01-01  02:34  Saturday        EGLINTON STATION  MUATC          0   \n",
              "...          ...    ...       ...                     ...    ...        ...   \n",
              "79520 2018-12-31  22:34    Monday          DUNDAS STATION  PUMST          0   \n",
              "79521 2018-12-31  22:36    Monday          DUNDAS STATION   SUDP          3   \n",
              "79522 2018-12-31  23:08    Monday           BLOOR STATION  MUIRS          0   \n",
              "79523 2018-12-31  23:21    Monday    QUEEN'S PARK STATION   TUSC          0   \n",
              "79524 2018-12-31  23:32    Monday        ST CLAIR STATION  MUPAA          0   \n",
              "\n",
              "       Min Gap Bound Line  Vehicle  \\\n",
              "0            0     N  SRT     3023   \n",
              "1            0         BD        0   \n",
              "2            0        SRT        0   \n",
              "3            0         YU        0   \n",
              "4            0     S   YU     5981   \n",
              "...        ...   ...  ...      ...   \n",
              "79520        0         YU        0   \n",
              "79521        6     N   YU     5881   \n",
              "79522        0         YU        0   \n",
              "79523        0     S   YU     5841   \n",
              "79524        0     S   YU     6061   \n",
              "\n",
              "                                                    Desc line_cleaned  \n",
              "0                                      Disorderly Patron          SRT  \n",
              "1      Injured or ill Customer (In Station) - Transpo...           BD  \n",
              "2                                    Miscellaneous Other          SRT  \n",
              "3      Injured or ill Customer (In Station) - Transpo...           YU  \n",
              "4                                            ATC Project           YU  \n",
              "...                                                  ...          ...  \n",
              "79520                         Station Stairway Incident            YU  \n",
              "79521                                  Disorderly Patron           YU  \n",
              "79522  Injured or ill Customer (In Station) - Medical...           YU  \n",
              "79523                              Operator Overspeeding           YU  \n",
              "79524  Passenger Assistance Alarm Activated - No Trou...           YU  \n",
              "\n",
              "[79499 rows x 12 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-34dd108a-93a1-42bc-857e-53e7a5761d98\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Time</th>\n",
              "      <th>Day</th>\n",
              "      <th>Station</th>\n",
              "      <th>Code</th>\n",
              "      <th>Min Delay</th>\n",
              "      <th>Min Gap</th>\n",
              "      <th>Bound</th>\n",
              "      <th>Line</th>\n",
              "      <th>Vehicle</th>\n",
              "      <th>Desc</th>\n",
              "      <th>line_cleaned</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>15:59</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "      <td>SRDP</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3023</td>\n",
              "      <td>Disorderly Patron</td>\n",
              "      <td>SRT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:23</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>SPADINA BD STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>BD</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>BD</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>22:00</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>KENNEDY SRT STATION TO</td>\n",
              "      <td>MRO</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>SRT</td>\n",
              "      <td>0</td>\n",
              "      <td>Miscellaneous Other</td>\n",
              "      <td>SRT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:28</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>VAUGHAN MC STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:34</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "      <td>MUATC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5981</td>\n",
              "      <td>ATC Project</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79520</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:34</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>PUMST</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Station Stairway Incident</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79521</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:36</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>SUDP</td>\n",
              "      <td>3</td>\n",
              "      <td>6</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5881</td>\n",
              "      <td>Disorderly Patron</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79522</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:08</td>\n",
              "      <td>Monday</td>\n",
              "      <td>BLOOR STATION</td>\n",
              "      <td>MUIRS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Medical...</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79523</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:21</td>\n",
              "      <td>Monday</td>\n",
              "      <td>QUEEN'S PARK STATION</td>\n",
              "      <td>TUSC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5841</td>\n",
              "      <td>Operator Overspeeding</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79524</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:32</td>\n",
              "      <td>Monday</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "      <td>MUPAA</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>6061</td>\n",
              "      <td>Passenger Assistance Alarm Activated - No Trou...</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>79499 rows × 12 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-34dd108a-93a1-42bc-857e-53e7a5761d98')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-34dd108a-93a1-42bc-857e-53e7a5761d98 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-34dd108a-93a1-42bc-857e-53e7a5761d98');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 59
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## replacing null in line values using line to station mapping"
      ],
      "metadata": {
        "id": "ZJ9GlI47vsOu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import json\n",
        "with open(\"/content/drive/MyDrive/subway_eda/line_station_mapping.json\", \"r\") as f:\n",
        "    line_station_mapping = json.load(f)"
      ],
      "metadata": {
        "id": "xpEdmf_eppl_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GeWqRNeUuSqm"
      },
      "outputs": [],
      "source": [
        "df_merged.line_cleaned = list(map(\n",
        "    lambda x, y: line_station_mapping.get(y, x) if x == '' else x, \n",
        "    df_merged.line_cleaned, \n",
        "    df_merged.Station\n",
        "))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "dFZP0HHYuSqn",
        "outputId": "c024fb85-68e4-4048-aafb-fba231f5ae0e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "WILSON CARHOUSE           10\n",
              "GREENWOOD YARD             9\n",
              "GREENWOOD CARHOUSE         5\n",
              "INGLIS BUILDING            5\n",
              "DANFORTH DIVISION          5\n",
              "YONGE-UNIVERSITY AND B     4\n",
              "DAVISVILLE YARD            4\n",
              "MCBRIEN BUILDING           3\n",
              "GREENWOOD SHOPS            3\n",
              "TRANSIT CONTROL            3\n",
              "TRANSIT CONTROL CENTRE     2\n",
              "GO PROTOCOL                2\n",
              "GREENWOOD SHOP             2\n",
              "DUNCAN SHOP                2\n",
              "GUNN BUILDING              2\n",
              "HARVEY SHOP                2\n",
              "WILSON HOSTLER             2\n",
              "WILSON YARD                2\n",
              "LOWER BAY                  1\n",
              "UNION TO MAIN STREET       1\n",
              "DON MLLS STATION           1\n",
              "EGLINTON BUS TERMINAL      1\n",
              "GO PROTOCOL - BARRIE L     1\n",
              "KING AND BATHURST          1\n",
              "DAVISVILLE YARD - TUNN     1\n",
              "SCARBOROUGH CTR STATIO     1\n",
              "UNION, MAIN, KENNEDY S     1\n",
              "SUBWAY OPS BUILDING        1\n",
              "DUNCAN SHOPS               1\n",
              "40 CARL HALL ROAD UNIT     1\n",
              "Name: Station, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 62
        }
      ],
      "source": [
        "df_merged[df_merged.line_cleaned == ''].Station.value_counts()[:30]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oGUfTCZuuSqo",
        "outputId": "0912c99d-529c-4ecb-f16f-865ab083b206",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(79393, 12)\n",
            "        Date   Time       Day                 Station   Code  Min Delay  \\\n",
            "0 2022-01-01  15:59  Saturday   LAWRENCE EAST STATION   SRDP          0   \n",
            "1 2022-01-01  02:23  Saturday      SPADINA BD STATION   MUIS          0   \n",
            "2 2022-01-01  22:00  Saturday  KENNEDY SRT STATION TO    MRO          0   \n",
            "3 2022-01-01  02:28  Saturday      VAUGHAN MC STATION   MUIS          0   \n",
            "4 2022-01-01  02:34  Saturday        EGLINTON STATION  MUATC          0   \n",
            "\n",
            "   Min Gap Bound Line  Vehicle  \\\n",
            "0        0     N  SRT     3023   \n",
            "1        0         BD        0   \n",
            "2        0        SRT        0   \n",
            "3        0         YU        0   \n",
            "4        0     S   YU     5981   \n",
            "\n",
            "                                                Desc line_cleaned  \n",
            "0                                  Disorderly Patron          SRT  \n",
            "1  Injured or ill Customer (In Station) - Transpo...           BD  \n",
            "2                                Miscellaneous Other          SRT  \n",
            "3  Injured or ill Customer (In Station) - Transpo...           YU  \n",
            "4                                        ATC Project           YU  \n"
          ]
        }
      ],
      "source": [
        "#remove null remaning after merging mapping\n",
        "df_merged = df_merged[df_merged.line_cleaned != ''].reset_index(drop = True)\n",
        "\n",
        "print(df_merged.shape)\n",
        "print(df_merged.head())"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Station name cleaning up"
      ],
      "metadata": {
        "id": "UdsQl1CZw38P"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged['Station'].unique()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Gbw2kTNmr4NG",
        "outputId": "1088aee4-b617-4202-f3b2-d6719ff47aee"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['LAWRENCE EAST STATION', 'SPADINA BD STATION',\n",
              "       'KENNEDY SRT STATION TO', 'VAUGHAN MC STATION', 'EGLINTON STATION',\n",
              "       'QUEEN STATION', 'DAVISVILLE STATION', 'ST PATRICK STATION',\n",
              "       'PAPE STATION', 'WILSON STATION', 'KENNEDY BD STATION',\n",
              "       'FINCH STATION', 'YONGE BD STATION', 'ST ANDREW STATION',\n",
              "       'MAIN STREET STATION', 'HIGH PARK STATION', 'GREENWOOD STATION',\n",
              "       'COXWELL STATION', 'WELLESLEY STATION', 'SHEPPARD WEST STATION',\n",
              "       'BLOOR STATION', 'UNION STATION', 'ST GEORGE YUS STATION',\n",
              "       'RUNNYMEDE STATION', 'ROSEDALE STATION', 'ISLINGTON STATION',\n",
              "       'SHERBOURNE STATION', 'HIGHWAY 407 STATION', 'KIPLING STATION',\n",
              "       'YONGE/UNIVERSITY AND B', 'ST GEORGE BD STATION',\n",
              "       'CHRISTIE STATION', 'BESSARION STATION', 'MCCOWAN STATION',\n",
              "       'ST CLAIR WEST STATION', 'YORKDALE STATION', 'BATHURST STATION',\n",
              "       'WARDEN STATION', 'COLLEGE STATION', 'DUPONT STATION',\n",
              "       'KEELE STATION', 'LAWRENCE WEST STATION', 'LANSDOWNE STATION',\n",
              "       'NORTH YORK CTR STATION', 'DUNDAS STATION', 'DUNDAS WEST STATION',\n",
              "       'ROYAL YORK STATION', 'FINCH WEST STATION', 'KENNEDY SRT STATION',\n",
              "       'ELLESMERE STATION', 'EGLINTON STATION (MIGR',\n",
              "       'SPADINA YUS STATION', 'KING STATION', 'OSSINGTON STATION',\n",
              "       'PIONEER VILLAGE STATIO', \"QUEEN'S PARK STATION\",\n",
              "       'YORK MILLS STATION', 'BROADVIEW STATION', 'OSGOODE STATION',\n",
              "       'DUFFERIN STATION', 'YONGE-UNIVERSITY AND B', 'LAWRENCE STATION',\n",
              "       'BAYVIEW STATION', 'LESLIE STATION', 'SHEPPARD-YONGE STATION',\n",
              "       'MIDLAND STATION', 'EGLINTON WEST STATION', 'WOODBINE STATION',\n",
              "       'DONLANDS STATION', 'SHEPPARD STATION', 'VICTORIA PARK STATION',\n",
              "       'CASTLE FRANK STATION', 'DOWNSVIEW PARK STATION',\n",
              "       'YORK UNIVERSITY STATIO', 'LAWRENCE EAST TO KENNE',\n",
              "       'UNION STATION TO ST AN', 'SUMMERHILL STATION', 'GREENWOOD YARD',\n",
              "       'ST CLAIR STATION', 'DAVISVILLE YARD', 'WILSON YARD',\n",
              "       'OLD MILL STATION', 'YONGE-UNIVERSITY/BLOOR', 'MUSEUM STATION',\n",
              "       'SCARBOROUGH CTR STATIO', 'SCARBOROUGH RAPID TRAN',\n",
              "       'TORONTO TRANSIC COMMIS', 'TORONTO TRANSIT COMMIS', 'BAY STATION',\n",
              "       'DON MILLS STATION', 'GLENCAIRN STATION', 'SYSTEM WIDE',\n",
              "       'SHEPPARD DISTRUBTION', 'CHESTER STATION', 'BLOOR DANFORTH LINE',\n",
              "       'YONGE UNIVERSITY LINE', 'BAYVIEW TO BESSARION',\n",
              "       'YONGE-UNIVERSITY SUBWA', 'MCCOWAN YARD', 'JANE STATION',\n",
              "       'TRANSIT CONTROL CENTRE', 'WILSON TO ST CLAIR STA',\n",
              "       'SHEPPARD WEST TO UNION', 'MAINLINE STORAGE',\n",
              "       'SHEPPARD WEST TO ST CL', 'KIPLING TO JANE',\n",
              "       'YONGE-UNIVERSITY LINE', 'YORK MILLS (APPROACHIN', 'GREENWOOD WYE',\n",
              "       'MCBRIEN BUILDING', 'UNION TO KING', 'TRANSIT CONTROL',\n",
              "       'NORTH HOSTLER', 'WILSON NORTH HOSTLER', 'MCCOWAN CARHOUSE',\n",
              "       'MIDLAND TO SCARBOROUGH', 'ST CLAIR TO FINCH STAT',\n",
              "       \"QUEEN'S QUAY - UNION\", 'KEELE YARD', 'DAVISVILLE CARHOUSE',\n",
              "       'WILSON CARHOUSE', 'MCCOWAN TO KENNEDY STA', 'DANFORTH DIVISION',\n",
              "       'SHEPPARD LINE', 'FINCH STATION TO EGLIN',\n",
              "       'EGLINTON PSUDO STATION', 'N/O SUMMERHILL TO S/O',\n",
              "       'DOWNVIEW PARK STATION', 'MUSEUM TO EGLINTON STA',\n",
              "       'UNION STATION TO KING', 'UNION STATION TOWARDS',\n",
              "       'YONGE-UNIVERSITY-SPADI', 'N/B TOWARDS FINCH',\n",
              "       'WARDEN AND ST CLAIR', 'BROADVIEW CENTRE TRACK', 'GUNN BUILDING',\n",
              "       'VMC TO LAWRENCE', 'EGLINTON (MIGRATION)',\n",
              "       'YONGE-UNIVERSITY SPADI', 'BLOOR DANFORTH SUBWAY',\n",
              "       'UNION STATION(TOWARD K', 'LINE 3 - SCARBOROUGH R',\n",
              "       'ST. GEORGE STATION', 'YONGE UNIVERSITY SPADI',\n",
              "       'GLENCARIN STATION', 'SPADINA STATION', 'YONGE STATION',\n",
              "       'UNION STATION (TO KING', 'LAWRENCE TO ST CLAIR S',\n",
              "       'BAY LOWER STATION', 'SCARBOROUGH CENTRE STA',\n",
              "       'SHEPPARD-YONGE LINE 4', 'WILSON SOUTH HOSTLER', 'KING TO COLLEGE',\n",
              "       'COLLEGE TO KING', 'DUFFERIN AND BLOOR', 'ST CLAIR STATION (APPR',\n",
              "       'YONGE - UNIVERSITY SUB', 'YONGE STATION TO GREEN',\n",
              "       'GREENWOOD PORTAL', 'MIGRATION POINT EGLINT',\n",
              "       'LAWRECNE WEST TO FINCH', 'BLOOR-DANFORTH LINE',\n",
              "       'LAWRENCE WEST TO FINCH', 'SHEPPARD-YONGE AND DON',\n",
              "       'GLENAYR EMERGENCY EXIT', 'BROADVIEW TO WOODBINE',\n",
              "       'KENNEDY SRT STATION LA', 'VICTORIA PARK TO WARDE',\n",
              "       'WOODBINE TO BROADVIEW', 'CHESTER CENTRE TRACK',\n",
              "       'ST ANDREW STATON', 'GREENWOOD CARHOUSE', 'CLOSURE FINCH TO ST CL',\n",
              "       'BAY LOWER', 'LINE 1', 'YONGE UNIVERSITY/BLOOR',\n",
              "       'MCCOWAN YARD - 3 TRACK', 'ST GEORGE YU STATION',\n",
              "       'FINCH TO EGLINTON STAT', 'WILSON DIVISION',\n",
              "       'YORK MILLS CENTRE TRAC', 'YONGE / UNIVERSITY - B',\n",
              "       'FINCH TO SHEPPARD STAT', 'YONGE/UNIVERSITY-SPADI',\n",
              "       'YONGE/UNIVERSITY LINE', 'EGLINTON STATION (ENTE',\n",
              "       'EGLINTON STATION (LEAV', 'WARDEN TO KENNEDY',\n",
              "       'BLOOR-DANFORTH SUBWAY', 'QUEENS PARK STATION',\n",
              "       'DAVISVILLE BUILD UP', \"QUEEN'S QUAY STATION\",\n",
              "       'EGLINTON STATION (APPR', 'KENNEDY SRT TO MCCOWA', 'YU & BD',\n",
              "       'LINE 3 SCARBOROUGH RT', 'GREENWOOD PORTAL (EXIT',\n",
              "       'KIPLING TO KENNEDY', 'BLOOR TO COLLEGE', 'YONGE UNIVERSITY SUBWA',\n",
              "       'KENNEDY TO MCCOWAN - L', 'EGLINTON STATION TO FI',\n",
              "       'EGLINTON - FINCH STATI', 'BEDFORD SUBSTATION',\n",
              "       'FINCH TO ST CLAIR STAT', 'YONGE AUTO ENTRANCE',\n",
              "       'OSSINGTON CENTRE TRACK', 'SHEPPARD-YONGE (TAIL T',\n",
              "       'SUMMERHILL TO BLOOR ST', 'SHEPPARD-YONGE TAIL',\n",
              "       'DAVISVILLE BUILDUP', 'YORK MILLS CENTER TRAC',\n",
              "       'SHEPPARD TAIL TRACK #2', 'FINCH WEST TO LAWRENCE', 'ZONE 1',\n",
              "       'WILSON HOSTLER', 'KIPLING TO ISLINGTON', 'UNION TO ST ANDREW',\n",
              "       'SHEPPARD WEST TO LAWRE', 'YONGE UNIVERSITY AND B',\n",
              "       'SPADINA YU STATION', 'SHEPPARD WEST TO WILSO',\n",
              "       'SHEPPARD WEST - WILSON', 'YONGE- UNIVERSITY SUBW',\n",
              "       'YONGE UNIVERSTIY SUBWA', 'WILSON TO SHEPPARD WES',\n",
              "       'YONGE AND SHEPPARD', 'BLOOR DANFORTH LINE ',\n",
              "       'YONGE UNVERSITY SUBWAY', 'YONGE-UNIVERSITY / BLO',\n",
              "       'KIPLING - ISLIINGTON S', 'GREENWOOD CAR HOUSE',\n",
              "       'MUSEUM STATION TO OSGO', 'YOONGE-UNIVERSITY SUBW',\n",
              "       'YONGE UNIVERSTY SUBWAY', 'KENNEDY TO KIPLING STA',\n",
              "       'PIONEER VILLAGE TO VAU', 'KILPING STATION',\n",
              "       'VMC STATION TO PIONEER', 'SRT LINE', 'DAVISVILLE BUILD-UP',\n",
              "       'SCARBOROUGH RAPID TRA', 'ST. PATRICK STATION',\n",
              "       'LESLIE STATION (LEAVIN', 'TORONTO TRANSIT CONTRO',\n",
              "       'GREENWOOD SHOP', 'KENNEDY STATION', 'BLOOR TO SHEPPARD STAT',\n",
              "       'KIPLING TO JANE STATIO', 'YONGE UNIVERSITY BLOOR',\n",
              "       'LAWERENCE STATION', 'ST.CLAIR WEST TO ST.A',\n",
              "       'ST CLAIR WEST TO ST AN', 'BAYVIEW TO SHEPAPRD/YO',\n",
              "       'KING STATION TO ST CLA', 'ST GEORGE STATION TO B',\n",
              "       'ST CLAIR WEST TO KING', 'LINE 3 SCARBOROUGH SRT',\n",
              "       'N/O QUEEN TO MARKDALE', 'ST. CLAIR WEST TO KING', 'LINE 3',\n",
              "       'LINE 1 AND LINE 2 AND', 'ST GEORGE STATION TO W',\n",
              "       'LINE 1, LINE 2 AND LIN', 'JANE STATION TO OLD MI',\n",
              "       'ST GEORGE TO BROADVIEW', 'VMC TO ST GEORGE STATI',\n",
              "       'KING STATION TO SPADIN', 'SPADINA STATION TO KIN',\n",
              "       'ST. CLAIR TO FINCH STA', 'GREENWOOD STATION (APP',\n",
              "       'DAVISVILLE BUILDD UP', 'SCARBORUOGH RAPID TRAN',\n",
              "       'GREENWOOD AND DANFORTH', 'BLOOR VIADUCT', 'KENNEDY TO MCCOWAN',\n",
              "       'KING TO EGLINTON STATO', 'LAWRENCE WEST TO EGLIN',\n",
              "       'KING TO ST GEORGE STAT', 'KING STATION TO ST GEO',\n",
              "       'YONGE/UNIVERSITY/SPADI', 'KIPLING STATION TO ISL',\n",
              "       'YONGE AND STEELES', 'FINCH TO ROSEDALE', 'FINCH WEST TO WILSON',\n",
              "       'KING TO SPADINA STATIO', 'SPADINA TO KING STATIO',\n",
              "       'STC TO MCCOWAN STATION', 'OSSIGNTON STATION',\n",
              "       'KIPLING STATION TO JAN', 'WILSON TO LAWRENCE WES',\n",
              "       'KING TO EGLINTON STATI', 'SHEPPARD WYE', 'VMC TO PIONEER VILLAGE',\n",
              "       'LYTTON EMERGENCY EXIT', 'KING STATION TO EGLINT', 'WILSON GARAGE',\n",
              "       'ST GEORGE TO SHEPPARD', 'SHEPPARD TO BLOOR',\n",
              "       'EGLINTON STATION TO VA', 'ISLINGTON TO KIPLING S',\n",
              "       'LAWRENCE WEST CENTRE', 'EGLINTON STATION APPRO',\n",
              "       'VMC TO EGLINTON STATIO', 'EGLINTON STATION - MIG',\n",
              "       'YOUNGE UNIVERSITY SPAD', 'OLD MILL TO ROYAL YORK',\n",
              "       'EGLINTON TO SHEPPARD S', 'FINCH STATION TO ST CL',\n",
              "       'SPADINA BD STATON', 'EGLINTON STATION TO SH',\n",
              "       'KENNEDY SRT TO LAWREN', 'LAWRENCE EAST - KENNED',\n",
              "       'LAWRENCE EAST - KENNE', 'KENNEDY SRT - LAWRENCE',\n",
              "       'KENNEDY - LAWRENCE EAS', 'WILSON TRACK AND STRUC',\n",
              "       'KENNEDY SRT TO LAWRENC', 'YUS LINE', 'ZONE 2',\n",
              "       'EGLINTON STATION ( MIG', 'EGLINTON STATION TO VM',\n",
              "       'WILSON DIVISION - 2ND', 'YONGE AND BLOOR', 'YUS AND BD SUBWAY',\n",
              "       'LINE 1 YUS', 'BLOOR HAYDEN ENTRANCE', 'YONGE SHEP STATION',\n",
              "       'YONGE-UNIVERSITY & BLO', 'KENNEDY BD TO KIPLING',\n",
              "       'NORTH YORK CENTRE STAT', 'DAVISVILLE TO EGLINTON',\n",
              "       'DUPONT TO ST CLAIR WES', 'SHEPPARD SUBWAY LINE',\n",
              "       'YONGE/UNIVERSITY SUBWA', 'GREENWOOD COMPLEX',\n",
              "       'UNION TO KING STATION', 'ST ANDREW TO SPADINA',\n",
              "       'MUSEUM TO FINCH STATIO', 'SPADINA TO ST ANDREW',\n",
              "       'EASTBOUND - BETWEEN SH', 'YUS/BD/SHEPPARD SUBWAY',\n",
              "       'OSSINGTON TO KEELE STA', 'YONGE SHP STATION',\n",
              "       'SHEPPARD - YONGE STATI', 'GLENCAIRN', 'CLOSURE- KEELE TO OSSI',\n",
              "       'KEELE TO OSSINGTON', 'KING STATION - COMMERC',\n",
              "       'CHANGE OVERS / GENERAL', 'SPADINA TO OSGOODE',\n",
              "       'FINCH TO QUEEN STATION', 'N/O YORK MILLS TO DAVI',\n",
              "       'OSSINGTON STAITON', 'S/O SHEPPARD TO DAVISV',\n",
              "       'YORK MILLS TO ST CLAIR', 'GREENWOOD - TRACK TRAI',\n",
              "       'DUNDAS WEST STATION -', 'ST CALIR STATION',\n",
              "       'SHEPPARD/YONGE STATION', 'ST CLAIR TO LAWRENCE S',\n",
              "       'SHERBOURNE AND BLOOR', 'LANSDOWN STATION', 'ATC AREA',\n",
              "       'WILSON HOSTLER ', 'TETRA RADIO ZONE YU/SH',\n",
              "       'WARDEN TO VICTORIA PAR', 'QUEEN ALBERT', 'SPADINA STATION YUS',\n",
              "       'BLOOR TO EGLINTON', 'LAWRENCE AND YONGE', 'YONGE- UNIVERSITY AND',\n",
              "       'ST CLAIR TO UNION STAT', 'YONGE - SHEPPARD (LEAV',\n",
              "       'BLOOR INTERLOCKING', 'WILSON CAR HOUSE', 'COXWELL TO KENNEDY STA',\n",
              "       'EGLINTON TO KING STATI', 'UNION AND KENNEDY STAT',\n",
              "       'YONGE-UNIVERISTY AND B', 'BLOOR TO LAWRENCE STAT',\n",
              "       'LAWRENCE TO BLOOR STAT', 'FINCH TO EGLINTON',\n",
              "       'EGLINTON TO BLOOR STAT', 'BLOOR SOUTH', 'YONGE/UNIVESITY AND BL',\n",
              "       'BROADVIEW TO ST. GEORG', 'DUFFERIN TO KIPLING ST',\n",
              "       'KEELE STATION TO KIPLI', 'ST CLAIR TO SHEPPARD S',\n",
              "       'ST CLAIR TO BLOOR STAT', 'MCCOWAN STATION NB',\n",
              "       'ST GEORGE/BEDFORD', 'UNION STATION TO ST. A',\n",
              "       'MCCOWAN STATION TO KEN', 'YONGE SHEPPARD STATION',\n",
              "       'WILSON YARD NORTH HOST', 'SUBWAY CLOSURE - JANE',\n",
              "       'KIPLING - KENNEDY', 'ST GEORGE TO WOODBINE',\n",
              "       'YORK MILLS STATION (LE', 'KIPLING TO ROYAL YORK',\n",
              "       'YORK MILLS STATION (AP', 'ISLINGTON CENTRE TRACK',\n",
              "       'ST CLAIR STATION (LEAV', 'LEAVING SHEPPARD/YONGE',\n",
              "       'BLOOR TO ST ANDREW STA', 'YONGE AND QUEEN', 'BRUNSWICK AND BLOOR',\n",
              "       'RAMPS BAY LOWER', 'SHEPPARD- YONGE STATIO',\n",
              "       'SHEPPARD WEST TO FINCH', 'VAUGHAN MC STATIO',\n",
              "       'FINCH WEST - SHEPPARD', 'BLOOR AND ABERFOYLE CR', 'YUS/BD',\n",
              "       'YORK MILLS STATION [', 'SHEPPARD TO EGLINTON S',\n",
              "       'LAWRENCE STATION TO YO', 'ROYAL YORK TO ISLINGTO',\n",
              "       'GREENWOOD GATEHOUSE', 'MARKDALE EE', 'LEAVING DON MILLS',\n",
              "       'ST. ANDREW STATION', 'YONGE / UNIVERSITY - L',\n",
              "       'LAWRENCE TO ST CLAIR', 'EGLINTON TO SHEPPARD',\n",
              "       'EARLY CLOSURE EGLINTON', 'KENNEDY STATION TO WOO',\n",
              "       'QUEEN TO FINCH STATION', 'ENGLINTON TO SHEPPARD',\n",
              "       'QUEEN TO KING STATIONS', 'PRINCE EDWARD SUBSTATI',\n",
              "       'ST GEORGE TO BLOOR STA', 'KENNEDY TO KIPLING',\n",
              "       'DAVISVILLE YARD - T&S', 'ST. CLAIR STATION',\n",
              "       'DAVISVILLE YARD (APPRO', 'ST. CLAIR WEST STATION',\n",
              "       'FICNH STATION', 'KIPLING STATION TO COX', 'LINE 4', 'LINE 2 (BD)',\n",
              "       'SHEPPARD - EGLINTON ST', 'ST CLAIR WEST TO VMC S',\n",
              "       'BLOOR TO MUSUEM STATIO', 'EGLINTON TO DAVISVILLE',\n",
              "       'SHEPPARD-YONGE TAIL TR', 'LEAVING SHEPPARD-YONGE',\n",
              "       'KIPLING STATION TO KEN', 'ST GEORGE TO BAY STATI',\n",
              "       'ST GEORGE AND BROADVIE', 'WILSON SUBWAY YARD',\n",
              "       'YORK MILLS TO LAWRENCE', 'MC COWAN STATION', 'YORK MILLS CENTRE',\n",
              "       'BROADVIEW STATION TO W', 'ST GEORGE STATION', 'SYSTEMWIDE',\n",
              "       'N/O WILSON TO S/O SHEP', 'ST GEORGE BEDFORD',\n",
              "       'SCARBOROUGH RT LINE', 'YONGE/UNIVERSITY/ BLOO',\n",
              "       'SCARB CTR STATION', 'WELLBECK EMERGENCY',\n",
              "       'GREENWOOD TRACK AND ST', 'GREENWOOD SHOPS',\n",
              "       'NORTHBOUND TO LAWRENCE', 'SPADINA TO UNION STATI',\n",
              "       'ST CLAIR WEST TO UNION', 'BATHUSRT STATION',\n",
              "       'SYSTEM - WIDE IN THE O', 'LAWRENCE SUB STATION',\n",
              "       'BROADVIEW TO YONGE', 'MCCOWAN PARKING LOT', 'DAVISVILLE HOSTLER',\n",
              "       'UNION TO ST CLAIR WEST', 'SHEPPARD WEST TO DOWNS',\n",
              "       'YONGE BD STATION (LEAV', 'DUPONT MIGRATION POINT',\n",
              "       'UNION TO BLOOR STATION', 'BLOOR TO OSGOODE STATI',\n",
              "       'WILSON - ST CLAIR WEST', 'FINCH STATION TO DUPON',\n",
              "       'ST ANDREW TO BLOOR STA', 'KENNEDY TO VICTORIA PA',\n",
              "       'VICTORIA PARK TO KENNE', 'BLOOR TO EGLINTON STAT',\n",
              "       'DAVISVILLE TO N/O BLOO', 'GLENAYR EE', 'N/O DAVISVILLE TO N/O',\n",
              "       'WILSON STATION TO VMC', 'FINCH - NORTH YORK CEN',\n",
              "       'BLOOR VIA DUCT', 'ST CLAIR TO UNION', 'PIONNER VILLAGE STATIO',\n",
              "       'YUS/BD/SEPPARD SUBWAY', 'WILSON TO ST CLAIR WES',\n",
              "       'KEELE TO HIGH PARK STA', 'KIPLING TO HIGH PARK',\n",
              "       'KIPLING TO KENNEDY STN', 'KIPLING TO COXWELL',\n",
              "       'HIGH PARK TO KEELE', 'YORKDALE TO FINCH STAT', 'UNION TO FINCH',\n",
              "       'S/O EGLINTON TO ST CLA', \"QUEEN'S PARK TO ST CLA\",\n",
              "       'EGLINTON TO LAWRENCE', 'KEELE TO HIGH PARK', 'OSSINGTION STAITON',\n",
              "       'KING COMMERCE', 'SUBWAY CLOSURE - WILSO', 'DAVISVILLE BUID-UP',\n",
              "       'UNION STATION TO ST CL', 'UNION, DOWNSVIEW PARK,',\n",
              "       'UNION, KIPLING, ROYAL', 'HIGH PARK TO KEELE STA',\n",
              "       \"QUEEN'S QUAY ELEVATOR\", 'WILSON STATION TO YORK', 'BLOOR SATION',\n",
              "       'ELLIS BRIDGE', 'WILSON STATION- HOSTLE', 'WILSON SUB STATION',\n",
              "       'BROADVIEW TO KIPLING S', 'FINCH TO LAWRENCE STAT', 'E13',\n",
              "       'MCCOWAN CAR HOUSE', 'YONGE-UNIVERSITY AND',\n",
              "       'YONGE BD STATION BOOTH', 'UNION STATION (TO ST A',\n",
              "       'ST ANDREW TO BLOOR ST', 'KING TO UNION', 'KEELE TO DUNDAS WEST',\n",
              "       'UNION TO FINCH STATION', 'ST GEORGE STATION TO G',\n",
              "       'CHESTER TO WARDEN', 'ST GEORGE TO GREENWOOD',\n",
              "       'ISLINGTON TO PRINCE ED', 'YONGE SHEPPARD SATATIO',\n",
              "       'YONGE TO COXWELL STATI', 'KIPLING TO KENNEDY STA',\n",
              "       'KIPLING TO ISLINGTON S', 'CHURCH EMERGENCY EXIT',\n",
              "       'WILSON YARD - NORTH HO', 'YORK UNIVERSTITY, DOWN',\n",
              "       'W/O CASTLE FRANK TO GR', 'CHESTER TO YONGE STATI',\n",
              "       'SCARBORUGH RAPID TRANS', 'WARDEN AND KENNEDY',\n",
              "       'UNION STATION (KING)', 'WOODBINE TO ST.GEORGE',\n",
              "       'KENNEDY AND SCARBOROUG', 'MCBRIEN BUIDING', 'BLOOR TO ST.CLAIR',\n",
              "       'UNION, EXHIBITION, MIM', 'ST CLAIR WEST TO SHEPP',\n",
              "       'VMC STATION TO SHEPPAR', 'BLOOR TO UNION',\n",
              "       'WILSON HOSTLER SOUTHBO', 'ALLEN RD (N OF EGLINTO',\n",
              "       'KENNEDY - WARDEN - MAI', 'SPADINA YUS STATION (L',\n",
              "       'WILSON DIVISION TRAILE', 'FINCH TO NORTH YORK CT',\n",
              "       'KENNEDY TO LAWRENCE EA', 'HARVEY SHOP', 'FINCH TO VAUGHAN METRO',\n",
              "       'YONGE - UNIVERSITY BLO', 'KENNEDY STATION TO KIP', 'TYSSE LINE',\n",
              "       'SHEPPARD WEST MIGRATI', 'UNION TO ST GEORGE',\n",
              "       'LAWRENCE EAST TO ELLES', 'EGLINTON WEST TO VMC',\n",
              "       'WARDEN TO KENNEDY STAT', 'UNION STATION TO FINCH',\n",
              "       'OSSINGTON AND DUFFERIN', 'LEAVING LAWRENCE EAST',\n",
              "       'SEHPPARD STATION', 'DUFFERIN TO LANSDOWNE', 'WILSON TO EGLINTON',\n",
              "       'SHEPPARD WEST PORTAL', 'YU/DB/SHEPPARD SUBWAY',\n",
              "       'KING STATION TO UNION', 'LEAVING YONGE/SHEPPARD',\n",
              "       'GLENCAIRN TO ST GEORGE', 'ST CLAIR WEST TO EGLIN',\n",
              "       'SHEPPARD WEST MIGRATIO', 'ST CLAIR WEST TO DUPON',\n",
              "       'OLD MILL TO ISLINGTON', 'LESLIE STATION(APPROAC',\n",
              "       'OSGOODE POCKET', 'YONGE SHP STATION (LEA', 'DUPONT TO OSGOODE',\n",
              "       'GREENWOOD WYE (ENTERI', 'EGINTON STATION', 'VMC TO SHEPPARD WEST',\n",
              "       'ATC FAILURE', 'KING TO S/O ST ANDREW', 'KENNEDY SRT TO MCCOWAN',\n",
              "       'WELLSLEY STATION', 'WILSON HOSLTER', 'SHEPPARD YONGE STATION',\n",
              "       'SCARBOROUGH SRT LINE', 'SHEPPARD WEST', 'SCARBOROUGH RAOID TRAN',\n",
              "       'SOUTH OF LAWRENCE SRT', 'SOUTH OF MIDLAND SRT',\n",
              "       'WELBECK EMERGENCY EXIT', 'YARD SWITCH - BD CHECK',\n",
              "       'UNION (TO ST ANDREW)', 'ST. CLAIR WEST TO DUPO',\n",
              "       'BESSARIAN STATION', 'KENNEDY - MCCOWAN', 'ST. GEORGE TO BROADVIE',\n",
              "       'CASTLE FRANK TO SHERBO', 'SHEPPARD WEST TO ST. C',\n",
              "       'LAWRENCE TO EGLINTON S', 'FINCH TO UNION STATION',\n",
              "       'ST GEORGE TO FINCH WES', 'ATC TERRITORY', 'SHEPPARD YONGE'],\n",
              "      dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 64
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8fGkHA-tuSqp"
      },
      "outputs": [],
      "source": [
        "#To simplify things, we can first remove punctuation marks from all station names. \n",
        "df_merged['station_cleaned'] = [re.sub('\\.|\\'', '', re.sub(' \\(.*$', '', x)) for x in df_merged.Station]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "-UfkKZ_ruSqp",
        "outputId": "b61e7a8b-f940-4543-9ef4-fe3df2a75041",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "         station_cleaned          station_fixed\n",
              "0        KIPLING STATION        KIPLING STATION\n",
              "1     KENNEDY BD STATION     KENNEDY BD STATION\n",
              "2  YONGE UNIVERSITY LINE                     YU\n",
              "3          FINCH STATION          FINCH STATION\n",
              "4  SHEPPARD WEST STATION  SHEPPARD WEST STATION"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-2a4e092a-8a3b-4e4a-86f7-cb50f3e2d175\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>station_cleaned</th>\n",
              "      <th>station_fixed</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>KIPLING STATION</td>\n",
              "      <td>KIPLING STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>KENNEDY BD STATION</td>\n",
              "      <td>KENNEDY BD STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>YONGE UNIVERSITY LINE</td>\n",
              "      <td>YU</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>FINCH STATION</td>\n",
              "      <td>FINCH STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>SHEPPARD WEST STATION</td>\n",
              "      <td>SHEPPARD WEST STATION</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2a4e092a-8a3b-4e4a-86f7-cb50f3e2d175')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-2a4e092a-8a3b-4e4a-86f7-cb50f3e2d175 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-2a4e092a-8a3b-4e4a-86f7-cb50f3e2d175');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 66
        }
      ],
      "source": [
        "station_fixed_mapping = pd.read_csv('/content/drive/MyDrive/subway_eda/station_spellings.csv')\n",
        "station_fixed_mapping.head()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "h5qY-aQiuSqq",
        "outputId": "00f849af-5911-41db-bbc8-befac5d52810",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(79393, 14)\n"
          ]
        }
      ],
      "source": [
        "#merging for spelling checks for station\n",
        "df_merged = df_merged.merge(\n",
        "    station_fixed_mapping, \n",
        "    on = 'station_cleaned', \n",
        "    how = 'left'\n",
        ")\n",
        "\n",
        "print(df_merged.shape)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_merged"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 676
        },
        "id": "HvC1-8Adz2Ky",
        "outputId": "5c97600f-c136-4ee2-f735-2423b2125a32"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            Date   Time       Day                 Station   Code  Min Delay  \\\n",
              "0     2022-01-01  15:59  Saturday   LAWRENCE EAST STATION   SRDP          0   \n",
              "1     2022-01-01  02:23  Saturday      SPADINA BD STATION   MUIS          0   \n",
              "2     2022-01-01  22:00  Saturday  KENNEDY SRT STATION TO    MRO          0   \n",
              "3     2022-01-01  02:28  Saturday      VAUGHAN MC STATION   MUIS          0   \n",
              "4     2022-01-01  02:34  Saturday        EGLINTON STATION  MUATC          0   \n",
              "...          ...    ...       ...                     ...    ...        ...   \n",
              "79388 2018-12-31  22:34    Monday          DUNDAS STATION  PUMST          0   \n",
              "79389 2018-12-31  22:36    Monday          DUNDAS STATION   SUDP          3   \n",
              "79390 2018-12-31  23:08    Monday           BLOOR STATION  MUIRS          0   \n",
              "79391 2018-12-31  23:21    Monday    QUEEN'S PARK STATION   TUSC          0   \n",
              "79392 2018-12-31  23:32    Monday        ST CLAIR STATION  MUPAA          0   \n",
              "\n",
              "       Min Gap Bound Line  Vehicle  \\\n",
              "0            0     N  SRT     3023   \n",
              "1            0         BD        0   \n",
              "2            0        SRT        0   \n",
              "3            0         YU        0   \n",
              "4            0     S   YU     5981   \n",
              "...        ...   ...  ...      ...   \n",
              "79388        0         YU        0   \n",
              "79389        6     N   YU     5881   \n",
              "79390        0         YU        0   \n",
              "79391        0     S   YU     5841   \n",
              "79392        0     S   YU     6061   \n",
              "\n",
              "                                                    Desc line_cleaned  \\\n",
              "0                                      Disorderly Patron          SRT   \n",
              "1      Injured or ill Customer (In Station) - Transpo...           BD   \n",
              "2                                    Miscellaneous Other          SRT   \n",
              "3      Injured or ill Customer (In Station) - Transpo...           YU   \n",
              "4                                            ATC Project           YU   \n",
              "...                                                  ...          ...   \n",
              "79388                         Station Stairway Incident            YU   \n",
              "79389                                  Disorderly Patron           YU   \n",
              "79390  Injured or ill Customer (In Station) - Medical...           YU   \n",
              "79391                              Operator Overspeeding           YU   \n",
              "79392  Passenger Assistance Alarm Activated - No Trou...           YU   \n",
              "\n",
              "              station_cleaned          station_fixed  \n",
              "0       LAWRENCE EAST STATION  LAWRENCE EAST STATION  \n",
              "1          SPADINA BD STATION     SPADINA BD STATION  \n",
              "2      KENNEDY SRT STATION TO                    NaN  \n",
              "3          VAUGHAN MC STATION     VAUGHAN MC STATION  \n",
              "4            EGLINTON STATION       EGLINTON STATION  \n",
              "...                       ...                    ...  \n",
              "79388          DUNDAS STATION         DUNDAS STATION  \n",
              "79389          DUNDAS STATION         DUNDAS STATION  \n",
              "79390           BLOOR STATION          BLOOR STATION  \n",
              "79391     QUEENS PARK STATION    QUEENS PARK STATION  \n",
              "79392        ST CLAIR STATION       ST CLAIR STATION  \n",
              "\n",
              "[79393 rows x 14 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-54365588-fc50-4d9f-a45c-75417a3b6eb9\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Time</th>\n",
              "      <th>Day</th>\n",
              "      <th>Station</th>\n",
              "      <th>Code</th>\n",
              "      <th>Min Delay</th>\n",
              "      <th>Min Gap</th>\n",
              "      <th>Bound</th>\n",
              "      <th>Line</th>\n",
              "      <th>Vehicle</th>\n",
              "      <th>Desc</th>\n",
              "      <th>line_cleaned</th>\n",
              "      <th>station_cleaned</th>\n",
              "      <th>station_fixed</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>15:59</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "      <td>SRDP</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3023</td>\n",
              "      <td>Disorderly Patron</td>\n",
              "      <td>SRT</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:23</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>SPADINA BD STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>BD</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>BD</td>\n",
              "      <td>SPADINA BD STATION</td>\n",
              "      <td>SPADINA BD STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>22:00</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>KENNEDY SRT STATION TO</td>\n",
              "      <td>MRO</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>SRT</td>\n",
              "      <td>0</td>\n",
              "      <td>Miscellaneous Other</td>\n",
              "      <td>SRT</td>\n",
              "      <td>KENNEDY SRT STATION TO</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:28</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>VAUGHAN MC STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>YU</td>\n",
              "      <td>VAUGHAN MC STATION</td>\n",
              "      <td>VAUGHAN MC STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2022-01-01</td>\n",
              "      <td>02:34</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "      <td>MUATC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5981</td>\n",
              "      <td>ATC Project</td>\n",
              "      <td>YU</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79388</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:34</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>PUMST</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Station Stairway Incident</td>\n",
              "      <td>YU</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79389</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>22:36</td>\n",
              "      <td>Monday</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>SUDP</td>\n",
              "      <td>3</td>\n",
              "      <td>6</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5881</td>\n",
              "      <td>Disorderly Patron</td>\n",
              "      <td>YU</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "      <td>DUNDAS STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79390</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:08</td>\n",
              "      <td>Monday</td>\n",
              "      <td>BLOOR STATION</td>\n",
              "      <td>MUIRS</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Medical...</td>\n",
              "      <td>YU</td>\n",
              "      <td>BLOOR STATION</td>\n",
              "      <td>BLOOR STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79391</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:21</td>\n",
              "      <td>Monday</td>\n",
              "      <td>QUEEN'S PARK STATION</td>\n",
              "      <td>TUSC</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5841</td>\n",
              "      <td>Operator Overspeeding</td>\n",
              "      <td>YU</td>\n",
              "      <td>QUEENS PARK STATION</td>\n",
              "      <td>QUEENS PARK STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>79392</th>\n",
              "      <td>2018-12-31</td>\n",
              "      <td>23:32</td>\n",
              "      <td>Monday</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "      <td>MUPAA</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>6061</td>\n",
              "      <td>Passenger Assistance Alarm Activated - No Trou...</td>\n",
              "      <td>YU</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>79393 rows × 14 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-54365588-fc50-4d9f-a45c-75417a3b6eb9')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-54365588-fc50-4d9f-a45c-75417a3b6eb9 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-54365588-fc50-4d9f-a45c-75417a3b6eb9');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 68
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "pbqkxgAruSqq",
        "outputId": "930f99e2-b854-4fd4-f283-4bc371e90faf",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "TORONTO TRANSIT COMMIS    264\n",
              "GREENWOOD YARD            171\n",
              "WILSON YARD               142\n",
              "KENNEDY SRT STATION TO     96\n",
              "DAVISVILLE YARD            66\n",
              "WILSON HOSTLER             61\n",
              "WILSON CARHOUSE            42\n",
              "YONGE SHEP STATION         40\n",
              "MCCOWAN YARD               39\n",
              "KEELE YARD                 26\n",
              "GREENWOOD WYE              26\n",
              "GREENWOOD CARHOUSE         24\n",
              "DAVISVILLE BUILD UP        21\n",
              "GREENWOOD PORTAL           20\n",
              "KENNEDY SRT TO LAWREN      19\n",
              "LAWRENCE EAST TO KENNE     18\n",
              "DAVISVILLE CARHOUSE        16\n",
              "YONGE/UNIVERSITY AND B     16\n",
              "EGLINTON                   16\n",
              "ST GEORGE YU STATION       14\n",
              "DAVISVILLE HOSTLER         13\n",
              "BLOOR-DANFORTH LINE        11\n",
              "TRANSIT CONTROL CENTRE     11\n",
              "LAWRENCE TO ST CLAIR S     10\n",
              "SHEPPARD WEST MIGRATIO     10\n",
              "DANFORTH DIVISION          10\n",
              "MCCOWAN CARHOUSE            9\n",
              "GREENWOOD SHOP              8\n",
              "YONGE UNIVERSITY SPADI      8\n",
              "DAVISVILLE BUILD-UP         8\n",
              "Name: station_cleaned, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 69
        }
      ],
      "source": [
        "#will remove the enteries with shops and carhouse since they are likely not train delays\n",
        "df_merged[df_merged.station_fixed.isnull()].station_cleaned.value_counts()[:30]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XOi1WIMiuSqq",
        "outputId": "0a4164f4-5f9c-4e19-8a6e-b2a0ccfe6a2d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(77510, 13)\n",
            "        Date   Time       Day                Station   Code  Min Delay  \\\n",
            "0 2022-01-01  15:59  Saturday  LAWRENCE EAST STATION   SRDP          0   \n",
            "1 2022-01-01  02:23  Saturday     SPADINA BD STATION   MUIS          0   \n",
            "2 2022-01-01  02:28  Saturday     VAUGHAN MC STATION   MUIS          0   \n",
            "3 2022-01-01  02:34  Saturday       EGLINTON STATION  MUATC          0   \n",
            "4 2022-01-01  05:40  Saturday          QUEEN STATION  MUNCA          0   \n",
            "\n",
            "   Min Gap Bound Line  Vehicle  \\\n",
            "0        0     N  SRT     3023   \n",
            "1        0         BD        0   \n",
            "2        0         YU        0   \n",
            "3        0     S   YU     5981   \n",
            "4        0         YU        0   \n",
            "\n",
            "                                                Desc line_cleaned  \\\n",
            "0                                  Disorderly Patron          SRT   \n",
            "1  Injured or ill Customer (In Station) - Transpo...           BD   \n",
            "2  Injured or ill Customer (In Station) - Transpo...           YU   \n",
            "3                                        ATC Project           YU   \n",
            "4                                                              YU   \n",
            "\n",
            "         station_cleaned  \n",
            "0  LAWRENCE EAST STATION  \n",
            "1     SPADINA BD STATION  \n",
            "2     VAUGHAN MC STATION  \n",
            "3       EGLINTON STATION  \n",
            "4          QUEEN STATION  \n"
          ]
        }
      ],
      "source": [
        "df_merged = (df_merged[~df_merged.station_fixed.isnull()].reset_index(drop = True)\n",
        "                                    .drop(columns = ['station_cleaned'])\n",
        "                                    .rename(columns = {'station_fixed': 'station_cleaned'}))\n",
        "\n",
        "print(df_merged.shape)\n",
        "print(df_merged.head())"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Delay Duration"
      ],
      "metadata": {
        "id": "e06RuI5g2nBV"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wRZATLVYuSqu",
        "outputId": "b76960a7-04a5-4fbe-a9aa-282e2a35385e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 287
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<matplotlib.axes._subplots.AxesSubplot at 0x7fbec4ebba50>"
            ]
          },
          "metadata": {},
          "execution_count": 71
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<Figure size 432x288 with 1 Axes>"
            ],
            "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYMAAAD8CAYAAACVZ8iyAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAYDUlEQVR4nO3df6zddZ3n8edrWtGKP1rAuWlasmViMwZhRbiBGs3mCmspOLH8oQZClq5p7CbirG5IZstusmRUEk1kGCFKthm6FMOIDKNpo3Vqt3Cy2T/KL0FKQbZXLNM2hY60wF5ddeu+94/zqXOst9xzb9tzb2+fj+Tkfr/v7+f7/X7el0tf53zP99ybqkKSdHr7o+megCRp+hkGkiTDQJJkGEiSMAwkSRgGkiT6DIMk/yHJziTPJPlWkrckOS/JI0lGk3w7yRlt7Jvb+mjbvqTnODe3+vNJruypr2i10SRrT3STkqQ3NmEYJFkE/HtguKouAOYA1wJfAW6vqncDh4DVbZfVwKFWv72NI8n5bb/3AiuAbySZk2QO8HXgKuB84Lo2VpI0IP1eJpoLzEsyF3grsB+4HHiwbd8AXNOWV7Z12vYrkqTV76+qX1fVz4BR4NL2GK2qF6rqN8D9bawkaUDmTjSgqvYl+Srwj8D/AX4IPAG8WlWH27C9wKK2vAjY0/Y9nOQ14OxW395z6N599hxVv2yieZ1zzjm1ZMmSiYaN6xe/+AVnnnnmlPY91djr7GSvs9PJ7vWJJ574eVW9a7xtE4ZBkgV0n6mfB7wK/B3dyzwDl2QNsAZgaGiIr371q1M6ztjYGG9729tO5NRmLHudnex1djrZvX74wx9+8VjbJgwD4F8DP6uqfwJI8h3gg8D8JHPbq4PFwL42fh9wLrC3XVZ6J/BKT/2I3n2OVf89VbUOWAcwPDxcIyMjfUz/D3U6Haa676nGXmcne52dprPXft4z+EdgWZK3tmv/VwDPAg8DH29jVgEb2/Kmtk7b/lB1fxveJuDadrfRecBS4FHgMWBpuzvpDLpvMm86/tYkSf3q5z2DR5I8CPwIOAw8SffZ+feB+5N8qdXubrvcDXwzyShwkO4/7lTVziQP0A2Sw8CNVfVbgCSfBbbQvVNpfVXtPHEtSpIm0s9lIqrqFuCWo8ov0L0T6OixvwI+cYzj3ArcOk59M7C5n7lIkk48P4EsSTIMJEmGgSQJw0CShGEgSaLPu4lmmx37XuPfrv1+3+N3f/mjJ3E2kjT9fGUgSTIMJEmGgSQJw0CShGEgScIwkCRhGEiSMAwkSRgGkiQMA0kShoEkCcNAkkQfYZDkT5M81fN4Pcnnk5yVZGuSXe3rgjY+Se5IMprk6SQX9xxrVRu/K8mqnvolSXa0fe5IkpPTriRpPBOGQVU9X1UXVdVFwCXAL4HvAmuBbVW1FNjW1gGuApa2xxrgLoAkZ9H9O8qX0f3bybccCZA25tM9+604Id1Jkvoy2ctEVwA/raoXgZXAhlbfAFzTllcC91bXdmB+koXAlcDWqjpYVYeArcCKtu0dVbW9qgq4t+dYkqQBmGwYXAt8qy0PVdX+tvwSMNSWFwF7evbZ22pvVN87Tl2SNCB9/3GbJGcAHwNuPnpbVVWSOpETO8Yc1tC99MTQ0BCdTmdKxxmaBzddeLjv8VM9z0wwNjZ2Ss9/Mux1drLXwZjMXzq7CvhRVb3c1l9OsrCq9rdLPQdafR9wbs9+i1ttHzByVL3T6ovHGf8HqmodsA5geHi4RkZGxhs2oTvv28htO/pvfff1UzvPTNDpdJjq9+lUY6+zk70OxmQuE13HP18iAtgEHLkjaBWwsad+Q7uraBnwWructAVYnmRBe+N4ObClbXs9ybJ2F9ENPceSJA1AX0+Pk5wJfAT4dz3lLwMPJFkNvAh8stU3A1cDo3TvPPoUQFUdTPJF4LE27gtVdbAtfwa4B5gH/KA9JEkD0lcYVNUvgLOPqr1C9+6io8cWcOMxjrMeWD9O/XHggn7mIkk68fwEsiTJMJAkGQaSJAwDSRKGgSQJw0CShGEgScIwkCRhGEiSMAwkSRgGkiQMA0kShoEkCcNAkoRhIEnCMJAkYRhIkjAMJEn0GQZJ5id5MMlPkjyX5ANJzkqyNcmu9nVBG5skdyQZTfJ0kot7jrOqjd+VZFVP/ZIkO9o+dyTJiW9VknQs/b4y+BrwD1X1HuB9wHPAWmBbVS0FtrV1gKuApe2xBrgLIMlZwC3AZcClwC1HAqSN+XTPfiuOry1J0mRMGAZJ3gn8K+BugKr6TVW9CqwENrRhG4Br2vJK4N7q2g7MT7IQuBLYWlUHq+oQsBVY0ba9o6q2V1UB9/YcS5I0AP28MjgP+CfgvyV5MsnfJDkTGKqq/W3MS8BQW14E7OnZf2+rvVF97zh1SdKAzO1zzMXAn1fVI0m+xj9fEgKgqipJnYwJ9kqyhu6lJ4aGhuh0OlM6ztA8uOnCw32Pn+p5ZoKxsbFTev6TYa+zk70ORj9hsBfYW1WPtPUH6YbBy0kWVtX+dqnnQNu+Dzi3Z//FrbYPGDmq3mn1xeOM/wNVtQ5YBzA8PFwjIyPjDZvQnfdt5LYd/bTetfv6qZ1nJuh0Okz1+3SqsdfZyV4HY8LLRFX1ErAnyZ+20hXAs8Am4MgdQauAjW15E3BDu6toGfBau5y0BVieZEF743g5sKVtez3JsnYX0Q09x5IkDUC/T4//HLgvyRnAC8Cn6AbJA0lWAy8Cn2xjNwNXA6PAL9tYqupgki8Cj7VxX6iqg235M8A9wDzgB+0hSRqQvsKgqp4ChsfZdMU4Ywu48RjHWQ+sH6f+OHBBP3ORJJ14fgJZkmQYSJIMA0kShoEkCcNAkoRhIEnCMJAkYRhIkjAMJEkYBpIkDANJEoaBJAnDQJKEYSBJwjCQJGEYSJIwDCRJGAaSJPoMgyS7k+xI8lSSx1vtrCRbk+xqXxe0epLckWQ0ydNJLu45zqo2fleSVT31S9rxR9u+OdGNSpKObTKvDD5cVRdV1ZG/hbwW2FZVS4FtbR3gKmBpe6wB7oJueAC3AJcBlwK3HAmQNubTPfutmHJHkqRJO57LRCuBDW15A3BNT/3e6toOzE+yELgS2FpVB6vqELAVWNG2vaOqtldVAff2HEuSNAD9hkEBP0zyRJI1rTZUVfvb8kvAUFteBOzp2Xdvq71Rfe84dUnSgMztc9yHqmpfkj8Gtib5Se/GqqokdeKn9/taEK0BGBoaotPpTOk4Q/PgpgsP9z1+queZCcbGxk7p+U+Gvc5O9joYfYVBVe1rXw8k+S7da/4vJ1lYVfvbpZ4Dbfg+4Nye3Re32j5g5Kh6p9UXjzN+vHmsA9YBDA8P18jIyHjDJnTnfRu5bUe/OQi7r5/aeWaCTqfDVL9Ppxp7nZ3sdTAmvEyU5Mwkbz+yDCwHngE2AUfuCFoFbGzLm4Ab2l1Fy4DX2uWkLcDyJAvaG8fLgS1t2+tJlrW7iG7oOZYkaQD6eXo8BHy33e05F/jbqvqHJI8BDyRZDbwIfLKN3wxcDYwCvwQ+BVBVB5N8EXisjftCVR1sy58B7gHmAT9oD0nSgEwYBlX1AvC+ceqvAFeMUy/gxmMcaz2wfpz648AFfcxXknQS+AlkSZJhIEkyDCRJGAaSJAwDSRKGgSQJw0CShGEgScIwkCRhGEiSMAwkSRgGkiQMA0kShoEkCcNAkoRhIEnCMJAkYRhIkjAMJElMIgySzEnyZJLvtfXzkjySZDTJt5Oc0epvbuujbfuSnmPc3OrPJ7myp76i1UaTrD1x7UmS+jGZVwafA57rWf8KcHtVvRs4BKxu9dXAoVa/vY0jyfnAtcB7gRXAN1rAzAG+DlwFnA9c18ZKkgakrzBIshj4KPA3bT3A5cCDbcgG4Jq2vLKt07Zf0cavBO6vql9X1c+AUeDS9hitqheq6jfA/W2sJGlA5vY57q+BvwDe3tbPBl6tqsNtfS+wqC0vAvYAVNXhJK+18YuA7T3H7N1nz1H1y8abRJI1wBqAoaEhOp1On9P/fUPz4KYLD088sJnqeWaCsbGxU3r+k2Gvs5O9DsaEYZDkz4ADVfVEkpGTP6Vjq6p1wDqA4eHhGhmZ2nTuvG8jt+3oNwdh9/VTO89M0Ol0mOr36VRjr7OTvQ5GP/8ifhD4WJKrgbcA7wC+BsxPMre9OlgM7Gvj9wHnAnuTzAXeCbzSUz+id59j1SVJAzDhewZVdXNVLa6qJXTfAH6oqq4HHgY+3oatAja25U1tnbb9oaqqVr+23W10HrAUeBR4DFja7k46o51j0wnpTpLUl/6vlfyh/wjcn+RLwJPA3a1+N/DNJKPAQbr/uFNVO5M8ADwLHAZurKrfAiT5LLAFmAOsr6qdxzEvSdIkTSoMqqoDdNryC3TvBDp6zK+ATxxj/1uBW8epbwY2T2YukqQTx08gS5IMA0mSYSBJwjCQJGEYSJIwDCRJGAaSJAwDSRKGgSQJw0CShGEgScIwkCRhGEiSMAwkSRgGkiQMA0kShoEkCcNAkkQfYZDkLUkeTfLjJDuT/GWrn5fkkSSjSb7d/pg97Q/ef7vVH0mypOdYN7f680mu7KmvaLXRJGtPfJuSpDfSzyuDXwOXV9X7gIuAFUmWAV8Bbq+qdwOHgNVt/GrgUKvf3saR5HzgWuC9wArgG0nmJJkDfB24CjgfuK6NlSQNyIRhUF1jbfVN7VHA5cCDrb4BuKYtr2zrtO1XJEmr319Vv66qnwGjwKXtMVpVL1TVb4D721hJ0oD09Z5Bewb/FHAA2Ar8FHi1qg63IXuBRW15EbAHoG1/DTi7t37UPseqS5IGZG4/g6rqt8BFSeYD3wXec1JndQxJ1gBrAIaGhuh0OlM6ztA8uOnCwxMPbKZ6nplgbGzslJ7/ZNjr7GSvg9FXGBxRVa8meRj4ADA/ydz27H8xsK8N2wecC+xNMhd4J/BKT/2I3n2OVT/6/OuAdQDDw8M1MjIymen/zp33beS2Hf23vvv6qZ1nJuh0Okz1+3SqsdfZyV4Ho5+7id7VXhGQZB7wEeA54GHg423YKmBjW97U1mnbH6qqavVr291G5wFLgUeBx4Cl7e6kM+i+ybzpRDQnSepPP0+PFwIb2l0/fwQ8UFXfS/IscH+SLwFPAne38XcD30wyChyk+487VbUzyQPAs8Bh4MZ2+YkknwW2AHOA9VW184R1KEma0IRhUFVPA+8fp/4C3TuBjq7/CvjEMY51K3DrOPXNwOY+5itJOgn8BLIkyTCQJBkGkiQMA0kShoEkCcNAkoRhIEnCMJAkYRhIkjAMJEkYBpIkDANJEoaBJAnDQJKEYSBJwjCQJGEYSJIwDCRJ9BEGSc5N8nCSZ5PsTPK5Vj8rydYku9rXBa2eJHckGU3ydJKLe461qo3flWRVT/2SJDvaPnckycloVpI0vn5eGRwGbqqq84FlwI1JzgfWAtuqaimwra0DXAUsbY81wF3QDQ/gFuAyun87+ZYjAdLGfLpnvxXH35okqV8ThkFV7a+qH7Xl/w08BywCVgIb2rANwDVteSVwb3VtB+YnWQhcCWytqoNVdQjYCqxo295RVdurqoB7e44lSRqASb1nkGQJ8H7gEWCoqva3TS8BQ215EbCnZ7e9rfZG9b3j1CVJAzK334FJ3gb8PfD5qnq997J+VVWSOgnzO3oOa+heemJoaIhOpzOl4wzNg5suPNz3+KmeZyYYGxs7pec/GfY6O9nrYPQVBkneRDcI7quq77Tyy0kWVtX+dqnnQKvvA87t2X1xq+0DRo6qd1p98Tjj/0BVrQPWAQwPD9fIyMh4wyZ0530buW1H3znI7uundp6ZoNPpMNXv06nGXmcnex2Mfu4mCnA38FxV/VXPpk3AkTuCVgEbe+o3tLuKlgGvtctJW4DlSRa0N46XA1vatteTLGvnuqHnWJKkAejn6fEHgX8D7EjyVKv9J+DLwANJVgMvAp9s2zYDVwOjwC+BTwFU1cEkXwQea+O+UFUH2/JngHuAecAP2kOSNCAThkFV/U/gWPf9XzHO+AJuPMax1gPrx6k/Dlww0VwkSSeHn0CWJBkGkiTDQJKEYSBJwjCQJGEYSJIwDCRJGAaSJAwDSRKGgSQJw0CShGEgScIwkCRhGEiSMAwkSRgGkiQMA0kShoEkiT7CIMn6JAeSPNNTOyvJ1iS72tcFrZ4kdyQZTfJ0kot79lnVxu9KsqqnfkmSHW2fO5Ic609sSpJOkn5eGdwDrDiqthbYVlVLgW1tHeAqYGl7rAHugm54ALcAlwGXArccCZA25tM9+x19LknSSTZhGFTV/wAOHlVeCWxoyxuAa3rq91bXdmB+koXAlcDWqjpYVYeArcCKtu0dVbW9qgq4t+dYkqQBmep7BkNVtb8tvwQMteVFwJ6ecXtb7Y3qe8epS5IGaO7xHqCqKkmdiMlMJMkaupefGBoaotPpTOk4Q/PgpgsP9z1+queZCcbGxk7p+U+Gvc5O9joYUw2Dl5MsrKr97VLPgVbfB5zbM25xq+0DRo6qd1p98Tjjx1VV64B1AMPDwzUyMnKsoW/ozvs2ctuO/lvfff3UzjMTdDodpvp9OtXY6+xkr4Mx1ctEm4AjdwStAjb21G9odxUtA15rl5O2AMuTLGhvHC8HtrRtrydZ1u4iuqHnWJKkAZnw6XGSb9F9Vn9Okr107wr6MvBAktXAi8An2/DNwNXAKPBL4FMAVXUwyReBx9q4L1TVkTelP0P3jqV5wA/aQ5I0QBOGQVVdd4xNV4wztoAbj3Gc9cD6ceqPAxdMNA9J0snjJ5AlSYaBJMkwkCRhGEiSMAwkSRgGkiQMA0kShoEkCcNAkoRhIEnCMJAkYRhIkjAMJEkYBpIkDANJEoaBJAnDQJKEYSBJYgaFQZIVSZ5PMppk7XTPR5JOJzMiDJLMAb4OXAWcD1yX5PzpnZUknT7mTvcEmkuB0ap6ASDJ/cBK4NlpnVWzZO33JzV+95c/epJmIkknx0wJg0XAnp71vcBl0zSX42Z4SDrVzJQw6EuSNcCatjqW5PkpHuoc4OcnZlbHL185qYefUb2eZPY6O9nrifMvjrVhpoTBPuDcnvXFrfZ7qmodsO54T5bk8aoaPt7jnArsdXay19lpOnudEW8gA48BS5Ocl+QM4Fpg0zTPSZJOGzPilUFVHU7yWWALMAdYX1U7p3laknTamBFhAFBVm4HNAzrdcV9qOoXY6+xkr7PTtPWaqpquc0uSZoiZ8p6BJGkanVZhMBt/5UWS9UkOJHmmp3ZWkq1JdrWvC1o9Se5o/T+d5OLpm/nkJDk3ycNJnk2yM8nnWn029vqWJI8m+XHr9S9b/bwkj7Sevt1utiDJm9v6aNu+ZDrnPxVJ5iR5Msn32vps7nV3kh1JnkryeKtN+8/xaRMGs/hXXtwDrDiqthbYVlVLgW1tHbq9L22PNcBdA5rjiXAYuKmqzgeWATe2/36zsddfA5dX1fuAi4AVSZYBXwFur6p3A4eA1W38auBQq9/exp1qPgc817M+m3sF+HBVXdRzG+n0/xxX1WnxAD4AbOlZvxm4ebrndYJ6WwI807P+PLCwLS8Enm/L/xW4brxxp9oD2Ah8ZLb3CrwV+BHdT+T/HJjb6r/7eaZ7F94H2vLcNi7TPfdJ9LiY7j+AlwPfAzJbe23z3g2cc1Rt2n+OT5tXBoz/Ky8WTdNcTrahqtrfll8ChtryrPgetEsD7wceYZb22i6bPAUcALYCPwVerarDbUhvP7/rtW1/DTh7sDM+Ln8N/AXw/9r62czeXgEK+GGSJ9pvVYAZ8HM8Y24t1clRVZVk1twyluRtwN8Dn6+q15P8btts6rWqfgtclGQ+8F3gPdM8pZMiyZ8BB6rqiSQj0z2fAflQVe1L8sfA1iQ/6d04XT/Hp9Mrg75+5cUs8XKShQDt64FWP6W/B0neRDcI7quq77TyrOz1iKp6FXiY7qWS+UmOPIHr7ed3vbbt7wReGfBUp+qDwMeS7Abup3up6GvMzl4BqKp97esBukF/KTPg5/h0CoPT6VdebAJWteVVdK+vH6nf0O5QWAa81vPSdEZL9yXA3cBzVfVXPZtmY6/vaq8ISDKP7nsjz9ENhY+3YUf3euR78HHgoWoXmGe6qrq5qhZX1RK6/08+VFXXMwt7BUhyZpK3H1kGlgPPMBN+jqf7zZQBv3FzNfC/6F5//c/TPZ8T1NO3gP3A/6V7PXE13Wuo24BdwH8HzmpjQ/eOqp8CO4Dh6Z7/JPr8EN1rrU8DT7XH1bO0138JPNl6fQb4L63+J8CjwCjwd8CbW/0tbX20bf+T6e5hin2PAN+bzb22vn7cHjuP/Ds0E36O/QSyJOm0ukwkSToGw0CSZBhIkgwDSRKGgSQJw0CShGEgScIwkCQB/x/MSjtHVy8MfQAAAABJRU5ErkJggg==\n"
          },
          "metadata": {
            "needs_background": "light"
          }
        }
      ],
      "source": [
        "df_merged['Min Delay'].hist(bins = 25)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "JWWdI720uSqu",
        "outputId": "22280758-fd8a-4028-e55e-f8166def394a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            Date   Time        Day                 Station   Code  Min Delay  \\\n",
              "1421  2022-01-25  21:03    Tuesday  SCARBOROUGH CTR STATIO   PRSL        285   \n",
              "1476  2022-01-26  20:15  Wednesday     KENNEDY SRT STATION  MRWEA        315   \n",
              "2312  2022-02-09  06:06  Wednesday          DUPONT STATION   SUAE        240   \n",
              "3022  2022-02-22  06:12    Tuesday     KENNEDY SRT STATION  MRPLB        221   \n",
              "4035  2022-03-12  12:59   Saturday       ELLESMERE STATION   PRSL        206   \n",
              "4594  2022-03-23  09:22  Wednesday  SHEPPARD-YONGE STATION  PUSRA        220   \n",
              "6406  2022-04-28  06:02   Thursday            JANE STATION   PUTR        388   \n",
              "8321  2021-01-05  13:02    Tuesday       ISLINGTON STATION  MUPR1        207   \n",
              "12800 2021-04-30  06:15     Friday   SHEPPARD WEST STATION  PUTWZ        255   \n",
              "13651 2021-05-24  05:48     Monday         MCCOWAN STATION   PRSL        275   \n",
              "15108 2021-06-29  10:21    Tuesday          MUSEUM STATION  PUTTP        348   \n",
              "16575 2021-08-05  20:32   Thursday        WOODBINE STATION    MUO        312   \n",
              "19517 2021-10-12  12:03    Tuesday        EGLINTON STATION  PUSTC        343   \n",
              "24845 2020-01-22  05:57  Wednesday           KEELE STATION   EUTR        258   \n",
              "25825 2020-02-13  05:30   Thursday   ST GEORGE YUS STATION   TUCC        450   \n",
              "27374 2020-03-19  11:26   Thursday      ROYAL YORK STATION  MUPR1        221   \n",
              "29758 2020-05-08  16:16     Friday        ST CLAIR STATION    MUO        446   \n",
              "40756 2019-02-12  20:28    Tuesday   LAWRENCE EAST STATION  MRWEA        284   \n",
              "40757 2019-02-12  20:28    Tuesday   LAWRENCE EAST STATION  MRWEA        284   \n",
              "46572 2019-06-05  12:42  Wednesday      UNION TO ST ANDREW  MUPLA        250   \n",
              "47650 2019-06-25  18:48    Tuesday  WILSON TO SHEPPARD WES   PUTR        455   \n",
              "53689 2019-10-22  14:22    Tuesday        LAWRENCE STATION   PUTS        228   \n",
              "57916 2018-01-13  19:19   Saturday           UNION STATION   PUTR        248   \n",
              "62710 2018-04-15  19:03     Sunday                SRT LINE  MRWEA        399   \n",
              "63096 2018-04-23  13:45     Monday        SHEPPARD STATION  SUPOL        269   \n",
              "73707 2018-10-20  17:44   Saturday      KENNEDY BD STATION   SUAP        515   \n",
              "77401 2018-12-29  12:09   Saturday      SUMMERHILL STATION  MUPR1        238   \n",
              "\n",
              "       Min Gap Bound Line  Vehicle  \\\n",
              "1421       291     S  SRT     3010   \n",
              "1476       321     S  SRT     3020   \n",
              "2312       243     S   YU     5426   \n",
              "3022       226     S  SRT     3014   \n",
              "4035       212     N  SRT     3019   \n",
              "4594       225     E  SHP     6166   \n",
              "6406       394     E   BD     5225   \n",
              "8321       210     E   BD     5260   \n",
              "12800      259     N   YU        0   \n",
              "13651        0     S  SRT     3014   \n",
              "15108      351     S   YU     5631   \n",
              "16575      319     W   BD     5232   \n",
              "19517      347     S   YU     5771   \n",
              "24845      261     W   BD     5070   \n",
              "25825      453     S   YU     5776   \n",
              "27374      224     E   BD     5226   \n",
              "29758      473     N   YU     5621   \n",
              "40756      290     N  SRT     3009   \n",
              "40757      290     N  SRT     3009   \n",
              "46572      254     N   YU     5876   \n",
              "47650      460     N   YU     5881   \n",
              "53689      231     N   YU     6041   \n",
              "57916      253     N   YU     5461   \n",
              "62710        0     N  SRT     3026   \n",
              "63096      272     N   YU     5731   \n",
              "73707      519     E   BD     5359   \n",
              "77401      241     S   YU     6031   \n",
              "\n",
              "                                              Desc line_cleaned  \\\n",
              "1421                         Loop Related Failures          SRT   \n",
              "1476              Weather Reports / Related Delays          SRT   \n",
              "2312                   Assault / Employee Involved           YU   \n",
              "3022                             Fire/Smoke Plan B          SRT   \n",
              "4035                         Loop Related Failures          SRT   \n",
              "4594                    Subway Radio System Fault           SHP   \n",
              "6406                          Rail Related Problem           BD   \n",
              "8321   Priority One - Train in Contact With Person           BD   \n",
              "12800                   Work Zone Problems - Track           YU   \n",
              "13651                        Loop Related Failures          SRT   \n",
              "15108                  Traction Power Rail Related           YU   \n",
              "16575                          Miscellaneous Other           BD   \n",
              "19517             Signals - Track Circuit Problems           YU   \n",
              "24845                                       Trucks           BD   \n",
              "25825             Transit Control Related Problems           YU   \n",
              "27374  Priority One - Train in Contact With Person           BD   \n",
              "29758                          Miscellaneous Other           YU   \n",
              "40756             Weather Reports / Related Delays          SRT   \n",
              "40757             Weather Reports / Related Delays          SRT   \n",
              "46572                            Fire/Smoke Plan A           YU   \n",
              "47650                         Rail Related Problem           YU   \n",
              "53689                    Structure Related Problem           YU   \n",
              "57916                         Rail Related Problem           YU   \n",
              "62710             Weather Reports / Related Delays          SRT   \n",
              "63096              Held By Polce - Non-TTC Related           YU   \n",
              "73707                    Assault / Patron Involved           BD   \n",
              "77401  Priority One - Train in Contact With Person           YU   \n",
              "\n",
              "                             station_cleaned  \n",
              "1421                       SCARB CTR STATION  \n",
              "1476                     KENNEDY SRT STATION  \n",
              "2312                          DUPONT STATION  \n",
              "3022                     KENNEDY SRT STATION  \n",
              "4035                       ELLESMERE STATION  \n",
              "4594                  SHEPPARD-YONGE STATION  \n",
              "6406                            JANE STATION  \n",
              "8321                       ISLINGTON STATION  \n",
              "12800                  SHEPPARD WEST STATION  \n",
              "13651                        MCCOWAN STATION  \n",
              "15108                         MUSEUM STATION  \n",
              "16575                       WOODBINE STATION  \n",
              "19517                       EGLINTON STATION  \n",
              "24845                          KEELE STATION  \n",
              "25825                   ST GEORGE YU STATION  \n",
              "27374                     ROYAL YORK STATION  \n",
              "29758                       ST CLAIR STATION  \n",
              "40756                  LAWRENCE EAST STATION  \n",
              "40757                  LAWRENCE EAST STATION  \n",
              "46572       UNION STATION, ST ANDREW STATION  \n",
              "47650  WILSON STATION, SHEPPARD WEST STATION  \n",
              "53689                       LAWRENCE STATION  \n",
              "57916                          UNION STATION  \n",
              "62710                                    SRT  \n",
              "63096                       SHEPPARD STATION  \n",
              "73707                     KENNEDY BD STATION  \n",
              "77401                     SUMMERHILL STATION  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-17ac6c8a-3e0c-40a2-8bc1-cb018e008de2\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Time</th>\n",
              "      <th>Day</th>\n",
              "      <th>Station</th>\n",
              "      <th>Code</th>\n",
              "      <th>Min Delay</th>\n",
              "      <th>Min Gap</th>\n",
              "      <th>Bound</th>\n",
              "      <th>Line</th>\n",
              "      <th>Vehicle</th>\n",
              "      <th>Desc</th>\n",
              "      <th>line_cleaned</th>\n",
              "      <th>station_cleaned</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1421</th>\n",
              "      <td>2022-01-25</td>\n",
              "      <td>21:03</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>SCARBOROUGH CTR STATIO</td>\n",
              "      <td>PRSL</td>\n",
              "      <td>285</td>\n",
              "      <td>291</td>\n",
              "      <td>S</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3010</td>\n",
              "      <td>Loop Related Failures</td>\n",
              "      <td>SRT</td>\n",
              "      <td>SCARB CTR STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1476</th>\n",
              "      <td>2022-01-26</td>\n",
              "      <td>20:15</td>\n",
              "      <td>Wednesday</td>\n",
              "      <td>KENNEDY SRT STATION</td>\n",
              "      <td>MRWEA</td>\n",
              "      <td>315</td>\n",
              "      <td>321</td>\n",
              "      <td>S</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3020</td>\n",
              "      <td>Weather Reports / Related Delays</td>\n",
              "      <td>SRT</td>\n",
              "      <td>KENNEDY SRT STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2312</th>\n",
              "      <td>2022-02-09</td>\n",
              "      <td>06:06</td>\n",
              "      <td>Wednesday</td>\n",
              "      <td>DUPONT STATION</td>\n",
              "      <td>SUAE</td>\n",
              "      <td>240</td>\n",
              "      <td>243</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5426</td>\n",
              "      <td>Assault / Employee Involved</td>\n",
              "      <td>YU</td>\n",
              "      <td>DUPONT STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3022</th>\n",
              "      <td>2022-02-22</td>\n",
              "      <td>06:12</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>KENNEDY SRT STATION</td>\n",
              "      <td>MRPLB</td>\n",
              "      <td>221</td>\n",
              "      <td>226</td>\n",
              "      <td>S</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3014</td>\n",
              "      <td>Fire/Smoke Plan B</td>\n",
              "      <td>SRT</td>\n",
              "      <td>KENNEDY SRT STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4035</th>\n",
              "      <td>2022-03-12</td>\n",
              "      <td>12:59</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>ELLESMERE STATION</td>\n",
              "      <td>PRSL</td>\n",
              "      <td>206</td>\n",
              "      <td>212</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3019</td>\n",
              "      <td>Loop Related Failures</td>\n",
              "      <td>SRT</td>\n",
              "      <td>ELLESMERE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4594</th>\n",
              "      <td>2022-03-23</td>\n",
              "      <td>09:22</td>\n",
              "      <td>Wednesday</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "      <td>PUSRA</td>\n",
              "      <td>220</td>\n",
              "      <td>225</td>\n",
              "      <td>E</td>\n",
              "      <td>SHP</td>\n",
              "      <td>6166</td>\n",
              "      <td>Subway Radio System Fault</td>\n",
              "      <td>SHP</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6406</th>\n",
              "      <td>2022-04-28</td>\n",
              "      <td>06:02</td>\n",
              "      <td>Thursday</td>\n",
              "      <td>JANE STATION</td>\n",
              "      <td>PUTR</td>\n",
              "      <td>388</td>\n",
              "      <td>394</td>\n",
              "      <td>E</td>\n",
              "      <td>BD</td>\n",
              "      <td>5225</td>\n",
              "      <td>Rail Related Problem</td>\n",
              "      <td>BD</td>\n",
              "      <td>JANE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8321</th>\n",
              "      <td>2021-01-05</td>\n",
              "      <td>13:02</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>ISLINGTON STATION</td>\n",
              "      <td>MUPR1</td>\n",
              "      <td>207</td>\n",
              "      <td>210</td>\n",
              "      <td>E</td>\n",
              "      <td>BD</td>\n",
              "      <td>5260</td>\n",
              "      <td>Priority One - Train in Contact With Person</td>\n",
              "      <td>BD</td>\n",
              "      <td>ISLINGTON STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12800</th>\n",
              "      <td>2021-04-30</td>\n",
              "      <td>06:15</td>\n",
              "      <td>Friday</td>\n",
              "      <td>SHEPPARD WEST STATION</td>\n",
              "      <td>PUTWZ</td>\n",
              "      <td>255</td>\n",
              "      <td>259</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Work Zone Problems - Track</td>\n",
              "      <td>YU</td>\n",
              "      <td>SHEPPARD WEST STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13651</th>\n",
              "      <td>2021-05-24</td>\n",
              "      <td>05:48</td>\n",
              "      <td>Monday</td>\n",
              "      <td>MCCOWAN STATION</td>\n",
              "      <td>PRSL</td>\n",
              "      <td>275</td>\n",
              "      <td>0</td>\n",
              "      <td>S</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3014</td>\n",
              "      <td>Loop Related Failures</td>\n",
              "      <td>SRT</td>\n",
              "      <td>MCCOWAN STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15108</th>\n",
              "      <td>2021-06-29</td>\n",
              "      <td>10:21</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>MUSEUM STATION</td>\n",
              "      <td>PUTTP</td>\n",
              "      <td>348</td>\n",
              "      <td>351</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5631</td>\n",
              "      <td>Traction Power Rail Related</td>\n",
              "      <td>YU</td>\n",
              "      <td>MUSEUM STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16575</th>\n",
              "      <td>2021-08-05</td>\n",
              "      <td>20:32</td>\n",
              "      <td>Thursday</td>\n",
              "      <td>WOODBINE STATION</td>\n",
              "      <td>MUO</td>\n",
              "      <td>312</td>\n",
              "      <td>319</td>\n",
              "      <td>W</td>\n",
              "      <td>BD</td>\n",
              "      <td>5232</td>\n",
              "      <td>Miscellaneous Other</td>\n",
              "      <td>BD</td>\n",
              "      <td>WOODBINE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19517</th>\n",
              "      <td>2021-10-12</td>\n",
              "      <td>12:03</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "      <td>PUSTC</td>\n",
              "      <td>343</td>\n",
              "      <td>347</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5771</td>\n",
              "      <td>Signals - Track Circuit Problems</td>\n",
              "      <td>YU</td>\n",
              "      <td>EGLINTON STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24845</th>\n",
              "      <td>2020-01-22</td>\n",
              "      <td>05:57</td>\n",
              "      <td>Wednesday</td>\n",
              "      <td>KEELE STATION</td>\n",
              "      <td>EUTR</td>\n",
              "      <td>258</td>\n",
              "      <td>261</td>\n",
              "      <td>W</td>\n",
              "      <td>BD</td>\n",
              "      <td>5070</td>\n",
              "      <td>Trucks</td>\n",
              "      <td>BD</td>\n",
              "      <td>KEELE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25825</th>\n",
              "      <td>2020-02-13</td>\n",
              "      <td>05:30</td>\n",
              "      <td>Thursday</td>\n",
              "      <td>ST GEORGE YUS STATION</td>\n",
              "      <td>TUCC</td>\n",
              "      <td>450</td>\n",
              "      <td>453</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5776</td>\n",
              "      <td>Transit Control Related Problems</td>\n",
              "      <td>YU</td>\n",
              "      <td>ST GEORGE YU STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27374</th>\n",
              "      <td>2020-03-19</td>\n",
              "      <td>11:26</td>\n",
              "      <td>Thursday</td>\n",
              "      <td>ROYAL YORK STATION</td>\n",
              "      <td>MUPR1</td>\n",
              "      <td>221</td>\n",
              "      <td>224</td>\n",
              "      <td>E</td>\n",
              "      <td>BD</td>\n",
              "      <td>5226</td>\n",
              "      <td>Priority One - Train in Contact With Person</td>\n",
              "      <td>BD</td>\n",
              "      <td>ROYAL YORK STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29758</th>\n",
              "      <td>2020-05-08</td>\n",
              "      <td>16:16</td>\n",
              "      <td>Friday</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "      <td>MUO</td>\n",
              "      <td>446</td>\n",
              "      <td>473</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5621</td>\n",
              "      <td>Miscellaneous Other</td>\n",
              "      <td>YU</td>\n",
              "      <td>ST CLAIR STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40756</th>\n",
              "      <td>2019-02-12</td>\n",
              "      <td>20:28</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "      <td>MRWEA</td>\n",
              "      <td>284</td>\n",
              "      <td>290</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3009</td>\n",
              "      <td>Weather Reports / Related Delays</td>\n",
              "      <td>SRT</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40757</th>\n",
              "      <td>2019-02-12</td>\n",
              "      <td>20:28</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "      <td>MRWEA</td>\n",
              "      <td>284</td>\n",
              "      <td>290</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3009</td>\n",
              "      <td>Weather Reports / Related Delays</td>\n",
              "      <td>SRT</td>\n",
              "      <td>LAWRENCE EAST STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>46572</th>\n",
              "      <td>2019-06-05</td>\n",
              "      <td>12:42</td>\n",
              "      <td>Wednesday</td>\n",
              "      <td>UNION TO ST ANDREW</td>\n",
              "      <td>MUPLA</td>\n",
              "      <td>250</td>\n",
              "      <td>254</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5876</td>\n",
              "      <td>Fire/Smoke Plan A</td>\n",
              "      <td>YU</td>\n",
              "      <td>UNION STATION, ST ANDREW STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>47650</th>\n",
              "      <td>2019-06-25</td>\n",
              "      <td>18:48</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>WILSON TO SHEPPARD WES</td>\n",
              "      <td>PUTR</td>\n",
              "      <td>455</td>\n",
              "      <td>460</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5881</td>\n",
              "      <td>Rail Related Problem</td>\n",
              "      <td>YU</td>\n",
              "      <td>WILSON STATION, SHEPPARD WEST STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>53689</th>\n",
              "      <td>2019-10-22</td>\n",
              "      <td>14:22</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>LAWRENCE STATION</td>\n",
              "      <td>PUTS</td>\n",
              "      <td>228</td>\n",
              "      <td>231</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>6041</td>\n",
              "      <td>Structure Related Problem</td>\n",
              "      <td>YU</td>\n",
              "      <td>LAWRENCE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>57916</th>\n",
              "      <td>2018-01-13</td>\n",
              "      <td>19:19</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>UNION STATION</td>\n",
              "      <td>PUTR</td>\n",
              "      <td>248</td>\n",
              "      <td>253</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5461</td>\n",
              "      <td>Rail Related Problem</td>\n",
              "      <td>YU</td>\n",
              "      <td>UNION STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>62710</th>\n",
              "      <td>2018-04-15</td>\n",
              "      <td>19:03</td>\n",
              "      <td>Sunday</td>\n",
              "      <td>SRT LINE</td>\n",
              "      <td>MRWEA</td>\n",
              "      <td>399</td>\n",
              "      <td>0</td>\n",
              "      <td>N</td>\n",
              "      <td>SRT</td>\n",
              "      <td>3026</td>\n",
              "      <td>Weather Reports / Related Delays</td>\n",
              "      <td>SRT</td>\n",
              "      <td>SRT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>63096</th>\n",
              "      <td>2018-04-23</td>\n",
              "      <td>13:45</td>\n",
              "      <td>Monday</td>\n",
              "      <td>SHEPPARD STATION</td>\n",
              "      <td>SUPOL</td>\n",
              "      <td>269</td>\n",
              "      <td>272</td>\n",
              "      <td>N</td>\n",
              "      <td>YU</td>\n",
              "      <td>5731</td>\n",
              "      <td>Held By Polce - Non-TTC Related</td>\n",
              "      <td>YU</td>\n",
              "      <td>SHEPPARD STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>73707</th>\n",
              "      <td>2018-10-20</td>\n",
              "      <td>17:44</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>KENNEDY BD STATION</td>\n",
              "      <td>SUAP</td>\n",
              "      <td>515</td>\n",
              "      <td>519</td>\n",
              "      <td>E</td>\n",
              "      <td>BD</td>\n",
              "      <td>5359</td>\n",
              "      <td>Assault / Patron Involved</td>\n",
              "      <td>BD</td>\n",
              "      <td>KENNEDY BD STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>77401</th>\n",
              "      <td>2018-12-29</td>\n",
              "      <td>12:09</td>\n",
              "      <td>Saturday</td>\n",
              "      <td>SUMMERHILL STATION</td>\n",
              "      <td>MUPR1</td>\n",
              "      <td>238</td>\n",
              "      <td>241</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>6031</td>\n",
              "      <td>Priority One - Train in Contact With Person</td>\n",
              "      <td>YU</td>\n",
              "      <td>SUMMERHILL STATION</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-17ac6c8a-3e0c-40a2-8bc1-cb018e008de2')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-17ac6c8a-3e0c-40a2-8bc1-cb018e008de2 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-17ac6c8a-3e0c-40a2-8bc1-cb018e008de2');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 72
        }
      ],
      "source": [
        "df_merged[df_merged['Min Delay'] > 200]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xZ1U0ULmuSqv",
        "outputId": "d679dab2-a225-4143-e78a-7a5851962754",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(77510, 13)\n"
          ]
        }
      ],
      "source": [
        "df_merged = df_merged[df_merged['Min Delay'] < 999]\n",
        "\n",
        "print(df_merged.shape)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "vrGPVUfnuSqv",
        "outputId": "e0a2dbb1-dbe9-44e2-c877-9e442251b088",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.5899496839117533"
            ]
          },
          "metadata": {},
          "execution_count": 74
        }
      ],
      "source": [
        "(df_merged['Min Delay'] == 0).mean()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "1FFIfYWFuSqv"
      },
      "outputs": [],
      "source": [
        "df_merged['Min Delay'] = df_merged['Min Delay'].replace(0, 0.5)\n",
        "\n",
        "assert (df_merged['Min Delay'] == 0).sum() == 0"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_t6OpGNyuSqw"
      },
      "outputs": [],
      "source": [
        "with open('/content/drive/MyDrive/subway_eda/stations_ordered.json') as json_file:\n",
        "    stations_ordered = json.load(json_file)\n",
        "    \n",
        "num_stations = {\n",
        "    'YU': 38, \n",
        "    'BD': 31, \n",
        "    'YU_BD': 69,\n",
        "    'SRT': 6,\n",
        "    'SHP': 5,\n",
        "    'YU_BD_SHP': 74,\n",
        "    'ALL': 80  \n",
        "}\n",
        "\n",
        "all_stations = sum(stations_ordered.values(), [])"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "stations_ordered.keys()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "87NGXsSGJ27t",
        "outputId": "59cd6e8a-a4f1-4dc2-c7ed-535ed1e2f28d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "dict_keys(['YU', 'BD', 'SRT', 'SHP'])"
            ]
          },
          "metadata": {},
          "execution_count": 77
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "6ladxkj6uSqw",
        "outputId": "bb1b0aa3-a5ed-47ad-f54f-1f89349d3f78",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(164449, 15)\n"
          ]
        }
      ],
      "source": [
        "df_mult_station = df_merged[(df_merged.station_cleaned.str.contains(',')) | \n",
        "                     (df_merged.station_cleaned.str.contains('_')) |\n",
        "                     (df_merged.station_cleaned.isin(['YU', 'BD', 'SHP', 'SRT', 'ALL']))].copy()\n",
        "\n",
        "new_df = pd.DataFrame()\n",
        "\n",
        "for index, row in df_mult_station.iterrows():\n",
        "    \n",
        "    st = row['station_cleaned']\n",
        "    \n",
        "    if st in ['YU', 'BD', 'SRT', 'SHP']:\n",
        "        df_i = pd.concat([row.to_frame(0).T] * num_stations[st])\n",
        "        df_i.station_cleaned = stations_ordered[st]\n",
        "    \n",
        "    elif '_' in st:\n",
        "        df_i = pd.concat([row.to_frame(0).T] * num_stations[st])       \n",
        "        df_i.station_cleaned = sum([stations_ordered[st_i] for st_i in st.split('_')], [])\n",
        "        \n",
        "    elif st == 'ALL':\n",
        "        df_i = pd.concat([row.to_frame(0).T] * num_stations[st])       \n",
        "        df_i.station_cleaned = sum([stations_ordered[k] for k in stations_ordered], [])\n",
        "    \n",
        "    else:\n",
        "        \n",
        "        fr_stn, to_stn = st.split(', ')\n",
        "        fr_idx_temp = stations_ordered[row['line_cleaned']].index(fr_stn)\n",
        "        to_idx_temp = stations_ordered[row['line_cleaned']].index(to_stn)\n",
        "        fr_idx, to_idx = min(fr_idx_temp, to_idx_temp), max(fr_idx_temp, to_idx_temp)\n",
        "        \n",
        "        stations = stations_ordered[row['line_cleaned']][fr_idx:(to_idx + 1)]\n",
        "        df_i = pd.concat([row.to_frame(0).T] * len(stations))\n",
        "        df_i.station_cleaned = stations\n",
        "    \n",
        "    df_i['Min Delay'] /= df_i.shape[0]\n",
        "    df_i['indicator_ns'] = [1] + [0] * (df_i.shape[0] - 1)\n",
        "    df_i['indicator_station'] = [1] * df_i.shape[0]\n",
        "    \n",
        "    new_df = new_df.append(df_i)\n",
        "\n",
        "print(new_df.shape)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "38_pI_eTuSqw",
        "outputId": "b759f14f-2861-4a48-bbb9-eb5cad2ea37b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 659
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            Date   Time        Day                 Station   Code  Min Delay  \\\n",
              "165   2022-01-03  23:42     Monday  SHEPPARD-YONGE STATION   TUSC        0.5   \n",
              "224   2022-01-04  10:19    Tuesday  SHEPPARD-YONGE STATION   MUSC        0.5   \n",
              "225   2022-01-04  22:20    Tuesday  SHEPPARD-YONGE STATION  TUMVS        3.0   \n",
              "288   2022-01-05  12:49  Wednesday  SHEPPARD-YONGE STATION   SUDP        3.0   \n",
              "516   2022-01-09  08:16     Sunday  SHEPPARD-YONGE STATION  TUMVS        0.5   \n",
              "...          ...    ...        ...                     ...    ...        ...   \n",
              "69212 2018-08-06  16:50     Monday         SPADINA STATION  MUIRS        0.5   \n",
              "69704 2018-08-14  15:53    Tuesday         SPADINA STATION   MUIS        0.5   \n",
              "70023 2018-08-19  22:02     Sunday         SPADINA STATION   EUBK        5.0   \n",
              "70069 2018-08-20  18:05     Monday         SPADINA STATION   MUIS       10.0   \n",
              "70556 2018-08-28  09:01    Tuesday         SPADINA STATION  MUIRS        0.5   \n",
              "\n",
              "       Min Gap Bound Line  Vehicle  \\\n",
              "165          0     E  SHP     6196   \n",
              "224          0     E  SHP     6181   \n",
              "225          8     W  SHP     6186   \n",
              "288          8     E  SHP     6146   \n",
              "516          0     E  SHP     6176   \n",
              "...        ...   ...  ...      ...   \n",
              "69212        0         BD        0   \n",
              "69704        0     E   BD        0   \n",
              "70023       10     S   YU     5616   \n",
              "70069       13     E   BD     5335   \n",
              "70556        0         YU        0   \n",
              "\n",
              "                                                    Desc line_cleaned  \\\n",
              "165                                Operator Overspeeding          SHP   \n",
              "224                          Miscellaneous Speed Control          SHP   \n",
              "225                             Operator Violated Signal          SHP   \n",
              "288                                    Disorderly Patron          SHP   \n",
              "516                             Operator Violated Signal          SHP   \n",
              "...                                                  ...          ...   \n",
              "69212  Injured or ill Customer (In Station) - Medical...           BD   \n",
              "69704  Injured or ill Customer (In Station) - Transpo...           BD   \n",
              "70023                                             Brakes           YU   \n",
              "70069  Injured or ill Customer (In Station) - Transpo...           BD   \n",
              "70556  Injured or ill Customer (In Station) - Medical...           YU   \n",
              "\n",
              "              station_cleaned  \n",
              "165    SHEPPARD-YONGE STATION  \n",
              "224    SHEPPARD-YONGE STATION  \n",
              "225    SHEPPARD-YONGE STATION  \n",
              "288    SHEPPARD-YONGE STATION  \n",
              "516    SHEPPARD-YONGE STATION  \n",
              "...                       ...  \n",
              "69212         SPADINA STATION  \n",
              "69704         SPADINA STATION  \n",
              "70023         SPADINA STATION  \n",
              "70069         SPADINA STATION  \n",
              "70556         SPADINA STATION  \n",
              "\n",
              "[697 rows x 13 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-ba29e35c-18ed-4e36-9b66-a8d4fa7e0606\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Time</th>\n",
              "      <th>Day</th>\n",
              "      <th>Station</th>\n",
              "      <th>Code</th>\n",
              "      <th>Min Delay</th>\n",
              "      <th>Min Gap</th>\n",
              "      <th>Bound</th>\n",
              "      <th>Line</th>\n",
              "      <th>Vehicle</th>\n",
              "      <th>Desc</th>\n",
              "      <th>line_cleaned</th>\n",
              "      <th>station_cleaned</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>165</th>\n",
              "      <td>2022-01-03</td>\n",
              "      <td>23:42</td>\n",
              "      <td>Monday</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "      <td>TUSC</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0</td>\n",
              "      <td>E</td>\n",
              "      <td>SHP</td>\n",
              "      <td>6196</td>\n",
              "      <td>Operator Overspeeding</td>\n",
              "      <td>SHP</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>224</th>\n",
              "      <td>2022-01-04</td>\n",
              "      <td>10:19</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "      <td>MUSC</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0</td>\n",
              "      <td>E</td>\n",
              "      <td>SHP</td>\n",
              "      <td>6181</td>\n",
              "      <td>Miscellaneous Speed Control</td>\n",
              "      <td>SHP</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>225</th>\n",
              "      <td>2022-01-04</td>\n",
              "      <td>22:20</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "      <td>TUMVS</td>\n",
              "      <td>3.0</td>\n",
              "      <td>8</td>\n",
              "      <td>W</td>\n",
              "      <td>SHP</td>\n",
              "      <td>6186</td>\n",
              "      <td>Operator Violated Signal</td>\n",
              "      <td>SHP</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>288</th>\n",
              "      <td>2022-01-05</td>\n",
              "      <td>12:49</td>\n",
              "      <td>Wednesday</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "      <td>SUDP</td>\n",
              "      <td>3.0</td>\n",
              "      <td>8</td>\n",
              "      <td>E</td>\n",
              "      <td>SHP</td>\n",
              "      <td>6146</td>\n",
              "      <td>Disorderly Patron</td>\n",
              "      <td>SHP</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>516</th>\n",
              "      <td>2022-01-09</td>\n",
              "      <td>08:16</td>\n",
              "      <td>Sunday</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "      <td>TUMVS</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0</td>\n",
              "      <td>E</td>\n",
              "      <td>SHP</td>\n",
              "      <td>6176</td>\n",
              "      <td>Operator Violated Signal</td>\n",
              "      <td>SHP</td>\n",
              "      <td>SHEPPARD-YONGE STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>69212</th>\n",
              "      <td>2018-08-06</td>\n",
              "      <td>16:50</td>\n",
              "      <td>Monday</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "      <td>MUIRS</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>BD</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Medical...</td>\n",
              "      <td>BD</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>69704</th>\n",
              "      <td>2018-08-14</td>\n",
              "      <td>15:53</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0</td>\n",
              "      <td>E</td>\n",
              "      <td>BD</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>BD</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>70023</th>\n",
              "      <td>2018-08-19</td>\n",
              "      <td>22:02</td>\n",
              "      <td>Sunday</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "      <td>EUBK</td>\n",
              "      <td>5.0</td>\n",
              "      <td>10</td>\n",
              "      <td>S</td>\n",
              "      <td>YU</td>\n",
              "      <td>5616</td>\n",
              "      <td>Brakes</td>\n",
              "      <td>YU</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>70069</th>\n",
              "      <td>2018-08-20</td>\n",
              "      <td>18:05</td>\n",
              "      <td>Monday</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "      <td>MUIS</td>\n",
              "      <td>10.0</td>\n",
              "      <td>13</td>\n",
              "      <td>E</td>\n",
              "      <td>BD</td>\n",
              "      <td>5335</td>\n",
              "      <td>Injured or ill Customer (In Station) - Transpo...</td>\n",
              "      <td>BD</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>70556</th>\n",
              "      <td>2018-08-28</td>\n",
              "      <td>09:01</td>\n",
              "      <td>Tuesday</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "      <td>MUIRS</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0</td>\n",
              "      <td></td>\n",
              "      <td>YU</td>\n",
              "      <td>0</td>\n",
              "      <td>Injured or ill Customer (In Station) - Medical...</td>\n",
              "      <td>YU</td>\n",
              "      <td>SPADINA STATION</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>697 rows × 13 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ba29e35c-18ed-4e36-9b66-a8d4fa7e0606')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-ba29e35c-18ed-4e36-9b66-a8d4fa7e0606 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-ba29e35c-18ed-4e36-9b66-a8d4fa7e0606');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 91
        }
      ],
      "source": [
        "df_merged[(~df_merged.station_cleaned.isin(all_stations)) &\n",
        "   (~df_merged.station_cleaned.str.contains(',')) & \n",
        "   (~df_merged.station_cleaned.str.contains('_')) & \n",
        "   (~df_merged.station_cleaned.isin(['YU', 'BD', 'SHP', 'SRT', 'ALL']))]\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "zNg330N5uSqx",
        "outputId": "8a97755a-12dd-4b28-d51a-d39924d094a5",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(240667, 30)\n"
          ]
        }
      ],
      "source": [
        "df_mult_line = df_merged[(~df_merged.station_cleaned.isin(all_stations)) &\n",
        "                  (~df_merged.station_cleaned.str.contains(',')) & \n",
        "                  (~df_merged.station_cleaned.str.contains('_')) & \n",
        "                  (~df_merged.station_cleaned.isin(['YU', 'BD', 'SHP', 'SRT', 'ALL']))].copy()\n",
        "\n",
        "for index, row in df_mult_line.iterrows():\n",
        "    \n",
        "    stations = ['SPADINA YU STATION', 'SPADINA BD STATION']\n",
        "    df_i = pd.concat([row.to_frame(0).T] * len(stations))\n",
        "    df_i.station_cleaned = stations\n",
        "    \n",
        "    df_i['Min Delay'] /= df_i.shape[0]\n",
        "    df_i['indicator_ns'] = [1] + [0] * (df_i.shape[0] - 1)\n",
        "    df_i['indicator_station'] = [1] * df_i.shape[0]\n",
        "    \n",
        "    new_df = new_df.append(df_i)\n",
        "\n",
        "print(new_df.shape)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "scrolled": true,
        "id": "1G_lDVNZuSqx",
        "outputId": "4f8bcb76-dcd1-4a73-f994-4e03c5696083",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(239273, 15)\n",
            "        Date   Time     Day                 Station Code Min Delay Min Gap  \\\n",
            "0 2022-01-03  22:00  Monday  YONGE-UNIVERSITY AND B  MUO  0.007246       0   \n",
            "1 2022-01-03  22:00  Monday  YONGE-UNIVERSITY AND B  MUO  0.007246       0   \n",
            "2 2022-01-03  22:00  Monday  YONGE-UNIVERSITY AND B  MUO  0.007246       0   \n",
            "3 2022-01-03  22:00  Monday  YONGE-UNIVERSITY AND B  MUO  0.007246       0   \n",
            "4 2022-01-03  22:00  Monday  YONGE-UNIVERSITY AND B  MUO  0.007246       0   \n",
            "\n",
            "  Bound   Line Vehicle                 Desc line_cleaned  \\\n",
            "0        YU/BD       0  Miscellaneous Other        YU_BD   \n",
            "1        YU/BD       0  Miscellaneous Other        YU_BD   \n",
            "2        YU/BD       0  Miscellaneous Other        YU_BD   \n",
            "3        YU/BD       0  Miscellaneous Other        YU_BD   \n",
            "4        YU/BD       0  Miscellaneous Other        YU_BD   \n",
            "\n",
            "           station_cleaned  indicator_ns  indicator_station  \n",
            "0       VAUGHAN MC STATION             1                  1  \n",
            "1      HIGHWAY 407 STATION             0                  1  \n",
            "2  PIONEER VILLAGE STATION             0                  1  \n",
            "3  YORK UNIVERSITY STATION             0                  1  \n",
            "4       FINCH WEST STATION             0                  1  \n"
          ]
        }
      ],
      "source": [
        "df_no_mult = df_merged[df_merged.station_cleaned.isin(all_stations)].copy()\n",
        "df_no_mult['indicator_ns'] = 1\n",
        "df_no_mult['indicator_station'] = 1\n",
        "\n",
        "new_df = new_df.append(df_no_mult).reset_index(drop = True)\n",
        "\n",
        "print(new_df.shape)\n",
        "print(new_df.head())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KMxKgEl4uSqy"
      },
      "outputs": [],
      "source": [
        "# Check that I did not miss any entries\n",
        "assert df_mult_line.shape[0] + df_mult_station.shape[0] + df_merged[df_merged.station_cleaned.isin(all_stations)].shape[0] == df_merged.shape[0]\n",
        "assert df_merged.shape[0] == new_df.indicator_ns.sum()\n",
        "assert round(df_merged['Min Delay'].sum(), 2) == round(new_df['Min Delay'].sum(), 2)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dUENtrXguSqz"
      },
      "outputs": [],
      "source": [
        "import calendar\n",
        "new_df['weekday'] = list(map(lambda x: calendar.day_name[x.weekday()], new_df.Date))\n",
        "\n",
        "assert new_df[new_df.weekday != new_df.Day].shape[0] == 0\n",
        "\n",
        "new_df = new_df.drop(columns = 'Day') # Just need to keep one of the two columns snce they are the same"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "new_df['year'] = list(map(lambda x: x.year, new_df.Date))\n",
        "new_df['month'] = list(map(lambda x: x.month, new_df.Date))\n",
        "new_df['day'] = list(map(lambda x: x.month, new_df.Date))\n",
        "new_df['hour'] = list(map(lambda x: int(x.split(':')[0]), new_df.Time))"
      ],
      "metadata": {
        "id": "gPhfuzH_ZEA-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "new_df['Date'].agg(['min', 'max'])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QHgc8I8Y3vKG",
        "outputId": "2acd7de5-af06-4776-9678-06e1e3b2173a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "min   2018-01-01\n",
              "max   2022-05-31\n",
              "Name: Date, dtype: datetime64[ns]"
            ]
          },
          "metadata": {},
          "execution_count": 85
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "new_df.groupby('Date')['indicator_ns'].sum().hist()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 283
        },
        "id": "BPOqilMQ3vOv",
        "outputId": "f23546db-a667-4de7-8895-aa1be63ba8b5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<matplotlib.axes._subplots.AxesSubplot at 0x7fbec3f192d0>"
            ]
          },
          "metadata": {},
          "execution_count": 86
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<Figure size 432x288 with 1 Axes>"
            ],
            "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD4CAYAAAAXUaZHAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAVbElEQVR4nO3df5BdZX3H8ffH8CtlbQJC78Qk7aZDqoNkDHCHH4N/3IVaAzgGZxDDpBIQZ+0MTLHGluA/ai3TODVSnVrqapDYWpYMQskE0KGBrXWmgESQDUTGFYJkJyQiIbqAjIvf/nGfjJfN7t67e3/ucz+vmTt7z3PO2fM9z5772bPPnnuPIgIzM8vLW9pdgJmZNZ7D3cwsQw53M7MMOdzNzDLkcDczy9BR7S4A4KSTTore3t52l3GEV155heOPP77dZbSV+8B90O37D53bBzt37nwxIk6ebF5HhHtvby+PPvpou8s4wtDQEKVSqd1ltJX7wH3Q7fsPndsHkp6bap6HZczMMuRwNzPLkMPdzCxDDnczsww53M3MMuRwNzPLkMPdzCxDDnczswzVHO6S5kl6TNL2NL1M0sOSRiTdLumY1H5smh5J83ubU7qZmU1lJu9QvQ7YDfxhmv4CcFNEDEr6N+Bq4Ob09WBEnCJpTVruww2s2dqod8M9bdnuno0Xt2W7ZnNVTWfukpYAFwPfSNMCzgfuSItsAS5Jz1enadL8C9LyZmbWIqrlNnuS7gD+EXgr8CngSuChiDglzV8K3BcRp0naBayKiL1p3s+AsyPixQnfsx/oBygUCmcODg42bKcaZWxsjJ6ennaX0VYT+2B49FBb6lixeEFbtgs+Drp9/6Fz+6Cvr29nRBQnm1d1WEbS+4EDEbFTUqlRRUXEADAAUCwWoxM/lKdTPyyolSb2wZXtGpZZW6q6TLN0+3HQ7fsPc7MPahlzPw/4gKSLgOMoj7l/GVgo6aiIGAeWAKNp+VFgKbBX0lHAAuCXDa/czMymVHXMPSJuiIglEdELrAEeiIi1wIPApWmxdcDd6fm2NE2a/0DUMvZjZmYNU8917tcDn5Q0ArwN2JzaNwNvS+2fBDbUV6KZmc3UjG7WERFDwFB6/gxw1iTL/Ab4UANqsym08nLE9SvG2zbObmaz53eompllyOFuZpYhh7uZWYYc7mZmGXK4m5llyOFuZpYhh7uZWYYc7mZmGXK4m5llyOFuZpYhh7uZWYYc7mZmGXK4m5llyOFuZpYhh7uZWYYc7mZmGaoa7pKOk/SIpB9LelLS51L7rZKelfR4eqxM7ZL0FUkjkp6QdEazd8LMzN6sljsxvQ6cHxFjko4GfiDpvjTvbyPijgnLXwgsT4+zgZvTVzMza5FabpAdETGWJo9Oj+lueL0a+FZa7yFgoaRF9ZdqZma1UsR0OZ0WkuYBO4FTgK9GxPWSbgXOpXxmvwPYEBGvS9oObIyIH6R1dwDXR8SjE75nP9APUCgUzhwcHGzcXjXI2NgYPT097S7jCMOjh1q2rcJ82P9ayzY3pRWLF7Rt2516HLRKt+8/dG4f9PX17YyI4mTzarpBdkS8AayUtBC4S9JpwA3AC8AxwABwPfD3tRYVEQNpPYrFYpRKpVpXbZmhoSE6sa5W3rB6/YpxNg3P6D7qTbFnbalt2+7U46BVun3/YW72wYyulomIl4EHgVURsS8NvbwOfBM4Ky02CiytWG1JajMzsxap5WqZk9MZO5LmA+8FfnJ4HF2SgEuAXWmVbcAV6aqZc4BDEbGvKdWbmdmkavl7exGwJY27vwXYGhHbJT0g6WRAwOPAX6Xl7wUuAkaAV4GrGl+2mZlNp2q4R8QTwOmTtJ8/xfIBXFN/aWZmNlt+h6qZWYYc7mZmGXK4m5llyOFuZpYhh7uZWYYc7mZmGXK4m5llyOFuZpYhh7uZWYYc7mZmGXK4m5llyOFuZpYhh7uZWYYc7mZmGXK4m5llyOFuZpYhh7uZWYZquYfqcZIekfRjSU9K+lxqXybpYUkjkm6XdExqPzZNj6T5vc3dBTMzm6iWM/fXgfMj4t3ASmBVuvH1F4CbIuIU4CBwdVr+auBgar8pLWdmZi1UNdyjbCxNHp0eAZwP3JHatwCXpOer0zRp/gWS1LCKzcysKpXvZ11lIWkesBM4Bfgq8E/AQ+nsHElLgfsi4jRJu4BVEbE3zfsZcHZEvDjhe/YD/QCFQuHMwcHBxu1Vg4yNjdHT09PuMo4wPHqoZdsqzIf9r7Vsc1NasXhB27bdqcdBq3T7/kPn9kFfX9/OiChONu+oWr5BRLwBrJS0ELgLeGe9RUXEADAAUCwWo1Qq1fstG25oaIhOrOvKDfe0bFvrV4yzabimw6Sp9qwttW3bnXoctEq37z/MzT6Y0dUyEfEy8CBwLrBQ0uFX/RJgND0fBZYCpPkLgF82pFozM6tJ1VMySScDv42IlyXNB95L+Z+kDwKXAoPAOuDutMq2NP1/af4DUcvYj9k0elv418pEt646vm3bNputWv7eXgRsSePubwG2RsR2SU8Bg5L+AXgM2JyW3wz8u6QR4CVgTRPqNjOzaVQN94h4Ajh9kvZngLMmaf8N8KGGVGdmZrPid6iamWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhhzuZmYZqhrukpZKelDSU5KelHRdav+spFFJj6fHRRXr3CBpRNLTkt7XzB0wM7Mj1XKbvXFgfUT8SNJbgZ2S7k/zboqIL1YuLOlUyrfWexfwduC/Jf1ZRLzRyMLNzGxqVc/cI2JfRPwoPf81sBtYPM0qq4HBiHg9Ip4FRpjkdnxmZtY8Mxpzl9RL+X6qD6emayU9IekWSSektsXA8xWr7WX6XwZmZtZgiojaFpR6gP8BboyIOyUVgBeBAD4PLIqIj0r6F+ChiPiPtN5m4L6IuGPC9+sH+gEKhcKZg4ODjdqnhhkbG6Onp6fdZRxhePRQy7ZVmA/7X2vZ5jrSsgXzOvI4aJVOfR20Uqf2QV9f386IKE42r5YxdyQdDXwH+HZE3AkQEfsr5n8d2J4mR4GlFasvSW1vEhEDwABAsViMUqlUSyktNTQ0RCfWdeWGe1q2rfUrxtk0XNNhkq1bVx3fkcdBq3Tq66CV5mIf1HK1jIDNwO6I+FJF+6KKxT4I7ErPtwFrJB0raRmwHHikcSWbmVk1tZySnQd8BBiW9Hhq+zRwuaSVlIdl9gAfB4iIJyVtBZ6ifKXNNb5SxsystaqGe0T8ANAks+6dZp0bgRvrqMvMzOrgd6iamWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhhzuZmYZcribmWWou+/CYFaD4dFDLb1BymF7Nl7c8m1aPnzmbmaWIYe7mVmGHO5mZhmq5R6qSyU9KOkpSU9Kui61nyjpfkk/TV9PSO2S9BVJI5KekHRGs3fCzMzerJYz93FgfUScCpwDXCPpVGADsCMilgM70jTAhZRvir0c6AdubnjVZmY2rarhHhH7IuJH6fmvgd3AYmA1sCUttgW4JD1fDXwryh4CFkpa1PDKzcxsSoqI2heWeoHvA6cBP4+IhaldwMGIWChpO7Ax3VgbSTuA6yPi0Qnfq5/ymT2FQuHMwcHB+vemwcbGxujp6Wl3GUcYHj3Usm0V5sP+11q2uY7Urj5YsXhB6zc6iU59HbRSp/ZBX1/fzogoTjav5uvcJfUA3wE+ERG/Kud5WUSEpNp/S5TXGQAGAIrFYpRKpZms3hJDQ0N0Yl2tvOZ6/YpxNg1399sh2tUHe9aWWr7NyXTq66CV5mIf1HS1jKSjKQf7tyPiztS8//BwS/p6ILWPAksrVl+S2szMrEVquVpGwGZgd0R8qWLWNmBder4OuLui/Yp01cw5wKGI2NfAms3MrIpa/tY8D/gIMCzp8dT2aWAjsFXS1cBzwGVp3r3ARcAI8CpwVUMrNjOzqqqGe/rHqKaYfcEkywdwTZ11mZlZHfwOVTOzDDnczcwy5HA3M8uQw93MLEMOdzOzDDnczcwy5HA3M8uQw93MLEMOdzOzDDnczcwy5HA3M8uQw93MLEMOdzOzDHX3LXbq1NvCOyKZmc2Ez9zNzDLkcDczy5DD3cwsQ7XcQ/UWSQck7apo+6ykUUmPp8dFFfNukDQi6WlJ72tW4WZmNrVaztxvBVZN0n5TRKxMj3sBJJ0KrAHeldb5V0nzGlWsmZnVpmq4R8T3gZdq/H6rgcGIeD0inqV8k+yz6qjPzMxmoZ5LIa+VdAXwKLA+Ig4Ci4GHKpbZm9qOIKkf6AcoFAoMDQ3VUUpzjI2NTVvX+hXjrSumTQrzu2M/p9OuPuiU10S110E3mIt9MNtwvxn4PBDp6ybgozP5BhExAAwAFIvFKJVKsyyleYaGhpiuriu74Dr39SvG2TTc3W+HaFcf7Flbavk2J1PtddAN5mIfzOpqmYjYHxFvRMTvgK/z+6GXUWBpxaJLUpuZmbXQrMJd0qKKyQ8Ch6+k2QaskXSspGXAcuCR+ko0M7OZqvq3pqTbgBJwkqS9wGeAkqSVlIdl9gAfB4iIJyVtBZ4CxoFrIuKN5pRuZmZTqRruEXH5JM2bp1n+RuDGeooyM7P6+B2qZmYZcribmWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhhzuZmYZcribmWXI4W5mliGHu5lZhqqGu6RbJB2QtKui7URJ90v6afp6QmqXpK9IGpH0hKQzmlm8mZlNrpYz91uBVRPaNgA7ImI5sCNNA1xI+b6py4F+4ObGlGlmZjNRNdwj4vvASxOaVwNb0vMtwCUV7d+KsoeAhRNupm1mZi0w2zH3QkTsS89fAArp+WLg+Yrl9qY2MzNroao3yK4mIkJSzHQ9Sf2Uh24oFAoMDQ3VW0rDjY2NTVvX+hXjrSumTQrzu2M/p9OuPuiU10S110E3mIt9MNtw3y9pUUTsS8MuB1L7KLC0Yrklqe0IETEADAAUi8UolUqzLKV5hoaGmK6uKzfc07pi2mT9inE2Ddd9DjCntasP9qwttXybk6n2OugGc7EPZjsssw1Yl56vA+6uaL8iXTVzDnCoYvjGzMxapOrpiKTbgBJwkqS9wGeAjcBWSVcDzwGXpcXvBS4CRoBXgauaULOZmVVRNdwj4vIpZl0wybIBXFNvUWZmVh+/Q9XMLEMOdzOzDHX3ZRBmHay3TVdj7dl4cVu2a43lM3czsww53M3MMuRwNzPLkMPdzCxDDnczsww53M3MMuRwNzPLkMPdzCxDDnczsww53M3MMuRwNzPLkMPdzCxDDnczsww53M3MMlTXR/5K2gP8GngDGI+IoqQTgduBXmAPcFlEHKyvTDMzm4lGnLn3RcTKiCim6Q3AjohYDuxI02Zm1kLNGJZZDWxJz7cAlzRhG2ZmNg2V72k9y5WlZ4GDQABfi4gBSS9HxMI0X8DBw9MT1u0H+gEKhcKZg4ODs66jWcbGxujp6Zly/vDooRZW0x6F+bD/tXZX0V7d1gcrFi9403S110E36NQ+6Ovr21kxavIm9d5m7z0RMSrpj4D7Jf2kcmZEhKRJf3tExAAwAFAsFqNUKtVZSuMNDQ0xXV1Xtuk2aK20fsU4m4a7+26M3dYHe9aW3jRd7XXQDeZiH9Q1LBMRo+nrAeAu4Cxgv6RFAOnrgXqLNDOzmZl1uEs6XtJbDz8H/gLYBWwD1qXF1gF311ukmZnNTD1/axaAu8rD6hwF/GdEfFfSD4Gtkq4GngMuq79MMzObiVmHe0Q8A7x7kvZfAhfUU5SZmdXH71A1M8uQw93MLEMOdzOzDHXPxbtmVpPeCe/fWL9ivGXv6diz8eKWbKcb+MzdzCxDDnczsww53M3MMuRwNzPLkMPdzCxDDnczsww53M3MMjTnr3OfeE1uI7Xy+l4zs0bymbuZWYYc7mZmGXK4m5llyOFuZpahOf8PVTPLRzMvkJhOjh9Y1rQzd0mrJD0taUTShmZtx8zMjtSUcJc0D/gqcCFwKnC5pFObsS0zMztSs4ZlzgJG0n1WkTQIrAaeatL2zMxmrdpwUDPf89KsISFFROO/qXQpsCoiPpamPwKcHRHXVizTD/SnyXcATze8kPqdBLzY7iLazH3gPuj2/YfO7YM/iYiTJ5vRtn+oRsQAMNCu7ddC0qMRUWx3He3kPnAfdPv+w9zsg2b9Q3UUWFoxvSS1mZlZCzQr3H8ILJe0TNIxwBpgW5O2ZWZmEzRlWCYixiVdC3wPmAfcEhFPNmNbTdbRw0Yt4j5wH3T7/sMc7IOm/EPVzMzayx8/YGaWIYe7mVmGHO6JpKWSHpT0lKQnJV2X2k+UdL+kn6avJ7S71maSNE/SY5K2p+llkh5OHyNxe/oHebYkLZR0h6SfSNot6dwuPAb+Jr0Gdkm6TdJxuR8Hkm6RdEDSroq2SX/uKvtK6osnJJ3Rvsqn5nD/vXFgfUScCpwDXJM+MmEDsCMilgM70nTOrgN2V0x/AbgpIk4BDgJXt6Wq1vky8N2IeCfwbsp90TXHgKTFwF8DxYg4jfIFEWvI/zi4FVg1oW2qn/uFwPL06AdublGNMxMRfkzyAO4G3kv5nbOLUtsi4Ol219bEfV5C+SA+H9gOiPK78o5K888FvtfuOpu4/wuAZ0kXGlS0d9MxsBh4HjiR8tV024H3dcNxAPQCu6r93IGvAZdPtlwnPXzmPglJvcDpwMNAISL2pVkvAIU2ldUK/wz8HfC7NP024OWIGE/Teym/+HO1DPgF8M00NPUNScfTRcdARIwCXwR+DuwDDgE76a7j4LCpfu6HfwEe1pH94XCfQFIP8B3gExHxq8p5Uf41neW1o5LeDxyIiJ3trqWNjgLOAG6OiNOBV5gwBJPzMQCQxpVXU/5F93bgeI4crug6c/Hn7nCvIOloysH+7Yi4MzXvl7QozV8EHGhXfU12HvABSXuAQcpDM18GFko6/Ga33D9GYi+wNyIeTtN3UA77bjkGAP4ceDYifhERvwXupHxsdNNxcNhUP/c58fEqDvdEkoDNwO6I+FLFrG3AuvR8HeWx+OxExA0RsSQiein/A+2BiFgLPAhcmhbLdv8BIuIF4HlJ70hNF1D+mOquOAaSnwPnSPqD9Jo43AddcxxUmOrnvg24Il01cw5wqGL4pmP4HaqJpPcA/wsM8/sx509THnffCvwx8BxwWUS81JYiW0RSCfhURLxf0p9SPpM/EXgM+MuIeL2d9TWTpJXAN4BjgGeAqyifBHXNMSDpc8CHKV9B9hjwMcpjytkeB5JuA0qUP9p3P/AZ4L+Y5Oeefun9C+XhqleBqyLi0XbUPR2Hu5lZhjwsY2aWIYe7mVmGHO5mZhlyuJuZZcjhbmaWIYe7mVmGHO5mZhn6f95ZeDRBv1dQAAAAAElFTkSuQmCC\n"
          },
          "metadata": {
            "needs_background": "light"
          }
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "new_df.groupby('Date')['indicator_ns'].sum().sort_values()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DnwGU2_KZfBc",
        "outputId": "b5d95e88-abfd-42c0-b39f-c4cfc95d44ab"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Date\n",
              "2020-06-14     13\n",
              "2020-08-30     15\n",
              "2020-06-07     18\n",
              "2021-07-18     19\n",
              "2020-05-24     19\n",
              "             ... \n",
              "2018-07-09     92\n",
              "2022-02-04     94\n",
              "2018-06-01     97\n",
              "2018-10-15     99\n",
              "2019-02-27    107\n",
              "Name: indicator_ns, Length: 1612, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 87
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "new_df.columns = [re.sub(' ', '_', x.lower()) for x in new_df.columns]\n",
        "        \n",
        "new_df[['date', 'time', \n",
        "        'year', 'month', 'day', \n",
        "        'weekday', 'hour',\n",
        "        'line_cleaned', 'station_cleaned', \n",
        "        'code', 'desc', 'min_delay', \n",
        "        'indicator_ns', 'indicator_station']].to_csv('ttc_subway_delay_data.csv', index = False)"
      ],
      "metadata": {
        "id": "ti9_ij_oZwWv"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0lPQrZJzuSqz"
      },
      "source": [
        "Next, I extracted the year, month, day and hour as separate features, since I'd be interested in studying seasonality of delay patterns."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hInO1ix2uSq4"
      },
      "outputs": [],
      "source": [
        "#!cp /content/ttc_subway_delay_data.csv /content/drive/MyDrive/subway_eda"
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "hfu7DQQknq9I"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.4"
    },
    "toc": {
      "base_numbering": 1,
      "nav_menu": {},
      "number_sections": true,
      "sideBar": true,
      "skip_h1_title": false,
      "title_cell": "Table of Contents",
      "title_sidebar": "Contents",
      "toc_cell": false,
      "toc_position": {
        "height": "calc(100% - 180px)",
        "left": "10px",
        "top": "150px",
        "width": "165.5px"
      },
      "toc_section_display": true,
      "toc_window_display": true
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}