1_intro_exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notebook 1/4: General introduction and initial data exploration\n",
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of contents\n",
    "1. [General introduction to the data](#1)\n",
    "    1. [Source](#1A)\n",
    "    2. [Datasets](#1B)\n",
    "    3. [Scope of the analysis](#1C)\n",
    "2. [Initial exploration](#2)\n",
    "    1. [Purpose of this notebook](#2A)\n",
    "    2. [Daily traffic *1st semester 2018*](#2B)\n",
    "        1. [Observations](#2Ba)\n",
    "        2. [Ticket Types](#2Bb)\n",
    "    3. [Daily traffic *2nd semester 2018*](#2C)\n",
    "        1. [Observations](#2Ca)\n",
    "        2. [Next steps](#2Cb)\n",
    "    4. [Hourly profiles 1st semester 2018](#2D)\n",
    "        1. [Observations](#2Da)\n",
    "        2. [Day Categories](#2Db)\n",
    "    5. [Hourly profiles *2nd semester 2018*](#2E)\n",
    "        1. [Observations](#2Ea)\n",
    "        2. [Next steps](#2Eb)\n",
    "    6. [Repository](#2F)\n",
    "        1. [Identification Codes](#2Fa)\n",
    "        2. [Stop Types](#2Fb)\n",
    "        3. [Observations](#2Fc)\n",
    "    7. [Geographical data](#2G)\n",
    "        1. [Observations](#2Ga)\n",
    "        2. [Next steps](#2Gb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. General introduction to the data <a name=\"1\"></a>\n",
    "\n",
    "\n",
    "## 1.A. Source: <a name=\"1A\"></a>\n",
    "\n",
    "Île-de-France Mobilités, formerly STIF, is the organisation authority that controls and coordinates the different transport companies operating in the Paris-area public transport network and rest of Île-de-France region. \n",
    "\n",
    "Since 2016, the STIF gives access to some of its raw data through an [opendata portal](https://opendata.stif.info/explore/?sort=modified).\n",
    "\n",
    "The STIF operates both a road network (bus) and a rail network (train, metro, RER, funicular).\n",
    "\n",
    "For the purpose of this analysis, we will focus on the **rail newtork**.\n",
    "\n",
    "\n",
    "## 1.B. Datasets: <a name=\"1B\"></a>\n",
    "\n",
    "The STIF provides the following data about the rail network: daily traffic per stop (number of checkins per day and per ticket type), hourly profiles per stop (traffic distribution per hour of a typical day), geographical coordinates (arranged by stop or by line of transport), repositories of all stops (arranged by stop or by line of transport).\n",
    "\n",
    "Data about daily traffic and hourly profiles is available for the years 2015 through 2018. \n",
    "\n",
    "We will focus our analysis on the year **2018**.\n",
    "\n",
    "For the year 2018, data is split accross 2 datasets, corresponding to the 1st and 2nd semester of the year.\n",
    "\n",
    "Below are the links to the aforementionned datasets:\n",
    "- [Daily traffic *1st semester 2018*](https://opendata.stif.info/explore/dataset/validations-sur-le-reseau-ferre-nombre-de-validations-par-jour-1er-sem/information/)\n",
    "- [Daily traffic *2nd semester 2018*](https://opendata.stif.info/explore/dataset/validations-sur-le-reseau-ferre-nombre-de-validations-par-jour-2e-sem/information/)\n",
    "- [Hourly profiles *1st semester 2018*](https://opendata.stif.info/explore/dataset/validations-sur-le-reseau-ferre-profils-horaires-par-jour-type-1er-sem/information/)\n",
    "- [Hourly profiles *2nd semester 2018*](https://opendata.stif.info/explore/dataset/validations-sur-le-reseau-ferre-profils-horaires-par-jour-type-2e-sem/information/)\n",
    "- [Repository](https://opendata.stif.info/explore/dataset/referentiel-arret-tc-idf/information/)\n",
    "> This page also contains information about identification codes.\n",
    "- [Geographical coordinates](https://opendata.stif.info/explore/dataset/emplacement-des-gares-idf-data-generalisee/information/)\n",
    "\n",
    "\n",
    "## 1.C. Scope of the analysis: <a name=\"1C\"></a>\n",
    "\n",
    "The ultimate goal is to analyze the traffic accross Parisian metro stations for the year 2018."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Initial exploration <a name=\"2\"></a>\n",
    "\n",
    "\n",
    "## 2.A. Purpose of this notebook <a name=\"2A\"></a>\n",
    "\n",
    "In this notebook, we will explore these datasets to gain a first understanding of the nature of the data at hand znd identify the necessary cleaning steps.\n",
    "\n",
    "We will perform the cleaning tasks and analysis in separate notebooks (see 3. Cleaning and 4. Analysis)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.B. Daily traffic *1st semester 2018* <a name=\"2B\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "nb_2018s1 = pd.read_csv(\"../../datasets/validations-sur-le-reseau-ferre-nombre-de-validations-par-jour-1er-sem.csv\", sep=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>JOUR</th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>LIBELLE_ARRET</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CATEGORIE_TITRE</th>\n",
       "      <th>NB_VALD</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</td>\n",
       "      <td>1006</td>\n",
       "      <td>OLYMPIADES</td>\n",
       "      <td>71557.0</td>\n",
       "      <td>AMETHYSTE</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1006</td>\n",
       "      <td>OLYMPIADES</td>\n",
       "      <td>71557.0</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>10764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1007</td>\n",
       "      <td>LES AGNETTES-ASNIERES-GENNEVILLIERS</td>\n",
       "      <td>72240.0</td>\n",
       "      <td>NAVIGO</td>\n",
       "      <td>3972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1008</td>\n",
       "      <td>LES COURTILLES</td>\n",
       "      <td>72286.0</td>\n",
       "      <td>TST</td>\n",
       "      <td>813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-05-09</td>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>104</td>\n",
       "      <td>BOURSE</td>\n",
       "      <td>73635.0</td>\n",
       "      <td>?</td>\n",
       "      <td>Moins de 5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         JOUR  CODE_STIF_TRNS CODE_STIF_RES CODE_STIF_ARRET  \\\n",
       "0  2018-05-09             100           110            1006   \n",
       "1  2018-05-09             100           110            1006   \n",
       "2  2018-05-09             100           110            1007   \n",
       "3  2018-05-09             100           110            1008   \n",
       "4  2018-05-09             100           110             104   \n",
       "\n",
       "                         LIBELLE_ARRET  ID_REFA_LDA CATEGORIE_TITRE  \\\n",
       "0                           OLYMPIADES      71557.0       AMETHYSTE   \n",
       "1                           OLYMPIADES      71557.0          NAVIGO   \n",
       "2  LES AGNETTES-ASNIERES-GENNEVILLIERS      72240.0          NAVIGO   \n",
       "3                       LES COURTILLES      72286.0             TST   \n",
       "4                               BOURSE      73635.0               ?   \n",
       "\n",
       "      NB_VALD  \n",
       "0         462  \n",
       "1       10764  \n",
       "2        3972  \n",
       "3         813  \n",
       "4  Moins de 5  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>JOUR</th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>LIBELLE_ARRET</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CATEGORIE_TITRE</th>\n",
       "      <th>NB_VALD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>777431</th>\n",
       "      <td>2018-06-27</td>\n",
       "      <td>800</td>\n",
       "      <td>853</td>\n",
       "      <td>308</td>\n",
       "      <td>GARE DU NORD</td>\n",
       "      <td>71410.0</td>\n",
       "      <td>FGT</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>777432</th>\n",
       "      <td>2018-06-27</td>\n",
       "      <td>800</td>\n",
       "      <td>853</td>\n",
       "      <td>308</td>\n",
       "      <td>GARE DU NORD</td>\n",
       "      <td>71410.0</td>\n",
       "      <td>IMAGINE R</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>777433</th>\n",
       "      <td>2018-06-27</td>\n",
       "      <td>800</td>\n",
       "      <td>853</td>\n",
       "      <td>784</td>\n",
       "      <td>SARCELLES-SAINT-BRICE</td>\n",
       "      <td>66079.0</td>\n",
       "      <td>?</td>\n",
       "      <td>Moins de 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>777434</th>\n",
       "      <td>2018-06-27</td>\n",
       "      <td>800</td>\n",
       "      <td>853</td>\n",
       "      <td>784</td>\n",
       "      <td>SARCELLES-SAINT-BRICE</td>\n",
       "      <td>66079.0</td>\n",
       "      <td>FGT</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>777435</th>\n",
       "      <td>2018-06-27</td>\n",
       "      <td>800</td>\n",
       "      <td>853</td>\n",
       "      <td>784</td>\n",
       "      <td>SARCELLES-SAINT-BRICE</td>\n",
       "      <td>66079.0</td>\n",
       "      <td>TST</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              JOUR  CODE_STIF_TRNS CODE_STIF_RES CODE_STIF_ARRET  \\\n",
       "777431  2018-06-27             800           853             308   \n",
       "777432  2018-06-27             800           853             308   \n",
       "777433  2018-06-27             800           853             784   \n",
       "777434  2018-06-27             800           853             784   \n",
       "777435  2018-06-27             800           853             784   \n",
       "\n",
       "                LIBELLE_ARRET  ID_REFA_LDA CATEGORIE_TITRE     NB_VALD  \n",
       "777431           GARE DU NORD      71410.0             FGT          11  \n",
       "777432           GARE DU NORD      71410.0       IMAGINE R          14  \n",
       "777433  SARCELLES-SAINT-BRICE      66079.0               ?  Moins de 5  \n",
       "777434  SARCELLES-SAINT-BRICE      66079.0             FGT          80  \n",
       "777435  SARCELLES-SAINT-BRICE      66079.0             TST          96  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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",
      "JOUR               777436 non-null object\n",
      "CODE_STIF_TRNS     777436 non-null int64\n",
      "CODE_STIF_RES      777436 non-null object\n",
      "CODE_STIF_ARRET    777436 non-null object\n",
      "LIBELLE_ARRET      777436 non-null object\n",
      "ID_REFA_LDA        767645 non-null float64\n",
      "CATEGORIE_TITRE    777436 non-null object\n",
      "NB_VALD            777436 non-null object\n",
      "dtypes: float64(1), int64(1), object(6)\n",
      "memory usage: 47.5+ MB\n"
     ]
    }
   ],
   "source": [
    "nb_2018s1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "JOUR                 181\n",
       "CODE_STIF_TRNS         3\n",
       "CODE_STIF_RES         14\n",
       "CODE_STIF_ARRET      754\n",
       "LIBELLE_ARRET        732\n",
       "ID_REFA_LDA          721\n",
       "CATEGORIE_TITRE        8\n",
       "NB_VALD            18583\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['110', '804', '805', '853', '852', 'ND', '800', '803', '854',\n",
       "       '802', '801', '850', '822', '851'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1['CODE_STIF_RES'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "301"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of unique stop names with a CODE_STIF_RES of '110'\n",
    "\n",
    "nb_2018s1.loc[nb_2018s1['CODE_STIF_RES'] == '110', 'LIBELLE_ARRET'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['AMETHYSTE', 'NAVIGO', 'TST', '?', 'FGT', 'AUTRE TITRE',\n",
       "       'IMAGINE R', 'NON DEFINI'], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s1['CATEGORIE_TITRE'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.a Observations: <a name=\"2Ba\"></a>\n",
    "\n",
    "> nb_2018s1 has 8 columns:\n",
    "\n",
    "> - JOUR (= Day): Day of the year. This column has the correct number of unique values (corresponds to the number of days for the 1st semester of 2018). The dtype is object instead of datetime.\n",
    "\n",
    "\n",
    "> - CODE_STIF_TRNS: Code allocated by the STIF to identify the carrier  \n",
    "\n",
    "\n",
    "> - CODE_STIF_RES: Code allocated by the STIF to identify the network. Some missing values are designated by ND (means 'Non Disponible' in French = 'Unavailable'). Some research revealed that '110' designates Parisian metro stations. There are 301 stops with a CODE_STIF_RES of '110' in the dataframe. 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",
    "\n",
    "> - CODE_STIF_ARRET: Code allocated by the STIF to identify the stop.   \n",
    "\n",
    "\n",
    "> - LIBELLE_ARRET (= Stop name): Commercial name of the stop. It has less unique values than CODE_STIF_ARRET, suggesting that one stop name can have several CODE_STIF_ARRET attached to it.     \n",
    "\n",
    "\n",
    "> - ID_REFA_LDA: Identification code of the LDA (= Lieu d'Arret). LDA refers to the place where different vehicles can stop. This column has missing values. See below for more details about the different identification codes.  \n",
    "\n",
    "\n",
    "> - CATEGORIE_TITRE (= Ticket type): Rate plan of the ticket used to check in. Some missing values are designated by '?'. See below for more details about the different rate plans.\n",
    "\n",
    "\n",
    "> - NB_VALD (= Number of checkins): Total number of checkins. These do not include transfers, but only account for checkins to the rail network. It means that if a person enters the network at station A and then travels throughout the network to station B, it is only accounted for once, at station A."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.B.b. Ticket types: <a name=\"2Bb\"></a>\n",
    "\n",
    "> The STIF gives the following description of each rate plan:\n",
    "\n",
    "> - IMAGINE R: Annual rate plan reserved for pupils and students.\n",
    "> - NAVIGO: Annual, monthly or weekly rate plan.\n",
    "> - AMETHYSTE: Annual rate plan reserved for elderly and disabled people.\n",
    "> - TST: Discount monthly or weekly rate plan, reserved for eligible people.\n",
    "> - FGT: Discount annual, monthly or weekly rate plan, reserved for eligible people.\n",
    "> - AUTRE TITRE: Other special fares.\n",
    "> - NON DEFINI: Indicates missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.C. Daily traffic *2nd semester 2018* <a name=\"2C\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "nb_2018s2 = pd.read_csv(\"../../datasets/validations-sur-le-reseau-ferre-nombre-de-validations-par-jour-2e-sem.csv\", sep = \";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>JOUR</th>\n",
       "      <th>CODE_STIF_TRNS</th>\n",
       "      <th>CODE_STIF_RES</th>\n",
       "      <th>CODE_STIF_ARRET</th>\n",
       "      <th>LIBELLE_ARRET</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CATEGORIE_TITRE</th>\n",
       "      <th>NB_VALD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-11-14</td>\n",
       "      <td>800</td>\n",
       "      <td>851.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>MORET-VENEUX-LES-SABLONS</td>\n",
       "      <td>61410</td>\n",
       "      <td>AMETHYSTE</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-11-14</td>\n",
       "      <td>800</td>\n",
       "      <td>851.0</td>\n",
       "      <td>882.0</td>\n",
       "      <td>VERNOU-SUR-SEINE</td>\n",
       "      <td>61450</td>\n",
       "      <td>IMAGINE R</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-11-14</td>\n",
       "      <td>800</td>\n",
       "      <td>852.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>CHAVILLE RIVE GAUCHE</td>\n",
       "      <td>73718</td>\n",
       "      <td>?</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-11-14</td>\n",
       "      <td>800</td>\n",
       "      <td>852.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>CHAVILLE RIVE GAUCHE</td>\n",
       "      <td>73718</td>\n",
       "      <td>FGT</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-11-14</td>\n",
       "      <td>800</td>\n",
       "      <td>852.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>CHAVILLE RIVE GAUCHE</td>\n",
       "      <td>73718</td>\n",
       "      <td>IMAGINE R</td>\n",
       "      <td>342.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         JOUR  CODE_STIF_TRNS  CODE_STIF_RES  CODE_STIF_ARRET  \\\n",
       "0  2018-11-14             800          851.0            880.0   \n",
       "1  2018-11-14             800          851.0            882.0   \n",
       "2  2018-11-14             800          852.0            176.0   \n",
       "3  2018-11-14             800          852.0            176.0   \n",
       "4  2018-11-14             800          852.0            176.0   \n",
       "\n",
       "              LIBELLE_ARRET ID_REFA_LDA CATEGORIE_TITRE  NB_VALD  \n",
       "0  MORET-VENEUX-LES-SABLONS       61410       AMETHYSTE     18.0  \n",
       "1          VERNOU-SUR-SEINE       61450       IMAGINE R     14.0  \n",
       "2      CHAVILLE RIVE GAUCHE       73718               ?      NaN  \n",
       "3      CHAVILLE RIVE GAUCHE       73718             FGT     18.0  \n",
       "4      CHAVILLE RIVE GAUCHE       73718       IMAGINE R    342.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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",
      "JOUR               883145 non-null object\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",
      "LIBELLE_ARRET      883145 non-null object\n",
      "ID_REFA_LDA        881869 non-null object\n",
      "CATEGORIE_TITRE    883145 non-null object\n",
      "NB_VALD            765356 non-null float64\n",
      "dtypes: float64(3), int64(1), object(4)\n",
      "memory usage: 53.9+ MB\n"
     ]
    }
   ],
   "source": [
    "nb_2018s2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "JOUR                 184\n",
       "CODE_STIF_TRNS         3\n",
       "CODE_STIF_RES         13\n",
       "CODE_STIF_ARRET      753\n",
       "LIBELLE_ARRET        732\n",
       "ID_REFA_LDA          722\n",
       "CATEGORIE_TITRE        8\n",
       "NB_VALD            18546\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s2.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([851., 852., 853., 804., 805., 850., 801., 802., 110., 803., 854.,\n",
       "       822.,  nan, 800.])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s2['CODE_STIF_RES'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['AMETHYSTE', 'IMAGINE R', '?', 'FGT', 'NAVIGO', 'AUTRE TITRE',\n",
       "       'TST', 'NON DEFINI'], dtype=object)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nb_2018s2['CATEGORIE_TITRE'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.C.a. Observations (and comparison of the 2 dataframes for the 1st and 2nd semester): <a name=\"2Ca\"></a>\n",
    "\n",
    "> nb_2018s2 contains the exact same columns as nb_2018s1.\n",
    "\n",
    "> The number of days is correct (corresponds to the number of days for the 2nd semester of 2018), totalling to 365 unique values for the year 2018. But for both dataframes, the dtype of this column is object instead of datetime.\n",
    "\n",
    "> We see that these 2 dataframes have different dtypes, so we will have to standardize the data.\n",
    "\n",
    "> The columns CODE_STIF_RES, CODE_STIF_ARRET and ID_REFA_LDA have a different number of unique values in each dataframe. \n",
    "\n",
    "> Looking at the CODE_STIF_RES column, we see that missing values are designated by 'ND' in one dataset and np.nan in the other. The issue is probably similar for CODE_STIF_ARRET & ID_REFA_LDA. We'll have to normalize the missing values for these columns.\n",
    "\n",
    "> We see that some missing values are designated by a question mark in the CATEGORY_TITRE column. Again, we'll have to normalize missing values in this column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.C.b. Next steps for the cleaning & merging of nb_2018s1 and nb_2018s2 (daily traffic for the 1st and 2nd semesters of 2018): <a name=\"2Cb\"></a>\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": "markdown",
   "metadata": {},
   "source": [
    "## 2.D. Hourly profiles *1st semester 2018* <a name=\"2D\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "hp_2018s1 = pd.read_csv('../../datasets/validations-sur-le-reseau-ferre-profils-horaires-par-jour-type-1er-sem.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>LIBELLE_ARRET</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CAT_JOUR</th>\n",
       "      <th>TRNC_HORR_60</th>\n",
       "      <th>pourc_validations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>717</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>11H-12H</td>\n",
       "      <td>3.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>717</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>13H-14H</td>\n",
       "      <td>5.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>717</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>14H-15H</td>\n",
       "      <td>6.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>717</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>15H-16H</td>\n",
       "      <td>7.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>717</td>\n",
       "      <td>QUAI DE LA GARE</td>\n",
       "      <td>71597.0</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>18H-19H</td>\n",
       "      <td>11.28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CODE_STIF_TRNS CODE_STIF_RES CODE_STIF_ARRET    LIBELLE_ARRET  ID_REFA_LDA  \\\n",
       "0             100           110             717  QUAI DE LA GARE      71597.0   \n",
       "1             100           110             717  QUAI DE LA GARE      71597.0   \n",
       "2             100           110             717  QUAI DE LA GARE      71597.0   \n",
       "3             100           110             717  QUAI DE LA GARE      71597.0   \n",
       "4             100           110             717  QUAI DE LA GARE      71597.0   \n",
       "\n",
       "  CAT_JOUR TRNC_HORR_60  pourc_validations  \n",
       "0    DIJFP      11H-12H               3.83  \n",
       "1    DIJFP      13H-14H               5.33  \n",
       "2    DIJFP      14H-15H               6.44  \n",
       "3    DIJFP      15H-16H               7.90  \n",
       "4    DIJFP      18H-19H              11.28  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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        82744 non-null object\n",
      "CODE_STIF_ARRET      82744 non-null object\n",
      "LIBELLE_ARRET        82744 non-null object\n",
      "ID_REFA_LDA          81576 non-null float64\n",
      "CAT_JOUR             82744 non-null object\n",
      "TRNC_HORR_60         82744 non-null object\n",
      "pourc_validations    82744 non-null float64\n",
      "dtypes: float64(2), int64(1), object(5)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "hp_2018s1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CODE_STIF_TRNS          3\n",
       "CODE_STIF_RES          14\n",
       "CODE_STIF_ARRET       754\n",
       "LIBELLE_ARRET         732\n",
       "ID_REFA_LDA           721\n",
       "CAT_JOUR                5\n",
       "TRNC_HORR_60           25\n",
       "pourc_validations    2578\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['110', '852', '853', 'ND', '800', '803', '851', '854', '801',\n",
       "       '802', '805', '804', '822', '850'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1['CODE_STIF_RES'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['DIJFP', 'JOHV', 'JOVS', 'SAHV', 'SAVS'], dtype=object)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1['CAT_JOUR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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', 'ND'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s1['TRNC_HORR_60'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.D.a. Observations: <a name=\"2Da\"></a>\n",
    "\n",
    "> hp_2018s1 has 8 columns.\n",
    "\n",
    "> Some of these columns are the same as we saw above, and present the same challenges with missing values:\n",
    "\n",
    "> - CODE_STIF_TRNS\n",
    "> - CODE_STIF_RES\n",
    "> - CODE_STIF_ARRET\n",
    "> - LIBELLE_ARRET\n",
    "> - ID_REFA_LDA\n",
    "\n",
    "\n",
    "> These 3 columns, however, are new:\n",
    "\n",
    "> - CAT_JOUR (= Day type): Designates the type of day. There are 5 categories. See below for more details about these categories.\n",
    "\n",
    "\n",
    "> - TRNC_HORR_60 (= Time delta): Periods of time of 1 hour each. This column also has missing values indicated by 'ND'.\n",
    "\n",
    "\n",
    "> - pourc_validations (= Percentage of checkins): Percentage of checkins per hour for a given day category and a given stop."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.D.b. Day Categories: <a name=\"2Db\"></a>\n",
    "\n",
    "- JOHV (Jour Ouvré Hors Vacances): Working day outside school holidays\n",
    "- JOVS (Jour Ouvré Vacances Scolaires): Working day during school holidays\n",
    "- SAHV (Samedi Hors Vacances): Saturday outside school holidays\n",
    "- SAVS (Samedi Vacances Scolaires): Saturday during school holidays\n",
    "- DIJFP (Dimanche Jour Férié Pont): Sunday & national holidays"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.E. Hourly profiles *2nd semester 2018* <a name=\"2E\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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_2018s2 = pd.read_csv('../../datasets/validations-sur-le-reseau-ferre-profils-horaires-par-jour-type-2e-sem.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>LIBELLE_ARRET</th>\n",
       "      <th>ID_REFA_LDA</th>\n",
       "      <th>CAT_JOUR</th>\n",
       "      <th>TRNC_HORR_60</th>\n",
       "      <th>pourc_validations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1007</td>\n",
       "      <td>LES AGNETTES-ASNIERES-GENNEVILLIERS</td>\n",
       "      <td>72240</td>\n",
       "      <td>SAVS</td>\n",
       "      <td>17H-18H</td>\n",
       "      <td>6.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1007</td>\n",
       "      <td>LES AGNETTES-ASNIERES-GENNEVILLIERS</td>\n",
       "      <td>72240</td>\n",
       "      <td>SAVS</td>\n",
       "      <td>23H-0H</td>\n",
       "      <td>1.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1007</td>\n",
       "      <td>LES AGNETTES-ASNIERES-GENNEVILLIERS</td>\n",
       "      <td>72240</td>\n",
       "      <td>SAVS</td>\n",
       "      <td>7H-8H</td>\n",
       "      <td>5.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1008</td>\n",
       "      <td>LES COURTILLES</td>\n",
       "      <td>72286</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>11H-12H</td>\n",
       "      <td>6.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>1008</td>\n",
       "      <td>LES COURTILLES</td>\n",
       "      <td>72286</td>\n",
       "      <td>DIJFP</td>\n",
       "      <td>12H-13H</td>\n",
       "      <td>6.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CODE_STIF_TRNS CODE_STIF_RES CODE_STIF_ARRET  \\\n",
       "0             100           110            1007   \n",
       "1             100           110            1007   \n",
       "2             100           110            1007   \n",
       "3             100           110            1008   \n",
       "4             100           110            1008   \n",
       "\n",
       "                         LIBELLE_ARRET ID_REFA_LDA CAT_JOUR TRNC_HORR_60  \\\n",
       "0  LES AGNETTES-ASNIERES-GENNEVILLIERS       72240     SAVS      17H-18H   \n",
       "1  LES AGNETTES-ASNIERES-GENNEVILLIERS       72240     SAVS       23H-0H   \n",
       "2  LES AGNETTES-ASNIERES-GENNEVILLIERS       72240     SAVS        7H-8H   \n",
       "3                       LES COURTILLES       72286    DIJFP      11H-12H   \n",
       "4                       LES COURTILLES       72286    DIJFP      12H-13H   \n",
       "\n",
       "   pourc_validations  \n",
       "0               6.61  \n",
       "1               1.68  \n",
       "2               5.04  \n",
       "3               6.09  \n",
       "4               6.66  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 83532 entries, 0 to 83531\n",
      "Data columns (total 8 columns):\n",
      "CODE_STIF_TRNS       83532 non-null int64\n",
      "CODE_STIF_RES        83532 non-null object\n",
      "CODE_STIF_ARRET      83532 non-null object\n",
      "LIBELLE_ARRET        83532 non-null object\n",
      "ID_REFA_LDA          83414 non-null object\n",
      "CAT_JOUR             83532 non-null object\n",
      "TRNC_HORR_60         83532 non-null object\n",
      "pourc_validations    83532 non-null float64\n",
      "dtypes: float64(1), int64(1), object(6)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "hp_2018s2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CODE_STIF_TRNS          3\n",
       "CODE_STIF_RES          25\n",
       "CODE_STIF_ARRET      1265\n",
       "LIBELLE_ARRET         732\n",
       "ID_REFA_LDA           722\n",
       "CAT_JOUR                5\n",
       "TRNC_HORR_60           25\n",
       "pourc_validations    2473\n",
       "dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s2.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['110', '803', '804', '800', 'ND', '805', '822', '850', '801',\n",
       "       '851', '853', '802', '854', '852', 853, 110, 822, 850, 852, 854,\n",
       "       801, 851, 802, 805, 804], dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s2['CODE_STIF_RES'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['SAVS', 'DIJFP', 'SAHV', 'JOHV', 'JOVS'], dtype=object)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s2['CAT_JOUR'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['17H-18H', '23H-0H', '7H-8H', '11H-12H', '12H-13H', '15H-16H',\n",
       "       '16H-17H', '18H-19H', '14H-15H', '1H-2H', '19H-20H', '21H-22H',\n",
       "       '9H-10H', '3H-4H', '5H-6H', '6H-7H', '10H-11H', '0H-1H', '2H-3H',\n",
       "       '22H-23H', '4H-5H', '20H-21H', '8H-9H', '13H-14H', 'ND'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp_2018s2['TRNC_HORR_60'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.E.a. Observations: <a name=\"2Ea\"></a>\n",
    "\n",
    "> This dataframe contains the exact same columns as the one for the 1st semester of 2018.\n",
    "\n",
    "> The 2 dataframes don't have the same dtypes. We'll have to standardize the data.\n",
    "\n",
    "> Both dataframes have missing values in the ID_REFA_LDA column and don't have the same number of unique values for that column.\n",
    "\n",
    "> Missing values seem to be indicated by 'ND' in CODE_STIF_RES column and TRNC_HORR_60. We'll have to normalize those missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.E.b. Next steps for the cleaning & merging of hp_2018s1 and hp_2018s2 (daily traffic for the 1st and 2nd semesters of 2018): <a name=\"2Eb\"></a>\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": [
    "## 2.F. Repository <a name=\"2F\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.F.a.  Identification Codes: <a name=\"2Fa\"></a>\n",
    "\n",
    "The STIF assigns 3 levels of identification to each stop:\n",
    "\n",
    "- LDA (Lieu D'Arrêt): Designates a place where vehicules from different lines can stop.\n",
    "- ZDL (Zone De Lieu): Designates an area within an LDA that regroups several ZDEs with the same operating name.\n",
    "- ZDE (Zone D'Embarquement): Designates a precise spot where people can get in and out of a vehicule (ex: metro platform).\n",
    "\n",
    "The STIF also allocates internal codes to identify carriers (CODE_STIF_TRNS), network types (CODE_STIF_RES), and stops (CODE_STIF_ARRET)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "ref_stops = pd.read_csv('../../datasets/referentiel-arret-tc-idf.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>ZDEr_ID_REF_A</th>\n",
       "      <th>ZDEr_NOM</th>\n",
       "      <th>ZDEr_ID_TYPE_ARRET</th>\n",
       "      <th>ZDEr_LIBELLE_TYPE_ARRET</th>\n",
       "      <th>ZDEr_X_Y</th>\n",
       "      <th>ZDLr_ID_REF_A</th>\n",
       "      <th>ZDLr_NOM</th>\n",
       "      <th>ZDLr_ID_TYPE_ARRET</th>\n",
       "      <th>ZDLr_LIBELLE_TYPE_ARRET</th>\n",
       "      <th>LDA_ID_REF_A</th>\n",
       "      <th>LDA_NOM</th>\n",
       "      <th>LDA_ID_TYPE_ARRET</th>\n",
       "      <th>LDA_LIBELLE_TYPE_ARRET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>36757</td>\n",
       "      <td>Saint-Exupéry</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>640837,6863829</td>\n",
       "      <td>50507</td>\n",
       "      <td>Saint-Exupéry</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>70831</td>\n",
       "      <td>Saint-Exupéry</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39355</td>\n",
       "      <td>La Paix</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>641034,6863697</td>\n",
       "      <td>50508</td>\n",
       "      <td>La Paix</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>70820</td>\n",
       "      <td>La Paix</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39358</td>\n",
       "      <td>Victorien Sardou</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>641128,6863107</td>\n",
       "      <td>50510</td>\n",
       "      <td>Victorien Sardou</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>70796</td>\n",
       "      <td>Victorien Sardou</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27653</td>\n",
       "      <td>Aristide Briand</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>658849,6869841</td>\n",
       "      <td>50518</td>\n",
       "      <td>Aristide Briand / Centre Culturel</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>72619</td>\n",
       "      <td>Aristide Briand / Centre Culturel</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19289</td>\n",
       "      <td>Bois d'Amour</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>660488.75,6868398.5</td>\n",
       "      <td>50520</td>\n",
       "      <td>Jean Jaurès / Bois d'Amour</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "      <td>72527</td>\n",
       "      <td>Jean Jaurès / Bois d'Amour</td>\n",
       "      <td>5</td>\n",
       "      <td>Arrêt de bus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ZDEr_ID_REF_A          ZDEr_NOM  ZDEr_ID_TYPE_ARRET  \\\n",
       "0          36757     Saint-Exupéry                   5   \n",
       "1          39355           La Paix                   5   \n",
       "2          39358  Victorien Sardou                   5   \n",
       "3          27653   Aristide Briand                   5   \n",
       "4          19289      Bois d'Amour                   5   \n",
       "\n",
       "  ZDEr_LIBELLE_TYPE_ARRET             ZDEr_X_Y  ZDLr_ID_REF_A  \\\n",
       "0            Arrêt de bus       640837,6863829          50507   \n",
       "1            Arrêt de bus       641034,6863697          50508   \n",
       "2            Arrêt de bus       641128,6863107          50510   \n",
       "3            Arrêt de bus       658849,6869841          50518   \n",
       "4            Arrêt de bus  660488.75,6868398.5          50520   \n",
       "\n",
       "                            ZDLr_NOM  ZDLr_ID_TYPE_ARRET  \\\n",
       "0                      Saint-Exupéry                   5   \n",
       "1                            La Paix                   5   \n",
       "2                   Victorien Sardou                   5   \n",
       "3  Aristide Briand / Centre Culturel                   5   \n",
       "4         Jean Jaurès / Bois d'Amour                   5   \n",
       "\n",
       "  ZDLr_LIBELLE_TYPE_ARRET  LDA_ID_REF_A                            LDA_NOM  \\\n",
       "0            Arrêt de bus         70831                      Saint-Exupéry   \n",
       "1            Arrêt de bus         70820                            La Paix   \n",
       "2            Arrêt de bus         70796                   Victorien Sardou   \n",
       "3            Arrêt de bus         72619  Aristide Briand / Centre Culturel   \n",
       "4            Arrêt de bus         72527         Jean Jaurès / Bois d'Amour   \n",
       "\n",
       "   LDA_ID_TYPE_ARRET LDA_LIBELLE_TYPE_ARRET  \n",
       "0                  5           Arrêt de bus  \n",
       "1                  5           Arrêt de bus  \n",
       "2                  5           Arrêt de bus  \n",
       "3                  5           Arrêt de bus  \n",
       "4                  5           Arrêt de bus  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ref_stops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 39925 entries, 0 to 39924\n",
      "Data columns (total 13 columns):\n",
      "ZDEr_ID_REF_A              39925 non-null int64\n",
      "ZDEr_NOM                   39925 non-null object\n",
      "ZDEr_ID_TYPE_ARRET         39925 non-null int64\n",
      "ZDEr_LIBELLE_TYPE_ARRET    39925 non-null object\n",
      "ZDEr_X_Y                   39925 non-null object\n",
      "ZDLr_ID_REF_A              39925 non-null int64\n",
      "ZDLr_NOM                   39925 non-null object\n",
      "ZDLr_ID_TYPE_ARRET         39925 non-null int64\n",
      "ZDLr_LIBELLE_TYPE_ARRET    39925 non-null object\n",
      "LDA_ID_REF_A               39925 non-null int64\n",
      "LDA_NOM                    39925 non-null object\n",
      "LDA_ID_TYPE_ARRET          39925 non-null int64\n",
      "LDA_LIBELLE_TYPE_ARRET     39925 non-null object\n",
      "dtypes: int64(6), object(7)\n",
      "memory usage: 4.0+ MB\n"
     ]
    }
   ],
   "source": [
    "ref_stops.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ZDEr_ID_REF_A              39925\n",
       "ZDEr_NOM                   14500\n",
       "ZDEr_ID_TYPE_ARRET             4\n",
       "ZDEr_LIBELLE_TYPE_ARRET        4\n",
       "ZDEr_X_Y                   39763\n",
       "ZDLr_ID_REF_A              18412\n",
       "ZDLr_NOM                   13001\n",
       "ZDLr_ID_TYPE_ARRET             4\n",
       "ZDLr_LIBELLE_TYPE_ARRET        4\n",
       "LDA_ID_REF_A               15361\n",
       "LDA_NOM                    11379\n",
       "LDA_ID_TYPE_ARRET              4\n",
       "LDA_LIBELLE_TYPE_ARRET         4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ref_stops.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each row corresponds to a unique ZDE ID, but several ZDE IDs can share the same commercial name. \n",
    "\n",
    "There are no missing values.\n",
    "\n",
    "Data types are consistent.\n",
    "\n",
    "There seem to be 4 types of stops, whether accross ZDEs, ZDLs or LDAs. Let's look into it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Arrêt de bus            36695\n",
       "Station ferrée / Val     2049\n",
       "Station de métro          768\n",
       "Arrêt de tram             413\n",
       "Name: ZDEr_LIBELLE_TYPE_ARRET, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ref_stops['ZDEr_LIBELLE_TYPE_ARRET'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.F.b.  Stop Types: <a name=\"2Fb\"></a>\n",
    "\n",
    "> There are 4 types of stops:\n",
    "\n",
    "> - 5: Arrêt de bus (=bus stop)\n",
    "> - 1: Station ferrée / Val (=rail station)\n",
    "> - 2: Station de métro (=metro station)\n",
    "> - 6: Arrêt de tram (=tram stop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "ZDE_val_counts = ref_stops['ZDEr_ID_TYPE_ARRET'].value_counts()\n",
    "ZDL_val_counts = ref_stops['ZDLr_ID_TYPE_ARRET'].value_counts()\n",
    "LDA_val_counts = ref_stops['LDA_ID_TYPE_ARRET'].value_counts()\n",
    "\n",
    "ZDE_counts = pd.DataFrame(data=ZDE_val_counts.values, index=ZDE_val_counts.index, columns = ['ZDE'])\n",
    "ZDL_counts = pd.DataFrame(data=ZDL_val_counts.values, index=ZDL_val_counts.index, columns = ['ZDL'])\n",
    "LDA_counts = pd.DataFrame(data=LDA_val_counts.values, index=LDA_val_counts.index, columns = ['LDA'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>ZDE</th>\n",
       "      <th>ZDL</th>\n",
       "      <th>LDA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>36695</td>\n",
       "      <td>36691</td>\n",
       "      <td>31819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2049</td>\n",
       "      <td>2059</td>\n",
       "      <td>5065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>768</td>\n",
       "      <td>762</td>\n",
       "      <td>2208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>413</td>\n",
       "      <td>413</td>\n",
       "      <td>833</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ZDE    ZDL    LDA\n",
       "5  36695  36691  31819\n",
       "1   2049   2059   5065\n",
       "2    768    762   2208\n",
       "6    413    413    833"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ids_counts = pd.concat([ZDE_counts, ZDL_counts, LDA_counts], axis=1)\n",
    "\n",
    "ids_counts "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of value counts per type of stops is not the same accross all of the ID columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>LDA_ID_REF_A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LDA_ID_TYPE_ARRET</th>\n",
       "      <th>ZDLr_ID_TYPE_ARRET</th>\n",
       "      <th>ZDEr_ID_TYPE_ARRET</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">1</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">1</th>\n",
       "      <th>1</th>\n",
       "      <td>2049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>2</th>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>5</th>\n",
       "      <td>2884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>6</th>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2</th>\n",
       "      <th>2</th>\n",
       "      <th>2</th>\n",
       "      <td>684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>5</th>\n",
       "      <td>1471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>6</th>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>5</th>\n",
       "      <th>5</th>\n",
       "      <td>31819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">6</th>\n",
       "      <th>5</th>\n",
       "      <th>5</th>\n",
       "      <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>6</th>\n",
       "      <td>316</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                         LDA_ID_REF_A\n",
       "LDA_ID_TYPE_ARRET ZDLr_ID_TYPE_ARRET ZDEr_ID_TYPE_ARRET              \n",
       "1                 1                  1                           2049\n",
       "                                     2                              6\n",
       "                                     5                              4\n",
       "                  2                  2                             78\n",
       "                  5                  5                           2884\n",
       "                  6                  6                             44\n",
       "2                 2                  2                            684\n",
       "                  5                  5                           1471\n",
       "                  6                  6                             53\n",
       "5                 5                  5                          31819\n",
       "6                 5                  5                            517\n",
       "                  6                  6                            316"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gbo_ids = ref_stops.groupby(['LDA_ID_TYPE_ARRET', 'ZDLr_ID_TYPE_ARRET', 'ZDEr_ID_TYPE_ARRET'])\n",
    "\n",
    "gp_ids_counts = pd.DataFrame(gbo_ids.count()['LDA_ID_REF_A'])\n",
    "\n",
    "gp_ids_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.F.c. Obervations: <a name=\"2Fc\"></a>\n",
    "\n",
    "This dataset does not need to be cleaned since we will use it in a fairly superficial way. \n",
    "\n",
    "There are no missing values. Data types are consistent.\n",
    "\n",
    "It contains 13 columns, all containing identification codes along with the names of the stops.\n",
    "\n",
    "There are 3 identification levels: LDA, ZDL and ZDE. Each LDA can contain several ZDLS that themselves contain a number of ZDEs. Each of these IDs has 4 stop types: metro, rail, bus and tram.\n",
    "\n",
    "This dataset does not contain internal STIF codes (CODE_STIF_RES for example).\n",
    "\n",
    "Each row corresponds to a unique ZDE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.G. Geographical data <a name=\"2G\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_stops = pd.read_csv('../../datasets/emplacement-des-gares-idf-data-generalisee.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Geo Point</th>\n",
       "      <th>Geo Shape</th>\n",
       "      <th>id_ref_zdl</th>\n",
       "      <th>nom_long</th>\n",
       "      <th>label</th>\n",
       "      <th>idrefliga</th>\n",
       "      <th>idrefligc</th>\n",
       "      <th>res_com</th>\n",
       "      <th>mode_</th>\n",
       "      <th>fer</th>\n",
       "      <th>...</th>\n",
       "      <th>terrer</th>\n",
       "      <th>termetro</th>\n",
       "      <th>tertram</th>\n",
       "      <th>ternavette</th>\n",
       "      <th>terval</th>\n",
       "      <th>exploitant</th>\n",
       "      <th>principal</th>\n",
       "      <th>idf</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48.8463569889, 2.41947990037</td>\n",
       "      <td>{\"type\": \"Point\", \"coordinates\": [2.4194799003...</td>\n",
       "      <td>47247</td>\n",
       "      <td>SAINT-MANDE</td>\n",
       "      <td>Saint-Mandé</td>\n",
       "      <td>A01534</td>\n",
       "      <td>C01371</td>\n",
       "      <td>M1</td>\n",
       "      <td>Metro</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>RATP</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>657397.0779</td>\n",
       "      <td>6.860858e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48.8662858046, 2.32294341224</td>\n",
       "      <td>{\"type\": \"Point\", \"coordinates\": [2.3229434122...</td>\n",
       "      <td>45676</td>\n",
       "      <td>CONCORDE</td>\n",
       "      <td>Concorde</td>\n",
       "      <td>A01534 / A01541 / A01545</td>\n",
       "      <td>C01371 / C01378 / C01382</td>\n",
       "      <td>M1 / M8 / M12</td>\n",
       "      <td>Metro</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>RATP</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>650331.6676</td>\n",
       "      <td>6.863130e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48.8828686476, 2.34413063372</td>\n",
       "      <td>{\"type\": \"Point\", \"coordinates\": [2.3441306337...</td>\n",
       "      <td>42210</td>\n",
       "      <td>ANVERS</td>\n",
       "      <td>Anvers</td>\n",
       "      <td>A01535</td>\n",
       "      <td>C01372</td>\n",
       "      <td>M2</td>\n",
       "      <td>Metro</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>RATP</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>651901.2249</td>\n",
       "      <td>6.864961e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48.9078125468, 2.45435282652</td>\n",
       "      <td>{\"type\": \"Point\", \"coordinates\": [2.4543528265...</td>\n",
       "      <td>47334</td>\n",
       "      <td>JEAN ROSTAND</td>\n",
       "      <td>Jean Rostand</td>\n",
       "      <td>A01191</td>\n",
       "      <td>C01389</td>\n",
       "      <td>T1</td>\n",
       "      <td>Tramway</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>RATP</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>660003.4947</td>\n",
       "      <td>6.867673e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48.8930946286, 2.48791098763</td>\n",
       "      <td>{\"type\": \"Point\", \"coordinates\": [2.4879109876...</td>\n",
       "      <td>44603</td>\n",
       "      <td>LA REMISE A JORELLE</td>\n",
       "      <td>La Remise à Jorelle</td>\n",
       "      <td>A01761</td>\n",
       "      <td>C01843</td>\n",
       "      <td>T4</td>\n",
       "      <td>Tramway</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>SNCF</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>662452.6934</td>\n",
       "      <td>6.866020e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Geo Point  \\\n",
       "0  48.8463569889, 2.41947990037   \n",
       "1  48.8662858046, 2.32294341224   \n",
       "2  48.8828686476, 2.34413063372   \n",
       "3  48.9078125468, 2.45435282652   \n",
       "4  48.8930946286, 2.48791098763   \n",
       "\n",
       "                                           Geo Shape  id_ref_zdl  \\\n",
       "0  {\"type\": \"Point\", \"coordinates\": [2.4194799003...       47247   \n",
       "1  {\"type\": \"Point\", \"coordinates\": [2.3229434122...       45676   \n",
       "2  {\"type\": \"Point\", \"coordinates\": [2.3441306337...       42210   \n",
       "3  {\"type\": \"Point\", \"coordinates\": [2.4543528265...       47334   \n",
       "4  {\"type\": \"Point\", \"coordinates\": [2.4879109876...       44603   \n",
       "\n",
       "              nom_long                label                 idrefliga  \\\n",
       "0          SAINT-MANDE          Saint-Mandé                    A01534   \n",
       "1             CONCORDE             Concorde  A01534 / A01541 / A01545   \n",
       "2               ANVERS               Anvers                    A01535   \n",
       "3         JEAN ROSTAND         Jean Rostand                    A01191   \n",
       "4  LA REMISE A JORELLE  La Remise à Jorelle                    A01761   \n",
       "\n",
       "                  idrefligc        res_com    mode_  fer      ...       \\\n",
       "0                    C01371             M1    Metro    0      ...        \n",
       "1  C01371 / C01378 / C01382  M1 / M8 / M12    Metro    0      ...        \n",
       "2                    C01372             M2    Metro    0      ...        \n",
       "3                    C01389             T1  Tramway    0      ...        \n",
       "4                    C01843             T4  Tramway    0      ...        \n",
       "\n",
       "   terrer  termetro  tertram  ternavette  terval  exploitant principal idf  \\\n",
       "0       0         0        0           0       0        RATP         0   1   \n",
       "1       0         0        0           0       0        RATP         0   1   \n",
       "2       0         0        0           0       0        RATP         0   1   \n",
       "3       0         0        0           0       0        RATP         0   1   \n",
       "4       0         0        0           0       0        SNCF         0   1   \n",
       "\n",
       "             x             y  \n",
       "0  657397.0779  6.860858e+06  \n",
       "1  650331.6676  6.863130e+06  \n",
       "2  651901.2249  6.864961e+06  \n",
       "3  660003.4947  6.867673e+06  \n",
       "4  662452.6934  6.866020e+06  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo_stops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 923 entries, 0 to 922\n",
      "Data columns (total 28 columns):\n",
      "Geo Point     923 non-null object\n",
      "Geo Shape     923 non-null object\n",
      "id_ref_zdl    923 non-null int64\n",
      "nom_long      923 non-null object\n",
      "label         923 non-null object\n",
      "idrefliga     890 non-null object\n",
      "idrefligc     847 non-null object\n",
      "res_com       923 non-null object\n",
      "mode_         923 non-null object\n",
      "fer           923 non-null int64\n",
      "train         923 non-null int64\n",
      "rer           923 non-null int64\n",
      "metro         923 non-null int64\n",
      "tramway       923 non-null int64\n",
      "navette       923 non-null int64\n",
      "val           923 non-null int64\n",
      "terfer        923 non-null object\n",
      "tertrain      923 non-null object\n",
      "terrer        923 non-null object\n",
      "termetro      923 non-null object\n",
      "tertram       923 non-null object\n",
      "ternavette    923 non-null object\n",
      "terval        923 non-null object\n",
      "exploitant    923 non-null object\n",
      "principal     923 non-null int64\n",
      "idf           923 non-null int64\n",
      "x             923 non-null float64\n",
      "y             923 non-null float64\n",
      "dtypes: float64(2), int64(10), object(16)\n",
      "memory usage: 202.0+ KB\n"
     ]
    }
   ],
   "source": [
    "geo_stops.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "923"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo_stops['id_ref_zdl'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.G.a. Observations: <a name=\"2Ga\"></a>\n",
    "\n",
    "> This dataset contains the geo coordinates of all stations of the rail network in Ile de France, by ZDL, along with some other information that is of lesser use to us (13 columns in total). \n",
    "\n",
    "> The Geo Point column contains the geographical coordinates of each stop. We will have to split this column into columns containing latitudes and longitudes.\n",
    "\n",
    "> It contains 923 unique ZDLs (corresponding to the total number of rows). As we saw earlier, there are multiple ZDLs for one LDA.\n",
    "\n",
    "> The datasets we want to analyze (daily traffic and hourly profiles) only contain LDAs (301 unique values).\n",
    "\n",
    "> The only way to merge these dataframes with geo_stops is to match the stations names (column 'nom_long'). First, we will have to filter geo_stops in order to keep metro stations only."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.G.b. Next steps to clean geo_stops (geographical data): <a name=\"2Gb\"></a>\n",
    "\n",
    "> Filter metro stations\n",
    "\n",
    "> Match station names of geo_stops with that of other dataframes\n",
    "\n",
    "> Update column names and drop useless columns\n",
    "\n",
    "> Create latitude and longitude columns\n",
    " \n",
    "> Export to .csv"
   ]
  }
 ],
 "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
}