Bycicles in Paris.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Context\" data-toc-modified-id=\"Context-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Context</a></span><ul class=\"toc-item\"><li><span><a href=\"#Objectives\" data-toc-modified-id=\"Objectives-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Objectives</a></span></li><li><span><a href=\"#Questions-to-answer\" data-toc-modified-id=\"Questions-to-answer-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Questions to answer</a></span></li></ul></li><li><span><a href=\"#Importing-the-datasets\" data-toc-modified-id=\"Importing-the-datasets-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Importing the datasets</a></span></li><li><span><a href=\"#Data-Cleaning\" data-toc-modified-id=\"Data-Cleaning-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Data Cleaning</a></span><ul class=\"toc-item\"><li><span><a href=\"#Traffic\" data-toc-modified-id=\"Traffic-3.1\"><span class=\"toc-item-num\">3.1&nbsp;&nbsp;</span>Traffic</a></span><ul class=\"toc-item\"><li><span><a href=\"#Missing-Values\" data-toc-modified-id=\"Missing-Values-3.1.1\"><span class=\"toc-item-num\">3.1.1&nbsp;&nbsp;</span>Missing Values</a></span></li><li><span><a href=\"#Extreme-Values/Outliers\" data-toc-modified-id=\"Extreme-Values/Outliers-3.1.2\"><span class=\"toc-item-num\">3.1.2&nbsp;&nbsp;</span>Extreme Values/Outliers</a></span></li></ul></li><li><span><a href=\"#Bike-lanes\" data-toc-modified-id=\"Bike-lanes-3.2\"><span class=\"toc-item-num\">3.2&nbsp;&nbsp;</span>Bike lanes</a></span><ul class=\"toc-item\"><li><span><a href=\"#Missing-values\" data-toc-modified-id=\"Missing-values-3.2.1\"><span class=\"toc-item-num\">3.2.1&nbsp;&nbsp;</span>Missing values</a></span></li></ul></li><li><span><a href=\"#Accidents\" data-toc-modified-id=\"Accidents-3.3\"><span class=\"toc-item-num\">3.3&nbsp;&nbsp;</span>Accidents</a></span><ul class=\"toc-item\"><li><span><a href=\"#Data-Cleaning\" data-toc-modified-id=\"Data-Cleaning-3.3.1\"><span class=\"toc-item-num\">3.3.1&nbsp;&nbsp;</span>Data Cleaning</a></span></li></ul></li></ul></li><li><span><a href=\"#Visualizing-Data\" data-toc-modified-id=\"Visualizing-Data-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Visualizing Data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Evolution-of-traffic\" data-toc-modified-id=\"Evolution-of-traffic-4.1\"><span class=\"toc-item-num\">4.1&nbsp;&nbsp;</span>Evolution of traffic</a></span></li><li><span><a href=\"#Seasonality\" data-toc-modified-id=\"Seasonality-4.2\"><span class=\"toc-item-num\">4.2&nbsp;&nbsp;</span>Seasonality</a></span></li><li><span><a href=\"#Comparing-Traffic-during-the-week\" data-toc-modified-id=\"Comparing-Traffic-during-the-week-4.3\"><span class=\"toc-item-num\">4.3&nbsp;&nbsp;</span>Comparing Traffic during the week</a></span></li><li><span><a href=\"#Comparing-Arrondissements\" data-toc-modified-id=\"Comparing-Arrondissements-4.4\"><span class=\"toc-item-num\">4.4&nbsp;&nbsp;</span>Comparing Arrondissements</a></span><ul class=\"toc-item\"><li><span><a href=\"#What-are-the-Arrondissements-with-most-traffic,-on-average?\" data-toc-modified-id=\"What-are-the-Arrondissements-with-most-traffic,-on-average?-4.4.1\"><span class=\"toc-item-num\">4.4.1&nbsp;&nbsp;</span>What are the Arrondissements with most traffic, on average?</a></span></li></ul></li><li><span><a href=\"#Accidents\" data-toc-modified-id=\"Accidents-4.5\"><span class=\"toc-item-num\">4.5&nbsp;&nbsp;</span>Accidents</a></span></li></ul></li><li><span><a href=\"#Get-postal-code-of-every-'compteur'---Google-Maps-API\" data-toc-modified-id=\"Get-postal-code-of-every-'compteur'---Google-Maps-API-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Get postal code of every 'compteur' - Google Maps API</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Context"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Objectives\n",
    "Analyse the number of cyclists in Paris\n",
    "\n",
    "* Quantify the rise of cyclists in Paris\n",
    "    * Get data from traffic, accidents\n",
    "    * Bike lane construction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Questions to answer\n",
    "* Correlate the accidents with time of day, condition of the road, gender\n",
    "* What are the arrondissements with most traffic?\n",
    "* Did Covid affect traffic, before, during, and after?\n",
    "* What is the time of day and day of the week with most traffic? Is it the same in every arrondissement?\n",
    "* Is the traffic seasonal?\n",
    "* Is the increase in car traffic leading to more bike accidents?\n",
    "* Is the increase bikes lanes helping in the drecrease of bike accidents?\n",
    "* Is the increase of bike traffic leading to more bike lanes? And in which areas?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing the datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from urllib.request import urlopen\n",
    "import zipfile, io\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from matplotlib.pyplot import figure\n",
    "import numpy as np\n",
    "\n",
    "%matplotlib inline\n",
    "sns.set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing the accidents dataset - Imports with no issues\n",
    "accidents = pd.read_csv('https://www.data.gouv.fr/en/datasets/r/3d5f2317-5afd-4a9f-a9c5-bd4fe0113f39', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing traffic file of 2018\n",
    "url = 'https://www.data.gouv.fr/en/datasets/r/58d6b982-4c70-4648-afe4-b80eab61d28d'\n",
    "archive = zipfile.ZipFile(io.BytesIO(urlopen(url).read())) # Takes some time\n",
    "csv_path = '2018_comptage-velo-donnees-compteurs.csv' # The desired csv file in the archive\n",
    "traffic_2018 = pd.read_csv(io.BytesIO(archive.read(csv_path)), sep = ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing traffic file of 2019\n",
    "url = 'https://www.data.gouv.fr/en/datasets/r/9c23d147-4032-429c-9c18-86dabd53e63f'\n",
    "archive = zipfile.ZipFile(io.BytesIO(urlopen(url).read())) # Takes some time\n",
    "csv_path = '2019_comptage-velo-donnees-compteurs-2.csv' # The desired csv file in the archive\n",
    "traffic_2019 = pd.read_csv(io.BytesIO(archive.read(csv_path)), sep = ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing the traffic dataset last 12M - Might take a while to download\n",
    "last_traffic = pd.read_csv('https://media.githubusercontent.com/media/tmcdonald92/Projects/master/Statistical%20Analysis%20of%20the%20Bike%20Traffic%20in%20Paris/comptage-velo-donnees-compteurs.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filtering the dataset to only have data from 2020\n",
    "last_traffic=last_traffic.loc[last_traffic['Date et heure de comptage'].str[:4]=='2020']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing the bike lanes dataset\n",
    "bike_lanes = pd.read_csv('https://www.data.gouv.fr/en/datasets/r/1211e838-4b77-4ee4-9567-03d78d55f0bf', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing the temporary bike lanes dataset \n",
    "\n",
    "temp=pd.read_csv('https://media.githubusercontent.com/media/tmcdonald92/Projects/master/Statistical%20Analysis%20of%20the%20Bike%20Traffic%20in%20Paris/deconfinement-pistes-cyclables-temporaires.csv', sep=';')\n",
    "temp=temp.loc[temp.Statut=='Réalisé']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Traffic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Append the traffic datasets\n",
    "total_traffic=traffic_2018.append([traffic_2019,last_traffic])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing the postal codes\n",
    "postal_codes=pd.read_csv('https://media.githubusercontent.com/media/tmcdonald92/Projects/master/Statistical%20Analysis%20of%20the%20Bike%20Traffic%20in%20Paris/localisations.csv', sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "postal_codes=postal_codes.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merging the postal codes in the traffic dataset\n",
    "total_traffic=total_traffic.merge(postal_codes, how='left', on='Identifiant du compteur')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding the year, month, day and hour columns\n",
    "total_traffic['Year']=total_traffic['Date et heure de comptage'].str[:4]\n",
    "total_traffic['Month']=total_traffic['Date et heure de comptage'].str[5:7]\n",
    "total_traffic['Day']=total_traffic['Date et heure de comptage'].str[8:10]\n",
    "total_traffic['Hour']=total_traffic['Date et heure de comptage'].str[11:13]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding day of week column. It is necessary a column with the date in datetime format first\n",
    "total_traffic['Date']=total_traffic['Date et heure de comptage'].str[:10]\n",
    "total_traffic.Date=pd.to_datetime(total_traffic.Date)\n",
    "total_traffic['num_week']=total_traffic.Date.dt.dayofweek+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unnecessary columns from traffic dataset\n",
    "columns_to_drop=['Nom du compteur_x',\n",
    "                 'Identifiant du site de comptage',\n",
    "                 'Nom du site de comptage',\n",
    "                'Lien vers photo du site de comptage',\n",
    "                'Coordonnées géographiques_x',\n",
    "                'Nom du compteur_y',\n",
    "                'Coordonnées géographiques_y',\n",
    "                'Date et heure de comptage']\n",
    "\n",
    "total_traffic.drop(columns=columns_to_drop, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding week days & months names\n",
    "#wk_days = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday']\n",
    "#months=['first','January','February','March','April','May','June','July','August','September','October','November','December']\n",
    "\n",
    "#total_traffic['day_week']=total_traffic['num_week'].apply(lambda x: wk_days[x])\n",
    "total_traffic['Month']=total_traffic['Month'].apply(lambda x: int(x))\n",
    "#total_traffic['month_name'] = total_traffic['Date'].dt.strftime('%b')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get arrondissements from postal code and remove all postal codes not starting with 75\n",
    "\n",
    "total_traffic['Postal code']=total_traffic['Postal code'].apply(lambda x: str(x))\n",
    "\n",
    "total_traffic=total_traffic.loc[total_traffic['Postal code'].str[:2]=='75']\n",
    "\n",
    "total_traffic['Arrondissement']=total_traffic['Postal code'].str[-2:]\n",
    "\n",
    "total_traffic['Arrondissement']=total_traffic['Arrondissement'].apply(lambda x: int(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changing data types\n",
    "total_traffic=total_traffic.astype({'Hour':'int32'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_traffic.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are no missing values in the traffic dataset ;)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extreme Values/Outliers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to check if there any extreme values in the traffic dataset that could affect future calculations like the mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_traffic[['Comptage horaire']].boxplot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The boxplot shows us that there are many outliers with a value higher than 200. But let's check the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_traffic.sort_values(by='Comptage horaire', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The values don't seem to be outliers. It actually explains the increase in traffic, especially after the end of confinement. \n",
    "7 of the top 10 records of traffic were in September 2020."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bike lanes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bike_lanes.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unnecessary columns from bike_lanes dataset\n",
    "\n",
    "cols_to_drop = ['Aménagement bidirectionnel',\n",
    "                'Régime de vitesse',\n",
    "                'Sens vélo',\n",
    "                'Bois',\n",
    "                'Longueur du tronçon en km',\n",
    "                'Position aménagement',\n",
    "                'Circulation générale interdite',\n",
    "                'Piste',\n",
    "                'Couloir bus',\n",
    "                'Continuité cyclable',\n",
    "                'Réseau cyclable',\n",
    "                'geo_shape'] \n",
    "\n",
    "bike_lanes.drop(columns=cols_to_drop, inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill missing values for delivery dates \n",
    "\n",
    "bike_lanes['Date de livraison']=bike_lanes.sort_values(by='geo_point_2d')['Date de livraison'].fillna(method='bfill').fillna(method='ffill')\n",
    "\n",
    "# Get the number of permanent bike lanes delivered since 2018\n",
    "\n",
    "qqq=bike_lanes.groupby('Date de livraison').count()['Longueur du tronçon en m'].reset_index()\n",
    "\n",
    "paaa=qqq.loc[qqq['Date de livraison'].str[:4].isin(['2018','2019','2020'])]\n",
    "\n",
    "# Get the number of temporary bike lanes delivered since the end of confinement\n",
    "\n",
    "fff=temp[['Date de réalisation','Longueur']].groupby('Date de réalisation').count().reset_index()\n",
    "fff.rename(columns={'Date de réalisation':'Date de livraison'},inplace=True)\n",
    "\n",
    "# Get the right datatypes, add a column to compute the running total including all bike lanes done before 2018\n",
    "\n",
    "fff['Date de livraison']=pd.to_datetime(fff['Date de livraison'])\n",
    "paaa['Date de livraison']=pd.to_datetime(paaa['Date de livraison'])\n",
    "\n",
    "b=paaa.merge(fff,'outer','Date de livraison')\n",
    "\n",
    "b['Longueur du tronçon en m']=b['Longueur du tronçon en m'].fillna(b['Longueur'])\n",
    "\n",
    "b.drop(columns='Longueur',inplace=True)\n",
    "\n",
    "b['Running total']=b['Longueur du tronçon en m'].cumsum()\n",
    "\n",
    "b['Running total']=b['Running total']+8453\n",
    "\n",
    "# Create a DF with every date of the period \n",
    "\n",
    "from datetime import datetime\n",
    "\n",
    "a=pd.date_range(start=\"2018-01-01\",end=\"2020-09-15\")\n",
    "cal=pd.DataFrame(a)\n",
    "cal.rename(columns={0:'Date de livraison'},inplace=True)\n",
    "\n",
    "# Merge it all and export it to csv to plot it in Tableau\n",
    "\n",
    "blfinal=cal.merge(b,'outer','Date de livraison').fillna(method='ffill')\n",
    "\n",
    "blfinal.to_csv('blfinal.csv',index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bike_lanes.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "29 streets missing, but not an issue because we have their arrondissement "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accidents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Filter the accidents dataframe to only have records in Paris\n",
    "accidents_paris = accidents.loc[(accidents.departement=='75')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate the dataframe by date, hour and commune before merging with the traffic dataframe\n",
    "accidents_paris = accidents_paris.pivot_table(index=['date','heure','commune'], \n",
    "                                            values='identifiant accident', \n",
    "                                            aggfunc='count').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the arrondissement from the commune column\n",
    "accidents_paris['Arrondissement']=accidents_paris.commune.str[-2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change data types\n",
    "accidents_paris=accidents_paris.astype({'Arrondissement': 'int64', 'date': 'datetime64[ns]', 'heure': 'int32'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename the columns\n",
    "accidents_paris=accidents_paris.rename(columns={'identifiant accident':'total_accidents','heure':'Hour','date':'Date'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "accidents_paris['month_year']=accidents_paris.Date.dt.strftime('%Y-%m')\n",
    "accidents_paris['Year']=accidents_paris.Date.dt.strftime('%Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter the dataframe to not have accidents from 2018 because the year is not complete\n",
    "accidents_paris = accidents_paris.loc[accidents_paris.Year != '2018']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare the accidents with the surface of each arrondissement\n",
    "surface=pd.read_html('https://fr.wikipedia.org/wiki/Arrondissements_de_Paris')[3].droplevel(0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surface=surface[['Arr.','Superficie(ha)']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surface['Arr.']=surface.index+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surface.drop(index=[20,21,22], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Visualizing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evolution of traffic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_traffic_date = total_traffic[['Comptage horaire','Date']].groupby(by=['Date'], as_index=False).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.lineplot(data=total_traffic_date, x=\"Date\", y=\"Comptage horaire\")\n",
    "plt.xlabel('Date', size= 16)\n",
    "plt.ylabel('Number of bikes', size=16)\n",
    "plt.title('Evolution of Traffic', size=26)\n",
    "plt.savefig('traffic_evolution.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see from the graph that there is a tendency on the rise of traffic between 2018 and 2020"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Seasonality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_traffic_month = total_traffic[['Comptage horaire','Year','Month']].groupby(by=['Year','Month']).mean()\n",
    "total_traffic_month = total_traffic_month.unstack().T.droplevel(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.lineplot(data=total_traffic_month, marker=\"o\")\n",
    "plt.xlabel('Month', size= 16)\n",
    "plt.ylabel('Number of bikes/Hour', size=16)\n",
    "plt.title('Average Number of bikes by Month and Year', size=26)\n",
    "plt.savefig('traffic_month.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we can see that there is a seasonality in the average traffic, for example in the month of August. Also, we can see the impact that COVID had due to the confinement, which lead to a drop in traffic in April."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparing Traffic during the week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_traffic_week = total_traffic[['Comptage horaire','Year','num_week']].groupby(by=['Year','num_week']).mean()\n",
    "total_traffic_week = total_traffic_week.unstack().T.droplevel(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.lineplot(data=total_traffic_week, marker=\"o\")\n",
    "plt.xlabel('Week', size= 16)\n",
    "plt.ylabel('Number of bikes/hour', size=16)\n",
    "plt.title('Average number of bikes by Week and Year', size=26)\n",
    "plt.savefig('traffic_week.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This shows that habits haven't from 2018 to 2020. We can see that the traffic decreases on the weekends, which also leads to the conclusion that most people use their bikes to commute, not for leisure."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparing Arrondissements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What are the Arrondissements with most traffic, on average?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_arrondissement = total_traffic[['Comptage horaire','Arrondissement']].groupby(by=['Arrondissement'], as_index=False).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.barplot(x=\"Comptage horaire\"\n",
    "            ,y=\"Arrondissement\"\n",
    "            , data=traffic_arrondissement\n",
    "            ,orient='h'\n",
    "            ,order=traffic_arrondissement.sort_values('Comptage horaire', ascending=False).Arrondissement\n",
    "            ,palette=\"rocket\"\n",
    "           );\n",
    "plt.xlabel('Number of bikes/hour', size= 16)\n",
    "plt.ylabel('Arrondissement', size=16)\n",
    "plt.title('Average number of bikes (hour) by Arrondissement', size=26)\n",
    "plt.savefig('traffic_arrondissement.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we can see that the 2nd arrondissement is the arrondissment with the most traffic per hour, on average.\n",
    "But is it the one with most traffic every hour of the day, and every day of the week?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_arrondissement_hour = total_traffic[['Comptage horaire','Arrondissement','Hour']].groupby(by=['Arrondissement','Hour']).mean()\n",
    "traffic_arrondissement_hour = traffic_arrondissement_hour.unstack().T.droplevel(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.lineplot(data=traffic_arrondissement_hour, dashes=False);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_arrondissement_week = total_traffic[['Comptage horaire','Arrondissement','num_week']].groupby(by=['Arrondissement','num_week']).mean()\n",
    "traffic_arrondissement_week = traffic_arrondissement_week.unstack().T.droplevel(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.lineplot(data=traffic_arrondissement_week, dashes=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_arrondissement_month = total_traffic[['Comptage horaire','Arrondissement','Month']].groupby(by=['Arrondissement','Month']).mean()\n",
    "traffic_arrondissement_month = traffic_arrondissement_month.unstack().T.droplevel(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.lineplot(data=traffic_arrondissement_month, dashes=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accidents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents_hour=accidents_paris[['Hour','total_accidents']].groupby(by='Hour', as_index=False).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(15, 10))\n",
    "ax = sns.barplot(data=accidents_hour, x=\"Hour\", y=\"total_accidents\", color=\"tomato\");\n",
    "plt.xlabel('Hour of the Day', size= 16)\n",
    "plt.ylabel('Number of Bike Accidents', size=16)\n",
    "plt.title('Number of Accidents during the Day (2005-2017)', size=26)\n",
    "\n",
    "for bar in ax.patches:\n",
    "    if bar.get_height() > 620:\n",
    "        bar.set_color('r')    \n",
    "    else:\n",
    "        bar.set_color('grey')\n",
    "plt.savefig('traffic_hour.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see from the graph the times of day where more bike accidents happened are during rush hour:\n",
    " * Between 8 and 9 a.m.\n",
    " * Between 17 and 19 p.m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents_arrondissement=accidents_paris[['Arrondissement','total_accidents']].groupby(by='Arrondissement', as_index=False).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(20, 10))\n",
    "sns.barplot(x=\"total_accidents\"\n",
    "            ,y=\"Arrondissement\"\n",
    "            , data=accidents_arrondissement\n",
    "            ,orient='h'\n",
    "            ,order=accidents_arrondissement.sort_values('total_accidents', ascending=False).Arrondissement\n",
    "            ,palette=\"rocket\"\n",
    "           );\n",
    "plt.xlabel('Total Accidents', size= 16)\n",
    "plt.ylabel('Arrondissement', size=16)\n",
    "plt.title('Total Accidents by Arrondissement (2005-2017)', size=26)\n",
    "plt.savefig('accidents_arrondissement.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents_arrondissement=accidents_paris[['Arrondissement','total_accidents']].groupby(by='Arrondissement', as_index=False).sum()\n",
    "accidents_arrondissement=accidents_arrondissement.merge(surface, how='left', left_on='Arrondissement', right_on='Arr.' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents_arrondissement=accidents_arrondissement.astype({'Superficie(ha)':'int64'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "figure(figsize=(30, 20))\n",
    "sns.lmplot(x='Superficie(ha)', y='total_accidents', data=accidents_arrondissement);\n",
    "plt.xlabel('Surface (ha)', size= 16)\n",
    "plt.ylabel('Total Accidents', size=16)\n",
    "#plt.title('Correlation between Surface of the Arrondissement and the Total Accidents', size=16)\n",
    "plt.savefig('correlation.png')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents_arrondissement_year=accidents_paris[['Arrondissement','Year','total_accidents']].groupby(by=['Arrondissement','Year'], as_index=False).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.catplot(data=accidents_arrondissement_year\n",
    "            , x='total_accidents'\n",
    "            , y='Arrondissement'\n",
    "            , kind='bar'\n",
    "            , col='Year'\n",
    "            , col_wrap=4\n",
    "            , orient='h'\n",
    "            , color='r'\n",
    "           )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get postal code of every 'compteur' - Google Maps API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#qqq=traffic.pivot_table(index='Identifiant du compteur', values='Coordonnées géographiques', aggfunc='head')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#qqq=qqq['Coordonnées géographiques'].str.split(',',expand=True)\n",
    "#qqq=qqq.applymap(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import googlemaps\n",
    "#from datetime import datetime\n",
    "\n",
    "#gmaps = googlemaps.Client(key='Key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#postal_codes=qqq.apply(lambda x: gmaps.reverse_geocode((x[0],x[1]))[0]['address_components'][-1]['long_name'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#postal_codes=pd.DataFrame(postal_codes, columns=['Postal code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#localisations=traffic.merge(postal_codes,left_index=True, right_index=True)\n",
    "#localisations=localisations[['Identifiant du compteur','Nom du compteur','Coordonnées géographiques','Postal code']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#localisations.to_csv('localisations.csv',sep=',',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}