14. Optimization of Employee Shuttle Stops.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import googlemaps\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>address</th>\n",
       "      <th>employee_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>98 Edinburgh St, San Francisco, CA 94112, USA</td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>237 Accacia St, Daly City, CA 94014, USA</td>\n",
       "      <td>2081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1835 Folsom St, San Francisco, CA 94103, USA</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>170 Cambridge St, San Francisco, CA 94134, USA</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16 Roanoke St, San Francisco, CA 94131, USA</td>\n",
       "      <td>1863</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          address  employee_id\n",
       "0   98 Edinburgh St, San Francisco, CA 94112, USA          206\n",
       "1        237 Accacia St, Daly City, CA 94014, USA         2081\n",
       "2    1835 Folsom St, San Francisco, CA 94103, USA          178\n",
       "3  170 Cambridge St, San Francisco, CA 94134, USA           50\n",
       "4     16 Roanoke St, San Francisco, CA 94131, USA         1863"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "address = pd.read_csv('./data/Bus/Employee_Addresses.csv')\n",
    "address.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2191 entries, 0 to 2190\n",
      "Data columns (total 2 columns):\n",
      "address        2191 non-null object\n",
      "employee_id    2191 non-null int64\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 34.3+ KB\n"
     ]
    }
   ],
   "source": [
    "address.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Street_One</th>\n",
       "      <th>Street_Two</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MISSION ST</td>\n",
       "      <td>ITALY AVE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MISSION ST</td>\n",
       "      <td>NEW MONTGOMERY ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MISSION ST</td>\n",
       "      <td>01ST ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MISSION ST</td>\n",
       "      <td>20TH ST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MISSION ST</td>\n",
       "      <td>FREMONT ST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Street_One         Street_Two\n",
       "0  MISSION ST          ITALY AVE\n",
       "1  MISSION ST  NEW MONTGOMERY ST\n",
       "2  MISSION ST            01ST ST\n",
       "3  MISSION ST            20TH ST\n",
       "4  MISSION ST         FREMONT ST"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops = pd.read_csv('./data/Bus/Potentail_Bust_Stops.csv')\n",
    "stops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 119 entries, 0 to 118\n",
      "Data columns (total 2 columns):\n",
      "Street_One    119 non-null object\n",
      "Street_Two    119 non-null object\n",
      "dtypes: object(2)\n",
      "memory usage: 1.9+ KB\n"
     ]
    }
   ],
   "source": [
    "stops.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get unique address and stop information\n",
    "unique_address = list(set(address['address']))\n",
    "\n",
    "def merge_stop(x):\n",
    "    return x[0] + ' & ' + x[1] + ' CA'\n",
    "\n",
    "unique_stop = list(set(stops.apply(merge_stop, axis=1)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['206 Lisbon St, San Francisco, CA 94112, USA',\n",
       " '40 Everson St, San Francisco, CA 94131, USA',\n",
       " '2635 Scott St, San Francisco, CA 94123, USA',\n",
       " '475 Bosworth St, San Francisco, CA 94112, USA',\n",
       " '122 Rousseau St, San Francisco, CA 94112, USA',\n",
       " '398 Hampshire St, San Francisco, CA 94110, USA',\n",
       " '900 Persia Ave, San Francisco, CA 94112, USA',\n",
       " '2885 Diamond St, San Francisco, CA 94131, USA',\n",
       " '98 Lisbon St, San Francisco, CA 94112, USA',\n",
       " '599 S Van Ness Ave, San Francisco, CA 94110, USA']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_address[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['MISSION ST & RANDALL ST CA',\n",
       " 'MISSION ST & LOWELL ST CA',\n",
       " 'MISSION ST & NIAGARA AVE CA',\n",
       " 'MISSION ST & FREMONT ST CA',\n",
       " 'MISSION ST & ANGELOS ALY CA',\n",
       " 'MISSION ST & 19TH ST CA',\n",
       " 'MISSION ST & ROLPH ST CA',\n",
       " 'MISSION ST & BRAZIL AVE CA',\n",
       " 'MISSION ST & LASKIE ST CA',\n",
       " 'MISSION ST & JESSIE EAST ST CA']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_stop[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Distance Measure"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, I am using the Google Distance Matrix API for origin and destination walking distance measure. The GitHub link for google-maps-services-python can be found at: [https://github.com/googlemaps/google-maps-services-python](https://github.com/googlemaps/google-maps-services-python).\n",
    "\n",
    "Since this is a charged service, I only show one query for demo purpose. After query the distance, I believe the next step is pretty clear."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# start google API with your KEY (you can register at Google.com)\n",
    "gmaps = googlemaps.Client(key='*************************************')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Origin:\t\t 206 Lisbon St, San Francisco, CA 94112, USA\n",
      "Destination:\t MISSION ST & RANDALL ST CA\n"
     ]
    }
   ],
   "source": [
    "# defind origin and destination\n",
    "origin = unique_address[0]\n",
    "destination = unique_stop[0]\n",
    "\n",
    "print('Origin:\\t\\t', origin)\n",
    "print('Destination:\\t', destination)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'destination_addresses': ['Mission St & Randall St, San Francisco, CA 94110, USA'],\n",
       " 'origin_addresses': ['206 Lisbon St, San Francisco, CA 94112, USA'],\n",
       " 'rows': [{'elements': [{'distance': {'text': '1.9 km', 'value': 1947},\n",
       "     'duration': {'text': '25 mins', 'value': 1487},\n",
       "     'status': 'OK'}]}],\n",
       " 'status': 'OK'}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query walking distance between origins and destinations\n",
    "result = gmaps.distance_matrix(origins=origin, destinations=destination, \n",
    "                               mode='walking')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reference Solution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is another solution online, you can refer to [Link](https://github.com/stasi009/TakeHomeDataChallenges/blob/master/14.ShuttleStops/shuttle_stops.py)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}