Utilization-Report-MySQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from icalendar import *\n",
    "from datetime import date, datetime, timedelta\n",
    "from __future__ import print_function\n",
    "import mysql.connector\n",
    "from mysql.connector import errorcode\n",
    "import pickle\n",
    "import csv\n",
    "import pandas\n",
    "from pandas.io import sql\n",
    "import matplotlib.pyplot as plt\n",
    "import xlsxwriter\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "import glob\n",
    "import pytz\n",
    "import calendar_parser as cp \n",
    "# for calendar_parser, I downloaded the Python file created for this package\n",
    "# https://github.com/oblique63/Python-GoogleCalendarParser/blob/master/calendar_parser.py\n",
    "# and saved it in the working directory with my Python file (Jupyter Notebook file). \n",
    "# In calendar_parser.py, their function _fix_timezone is very crucial for my code to \n",
    "# display the correct local time. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Establish Connection with MySQL (optional approach)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "USER = # enter database username\n",
    "PASS = # enter database password\n",
    "HOST = # enter hostname, e.g. '127.0.0.1'\n",
    "cnx = mysql.connector.connect(user=USER, password=PASS, host=HOST)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor = cnx.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Approach / Code modified from MySQL Connector web page\n",
    "DB_NAME = \"CalDb\"\n",
    "\n",
    "# 1) Creates database if it doesn't already exist\n",
    "# 2) Then connects to the database\n",
    "def create_database(cursor):\n",
    "    try:\n",
    "        cursor.execute(\n",
    "            \"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'\".format(DB_NAME))\n",
    "    except mysql.connector.Error as err:\n",
    "        print(\"Failed creating database: {}\".format(err))\n",
    "        exit(1)\n",
    "\n",
    "try:\n",
    "    cnx.database = DB_NAME    \n",
    "except mysql.connector.Error as err:\n",
    "    if err.errno == errorcode.ER_BAD_DB_ERROR:\n",
    "        create_database(cursor)\n",
    "        cnx.database = DB_NAME\n",
    "    else:\n",
    "        print(err)\n",
    "        exit(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create table specifications\n",
    "TABLES = {}\n",
    "TABLES['eBike'] = (\n",
    "    \"CREATE TABLE IF NOT EXISTS `eBike` (\"\n",
    "    \"  `eBikeName` varchar(10),\"\n",
    "    \"  `Organizer` varchar(100),\"\n",
    "    \"  `Created` datetime NOT NULL,\"\n",
    "    \"  `Start` datetime NOT NULL,\"\n",
    "    \"  `End` datetime NOT NULL\"\n",
    "    \") ENGINE=InnoDB\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating table eBike: OK\n"
     ]
    }
   ],
   "source": [
    "# If table does not already exist, this code will create it based on specifications\n",
    "for name, ddl in TABLES.iteritems():\n",
    "    try:\n",
    "        print(\"Creating table {}: \".format(name), end='')\n",
    "        cursor.execute(ddl)\n",
    "    except mysql.connector.Error as err:\n",
    "        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:\n",
    "            print(\"already exists.\")\n",
    "        else:\n",
    "            print(err.msg)\n",
    "    else:\n",
    "        print(\"OK\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Obtain current count from each calendar to read in and add additional entries only\n",
    "cursor.execute(\"SELECT COUNT(*) FROM eBike WHERE eBikeName = 'Gold'\")\n",
    "GoldExistingCount = cursor.fetchall()\n",
    "\n",
    "cursor.execute(\"SELECT COUNT(*) FROM eBike WHERE eBikeName = 'Blue'\")\n",
    "BlueExistingCount = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#print(GoldExistingCount[0][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read in Calendar Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Declare lists\n",
    "eBikeName = []\n",
    "Organizer = []\n",
    "DTcreated = []\n",
    "DTstart = []\n",
    "DTend = []\n",
    "Counter = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Open first e-bike calendar, appends data, then repeats for second calendar. \n",
    "# A future modification I am working on is to bring this into one loop such that \n",
    "# as many calendars as desired for a specific table can be read in from one folder. \n",
    "#\n",
    "# Additionally, I plan to look into potential for using the .ics url to read in\n",
    "# calendar data so that the file does not need to be updated each time this code\n",
    "# is run for analysis of calendar data. \n",
    "b = open('Gold.ics','rb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cal = Calendar.from_ical(b.read())\n",
    "\n",
    "timezones = cal.walk('VTIMEZONE')\n",
    "\n",
    "for k in cal.walk():\n",
    "    if k.name == \"VEVENT\":\n",
    "        Counter = Counter + 1\n",
    "        if Counter > GoldExistingCount[0][0]:\n",
    "            eBikeName.append('Gold')\n",
    "            Organizer.append( re.sub(r'mailto:', \"\", str(k.get('ORGANIZER') ) ) )\n",
    "            DTcreated.append( cp._fix_timezone( k.decoded('CREATED'), pytz.timezone(timezones[0]['TZID']) ) )\n",
    "            DTstart.append( cp._fix_timezone( k.decoded('DTSTART'), pytz.timezone(timezones[0]['TZID']) ) )\n",
    "            DTend.append( cp._fix_timezone( k.decoded('DTEND'), pytz.timezone(timezones[0]['TZID']) ) )\n",
    "\n",
    "b.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Resetting 'Counter' to 0 and opening next calendar...\n",
    "Counter = 0\n",
    "b = open('Blue.ics','rb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cal = Calendar.from_ical(b.read())\n",
    "\n",
    "timezones = cal.walk('VTIMEZONE')\n",
    "\n",
    "for k in cal.walk():\n",
    "    if k.name == \"VEVENT\":\n",
    "        Counter = Counter + 1\n",
    "        if Counter > BlueExistingCount[0][0]:\n",
    "            eBikeName.append('Blue')\n",
    "            Organizer.append( re.sub(r'mailto:', \"\", str(k.get('ORGANIZER') ) ) )\n",
    "            DTcreated.append( cp._fix_timezone( k.decoded('CREATED'), pytz.timezone(timezones[0]['TZID']) ) )\n",
    "            DTstart.append( cp._fix_timezone( k.decoded('DTSTART'), pytz.timezone(timezones[0]['TZID']) ) )\n",
    "            DTend.append( cp._fix_timezone( k.decoded('DTEND'), pytz.timezone(timezones[0]['TZID']) ) )\n",
    "\n",
    "b.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Now that calendar data is fully read in, create a list with data in a format for \n",
    "# entering into the MySQL database. \n",
    "# \n",
    "# At this point, if the MySQL Connector component is not desired, other approaches  \n",
    "# include creating a Pandas dataframe or something else.\n",
    "# For reference, a Pandas dataframe could be created with the following command: \n",
    "# df = pandas.DataFrame({'ORGANIZER' : Organizer,'CREATED' : DTcreated, 'DTSTART' : DTstart,'DTEND': DTend})\n",
    "eBikeData = []\n",
    "for i in range(len(DTcreated)):\n",
    "    eBikeData.append((eBikeName[i], Organizer[i], DTcreated[i], DTstart[i], DTend[i]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MySQL and Excel Connection for Desired Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Insert calendar data into MySQL table eBike\n",
    "cursor.executemany(\"INSERT INTO eBike (eBikeName, Organizer, Created, Start, End) VALUES (%s, %s, %s, %s, %s)\", \n",
    "                   eBikeData)\n",
    "cnx.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Emails for Weekly E-Bike Use Survey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Find emails associated with reservations created at latest 7 days ago\n",
    "\n",
    "cursor.execute(\"SELECT DISTINCT Organizer FROM eBike WHERE DATEDIFF(CURDATE(), Start) <= 7 AND DATEDIFF(CURDATE(), Start) >= 0\")\n",
    "WeeklyEmail = cursor.fetchall()\n",
    "Email = []\n",
    "for i in range(len(WeeklyEmail)):\n",
    "    Email.append(WeeklyEmail[i][0])\n",
    "    if(Email[i] != 'None'):\n",
    "        print(Email[i])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 415,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 415,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# https://xlsxwriter.readthedocs.org\n",
    "# Workbook Document Name\n",
    "workbook = xlsxwriter.Workbook('E-BikeUpdate' + datetime.strftime(datetime.now(), \"%Y-%m-%d\") + '.xlsx')\n",
    "\n",
    "# Define 'bold' format\n",
    "bold = workbook.add_format({'bold': True})\n",
    "format1 = workbook.add_format({'bold': 1,\n",
    "                               'bg_color': '#3CDAE5',\n",
    "                               'font_color': '#092A51'})\n",
    "format2 = workbook.add_format({'bold': 1,\n",
    "                               'bg_color': '#DA7BD0',\n",
    "                               'font_color': '#A50202'})\n",
    "\n",
    "# Add Intro Sheet\n",
    "worksheet = workbook.add_worksheet('INTRO')\n",
    "worksheet.write('A1', 'Sheet', bold)\n",
    "worksheet.write('A2', 'Ebike_Rides_by_User')\n",
    "worksheet.write('A3', 'Trips_by_Res_Time')\n",
    "worksheet.write('A4', 'Trips_by_Weekday')\n",
    "worksheet.write('A5', 'Utilization')\n",
    "worksheet.write('A6', 'Aggregate_Advance_Reservation')\n",
    "worksheet.write('A7', 'Time_Series_Advance_Reservation')\n",
    "\n",
    "worksheet.write('B1', 'Description', bold)\n",
    "worksheet.write('B2', 'Total E-Bike Rides by User Email')\n",
    "worksheet.write('B3', 'Total E-Bike Rides by Reservation Hour')\n",
    "worksheet.write('B4', 'Total E-Bike Rides by Weekday')\n",
    "worksheet.write('B5', 'Average and Maximum Percent and Hours Utilization')\n",
    "worksheet.write('B6', 'Number of Days E-Bikes Were Reserved in Advance, by Count of Reservations')\n",
    "worksheet.write('B7', 'Number of Days E-Bikes Were Reserved in Advance, by Reservation Start Datetime')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total e-Bike Rides by User"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT Organizer, COUNT(*) AS Total_Rides FROM eBike GROUP BY Organizer ORDER BY Total_Rides DESC;\")\n",
    "TotalRides_by_User = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 417,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Worksheet Name\n",
    "worksheet1 = workbook.add_worksheet('Ebike_Rides_by_User')\n",
    "\n",
    "# Column Names\n",
    "worksheet1.write('A1', 'User', bold)\n",
    "worksheet1.write('B1', 'Total Rides', bold)\n",
    "\n",
    "# Declare Starting Point for row, col\n",
    "row = 1\n",
    "col = 0\n",
    "\n",
    "# Iterate over the data and write it out row by row\n",
    "for UserEmail, UserRideCount in (TotalRides_by_User):\n",
    "    worksheet1.write(row, col,     UserEmail)\n",
    "    worksheet1.write(row, col + 1, UserRideCount)\n",
    "    row += 1\n",
    "\n",
    "# Conditional Formatting: E-bike Users with 20+ Rides\n",
    "worksheet1.conditional_format('B1:B9999', {'type':     'cell',\n",
    "                                        'criteria': '>=',\n",
    "                                        'value':    20,\n",
    "                                        'format':   format1})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total Trips by Reservation Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 418,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT EXTRACT(HOUR FROM Start) AS Hour_24, DATE_FORMAT(Start, '%h %p') AS Reservation_Time, COUNT(*) AS Total_Rides FROM eBike GROUP BY Reservation_Time, Hour_24 ORDER BY Hour_24 ASC\")\n",
    "Trips_by_Time = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 419,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Worksheet Name\n",
    "worksheet2 = workbook.add_worksheet('Trips_by_Res_Time')  # Data.\n",
    "\n",
    "# Column Names\n",
    "worksheet2.write('A1', 'Reservation Time', bold)\n",
    "worksheet2.write('B1', 'Total Rides', bold)\n",
    "\n",
    "# Declare Starting Point for row, col\n",
    "row = 1\n",
    "col = 0\n",
    "\n",
    "# Iterate over the data and write it out row by row\n",
    "for Hour_24, Reservation_Time, Total_Rides in (Trips_by_Time):\n",
    "    worksheet2.write(row, col,     Reservation_Time)\n",
    "    worksheet2.write(row, col + 1, Total_Rides)\n",
    "    row += 1\n",
    "    \n",
    "# Add Chart\n",
    "chart = workbook.add_chart({'type': 'line'})\n",
    "\n",
    "# Add Data to Chart\n",
    "chart.add_series({\n",
    "    'categories': '=Trips_by_Res_Time!$A$2:$A$16',\n",
    "    'values':     '=Trips_by_Res_Time!$B$2:$B$16',\n",
    "    'fill':       {'color': '#791484'},\n",
    "    'border':     {'color': '#52B7CB'}\n",
    "})\n",
    "\n",
    "# Format Chart\n",
    "chart.set_title({\n",
    "    'name': 'Total Rides by Reservation Time',\n",
    "    'name_font': {\n",
    "        'name': 'Calibri',\n",
    "        'color': '#52B7CB',\n",
    "    },\n",
    "})\n",
    "\n",
    "chart.set_x_axis({\n",
    "    'name': 'Reservation Time',\n",
    "    'empty_cells': 'gaps',\n",
    "    'name_font': {\n",
    "        'name': 'Calibri',\n",
    "        'color': '#52B7CB'\n",
    "    },\n",
    "    'num_font': {\n",
    "        'name': 'Arial',\n",
    "        'color': '#52B7CB',\n",
    "    },\n",
    "})\n",
    "\n",
    "chart.set_y_axis({\n",
    "    'name': 'Total Rides',\n",
    "    'empty_cells': 'gaps',\n",
    "    'name_font': {\n",
    "        'name': 'Calibri',\n",
    "        'color': '#52B7CB'\n",
    "    },\n",
    "    'num_font': {\n",
    "        'italic': True,\n",
    "        'color': '#52B7CB',\n",
    "    },\n",
    "})\n",
    "\n",
    "# Remove Legend\n",
    "chart.set_legend({'position': 'none'})\n",
    "\n",
    "# Insert Chart\n",
    "worksheet2.insert_chart('E1', chart)\n",
    "\n",
    "# GO TO END OF DATA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total Trips by Weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 420,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT DAYNAME(Start) AS Weekday, COUNT(*) AS Total_Rides FROM eBike GROUP BY Weekday ORDER BY FIELD(Weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY')\")\n",
    "Trips_by_Weekday = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 421,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Worksheet Name\n",
    "worksheet3 = workbook.add_worksheet('Trips_by_Weekday')\n",
    "\n",
    "# Column Names\n",
    "worksheet3.write('A1', 'Weekday', bold)\n",
    "worksheet3.write('B1', 'Total Rides', bold)\n",
    "\n",
    "# Declare Starting Point for row, col\n",
    "row = 1\n",
    "col = 0\n",
    "\n",
    "# Iterate over the data and write it out row by row\n",
    "for Weekday, Total_Rides_by_Weekday in (Trips_by_Weekday):\n",
    "    worksheet3.write(row, col,     Weekday)\n",
    "    worksheet3.write(row, col + 1, Total_Rides_by_Weekday)\n",
    "    row += 1\n",
    "    \n",
    "# Add Chart\n",
    "chart = workbook.add_chart({'type': 'line'})\n",
    "\n",
    "# Add Data to Chart\n",
    "chart.add_series({\n",
    "    'categories': '=Trips_by_Weekday!$A$2:$A$8)',\n",
    "    'values':     '=Trips_by_Weekday!$B$2:$B$8)',\n",
    "    'fill':       {'color': '#791484'},\n",
    "    'border':     {'color': '#52B7CB'}\n",
    "})\n",
    "\n",
    "# Format Chart\n",
    "chart.set_title({\n",
    "    'name': 'Total Rides by Weekday',\n",
    "    'name_font': {\n",
    "        'name': 'Calibri',\n",
    "        'color': '#52B7CB',\n",
    "    },\n",
    "})\n",
    "\n",
    "chart.set_x_axis({\n",
    "    'name': 'Weekday',\n",
    "    'name_font': {\n",
    "        'name': 'Calibri',\n",
    "        'color': '#52B7CB'\n",
    "    },\n",
    "    'num_font': {\n",
    "        'name': 'Arial',\n",
    "        'color': '#52B7CB',\n",
    "    },\n",
    "})\n",
    "\n",
    "chart.set_y_axis({\n",
    "    'name': 'Total Rides',\n",
    "    'name_font': {\n",
    "        'name': 'Calibri',\n",
    "        'color': '#52B7CB'\n",
    "    },\n",
    "    'num_font': {\n",
    "        'italic': True,\n",
    "        'color': '#52B7CB',\n",
    "    },\n",
    "})\n",
    "\n",
    "# Remove Legend\n",
    "chart.set_legend({'position': 'none'})\n",
    "\n",
    "# Insert Chart\n",
    "worksheet3.insert_chart('E1', chart)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Average and Maximum Hours and Percent Utilization by Weekday"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 422,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT DAYNAME(Start) AS Weekday, MAX((HOUR(End - Start)*60 + MINUTE(End - Start))/60) AS Max_Hours, (MAX((HOUR(End - Start)*60 + MINUTE(End - Start))/60)/8)*100 AS Max_PCT_Utilization, AVG((HOUR(End - Start)*60 + MINUTE(End - Start))/60) AS Avg_Hours, (AVG((HOUR(End - Start)*60 + MINUTE(End - Start))/60)/8)*100 AS Avg_PCT_Utilization FROM eBike GROUP BY Weekday ORDER BY FIELD(Weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY')\")\n",
    "Avg_Max_Hours_PCTutilization_by_Weekday = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 423,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Worksheet Name\n",
    "worksheet4 = workbook.add_worksheet('Utilization')\n",
    "\n",
    "# Column Names\n",
    "worksheet4.write('A1', 'Weekday', bold)\n",
    "worksheet4.write('B1', 'Maximum Reservation Duration (hrs)', bold)\n",
    "worksheet4.write('C1', 'Maximum Percentage Utilization', bold)\n",
    "worksheet4.write('D1', 'Average Reservation Duration (hrs)', bold)\n",
    "worksheet4.write('E1', 'Average Percent Utilization', bold)\n",
    "\n",
    "# Declare Starting Point for row, col\n",
    "row = 1\n",
    "col = 0\n",
    "\n",
    "# Iterate over the data and write it out row by row\n",
    "for Weekday_AMH, Max_Hours, Max_PCT_Utilization, Avg_Hours, Avg_PCT_Utilization in (Avg_Max_Hours_PCTutilization_by_Weekday):\n",
    "    worksheet4.write(row, col,     Weekday_AMH)\n",
    "    worksheet4.write(row, col + 1, Max_Hours)\n",
    "    worksheet4.write(row, col + 2, Max_PCT_Utilization)\n",
    "    worksheet4.write(row, col + 3, Avg_Hours)\n",
    "    worksheet4.write(row, col + 4, Avg_PCT_Utilization)\n",
    "    row += 1\n",
    "    \n",
    "# Conditional Formatting: Percent Utilization Greater Than 50\n",
    "worksheet4.conditional_format('E2:E8', {'type':     'cell',\n",
    "                                        'criteria': '>=',\n",
    "                                        'value':    30,\n",
    "                                        'format':   format1})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How far in Advance Reservations are Created"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 424,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# How far in advance reservations are created\n",
    "cursor.execute(\"SELECT DATEDIFF(Start, Created) AS Days_Advance_Reservation, COUNT(*) AS Number_Reserved_Trips FROM eBike WHERE DATEDIFF(Start, Created) >= 0 GROUP BY Days_Advance_Reservation ORDER BY Days_Advance_Reservation DESC\")\n",
    "Advance_Reservation = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 425,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Worksheet Name\n",
    "worksheet5 = workbook.add_worksheet('Aggregate_Advance_Reservation')\n",
    "\n",
    "# Column Names\n",
    "worksheet5.write('A1', 'Days E-Bike was Reserved Ahead of Time', bold)\n",
    "worksheet5.write('B1', 'Total Reservations', bold)\n",
    "\n",
    "# Declare Starting Point for row, col\n",
    "row = 1\n",
    "col = 0\n",
    "\n",
    "# Iterate over the data and write it out row by row\n",
    "for Days_Advance_Reservation, Number_Reserved_Trips in (Advance_Reservation):\n",
    "    worksheet5.write(row, col,     Days_Advance_Reservation)\n",
    "    worksheet5.write(row, col + 1, Number_Reserved_Trips)\n",
    "    row += 1\n",
    "    \n",
    "worksheet5.conditional_format('B2:B9999', {'type':     'cell',\n",
    "                                        'criteria': '>=',\n",
    "                                        'value':    5,\n",
    "                                        'format':   format2})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 426,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Time series of how far in advance reservations are created\n",
    "cursor.execute(\"SELECT Start, DATEDIFF(Start, Created) AS Days_Advance_Reservation FROM eBike WHERE DATEDIFF(Start, Created) > 0 ORDER BY Start ASC\")\n",
    "Time_Series_Advance_Reservation = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 432,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Starts = []\n",
    "for i in range(0, len(Time_Series_Advance_Reservation)): \n",
    "    Starts.append(str(Time_Series_Advance_Reservation[i][0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 433,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Worksheet Name\n",
    "worksheet6 = workbook.add_worksheet('Time_Series_Advance_Reservation')\n",
    "\n",
    "# Column Names\n",
    "worksheet6.write('A1', 'Reservation Start Date', bold)\n",
    "worksheet6.write('B1', 'Days E-Bike was Reserved Ahead of Time', bold)\n",
    "\n",
    "# Declare Starting Point for row, col\n",
    "row = 1\n",
    "col = 0\n",
    "\n",
    "# Iterate over the data and write it out row by row\n",
    "for StartVal in Starts:\n",
    "    worksheet6.write(row, col, StartVal)\n",
    "    row += 1\n",
    "\n",
    "row = 1\n",
    "for Start, Days_Advance_Reservation in (Time_Series_Advance_Reservation):\n",
    "    worksheet6.write(row, col + 1, Days_Advance_Reservation)\n",
    "    row += 1\n",
    "    \n",
    "# Add Chart\n",
    "chart = workbook.add_chart({'type': 'line'})\n",
    "\n",
    "worksheet6.conditional_format('B2:B9999', {'type':     'cell',\n",
    "                                        'criteria': '>=',\n",
    "                                        'value':    5,\n",
    "                                        'format':   format2})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 434,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "workbook.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cnx.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}