Signal Timing performance.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import csv\n",
    "from tabulate import tabulate\n",
    "import matplotlib.pylab as plt\n",
    "import matplotlib as mpl\n",
    "import pandas as pd\n",
    "import pandas.io.sql as pandasql\n",
    "import datetime\n",
    "from psycopg2 import connect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Setting up postgresql connection\n",
    "con = connect(database='rdumas',host='localhost',port='5433',user='rdumas')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Ostensibly prevents plotting when using the save_fig command, but I've seen no evidence of success\n",
    "#http://stackoverflow.com/a/15713545/4047679\n",
    "plt.ioff()\n",
    "plt.style.use('ggplot')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "'''\n",
    "Setting colours\n",
    "http://stackoverflow.com/a/37211181/4047679\n",
    "Blue: #004B85\n",
    "Red: #F2756D\n",
    "'''\n",
    "mpl.rcParams['axes.prop_cycle'] = mpl.cycler(color=['#004B85','#F2756D'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT dt_week::date, roadname as corridor, direction, period,tt_med as median_tt \"\\\n",
    "      \"FROM rdumas.signal_timing_perf_alldata \"\\\n",
    "      \"INNER JOIN rdumas.corridors_signal_timing_lookup USING (signal_timing_id) \"\\\n",
    "      \"WHERE daytype = 'Midweek' and period IN ('AMPK','PMPK') \"\\\n",
    "      \"ORDER BY roadname, direction, period, dt_week\"\n",
    "signals_perf = pandasql.read_sql(sql, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dirs = signals_perf.direction.unique()\n",
    "peaks = signals_perf.period.unique()\n",
    "corridors = pandasql.read_sql(\"SELECT roadname as corridor, direction FROM corridors_signal_timing_lookup\", con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Signal retiming periods\n",
    "retiming_dates = {'Markham Rd':dict(x1=datetime.date(2014,8,11), x2=datetime.date(2014,8,20), text='Signal\\nRetiming'),\n",
    "                  'Sheppard Ave':dict(x1=datetime.date(2014,7,22), x2=datetime.date(2014,7,25), text='Signal\\nRetiming')}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "'''Median Travel Time Plots'''\n",
    "ylim=[0,40]\n",
    "for corridor in signals_perf.corridor.unique():\n",
    "    fig, ax = plt.subplots(1,2, figsize=(16,4), sharey=True)\n",
    "    \n",
    "    for ax_c, pk in enumerate(peaks):\n",
    "        \n",
    "        for direction in corridors[corridors[\"corridor\"]==corridor].direction.sort_values():\n",
    "            signals_perf[(signals_perf[\"corridor\"]==corridor)\n",
    "                               & (signals_perf[\"direction\"]==direction)\n",
    "                               & (signals_perf[\"period\"]==pk)].plot(x='dt_week',\n",
    "                                                                          y='median_tt',\n",
    "                                                                          linewidth=2,\n",
    "                                                                          ax=ax[ax_c],\n",
    "                                                                          label=direction\n",
    "                                                                         )\n",
    "        ax[ax_c].set_title(pk)\n",
    "        ax[ax_c].set_autoscaley_on(False)\n",
    "        ax[ax_c].set_ylim(ylim)\n",
    "        ax[ax_c].axvspan(retiming_dates[corridor]['x1'], retiming_dates[corridor]['x2'], alpha=0.5, label=retiming_dates[corridor]['text'])\n",
    "        ax[ax_c].set_xlabel('Month')\n",
    "        ax[ax_c].xaxis.set_major_locator(mpl.dates.MonthLocator(bymonth=range(1,13,3)))\n",
    "    fig.suptitle(corridor, fontsize=16)\n",
    "    ax[0].set_ylabel('Median Travel Time (min)')\n",
    "    ax[0].legend().set_visible(False)\n",
    "    leg = ax[1].legend(loc='center right',bbox_to_anchor=(1.3,0.5))\n",
    "    for legobj in leg.legendHandles:\n",
    "        legobj.set_linewidth(6.0)\n",
    "    plt.gcf().autofmt_xdate()\n",
    "    plt.minorticks_off()\n",
    "    fig.savefig('corridors/signal_retiming/median_tt_plots/'+ corridor +'.png', format='png', transparent=True)\n",
    "    plt.close(fig)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Score 30 data only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT dt_week::date, roadname as corridor, direction, period,tt_med as median_tt \"\\\n",
    "      \"FROM rdumas.signal_timing_perf_score30 \"\\\n",
    "      \"INNER JOIN rdumas.corridors_signal_timing_lookup USING (signal_timing_id) \"\\\n",
    "      \"WHERE daytype = 'Midweek' and period IN ('AMPK','PMPK') \"\\\n",
    "      \"AND dt_week < '2016-01-01'::date \"\\\n",
    "      \"ORDER BY roadname, direction, period, dt_week\"\n",
    "signals_perf = pandasql.read_sql(sql, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dirs = signals_perf.direction.unique()\n",
    "peaks = signals_perf.period.unique()\n",
    "corridors = pandasql.read_sql(\"SELECT roadname as corridor, direction FROM corridors_signal_timing_lookup\", con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Signal retiming periods\n",
    "retiming_dates = {'Markham Rd':dict(x1=datetime.date(2014,8,11), x2=datetime.date(2014,8,20), text='Signal\\nRetiming'),\n",
    "                  'Sheppard Ave':dict(x1=datetime.date(2014,7,22), x2=datetime.date(2014,7,25), text='Signal\\nRetiming')}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "'''Median Travel Time Plots'''\n",
    "ylim=[0,40]\n",
    "for corridor in signals_perf.corridor.unique():\n",
    "    fig, ax = plt.subplots(1,2, figsize=(16,4), sharey=True)\n",
    "    \n",
    "    for ax_c, pk in enumerate(peaks):\n",
    "        \n",
    "        for direction in corridors[corridors[\"corridor\"]==corridor].direction.sort_values():\n",
    "            signals_perf[(signals_perf[\"corridor\"]==corridor)\n",
    "                               & (signals_perf[\"direction\"]==direction)\n",
    "                               & (signals_perf[\"period\"]==pk)].plot(x='dt_week',\n",
    "                                                                          y='median_tt',\n",
    "                                                                          linewidth=2,\n",
    "                                                                          ax=ax[ax_c],\n",
    "                                                                          label=direction\n",
    "                                                                         )\n",
    "        ax[ax_c].set_title(pk)\n",
    "        ax[ax_c].set_autoscaley_on(False)\n",
    "        ax[ax_c].set_ylim(ylim)\n",
    "        ax[ax_c].axvspan(retiming_dates[corridor]['x1'], retiming_dates[corridor]['x2'], alpha=0.5, label=retiming_dates[corridor]['text'])\n",
    "        ax[ax_c].set_xlabel('Month')\n",
    "        ax[ax_c].xaxis.set_major_locator(mpl.dates.MonthLocator(bymonth=range(1,13,3)))\n",
    "    fig.suptitle(corridor, fontsize=16)\n",
    "    ax[0].set_ylabel('Median Travel Time (min)')\n",
    "    ax[0].legend().set_visible(False)\n",
    "    leg = ax[1].legend(loc='center right',bbox_to_anchor=(1.3,0.5))\n",
    "    for legobj in leg.legendHandles:\n",
    "        legobj.set_linewidth(6.0)\n",
    "    plt.gcf().autofmt_xdate()\n",
    "    plt.minorticks_off()\n",
    "    fig.savefig('corridors/signal_retiming/median_tt_plots/'+ corridor +'score30.png', format='png', transparent=True)\n",
    "    plt.close(fig)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Before-After \n",
    "Generating tables and boxplots for a month of midweek days before and a month of midweek days after signal retiming"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 OVERNIGHT 00:00:00\n",
      "1 EARLYMORN 05:00:00\n",
      "2 AMPK 07:00:00\n",
      "3 AMSH 10:00:00\n",
      "4 PMSH 12:00:00\n",
      "5 PMPK 15:30:00\n",
      "6 EVENING 18:30:00\n",
      "7 NIGHT 21:00:00\n"
     ]
    }
   ],
   "source": [
    "time_periods = pandasql.read_sql(' SELECT period, lower(period_range) AS starttime, upper(period_range) AS endtime'\\\n",
    "                                 ' FROM ref.timeperiod_ranges'\\\n",
    "                                 ' GROUP BY period ORDER BY starttime', con)\n",
    "for idx, row in time_periods.iterrows():\n",
    "    print(idx, row['period'], row['starttime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "'''Printing tables to csv'''\n",
    "cur = con.cursor()\n",
    "for corridor, signal_dict in retiming_dates.items():\n",
    "    cur.execute('SELECT direction FROM corridors_signal_timing_lookup WHERE roadname = %(roadname)s', {'roadname':corridor})\n",
    "    directions = cur.fetchall()\n",
    "    \n",
    "    with open('corridors/signal_retiming/before-after-corridors.md', 'a') as f:\n",
    "            f.write('#{corridor} \\n'.format(corridor=corridor))\n",
    "            \n",
    "    for direction in directions:\n",
    "        \n",
    "        sql = cur.mogrify(\"\"\" WITH before AS(\n",
    "                    SELECT period, percentile_cont(0.5) WITHIN GROUP (ORDER BY corridor_tt) AS median_tt\n",
    "                    FROM rdumas.corridors_signal_timing_tt2\n",
    "                    INNER JOIN ref.timeperiod USING (time_15_continuous)\n",
    "                    INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt))\n",
    "                    INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id)\n",
    "                    WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday)\n",
    "                    AND roadname = %(corridor)s AND direction = %(direction)s\n",
    "                    AND dt <@ daterange( (%(startdate)s - INTERVAL '1 month')::DATE, %(startdate)s)\n",
    "                    GROUP BY period\n",
    "                ), after AS(\n",
    "                    SELECT period, percentile_cont(0.5) WITHIN GROUP (ORDER BY corridor_tt) AS median_tt\n",
    "                    FROM rdumas.corridors_signal_timing_tt2\n",
    "                    INNER JOIN ref.timeperiod USING (time_15_continuous)\n",
    "                    INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt))\n",
    "                    INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id)\n",
    "                    WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday)\n",
    "                    AND roadname = %(corridor)s AND direction = %(direction)s\n",
    "                    AND dt <@ daterange( %(enddate)s, (%(enddate)s + INTERVAL '1 month')::DATE)\n",
    "                    GROUP BY period\n",
    "                )\n",
    "                SELECT period,\n",
    "                    to_char(before.median_tt, 'FM90.0') AS \"Travel Time Before (min)\", \n",
    "                    to_char(after.median_tt, 'FM90.0') AS \"Travel Time After(min)\", \n",
    "                    to_char(100*(after.median_tt - before.median_tt)/before.median_tt,'FMSG990.0')||'%%' AS \"Percentage Change\"\n",
    "                FROM before\n",
    "                INNER JOIN after USING (period)\n",
    "                INNER JOIN ref.timeperiod_ranges USING (period)\n",
    "                ORDER BY period_range\"\"\",\n",
    "                          {'startdate':signal_dict['x1'], 'enddate':signal_dict['x2'], 'corridor':corridor, 'direction':direction[0]})\n",
    "        \n",
    "        data = pandasql.read_sql(sql.decode('utf-8'), con)\n",
    "        \n",
    "        with open('corridors/signal_retiming/before-after-corridors.md', 'a') as f:\n",
    "            f.write('##{direction}\\n'.format(direction=direction[0]))\n",
    "            f.write(tabulate(data, headers=\"keys\", tablefmt=\"pipe\"))\n",
    "            f.write('\\n')\n",
    "cur.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b' WITH before AS(\\n                    SELECT period, median(corridor_tt) AS median_tt\\n                    FROM rdumas.corridors_signal_timing_tt2\\n                    INNER JOIN ref.timeperiod USING (time_15_continuous)\\n                    INNER JOIN ref.daytypes ON (isodow = EXTRACT(\\'isodow\\' from dt))\\n                    INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id)\\n                    GROUP BY period\\n                    WHERE daytype = \\'Midweek\\' AND dt NOT IN (SELECT dt FROM ref.holiday)\\n                    AND roadname = \\'Sheppard Ave\\' AND direction = \\'Eastbound\\'\\n                    AND dt <@ daterange( (\\'2014-07-22\\'::date - INTERVAL \\'1 month\\')::DATE, \\'2014-07-22\\'::date)\\n                ), after AS(\\n                    SELECT period, median(corridor_tt) AS median_tt\\n                    FROM rdumas.corridors_signal_timing_tt2\\n                    INNER JOIN ref.timeperiod USING (time_15_continuous)\\n                    INNER JOIN ref.daytypes ON (isodow = EXTRACT(\\'isodow\\' from dt))\\n                    INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id)\\n                    GROUP BY period\\n                    WHERE daytype = \\'Midweek\\' AND dt NOT IN (SELECT dt FROM ref.holiday)\\n                    AND roadname = \\'Sheppard Ave\\' AND direction = \\'Eastbound\\'\\n                    AND dt <@ daterange( (\\'2014-07-25\\'::date - INTERVAL \\'1 month\\')::DATE, \\'2014-07-25\\'::date)\\n                )\\n                SELECT period,\\n                    before.median_tt AS \"Travel Time Before (min)\", \\n                    after.median_tt AS \"Travel Time After(min)\", \\n                    to_char(100*(after.median_tt - before.median_tt)/before.median_tt),\\'FMSG990.0\\')||\\'%\\' AS \"Percentage Change\"\\n                FROM before\\n                INNER JOIN after USING (period)\\n                INNER JOIN ref.timeperiod_ranges\\n                ORDER BY period_range'\n"
     ]
    }
   ],
   "source": [
    "print(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sheppard Ave Eastbound\n",
      "Sheppard Ave Westbound\n",
      "Markham Rd Northbound\n",
      "Markham Rd Southbound\n"
     ]
    }
   ],
   "source": [
    "cur = con.cursor()\n",
    "for corridor, signal_dict in retiming_dates.items():\n",
    "    cur.execute('SELECT direction FROM corridors_signal_timing_lookup WHERE roadname = %(roadname)s', {'roadname':corridor})\n",
    "    directions = cur.fetchall()\n",
    "    for direction in directions:\n",
    "        print(corridor, direction[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "con.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Box Plots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''Testing Whisker Plot'''\n",
    "corridor = 'Markham Rd'\n",
    "signal_dict = retiming_dates['Markham Rd']\n",
    "\n",
    "time_periods = pand\n",
    "\n",
    "fig, ax = plt.subplots(1,2, figsize=(16,4), sharey=True)\n",
    "data_sql = \"SELECT period, roadname as corridor, direction, corridor_tt, \"\\\n",
    "                \"CASE WHEN dt < %(startdate)s THEN 'Before' \"\\\n",
    "                \"WHEN dt > %(enddate)s THEN 'After' \"\\\n",
    "                \"END as signal_period \"\\\n",
    "                \"FROM rdumas.corridors_signal_timing_tt2 \"\\\n",
    "                \"INNER JOIN ref.timeperiod USING (time_15_continuous) \"\\\n",
    "                \"INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt)) \"\\\n",
    "                \"INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id) \"\\\n",
    "                \"WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday) \"\\\n",
    "                \"AND roadname = %(corridor)s \"\\\n",
    "                \"AND (dt <@ daterange( (%(startdate)s - INTERVAL '1 month')::DATE, %(startdate)s) \"\\\n",
    "                \"OR dt <@ daterange(%(enddate)s,(%(enddate)s + INTERVAL '1 month')::DATE))\"\n",
    "whisker_data = pandasql.read_sql(data_sql, con, \n",
    "                                 params={'startdate':signal_dict['x1'], 'enddate':signal_dict['x2'], 'corridor':corridor})\n",
    "directions = whisker_data.direction.unique()\n",
    "\n",
    "for ax_c, dir in enumerate(directions):\n",
    "    \n",
    "    \n",
    "    ax[ax_c].boxplot()\n",
    "    whisker_data[]\n",
    "\n",
    "plt.boxplot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-52-e64ddcbece4e>, line 2)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-52-e64ddcbece4e>\"\u001b[1;36m, line \u001b[1;32m2\u001b[0m\n\u001b[1;33m    data_sql = \"SELECT period, roadname as corridor, direction, corridor_tt, \"                \"CASE WHEN dt < %(startdate)s THEN 'Before' \"                \"WHEN dt > %(enddate)s THEN 'After' \"                \"END as signal_period \"                \"FROM rdumas.corridors_signal_timing_tt2 \"                \"INNER JOIN ref.timeperiod USING (time_15_continuous) \"                \"INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt)) \"                \"INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id) \"                WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday)\u001b[0m\n\u001b[1;37m                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "for corridor, signal_dict in retiming_dates.items():\n",
    "    data_sql = \"SELECT period, roadname as corridor, direction, corridor_tt, \"\\\n",
    "                \"CASE WHEN dt < %(startdate)s THEN 'Before' \"\\\n",
    "                \"WHEN dt > %(enddate)s THEN 'After' \"\\\n",
    "                \"END as signal_period \"\\\n",
    "                \"FROM rdumas.corridors_signal_timing_tt2 \"\\\n",
    "                \"INNER JOIN ref.timeperiod USING (time_15_continuous) \"\\\n",
    "                \"INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt)) \"\\\n",
    "                \"INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id) \"\\\n",
    "                \"WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday) \"\\\n",
    "                \"AND roadname = %(corridor)s \"\\\n",
    "                \"AND (dt <@ daterange( (%(startdate)s - INTERVAL '1 month')::DATE, %(startdate)s) \"\\\n",
    "                \"OR dt <@ daterange(%(enddate)s,(%(enddate)s + INTERVAL '1 month')::DATE))\"\n",
    "    whisker_date = pandasql.read_sql(data_sql, con, \n",
    "                                     params={'startdate':signal_dict['x1'], 'enddate':signal_dict['x2'], 'corridor':corridor})\n",
    "    dic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\"\""
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}