Report03_Popular_Stations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Further explorations on the Citibike data\n",
    "- Find out which stations are popular among subscribers and non-subscribers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "#import seaborn as sns\n",
    "from sklearn.linear_model import LinearRegression, RANSACRegressor\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import datasets\n",
    "d1 = pd.read_csv('../data/processed/extended2/p_s_alltime_extended.csv')\n",
    "d2 = pd.read_csv('../data/processed/extended2/p_ns_alltime_extended.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# data wrangling\n",
    "d1['usertype'] = 'subscriber'\n",
    "d2['usertype'] = 'customer'\n",
    "d1 = d1[d1['AWND'] >= 0]\n",
    "d2 = d2[d2['AWND'] >= 0]\n",
    "data = pd.concat([d1, d2], axis=0)\n",
    "del data['Unnamed: 0']\n",
    "del data['DATE']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'pickups', 'st_id', 'st_name', 'st_latitude', 'st_longitude',\n",
       "       'closest_college', 'closest_college_distance', 'closest_subway',\n",
       "       'closest_subway_distance', 'closest_theater',\n",
       "       'closest_theater_distance', 'closest_museum', 'closest_museum_distance',\n",
       "       'closest_park_name', 'closest_park_distance', 'PRCP', 'SNOW', 'SNWD',\n",
       "       'TMAX', 'TMIN', 'AWND', 'usertype'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>pickups</th>\n",
       "      <th>st_id</th>\n",
       "      <th>st_name</th>\n",
       "      <th>st_latitude</th>\n",
       "      <th>st_longitude</th>\n",
       "      <th>closest_college</th>\n",
       "      <th>closest_college_distance</th>\n",
       "      <th>closest_subway</th>\n",
       "      <th>closest_subway_distance</th>\n",
       "      <th>...</th>\n",
       "      <th>closest_museum_distance</th>\n",
       "      <th>closest_park_name</th>\n",
       "      <th>closest_park_distance</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>AWND</th>\n",
       "      <th>usertype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20130701</td>\n",
       "      <td>47</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>American Academy Mcallister Institute Of Funer...</td>\n",
       "      <td>0.449863</td>\n",
       "      <td>8th Ave &amp; 50th St at NW corner</td>\n",
       "      <td>0.836766</td>\n",
       "      <td>...</td>\n",
       "      <td>0.648424</td>\n",
       "      <td>De Witt Clinton Park</td>\n",
       "      <td>0.190527</td>\n",
       "      <td>21.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>22.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20130701</td>\n",
       "      <td>73</td>\n",
       "      <td>79</td>\n",
       "      <td>Franklin St &amp; W Broadway</td>\n",
       "      <td>40.719116</td>\n",
       "      <td>-74.006667</td>\n",
       "      <td>Borough Of Manhattan Comm College (Cuny)</td>\n",
       "      <td>0.438224</td>\n",
       "      <td>West Broadway &amp; Franklin St at SW corner</td>\n",
       "      <td>0.012754</td>\n",
       "      <td>...</td>\n",
       "      <td>0.470931</td>\n",
       "      <td>GREENSTREET</td>\n",
       "      <td>0.154135</td>\n",
       "      <td>21.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>22.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20130701</td>\n",
       "      <td>13</td>\n",
       "      <td>82</td>\n",
       "      <td>St James Pl &amp; Pearl St</td>\n",
       "      <td>40.711174</td>\n",
       "      <td>-74.000165</td>\n",
       "      <td>Pace University / New York City Campus</td>\n",
       "      <td>0.390559</td>\n",
       "      <td>Centre St &amp; Chambers St at SE corner</td>\n",
       "      <td>0.372382</td>\n",
       "      <td>...</td>\n",
       "      <td>0.590027</td>\n",
       "      <td>James Madison Plaza</td>\n",
       "      <td>0.143915</td>\n",
       "      <td>21.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>22.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20130701</td>\n",
       "      <td>19</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>Long Island University / Brooklyn Campus</td>\n",
       "      <td>0.970719</td>\n",
       "      <td>4th Ave &amp; Pacific St at NE corner</td>\n",
       "      <td>0.176488</td>\n",
       "      <td>...</td>\n",
       "      <td>0.224781</td>\n",
       "      <td>Brooklyn Academy Of Music</td>\n",
       "      <td>0.108015</td>\n",
       "      <td>21.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>22.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>subscriber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20130701</td>\n",
       "      <td>47</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>New School University / Jazz And Contemporary ...</td>\n",
       "      <td>0.717327</td>\n",
       "      <td>8th Ave &amp; 16th St at NW corner</td>\n",
       "      <td>0.062889</td>\n",
       "      <td>...</td>\n",
       "      <td>0.368229</td>\n",
       "      <td>Dr. Gertrude B. Kelly Playground</td>\n",
       "      <td>0.170808</td>\n",
       "      <td>21.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>22.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>subscriber</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  pickups  st_id                        st_name  st_latitude  \\\n",
       "0  20130701       47     72               W 52 St & 11 Ave    40.767272   \n",
       "1  20130701       73     79       Franklin St & W Broadway    40.719116   \n",
       "2  20130701       13     82         St James Pl & Pearl St    40.711174   \n",
       "3  20130701       19     83  Atlantic Ave & Fort Greene Pl    40.683826   \n",
       "4  20130701       47    116                W 17 St & 8 Ave    40.741776   \n",
       "\n",
       "   st_longitude                                    closest_college  \\\n",
       "0    -73.993929  American Academy Mcallister Institute Of Funer...   \n",
       "1    -74.006667           Borough Of Manhattan Comm College (Cuny)   \n",
       "2    -74.000165             Pace University / New York City Campus   \n",
       "3    -73.976323           Long Island University / Brooklyn Campus   \n",
       "4    -74.001497  New School University / Jazz And Contemporary ...   \n",
       "\n",
       "   closest_college_distance                            closest_subway  \\\n",
       "0                  0.449863            8th Ave & 50th St at NW corner   \n",
       "1                  0.438224  West Broadway & Franklin St at SW corner   \n",
       "2                  0.390559      Centre St & Chambers St at SE corner   \n",
       "3                  0.970719         4th Ave & Pacific St at NE corner   \n",
       "4                  0.717327            8th Ave & 16th St at NW corner   \n",
       "\n",
       "   closest_subway_distance     ...     closest_museum_distance  \\\n",
       "0                 0.836766     ...                    0.648424   \n",
       "1                 0.012754     ...                    0.470931   \n",
       "2                 0.372382     ...                    0.590027   \n",
       "3                 0.176488     ...                    0.224781   \n",
       "4                 0.062889     ...                    0.368229   \n",
       "\n",
       "                  closest_park_name closest_park_distance  PRCP SNOW  SNWD  \\\n",
       "0              De Witt Clinton Park              0.190527  21.3  0.0   0.0   \n",
       "1                       GREENSTREET              0.154135  21.3  0.0   0.0   \n",
       "2               James Madison Plaza              0.143915  21.3  0.0   0.0   \n",
       "3         Brooklyn Academy Of Music              0.108015  21.3  0.0   0.0   \n",
       "4  Dr. Gertrude B. Kelly Playground              0.170808  21.3  0.0   0.0   \n",
       "\n",
       "   TMAX  TMIN  AWND    usertype  \n",
       "0  25.0  22.2   1.4  subscriber  \n",
       "1  25.0  22.2   1.4  subscriber  \n",
       "2  25.0  22.2   1.4  subscriber  \n",
       "3  25.0  22.2   1.4  subscriber  \n",
       "4  25.0  22.2   1.4  subscriber  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>pickups</th>\n",
       "      <th>st_id</th>\n",
       "      <th>st_latitude</th>\n",
       "      <th>st_longitude</th>\n",
       "      <th>closest_college_distance</th>\n",
       "      <th>closest_subway_distance</th>\n",
       "      <th>closest_theater_distance</th>\n",
       "      <th>closest_museum_distance</th>\n",
       "      <th>closest_park_distance</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>AWND</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>927184.00</td>\n",
       "      <td>927184.00</td>\n",
       "      <td>927184.00</td>\n",
       "      <td>926658.00</td>\n",
       "      <td>926658.00</td>\n",
       "      <td>926658.00</td>\n",
       "      <td>926658.00</td>\n",
       "      <td>926658.00</td>\n",
       "      <td>926658.00</td>\n",
       "      <td>926658.00</td>\n",
       "      <td>927184.00</td>\n",
       "      <td>927184.00</td>\n",
       "      <td>927184.00</td>\n",
       "      <td>927184.00</td>\n",
       "      <td>927184.00</td>\n",
       "      <td>927184.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>20151089.71</td>\n",
       "      <td>41.66</td>\n",
       "      <td>969.03</td>\n",
       "      <td>40.73</td>\n",
       "      <td>-73.98</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.28</td>\n",
       "      <td>1.12</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2.70</td>\n",
       "      <td>1.34</td>\n",
       "      <td>10.23</td>\n",
       "      <td>18.39</td>\n",
       "      <td>10.28</td>\n",
       "      <td>2.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>11423.29</td>\n",
       "      <td>55.11</td>\n",
       "      <td>1129.51</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.42</td>\n",
       "      <td>0.23</td>\n",
       "      <td>1.27</td>\n",
       "      <td>0.63</td>\n",
       "      <td>0.03</td>\n",
       "      <td>7.61</td>\n",
       "      <td>12.26</td>\n",
       "      <td>48.83</td>\n",
       "      <td>9.91</td>\n",
       "      <td>9.19</td>\n",
       "      <td>1.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>20130701.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>72.00</td>\n",
       "      <td>40.52</td>\n",
       "      <td>-74.03</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-9.30</td>\n",
       "      <td>-18.20</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>20140816.00</td>\n",
       "      <td>6.00</td>\n",
       "      <td>315.00</td>\n",
       "      <td>40.71</td>\n",
       "      <td>-74.00</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10.60</td>\n",
       "      <td>3.30</td>\n",
       "      <td>1.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>20150924.00</td>\n",
       "      <td>19.00</td>\n",
       "      <td>430.00</td>\n",
       "      <td>40.73</td>\n",
       "      <td>-73.99</td>\n",
       "      <td>0.59</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.48</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>19.40</td>\n",
       "      <td>10.60</td>\n",
       "      <td>2.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>20160716.00</td>\n",
       "      <td>57.00</td>\n",
       "      <td>532.00</td>\n",
       "      <td>40.75</td>\n",
       "      <td>-73.97</td>\n",
       "      <td>0.90</td>\n",
       "      <td>0.41</td>\n",
       "      <td>1.64</td>\n",
       "      <td>0.76</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>27.20</td>\n",
       "      <td>18.30</td>\n",
       "      <td>2.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>20170331.00</td>\n",
       "      <td>827.00</td>\n",
       "      <td>3456.00</td>\n",
       "      <td>40.80</td>\n",
       "      <td>-73.93</td>\n",
       "      <td>11.94</td>\n",
       "      <td>7.75</td>\n",
       "      <td>21.62</td>\n",
       "      <td>7.72</td>\n",
       "      <td>0.22</td>\n",
       "      <td>126.20</td>\n",
       "      <td>279.00</td>\n",
       "      <td>480.00</td>\n",
       "      <td>36.70</td>\n",
       "      <td>28.30</td>\n",
       "      <td>8.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              date    pickups      st_id  st_latitude  st_longitude  \\\n",
       "count    927184.00  927184.00  927184.00    926658.00     926658.00   \n",
       "mean   20151089.71      41.66     969.03        40.73        -73.98   \n",
       "std       11423.29      55.11    1129.51         0.03          0.02   \n",
       "min    20130701.00       1.00      72.00        40.52        -74.03   \n",
       "25%    20140816.00       6.00     315.00        40.71        -74.00   \n",
       "50%    20150924.00      19.00     430.00        40.73        -73.99   \n",
       "75%    20160716.00      57.00     532.00        40.75        -73.97   \n",
       "max    20170331.00     827.00    3456.00        40.80        -73.93   \n",
       "\n",
       "       closest_college_distance  closest_subway_distance  \\\n",
       "count                 926658.00                926658.00   \n",
       "mean                       0.66                     0.28   \n",
       "std                        0.42                     0.23   \n",
       "min                        0.02                     0.00   \n",
       "25%                        0.33                     0.09   \n",
       "50%                        0.59                     0.22   \n",
       "75%                        0.90                     0.41   \n",
       "max                       11.94                     7.75   \n",
       "\n",
       "       closest_theater_distance  closest_museum_distance  \\\n",
       "count                 926658.00                926658.00   \n",
       "mean                       1.12                     0.66   \n",
       "std                        1.27                     0.63   \n",
       "min                        0.01                     0.02   \n",
       "25%                        0.25                     0.27   \n",
       "50%                        0.52                     0.48   \n",
       "75%                        1.64                     0.76   \n",
       "max                       21.62                     7.72   \n",
       "\n",
       "       closest_park_distance       PRCP       SNOW       SNWD       TMAX  \\\n",
       "count              926658.00  927184.00  927184.00  927184.00  927184.00   \n",
       "mean                    0.15       2.70       1.34      10.23      18.39   \n",
       "std                     0.03       7.61      12.26      48.83       9.91   \n",
       "min                     0.09       0.00       0.00       0.00      -9.30   \n",
       "25%                     0.13       0.00       0.00       0.00      10.60   \n",
       "50%                     0.15       0.00       0.00       0.00      19.40   \n",
       "75%                     0.17       0.80       0.00       0.00      27.20   \n",
       "max                     0.22     126.20     279.00     480.00      36.70   \n",
       "\n",
       "            TMIN       AWND  \n",
       "count  927184.00  927184.00  \n",
       "mean       10.28       2.33  \n",
       "std         9.19       1.01  \n",
       "min       -18.20       0.30  \n",
       "25%         3.30       1.60  \n",
       "50%        10.60       2.20  \n",
       "75%        18.30       2.90  \n",
       "max        28.30       8.20  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.describe().round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "927184\n",
      "1349\n"
     ]
    }
   ],
   "source": [
    "print(len(data['date']))\n",
    "print(len(data['date'].unique()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identify staions with highest average subscriber daily usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create a dataset for subscribers daily usage by location\n",
    "sub_by_station= d1[['pickups', 'st_id', 'st_name']].groupby(['st_id', 'st_name']).mean().reset_index()\n",
    "sub_by_station['stds'] = d1[['pickups', 'st_id', 'st_name']].groupby(['st_id', 'st_name']).std().reset_index()['pickups']\n",
    "sub_by_station['counts'] = d1[['pickups', 'st_id', 'st_name']].groupby(['st_id', 'st_name']).size().reset_index()[0]\n",
    "\n",
    "highest10_by_mean_sub  = sub_by_station[sub_by_station['counts'] >= 100].sort_values('pickups', ascending=False).head(10)\n",
    "lowest10_by_mean_sub = sub_by_station[sub_by_station['counts'] >= 100].sort_values('pickups', ascending=True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "667"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(sub_by_station)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     667.0\n",
       "mean      810.0\n",
       "std       503.0\n",
       "min         1.0\n",
       "25%       229.0\n",
       "50%       578.0\n",
       "75%      1334.0\n",
       "max      1349.0\n",
       "Name: counts, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub_by_station['counts'].describe().round(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>st_id</th>\n",
       "      <th>st_name</th>\n",
       "      <th>pickups</th>\n",
       "      <th>stds</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>519</td>\n",
       "      <td>Pershing Square N</td>\n",
       "      <td>292.737194</td>\n",
       "      <td>203.836791</td>\n",
       "      <td>1347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>521</td>\n",
       "      <td>8 Ave &amp; W 31 St</td>\n",
       "      <td>247.178677</td>\n",
       "      <td>177.655501</td>\n",
       "      <td>1013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>3263</td>\n",
       "      <td>Cooper Square &amp; E 7 St</td>\n",
       "      <td>238.551724</td>\n",
       "      <td>135.693469</td>\n",
       "      <td>261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>273</th>\n",
       "      <td>497</td>\n",
       "      <td>E 17 St &amp; Broadway</td>\n",
       "      <td>222.685034</td>\n",
       "      <td>100.149723</td>\n",
       "      <td>1343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>435</td>\n",
       "      <td>W 21 St &amp; 6 Ave</td>\n",
       "      <td>222.197026</td>\n",
       "      <td>93.873652</td>\n",
       "      <td>1345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>219.636812</td>\n",
       "      <td>106.824420</td>\n",
       "      <td>1217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>3230</td>\n",
       "      <td>Penn Station Valet</td>\n",
       "      <td>214.724638</td>\n",
       "      <td>106.516961</td>\n",
       "      <td>345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>402</td>\n",
       "      <td>Broadway &amp; E 22 St</td>\n",
       "      <td>188.897626</td>\n",
       "      <td>102.494079</td>\n",
       "      <td>1348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>285</td>\n",
       "      <td>Broadway &amp; E 14 St</td>\n",
       "      <td>187.233457</td>\n",
       "      <td>78.923310</td>\n",
       "      <td>1345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>3255</td>\n",
       "      <td>8 Ave &amp; W 31 St</td>\n",
       "      <td>184.226230</td>\n",
       "      <td>83.531459</td>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     st_id                 st_name     pickups        stds  counts\n",
       "294    519       Pershing Square N  292.737194  203.836791    1347\n",
       "296    521         8 Ave & W 31 St  247.178677  177.655501    1013\n",
       "508   3263  Cooper Square & E 7 St  238.551724  135.693469     261\n",
       "273    497      E 17 St & Broadway  222.685034  100.149723    1343\n",
       "213    435         W 21 St & 6 Ave  222.197026   93.873652    1345\n",
       "83     293   Lafayette St & E 8 St  219.636812  106.824420    1217\n",
       "481   3230      Penn Station Valet  214.724638  106.516961     345\n",
       "184    402      Broadway & E 22 St  188.897626  102.494079    1348\n",
       "79     285      Broadway & E 14 St  187.233457   78.923310    1345\n",
       "502   3255         8 Ave & W 31 St  184.226230   83.531459     305"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top 10 bike stations for subscribers\n",
    "highest10_by_mean_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>st_id</th>\n",
       "      <th>st_name</th>\n",
       "      <th>pickups</th>\n",
       "      <th>stds</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>3219</td>\n",
       "      <td>NYCBS Depot - STY</td>\n",
       "      <td>1.769784</td>\n",
       "      <td>0.911414</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>2005</td>\n",
       "      <td>Railroad Ave &amp; Kay Ave</td>\n",
       "      <td>2.538238</td>\n",
       "      <td>1.570297</td>\n",
       "      <td>1033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>3342</td>\n",
       "      <td>Pioneer St &amp; Richards St</td>\n",
       "      <td>3.069892</td>\n",
       "      <td>1.779905</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>3326</td>\n",
       "      <td>Clinton St &amp; Centre St</td>\n",
       "      <td>3.129032</td>\n",
       "      <td>2.119285</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615</th>\n",
       "      <td>3394</td>\n",
       "      <td>Columbia St &amp; W 9 St</td>\n",
       "      <td>3.211538</td>\n",
       "      <td>3.998402</td>\n",
       "      <td>156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>555</th>\n",
       "      <td>3330</td>\n",
       "      <td>Henry St &amp; Bay St</td>\n",
       "      <td>3.427711</td>\n",
       "      <td>2.496825</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>3393</td>\n",
       "      <td>Richards St &amp; Delavan St</td>\n",
       "      <td>3.532995</td>\n",
       "      <td>2.093522</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616</th>\n",
       "      <td>3395</td>\n",
       "      <td>Henry St &amp; W 9 St</td>\n",
       "      <td>3.843434</td>\n",
       "      <td>2.330711</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>3044</td>\n",
       "      <td>Albany Ave &amp; Fulton St</td>\n",
       "      <td>3.863039</td>\n",
       "      <td>2.253236</td>\n",
       "      <td>533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>3333</td>\n",
       "      <td>Columbia St &amp; Lorraine St</td>\n",
       "      <td>4.043956</td>\n",
       "      <td>3.184604</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     st_id                    st_name   pickups      stds  counts\n",
       "474   3219          NYCBS Depot - STY  1.769784  0.911414     139\n",
       "322   2005     Railroad Ave & Kay Ave  2.538238  1.570297    1033\n",
       "566   3342   Pioneer St & Richards St  3.069892  1.779905     186\n",
       "551   3326     Clinton St & Centre St  3.129032  2.119285     186\n",
       "615   3394       Columbia St & W 9 St  3.211538  3.998402     156\n",
       "555   3330          Henry St & Bay St  3.427711  2.496825     166\n",
       "614   3393   Richards St & Delavan St  3.532995  2.093522     197\n",
       "616   3395          Henry St & W 9 St  3.843434  2.330711     198\n",
       "341   3044     Albany Ave & Fulton St  3.863039  2.253236     533\n",
       "558   3333  Columbia St & Lorraine St  4.043956  3.184604     182"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Bottom 10 bike stations for subscribers\n",
    "lowest10_by_mean_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create a dataset for non-subscribers daily usage by location\n",
    "nonsub_by_station= d2[['pickups', 'st_id', 'st_name']].groupby(['st_id', 'st_name']).mean().reset_index()\n",
    "nonsub_by_station['stds'] = d2[['pickups', 'st_id', 'st_name']].groupby(['st_id', 'st_name']).std().reset_index()['pickups']\n",
    "nonsub_by_station['counts'] = d2[['pickups', 'st_id', 'st_name']].groupby(['st_id', 'st_name']).size().reset_index()[0]\n",
    "\n",
    "highest10_by_mean_nonsub  = nonsub_by_station[nonsub_by_station['counts'] >= 100].sort_values('pickups', ascending=False).head(10)\n",
    "lowest10_by_mean_nonsub = nonsub_by_station[nonsub_by_station['counts'] >= 100].sort_values('pickups', ascending=True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "657"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(nonsub_by_station)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     657.0\n",
       "mean      588.0\n",
       "std       421.0\n",
       "min         1.0\n",
       "25%       157.0\n",
       "50%       489.0\n",
       "75%      1026.0\n",
       "max      1261.0\n",
       "Name: counts, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nonsub_by_station['counts'].describe().round(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>st_id</th>\n",
       "      <th>st_name</th>\n",
       "      <th>pickups</th>\n",
       "      <th>stds</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>2006</td>\n",
       "      <td>Central Park S &amp; 6 Ave</td>\n",
       "      <td>89.953212</td>\n",
       "      <td>84.182956</td>\n",
       "      <td>1261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>387</td>\n",
       "      <td>Centre St &amp; Chambers St</td>\n",
       "      <td>66.829032</td>\n",
       "      <td>64.458067</td>\n",
       "      <td>1240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>435</th>\n",
       "      <td>3143</td>\n",
       "      <td>5 Ave &amp; E 78 St</td>\n",
       "      <td>60.123016</td>\n",
       "      <td>57.269243</td>\n",
       "      <td>504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>281</td>\n",
       "      <td>Grand Army Plaza &amp; Central Park S</td>\n",
       "      <td>59.472625</td>\n",
       "      <td>54.725390</td>\n",
       "      <td>1242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>3137</td>\n",
       "      <td>5 Ave &amp; E 73 St</td>\n",
       "      <td>56.392857</td>\n",
       "      <td>55.022139</td>\n",
       "      <td>504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>3282</td>\n",
       "      <td>5 Ave &amp; E 88 St</td>\n",
       "      <td>55.815315</td>\n",
       "      <td>55.315362</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>3165</td>\n",
       "      <td>Central Park West &amp; W 72 St</td>\n",
       "      <td>52.835391</td>\n",
       "      <td>53.477172</td>\n",
       "      <td>486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>426</td>\n",
       "      <td>West St &amp; Chambers St</td>\n",
       "      <td>51.807914</td>\n",
       "      <td>52.685694</td>\n",
       "      <td>1213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289</th>\n",
       "      <td>514</td>\n",
       "      <td>12 Ave &amp; W 40 St</td>\n",
       "      <td>49.744787</td>\n",
       "      <td>54.272698</td>\n",
       "      <td>1199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>459</th>\n",
       "      <td>3168</td>\n",
       "      <td>Central Park West &amp; W 85 St</td>\n",
       "      <td>44.174393</td>\n",
       "      <td>45.775501</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     st_id                            st_name    pickups       stds  counts\n",
       "323   2006             Central Park S & 6 Ave  89.953212  84.182956    1261\n",
       "169    387            Centre St & Chambers St  66.829032  64.458067    1240\n",
       "435   3143                    5 Ave & E 78 St  60.123016  57.269243     504\n",
       "76     281  Grand Army Plaza & Central Park S  59.472625  54.725390    1242\n",
       "429   3137                    5 Ave & E 73 St  56.392857  55.022139     504\n",
       "505   3282                    5 Ave & E 88 St  55.815315  55.315362     222\n",
       "456   3165        Central Park West & W 72 St  52.835391  53.477172     486\n",
       "205    426              West St & Chambers St  51.807914  52.685694    1213\n",
       "289    514                   12 Ave & W 40 St  49.744787  54.272698    1199\n",
       "459   3168        Central Park West & W 85 St  44.174393  45.775501     453"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top 10 bike stations for non-subscribers\n",
    "highest10_by_mean_nonsub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>st_id</th>\n",
       "      <th>st_name</th>\n",
       "      <th>pickups</th>\n",
       "      <th>stds</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>2005</td>\n",
       "      <td>Railroad Ave &amp; Kay Ave</td>\n",
       "      <td>1.483051</td>\n",
       "      <td>0.792411</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>3049</td>\n",
       "      <td>Cambridge Pl &amp; Gates Ave</td>\n",
       "      <td>1.798658</td>\n",
       "      <td>1.257302</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>3221</td>\n",
       "      <td>47 Ave &amp; 31 St</td>\n",
       "      <td>1.868421</td>\n",
       "      <td>1.435981</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>416</th>\n",
       "      <td>3123</td>\n",
       "      <td>31 St &amp; Thomson Ave</td>\n",
       "      <td>1.891566</td>\n",
       "      <td>1.330409</td>\n",
       "      <td>166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>3059</td>\n",
       "      <td>Pulaski St &amp; Marcus Garvey Blvd</td>\n",
       "      <td>1.923729</td>\n",
       "      <td>1.275387</td>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>3070</td>\n",
       "      <td>McKibbin St &amp; Manhattan Ave</td>\n",
       "      <td>1.931507</td>\n",
       "      <td>1.397819</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>3044</td>\n",
       "      <td>Albany Ave &amp; Fulton St</td>\n",
       "      <td>1.961290</td>\n",
       "      <td>1.278615</td>\n",
       "      <td>155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>3249</td>\n",
       "      <td>Verona Pl &amp; Fulton St</td>\n",
       "      <td>1.975000</td>\n",
       "      <td>1.305853</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>354</th>\n",
       "      <td>3061</td>\n",
       "      <td>Throop Ave &amp; Myrtle Ave</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.324042</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>3095</td>\n",
       "      <td>Graham Ave &amp; Herbert St</td>\n",
       "      <td>2.009259</td>\n",
       "      <td>1.503456</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     st_id                          st_name   pickups      stds  counts\n",
       "322   2005           Railroad Ave & Kay Ave  1.483051  0.792411     118\n",
       "343   3049         Cambridge Pl & Gates Ave  1.798658  1.257302     149\n",
       "472   3221                   47 Ave & 31 St  1.868421  1.435981     152\n",
       "416   3123              31 St & Thomson Ave  1.891566  1.330409     166\n",
       "352   3059  Pulaski St & Marcus Garvey Blvd  1.923729  1.275387     118\n",
       "363   3070      McKibbin St & Manhattan Ave  1.931507  1.397819     146\n",
       "339   3044           Albany Ave & Fulton St  1.961290  1.278615     155\n",
       "492   3249            Verona Pl & Fulton St  1.975000  1.305853     120\n",
       "354   3061          Throop Ave & Myrtle Ave  2.000000  1.324042     163\n",
       "388   3095          Graham Ave & Herbert St  2.009259  1.503456     216"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Bottom 10 bike stations for non-subscribers\n",
    "lowest10_by_mean_nonsub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}