2_cleaning_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notebook 2/4: Data cleaning (daily traffic and hourly profiles)\n",
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of contents\n",
    "1. [Purpose of this notebook](#1)\n",
    "2. [Daily traffic](#2)\n",
    "    1. [Description & steps](#2A)\n",
    "    2. [Cleaning](#2B)\n",
    "        1. [Rename columns](#2Ba)\n",
    "        2. [Type conversion](#2Bb)\n",
    "        3. [Join dataframes](#2Bc)\n",
    "        4. [Prepare to filter](#2Bd)\n",
    "        5. [Filter Paris metro stations](#2Be)\n",
    "        6. [Missing values](#2Bf)\n",
    "        7. [Drop columns](#2Bg)\n",
    "    3. [Export to csv](#2C)\n",
    "3. [Hourly profiles](#3)\n",
    "    1. [Description & steps](#3A)\n",
    "    2. [Cleaning](#3B)\n",
    "        1. [Rename columns](#3Ba)\n",
    "        2. [Type conversion](#3Bb)\n",
    "        3. [Filter](#3Bc)\n",
    "        4. [Join](#3Bd)\n",
    "        5. [Compute mean hourly profile of 1st & 1nd semesters](#3Be)\n",
    "        6. [Check data completeness](#3Bf)\n",
    "        7. [Filter columns](#3Bg)\n",
    "    3. [Export to csv](#3C)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Purpose of this notebook: <a name=\"1\"></a>\n",
    "\n",
    "The purpose of this notebook is to clean the data (following the steps identified in the previous notebook (1/4)) and prepare it for analysis (see notebook 4/4).\n",
    "\n",
    "For better readability, the cleaning of the stops' repository and geographical coordinates can be found in a separate notebook (see notebook 3/4).\n",
    "\n",
    "For more information on this data, please refer to notebook 1/4."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Cleaning: Daily traffic <a name=\"2\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.A. Description & steps <a name=\"2A\"></a>\n",
    "\n",
    "\n",
    "\n",
    "nb_2018s1 and nb_2018s2 provide the number of checkins per day, per station (LDA) and per rate plan, for the 1st and 2nd semesters of 2018 respectively.\n",
    "\n",
    "As a reminder, here are the steps we idenfied:\n",
    "\n",
    "> Rename columns (translate to english, except columns that contain IDs)\n",
    "\n",
    "> Handle misdesignated missing values ('ND', '?')\n",
    "\n",
    "> Cast the CODE_STIF_RES, CODE_STIF_ARRET and NB_VALD columns of nb_2018s1 to numeric\n",
    "\n",
    "> Cast the ID_REFA_LDA column of nb_2018s2 to numeric\n",
    "\n",
    "> Convert the JOUR column to datetime in both nb_2018s1 and nb_2018s2\n",
    "\n",
    "> Concatenate nb_2018s1 & nb_2018s2 into a new dataframe\n",
    "\n",
    "> Filter Paris metro stations\n",
    "\n",
    "> Handle remaining missing values\n",
    "\n",
    "> Export to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read files with the number of checkins per day\n",
    "\n",
    "nb_2018s1 = pd.read_csv(\"../../datasets/validations-sur-le-reseau-ferre-nombre-de-validations-par-jour-1er-sem.csv\", sep=\";\")\n",
    "\n",
    "nb_2018s2 = pd.read_csv(\"../../datasets/validations-sur-le-reseau-ferre-nombre-de-validations-par-jour-2e-sem.csv\", sep = \";\")\n",
    "\n",
    "# Read repository file.\n",
    "ref_stops = pd.read_csv('../../datasets/referentiel-arret-tc-idf.csv', sep=';')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.B. Cleaning <a name=\"2B\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.a. Rename columns <a name=\"2Ba\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ True,  True,  True,  True,  True,  True,  True,  True])"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make sure that column names are the same\n",
    "\n",
    "nb_2018s1.columns == nb_2018s2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename columns (translate to english)\n",
    "\n",
    "cols = {'JOUR':'DAY', 'LIBELLE_ARRET':'STOP_NAME', 'CATEGORIE_TITRE':'TICKET_TYPE', 'NB_VALD':'NB_CHECKINS'}\n",
    "\n",
    "nb_2018s1.rename(cols, axis=1, inplace=True)\n",
    "nb_2018s2.rename(cols, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DAY', 'CODE_STIF_TRNS', 'CODE_STIF_RES', 'CODE_STIF_ARRET',\n",
       "       'STOP_NAME', 'ID_REFA_LDA', 'TICKET_TYPE', 'NB_CHECKINS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DAY', 'CODE_STIF_TRNS', 'CODE_STIF_RES', 'CODE_STIF_ARRET',\n",
       "       'STOP_NAME', 'ID_REFA_LDA', 'TICKET_TYPE', 'NB_CHECKINS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s2.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.b. Type conversion <a name=\"2Bb\"></a>\n",
    "\n",
    "#### Cast the CODE_STIF_RES, CODE_STIF_ARRET and NB_VALD columns of nb_2018s1 to numeric & convert the JOUR column to datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ND'], dtype=object)"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify non numeric values in the following columns: 'CODE_STIF_RES', 'CODE_STIF_ARRET', 'NB_CHECKINS'\n",
    "\n",
    "nb_2018s1.loc[~nb_2018s1['CODE_STIF_RES'].astype(str).str.isnumeric(),'CODE_STIF_RES'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ND'], dtype=object)"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1.loc[~nb_2018s1['CODE_STIF_ARRET'].astype(str).str.isnumeric(),'CODE_STIF_ARRET'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Moins de 5'], dtype=object)"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1.loc[~nb_2018s1['NB_CHECKINS'].astype(str).str.isnumeric(),'NB_CHECKINS'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the CODE_STIF_RES & CODE_STIF_ARRET columns, the non-numeric values are 'ND' which standes for unavailable (or \"Non Disponible\" in French). They can be replaced by np.nan while casting the column to numeric by using the errors = 'coerce' keyword argument in the pd.to_numeric() function.\n",
    "\n",
    "The non-numeric values in the NB_CHECKINS columns are not missing values but indicate values under 5. Let's replace these by the numeric value 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Casting CODE_STIF_RES & CODE_STIF_ARRET and replacing non-numeric values by np.nan\n",
    "\n",
    "nb_2018s1['CODE_STIF_RES'] = pd.to_numeric(nb_2018s1['CODE_STIF_RES'], errors = 'coerce')\n",
    "nb_2018s1['CODE_STIF_ARRET'] = pd.to_numeric(nb_2018s1['CODE_STIF_ARRET'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace non-numeric values in NB_CHECKINS by 4 and cast to numeric:\n",
    "\n",
    "nb_2018s1['NB_CHECKINS'] = nb_2018s1['NB_CHECKINS'].replace('Moins de 5', '4')\n",
    "nb_2018s1['NB_CHECKINS'] = pd.to_numeric(nb_2018s1['NB_CHECKINS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change the DAY column to datetime\n",
    "\n",
    "nb_2018s1[\"DAY\"] = pd.to_datetime(nb_2018s1[\"DAY\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 777436 entries, 0 to 777435\n",
      "Data columns (total 8 columns):\n",
      "DAY                777436 non-null datetime64[ns]\n",
      "CODE_STIF_TRNS     777436 non-null int64\n",
      "CODE_STIF_RES      776001 non-null float64\n",
      "CODE_STIF_ARRET    776001 non-null float64\n",
      "STOP_NAME          777436 non-null object\n",
      "ID_REFA_LDA        767645 non-null float64\n",
      "TICKET_TYPE        777436 non-null object\n",
      "NB_CHECKINS        777436 non-null int64\n",
      "dtypes: datetime64[ns](1), float64(3), int64(2), object(2)\n",
      "memory usage: 47.5+ MB\n"
     ]
    }
   ],
   "source": [
    "# Check nb_2018s1 data types\n",
    "\n",
    "nb_2018s1.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cast the ID_REFA_LDA column of nb_2018s2 to numeric & convert the JOUR column to datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['?', nan], dtype=object)"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify non numeric values in ID_REFA_LDA\n",
    "\n",
    "nb_2018s2.loc[~nb_2018s2['ID_REFA_LDA'].astype(str).str.isnumeric(),'ID_REFA_LDA'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cast the ID_REFA_LDA column of nb_2018s2 to numeric\n",
    "\n",
    "nb_2018s2['ID_REFA_LDA'] = pd.to_numeric(nb_2018s2['ID_REFA_LDA'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change the DAY column to datetime\n",
    "\n",
    "nb_2018s2[\"DAY\"] = pd.to_datetime(nb_2018s2[\"DAY\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 883145 entries, 0 to 883144\n",
      "Data columns (total 8 columns):\n",
      "DAY                883145 non-null datetime64[ns]\n",
      "CODE_STIF_TRNS     883145 non-null int64\n",
      "CODE_STIF_RES      881677 non-null float64\n",
      "CODE_STIF_ARRET    881677 non-null float64\n",
      "STOP_NAME          883145 non-null object\n",
      "ID_REFA_LDA        871326 non-null float64\n",
      "TICKET_TYPE        883145 non-null object\n",
      "NB_CHECKINS        765356 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(4), int64(1), object(2)\n",
      "memory usage: 53.9+ MB\n"
     ]
    }
   ],
   "source": [
    "nb_2018s2.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.c. Join dataframes <a name=\"2Bc\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 15)"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make sure that no day appears twice in the s1 and s2 dataframes by merging them with an inner join on the DAY column\n",
    "\n",
    "compare = pd.merge(left = nb_2018s1, right = nb_2018s2, how = 'inner', on = 'DAY')\n",
    "\n",
    "compare.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The resulting dataframe has 0 lines, meaning that there are no common values in the DAY columns of the s1 and s2 dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concatenate s1 and s2 into a new dataframe\n",
    "\n",
    "nb_checkins = pd.concat([nb_2018s1, nb_2018s2], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DAY</th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>STOP_NAME</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>TICKET_TYPE</th>\n",
       "      <th>NB_CHECKINS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>1006.0</td>\n",
       "      <td>OLYMPIADES</td>\n",
       "      <td>71557.0</td>\n",
       "      <td>AMETHYSTE</td>\n",
       "      <td>462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>1006.0</td>\n",
       "      <td>OLYMPIADES</td>\n",
       "      <td>71557.0</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>10764.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>1007.0</td>\n",
       "      <td>LES AGNETTES-ASNIERES-GENNEVILLIERS</td>\n",
       "      <td>72240.0</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>3972.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>1008.0</td>\n",
       "      <td>LES COURTILLES</td>\n",
       "      <td>72286.0</td>\n",
       "      <td>TST</td>\n",
       "      <td>813.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>BOURSE</td>\n",
       "      <td>73635.0</td>\n",
       "      <td>?</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DAY  CODE_STIF_TRNS  CODE_STIF_RES  CODE_STIF_ARRET  \\\n",
       "0 2018-05-09             100          110.0           1006.0   \n",
       "1 2018-05-09             100          110.0           1006.0   \n",
       "2 2018-05-09             100          110.0           1007.0   \n",
       "3 2018-05-09             100          110.0           1008.0   \n",
       "4 2018-05-09             100          110.0            104.0   \n",
       "\n",
       "                             STOP_NAME  ID_REFA_LDA TICKET_TYPE  NB_CHECKINS  \n",
       "0                           OLYMPIADES      71557.0   AMETHYSTE        462.0  \n",
       "1                           OLYMPIADES      71557.0      NAVIGO      10764.0  \n",
       "2  LES AGNETTES-ASNIERES-GENNEVILLIERS      72240.0      NAVIGO       3972.0  \n",
       "3                       LES COURTILLES      72286.0         TST        813.0  \n",
       "4                               BOURSE      73635.0           ?          4.0  "
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_checkins.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.d. Prepare to filter <a name=\"2Bd\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1660581 entries, 0 to 883144\n",
      "Data columns (total 8 columns):\n",
      "DAY                1660581 non-null datetime64[ns]\n",
      "CODE_STIF_TRNS     1660581 non-null int64\n",
      "CODE_STIF_RES      1657678 non-null float64\n",
      "CODE_STIF_ARRET    1657678 non-null float64\n",
      "STOP_NAME          1660581 non-null object\n",
      "ID_REFA_LDA        1638971 non-null float64\n",
      "TICKET_TYPE        1660581 non-null object\n",
      "NB_CHECKINS        1542792 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(4), int64(1), object(2)\n",
      "memory usage: 114.0+ MB\n"
     ]
    }
   ],
   "source": [
    "nb_checkins.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that the following columns have missing values:\n",
    "- CODE_STIF_RES\n",
    "- CODE_STIF_ARRET\n",
    "- ID_REFA_LDA \n",
    "- NB_CHECKINS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DAY                  365\n",
       "CODE_STIF_TRNS         3\n",
       "CODE_STIF_RES         13\n",
       "CODE_STIF_ARRET      753\n",
       "STOP_NAME            732\n",
       "ID_REFA_LDA          721\n",
       "TICKET_TYPE            8\n",
       "NB_CHECKINS        22771\n",
       "dtype: int64"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_checkins.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There also might be columns where missing values are indicated by strings and therefore are not easily identifiable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of the missing values will probably be dropped as we will filter nb_checkins on the CODE_STIF_RES column.\n",
    "\n",
    "Before handling missing values on all these columns, let's look at the missing CODE_STIF_RES. Then we can filter our data and look at the remaining missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([110., 804., 805., 853., 852.,  nan, 800., 803., 854., 802., 801.,\n",
       "       850., 822., 851.])"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_checkins['CODE_STIF_RES'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2903"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_checkins['CODE_STIF_RES'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Inconnu'], dtype=object)"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_checkins.loc[nb_checkins['CODE_STIF_RES'].isnull(), 'STOP_NAME'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that the only rows that have a missing CODE_STIF_RES are those with an 'Inconnu' (='Unknown') STOP_NAME.\n",
    "\n",
    "Let's make sure that 'Inconnu' is not a valid metro station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standardize capitalisation in the STOP_NAME column\n",
    "\n",
    "nb_checkins['STOP_NAME'] = nb_checkins['STOP_NAME'].str.upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "stop_unknown = nb_checkins.loc[nb_checkins['STOP_NAME'] == 'INCONNU']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DAY</th>\n",
       "      <th>TICKET_TYPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2903</td>\n",
       "      <td>2903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>365</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>2018-03-03 00:00:00</td>\n",
       "      <td>AMETHYSTE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>8</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first</th>\n",
       "      <td>2018-01-01 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>last</th>\n",
       "      <td>2018-12-31 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        DAY TICKET_TYPE\n",
       "count                  2903        2903\n",
       "unique                  365           8\n",
       "top     2018-03-03 00:00:00   AMETHYSTE\n",
       "freq                      8         365\n",
       "first   2018-01-01 00:00:00         NaN\n",
       "last    2018-12-31 00:00:00         NaN"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stop_unknown[['DAY', 'TICKET_TYPE']].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the data is evenly distributed among all 365 days of the year and all 7 rate plans."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check in the stops repository dataset if INCONNU appears\n",
    "\n",
    "ref_stops['LDA_NOM'] = ref_stops['LDA_NOM'].str.upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_name(x):\n",
    "    if (ref_stops['LDA_NOM'] == x).sum() == 0:\n",
    "        x_list = x.split()\n",
    "        matches = []\n",
    "        if len(x_list) > 1:\n",
    "            m_lst = ref_stops.loc[ref_stops['LDA_NOM'].str.contains(x_list[0]), 'LDA_NOM'].unique()\n",
    "            matches.append(m_lst)\n",
    "        else:\n",
    "            matches = ref_stops.loc[ref_stops['LDA_NOM'].str.contains(x_list[0]), 'LDA_NOM'].unique()\n",
    "    else:\n",
    "        matches = ref_stops.loc[ref_stops['LDA_NOM'] == x, 'LDA_NOM'].unique()\n",
    "    return matches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_name('INCONNU')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "'INCONNU' is not a valid STOP_NAME and indicates a missing value. These rows will be dropped as we filter on CODE_STIF_RES."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.e. Filter Paris metro stations <a name=\"2Be\"></a>\n",
    "\n",
    "To filter Paris metro stations only, we can use the CODE_STIF_RES 110."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "110.0    744502\n",
       "803.0    140856\n",
       "854.0    132786\n",
       "801.0    112889\n",
       "802.0    107446\n",
       "804.0     99488\n",
       "853.0     91762\n",
       "852.0     66395\n",
       "850.0     54753\n",
       "805.0     46934\n",
       "851.0     32367\n",
       "800.0     14302\n",
       "822.0     13198\n",
       "Name: CODE_STIF_RES, dtype: int64"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_checkins['CODE_STIF_RES'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DAY</th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>STOP_NAME</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>TICKET_TYPE</th>\n",
       "      <th>NB_CHECKINS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>800</td>\n",
       "      <td>804.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>IMAGINE R</td>\n",
       "      <td>332.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>800</td>\n",
       "      <td>804.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>AMETHYSTE</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1342</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>810</td>\n",
       "      <td>801.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>TST</td>\n",
       "      <td>800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27994</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>FGT</td>\n",
       "      <td>203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27995</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>TST</td>\n",
       "      <td>172.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29920</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>810</td>\n",
       "      <td>801.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>AMETHYSTE</td>\n",
       "      <td>366.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29921</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>810</td>\n",
       "      <td>801.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>17061.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29923</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>810</td>\n",
       "      <td>801.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>?</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30771</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>IMAGINE R</td>\n",
       "      <td>918.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30772</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>AMETHYSTE</td>\n",
       "      <td>181.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30777</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>?</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31790</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>810</td>\n",
       "      <td>801.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>AUTRE TITRE</td>\n",
       "      <td>1181.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44377</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>800</td>\n",
       "      <td>804.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>AUTRE TITRE</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44381</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>800</td>\n",
       "      <td>804.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>FGT</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44620</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>5004.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130120</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>810</td>\n",
       "      <td>801.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>IMAGINE R</td>\n",
       "      <td>3611.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130123</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>810</td>\n",
       "      <td>801.0</td>\n",
       "      <td>306.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>FGT</td>\n",
       "      <td>861.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142203</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>800</td>\n",
       "      <td>804.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>?</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679042</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>317.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>AUTRE TITRE</td>\n",
       "      <td>163.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680592</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>800</td>\n",
       "      <td>804.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>697.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680593</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>800</td>\n",
       "      <td>804.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>GARE DE LYON</td>\n",
       "      <td>73626.0</td>\n",
       "      <td>TST</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              DAY  CODE_STIF_TRNS  CODE_STIF_RES  CODE_STIF_ARRET  \\\n",
       "161    2018-01-01             800          804.0            307.0   \n",
       "162    2018-01-01             800          804.0            307.0   \n",
       "1342   2018-01-01             810          801.0            306.0   \n",
       "27994  2018-01-01             100          110.0            317.0   \n",
       "27995  2018-01-01             100          110.0            317.0   \n",
       "29920  2018-01-01             810          801.0            306.0   \n",
       "29921  2018-01-01             810          801.0            306.0   \n",
       "29923  2018-01-01             810          801.0            306.0   \n",
       "30771  2018-01-01             100          110.0            317.0   \n",
       "30772  2018-01-01             100          110.0            317.0   \n",
       "30777  2018-01-01             100          110.0            317.0   \n",
       "31790  2018-01-01             810          801.0            306.0   \n",
       "44377  2018-01-01             800          804.0            307.0   \n",
       "44381  2018-01-01             800          804.0            307.0   \n",
       "44620  2018-01-01             100          110.0            317.0   \n",
       "130120 2018-01-01             810          801.0            306.0   \n",
       "130123 2018-01-01             810          801.0            306.0   \n",
       "142203 2018-01-01             800          804.0            307.0   \n",
       "679042 2018-01-01             100          110.0            317.0   \n",
       "680592 2018-01-01             800          804.0            307.0   \n",
       "680593 2018-01-01             800          804.0            307.0   \n",
       "\n",
       "           STOP_NAME  ID_REFA_LDA  TICKET_TYPE  NB_CHECKINS  \n",
       "161     GARE DE LYON      73626.0    IMAGINE R        332.0  \n",
       "162     GARE DE LYON      73626.0    AMETHYSTE         37.0  \n",
       "1342    GARE DE LYON      73626.0          TST        800.0  \n",
       "27994   GARE DE LYON      73626.0          FGT        203.0  \n",
       "27995   GARE DE LYON      73626.0          TST        172.0  \n",
       "29920   GARE DE LYON      73626.0    AMETHYSTE        366.0  \n",
       "29921   GARE DE LYON      73626.0       NAVIGO      17061.0  \n",
       "29923   GARE DE LYON      73626.0            ?          5.0  \n",
       "30771   GARE DE LYON      73626.0    IMAGINE R        918.0  \n",
       "30772   GARE DE LYON      73626.0    AMETHYSTE        181.0  \n",
       "30777   GARE DE LYON      73626.0            ?          4.0  \n",
       "31790   GARE DE LYON      73626.0  AUTRE TITRE       1181.0  \n",
       "44377   GARE DE LYON      73626.0  AUTRE TITRE          9.0  \n",
       "44381   GARE DE LYON      73626.0          FGT         88.0  \n",
       "44620   GARE DE LYON      73626.0       NAVIGO       5004.0  \n",
       "130120  GARE DE LYON      73626.0    IMAGINE R       3611.0  \n",
       "130123  GARE DE LYON      73626.0          FGT        861.0  \n",
       "142203  GARE DE LYON      73626.0            ?          4.0  \n",
       "679042  GARE DE LYON      73626.0  AUTRE TITRE        163.0  \n",
       "680592  GARE DE LYON      73626.0       NAVIGO        697.0  \n",
       "680593  GARE DE LYON      73626.0          TST         75.0  "
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's look at values for 1 random day for GARE DE LYON, which we know has multiple stop types (metro, train, etc.)\n",
    "\n",
    "nb_checkins.loc[(nb_checkins['DAY'] == '2018-01-01')&(nb_checkins['STOP_NAME'].str.contains('GARE DE LYON'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at the dataframe above, we see that for 1 LDA, there are multiple CODE_STIF_ARRET. The CODE_STIF_ARRET is different for each network category within a station. The different network categories are designated by the CODE_STIF_RES, which uses the number 110 for metro network."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter by metro station\n",
    "\n",
    "metro_nb = nb_checkins.loc[nb_checkins['CODE_STIF_RES']==110]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(744502, 8)"
      ]
     },
     "execution_count": 190,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Shape of the filtered dataframe\n",
    "\n",
    "metro_nb.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "301"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_nb['STOP_NAME'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This number is consistent with the number indicated in the Wikipedia page for Paris metro: 302 (https://en.wikipedia.org/wiki/Paris_M%C3%A9tro)\n",
    "\n",
    "A station might be missing from metro_nb. We will have to check it with the repository of metro stations and the geographical data of metro stations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "971230981.0"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_nb['NB_CHECKINS'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.f. Missing values <a name=\"2Bf\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 744502 entries, 0 to 883141\n",
      "Data columns (total 8 columns):\n",
      "DAY                744502 non-null datetime64[ns]\n",
      "CODE_STIF_TRNS     744502 non-null int64\n",
      "CODE_STIF_RES      744502 non-null float64\n",
      "CODE_STIF_ARRET    744502 non-null float64\n",
      "STOP_NAME          744502 non-null object\n",
      "ID_REFA_LDA        742269 non-null float64\n",
      "TICKET_TYPE        744502 non-null object\n",
      "NB_CHECKINS        710580 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(4), int64(1), object(2)\n",
      "memory usage: 51.1+ MB\n"
     ]
    }
   ],
   "source": [
    "metro_nb.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DAY                  365\n",
       "CODE_STIF_TRNS         1\n",
       "CODE_STIF_RES          1\n",
       "CODE_STIF_ARRET      301\n",
       "STOP_NAME            301\n",
       "ID_REFA_LDA          300\n",
       "TICKET_TYPE            8\n",
       "NB_CHECKINS        19339\n",
       "dtype: int64"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_nb.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that the TICKET_TYPE column has 8 unique values instead of 7. We saw before that some missing values were designated by a question mark in the column.\n",
    "\n",
    "There are missing ID_REFA_LDA. This column has 300 unique values when it should have 301 (as CODE_STIF_ARRET and STOP_NAME). 1 ID_REFA_LDA is probably missing for a bunch of rows.\n",
    "\n",
    "Finally, NB_CHECKINS has missing values. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### TICKET_TYPE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AUTRE TITRE    107995\n",
       "NAVIGO         107959\n",
       "IMAGINE R      107906\n",
       "FGT            107893\n",
       "TST            107886\n",
       "AMETHYSTE      107882\n",
       "?               88752\n",
       "NON DEFINI       8229\n",
       "Name: TICKET_TYPE, dtype: int64"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_nb['TICKET_TYPE'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that there are 2 incorrectly designated missing values in the TICKET_TYPE column: '?' and 'NON DEFINI' (undefined). These indicate anomalies at the time of checkin.\n",
    "\n",
    "'AUTRE TITRE' (other type of fare) could suggest that the rate plan is unknown, but it does in fact designate a group of other special rate plans that probably are not common enough each on its own to appear in separate groups.\n",
    "\n",
    "Fortunately, '?' and 'NON DEFINI' are not as frequent as the 6 valid plan rates.\n",
    "\n",
    "The fact that each rate plan does not have the same number of rows means that when no checkins are registered for a specific day, stop and rate plan, the row is missing from the data.\n",
    "\n",
    "Let's see if dropping the lines for which the TICKET_TYPE column is '?' or 'NON DEFINI' would cause information loss."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rows where the TICKET_TYPE is missing\n",
    "\n",
    "rate_missing = metro_nb[metro_nb['TICKET_TYPE'].isin(['?','NON DEFINI'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DAY</th>\n",
       "      <th>STOP_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>96981</td>\n",
       "      <td>96981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>365</td>\n",
       "      <td>301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>2018-10-13 00:00:00</td>\n",
       "      <td>PALAIS ROYAL-MUSEE DU LOUVRE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>398</td>\n",
       "      <td>459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first</th>\n",
       "      <td>2018-01-01 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>last</th>\n",
       "      <td>2018-12-31 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        DAY                     STOP_NAME\n",
       "count                 96981                         96981\n",
       "unique                  365                           301\n",
       "top     2018-10-13 00:00:00  PALAIS ROYAL-MUSEE DU LOUVRE\n",
       "freq                    398                           459\n",
       "first   2018-01-01 00:00:00                           NaN\n",
       "last    2018-12-31 00:00:00                           NaN"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_missing[['DAY','STOP_NAME']].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing values are distributed among all the values in the DAY column and most of the values in the STOP_NAME column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.05104511796869874"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_missing_perc = (rate_missing['NB_CHECKINS'].sum()/metro_nb['NB_CHECKINS'].sum())*100\n",
    "rate_missing_perc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing rate values account for more than 0.05% of all checkins.\n",
    "\n",
    "Let's make sure that the number of checkins when the rate plan is missing is proportionate to the total number of checkins for stops."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Correlation between missing ticket type and number of checkins per stop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group metro_nb by 'STOP_NAME' and sum() 'NB_CHECKINS'. Sort in ascending order by 'NB_CHECKINS'\n",
    "\n",
    "gp_stop = metro_nb.groupby('STOP_NAME')\n",
    "sum_stop = gp_stop.sum().sort_values('NB_CHECKINS', ascending=True)\n",
    "sum_stop = sum_stop.reset_index()\n",
    "sum_stop = sum_stop[['STOP_NAME', 'NB_CHECKINS']]\n",
    "\n",
    "# Group rate_missing by 'STOP_NAME' and sum() 'NB_CHECKINS'\n",
    "\n",
    "gp_stop_missing = rate_missing.groupby('STOP_NAME')\n",
    "sum_stop_missing = gp_stop_missing.sum()\n",
    "sum_stop_missing.reset_index(inplace=True)\n",
    "sum_stop_missing = sum_stop_missing[['STOP_NAME', 'NB_CHECKINS']]\n",
    "\n",
    "# Merge both dataframes. The resulting dataframe is sorted by 'NB_CHECKINS'\n",
    "\n",
    "join_stop = pd.merge(left=sum_stop, right=sum_stop_missing, on='STOP_NAME', how='left', suffixes=('_all','_missing_rate'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAckAAAD7CAYAAAAB+m9jAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjAsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+17YcXAAAgAElEQVR4nO3de5wddX3/8dcnywKLIhsgpWZDTBSMhaIEVy5Ffyoq4aJNCiooQrRUWu+KjSZI5aYlmipqVSwVaqgUiBBDbKABDV5KBdkYIAaIRm7JEiSQLCBZYRM+vz++37OZPZk5tz33fT8fjzyy53vmzHxnzpz5zPc65u6IiIjIzsY1OgMiIiLNSkFSREQkg4KkiIhIBgVJERGRDAqSIiIiGRQkRUREMihItiAzm2xmfzSzjlGs449m9vJq5qvRzOwhM3tro/Mho1fP79LMzjez72e89yYz21CPfEjtmdkUM3Mz2yW+/qmZ/V2hzxQNkvFkfdzMXpRI+zsz+2nitZvZs/HC+4SZXW1m3SVmeoaZ/dzMnjGzTWb2MzP76/je+83sfzPy9NbEMtvjtpP/JiaWf6+Z9cX0jWZ2k5m9Pr434gdiZj1mdr+ZfcOC4YMYfzBuZt/Oy8//mtn749+7mtlXzGxD3N5DZva1Uo5Fqdz9EXd/sbtvH8U6XuzuD1QzX6PVTEHOzM4xswfjd7jBzK6N6WsS59h2M/tT4vU5cZlJZnaVmT0Zfxe/MrO3560/+ZvpN7OvFrvpKeW3GNPMzB4ws3tT1vHTuO3X5KX/MKa/Kb4+38yG8n5TA+UdRUkys+/FY3x4Iu0AM/PE658mzqmn4rXxkBLX/0oz+0G8Bj9lZveY2dlm1mF5wSEvT1+If+eWyb+WnpJY/nAzu9HMBsxsczy3PxDfG3FDEa+Fi83sNjN7Scq11s1stZmNS6R9wcy+l3h9ZrweP2Nmf4jb3rPEQ14VpZYkO4BPFFnmNe7+YuDlwHjg/GIrNbN3Aj8ArgQmAfsBnwfeUWK+cn4ZL/rJf4/GbZwNfA3457j+ycC3gZkp+XkZ8HNgqbt/3NNnWngWON3MpmTkZR7QCxwO7Am8Cfh1mfvTdvJ/nM3MzGYDpwNvjed0L/ATAHc/OHeOAb8APpo45/7ZzPYG/hd4HjgY2Be4BPiveL4n5X4zbwHeC3ywhOyV8lv8f8CfAS83s9elvP9b4IzE/u4DHAVsylvu2rzfVEk3vuWo9nnRAufZZuALRZb5aDwv9gZ+CvxnsZWa2SuAO4D1wCHuvhfwLsK5W25Q6c773nM3iEcBK4CfAQcA+wAfAo5Pyc9uwGKgGzjW3Z/O2NZE4NSMfXoj4br9HnffE/gL4Noy92XUSg2SC4B/tBJKh/FgLAUOKrScmRnwVeAid/+uuz/l7i+4+8/cvZSLRVFmthdwIfARd1/s7s+6+5C7/8jd5+Qt+wpCgLzK3T9TYLUDwPeA8zLefx3wQ3d/1IOH3P3KEvL6kJnNiXd/z5rZ5Wa2n4VS7zNm9mMzGx+Xza8yeH8sOTxjofRzWkw/wELJ/Kl4d3ltYntuZgfEv79nZt8ys2VxHXfE45Fb9lgzWxvX8+24zoJVFDFPt5nZJWb2JHC+mb3CzFZYKGE9YaG01R2X/0/CDcyP4t3rZ2L6kWb2f/HO9W6LJZ0CXmdm95rZFjP7DzPbPa7nN2Y2fPNlZp0xD9PT1gEsd/ffA7j7Y+5+WZHt5nwK+CNwZvzcoLtfDXwR+Eo870dw9/sJAfcvS1h/Kb/F2cANwI3x73xXAafYjpLre4AfEgL7qJnZODM718wetlDyvTL+FpPn7plm9gjhoouZnR6Xf9LMPpeyvrlm9vv4/qJ4M1JofZnnjZlNjefwM2Z2C+FGptg+nRPPl4cSv6/XWSjddCSWO8nM7i6wqoXAq2MAKCjWFF1DkWtpdAHwf+5+trtvjJ9f6+7vdfdq1QAsABa6+5fc/Yl4fVvp7u9OLmRmewA/AnYBTnT3Zwus88vABZZ+c/M6QgFoFYC7b3b3he7+TKFMmtmJZrbKzJ42s/Vmdn4Z+7iTUoNkH+GO5h+LLRgv5LOA24ssOg3YH7iuxDxU4ihgd8IFoJCXEwLkv7n750tY7xeBk81sWsp7twNnm9mHzeyQtItiAScDbwNeSShN3wScA0wgfFcfz/+Ahaq3bwDHx7utvwLuim9fBNxMKNlPAv61wLZPJfzQxgPr4j5iZvsSvqN5hDvHtXEbpTgCeIBQgv8iYMDFhLvHvyB8/+cDuPvpwCPAO+Ld65fNrAdYRrjz3ptw/l1vZhMKbPM0YAbwCsJxPDemXwm8L7HcCcDG3A8wz+3AGRZuWnqtvLbftwHXu/sLeemLCDcBr8z/gJkdBLwBSMtLvoK/xXiBeichEF4FnGpmu+Yt9ihwL3BsfH0G4fhUy/vjvzcTflsvBr6Zt8wbCefAjLj/lxJK7xMJ59mkxLIfI1xT3hjf3wJ8q8D6ip03/wWsJATHi0i/kUj687hsT1z2MjOb5u53Ak+y4zgS96HQsdxKKB19scg2id/baRS/lgK8lRpeS+N5dVQJ29iNcN36EzDT3QeLLL8YeJpwvuS7g/B9XmBmR1sonZbiWcI53Q2cCHzIzGaV+NmdlNNx5/PAxwpcoH5toc3iCcLF4N+KrG+f+P/GIssdGe8Gh//F9Rda5veJbTzh7tuKbOMvgRdRYlHe3R8DvkMopea7GPgS4eTuA/otVN+V4l/d/Q/u3k8oWdzh7qvc/U+EQJ9W6gF4AfhLM+ty943uviamDwEvAya6+5/cfaf23YQfuvuv4rG6Cjg0pp8ArIkl8W2EgPxYifvzqLv/q7tviyWqde5+i7s/5+6bCDUJhe6o3wfc6O43xlqGWwjH9IQCn/mmu693982EC9F7Yvr3gRPM7CXx9elkVGO5+/cJF+YZhKqlx83ssyXu876kn9MbE+/n/NrMthDuur8L/EeJ2yj0WzwJeI5wc7QM6CRcKPJdSbgReBWheu2XKcu8O+93dWuJ+TsN+Kq7P+DufyTcYJ2aV1o4P9bsDBKC+n+7+8/d/TngnwjndM4/AJ9z9w3x/fOBdxZYX+Z5Y2aTCSWUf4rn4c8Jx7+Y3PI/IxzXXOlpYdwesXQ7gxCEC/k3YLKZ7VRNGX0jXueeAT5KuHktZh+KX0sBnsi7lr632DJm9heEm+dxJWxjT0IwXRi/q2Kc8H3/U/7NnLv/gnA+H0Y45k9aCW337v5Td18dv/t7gKspfJ0pqOQg6e6/Af4bmJuxyGGxzWJ3wl3hLyxWdWV4Mv7/0iKbvt3du5P/CCWOQsvkqgqfBPbNKMonLQWuAFZYaJcsxZcIdzkjOkC4+3Z3/5a7H024k/kicEU80Yr5Q+LvwZTXL87/QKzKOIVwIdloocr0VfHtzxBKb7+y0OHkbwtsOxn4tia2NZHQzpHbngOl9vZbn3xhofr4GgsdVZ4mBK5CVV0vA96V96N+PYXPmeQ2H475x0Mb9W2EGoBuQjvKVVkrcfer3P2thO/wH4CLzGxGge3mPJGRv5cm3s85zN3Hu/sr3P3clNJnVt4K/RZnA4vijcmfgOtJLyktBo4hXISz2rwW5f2u3lxK/gjH/OHE64cJVW/7JdLW5y2fPMeeZcf1AcJ58MPEOXAfsL3A+gqdNxOBLXlVgMm8pklbPtcx8PvAO2KNzruBX+SqO7PE4HFR/Jfm4/E61wW8HbjOzF5dJI9PUvxaCrBv3rU0LaDvm/e930covb9QwjaeINRKLSzx94K730i4pvx9yns3ufs7CDUCMwklzmJNPUeY2a0WOoI+Rfj9Fq1Sz1LuEJDzCJ0LerIWcPchwl3xVAq3sawlnNgnl5mHcvyScFddtKjt7mcTLjwrYnVNseWfJHQIyjrRiaWnbxFOsFLaFSri7svd/W2EE/h+4N9j+mPu/kF3n0g4Ab9tsR2yDBtJVH3F6uNJ2YuPzFre63+OaYe4+0sId+BWYPn1wH/m/WBf5O7zC2xz/8TfkwlVizm5u/53Edo6+ovuQGjD/gFwD6W1Gf4YOMkSPfaid8f9+W0J6yjFTr9FM5tECHzvM7PHzOwxQinthFhtPszdtxKqxT5ECR1DyvQoIVDlTAa2MfKmL/ldbyTxvcWqvX0S768nNCckz4Pd874/z1s+67zZCIy3RA9hdq6Zype2/KMAMQ+/JJR4MmsnUvwH4QbspKwFYknoF4Tmj2Ozlot+TA2vpfF8+WUp23D3xYRz8zozK/XG6nOEpqU9Mtb5grv/hNDmXOx3+F+Egs/+HjowfYeR15mylBUk3X0doUpyp7axnFgU/gCh5JM5xCCWSM4mFLM/YKGL8Dgze72ZldpJolh+nyJUTX3LzGaZ2R4WOmwcb2ZfTvnIR4FbgZ+Y2X4p7+f7KqF9briUaGaftNAVusvMdolVrXtSWntT2WLpbGb8ET9H6DTyQnzvXfHCCSFQOyOrsUqxDDgkHr9dgI8Q2mgqsWfM31PxRmRO3vt/ILRh5eTu0mdY6Ma+ezy2hYL0RywMwdib8MNLVqEvIVTdfIIC7UYWOh2daGZ7xnPyeEJP1TtK2MdLgL2Ay83sz2Oe3xPzMiee96OW8Vs8nRCEpxGqyw8ltIFuYEe1c9I5wBvd/aFyt29hqML5GW9fDXzKQgeZFxNujq717GaP64C3x9/+roRmjOS16TvAF3O1PGY2wcx26p2ekHneuPvDhKrXCywMUXg9pfWmzy3/BkLp7geJ964k1NocQiihFxWPxXlAwWp8Cz1KDwLWFFouruuvzGyBmf15/OwBZvZ9K3E4Xgk+A7zfQlv9PnEbrzGza/IX9NBZ7aPADWZ2dLEVu/tPgd+QqPWI17VTzWy8BYcTqk2LtdHuCWx29z/Fz6RVKZeskskELiS03+W728z+SLgYzwb+JrYLZXL36whVhX9LuDP7A6Gx/YYy83SU7Ty253VxG18hBONzCV3c1xO+vCUp+XHgLOBXwI/z775Tln+a0Dtr70TyVuArhOrLJwhB5WSv3ZjEcYT9e5TQvfyNhNIBhLaXO+L3shT4RLn5cPcnCCWvLxOqdA4iXGRKaW/IdwEhSD1FCL75F5SLgXNjFdk/uvt6QhXLOez47uZQ+Lz9L0J73APA70l0t4/tVdcTajkKXcyejtt8hNCb+cvAh7xwm25uG08SqvZ2J3SOeZLw/ZzusSt9FeX/FmcD3441CMP/CEFmpypXDz2wC+3TKSm/qz+L7+1PqL5OcwWhRPVz4EFCJ46PZW3EQxv6Rwjf3UbCNSRZpf91wvl7s5k9Q7hIHlFgfcXOm/fGz28mBJdinZYei3l6lFBF/w8eeiTn/JBYJRxLXKW6mvQ2vm/mjjfhOJ7r7jcVWpGHnthHAVOANbGa8XrCb7Vgb9AUA3nf+dlxG/9HqKk4BnjAzDYDlxF6UaflaSHwaWCZJcaGFnAuI6+lWwgl0t8RfpPfBxa4e2YzSfRh4MJ4rnye0GmuYlalG1sZI2I14gbgNHcvtSNH0zCzzwOvdPf3FV1YUsWS/CJ3L7WXc9uz0Fnw7939x43Oi1SXpqWTomK1VbeFLtjnEOr3S+mW3lRiFeyZhLtfqZCHXqYKkJGZnUxoyljR6LxI9dU8SKZU1+T+vaHW224mtmO+1bR/xToONNpRhKrLJwjtN7PcfdDMvpOxP99pbHZ3ZmYfJFS73eSh23/TafFzZEyyMCXgpYQJS8pt7y93WzdlnBvn1HK7zchGTg+Z/Hda1bel6lYREZF0qm4VERHJoCApIiKSodlnzK+Zfffd16dMmdLobIiItIyVK1c+4e6F5k5uO2M2SE6ZMoW+vr5GZ0NEpGWYWbEp/NqOqltFREQyKEiKiIhkqMc4ySssPHj1N3npHzOz++N4ly8n0ueZ2ToLD/mdkUg/LqatM7O5ifSpFh4SvM7MrrWdn50nIiJSkXqUJL8HHJdMsDAz/EzgNe5+MPAvMf0gwmNWDo6f+baFCYo7CA9ZPZ4wd+h74rIQHll1ibsfQJjr78ya75GIiIwJNQ+ScXaT/InOPwTMj89Vw90fj+kzgWs8PNz0QcIjYg6P/9Z5eIjr88A1wEwzM8Jku7mnZS+khMdiiYiIlKJRbZKvBN4Qq0l/lntiB+HZeMmHp26IaVnp+wADiUfw5NJTmdlZZtZnZn2bNm2q0q6IiDS/Jav6OXr+CqbOXcbR81ewZFXRx6kKjQuSuxAeiXIk4RE2i2KpsKbc/TJ373X33gkTxtRQHxEZw5as6mfe4tX0DwziQP/AIPMWr1agLEGjguQGYLEHvyI8CHhfoJ+RT5afFNOy0p8Eui08DDiZLiIi0YLlaxkc2j4ibXBoOwuWr21QjlpHo4LkEuDNAGb2SmBXwhMmlgKnmtluZjYVOJDwAOQ7gQNjT9ZdCZ17lsaHJN8KvDOudzblP7BZRKStPTowWFa67FCPISBXA78EppnZBjM7k/Dk8pfHYSHXALNjqXIN4SnS9wL/Q3j8zPbY5vhRYDlwH+GBr2viJj4LnG1m6whtlJfXep9ERFrJxO6ustJlhzH7qKze3l7XtHQiMhbk2iSTVa5dnR1cfNIhzJqe2ddxJ2a20t17a5HHZjVm524VERkrcoFwwfK1PDowyMTuLubMmFZWgByrFCRFRMaAWdN7FBQroLlbRUREMihIioiIZFCQFBERyaAgKSIikkFBUkREJIN6t4qItLklq/o1/KNCCpIiIm0sfyKB3OTmgAJlCVTdKiLSxjS5+egoSIqItDFNbj46CpIiIm1Mk5uPjoKkiEgbmzNjGl2dHSPSujo7mDNjWoNy1FrUcUdEpI1pcvPRUZAUEWlzmty8cqpuFRERyVDzIGlmV5jZ42b2m5T3Pm1mbmb7xtdmZt8ws3Vmdo+ZHZZYdraZ/S7+m51If62ZrY6f+YaZWa33SURExoZ6lCS/BxyXn2hm+wPHAo8kko8HDoz/zgIujcvuDZwHHAEcDpxnZuPjZy4FPpj43E7bEhERqUTNg6S7/xzYnPLWJcBnAE+kzQSu9OB2oNvMXgrMAG5x983uvgW4BTguvvcSd7/d3R24EphVy/0REZGxoyFtkmY2E+h397vz3uoB1ideb4hphdI3pKSLiIiMWt17t5rZHsA5hKrWem/7LEI1LpMnT6735kVEpMU0oiT5CmAqcLeZPQRMAn5tZn8O9AP7J5adFNMKpU9KSU/l7pe5e6+7906YMKEKuyIiIu2s7kHS3Ve7+5+5+xR3n0KoIj3M3R8DlgJnxF6uRwJPuftGYDlwrJmNjx12jgWWx/eeNrMjY6/WM4Ab6r1PIiLSnuoxBORq4JfANDPbYGZnFlj8RuABYB3w78CHAdx9M3ARcGf8d2FMIy7z3fiZ3wM31WI/RERk7LHQKXTs6e3t9b6+vkZnQ0SkZZjZSnfvbXQ+6kkz7oiIiGRQkBQREcmgICkiIpJBQVJERCSDgqSIiEgGBUkREZEMeuiyiEgbW7KqnwXL1/LowCATu7uYM2OaHsBcBgVJEZE2tWRVP/MWr2ZwaDsA/QODzFu8GkCBskSqbhURaVMLlq8dDpA5g0PbWbB8bYNy1HoUJEVE2tSjA4NlpcvOFCRFRNrUxO6ustJlZwqSIiJtas6MaXR1doxI6+rsYM6MaQ3KUetRxx0RkTaV65yj3q2VU5AUEWljs6b3KCiOgqpbRUREMihIioiIZKh5kDSzK8zscTP7TSJtgZndb2b3mNkPzaw78d48M1tnZmvNbEYi/biYts7M5ibSp5rZHTH9WjPbtdb7JCIiY0M9SpLfA47LS7sF+Et3fzXwW2AegJkdBJwKHBw/820z6zCzDuBbwPHAQcB74rIAXwIucfcDgC3AmbXdHRERGStqHiTd/efA5ry0m919W3x5OzAp/j0TuMbdn3P3B4F1wOHx3zp3f8DdnweuAWaamQHHANfFzy8EZtV0h0REZMxohjbJvwVuin/3AOsT722IaVnp+wADiYCbSxcRERm1hgZJM/scsA24qk7bO8vM+sysb9OmTfXYpIiItLCGBUkzez/wduA0d/eY3A/sn1hsUkzLSn8S6DazXfLSU7n7Ze7e6+69EyZMqMp+iIhI+2pIkDSz44DPAH/t7lsTby0FTjWz3cxsKnAg8CvgTuDA2JN1V0LnnqUxuN4KvDN+fjZwQ732Q0RE2ls9hoBcDfwSmGZmG8zsTOCbwJ7ALWZ2l5l9B8Dd1wCLgHuB/wE+4u7bY5vjR4HlwH3AorgswGeBs81sHaGN8vJa75OIiIwNtqOmc2zp7e31vr6+RmdDRKRlmNlKd+9tdD7qqRl6t4qIiDQlBUkREZEMCpIiIiIZFCRFREQyKEiKiIhkUJAUERHJoCApIiKSQUFSREQkg4KkiIhIBgVJERGRDAqSIiIiGRQkRUREMihIioiIZFCQFBERyaAgKSIikkFBUkREJEPNg6SZXWFmj5vZbxJpe5vZLWb2u/j/+JhuZvYNM1tnZveY2WGJz8yOy//OzGYn0l9rZqvjZ75hZlbrfRIRkbGhHiXJ7wHH5aXNBX7i7gcCP4mvAY4HDoz/zgIuhRBUgfOAI4DDgfNygTUu88HE5/K3JSIiUpGaB0l3/zmwOS95JrAw/r0QmJVIv9KD24FuM3spMAO4xd03u/sW4BbguPjeS9z9dnd34MrEukREREalUW2S+7n7xvj3Y8B+8e8eYH1iuQ0xrVD6hpR0ERGRUWt4x51YAvR6bMvMzjKzPjPr27RpUz02KSIiLaxRQfIPsaqU+P/jMb0f2D+x3KSYVih9Ukp6Kne/zN173b13woQJo94JERFpb40KkkuBXA/V2cANifQzYi/XI4GnYrXscuBYMxsfO+wcCyyP7z1tZkfGXq1nJNYlIiIyKrvUegNmdjXwJmBfM9tA6KU6H1hkZmcCDwPvjovfCJwArAO2Ah8AcPfNZnYRcGdc7kJ3z3UG+jChB20XcFP8JyIiMmoWmgTHnt7eXu/r62t0NkREWoaZrXT33kbno54a3nFHRESkWSlIioiIZFCQFBERyaAgKSIikkFBUkREJEPNh4CIiLSTJav6WbB8LY8ODDKxu4s5M6Yxa7pmw2xXCpIiIiVasqqfeYtXMzi0HYD+gUHmLV4NoEDZplTdKiJSogXL1w4HyJzBoe0sWL62QTmSWlOQFBEp0aMDg2WlS+tTkBQRKdHE7q6y0qX1KUiKiJToza+agOWldXV2MGfGtIbkJ2nJqn6Onr+CqXOXcfT8FSxZlflAJCmDOu6IiJRgyap+rl/ZP+Lhtwac/NqehnfaUYei2lFJUkSkBGmddhy49f7GP8BdHYpqRyVJEZESNLrTTqHxmY3OWztTSVJEpASN7LSTq07tHxjE2VGdmmt3zMrDODO1UY6SgqSISAnmzJhGV2fHiLR6ddopVp2aljeA7e6pQVVK19AgaWafMrM1ZvYbM7vazHY3s6lmdoeZrTOza81s17jsbvH1uvj+lMR65sX0tWY2o1H7IyLta9b0Hi4+6RB6urswoKe7i4tPOqQuHWOKVafm563D8vvgqo2yUg1rkzSzHuDjwEHuPmhmi4BTgROAS9z9GjP7DnAmcGn8f4u7H2BmpwJfAk4xs4Pi5w4GJgI/NrNXuvv2lM2KiFRs1vTG9GSd2N1Ff0qgTFazJvM2de6y1PWojbJ8ja5u3QXoMrNdgD2AjcAxwHXx/YXArPj3zPia+P5bzMxi+jXu/py7PwisAw6vU/5FRGqu3KrerDbKvbo6q563dtewIOnu/cC/AI8QguNTwEpgwN23xcU2ALnbth5gffzstrj8Psn0lM+IiLS8cqt658yYRue4natcn31+m9oly9TI6tbxhFLgVGAA+AFwXI23eRZwFsDkyZNruSkRkaoqp6p31vQeLvjRGrZsHRqRPrTdWbB8rSYYKEMjq1vfCjzo7pvcfQhYDBwNdMfqV4BJQO62px/YHyC+vxfwZDI95TMjuPtl7t7r7r0TJkyo9v6IiDSNgbwAmaN2yfI0Mkg+AhxpZnvEtsW3APcCtwLvjMvMBm6Ify+Nr4nvr3B3j+mnxt6vU4EDgV/VaR9ERJqSJmOvjka2Sd5B6IDza2B1zMtlwGeBs81sHaHN8fL4kcuBfWL62cDcuJ41wCJCgP0f4CPq2SoiY0XWxOaNHNfZTiwUxsae3t5e7+vra3Q2REQqlj+xOYRAmOvUU2gqu0qY2Up3761G3luF5m4VEWlRhWbiyXX0USed0Wn0OEkREamQJjavPQVJEZEWpc45tacgKSLSotQ5p/bUJiki0qJy7Y3V7JwjIylIioi0oPyeq5eccqiCYw0oSIqItJj8oR+550UCCpRVpjZJEZEWU+whzFI9KkmKiLSYYkM/qj2JwFimkqSISIspNPQjVxXbPzCIs6MqVo/IqoyCpIhIiyk09ENVsdWlICki0mIKPYRZs/BUl9okRURaUNa8rBO7u+hPCYiahacyKkmKiLQRzcJTXSpJioi0Ec3CU10KkiIibUaPyKqehla3mlm3mV1nZveb2X1mdpSZ7W1mt5jZ7+L/4+OyZmbfMLN1ZnaPmR2WWM/suPzvzGx24/ZIRETaSaPbJL8O/I+7vwp4DXAfMBf4ibsfCPwkvgY4Hjgw/jsLuBTAzPYGzgOOAA4HzssFVhFpHUtW9XP0/BVMnbuMo+ev0Lg+aQoNC5Jmthfw/4DLAdz9eXcfAGYCC+NiC4FZ8e+ZwJUe3A50m9lLgRnALe6+2d23ALcAx9VxV0RklDQAXppVI0uSU4FNwH+Y2Soz+66ZvQjYz903xmUeA/aLf/cA6xOf3xDTstJFpEVoALw0q0YGyV2Aw4BL3X068Cw7qlYBcHcHvFobNLOzzKzPzPo2bdpUrdWKyChpALw0q0YGyQ3ABne/I76+jhA0/xCrUYn/Px7f7wf2T3x+UkzLSt+Ju1/m7r3u3jthwoSq7YiIjE6huUhFGqlhQdLdHwPWm1luhOtbgHuBpUCuh+ps4Ib491LgjNjL9UjgqVgtuxw41szGxw47x8Y0EWkRGgAvzarR4yQ/BlxlZrsCDwAfIATuRWZ2JvAw8O647I3ACcA6YGtcFnffbGYXAXfG5S5098312wWRsaNWj2DSAHhpVhaa/cae3sor13kAABS1SURBVN5e7+vra3Q2RFpGrgdqsoNNV2fH8MTa0v7MbKW79zY6H/XU6JKkiLSIQj1QWz1I6iHFkkVBUkRK0q49UPNLyLkxmoACpTR8xh0RaRHt2gNVYzSlEAVJESlJu/ZAbdcSslSHgqSIlGTW9B4uPukQerq7MKCnu6stOu20awlZqkNtkiJtqhadUdrxEUxzZkxL7bXb6iVkqQ4FSZE21GydUZq596jGaEohCpIibaiZhms0W8BOU8sScjVvEHLr6h8YpMOM7e705K2zmW9IWpGCpEgbaqbOKM0UsOutmjcI+evaHieCSa4TaPobklajICnShiZ2d9GfEhAb0RmlEQG7WUpT1bxBSFtX/jpzf1djexKod6tIG2qm4Rr17j3aTA9wTrtRgcpuEIp95tGBwaaqQWgXCpIibaiZhmvUO2A3y+QAS1b1YxnvVXKDUOwzE7u7NJylBlTdKtKmmmW4Rr17jzZLaWrB8rWpT4w3qOgGIW2oSk7ypkPDWapLQVJEaq6eAbtZ2mOzgrJTWSea5M1God6tuWUa3R7bLhQkRaStNMvkAFnBumcUwbqUm41mqUFoF2qTFJG20iztsc3UeUoq1/CHLptZB9AH9Lv7281sKnANsA+wEjjd3Z83s92AK4HXAk8Cp7j7Q3Ed84Azge3Ax919ebHt6qHLImNDI4eDNMtQlGrRQ5cb4xPAfcBL4usvAZe4+zVm9h1C8Ls0/r/F3Q8ws1PjcqeY2UHAqcDBwETgx2b2SndPH1Ak0sLa7aJba42e7UdVn62vodWtZjYJOBH4bnxtwDHAdXGRhcCs+PfM+Jr4/lvi8jOBa9z9OXd/EFgHHF6fPRCpn2Ya/9cqmmU4SCFLVvVz9PwVTJ27jKPnr9D32WQa3Sb5NeAzwAvx9T7AgLtvi683ALnbsB5gPUB8/6m4/HB6ymdE2kYrXPCbTbMMB8miG5/m17AgaWZvBx5395V13OZZZtZnZn2bNm2q12ZFqqLZL/jNqNkH1+vGp/k1siR5NPDXZvYQoaPOMcDXgW4zy7WVTgJyt1T9wP4A8f29CB14htNTPjOCu1/m7r3u3jthwoTq7o1IjTX7Bb8ZNXsPU934NL+GBUl3n+fuk9x9CqHjzQp3Pw24FXhnXGw2cEP8e2l8TXx/hYeuuUuBU81st9gz9kDgV3XaDZG6afYLfjNqluEg+XLtkFljC3Tj0zyaoXdrvs8C15jZF4BVwOUx/XLgP81sHbCZEFhx9zVmtgi4F9gGfEQ9W6Ud6eHAlal3D9NiPZDze9zm041Pc2n4OMlG0ThJEUlT7jCb5PJ7dXXy7PPbGNq+47ra1dkxovR69PwVmU8HSZtirplonKSISBtJC3iQXRovd1xl/vIDg0M7LZP/PMes9kYDbpt7zOh2WKpOQVJE2lJawJvzg7vBGC7p5QfBch+SXOhByEnJwNgsE7BLaRo9TlJEpCYD6tMC2NALPqIqFEYOuSi3t2mpvVCTAbCaHbA0EUHtqSQpY46mdmsutZo6rpxhFP0DgyxZ1V92KS9r+aT8AFhKB6xSztFGT7k3VqgkKWOKZjhpPrUaUF9u9eW8xat586smlFXKSysVdo4zxu/RWXDIyazpPdw29xgenH8it809JrX3a7FzVBMR1IdKkjKmlNvmJNWTVTqq1YD6tOdKdo6zEW2SSYND27n1/k1cfNIhJdc0jGZYTtbxyDpHz1+6ZsR6s45P/8AgU+cuUy1JlShIypiiGU4ao1DVYK06smQFMIBPXntX6mceHRjcaVxlrt0vKwhWMg6z0PHIOhcHBodYsqp/eFuFqnqTJdBcHqUyqm6VMUVTuzVGoRJ8LWcSSqvWnDW9h54Sz4Nyq+dL7UhT6HgUOheTValpxy2fql9HT0FSxhRN7dYYhUrwjZg6rtTzoJx2v3ICaqHjUehcTH4ud9w6zDKXL7QtKY2qW2VMqdXUbrXqMdsuPXGLVanWe+q4Us+Dcqrny2nvLnQ8Zk3v4YIfrWHL1p0nJsgvZc6a3sOnMqqOsz4j5VGQlJYz2sBR7Qtyrbrit1MX/7RONF2dHbz5VRMKtvfVUinnQTntpeUE1KzjkStFnveOgwu+X0oeC31GSqfqVmkpzTiEo1Zd8Vupi3+xtri0KtWTX9vD9Sv7y/4u6zmAvpzq+XLau4tVMZdTBZ3VNjl+j86meOJJq1NJUlpKMw7hqFWP2Ub1xK1kgu9SSrz5Jbej568o+7usd+m6nOr5YqXDtHUXynOpNR56OkxtKUhKS2nGIRy1GsIwmvVWWiVdSRCq9Malku+y2jdJpRynVghW9W7THUsUJKWlNOPk0OWWIGq93tGUtioJQpXeuFTyXWa1vVVyk1SLUqmCVftRm6S0lGYcwlGrIQyVrnc0bZmVBLxKx56W+10uWdVP1mCHSm6Sso7TJ6+9q+6ThS9Z1c+hF9zMlLnLmDJ3GdMvvHl4+5rEvLEaVpI0s/2BK4H9CBNEXObuXzezvYFrgSnAQ8C73X2LmRnwdeAEYCvwfnf/dVzXbODcuOovuPvCeu6L1E+ztr/UqgRRyXpHUyWdVbpzQhti2rGutMRb7ne5YPla0h4RbzEP5Sp0POrZk3jJqn7m/OBuhl7YsXdbtg4x57q76Xt4M9ev7G+LHs6tqpHVrduAT7v7r81sT2Clmd0CvB/4ibvPN7O5wFzgs8DxwIHx3xHApcARMaieB/QSfssrzWypu2+p+x7VQLuMk6smVWkVNpoq6bSAl1OoQw5UduNSzneZFdScygJGsSd41KJDWNrvecHytSMCZM7QdueqOx7B895qdEe1saZhQdLdNwIb49/PmNl9QA8wE3hTXGwh8FNCkJwJXOnuDtxuZt1m9tK47C3uvhkgBtrjgKvrtjM10k7j5KR+RtNGmgx4aQEk6wJdjxuX7j06UwfYZ00xV0yhG4KcanYIy/o9F9p+foCsRb6ksKbouGNmU4DpwB3AfjGAAjxGqI6FEEDXJz62IaZlpbe8Wgx3UMm0/Y22SjoX8KbOXZZavTnaC3Ql5+CSVf388U/bdkrv7LCK26OL3RBA4dJ3ufuR9XvuMGN7VjTMoFl06qfhQdLMXgxcD3zS3Z+2xDyE7u5mVt7ZU3hbZwFnAUyePLlaq62Zag93UMl0bKjWjVAp1ba1GlOZL6tK8kW7hktYubP25Of7fUdOHtH2B6Gts39gMLUttpL9yPrdbnenc5yl7l+aRndUG2sa2rvVzDoJAfIqd18ck/8Qq1GJ/z8e0/uB/RMfnxTTstJ34u6XuXuvu/dOmDChejtSI9V+YkUrzeBSK83cU7AaeavmjETFep9Wsq1SzsH843DuktWZJb2BwSE+ee1dZeUhLd/Xr+zn5NfueDqIwXApOm2dWfvx6UV3Z35/Wb/bDistQNZr8ncZqWFBMvZWvRy4z92/mnhrKTA7/j0buCGRfoYFRwJPxWrZ5cCxZjbezMYDx8a0llft4Q7NOBC/nppxSrtq5220N0LJALVg+drhwJF2ga5kW8XOwbTj8P3bHykp76XmISvft96/idvmHkNPd9dO1cz568wK2tvdh/M95wd3M/3Cm5k6dxmHXnAzW559LvMzxfR0d4143JfUTyOrW48GTgdWm1luGvtzgPnAIjM7E3gYeHd870bC8I91hCEgHwBw981mdhFwZ1zuwlwnnlaRVWVV7eEOWdVn48xGPMy1XTXjlHY51crbaG6E0qoQr7r9EZxwkc4/9yodU1moCjftOFSiUB6K5Tvr/f6BQabOXcZeXZ0l5WHoBR/uaDQwuHOHo1Ll3xirX0F9NbJ36/9C5tjgt6Qs78BHMtZ1BXBF9XJXP8XaNqrZazCrN992d+YtXk3fw5u59f5NTfXjq+YFoZlL0tXK22iGf6QFqPwqR9jR5lbJtor1vK3Wd1EoD4XyvWRVP+MKdKRxRhfwStXT3TXinIfQ7to/MJhaFQzqV1ArmnGnwerZTljoIa2DQ9u56vZHmqoqstrVo9Vu462mauWt0ir6Jav6C44ZhJ3Py0q2lTsHuxOlsd07d1yGqvVdvPlV6X0Olqzq59nndu4lm+uk86lr7yq7p2m19XR3cdvcY3hw/onMmTGN85euGW53BYpWBUt1Nbx361hX79JNoYe0Zv34GnWHWu3q0VrNsVoN1cpbqVX0yRL6Xl2dPPv8zoEjTVpvz3K39fy27WwdemH4/S1bh4ZLQ6WMXSzF929/hGX3bOS8dxw8nJ/8Wpskz/u/EskSXqXSOkaVciyaoTakXSlINlgjJuwuNtNIUiN/fNW+gRhNG29+te+bXzWh5KrpUp80UWne0vaznGEY5VYfpjUJZG3n/KVrRqw/a1u5m5/b5h4DFB67CGF85ND2wiEpGXxnTe+pWntnan7GGZ0dNhz89+gcx9ALXjSPSfntvuXktxlqQ9qVgmSDNaJ0k7bNrLvgYj++WnYiqMUNRCVtvGntxskel4XahdI++8lr7+KcxfewW2cHW7YODQ8mT+sck7+uXPDI+kyhYL5XVydmpM5aU66sEn0yj+WWrHI3P7nvKNcGl6/DigfItHzW6oavO5bEk6VjxzjldZOGj/3uneMYTLyf1NXZkTqso9T8NkttSLsyb3D9e6P09vZ6X19fo7MBNKa3WtrFNH8wddaPN7mOtABfbBxXqftb6frL3U4xWRfrNPlBq5zPQvr+heNwT+ZFFuBFu3bwN4f17PQd1ss4gxd89FWOPYmOKvmlUAjHp9z9M+CSUw7l04vurnp7Y25cZdp3nGtbzCnlJiep0LmTO87FbqyqzcxWuntvXTbWJBQkG2i0F/FqBIH89iIzGNg6VNL6sn7EaReHtAteTqHAV62HBxfbTiFZ07Nl6RxnvHj3XRjYOlRRwEgev7QnRNRToSBQb+P36OS8dxxctCo2X3dXJ89teyE1uHaOMzBGlExzAcgse+5U2HE+ferauzKfTvLg/BNLzme+rDbJ3HFoRF+BsRgkVd3aIKOdIq4aU8yltU11dXZwySmH7lSSSQtUpbQZlnKRL1Z1lxUgC92ZZ3X6OX/pmrJLoeUWj5Lj4yrRPzA4PG41azq2esj1+Ozu6hwuKTbSHrvuMvzdldO5Z2h7eoA0gwXveg2Q3hacFqTSSnBZQXucGVPnLqv4BrZZHws31qgk2SDFSmH5pa/8u8fpF96c+USEZCmukKx1dHd18qLddhnR8zF5p527g866OHSY8YI7E7u72Pr8tpIDhsGIcWGF7qKz3s+tp9BZ/bXETUBaIO57ePPwIPpG6ers4OTX9pQ928xo5W42qtFTM80eneOG22Ir2cZDsWSW/72Vc54lfS3vhjBfKTUZpfRCrbQWo9mMxZKkgmSDZFXh5dpPskpf4/fo5MRXvzTz4llqFc+SVf18MmMoSClyd9LV6K6fr6uzg907x2Ve9Iq9X0x3Vyd3nXdsWV3sx5JaBcjccYfy22pz+cqv5cip9Lss56aykGQwzZqMoFrbaqSxGCRV3dogWT039+rqLFjFtmXrUMHSRa7nZ1Yvw1y1WdqEAuXoHxjk7EV3Da9ru3tFj/xJMzi0veDFrtj7xQwMDjH9wpt5PqOdql1UGuxqESA7O4zz//rg4deV9DR1yBwjm0sr98avWj1ek72mp85dVtNtSX0pSNZRfuBK8+zz20Y17dXW57cxJe9Hmrzo5WJvNYJZcl3lPOqnGVRjGESj5GoTrl+5oWCPV4eq3bhA5UF3XOwYk5sVZtb0nrLG6iYVCjSF2gez1GJ8YSPGPkvtaFq6Ojl3yWo+VWBqqZxyBh+nadTFv5UCZKONpgzf093Fqs8fyxdmHcJ9Fx1fdPlc+2K58j/T1dnBaUdOHu7tmquJ6Onu4munHFpwG7lTIzmtYNaUdu9LbCNNsUCTtt4stRpfWO2n90hjqSRZY8WGP8jY41RWKjPY6ULbU6RE1hPHwJbTEcmA046cXNZk96WW4NJm1knbRtYQnmKBJvf5tN9ccmhOLXuKqldqe1HHnVFI6/kGO34chWbZECk3UL7vyMl8YdYhI9KWrOovOE4v19Gl1F68uQCZv51iyuk4U07nskaPI5aRxmLHHQXJCp27ZHXDhwmMlgHde3S2dPtcPZXTq7Y7ZehMmuQjkdJmPYIwm84X/yZ7+EDauVhqsKtmICl1WEY79PIcqxQkx5BKg2S7VZ9+7ZRDM0si7aSUUltuhpVcZ5fulBmIoPBA9s4OY8E7XzOi9JZVDZkWLEYzw1CzlZqqOeuRNIexGCTVJlmGdig9JvV0dzFrek/Jg+dftGsHzz7fmCETnR3GKa/bn/++e+OICRZOfPVLR7SdZc1Be/Jre0ZM9J01QUKpF++04TX5Ez7khgWU075W6UO2q/lw7mpR25y0g7YpSZrZccDXgQ7gu+4+v9Dy5ZYkC7X91Fp3VyfPPrcttQdppd3y84NCqY+CKtb2lBuH2ZPXRpv2VIi0eTPzHzkE5c9VWeosKfW8eDdjSU+kXGOxJNkWQdLMOoDfAm8DNgB3Au9x93uzPlNukKxkhpBqyFXfAanT1BVKL2XeyUqU+zSD/M9ldXRS8BBpbgqSLcrMjgLOd/cZ8fU8AHe/OOsz5QbJcp8EkaWnSJVgfnXiaGb7V+lFRKppLAbJdmmT7AHWJ15vAI7IX8jMzgLOApg8eXJZG6h0hpCc/OrN3pftnRrAyu16X0gztlOJiLSSdgmSJXH3y4DLIJQky/lsqZN559rk0npGJgOWApiISPNrlyDZD+yfeD0pplVNsqdeuW1xIiLSmtolSN4JHGhmUwnB8VTgvdXeiEp/IiJjS1sESXffZmYfBZYThoBc4e5rGpwtERFpcW0RJAHc/UbgxkbnQ0RE2ocelSUiIpJBQVJERCSDgqSIiEiGtphxpxJmtgl4uMKP71vNvDTQE7TPvlSTjks6HZdsrXZsnqjwcy9z9wlVzUmTG7NBcjTMbHRPa24S7t7bLvtSTTou6XRcsrXasRlrU8uNhqpbRUREMihIioiIZGibcZJ1dlmjM1BF7bQv1aTjkk7HJZuOTRtSm6SIiEgGVbeKiIhkUJAUERHJoCApIiKSQUFSREQkg4KkiIhIhv8Pckqljjc5iPMAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Plot the NB_CHECKINS_all and NB_CHECKINS_missing_rate columns of the join_stop dataframe to see if they are correlated\n",
    "\n",
    "x = join_stop['STOP_NAME']\n",
    "y = join_stop['NB_CHECKINS_missing_rate']\n",
    "\n",
    "fig, ax = plt.subplots()\n",
    "\n",
    "ax.set_xticklabels([])\n",
    "\n",
    "ax.set_title(\"NB_CHECKINS_missing_rate by STOP_NAME, ordered by NB_CHECKINS_all\")\n",
    "\n",
    "ax.scatter(x,y)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NB_CHECKINS_all</th>\n",
       "      <th>NB_CHECKINS_missing_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NB_CHECKINS_all</th>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.82108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NB_CHECKINS_missing_rate</th>\n",
       "      <td>0.82108</td>\n",
       "      <td>1.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          NB_CHECKINS_all  NB_CHECKINS_missing_rate\n",
       "NB_CHECKINS_all                   1.00000                   0.82108\n",
       "NB_CHECKINS_missing_rate          0.82108                   1.00000"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pearson correlation coefficient\n",
    "\n",
    "join_stop_corr = join_stop[['NB_CHECKINS_all', 'NB_CHECKINS_missing_rate']].corr()\n",
    "join_stop_corr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a high correlation between the number of checkins with missing information about the rate plan and the total number of checkins per stop. For NB_CHECKINS above 10 million, the correlation still holds but the slope is much steeper.\n",
    "\n",
    "Therefore, dropping those rows would not result in a loss of information when it comes to the number of checkins per stop."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Correlation between missing ticket type and number of checkins per day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group metro_nb by 'DAY' and sum() 'NB_CHECKINS'. Sort in ascending order by 'NB_CHECKINS'\n",
    "\n",
    "gp_day = metro_nb.groupby('DAY')\n",
    "sum_day = gp_day.sum().sort_values('NB_CHECKINS', ascending=True)\n",
    "sum_day.reset_index(inplace=True)\n",
    "sum_day = sum_day[['DAY', 'NB_CHECKINS']]\n",
    "\n",
    "# Group rate_missing by 'DAY' and sum() 'NB_CHECKINS'\n",
    "\n",
    "gp_day_missing = rate_missing.groupby('DAY')\n",
    "sum_day_missing = gp_day_missing.sum()\n",
    "sum_day_missing.reset_index(inplace=True)\n",
    "sum_day_missing = sum_day_missing[['DAY', 'NB_CHECKINS']]\n",
    "\n",
    "# Merge both dataframes. The resulting dataframe is sorted by 'NB_CHECKINS'\n",
    "\n",
    "join_day = pd.merge(left=sum_day, right=sum_day_missing, on='DAY', how='left', suffixes=('_all','_missing_rate'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZwAAAD7CAYAAABexyJvAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjAsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+17YcXAAAgAElEQVR4nO29fZxdVXXw/12ZXGB4KRMkjw8MCUFFqDQlkfDiQ18AKxF8hAjKS9GitaVPf1gFMRqUGl4llSLWVm2pUl9ASBSMQegTUKC2PgVMTCIGoaaCJkPEKJmIZEgmk/X74+wznDlzXva599x7z713fT+f+czMed3nnL3X2nuttdcWVcUwDMMwms2UdhfAMAzD6A1M4RiGYRgtwRSOYRiG0RJM4RiGYRgtwRSOYRiG0RJM4RiGYRgtwRROByMiM0XkNyLS18A1fiMiryizXO1GRJ4WkT9qdzmqgIicJCKbWni/1HcvIl8QkWtbVRajuYjIlSJyq/t7loioiEzNOsdb4biK9AsR2Sey7c9E5KHI/yoiLzgh9ksRuV1EBjyvP19EviMiz4vIFhH5NxE5w+17p4j8R0qZ/ihyzJi7d/Tn4Mjxfywiq9z2zSLyryLye27f+Mtz/w+KyBMi8ikJeEhE/sztO8k962di5fkPEXmn+3sPEblRRDa5+z0tIp/0eRe+qOrPVHVfVR1r4Br7qupPyixXo1RFYbjvvDtSlzaJyDIROTbhWBGRn4jI47Ht14rIt2PbXi0ivxaR2c1+hm7Ftb/HRGRKZNu1IvIF93coAMNv96yIfEZEap7X95YVsTK9KnLMaEwWDUeOFRF5r4j80MnMTSLy1bBOxJWziBzlyvEB939c9qmIfDBWnk0icpL7e0BEbhGRnzsZ+18issjvbZdH0RFOH/C+nGOOVtV9gVcA04Ar8y4qIm8Fvgp8CTgEeDnwUeDNBcv3n06ARn+ecfd4P/BJ4GPu+jOBzwBnJpTnUOA7wApVfa8mz459AXiHiMxKKcvlwDzgOGA/4CTg+wWfp+uQnB5QBXnG1ef9gBOAJ4B/F5HXx477A+B/AK+IKaRrgP8pIn8OgaAB/hn4hKo+VmZBy363HfCtDgbOyzlmwH2/2cDrgIvzLlpEVuSwNCaLop3vvyOQpe8FDgBeDSwH3pRQnrnAg8C1qvq3Kfd6DvigiOyXsv8mYF/gt4H9gTOADQWfp2GKKpwbgA+Ix6hFVX8NrABek3Wca4CfAK5R1c+p6jZV3a2q/6aqf16wfGn32B+4GrhYVe9S1RdUdVRV71bVhbFjX0mgbG5T1Q8mXc8xDHwBWJyy/1jg66r6jAY8rapf8ijr0yKyUER+4Ho+nxeRl7se1vMi8i0RmeaOnTCMdT2dn7jjnhKRC9z2V0kwYtwmwchzaeR+0V7ZF0Tk0yJyj7vGI+59hMeeKiJPuut8xl3zz3Ke550i8l0RuUlEfgVcKSKvFJEHRORXrjy3hXVKRL5M0MDvdr3CD7rtJ4jI/xORYRFZF/bcMjhWRB4Xka0i8i8ispe7zg9FZLwjIyI1V4a5WRdz33CTqn4U+BzwN7FDLgS+Adzr/g7P2wH8KbBEgtH2RQQdsetyyh+W72ARWSEiz4nIhlBxuX1XisjXRORWEfk18E4R6XffcasEo61jE653pwRWhKdE5L0515siIotE5L/d91omIgdEznmHiPzU7fuIxyMdKCL3u/r1b65zh6t3N8bKukJELs241seBq3wUo6r+AriffHnkLSvqRUQOJ1B856vqA6q6Q1W3q+ptqrokduxxrtwfVtVPZ1z2R8B/Au9P2X8s8BVV3erk6xOq+jWPsv6diGyUYES+WkR+3+shUyiqcFYBDwEfyDvQCcUFwMM5hx4BzAByH74BXgfsBXw957hXECibf3KCJY/rgLNF5IiEfQ8D7xeR/09EZjvF6svZwBsIej1vBv4V+DAwneCbvTd+ggSmzk8Bp6nqfsD/Ata63dcA9xEIukOAv8+493nAVe7YDe4ZEZEDCb7R5cDLgCfdPXw4HvgJQW/xOkCA6wl6qL9N8P2vBFDVdwA/A97seoUfF5FB4B7gWoLe4AeAO0VkesY9LwDmA68keI9XuO1fAt4eOe50YLOqrvF8FoC7gNe6d46I7A28FbjN/ZwnInuEB6vqIwSdky+75/9TVR31vNcdwCaCd/VW4GMickpk/5kE32XA3XsxwTO/kuD5x5WfBOanu4F1wCDweuASEZmfcb2/ImjHf+jKsBX4tLvea4DPAu9w+15GUL+yuICgPh5IUD9vc9u/CJzvyhjWtz8CvpJxrbuAXwPvzLknTtnPJ18e+cqKRng9sElVH8057jjg/wKXqurnPK771wTf84CEfQ8D14nIu5zC8+V7wByCdvcV4Kth560e6gka+CjwVxmN/fsS2Cp/SdBT/aec673M/d6cc9wJrnc7/uOun3XMf0fu8UtV3ZVzj98B9gGW5hwHgKr+HPhHgh5RnOsJesEXECjqIRG5MOG4JP5eVZ9V1SHg34FHVHWNqr5I0BDSeuO7gd8RkX5V3ayq6932UeBQ4GBVfVFVJ/nDInxdVR917+o2gsoGgWBe73p9uwiU2889n+cZVf17Vd2lqiOqukFV73c9uy0EI9w/zDj/7cC9qnqv653dT/BOT8845x9UdaOqPkcg5M93228FTheR33L/v4NAERThGQKlGY70zwJ2ECj1e4Aak00jVwCvAr6sqqt8biIiM4ATgQ+577aWYHT1J5HD/lNVl7v3MgKcA1ynqs+p6kaC7xRyLDBdVa9W1Z3Od/fPTDRLxa/3f4CPuNHdDoKOwVvdqOKtwDdV9Ttu318T1MEs7okc/xHgdSIywwnfbQTCGFemh1T12YxrqbvnX0cVfIxfOlkxRGAGz+vY+sqKcxLkUd4xD0bukSfvIDDhbiPocObi6sf9wIcSdv8VQXt+D/C4Gy2f5nHNW1X1V67t3gjsSTBIqIvCCkdVfwh8E0hzOL3W2Sr3Iuj9/HuORvyV+31Qzq0fVtWB6A9BTzjrmNAc9CuCoXze0HsFcAvwQDjU9+BvgPkicnR0o6qOqeqnVfVEAsF0HXCLiPy2xzWjjWwk4f994yeo6gvAuQQCYrMEZrEj3e4PEgjIR0VkvYj8aca9o0pke+ReBwMbI/dTgp63Dxuj/0hgIrxDRIac6eZWgh5vGocCb4s17t8ju85E7/lTV3408Ol9l2BkOgCcxku9bF8GCYRdKGQuBJa5RvkicCeRkYW77wjwFLAefw4GnlPV52PPMhj5f+PEUyZ+J3d8yKHAwbH3+GGCkWfa9Q4Fvh45/kfAmDsnXide4KX2nEb0+N8Q+B7CwJ4v8tLo8+14dARU9V6CevgXKYcc6GTF3gTffWXOJX1lxbIEeZR3zMmRe+TJOwhGkquA+53FyIePAn8pItFviuvofUxVjyFQeMsIRitJo6FxROQDIvIjCczowwT+n6y2mkm9YdGLgT9nYsWfgDMZfA44jGDkkMaTBJXw7DrL4sN/EvRAF+QdqKrvJ1CoDzhTTt7xvyJwMF6TccyIs79uJceG3AiqulJV30BQmZ8g6L2iqj9X1T9X1YMJGuZnxPltCrCZiLnEmQjzzCfjRYv9/zG3bbaq/haBcJGM4zcSjAyijXefuL07xozI3zMJRiUhoWB7G0GPfsjzOULeAnxfVV8QkUOAU4C3SxAB9HOCnv/pzizUCM8AB8hER/BMgt56SPxdbWbys4dsBJ6Kvcf9VDU6Ukx696fFztnLvbMJ93KmxZeRTfT4fQlMNeG3uRU403XefpvAie7DRwgU595pBziF/wUCK0jWd/GWFQ3wbeAQEZmXc9wY8McEHeuVkVF5Kqr6BIGpMdWfpoF//WME1pzD0o5z/poPEoyapzmluo2JbbUQdSkcVd1AYHaa5EsIkWBuyLsIeuSpYbeup/x+gmHxu0TktyRwVP6eiNxcT/kS7rGNQPN/WkQWiMjeEjiLTxORjyec8h6CqJBvx3sKKXyCwJ8xPnoRkUskCKvtF5Gpzpy2H1DEV+CNGzWc6fwKO4Df4MwbIvI2JxghUHpKvukjzj3AbPf+phI4Pf9nncXdz5Vvm1PqcWfsswT+tJBbgTdLEDrfJyJ7uXebpfAuFpFDXA/uI0w0ky4HXksQJZQbyAHjYayDIrIY+DMCAQeBSe6/CMwMc9zPqwl63ecnXSt23TDwY1Z8nzOJ/T/gevfMvwu8m+B9pLEMuFxEprn381eRfY8Cz4vIh1y97BOR35GEMO8I/0hg+w+d+9NFJIzW+hrwv11b3YPAtJwnU06PHH8NgVVio3veTQQ+gy8DdzolkYuqPgT8kNioMoqI7EnwrX5OxiisDllRGFX9MUHU2+2uHu/hvu95EgtVdh33txG4KO6VyLSUDK4ikL3joy4R+WsROTa8F0HdHybo8KexH7AL2AJMFZGPArlKL4tGJn5eTaAh46wTkd8QCLYLgbc4O3oqGkRLnEsQzfMMgcC5liDqpwivk8nzcI5197iRQLFdQfACNxIolkm9KKcELyJooN/K66m6HsPHCXprIduBGwkq+C8JBPTZ2rw5L1MInu8ZAjPFHwJ/6fYdCzzivssK4H1Fy6GqvySo+B8naLCvIRju76ijrFcRCPxtBIrsrtj+64ErnBnnA04gnUkg5MNvt5Ds+vsVAp/KT4D/JqhP4bOMEJi9Dku4d5yD3Xv7DYEwnA2cpKr3uf0XAp9xo8jxHwJB7eOzm0Fg9kobZZ0PzCL4rl8HFqvqtzKud5W73lMEzz9ultJgvtb/JlCKTxHUy88RmEnS+DuCOnOfiDxP4Hw+3l1vPUG9/grBaGcr+WbWrxBYSJ4DjmFiAAcEo8/ZFPerXcHE9hcy7L7fswQBAWe49p1KEVmRw7kJ8uh/uH3vBf6BwGw2TFBH30IQ1BEvz04CP+GLBNGb/Tnlf4rg/UXlswL/QvDNnyEISnqTM2umsZIgaOG/COrUi0w2uRZCct69YSQiQTTRJuACVX0w7/iq4Xprr1bVuMBrdTmuALaoal5wTU8gIn9AMII7NE8xGJ1H1Sd2GRVCgvDZRwjMpAsJbLl5YaaVw5nZ3k1gYmkrqmqpXhwSZAF4H/A5UzbdSctyqSUMLcOfhiYSdRryUv6zpJ94mHfVeB3B0P+XBPODFqjqiIj8Y8rz/GN7izsZCSZObgT+VVW/0+7yGAESRG8OEwS8lJoCKuFendwGS0VEfj/tXTTlftaRMAzDMFqBZYs2DMMwWoIpHMMwDKMlVDpo4MADD9RZs2a1uxiGYRgdxerVq3+pqlm5BttCpRXOrFmzWLXKK+2UYRiG4RCRn+Yf1XrMpGYYhmG0BFM4hmEYRkswhWMYhmG0BFM4hmEYRkswhWMYhmG0hEpHqRlGr7F8zRA3rHySZ4ZHOHign4Xzj2DB3NxlmQyjIzCFYxgVYfmaIS6/6zFGRscAGBoe4fK7HgMwpWN0BWZSM4yKcMPKJ8eVTcjI6Bg3rMxaI8swOgdTOIZREZ4ZTl7gMm27YXQauQrHLX36qIisE5H1InKV2/4FEXlKRNa6nzluu4jIp0Rkg4j8QEReG7nWhSLyY/fjsxqiYfQMBw8kL+SYtt0wOg2fEc4O4BRVPZpgado3isgJbt9CVZ3jfta6bacBh7ufi4DPwviiV4sJlqc9DlgsItPKexTD6GwWzj+C/lrfhG39tT4Wzj+iTSUyjHLJVTgaEC7GU3M/WYvonAl8yZ33MDAgIgcB84H7VfU5Vd0K3A+8sbHiG0b3sGDuINefNZvBgX4EGBzo5/qzZlvAgNE1eEWpiUgfsBp4FfBpVX1ERP4SuM6tDf9tYJGq7gAGCVZUDNnktqVtNwzDsWDuoCkYo2vxChpQ1TFVnQMcAhwnIr8DXA4cCRwLHAB8qIwCichFIrJKRFZt2bKljEsahmEYFaBQlJqqDgMPAm9U1c3ObLYD+BcCvwzAEDAjctohblva9vg9blbVeao6b/r0yi3nYBiGYdSJT5TadBEZcH/3A28AnnB+GUREgAXAD90pK4A/cdFqJwDbVHUzsBI4VUSmuWCBU902wzAMowfw8eEcBHzR+XGmAMtU9Zsi8oCITAcEWAv8H3f8vcDpwAZgO/AuAFV9TkSuAb7njrtaVZ8r71EMwzCMKiOqWQFn7WXevHlqK34ahmEUQ0RWq+q8dpcjjuVSM3oOS5BpGO3BFI7RU1iCTMNoH5ZLzegpLEGmYbQPUzhGT2EJMg2jfZjCMXoKS5BpGO3DFI7RU1iCTMNoHxY0YPQUYWCARakZRusxhWPk0m1hxJYg0zDagykcIxMLIzYMoyzMh2NkYmHEhmGUhSkcIxMLIzYMoyxM4RiZWBixYRhlYQrHyMTCiA3DKAsLGjAysTBiwzDKwhSOkYuFERuGUQZmUjMMwzBagikcwzAMoyWYwjEMwzBagikcwzAMoyWYwjEMwzBaQq7CEZG9RORREVknIutF5Cq3/TAReURENojIUhHZw23f0/2/we2fFbnW5W77kyIyv1kPZRiGYVQPnxHODuAUVT0amAO8UUROAP4GuElVXwVsBd7tjn83sNVtv8kdh4i8BjgPOAp4I/AZEZk4o9AwDMPoWnIVjgb8xv1bcz8KnAJ8zW3/IrDA/X2m+x+3//UiIm77Haq6Q1WfAjYAx5XyFIZhGEbl8fLhiEifiKwFfgHcD/w3MKyqu9whm4BwZuAgsBHA7d8GvCy6PeEcwzAMo8vxUjiqOqaqc4BDCEYlRzarQCJykYisEpFVW7ZsadZtDMMwjBZTKEpNVYeBB4HXAQMiEqbGOQQYcn8PATMA3P79gV9FtyecE73Hzao6T1XnTZ8+vUjxDMMwjArjE6U2XUQG3N/9wBuAHxEonre6wy4EvuH+XuH+x+1/QFXVbT/PRbEdBhwOPFrWgxiGYRjVxid550HAF11E2RRgmap+U0QeB+4QkWuBNcDn3fGfB74sIhuA5wgi01DV9SKyDHgc2AVcrKpjGIZhGD2BBIOPajJv3jxdtWpVu4thGIbRUYjIalWd1+5yxLHlCYzKs3zNkK3HYxhdgCkco9IsXzPE5Xc9xshoYH0dGh7h8rseAzClYxgdhuVSMyrNDSufHFc2ISOjY9yw8sk2lcgwjHqxEY5RaZ4ZHim0vVX0gpmvF57RaC2mcIzKERV0U0QYSwhsOXigvw0lC+gFM18vPKPResykZlSKUNANDY+gkKhs+mt9LJx/ROsL5+gFM18vPKPRemyEY1SKJEEH0CfCbtVKmHaqauYrk154RqP1mMIxKkWaQNutylNL3tTi0iRz8EA/QwnlbKeZr2x64RmN1mMmNaNSpAm0/ftrLS5JOgvnH0F/beJSTu0285VNLzyj0XpM4RiVYuH8I6hNkUnbX9i5i+VrJuV6bQsL5g5y/VmzGRzoR4DBgX6uP2t2VznTe+EZjdZjqW2MyjH36vvYun100vbBgX6+u+iUNpTIMDqLqqa2sRGOUTmGE5QNBKG5VRnlGIZRHFM4RuXIckxfftdjpnQMo0MxhWNUjiSHdYjNBTGMzsXCoruIbklFEpb5kqVrE/fbXBDD6ExshNMlxGfoh6lIqmx+Wr5miBOXPMBhi+7hxCUPTCjrgrmDDKaY1mwuiGF0JqZwuoROS0XioyBtLohhdBemcLqETktF4qMgbS6IYXQX5sPpEqqeiiTuX0oqK0xWkAvmDpaiYLrFv2UYnYyNcLqEKpufksxnk3MJBDRDQXaif8swupFchSMiM0TkQRF5XETWi8j73PYrRWRIRNa6n9Mj51wuIhtE5EkRmR/Z/ka3bYOILGrOI/UmVTY/JZnPFCYpnWYpyE7zb3UrWUEiRm/gY1LbBVymqt8Xkf2A1SJyv9t3k6r+bfRgEXkNcB5wFHAw8C0RebXb/WngDcAm4HsiskJVHy/jQYzyzE9lk+ZHUgLF2GwzV6f5t7oRW9DNAA+Fo6qbgc3u7+dF5EdAVg05E7hDVXcAT4nIBuA4t2+Dqv4EQETucMeawuly0nw2rcqNVnX/Vi+QNco0hdM7FPLhiMgsYC7wiNv0HhH5gYjcIiLT3LZBYGPktE1uW9p2o8tpt3+p3fc3bJRpBHgrHBHZF7gTuERVfw18FnglMIdgBHRjGQUSkYtEZJWIrNqyZUsZlzTaTLv9S+2+v5E+mrRRZm/hFRYtIjUCZXObqt4FoKrPRvb/M/BN9+8QMCNy+iFuGxnbx1HVm4GbIViewOspjMrTbv9Su+/f6yycf8QEHw7YKLMX8YlSE+DzwI9U9ROR7QdFDnsL8EP39wrgPBHZU0QOAw4HHgW+BxwuIoeJyB4EgQUrynkMwzCqjI0yDfAb4ZwIvAN4TETCbIofBs4XkTkEwUZPA38BoKrrRWQZQTDALuBiVR0DEJH3ACuBPuAWVV1f4rP0BDaB0ehUbJRp2IqfHUQ8tBQCs0S7e4qdqgQ7tdyGkUdVV/y01DYdRBVDSzt1fkWnlNuUotFNWGqbDqKKoaXtnsVf7+z1dpfbB0vJY3QbpnA6iCqGlrZTCTYikKuovON0glI0jCKYwukgqjiBsZ1KsBGBXEXlHadVStFynBmtwhROB1HF0NJ2KsFGBHIVlXecVihFM9sZrcSCBjqMqoWWhmXxcWyX7QBvJEdakXK3i1ZMlqxiIIrRvZjCMRrGRwk2IyqsUYFcNeUdpxVKsRN8WUb3YArHaAnN6El3wiilUZqtFC2TttFKelbh2PyG1tKsnnTVRylVx3KcGa2kJxVOp0z66yasJ11NemGUaFSHnlQ45ihtPdaTLo+yR+c2SjRaRU8qHHOUth7rSZdDFUbnZo426qUnFU6eeccaVHOwnnTjtHt0XgWFZ3QuPTnxM2vSX6dMhCt7drjNNu8M2j06t3Q7RiP05Agny7xz4pIHKu/fKbuXmXU9MDNYlWh38EW7FJ5ZHbqDnlQ4kG7eaXcP0oeyzSpp17v8rh/w4uhuwhWTqmw+6RWB1O7gi3YovG404/VKfY3Tkya1LHoxqWPaeSMRZfPStuqZTzrFDFoG7c6n144cdN1mxrti+WNcunRtT9TXOD07wkmj3T1IH8ruZaZdLw0fxdbKHly7Hemtpp3BF+2INuwEq0MW0bawf3+N4ZHRScd0c32NYgonRieE75atFJOul0WeYmu1T6gTBVK9CrkKpphWK7x2+60aId4WkpRNSJXra1mYwkmg6uG7ZSvF8LzLlq1jTONGtIkIJCq2qCCcIjLpOiOjY1y5Yj07du0u3RbfaQKpXp9Ep/gyylaKnWB1SCNp9J1GVetrmeT6cERkhog8KCKPi8h6EXmf236AiNwvIj92v6e57SIinxKRDSLyAxF5beRaF7rjfywiFzbvsbqfBXMH+e6iU3hqyZv47qJTGhY4C+YOsttD2VxwwsxJ94r7UNKU1vDIaFNs8VVa28YnvLxen0QrfBmNhsc3w5/Wbr9VI/iOWtI6ct2GzwhnF3CZqn5fRPYDVovI/cA7gW+r6hIRWQQsAj4EnAYc7n6OBz4LHC8iBwCLgXmAuuusUNWtZT+UUYywR5qlbgZdTxXgxCUPTOi9FunFJVFGAk9ovxnUdwRSrwkwzc9WlimmjBFUs/xpVbc6pOHjH03ryHUjuQpHVTcDm93fz4vIj4BB4EzgJHfYF4GHCBTOmcCXVFWBh0VkQEQOcsfer6rPATil9Ubg9hKfxyhIXMjE6a/1jfcm0wSSj7Lpr/WxV20KW7dPtmGXYUqoVyCVaf7xFbb1mACXrxlCILFTUJYppgxl0Yn+tGaSZA6sTRH23Wsqw9tHK+kjbiaFfDgiMguYCzwCvNwpI4CfAy93fw8CGyOnbXLb0rYbbSRrdDIYawxpAqkvwWcD0CfCbtXxRgVUyhZftk/EV9jW45NIG4GWaYopQ1l0mj+t2VRl9F0VvBWOiOwL3Alcoqq/FpHxfaqqIpLtAPC/z0XARQAzZ84s45JGBmnCRIDvLjrF69gxVfprfZMEaJqdPd74YLKZrhUNsmzzj6+wrUcIpb17pbyAgTKURSc7+JtF2ui7ChGHrcZL4YhIjUDZ3Kaqd7nNz4rIQaq62ZnMfuG2DwEzIqcf4rYN8ZIJLtz+UPxeqnozcDPAvHnzSlFiRjpZQibeIAb2riWaxAYjvpxwroEIXLp0LTesfHJCQ4o3vnZGXpVt/ikibIuaANO+02CJI4cylIX16P3olIjDsslVOBIMZT4P/EhVPxHZtQK4EFjifn8jsv09InIHQdDANqeUVgIfC6PZgFOBy8t5DCNKkZ5TmpA5+cjpkxpEbYpQ6xNGx3TSsVFl88LOXePH5DWkdk7aLNv800xh24qRQ1nlb7aDvxtGBr02WTnEZ4RzIvAO4DERWeu2fZhA0SwTkXcDPwXOcfvuBU4HNgDbgXcBqOpzInIN8D133NVhAIFRHkV7TmlCJqlBjO5WBvpr7LPn1PFjTz5yOneuHsqc2JbVkNrpZG6GEC9T2MYF69nHDPLgE1saFrRZArvq0WDdMjLo1eAKnyi1/yAw6Sfx+oTjFbg45Vq3ALcUKaBRjHp6TklC5tKlaxOP3TYyytrFp47/n5RdO4m0hpSW6mP//lruNRulyuafJMF65+qhhuefdLrA7vSRQd4UhG4PrrBMA550yjC+rJ6Tr7nJ97ppDUlSujLDI6OcuOSBpr/nRnr0zawTzRKsade9JMHfVgXi77jZc5HqLZfPe/OZgtDtwRWmcDzopF5hWX6JLHNTXhqbOLUpwvaduzhs0T2TGudwQhBCSJXfc7PrRLNMLlnnV+19J73jZs9FqrdcPu+tyBSEbsWWJ/Cgk9Kjl5XmJS2dCJCbxqY2RZi2dw0BBvprILB1++h4qpNLl65llkudMrB3tumsqu+52XWiWctk5J1fpfed9I6Vyfb9Vo8M6v32eVMQul3ZgI1wvOgkB1+Zfokkc1OazyY+yTM878QlD0zy0UQXdEuKfItTxffc7DrhE9CQZ9ZJ2u+TGbwq7ztr7tHgQH/bzNv1fvs068P+/bW2zENrB6ZwIqQ14E6ZPR0v/03nzim94qY1qt2qPLXkTd7Hh0Qj39Ls81V7z9D8GfV5HYc8s07a/uvPms31Z83mhpVPer/vev0VeSSWFiEAACAASURBVOfkHZM19yg+KbmV1Pvt09LcvLBz13inrGpmzbIxk5ojK8ttlbIRp9HsVS/DLMJFo2t8BPC2kVG+u+gUPnnunMq/55BW1ImsjOB5Zp28oAPf911PvfI5x+cY33fcaIbrotT77ZPM1PvuNXXS6L5KZs2ysRGOI6uBhr2pKkep+UY1xXuVJx85PXduRyPRNT4mnFApVTlMOU67y5pn1vEx+0SfYWh4hD6RCcJuwdzBuqLlfM7xOcbnHbcjoKeRbx8/N60DVxWzZtmYwnHkNdCqT4jzETBJjfPWh382vj+tsTYSXRMXavEoo7iyqvp7jtLOsuaZdYrmdUsT2vX4K3zO8b1u3jtu17ycRrKT+2RYr6IZuQzMpOZoVlRQq/Apv8+6NUnD+Uaja0ITztNL3sRN587pyIW0mkk9JqE8s04Rs0+W0K6nXficU1Z7ayR4o9WmOPBrgwKcfOT0ppelHZjCcXSCnyYLn/L7DtPjx5WpjLP8Er1Ivb63tLD1qDkqLaw9LmSzhHY97cLnnLLaW711s9k+zzR82qACd64eaokCbDVmUnO02ybfKD7l91l9MDwuSjNyjnVK5oZmU49JyDca0Tczd1oW8IMH+utqFz7nlNXe6q2b7TLF+bbBTkrXUwTRnFni7WTevHm6atWqdhdjAp0mKKPl3b+/xq9fHGV3xidPW8emzOdOsmP31/pKS07ZSRy26J7UhdWiYebh+0/zg/mYJk9c8kCisBvor7Fj1+6O/R711E3f9152GbOyJZRZFhFZrarz6jq5idgIpwCdlOIGJpc3KUlmlKwAgDId5Gm9y9se/tmESaFVfrdl4ePcj3/HuLDy7Q2nmXO2jYxy07lzJkUvRrOAV/l71FM3651Lk6bcspRe0vcLlc7gQD/bd+5q2tLrVcN8OAXopBQ34OegDGlleo2sGeRRynq3rXAO13uPk4+cnpuqxec7+vgGivg77vnB5o6q60Wpx4eU5ve5Yvljmf6gtBQ94QTWxW8+qqP9x0WwEU4BOinFDRQrVyvTa/jasSH9GXzNKK0YldZ7j+Vrhrhz9dAERSvA2cdM7LE3okyiFFlsL42sspSRXaBV1ONDSutw3v7Ixkk5BaOjTp8pF0XL0qmYwilAp6S4CUkrb9yG3Or0GkmCr0gW4CICvhXO4XrvkdbzffCJLRO25Slo395wmmArMhJOq+s+36RqJumiprg0xZGWLT083kduxL9NdPJtN2EmtQI0I3S6meaetPJecMLMtqbXSArZveCEmaXMG4mT1bss693XO/L1PS/pO4ZmuKJzmZLC0n1HwuFoKOmd+XyTVpuky25baZnN+1IWdQoVio/caFeYdquxEU4Byh76NrvH51vewxbdk3h+s0yFaWaVeYce4PVuiwj4rAy9Zb37eke+Wec1a3npIuWAiVnATz5yOku/t3G8czI0PMLCr60Dys0uUAZlt63la4b4zYu7Jm2v9QnnHjtjQoAFTF4D6uxjBvnmus3jVoS9ahP7+mnK+MoV67tqlGMKpyCtiNYqy9zjay9vpakwTxD4PHeR8qb5LUQo7d3XOxekiE+ljOWls8pxScqS4tEs4HOvvm/SSHh0TLnq7vVe36SV9azstnXDyicZTZhPsM8eU7l2wewJnaX9+2u8EIk8GxoeYemjGycs5LN1++iEep+mdIdHRlm+ZqhrlI6Z1NpIM3t8RYboaSabZqTXKMOsUsS0mTbjPm2l0aHhkcLml7xZ/0XPe/CJLS01PS2YO8i0FHNRVBkkhe6G27O+SWjaCuegJB1TNmW3rayQcphoqtxnz8km6tHdmmm2zlK63RIZCB4KR0RuEZFfiMgPI9uuFJEhEVnrfk6P7LtcRDaIyJMiMj+y/Y1u2wYRWVT+o3QezczfVkSwL5g7yNnHDE4QBs1Kr1GGICgq4JP8FlnvuB77+YK5gyycfwQHD/SPO319zi/iU2mkI5Lnz2g0NDcrlU7Y8YGJK3Y2M5de2W2ryPWKfKfw2Kz3XNUo2HrwMal9AfgH4Eux7Tep6t9GN4jIa4DzgKOAg4Fvicir3e5PA28ANgHfE5EVqvp4A2XveJqRMiakqNB68IktdU8oLEJZZpVGTZt5yyYUffYyfQZlm558yubj7xvoryVOHh7or41fI/6sSSvERuegNIssc2U94f9F2mqRsP/o0hxX3b2+6yeA5o5wVPU7wHOe1zsTuENVd6jqU8AG4Dj3s0FVf6KqO4E73LE9Tb2mGB+K9vBa5dCtSpLU6LtPo8izlxmBVfY78i1bdLQVhktHR0RXnnEUtSmTI7KGR0ZTzZDtmruW1LbOPmaQO1cPeUeCRUeFN6x8krOPGfRqq0nfL1xKPUr8m/bCBNBGggbeIyJ/AqwCLlPVrcAg8HDkmE1uG8DG2PbjG7h311BmEEKUoqOnVjl02zXJLS2AYsHcwdQcY0WevUzBWvY7Klq2rOWpb3jb0Yk5wdJGdO2cuxZvW0mjrbSRbNI78A3cSPt+Sdvi19pz6pTxe07bu8biNx/VNQEDUL/C+SxwDUF9uwa4EfjTMgokIhcBFwHMnDmzjEv2JEWFVjPNe0lly2tEzUwWGheOvs+eVaayBWvRjkiZZctb/TZNSScJ77x32+h3LnJ+EcXbaJRb2vdLOzcpoe2Lo7tz79Np1KVwVPXZ8G8R+Wfgm+7fIWBG5NBD3DYytsevfTNwMwTZouspnxFQRGg1Y+RRrzApew5FnvDwefaylFYzKLtsZa/YCcnvNqnclyxdy1V3r/fq2RetJ0UUb6tNge1aLqHV1KVwROQgVd3s/n0LEEawrQC+IiKfIAgaOBx4lCAw5XAROYxA0ZwH/HEjBe9UiuQAu3LF+nEnre/wOn79k4+c7j1hsIiCynuORpRG2Y3PR3jkPXsZSqsZLF8zxGXL1mXm8ipatqz1caJ/+wrvtHebllInPkcljaL1pAzHf7NMgZ2Wp7FechWOiNwOnAQcKCKbgMXASSIyh8Ck9jTwFwCqul5ElgGPA7uAi1V1zF3nPcBKoA+4RVXXl/40FcdXCC9fM8TCr66bMNFs6/bR8VndvsPyoeERbn34Z+P7y8pk4PMcjSiNIo3PR4GXITzKUFplJ64Mv0NaLq9wTlGR+2XNqI+v2NnoiC5LmPrMsi8qpIso3laPWDstT2O9+ESpna+qB6lqTVUPUdXPq+o7VHW2qv6uqp4RGe2gqtep6itV9QhV/dfI9ntV9dVu33XNeqAq4xstdNXd6xNnNY+OaWbUk08SxjImEPo8RyM9Nt8IO9/JrY1GfS1fM8SUnHxZPtcoO1dW3vcWd58i98uaUR8VzFnzbnzzl+W9u3CWfdHzs64bRuLddO4cAC5dupY5V93H3Kvvm1DmZkaQJlGV6M1mY5kGSiRvcp2PEF6+Zih1RnfWNfL21XNc0fOj2xuZeOfb+IqE+yaFyMbDfpPIGkUUEQjNSFyZ9R2Tsm/73C9vRn2U+KRVoJBSTfrOcbLKW6+Qjiv/4ZFRtm4fHS/zwq+uY+7V93GpS/dz07lzvNaKaiRZaKsVXLuwXGolEPe3QLKZKW3YPEVkvHJetmxd5r2yBLbvhLNGh+k+w/9GTBK+po8io6iouavR5Q0gSGpZRCCklXVoeKTuXFlp36FPJDdlftFrZtWZ6PLJcaJKLu17xtuOb3nr9ZvljQxHd+uEPGi+axs1GujSrCkSVcJGOCn49lbCipbUYOI9yrQe3ZgqC7+6joVfm+z8jRK3o8fx6TGWMUz36Vk22mOL957Too6SyFOoZSxvsFu1kHDIKlO9prW073DjOUenTmjNezdFRw3R0UIaofBNGvksmDvI2sWneuVyS8KnnsQpOsL3GRm2eumFTsUUTgJF7O1X3b0+s7cUOm6js5WT1s9ISu4XRQRueOvRqRFtJy55gEuXrmXPqVPGG294n/B3WcN0X2VSjzAoQr0mlazRRrxzUVZOrqzOQL2CKes71PtuinYUfPyGfSK5wjhplj3A9p27Ss/nV88IP9qO6zWXGyCa0aNuN/PmzdNVq1a1/L5pM8/j+Z+WrxlKTeueRn+tz3t1xeg5aY0+acJYbYqAMEGB1aYI++41leHto12zhG29oeNp3zck9H8MurDy+FonWd8jr7xp9UVgfBmAsmjmcs5ZZrQoWfVdCPwj0bT+O3eNsT024bHe9x0va3SqQPyb5hH3icXL5CszWoWIrFbVeS2/cQ6mcBI4bNE9icsdQ1CBwoabJ7jSyLKxJx174zlHA8m26nrL0GgjbjdJitb3mZLOTaO/1lfK4md5ArpdgqkI0WdIWxI8Slh30557oL/Gjl27J3yHtOvmvZ805ZpWT6LfdP/+GiIwvH10fC2baGfNp0yN1MdmUFWFY0EDCWQ536POwHqHy2Oqk3p+SaOSsMICqQ7JestQ1izmslKTDA2PjCviUKlDukO4kXk+0WvkKeuR0TEefGJLQ8rAR8GFpqNQSDZ7AmnRe8Sfwae7NKbKpUvXsn9/jVqfTKrbSYvgpV03q55nOezT6knWN42/m7Q6Ep+DFd6vlRN/Ow0b4STgIyBCp2w9o4tQoPom98sartdbBmjcjNNory7rPScp4KipK+2Ziz6Tzwix0ffkOwoNe95lmfDSqOe71TuSDkky6V66dK2X4oLsEU5W+yijnlTNXOaDjXA6CJ8e8DPDI9x07hxv00xI6LgtktwvyyF5wQkzJ2QTKML+/bXUmeg+PeBGU9BkOZyTJh9GMxOnmTnqcebnfcNGw8h9R6Ejo2Pc/sjGzDQ19RD/ltt37ir83Rp1fo/uVlQnCvm09pXkL6lngbKy6kkZyV2NAFM4MeKVZlpGXqn4MHpKjm/Gx6mdVGnThvX799e4c3V6BE+fCLtVE+3StSnCCzt3jTvco2YISDfhRcvuO5E1rRE2IsTClSPjbztqmvIh3rkoKux8KLIgV73zZ9JIMjelkXWPIs+QRpg5IHznaYK8qM+s6HwkIXuFzTg+5rKyE852K2ZSi1Ak4qvWJ+PRNKEiyTIRTNu7lhshluXgjJtZalOCxpQwEBg/L2oiiQr+/ftrbBsZTXWEQrJgipsQ8kwNeaabRs00kLwKZSMmqGb0UpPeQ1rPO01I1mu+KfKOs+6R9Qxx31uR4Igy3ndaPcsatT5dckRg1cxuZlLrAJJMPKO7lYH+GvvsOXVcWP/6xVFGRycn1tx7jz5e2JlcyX1mLmc5OK8/a/YEhfHCzl3szlguIy5wQxNemBi0HudsfF+eqSHP5JZlzkpS9HFC5RhXOPWaoOpxpPscn9RDTgu3TvPhFB1l+YYt+96jqFM8Lfw7afmCMuaFJZUtKzqunmWms7B5OH6YwomQlUdq7eJTgaAnk5RVYHRM2TXm58tJE4hZlTbaMNPKEDIYMffFSUvOGHJwxggnbvfOE0J5jTBuzkqLUssydV2aItjCiXrNWoOn6FouSYJ13qEHJL67tO2++AS9RDtRvvfwVQ4L5g5y1d3rc5c4KJO0siVZLNJMyY0onV7J9twopnAi+FSarB5LEeNk0nV8K21WGep1sIaE5/rmQcsSQj7PkyfE8oIYsnrxzVyDp9G1XML9aSOiRoRf3uz//lofV57R3KWLF7/5qLYtSBeS1CHavnPXJEVYxhSBMpcz6ObgA1M4EXwqTZbztMiEzqSej2+lzSrD2cdkC6uscwf6axPObbTSl9kI04RwXpRZo2vwDA2PMGvRPRMm/GYdX+SezSKrbPHnaBZpZsQbVj7JpUvXtkyQxuvNYYvuSTyukaCMqKl7r9qUhrJ5dHvwgSmcCD526oXzj5i0OBoEiTXPPXaGV8oMAU4+cvqEbWHFHRkdm2Raile0rDkMDz6xJfW+y9cM8cKOyYtrheW/8oyjxv9vpm29kesm9f5C/5bPBL008qKw4g0/7/h22u7TytYMh30W0TpUliD1KXPWMWWavuLPNDwySn+tj5vOnVNoekGURqcaVB2LUquDpBxeb/rdg3jwiS0TfBFZRCOp6pmINyulp5Y2oS3Lru+bg6zd1Bv15hMp5JvuJhqBlxWV2MroJJ9cYUlRi61MxVJGFJdPmfOOKfO5G43SDMvrk9Wg6OTjqkapWbboAkSzMu+z51Q+ee4cnl7yJha/+SjuXD00XlnGVEleH/IlRkbHuGzZOg5bdA+XLVtXOLV50fTzaXb9wYF+1nz0VG/Her0LTJVBXgr4RlZNXDB3cDyNUBbRgIesLkVS5ulmkJTZ/M7VQ5x9zGBmxudWp9MvGsWVVNd8ypx3TPid87Jh+9T1vGfKK0vSt0uTG90SfGAmNU+K5mtKm5gYJRwF1TPZr6h/pNGwzSrYlotEvdVjJsoLQoCJDT8rdQo09x3lLXqWl/+t1WG8RUxZaXUtbfQZLXOWL+6Vl9/LmCp9Ipx//AyuXZDcwUi6/6VL13LJ0rUTzNxZE7Kz5j9lKaQkudFNS03bCMeTtN7KZcvWpVasRo2VeWuzF1m3pNF1XaqwwJTPMyyY29gaPCcfOT21lxlv+D4L3o2MjnHlivWFypCHz6JnPit7+mwva1RbZPSZVteS1pGKlzmrPkc7eLc+/DOuWP5Y4nFpigDcEtRfW8cVyx9L9IeGYdc+nZa0bxTmC+zGpaZzFY6I3CIivxCRH0a2HSAi94vIj93vaW67iMinRGSDiPxARF4bOedCd/yPReTC5jxO80irHFm+msGB/lTTVx6+i2X5CtdGzE1QjYltScqgyDPkCc/la4a4c/VQagaGpMm0UaWfxvDIKHOvvi9XWPsKd59Fz8pY2bPIQoR5FOkgZbW1eJmFieZLn05AyO2PbEzcnlenR8cChRWfCzdt7xr77jU1eyFFXpp6kPaNQh9QsxYubCc+I5wvAG+MbVsEfFtVDwe+7f4HOA043P1cBHwWAgUFLAaOB44DFodKqlMoakMNG2+RBhAybe/aJMdio73MoiOiOGWtfFkvacpgSp6zLHJ+nvDM8nOlNfyo0s/qXITzc/KWKvcR7nkCsayVPcse1fp2kLIEcVhmmGh6ipov48+VRryzGLazei0Te+8RZMPOQnnJvNpoJ7ATyfXhqOp3RGRWbPOZwEnu7y8CDwEfctu/pEHo28MiMiAiB7lj71fV5wBE5H4CJXZ7w0/QInyyCockhTMXWXHwxciKh2X6ThoJdS5zTk09pCmDF3aOeb0Pn3DTRpORLpx/ROYKsEUnkqYdnxXNVGSeTV59aNeoNquuhWVO8pGE7yuuzELfTZw+kcKLymUxNDyC5FxEYDyBaTOmDVSdeoMGXq6qm93fPwde7v4eBKLj1E1uW9r2jiFeOdIyQ0cTGEYnuMUduGH6kjSnbyhoqhKX3+7G0ehESx/hmefYzlP+WSldipQjb3uaQC7b1t+udC0+da3I+zr/+BmJS3ic8IpphReVyyNvlonChLpaxny3TqLhKDVVVREpbTKPiFxEYI5j5syZZV22EGm92KyJbBA0+pOPnJ47IoleP41wX1ballbTzsbR6ERLH+HZaDJSSE7pknY/n/JlrVnUbOXfqlFtVntLo4gyDKPRwrWGwii1B5/Y4mWxqE2RzPyDRenlhJ71KpxnReQgVd3sTGa/cNuHgBmR4w5x24Z4yQQXbn8o6cKqejNwMwQTP+ss3wSKzPb1NWGlNfo8oeQ7wTBsOGmTSNMidrqVhfOPYOHX1qU6ZH2c5HnCM0uQL18zlKrwkpYavvyuHzAyOjGdd5awPvnI6Ym98Od3pCea9J071UiYODRXsdVrMs76nknPfO2C2ZPCoNPS3EQZ6K8BkzOSN0K3zKmph3oVzgrgQmCJ+/2NyPb3iMgdBAEC25xSWgl8LBIocCpwef3F9qdoVt8iJqykRp+WvfiZ4RGWrxnismXrvLIQhIIp7VjfnG1dRcbaPz6rL0aXeEgTnknfNKxDaezvhNJEZNJ/WXnu0lISjcV61kXMqWX4/xod1eYpPJ/2lnWN+HbwWzwQ/BaViy9c2Kifp9uDAvLIVTgicjvB6ORAEdlEEG22BFgmIu8Gfgqc4w6/Fzgd2ABsB94FoKrPicg1wPfccVeHAQTNpmhW30YdpVmmkcvveixTUYg7P9qg0iYX1htu7UMVs9WmLavQJzLJd5EmaK8/a3Zd6WbywpBfiK0ymjaPIyvPXREzi++x7fb/+Si8vPbm4zeLcuKSB7yf2ScQKD6i9pnQHaU2Rdh3r6kNJfTsJnyi1M5P2fX6hGMVuDjlOrcAtxQqXQP4LEAVTsqLCteBlCWls2zpUdKG+iJkVuy0nFKtjg6rQkaBJNIE025V71HqZcvWAcWfw2deRtFotzhFlnBOM8lE63xWPr9W+RB8FF6eL6ao0sx692mjXh+rQxQleaVZgL1rU9iz1mcKJoWuzDTgMxM7ZHhkdMLch9+8uIta30RzSHT2cN4cibT5DVnx+VkKpNH5M0WpQkaBJIrMA8qaOFjPxEUfm3s82q3odZLmZIRLmUdJqytXLH+MS5eunZDPL41W+RB8FG/eXJSiyjvt2UILQ7wNA9x4ztGJZZi2d5KpNGiDaxefyifPnTOhXX7y3Dk8fs1prPnoqV05abMMujKXms9M7DTiS0pnLdoUHx0lRbNFy5SmAOPJBePErxdOUGuGyasKGQWSKDLSSxulQn0mpbyABciPdgPYHjO9RcnySUQzk+9Vm9xHXL5miNse/pmXmaeVUWZZoxffdWR8QtWj9571sn6ecUolRIDRsd2pHanQspDnD4KJ76/XQprLoCuXJzhs0T0NOfbiqcB9r5c1F8InOi3P3htfFsHnvkUpI418SNm+IN+1UN6/bC1ZUaxFUr2nvfMoSe8/7TwBLjhhZmriyKT75825yUoUGWWgv4YIpZp7ssoHyQL77GMGc5dPCK+d9u7DJUF81p/KIq8uVNGf6UNVlyfoyhFOEXt42vn1XC+r9+wzcXR0t473zOO+kyyFVaYjuBGfUbzXGo3wKcMX5NOjvOru9ZnKBvxNSj6dhLSZ/eGk3biwVOC2h3/GvEMP8HoPjWRIiDLQX2PHrt2l++ayypc2cvCNTMt691u3j3qP6rLIqws2iimXrvTh1JO/LKTWJ5OEa5HrZTX+BXNfyiW122NkGTW15ZkJyzJ51esziucCGx4ZnWSCaoUvKGuWPxQzKeW9c4FMO31WNmDf9+CbISGLtKCVMr6Hz5IR8fxpeY79E5c8wCVL1+aOXBpVNr0eotwOulLhAOw5tb5H22ePqZOG9aHgCSdbDg70pzoUfXvPvseFjbPedPP1kCQk8vD1m7XTF1Q04KLRd56133eBNp8AhKwOUV7QSqPfo54ACR/HfjMY6K9N6EidfUwwCm3XgoK9SNcpnLCnXe/M4G2R8+LRbmF69IXzj2Dxm49qKNOr76gpbJxZDTjvvq1YqbPIPKVmMpA4CTPYXjRqqJF3DsE3zsoH4ZPu3yejcHRUCkzoGEUd+EkUMS8m1aF6Mh6nnZM3daAR+mt9XHnGUeMdqYXzjxhfpbfRpRcMf7pO4TQSoQYTG2CerbmRcOX4+QP9tcwQ2DQFFV/KIE6Za5pk4SO4WmHCuPKMo6hNmRzWfuUZRxW6zvI1Q4kLbEH+Ow9ZMHeQ//XKAzKPyTNr+dazBXMHx+tI6BuMfutGUuFn1aF62kE9Uwd8mLZ3jbefMDNR8bZ7iW0joOui1BqJUAtnEIc9w0uXrk28VpEopyIkRcQAExzxRaOMyow6yyt73MnbrlnWjUYWpTmsp+1dS0yHlIVPBFlZ9SnvW9f7XlpVh/Le1dtPmMmDT2zJXJrBtzxpcqJZbbvVWJRai/CJKOsT4cZzjgZemh+TtJjT3nv08cLOyaOlZpmFkubbRAXf8Mgo/bU+bjp3TsN+iLJ9Ka1I9FikLGVHXkGwwFbZWQqgvPrk48APIx7D5TNuWPlk7ndqVR3K6uQNDvSPh5KnKYvQL+ZT/9LkxF61KePr54RZpX1D2I18uk7h+ORHiqdDSUptkXZ+UhRbsygjF1Yr1zTplhDSMgVsXgeoTDOjz7euJ3VRq+rQgrmDrPrpc5PCncNlP0JlkrYWVbjcNOQ/V5KcmAITMnyPqY5n8DalUw5d58OJO1CTiC+oVSSPUjyKrZmUIfh6cRnbRilzOe2k9x96mMpOU+TzrevxXbSyDl27YDY3xVLGhBNFQx9SmrKJb816riQ/UlqEx+2PbEzeYRSm60Y4MNF0UHRBrTy2lbguRh5l9Cxbberq1JnZUcpMmNrK9+9zr3o6Ma2uQ/GRclIGaAhM47tVM0eRec8Vvc+slPVxenIpkCbRlQonJK+hZFXGtBTkrVw8qSzB1ypTV1UzTRelbAHr8/7LUtR596q3E1NmHSr6rFmZwkMHf1rAQZH2aosdNp+uVjiQ3VDSGl/oLEzK99RKU1SVHPE+tHv9lTJppT+qlYq61ctdxGmWD6mM5zr/+BmJq66ef/yMhKONeuh6hZNFWiUN7erzDj2g7cK+kxzxVc00XXVaqajb3Ymp51kbXR7clzAw4PZHNlqUWpPounk4RekGn0O7iL+7pGUcoPz5Gt1A9N2ltcBumRMSpd75L9ZOi2HzcCpKJ40gqkSSaSRcMCyatNMi4ibjk4UaWusvbBVV8CEZ7aPrwqKN1pBkGhndreyzx9SWrU7aqfhER3arorYw/d6m50c4Rn2k+WW2jYyydvGpLS5NZ5EXHdnNJqN2+5CM9tKQwhGRp4HngTFgl6rOE5EDgKXALOBp4BxV3SoiAvwdcDqwHXinqn6/kfsb7aOVGQy6jbR31yu+LjOP9S5lmNROVtU5EQfVIuDbqno48G33P8BpwOHu5yLgsyXc22gTZhqpH3t3Rq/SDJPamcBJ7u8vAg8BH3Lbv6RBWNzDIjIgIgep6uYmlMGiWpqMmUbqx96d0as0qnAUuE9EFPgnVb0ZeHlEifwceLn7exCIJiXa5LaVrnC6ZcZ71THTSP3YuzN6SbLrfQAAAwtJREFUkUZNar+nqq8lMJddLCJ/EN3pRjOFJvqIyEUiskpEVm3ZsqWuQtniSoZhGNWjIYWjqkPu9y+ArwPHAc+KyEEA7vcv3OFDQDRHxCFuW/yaN6vqPFWdN3369LrKZTPeDcMwqkfdCkdE9hGR/cK/gVOBHwIrgAvdYRcC33B/rwD+RAJOALY1y39TZnp5wzAMoxwa8eG8HPh6EO3MVOArqvp/ReR7wDIReTfwU+Acd/y9BCHRGwjCot/VwL0zKZrIzwIMDMMwmk/dCkdVfwIcnbD9V8DrE7YrcHG99ytCkSggCzAwDMNoDV2bacA3CqibUuobhmFUma5VOL5YgEFvYeZTw2gfPa9wLEVLMTpZYJv51DDaS89ni7Y0I/6EAnvIreESCuzlayZFt1cSm59lGO2l5xXOgrmDXH/WbEup70GnC2wznxpGe+l5kxpYmhFfqi6w88x9Zj41jPbS8yMcw58qT6j1MfeZ+dQw2ospHMObKgtsH3OfmU8No72YSc3wpspp9X3NfWY+NYz2YQrHKERVBbb5Zwyj+phJzegKksx9tSnC9p27OGzRPZy45IGOCd82jG7FRjhGVxA39+3fX+OFnbvYun0UsEmehlEFbIRjdA0L5g7y3UWn8NSSN7HPnlMZHZu49l8nzRkyjG7EFI7RlVR9zpBh9CKmcIyupMpzhgyjVzGFY3QlVZ4zZBi9igUNGF1JlecMGUavYgrH6FqqOmfIMHoVM6kZhmEYLcEUjmEYhtESTOEYhmEYLcEUjmEYhtESTOEYhmEYLUFUNf+oNiEiW4CfNnCJA4H9gOdj2ztxW9XK0+3ltmfpnG1VK0+Zz/LLhP0+HKqq0+s8t2lUOiy60RcmIquA6cBTsV2duK1q5en2ctuzdM62qpWntGdR1XkJ+zsWM6kZhmEYLcEUjmEYhtESKm1SK4Gbgd8H/j22vRO3Va083V5ue5bO2Va18jTjWbqCSgcNGIZhGN2DmdQMwzCMlmAKxzAMw2gJpnAMwzCMlmAKxzAMw2gJpnAMwzCMlvD/A7ftXmX6lrcKAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Plot the NB_CHECKINS_all and NB_CHECKINS_missing_rate columns of the join_day dataframe to see if they are correlated\n",
    "\n",
    "x = join_day['DAY'].astype('str')\n",
    "y = join_day['NB_CHECKINS_missing_rate']\n",
    "\n",
    "fig, ax = plt.subplots()\n",
    "\n",
    "ax.set_xticklabels([])\n",
    "\n",
    "ax.set_title('NB_CHECKINS_missing_rate by DAY, ordered by NB_CHECKINS_all')\n",
    "\n",
    "ax.scatter(x,y)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NB_CHECKINS_all</th>\n",
       "      <th>NB_CHECKINS_missing_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NB_CHECKINS_all</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.066216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NB_CHECKINS_missing_rate</th>\n",
       "      <td>0.066216</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          NB_CHECKINS_all  NB_CHECKINS_missing_rate\n",
       "NB_CHECKINS_all                  1.000000                  0.066216\n",
       "NB_CHECKINS_missing_rate         0.066216                  1.000000"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pearson correlation coefficient\n",
    "\n",
    "join_day_corr = join_day[['NB_CHECKINS_all', 'NB_CHECKINS_missing_rate']].corr()\n",
    "join_day_corr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The correlation between the number of checkins with missing data regarding the rate plan and the total number of checkins per day is weak. This actually makes sense, because when such anomalies occur, they probably impact most of the checkins for the day, regardless of how busy that day is. On the other hand, if a station has a high number of checkins year round, such anomalies will impact a high number of checkins, regardless of the day when the anomaly occurs.\n",
    "\n",
    "Dropping those rows would not impact our analysis of the number of checkins per stop, but it would impact our analysis per day.\n",
    "\n",
    "Let's keep these rows but re-label them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/pandas/core/generic.py:4619: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._update_inplace(new_data)\n"
     ]
    }
   ],
   "source": [
    "# Replace '?' and 'NON DEFINI' by 'NA' (Not available)\n",
    "\n",
    "metro_nb['TICKET_TYPE'].replace(['?', 'NON DEFINI'], ['NA', 'NA'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AUTRE TITRE    107995\n",
       "NAVIGO         107959\n",
       "IMAGINE R      107906\n",
       "FGT            107893\n",
       "TST            107886\n",
       "AMETHYSTE      107882\n",
       "NA              96981\n",
       "Name: TICKET_TYPE, dtype: int64"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_nb['TICKET_TYPE'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ID_REFA_LDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PORTE DAUPHINE'], dtype=object)"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify the missing ID_REFA_LDA\n",
    "\n",
    "metro_nb.loc[metro_nb['ID_REFA_LDA'].isnull(),'STOP_NAME'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ID_REFA_LDA is missing for the station PORTE DAUPHINE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DAY</th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>STOP_NAME</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>TICKET_TYPE</th>\n",
       "      <th>NB_CHECKINS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1018</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>PORTE DAUPHINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IMAGINE R</td>\n",
       "      <td>2106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3704</th>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>PORTE DAUPHINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AUTRE TITRE</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4831</th>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>PORTE DAUPHINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FGT</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4832</th>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>PORTE DAUPHINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>1337.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4833</th>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>100</td>\n",
       "      <td>110.0</td>\n",
       "      <td>682.0</td>\n",
       "      <td>PORTE DAUPHINE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TST</td>\n",
       "      <td>147.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            DAY  CODE_STIF_TRNS  CODE_STIF_RES  CODE_STIF_ARRET  \\\n",
       "1018 2018-05-09             100          110.0            682.0   \n",
       "3704 2018-01-03             100          110.0            682.0   \n",
       "4831 2018-01-07             100          110.0            682.0   \n",
       "4832 2018-01-07             100          110.0            682.0   \n",
       "4833 2018-01-07             100          110.0            682.0   \n",
       "\n",
       "           STOP_NAME  ID_REFA_LDA  TICKET_TYPE  NB_CHECKINS  \n",
       "1018  PORTE DAUPHINE          NaN    IMAGINE R       2106.0  \n",
       "3704  PORTE DAUPHINE          NaN  AUTRE TITRE         98.0  \n",
       "4831  PORTE DAUPHINE          NaN          FGT         89.0  \n",
       "4832  PORTE DAUPHINE          NaN       NAVIGO       1337.0  \n",
       "4833  PORTE DAUPHINE          NaN          TST        147.0  "
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_nb[metro_nb['ID_REFA_LDA'].isnull()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see if we can replace it using ref_stops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "474152"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_lda = ref_stops.loc[ref_stops['LDA_NOM'] == 'PORTE DAUPHINE', 'LDA_ID_REF_A'].unique()[0]\n",
    "\n",
    "pd_lda = pd_lda.astype(int)\n",
    "\n",
    "pd_lda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/pandas/core/indexing.py:537: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "# Replace missing values by 474152\n",
    "\n",
    "metro_nb.loc[metro_nb['STOP_NAME'] == 'PORTE DAUPHINE', 'ID_REFA_LDA'] = pd_lda"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NB_CHECKINS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.04556334301318197"
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Percentage of rows with missing number of checkins\n",
    "\n",
    "perc_missing = (len(metro_nb[metro_nb['NB_CHECKINS'].isnull()]))/(len(metro_nb))\n",
    "\n",
    "perc_missing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing values in the NB_CHECKINS column account for 4.5% of all rows.\n",
    "\n",
    "Let's see if these missing values correspond to data from a particular column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DAY            184\n",
       "ID_REFA_LDA    301\n",
       "TICKET_TYPE      7\n",
       "dtype: int64"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_nb.loc[metro_nb['NB_CHECKINS'].isnull(), ['DAY', 'ID_REFA_LDA', 'TICKET_TYPE']].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These missing values are distributed among all ID_REFA_LDA and all TICKET_TYPE unique values.\n",
    "\n",
    "However, we see that these anomalies only occured during the 2nd semester of 2018.\n",
    "\n",
    "Earlier, we saw that data from the 1st semester had strings indicating 'moins de 5' (=less than 5). When there were 0 to and incl. 4 checkins, the STIF did not provide the exact number to prevent the tracking of a particular individual. We replaced these values by 4 in order to cast the column to numeric. \n",
    "\n",
    "It is possible that the same logic was applied for the 2nd semester, but that it is indicated by NaNs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Percentage of rows with a number of checkins less than 5\n",
    "\n",
    "perc_less5 = len(metro_nb.loc[metro_nb['NB_CHECKINS'] == 4])/len(metro_nb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Almost 4% of rows have less than 5 checkins. It seems in line with our hypothesis. \n",
    "\n",
    "To confirm it, let's see if there were NB_CHECKINS values that were less than 5 in the dataset for the 2nd semester (nb_2018s2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(nb_2018s2[nb_2018s2['NB_CHECKINS']<5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14329"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(nb_2018s2[nb_2018s2['NB_CHECKINS']==5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "751027"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(nb_2018s2[nb_2018s2['NB_CHECKINS']>5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that there were no rows with NB_CHECKINS less than 5 in the data from the 2nd semester. This confirms that the NaNs correspond to values that are less than 5. Let's replace them by 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "metro_nb['NB_CHECKINS'] = metro_nb['NB_CHECKINS'].fillna(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 744502 entries, 0 to 883141\n",
      "Data columns (total 8 columns):\n",
      "DAY                744502 non-null datetime64[ns]\n",
      "CODE_STIF_TRNS     744502 non-null int64\n",
      "CODE_STIF_RES      744502 non-null float64\n",
      "CODE_STIF_ARRET    744502 non-null float64\n",
      "STOP_NAME          744502 non-null object\n",
      "ID_REFA_LDA        744502 non-null float64\n",
      "TICKET_TYPE        744502 non-null object\n",
      "NB_CHECKINS        744502 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(4), int64(1), object(2)\n",
      "memory usage: 51.1+ MB\n"
     ]
    }
   ],
   "source": [
    "metro_nb.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are no missing values left in metro_nb."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.g. Drop columns <a name=\"2Bg\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"\n"
     ]
    }
   ],
   "source": [
    "# List of columns to drop\n",
    "col_todrop = ['CODE_STIF_TRNS', 'CODE_STIF_RES', 'CODE_STIF_ARRET']\n",
    "\n",
    "# Drop columns\n",
    "metro_nb.drop(columns=col_todrop, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.C. Export to .csv <a name=\"2C\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_nb = metro_nb.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_nb.to_csv('../../datasets/metro_nb.csv', index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Hourly profiles <a name=\"3\"></a>\n",
    "\n",
    "\n",
    "# 3.A. Description & steps <a name=\"3A\"></a>\n",
    "\n",
    "hp_2018s1 and hp_2018s2 provide the percentage of checkins per hour (hourly profile), per station (LDA) and per day category, for the 1st and 2nd semesters of 2018 respectively.\n",
    "\n",
    "As a reminder, here are the steps we identified in order to clean these dataframes:\n",
    "\n",
    "> Rename columns (translate to english, except columns that contain IDs)\n",
    "\n",
    "> Handle misdesignated missing values ('ND', '?')\n",
    "\n",
    "> Cast the ID_REFA_LDA column of pct_2018s2 to numeric\n",
    "\n",
    "> Cast the CODE_STIF_RES & CODE_STIF_ARRET to numeric for both dataframes\n",
    " \n",
    "> Concatenate nb_2018s1 & nb_2018s2 into a new dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3.B. Cleaning <a name=\"3B\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2728: DtypeWarning: Columns (1,2) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "hp_2018s1 = pd.read_csv('../../datasets/validations-sur-le-reseau-ferre-profils-horaires-par-jour-type-1er-sem.csv', sep=';')\n",
    "\n",
    "hp_2018s2 = pd.read_csv('../../datasets/validations-sur-le-reseau-ferre-profils-horaires-par-jour-type-2e-sem.csv', sep=';')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.B.a. Rename columns <a name=\"3Ba\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ True,  True,  True,  True,  True,  True,  True,  True])"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make sure that column names are the same\n",
    "\n",
    "hp_2018s1.columns == hp_2018s2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = {'LIBELLE_ARRET': 'STOP_NAME', 'CAT_JOUR': 'CAT_DAY', 'TRNC_HORR_60': 'HOUR', 'pourc_validations': 'PCT_CHECKINS'}\n",
    "\n",
    "hp_2018s1.rename(cols, axis=1, inplace=True)\n",
    "hp_2018s2.rename(cols, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CODE_STIF_TRNS', 'CODE_STIF_RES', 'CODE_STIF_ARRET', 'STOP_NAME',\n",
       "       'ID_REFA_LDA', 'CAT_DAY', 'HOUR', 'PCT_CHECKINS'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 225,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.B.b. Type conversion <a name=\"3Bb\"></a>\n",
    "\n",
    "#### Cast the ID_REFA_LDA column of pct_2018s2 to numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['?', nan], dtype=object)"
      ]
     },
     "execution_count": 226,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify non numeric values \n",
    "\n",
    "non_num = hp_2018s2[~hp_2018s2['ID_REFA_LDA'].astype('str').str.isnumeric()]\n",
    "\n",
    "non_num.loc[:,'ID_REFA_LDA'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are '?' and NaNs in this column. This is problematic because this is the column we will be using to link this dataset to other datasets.\n",
    "\n",
    "Let's look into it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "?      1070\n",
       "NaN     118\n",
       "Name: ID_REFA_LDA, dtype: int64"
      ]
     },
     "execution_count": 227,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_num['ID_REFA_LDA'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These missing values account for 1.4% of all values in pct_2018s2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>STOP_NAME</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CAT_DAY</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>PCT_CHECKINS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2698</th>\n",
       "      <td>800</td>\n",
       "      <td>800</td>\n",
       "      <td>1206</td>\n",
       "      <td>DUGNY COURNEU</td>\n",
       "      <td>?</td>\n",
       "      <td>SAVS</td>\n",
       "      <td>6H-7H</td>\n",
       "      <td>4.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2699</th>\n",
       "      <td>800</td>\n",
       "      <td>800</td>\n",
       "      <td>1206</td>\n",
       "      <td>DUGNY COURNEU</td>\n",
       "      <td>?</td>\n",
       "      <td>SAVS</td>\n",
       "      <td>7H-8H</td>\n",
       "      <td>6.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2761</th>\n",
       "      <td>100</td>\n",
       "      <td>ND</td>\n",
       "      <td>ND</td>\n",
       "      <td>Inconnu</td>\n",
       "      <td>?</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>13H-14H</td>\n",
       "      <td>6.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2762</th>\n",
       "      <td>100</td>\n",
       "      <td>ND</td>\n",
       "      <td>ND</td>\n",
       "      <td>Inconnu</td>\n",
       "      <td>?</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>16H-17H</td>\n",
       "      <td>9.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2763</th>\n",
       "      <td>100</td>\n",
       "      <td>ND</td>\n",
       "      <td>ND</td>\n",
       "      <td>Inconnu</td>\n",
       "      <td>?</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>20H-21H</td>\n",
       "      <td>5.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      CODE_STIF_TRNS CODE_STIF_RES CODE_STIF_ARRET      STOP_NAME ID_REFA_LDA  \\\n",
       "2698             800           800            1206  DUGNY COURNEU           ?   \n",
       "2699             800           800            1206  DUGNY COURNEU           ?   \n",
       "2761             100            ND              ND        Inconnu           ?   \n",
       "2762             100            ND              ND        Inconnu           ?   \n",
       "2763             100            ND              ND        Inconnu           ?   \n",
       "\n",
       "     CAT_DAY     HOUR  PCT_CHECKINS  \n",
       "2698    SAVS    6H-7H          4.39  \n",
       "2699    SAVS    7H-8H          6.33  \n",
       "2761   DIJFP  13H-14H          6.29  \n",
       "2762   DIJFP  16H-17H          9.82  \n",
       "2763   DIJFP  20H-21H          5.44  "
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_num.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that some of the '?' are on the same rows as 'Inconnu' stop names. Just like we did for nb_checkins, we will drop the rows that have an 'Inconnu' stop name as we filter on CODE_STIF_RES. So let's ignore those for now and look at the other STOP_NAMEs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "EPINAY VIL T11     120\n",
       "ROSA PARKS         120\n",
       "BOURGET T11        120\n",
       "STAINS CERISAIE    120\n",
       "EPINAY SEIN T11    120\n",
       "VILLETANEUSE UN    120\n",
       "PIERREFITTE T11    119\n",
       "PORTE DAUPHINE     118\n",
       "DUGNY COURNEU      117\n",
       "Inconnu            114\n",
       "Name: STOP_NAME, dtype: int64"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_num.loc[:, 'STOP_NAME'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Considering there are 24h per day and 5 types of days (5\\*24=120), we can conclude that ID_REFA_LDA is missing for all the rows of these stops."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['DUGNY COURNEU',\n",
       " 'EPINAY SEIN T11',\n",
       " 'BOURGET T11',\n",
       " 'EPINAY VIL T11',\n",
       " 'VILLETANEUSE UN',\n",
       " 'PIERREFITTE T11',\n",
       " 'STAINS CERISAIE',\n",
       " 'ROSA PARKS',\n",
       " 'PORTE DAUPHINE']"
      ]
     },
     "execution_count": 230,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_lda = non_num.loc[non_num['STOP_NAME']!='Inconnu', 'STOP_NAME'].unique().tolist()\n",
    "\n",
    "missing_lda"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see if these names appear in the other dfs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PORTE DAUPHINE'], dtype=object)"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if there are matches in metro_nb\n",
    "\n",
    "metro_nb.loc[metro_nb['STOP_NAME'].isin(missing_lda), 'STOP_NAME'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PORTE DAUPHINE', 'ROSA PARKS'], dtype=object)"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if there are matches in ref_stops\n",
    "\n",
    "ref_stops.loc[ref_stops['LDA_NOM'].isin(missing_lda), 'LDA_NOM'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that only PORTE DAUPHINE is a valid metro stop name. ROSA PARKS is also a valid station name, but it doesn't have a metro line, therefore it doesn't appear in metro_nb. See: https://en.wikipedia.org/wiki/Gare_Rosa-Parks\n",
    "\n",
    "The ID_REFA_LDA for PORTE DAUPHINE was also missing in metro_nb. We can fill it with the correct value.\n",
    "\n",
    "Let's check if all the other stop names have a valid CODE_STIF_RES and whether it is 110."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Inconnu    114\n",
       "Name: STOP_NAME, dtype: int64"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check that 'Inconnu' is the only STOP_NAME with a CODE_STIF_RES that is 'ND' and that other STOP_NAMEs have a valid CODE_STIF_RES\n",
    "\n",
    "non_num.loc[non_num['CODE_STIF_RES']== 'ND', 'STOP_NAME'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PORTE DAUPHINE'], dtype=object)"
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check which STOP_NAMEs have a CODE_STIF_RES of 110\n",
    "\n",
    "non_num.loc[non_num['CODE_STIF_RES']== '110', 'STOP_NAME'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PORTE DAUPHINE is the only STOP_NAME that we will want to keep in the final dataframe. We can drop all the other rows now to be able to cast the column to numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace ID_REFA_LDA for PORTE DAUPHINE in both dataframes\n",
    "\n",
    "hp_2018s1.loc[hp_2018s1['STOP_NAME'] == 'PORTE DAUPHINE', 'ID_REFA_LDA'] = pd_lda\n",
    "hp_2018s2.loc[hp_2018s2['STOP_NAME'] == 'PORTE DAUPHINE', 'ID_REFA_LDA'] = pd_lda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop rows that contain '?' in ID_REFA_LDA (including rows that have 'Inconnu' as STOP_NAME)\n",
    "\n",
    "hp_2018s2 = hp_2018s2.loc[hp_2018s2['ID_REFA_LDA'] != '?']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cast ID_REFA_LDA to numeric\n",
    "\n",
    "hp_2018s2['ID_REFA_LDA'] = pd.to_numeric(hp_2018s2['ID_REFA_LDA'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cast the CODE_STIF_RES & CODE_STIF_ARRET to numeric for both dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Identify non numeric values\n",
    "\n",
    "def check_num(df, col):\n",
    "    non_num = df.loc[~df[col].astype('str').str.isnumeric(), col].unique()\n",
    "    return non_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ND'], dtype=object)"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_num(hp_2018s1, 'CODE_STIF_RES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ND'], dtype=object)"
      ]
     },
     "execution_count": 240,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_num(hp_2018s1, 'CODE_STIF_ARRET')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 241,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_num(hp_2018s2, 'CODE_STIF_RES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check_num(hp_2018s2, 'CODE_STIF_ARRET')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since 'ND' indicates missing data as we saw before, we can simply convert the columns with pd.to_numeric and errors='coerce' to replace 'PD' by np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cast all these columns to numeric\n",
    "\n",
    "hp_2018s1['CODE_STIF_RES'] = pd.to_numeric(hp_2018s1['CODE_STIF_RES'], errors = 'coerce')\n",
    "\n",
    "hp_2018s1['CODE_STIF_ARRET'] = pd.to_numeric(hp_2018s1['CODE_STIF_ARRET'], errors = 'coerce')\n",
    "\n",
    "hp_2018s2['CODE_STIF_RES'] = pd.to_numeric(hp_2018s2['CODE_STIF_RES'], errors = 'coerce')\n",
    "\n",
    "hp_2018s2['CODE_STIF_ARRET'] = pd.to_numeric(hp_2018s2['CODE_STIF_ARRET'], errors = 'coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 82744 entries, 0 to 82743\n",
      "Data columns (total 8 columns):\n",
      "CODE_STIF_TRNS     82744 non-null int64\n",
      "CODE_STIF_RES      82629 non-null float64\n",
      "CODE_STIF_ARRET    82629 non-null float64\n",
      "STOP_NAME          82744 non-null object\n",
      "ID_REFA_LDA        81696 non-null float64\n",
      "CAT_DAY            82744 non-null object\n",
      "HOUR               82744 non-null object\n",
      "PCT_CHECKINS       82744 non-null float64\n",
      "dtypes: float64(4), int64(1), object(3)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "hp_2018s1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 82462 entries, 0 to 83531\n",
      "Data columns (total 8 columns):\n",
      "CODE_STIF_TRNS     82462 non-null int64\n",
      "CODE_STIF_RES      82462 non-null int64\n",
      "CODE_STIF_ARRET    82462 non-null int64\n",
      "STOP_NAME          82462 non-null object\n",
      "ID_REFA_LDA        82462 non-null int64\n",
      "CAT_DAY            82462 non-null object\n",
      "HOUR               82462 non-null object\n",
      "PCT_CHECKINS       82462 non-null float64\n",
      "dtypes: float64(1), int64(4), object(3)\n",
      "memory usage: 5.7+ MB\n"
     ]
    }
   ],
   "source": [
    "hp_2018s2.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that there are missing values in hp_2018s1 for the columns CODE_STIF_RES, CODE_STIF_ARRET and ID_REFA_LDA."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### HOUR: Replace 'ND' by np.nan and cast to numeric"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To be able to work with hours more easily, we can change them to floats. For instance, 1H-2H would be 1.0. By doing so, we would not lose any information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [],
   "source": [
    "hp_2018s1['HOUR'].replace('ND', np.nan, inplace=True)\n",
    "hp_2018s2['HOUR'].replace('ND', np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['11H-12H', '13H-14H', '14H-15H', '15H-16H', '18H-19H', '1H-2H',\n",
       "       '21H-22H', '5H-6H', '6H-7H', '20H-21H', '2H-3H', '9H-10H',\n",
       "       '22H-23H', '23H-0H', '8H-9H', '0H-1H', '12H-13H', '16H-17H',\n",
       "       '3H-4H', '4H-5H', '7H-8H', '10H-11H', '19H-20H', '17H-18H', nan],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1['HOUR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [],
   "source": [
    "hp_2018s1['HOUR'] = hp_2018s1['HOUR'].str.extract('(\\d+)H', expand=True).astype(float)[0]\n",
    "hp_2018s2['HOUR'] = hp_2018s2['HOUR'].str.extract('(\\d+)H', expand=True).astype(float)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([11., 13., 14., 15., 18.,  1., 21.,  5.,  6., 20.,  2.,  9., 22.,\n",
       "       23.,  8.,  0., 12., 16.,  3.,  4.,  7., 10., 19., 17., nan])"
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1['HOUR'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.B.c. Filter <a name=\"3Bc\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp_s1 = hp_2018s1.loc[hp_2018s1['CODE_STIF_RES'] == 110]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp_s2 = hp_2018s2.loc[hp_2018s2['CODE_STIF_RES'] == 110]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 252,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp_s1['ID_REFA_LDA'].nunique() == metro_hp_s2['ID_REFA_LDA'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The fact that these 2 dfs have a different number of rows and yet have the exact same number of unique ID_REFA_LDA indicates that for some stops, some CAT_DAY or HOUR are missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp_s1['CAT_DAY'].nunique() == metro_hp_s2['CAT_DAY'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp_s1['HOUR'].nunique() == metro_hp_s2['HOUR'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n",
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "metro_hp_s1['CODE_STIF_RES'] = metro_hp_s1['CODE_STIF_RES'].astype('int')\n",
    "metro_hp_s1['CODE_STIF_ARRET'] = metro_hp_s1['CODE_STIF_ARRET'].astype('int')\n",
    "metro_hp_s1['ID_REFA_LDA'] = metro_hp_s1['ID_REFA_LDA'].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 35237 entries, 0 to 82733\n",
      "Data columns (total 8 columns):\n",
      "CODE_STIF_TRNS     35237 non-null int64\n",
      "CODE_STIF_RES      35237 non-null int64\n",
      "CODE_STIF_ARRET    35237 non-null int64\n",
      "STOP_NAME          35237 non-null object\n",
      "ID_REFA_LDA        35237 non-null int64\n",
      "CAT_DAY            35237 non-null object\n",
      "HOUR               35233 non-null float64\n",
      "PCT_CHECKINS       35237 non-null float64\n",
      "dtypes: float64(2), int64(4), object(2)\n",
      "memory usage: 2.4+ MB\n"
     ]
    }
   ],
   "source": [
    "metro_hp_s1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 35175 entries, 0 to 82679\n",
      "Data columns (total 8 columns):\n",
      "CODE_STIF_TRNS     35175 non-null int64\n",
      "CODE_STIF_RES      35175 non-null int64\n",
      "CODE_STIF_ARRET    35175 non-null int64\n",
      "STOP_NAME          35175 non-null object\n",
      "ID_REFA_LDA        35175 non-null int64\n",
      "CAT_DAY            35175 non-null object\n",
      "HOUR               35168 non-null float64\n",
      "PCT_CHECKINS       35175 non-null float64\n",
      "dtypes: float64(2), int64(4), object(2)\n",
      "memory usage: 2.4+ MB\n"
     ]
    }
   ],
   "source": [
    "metro_hp_s2.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.B.d. Join <a name=\"3Bd\"></a>\n",
    "\n",
    "This time, we don't want to join those dataframes vertically, but horizontally. \n",
    "\n",
    "For each ID_REFA_LDA, CAT_DAY, and HOUR, we want the PCT_CHECKINS for s1 and the PCT_CHECKINS for s2.\n",
    "\n",
    "In order to merge these dataframes, we need a unique ID for each line, that is a combination of ID_REFA_LDA, CAT_DAY, and HOUR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "metro_hp_s1['ID'] = metro_hp_s1['ID_REFA_LDA'].astype(str) + metro_hp_s1['CAT_DAY'] + metro_hp_s1['HOUR'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    71597DIJFP11.0\n",
       "1    71597DIJFP13.0\n",
       "2    71597DIJFP14.0\n",
       "3    71597DIJFP15.0\n",
       "4    71597DIJFP18.0\n",
       "Name: ID, dtype: object"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp_s1['ID'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/fannykassapian/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "metro_hp_s2['ID'] = metro_hp_s2['ID_REFA_LDA'].astype(str) + metro_hp_s2['CAT_DAY'] + metro_hp_s2['HOUR'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     72240SAVS17.0\n",
       "1     72240SAVS23.0\n",
       "2      72240SAVS7.0\n",
       "3    72286DIJFP11.0\n",
       "4    72286DIJFP12.0\n",
       "Name: ID, dtype: object"
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp_s2['ID'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp = pd.merge(left = metro_hp_s1, right = metro_hp_s2, how = 'outer', on = 'ID', suffixes = ('_s1', '_s2'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CODE_STIF_TRNS_s1</th>\n",
       "      <th>CODE_STIF_RES_s1</th>\n",
       "      <th>CODE_STIF_ARRET_s1</th>\n",
       "      <th>STOP_NAME_s1</th>\n",
       "      <th>ID_REFA_LDA_s1</th>\n",
       "      <th>CAT_DAY_s1</th>\n",
       "      <th>HOUR_s1</th>\n",
       "      <th>PCT_CHECKINS_s1</th>\n",
       "      <th>ID</th>\n",
       "      <th>CODE_STIF_TRNS_s2</th>\n",
       "      <th>CODE_STIF_RES_s2</th>\n",
       "      <th>CODE_STIF_ARRET_s2</th>\n",
       "      <th>STOP_NAME_s2</th>\n",
       "      <th>ID_REFA_LDA_s2</th>\n",
       "      <th>CAT_DAY_s2</th>\n",
       "      <th>HOUR_s2</th>\n",
       "      <th>PCT_CHECKINS_s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.83</td>\n",
       "      <td>71597DIJFP11.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.33</td>\n",
       "      <td>71597DIJFP13.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.44</td>\n",
       "      <td>71597DIJFP14.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.90</td>\n",
       "      <td>71597DIJFP15.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>18.0</td>\n",
       "      <td>11.28</td>\n",
       "      <td>71597DIJFP18.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>18.0</td>\n",
       "      <td>9.68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CODE_STIF_TRNS_s1  CODE_STIF_RES_s1  CODE_STIF_ARRET_s1     STOP_NAME_s1  \\\n",
       "0              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "1              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "2              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "3              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "4              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "\n",
       "   ID_REFA_LDA_s1 CAT_DAY_s1  HOUR_s1  PCT_CHECKINS_s1              ID  \\\n",
       "0         71597.0      DIJFP     11.0             3.83  71597DIJFP11.0   \n",
       "1         71597.0      DIJFP     13.0             5.33  71597DIJFP13.0   \n",
       "2         71597.0      DIJFP     14.0             6.44  71597DIJFP14.0   \n",
       "3         71597.0      DIJFP     15.0             7.90  71597DIJFP15.0   \n",
       "4         71597.0      DIJFP     18.0            11.28  71597DIJFP18.0   \n",
       "\n",
       "   CODE_STIF_TRNS_s2  CODE_STIF_RES_s2  CODE_STIF_ARRET_s2     STOP_NAME_s2  \\\n",
       "0              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "1              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "2              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "3              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "4              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "\n",
       "   ID_REFA_LDA_s2 CAT_DAY_s2  HOUR_s2  PCT_CHECKINS_s2  \n",
       "0         71597.0      DIJFP     11.0             3.87  \n",
       "1         71597.0      DIJFP     13.0             5.38  \n",
       "2         71597.0      DIJFP     14.0             6.59  \n",
       "3         71597.0      DIJFP     15.0             7.74  \n",
       "4         71597.0      DIJFP     18.0             9.68  "
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "366"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp['CODE_STIF_TRNS_s1'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_tofill=['CODE_STIF_TRNS_s1',\n",
    "              'CODE_STIF_RES_s1',\n",
    "              'CODE_STIF_ARRET_s1',\n",
    "              'STOP_NAME_s1',\n",
    "              'ID_REFA_LDA_s1',\n",
    "              'CAT_DAY_s1',\n",
    "              'HOUR_s1']\n",
    "\n",
    "cols_data = ['CODE_STIF_TRNS_s2',\n",
    "              'CODE_STIF_RES_s2',\n",
    "              'CODE_STIF_ARRET_s2',\n",
    "              'STOP_NAME_s2',\n",
    "              'ID_REFA_LDA_s2',\n",
    "              'CAT_DAY_s2',\n",
    "              'HOUR_s2']\n",
    "\n",
    "for i in range(7):\n",
    "    metro_hp[cols_tofill[i]] = metro_hp[cols_tofill[i]].fillna(metro_hp[cols_data[i]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp['CODE_STIF_TRNS_s1'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_todrop = ['CODE_STIF_TRNS_s2',\n",
    "              'CODE_STIF_RES_s2',\n",
    "              'CODE_STIF_ARRET_s2',\n",
    "              'STOP_NAME_s2',\n",
    "              'ID_REFA_LDA_s2',\n",
    "              'CAT_DAY_s2',\n",
    "              'HOUR_s2']\n",
    "\n",
    "metro_hp = metro_hp.drop(cols_todrop, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CODE_STIF_TRNS_s1</th>\n",
       "      <th>CODE_STIF_RES_s1</th>\n",
       "      <th>CODE_STIF_ARRET_s1</th>\n",
       "      <th>STOP_NAME_s1</th>\n",
       "      <th>ID_REFA_LDA_s1</th>\n",
       "      <th>CAT_DAY_s1</th>\n",
       "      <th>HOUR_s1</th>\n",
       "      <th>PCT_CHECKINS_s1</th>\n",
       "      <th>ID</th>\n",
       "      <th>PCT_CHECKINS_s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.83</td>\n",
       "      <td>71597DIJFP11.0</td>\n",
       "      <td>3.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.33</td>\n",
       "      <td>71597DIJFP13.0</td>\n",
       "      <td>5.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.44</td>\n",
       "      <td>71597DIJFP14.0</td>\n",
       "      <td>6.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.90</td>\n",
       "      <td>71597DIJFP15.0</td>\n",
       "      <td>7.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>18.0</td>\n",
       "      <td>11.28</td>\n",
       "      <td>71597DIJFP18.0</td>\n",
       "      <td>9.68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CODE_STIF_TRNS_s1  CODE_STIF_RES_s1  CODE_STIF_ARRET_s1     STOP_NAME_s1  \\\n",
       "0              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "1              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "2              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "3              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "4              100.0             110.0               717.0  QUAI DE LA GARE   \n",
       "\n",
       "   ID_REFA_LDA_s1 CAT_DAY_s1  HOUR_s1  PCT_CHECKINS_s1              ID  \\\n",
       "0         71597.0      DIJFP     11.0             3.83  71597DIJFP11.0   \n",
       "1         71597.0      DIJFP     13.0             5.33  71597DIJFP13.0   \n",
       "2         71597.0      DIJFP     14.0             6.44  71597DIJFP14.0   \n",
       "3         71597.0      DIJFP     15.0             7.90  71597DIJFP15.0   \n",
       "4         71597.0      DIJFP     18.0            11.28  71597DIJFP18.0   \n",
       "\n",
       "   PCT_CHECKINS_s2  \n",
       "0             3.87  \n",
       "1             5.38  \n",
       "2             6.59  \n",
       "3             7.74  \n",
       "4             9.68  "
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_rename = {'CODE_STIF_TRNS_s1':'CODE_STIF_TRNS',\n",
    "              'CODE_STIF_RES_s1': 'CODE_STIF_RES',\n",
    "              'CODE_STIF_ARRET_s1': 'CODE_STIF_ARRET',\n",
    "              'STOP_NAME_s1': 'STOP_NAME',\n",
    "              'ID_REFA_LDA_s1': 'ID_REFA_LDA',\n",
    "              'CAT_DAY_s1': 'CAT_DAY',\n",
    "              'HOUR_s1': 'HOUR',\n",
    "              'ID':'ID_ROW'}\n",
    "\n",
    "metro_hp = metro_hp.rename(cols_rename, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>STOP_NAME</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CAT_DAY</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>PCT_CHECKINS_s1</th>\n",
       "      <th>ID_ROW</th>\n",
       "      <th>PCT_CHECKINS_s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.83</td>\n",
       "      <td>71597DIJFP11.0</td>\n",
       "      <td>3.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.33</td>\n",
       "      <td>71597DIJFP13.0</td>\n",
       "      <td>5.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.44</td>\n",
       "      <td>71597DIJFP14.0</td>\n",
       "      <td>6.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.90</td>\n",
       "      <td>71597DIJFP15.0</td>\n",
       "      <td>7.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>18.0</td>\n",
       "      <td>11.28</td>\n",
       "      <td>71597DIJFP18.0</td>\n",
       "      <td>9.68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CODE_STIF_TRNS  CODE_STIF_RES  CODE_STIF_ARRET        STOP_NAME  \\\n",
       "0           100.0          110.0            717.0  QUAI DE LA GARE   \n",
       "1           100.0          110.0            717.0  QUAI DE LA GARE   \n",
       "2           100.0          110.0            717.0  QUAI DE LA GARE   \n",
       "3           100.0          110.0            717.0  QUAI DE LA GARE   \n",
       "4           100.0          110.0            717.0  QUAI DE LA GARE   \n",
       "\n",
       "   ID_REFA_LDA CAT_DAY  HOUR  PCT_CHECKINS_s1          ID_ROW  PCT_CHECKINS_s2  \n",
       "0      71597.0   DIJFP  11.0             3.83  71597DIJFP11.0             3.87  \n",
       "1      71597.0   DIJFP  13.0             5.33  71597DIJFP13.0             5.38  \n",
       "2      71597.0   DIJFP  14.0             6.44  71597DIJFP14.0             6.59  \n",
       "3      71597.0   DIJFP  15.0             7.90  71597DIJFP15.0             7.74  \n",
       "4      71597.0   DIJFP  18.0            11.28  71597DIJFP18.0             9.68  "
      ]
     },
     "execution_count": 270,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_order = ['ID_ROW',\n",
    "              'CODE_STIF_TRNS',\n",
    "              'CODE_STIF_RES',\n",
    "              'CODE_STIF_ARRET',\n",
    "              'STOP_NAME',\n",
    "              'ID_REFA_LDA',\n",
    "              'CAT_DAY',\n",
    "              'HOUR',\n",
    "              'PCT_CHECKINS_s1',\n",
    "              'PCT_CHECKINS_s2']\n",
    "\n",
    "metro_hp = metro_hp[cols_order]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID_ROW</th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>STOP_NAME</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CAT_DAY</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>PCT_CHECKINS_s1</th>\n",
       "      <th>PCT_CHECKINS_s2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>71597DIJFP11.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.83</td>\n",
       "      <td>3.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>71597DIJFP13.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.33</td>\n",
       "      <td>5.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>71597DIJFP14.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.44</td>\n",
       "      <td>6.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71597DIJFP15.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.90</td>\n",
       "      <td>7.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71597DIJFP18.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>18.0</td>\n",
       "      <td>11.28</td>\n",
       "      <td>9.68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ID_ROW  CODE_STIF_TRNS  CODE_STIF_RES  CODE_STIF_ARRET  \\\n",
       "0  71597DIJFP11.0           100.0          110.0            717.0   \n",
       "1  71597DIJFP13.0           100.0          110.0            717.0   \n",
       "2  71597DIJFP14.0           100.0          110.0            717.0   \n",
       "3  71597DIJFP15.0           100.0          110.0            717.0   \n",
       "4  71597DIJFP18.0           100.0          110.0            717.0   \n",
       "\n",
       "         STOP_NAME  ID_REFA_LDA CAT_DAY  HOUR  PCT_CHECKINS_s1  \\\n",
       "0  QUAI DE LA GARE      71597.0   DIJFP  11.0             3.83   \n",
       "1  QUAI DE LA GARE      71597.0   DIJFP  13.0             5.33   \n",
       "2  QUAI DE LA GARE      71597.0   DIJFP  14.0             6.44   \n",
       "3  QUAI DE LA GARE      71597.0   DIJFP  15.0             7.90   \n",
       "4  QUAI DE LA GARE      71597.0   DIJFP  18.0            11.28   \n",
       "\n",
       "   PCT_CHECKINS_s2  \n",
       "0             3.87  \n",
       "1             5.38  \n",
       "2             6.59  \n",
       "3             7.74  \n",
       "4             9.68  "
      ]
     },
     "execution_count": 272,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 35603 entries, 0 to 35602\n",
      "Data columns (total 10 columns):\n",
      "ID_ROW             35603 non-null object\n",
      "CODE_STIF_TRNS     35603 non-null float64\n",
      "CODE_STIF_RES      35603 non-null float64\n",
      "CODE_STIF_ARRET    35603 non-null float64\n",
      "STOP_NAME          35603 non-null object\n",
      "ID_REFA_LDA        35603 non-null float64\n",
      "CAT_DAY            35603 non-null object\n",
      "HOUR               35592 non-null float64\n",
      "PCT_CHECKINS_s1    35237 non-null float64\n",
      "PCT_CHECKINS_s2    35175 non-null float64\n",
      "dtypes: float64(7), object(3)\n",
      "memory usage: 3.0+ MB\n"
     ]
    }
   ],
   "source": [
    "metro_hp.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID_ROW               0\n",
       "CODE_STIF_TRNS       0\n",
       "CODE_STIF_RES        0\n",
       "CODE_STIF_ARRET      0\n",
       "STOP_NAME            0\n",
       "ID_REFA_LDA          0\n",
       "CAT_DAY              0\n",
       "HOUR                11\n",
       "PCT_CHECKINS_s1    366\n",
       "PCT_CHECKINS_s2    428\n",
       "dtype: int64"
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 35603 entries, 0 to 35602\n",
      "Data columns (total 10 columns):\n",
      "ID_ROW             35603 non-null object\n",
      "CODE_STIF_TRNS     35603 non-null float64\n",
      "CODE_STIF_RES      35603 non-null float64\n",
      "CODE_STIF_ARRET    35603 non-null float64\n",
      "STOP_NAME          35603 non-null object\n",
      "ID_REFA_LDA        35603 non-null float64\n",
      "CAT_DAY            35603 non-null object\n",
      "HOUR               35592 non-null float64\n",
      "PCT_CHECKINS_s1    35237 non-null float64\n",
      "PCT_CHECKINS_s2    35175 non-null float64\n",
      "dtypes: float64(7), object(3)\n",
      "memory usage: 3.0+ MB\n"
     ]
    }
   ],
   "source": [
    "metro_hp.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is normal that there remain missing values for PCT_CHECKINS_s1 and s2 due to the merge."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID_ROW             35603\n",
       "CODE_STIF_TRNS         1\n",
       "CODE_STIF_RES          1\n",
       "CODE_STIF_ARRET      301\n",
       "STOP_NAME            301\n",
       "ID_REFA_LDA          301\n",
       "CAT_DAY                5\n",
       "HOUR                  24\n",
       "PCT_CHECKINS_s1     1613\n",
       "PCT_CHECKINS_s2     1601\n",
       "dtype: int64"
      ]
     },
     "execution_count": 276,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metro_hp.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.B.e. Compute mean hourly profile of 1st & 1nd semesters <a name=\"3Be\"></a>\n",
    "\n",
    "Add a column with the mean of PCT_CHECKINS_s1 and PCT_CHECKINS_s2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp['PCT_CHECKINS_mean'] = (metro_hp['PCT_CHECKINS_s1']+metro_hp['PCT_CHECKINS_s2'])/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp['PCT_CHECKINS_mean'] = metro_hp['PCT_CHECKINS_mean'].fillna(metro_hp['PCT_CHECKINS_s1'])\n",
    "metro_hp['PCT_CHECKINS_mean'] = metro_hp['PCT_CHECKINS_mean'].fillna(metro_hp['PCT_CHECKINS_s2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 35603 entries, 0 to 35602\n",
      "Data columns (total 11 columns):\n",
      "ID_ROW               35603 non-null object\n",
      "CODE_STIF_TRNS       35603 non-null float64\n",
      "CODE_STIF_RES        35603 non-null float64\n",
      "CODE_STIF_ARRET      35603 non-null float64\n",
      "STOP_NAME            35603 non-null object\n",
      "ID_REFA_LDA          35603 non-null float64\n",
      "CAT_DAY              35603 non-null object\n",
      "HOUR                 35592 non-null float64\n",
      "PCT_CHECKINS_s1      35237 non-null float64\n",
      "PCT_CHECKINS_s2      35175 non-null float64\n",
      "PCT_CHECKINS_mean    35603 non-null float64\n",
      "dtypes: float64(8), object(3)\n",
      "memory usage: 3.3+ MB\n"
     ]
    }
   ],
   "source": [
    "metro_hp.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.B.f Check data completeness <a name=\"3Bf\"></a>\n",
    "\n",
    "Check that both metro_nb and metro_hp have the same ID_REFA_LDA and STOP_NAME values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp_lda = np.sort(metro_hp['ID_REFA_LDA'].unique())\n",
    "metro_nb_lda = np.sort(metro_nb['ID_REFA_LDA'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(metro_hp_lda == metro_nb_lda).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp_name = np.sort(metro_hp['STOP_NAME'].unique())\n",
    "metro_nb_name = np.sort(metro_nb['STOP_NAME'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 283,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(metro_hp_name == metro_nb_name).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ID_REFA_LDA and STOP_NAME columns of both dataframes match perfectly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.B.g. Filter columns <a name=\"3Bg\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of columns to drop\n",
    "col_todrop = ['ID_ROW', 'CODE_STIF_TRNS', 'CODE_STIF_RES', 'CODE_STIF_ARRET']\n",
    "\n",
    "# Drop columns\n",
    "metro_hp.drop(columns=col_todrop, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.C. Export to .csv <a name=\"3C\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {},
   "outputs": [],
   "source": [
    "metro_hp.to_csv('../../datasets/metro_hp.csv', index=None)"
   ]
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}