Data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Citibike Data Visualization (03-2020 to 04-2020)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After extracting and importing the data , the next step is to make sure that the visualization of the data is as accurate as possible and for this data cleaning is very imoprtant. It can be done by identifying erros or corruptions, correcting or deleting them, or manually processing data as needed to prevent the same errors from occuring. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first step, here, is to import all the necessary modules and then with the help of the modules import file that has the data. Next is to find the data type which helps not just in the accuracy of the visualization but also to makes the cleaning proccess as simple as possible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import necessary modules\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import math"
   ]
  },
  {
   "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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1589</td>\n",
       "      <td>2020-03-01 00:00:03.6400</td>\n",
       "      <td>2020-03-01 00:26:32.9860</td>\n",
       "      <td>224</td>\n",
       "      <td>Spruce St &amp; Nassau St</td>\n",
       "      <td>40.71146364</td>\n",
       "      <td>-74.00552427</td>\n",
       "      <td>3574</td>\n",
       "      <td>Prospect Pl &amp; Underhill Ave</td>\n",
       "      <td>40.6769694</td>\n",
       "      <td>-73.96579</td>\n",
       "      <td>16214</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>389</td>\n",
       "      <td>2020-03-01 00:00:16.7560</td>\n",
       "      <td>2020-03-01 00:06:46.0620</td>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>40.73020660529954</td>\n",
       "      <td>-73.99102628231049</td>\n",
       "      <td>223</td>\n",
       "      <td>W 13 St &amp; 7 Ave</td>\n",
       "      <td>40.73781509</td>\n",
       "      <td>-73.99994661</td>\n",
       "      <td>29994</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1991</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>614</td>\n",
       "      <td>2020-03-01 00:00:20.0580</td>\n",
       "      <td>2020-03-01 00:10:34.2200</td>\n",
       "      <td>379</td>\n",
       "      <td>W 31 St &amp; 7 Ave</td>\n",
       "      <td>40.749156</td>\n",
       "      <td>-73.9916</td>\n",
       "      <td>515</td>\n",
       "      <td>W 43 St &amp; 10 Ave</td>\n",
       "      <td>40.76009437</td>\n",
       "      <td>-73.99461843</td>\n",
       "      <td>39853</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1991</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>597</td>\n",
       "      <td>2020-03-01 00:00:24.3510</td>\n",
       "      <td>2020-03-01 00:10:22.3390</td>\n",
       "      <td>3739</td>\n",
       "      <td>Perry St &amp; Greenwich Ave</td>\n",
       "      <td>40.735918</td>\n",
       "      <td>-74.000939</td>\n",
       "      <td>325</td>\n",
       "      <td>E 19 St &amp; 3 Ave</td>\n",
       "      <td>40.73624527</td>\n",
       "      <td>-73.98473765</td>\n",
       "      <td>42608</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1989</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1920</td>\n",
       "      <td>2020-03-01 00:00:26.1120</td>\n",
       "      <td>2020-03-01 00:32:26.2680</td>\n",
       "      <td>236</td>\n",
       "      <td>St Marks Pl &amp; 2 Ave</td>\n",
       "      <td>40.7284186</td>\n",
       "      <td>-73.98713956</td>\n",
       "      <td>3124</td>\n",
       "      <td>46 Ave &amp; 5 St</td>\n",
       "      <td>40.74731</td>\n",
       "      <td>-73.95451</td>\n",
       "      <td>36288</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1993</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  tripduration                 starttime                  stoptime  \\\n",
       "0         1589  2020-03-01 00:00:03.6400  2020-03-01 00:26:32.9860   \n",
       "1          389  2020-03-01 00:00:16.7560  2020-03-01 00:06:46.0620   \n",
       "2          614  2020-03-01 00:00:20.0580  2020-03-01 00:10:34.2200   \n",
       "3          597  2020-03-01 00:00:24.3510  2020-03-01 00:10:22.3390   \n",
       "4         1920  2020-03-01 00:00:26.1120  2020-03-01 00:32:26.2680   \n",
       "\n",
       "  start station id        start station name start station latitude  \\\n",
       "0              224     Spruce St & Nassau St            40.71146364   \n",
       "1              293     Lafayette St & E 8 St      40.73020660529954   \n",
       "2              379           W 31 St & 7 Ave              40.749156   \n",
       "3             3739  Perry St & Greenwich Ave              40.735918   \n",
       "4              236       St Marks Pl & 2 Ave             40.7284186   \n",
       "\n",
       "  start station longitude end station id             end station name  \\\n",
       "0            -74.00552427           3574  Prospect Pl & Underhill Ave   \n",
       "1      -73.99102628231049            223              W 13 St & 7 Ave   \n",
       "2                -73.9916            515             W 43 St & 10 Ave   \n",
       "3              -74.000939            325              E 19 St & 3 Ave   \n",
       "4            -73.98713956           3124                46 Ave & 5 St   \n",
       "\n",
       "  end station latitude end station longitude bikeid    usertype birth year  \\\n",
       "0           40.6769694             -73.96579  16214  Subscriber       1980   \n",
       "1          40.73781509          -73.99994661  29994  Subscriber       1991   \n",
       "2          40.76009437          -73.99461843  39853  Subscriber       1991   \n",
       "3          40.73624527          -73.98473765  42608  Subscriber       1989   \n",
       "4             40.74731             -73.95451  36288  Subscriber       1993   \n",
       "\n",
       "  gender  \n",
       "0      1  \n",
       "1      2  \n",
       "2      1  \n",
       "3      1  \n",
       "4      1  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read the csv file\n",
    "df = pd.read_csv(\"Resources/202003_and_04_tripdata.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1751569, 15)\n",
      "tripduration               object\n",
      "starttime                  object\n",
      "stoptime                   object\n",
      "start station id           object\n",
      "start station name         object\n",
      "start station latitude     object\n",
      "start station longitude    object\n",
      "end station id             object\n",
      "end station name           object\n",
      "end station latitude       object\n",
      "end station longitude      object\n",
      "bikeid                     object\n",
      "usertype                   object\n",
      "birth year                 object\n",
      "gender                     object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "#check the number of rows and dtypes of the df\n",
    "print(df.shape)\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "According to Citi Bike ridership the data includes:\n",
    "\n",
    "-Trip Duration (seconds)\n",
    "\n",
    "-Start Time and Date\n",
    "\n",
    "-Stop Time and Date\n",
    "\n",
    "-Start Station Name\n",
    "\n",
    "-End Station Name\n",
    "\n",
    "-Station ID\n",
    "\n",
    "-Station Lat/Long\n",
    "\n",
    "-Bike ID\n",
    "\n",
    "-User Type (Customer = 24-hour pass or 3-day pass user; Subscriber = Annual Member)\n",
    "\n",
    "-Gender (Zero=unknown; 1=male; 2=female)\n",
    "\n",
    "-Year of Birth\n",
    "\n",
    "Here, the values of each column were object type. This may not be a problem if all the values had letters or if adding and substracting were not required but since numeric values are of great importance for the visualization, I converted the data types from object to appropriate types."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The time for the duration of trip is recorded at seconds so I coverted it from objects to integers. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert dtype of column tripduration and replace anything that is not a number with 0\n",
    "df[\"tripduration\"] =pd.to_numeric(df[\"tripduration\"], errors = 'coerce').fillna(0).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After converting the values of the column tripduration, I dropped the rows that took less than a minute. Keeping the traffic of New York city in mind it is safe to assume that the riders took at least 2 minutes to get from one station to another. \n",
    "\n",
    "Likewise, I dropped the rows that had tripduration of more than 24 hours. It is likely that the bike went for repair and maintainance or it was stolen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop rows that were less than 90 or more than 24 hours\n",
    "df= df[~((df[\"tripduration\"]<90) |(df[\"tripduration\"]>86400))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I also dropped the rows that started and ended in the same station with trip duration of less than 5 min. This was done on the assumption that the bike or the rider had mechanical issues and also for my analysis I am only considering the rides that covered some distance from one station to another and not just a ride around the block and back. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop rows that started and ended in the same station less than a 5 min\n",
    "df= df[~((df[\"tripduration\"]<=300) & (df[\"start station id\"] == df[\"end station id\"]))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The starttime column was converted to datetime following the format year-month-day hour-min-sec. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert dtype of column starttime, set errors='coerce' for invalid values\n",
    "df['starttime'] = pd.to_datetime(df['starttime'],errors='coerce', format='%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, stoptime column was also converted to datetime following the format year-month-day hour-min-sec."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert dtype of column stoptime, set errors='coerce' for invalid values\n",
    "df['stoptime'] = pd.to_datetime(df['stoptime'],errors='coerce', format='%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the process of converting the starttime and stoptime, I kept getting type error saying that the format does not match. On further inquiry I came to know that some of the rows had name of the columns as their values instead of the actual data. By setting errors to coerce I  replaced the invalid parsing with NaN and later dropped the rows with NaN value. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even though the station ids were not of priority, I decided to convert them from object type to integers so that I could remove any missing data. One other way of removing missing data is replacing the invalid parsing (if any) with 0 and then dropping the columns with ) values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert dtype for column start station id where valid\n",
    "df[\"start station id\"] =pd.to_numeric(df[\"start station id\"], errors = 'coerce').fillna(0).astype(int)\n",
    "#drop station id 0\n",
    "df=df[~(df[\"start station id\"] ==0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert dtype for column end station id where valid\n",
    "df[\"end station id\"] =pd.to_numeric(df[\"end station id\"], errors = 'coerce').fillna(0).astype(int)\n",
    "#drop station id 0\n",
    "df=df[~(df[\"end station id\"] ==0)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The location of the stations were very important for my analysis so I converted them from objects to appropritate numeric type by using to_numeric so as not to lose precision. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert dtype for all of the given lat and long\n",
    "df[[\"start station latitude\", \n",
    "    \"start station longitude\", \n",
    "    \"end station latitude\", \n",
    "    \"end station longitude\"]] = df[[\"start station latitude\", \n",
    "                                    \"start station longitude\",\n",
    "                                    \"end station latitude\",\n",
    "                                    \"end station longitude\"]].apply(pd.to_numeric)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the same conversion method, the vlaues on the column birth year were changed to integers from object and any invalid parsing replaced with 0. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert birth year from object to int\n",
    "df[\"birth year\"] = pd.to_numeric(df[\"birth year\"], errors = 'coerce').fillna(0).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dropped the rows with  birth year 0 and also the ones the with year before 1930 ( riders who are said to be 90 plus). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop birth year 0 (if any) or year 1930 and less\n",
    "df= df[~((df[\"birth year\"] == 0) | (df[\"birth year\"] <= 1930))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Changed the bike id to integers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert bikeid from object to int\n",
    "df[\"bikeid\"] = pd.to_numeric(df[\"bikeid\"]).astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replaced the gender category from numbers to corresponding sex."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#replace numbers with genders under gender column\n",
    "df['gender'] = df[\"gender\"].map({\"0\":\"Unknown\", \"1\":\"Male\", \"2\":\"Female\"})\n",
    "#df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checked to make sure the data type were converted correctly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration                        int32\n",
       "starttime                  datetime64[ns]\n",
       "stoptime                   datetime64[ns]\n",
       "start station id                    int32\n",
       "start station name                 object\n",
       "start station latitude            float64\n",
       "start station longitude           float64\n",
       "end station id                      int32\n",
       "end station name                   object\n",
       "end station latitude              float64\n",
       "end station longitude             float64\n",
       "bikeid                              int32\n",
       "usertype                           object\n",
       "birth year                          int32\n",
       "gender                             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lastly, I dropped all the rows that had any NaN values. At the end the rows omitted were around 23000 or 1.35% of the original data which is reasonable  enough to get some results for the analysis  and for reliable visualization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1589</td>\n",
       "      <td>2020-03-01 00:00:03.640</td>\n",
       "      <td>2020-03-01 00:26:32.986</td>\n",
       "      <td>224</td>\n",
       "      <td>Spruce St &amp; Nassau St</td>\n",
       "      <td>40.711464</td>\n",
       "      <td>-74.005524</td>\n",
       "      <td>3574</td>\n",
       "      <td>Prospect Pl &amp; Underhill Ave</td>\n",
       "      <td>40.676969</td>\n",
       "      <td>-73.965790</td>\n",
       "      <td>16214</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>389</td>\n",
       "      <td>2020-03-01 00:00:16.756</td>\n",
       "      <td>2020-03-01 00:06:46.062</td>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>40.730207</td>\n",
       "      <td>-73.991026</td>\n",
       "      <td>223</td>\n",
       "      <td>W 13 St &amp; 7 Ave</td>\n",
       "      <td>40.737815</td>\n",
       "      <td>-73.999947</td>\n",
       "      <td>29994</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1991</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>614</td>\n",
       "      <td>2020-03-01 00:00:20.058</td>\n",
       "      <td>2020-03-01 00:10:34.220</td>\n",
       "      <td>379</td>\n",
       "      <td>W 31 St &amp; 7 Ave</td>\n",
       "      <td>40.749156</td>\n",
       "      <td>-73.991600</td>\n",
       "      <td>515</td>\n",
       "      <td>W 43 St &amp; 10 Ave</td>\n",
       "      <td>40.760094</td>\n",
       "      <td>-73.994618</td>\n",
       "      <td>39853</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1991</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>597</td>\n",
       "      <td>2020-03-01 00:00:24.351</td>\n",
       "      <td>2020-03-01 00:10:22.339</td>\n",
       "      <td>3739</td>\n",
       "      <td>Perry St &amp; Greenwich Ave</td>\n",
       "      <td>40.735918</td>\n",
       "      <td>-74.000939</td>\n",
       "      <td>325</td>\n",
       "      <td>E 19 St &amp; 3 Ave</td>\n",
       "      <td>40.736245</td>\n",
       "      <td>-73.984738</td>\n",
       "      <td>42608</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1989</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1920</td>\n",
       "      <td>2020-03-01 00:00:26.112</td>\n",
       "      <td>2020-03-01 00:32:26.268</td>\n",
       "      <td>236</td>\n",
       "      <td>St Marks Pl &amp; 2 Ave</td>\n",
       "      <td>40.728419</td>\n",
       "      <td>-73.987140</td>\n",
       "      <td>3124</td>\n",
       "      <td>46 Ave &amp; 5 St</td>\n",
       "      <td>40.747310</td>\n",
       "      <td>-73.954510</td>\n",
       "      <td>36288</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1993</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1727958</th>\n",
       "      <td>1680</td>\n",
       "      <td>2020-04-30 23:59:00.778</td>\n",
       "      <td>2020-05-01 00:27:01.760</td>\n",
       "      <td>447</td>\n",
       "      <td>8 Ave &amp; W 52 St</td>\n",
       "      <td>40.763707</td>\n",
       "      <td>-73.985162</td>\n",
       "      <td>2021</td>\n",
       "      <td>W 45 St &amp; 8 Ave</td>\n",
       "      <td>40.759291</td>\n",
       "      <td>-73.988597</td>\n",
       "      <td>43888</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1989</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1727959</th>\n",
       "      <td>1334</td>\n",
       "      <td>2020-04-30 23:59:23.646</td>\n",
       "      <td>2020-05-01 00:21:38.219</td>\n",
       "      <td>3292</td>\n",
       "      <td>5 Ave &amp; E 93 St</td>\n",
       "      <td>40.785785</td>\n",
       "      <td>-73.957481</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>36853</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1973</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1727960</th>\n",
       "      <td>1334</td>\n",
       "      <td>2020-04-30 23:59:33.676</td>\n",
       "      <td>2020-05-01 00:21:48.215</td>\n",
       "      <td>3292</td>\n",
       "      <td>5 Ave &amp; E 93 St</td>\n",
       "      <td>40.785785</td>\n",
       "      <td>-73.957481</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>37808</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1981</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1727961</th>\n",
       "      <td>234</td>\n",
       "      <td>2020-04-30 23:59:56.972</td>\n",
       "      <td>2020-05-01 00:03:51.960</td>\n",
       "      <td>469</td>\n",
       "      <td>Broadway &amp; W 53 St</td>\n",
       "      <td>40.763441</td>\n",
       "      <td>-73.982681</td>\n",
       "      <td>495</td>\n",
       "      <td>W 47 St &amp; 10 Ave</td>\n",
       "      <td>40.762699</td>\n",
       "      <td>-73.993012</td>\n",
       "      <td>20635</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1996</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1727962</th>\n",
       "      <td>267</td>\n",
       "      <td>2020-04-30 23:59:59.351</td>\n",
       "      <td>2020-05-01 00:04:26.811</td>\n",
       "      <td>3812</td>\n",
       "      <td>University Pl &amp; E 14 St</td>\n",
       "      <td>40.734814</td>\n",
       "      <td>-73.992085</td>\n",
       "      <td>503</td>\n",
       "      <td>E 20 St &amp; Park Ave</td>\n",
       "      <td>40.738274</td>\n",
       "      <td>-73.987520</td>\n",
       "      <td>37530</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1727963 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         tripduration               starttime                stoptime  \\\n",
       "0                1589 2020-03-01 00:00:03.640 2020-03-01 00:26:32.986   \n",
       "1                 389 2020-03-01 00:00:16.756 2020-03-01 00:06:46.062   \n",
       "2                 614 2020-03-01 00:00:20.058 2020-03-01 00:10:34.220   \n",
       "3                 597 2020-03-01 00:00:24.351 2020-03-01 00:10:22.339   \n",
       "4                1920 2020-03-01 00:00:26.112 2020-03-01 00:32:26.268   \n",
       "...               ...                     ...                     ...   \n",
       "1727958          1680 2020-04-30 23:59:00.778 2020-05-01 00:27:01.760   \n",
       "1727959          1334 2020-04-30 23:59:23.646 2020-05-01 00:21:38.219   \n",
       "1727960          1334 2020-04-30 23:59:33.676 2020-05-01 00:21:48.215   \n",
       "1727961           234 2020-04-30 23:59:56.972 2020-05-01 00:03:51.960   \n",
       "1727962           267 2020-04-30 23:59:59.351 2020-05-01 00:04:26.811   \n",
       "\n",
       "         start station id        start station name  start station latitude  \\\n",
       "0                     224     Spruce St & Nassau St               40.711464   \n",
       "1                     293     Lafayette St & E 8 St               40.730207   \n",
       "2                     379           W 31 St & 7 Ave               40.749156   \n",
       "3                    3739  Perry St & Greenwich Ave               40.735918   \n",
       "4                     236       St Marks Pl & 2 Ave               40.728419   \n",
       "...                   ...                       ...                     ...   \n",
       "1727958               447           8 Ave & W 52 St               40.763707   \n",
       "1727959              3292           5 Ave & E 93 St               40.785785   \n",
       "1727960              3292           5 Ave & E 93 St               40.785785   \n",
       "1727961               469        Broadway & W 53 St               40.763441   \n",
       "1727962              3812   University Pl & E 14 St               40.734814   \n",
       "\n",
       "         start station longitude  end station id             end station name  \\\n",
       "0                     -74.005524            3574  Prospect Pl & Underhill Ave   \n",
       "1                     -73.991026             223              W 13 St & 7 Ave   \n",
       "2                     -73.991600             515             W 43 St & 10 Ave   \n",
       "3                     -74.000939             325              E 19 St & 3 Ave   \n",
       "4                     -73.987140            3124                46 Ave & 5 St   \n",
       "...                          ...             ...                          ...   \n",
       "1727958               -73.985162            2021              W 45 St & 8 Ave   \n",
       "1727959               -73.957481             509              9 Ave & W 22 St   \n",
       "1727960               -73.957481             509              9 Ave & W 22 St   \n",
       "1727961               -73.982681             495             W 47 St & 10 Ave   \n",
       "1727962               -73.992085             503           E 20 St & Park Ave   \n",
       "\n",
       "         end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0                   40.676969             -73.965790   16214  Subscriber   \n",
       "1                   40.737815             -73.999947   29994  Subscriber   \n",
       "2                   40.760094             -73.994618   39853  Subscriber   \n",
       "3                   40.736245             -73.984738   42608  Subscriber   \n",
       "4                   40.747310             -73.954510   36288  Subscriber   \n",
       "...                       ...                    ...     ...         ...   \n",
       "1727958             40.759291             -73.988597   43888  Subscriber   \n",
       "1727959             40.745497             -74.001971   36853  Subscriber   \n",
       "1727960             40.745497             -74.001971   37808  Subscriber   \n",
       "1727961             40.762699             -73.993012   20635  Subscriber   \n",
       "1727962             40.738274             -73.987520   37530  Subscriber   \n",
       "\n",
       "         birth year  gender  \n",
       "0              1980    Male  \n",
       "1              1991  Female  \n",
       "2              1991    Male  \n",
       "3              1989    Male  \n",
       "4              1993    Male  \n",
       "...             ...     ...  \n",
       "1727958        1989    Male  \n",
       "1727959        1973    Male  \n",
       "1727960        1981    Male  \n",
       "1727961        1996  Female  \n",
       "1727962        1980    Male  \n",
       "\n",
       "[1727963 rows x 15 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#lastly drop any nan values\n",
    "df.dropna()\n",
    "df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sorting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since age was one of the factor that I wanted to work on, I created a new column with ages of the riders as the values. By substracting the birth year column from the period of analysis I was able to get the ages of the rider. Then I removed the birth column since it was not necessary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#calculate age \n",
    "df[\"age\"] = 2020 - df[\"birth year\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop birth year column\n",
    "del df[\"birth year\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the Haversine formula I defined a function to get the distance between the start and end station in miles. The formula and how to use it are available with a quick google search. The only thing to bear in mind is the unit of measurement for the distance. Here, I am using Miles, so the radius of the Earth is set at 3959 (approx.). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# using haversine to get the distance(mil) between two stations \n",
    "def distance(origin, destination):\n",
    "    lat1, lon1 = origin\n",
    "    lat2, lon2 = destination\n",
    "    radius = 3959 # radius of Earth in miles\n",
    "\n",
    "    dlat = math.radians(lat2-lat1)\n",
    "    dlon = math.radians(lon2-lon1)\n",
    "    a = math.sin(dlat/2) * math.sin(dlat/2) + math.cos(math.radians(lat1)) \\\n",
    "        * math.cos(math.radians(lat2)) * math.sin(dlon/2) * math.sin(dlon/2)\n",
    "    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1-a))\n",
    "    d = radius * c\n",
    " \n",
    "    return d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I then used the distance function on each of the locations and created a new column with distance between them as the values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a new column with distance\n",
    "df[\"distance(mi.)\"] = df.apply(lambda x: distance((x[\"start station latitude\"], x[\"start station longitude\"]), (x[\"end station latitude\"], x[\"end station longitude\"])), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Usinf the new distance column and trip duration I then calculated the speed of each rider in miles per hour."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#calcuate the average speed\n",
    "df[\"speed(miles per hr)\"] = df[\"distance(mi.)\"]/(df[\"tripduration\"]/3600)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>distance(mi.)</th>\n",
       "      <th>speed(miles per hr)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1589</td>\n",
       "      <td>2020-03-01 00:00:03.640</td>\n",
       "      <td>2020-03-01 00:26:32.986</td>\n",
       "      <td>224</td>\n",
       "      <td>Spruce St &amp; Nassau St</td>\n",
       "      <td>40.711464</td>\n",
       "      <td>-74.005524</td>\n",
       "      <td>3574</td>\n",
       "      <td>Prospect Pl &amp; Underhill Ave</td>\n",
       "      <td>40.676969</td>\n",
       "      <td>-73.965790</td>\n",
       "      <td>16214</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>40</td>\n",
       "      <td>3.164534</td>\n",
       "      <td>7.169491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>389</td>\n",
       "      <td>2020-03-01 00:00:16.756</td>\n",
       "      <td>2020-03-01 00:06:46.062</td>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>40.730207</td>\n",
       "      <td>-73.991026</td>\n",
       "      <td>223</td>\n",
       "      <td>W 13 St &amp; 7 Ave</td>\n",
       "      <td>40.737815</td>\n",
       "      <td>-73.999947</td>\n",
       "      <td>29994</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Female</td>\n",
       "      <td>29</td>\n",
       "      <td>0.703228</td>\n",
       "      <td>6.508026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>614</td>\n",
       "      <td>2020-03-01 00:00:20.058</td>\n",
       "      <td>2020-03-01 00:10:34.220</td>\n",
       "      <td>379</td>\n",
       "      <td>W 31 St &amp; 7 Ave</td>\n",
       "      <td>40.749156</td>\n",
       "      <td>-73.991600</td>\n",
       "      <td>515</td>\n",
       "      <td>W 43 St &amp; 10 Ave</td>\n",
       "      <td>40.760094</td>\n",
       "      <td>-73.994618</td>\n",
       "      <td>39853</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>29</td>\n",
       "      <td>0.772151</td>\n",
       "      <td>4.527271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>597</td>\n",
       "      <td>2020-03-01 00:00:24.351</td>\n",
       "      <td>2020-03-01 00:10:22.339</td>\n",
       "      <td>3739</td>\n",
       "      <td>Perry St &amp; Greenwich Ave</td>\n",
       "      <td>40.735918</td>\n",
       "      <td>-74.000939</td>\n",
       "      <td>325</td>\n",
       "      <td>E 19 St &amp; 3 Ave</td>\n",
       "      <td>40.736245</td>\n",
       "      <td>-73.984738</td>\n",
       "      <td>42608</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>31</td>\n",
       "      <td>0.848553</td>\n",
       "      <td>5.116904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1920</td>\n",
       "      <td>2020-03-01 00:00:26.112</td>\n",
       "      <td>2020-03-01 00:32:26.268</td>\n",
       "      <td>236</td>\n",
       "      <td>St Marks Pl &amp; 2 Ave</td>\n",
       "      <td>40.728419</td>\n",
       "      <td>-73.987140</td>\n",
       "      <td>3124</td>\n",
       "      <td>46 Ave &amp; 5 St</td>\n",
       "      <td>40.747310</td>\n",
       "      <td>-73.954510</td>\n",
       "      <td>36288</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>Male</td>\n",
       "      <td>27</td>\n",
       "      <td>2.149965</td>\n",
       "      <td>4.031184</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration               starttime                stoptime  \\\n",
       "0          1589 2020-03-01 00:00:03.640 2020-03-01 00:26:32.986   \n",
       "1           389 2020-03-01 00:00:16.756 2020-03-01 00:06:46.062   \n",
       "2           614 2020-03-01 00:00:20.058 2020-03-01 00:10:34.220   \n",
       "3           597 2020-03-01 00:00:24.351 2020-03-01 00:10:22.339   \n",
       "4          1920 2020-03-01 00:00:26.112 2020-03-01 00:32:26.268   \n",
       "\n",
       "   start station id        start station name  start station latitude  \\\n",
       "0               224     Spruce St & Nassau St               40.711464   \n",
       "1               293     Lafayette St & E 8 St               40.730207   \n",
       "2               379           W 31 St & 7 Ave               40.749156   \n",
       "3              3739  Perry St & Greenwich Ave               40.735918   \n",
       "4               236       St Marks Pl & 2 Ave               40.728419   \n",
       "\n",
       "   start station longitude  end station id             end station name  \\\n",
       "0               -74.005524            3574  Prospect Pl & Underhill Ave   \n",
       "1               -73.991026             223              W 13 St & 7 Ave   \n",
       "2               -73.991600             515             W 43 St & 10 Ave   \n",
       "3               -74.000939             325              E 19 St & 3 Ave   \n",
       "4               -73.987140            3124                46 Ave & 5 St   \n",
       "\n",
       "   end station latitude  end station longitude  bikeid    usertype  gender  \\\n",
       "0             40.676969             -73.965790   16214  Subscriber    Male   \n",
       "1             40.737815             -73.999947   29994  Subscriber  Female   \n",
       "2             40.760094             -73.994618   39853  Subscriber    Male   \n",
       "3             40.736245             -73.984738   42608  Subscriber    Male   \n",
       "4             40.747310             -73.954510   36288  Subscriber    Male   \n",
       "\n",
       "   age  distance(mi.)  speed(miles per hr)  \n",
       "0   40       3.164534             7.169491  \n",
       "1   29       0.703228             6.508026  \n",
       "2   29       0.772151             4.527271  \n",
       "3   31       0.848553             5.116904  \n",
       "4   27       2.149965             4.031184  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the help of summary statistics, I was able to see if there were any corrupt data or any outliers that might be misleading. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>tripduration</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>age</th>\n",
       "      <th>distance(mi.)</th>\n",
       "      <th>speed(miles per hr)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "      <td>1727963.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1116.43</td>\n",
       "      <td>1889.50</td>\n",
       "      <td>40.74</td>\n",
       "      <td>-73.98</td>\n",
       "      <td>1882.56</td>\n",
       "      <td>40.74</td>\n",
       "      <td>-73.98</td>\n",
       "      <td>33756.79</td>\n",
       "      <td>40.03</td>\n",
       "      <td>1.25</td>\n",
       "      <td>5.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1870.09</td>\n",
       "      <td>1513.88</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.02</td>\n",
       "      <td>1514.29</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.02</td>\n",
       "      <td>8276.76</td>\n",
       "      <td>12.22</td>\n",
       "      <td>1.05</td>\n",
       "      <td>2.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>90.00</td>\n",
       "      <td>72.00</td>\n",
       "      <td>40.66</td>\n",
       "      <td>-74.02</td>\n",
       "      <td>72.00</td>\n",
       "      <td>40.66</td>\n",
       "      <td>-74.05</td>\n",
       "      <td>14530.00</td>\n",
       "      <td>16.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>417.00</td>\n",
       "      <td>408.00</td>\n",
       "      <td>40.72</td>\n",
       "      <td>-73.99</td>\n",
       "      <td>405.00</td>\n",
       "      <td>40.72</td>\n",
       "      <td>-73.99</td>\n",
       "      <td>29143.00</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0.51</td>\n",
       "      <td>3.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>784.00</td>\n",
       "      <td>2003.00</td>\n",
       "      <td>40.74</td>\n",
       "      <td>-73.98</td>\n",
       "      <td>2002.00</td>\n",
       "      <td>40.74</td>\n",
       "      <td>-73.98</td>\n",
       "      <td>36298.00</td>\n",
       "      <td>37.00</td>\n",
       "      <td>0.95</td>\n",
       "      <td>5.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1437.00</td>\n",
       "      <td>3374.00</td>\n",
       "      <td>40.76</td>\n",
       "      <td>-73.96</td>\n",
       "      <td>3374.00</td>\n",
       "      <td>40.76</td>\n",
       "      <td>-73.96</td>\n",
       "      <td>40423.00</td>\n",
       "      <td>51.00</td>\n",
       "      <td>1.71</td>\n",
       "      <td>6.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>86384.00</td>\n",
       "      <td>3992.00</td>\n",
       "      <td>40.84</td>\n",
       "      <td>-73.90</td>\n",
       "      <td>3992.00</td>\n",
       "      <td>40.84</td>\n",
       "      <td>-73.90</td>\n",
       "      <td>43912.00</td>\n",
       "      <td>89.00</td>\n",
       "      <td>11.57</td>\n",
       "      <td>24.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       tripduration  start station id  start station latitude  \\\n",
       "count    1727963.00        1727963.00              1727963.00   \n",
       "mean        1116.43           1889.50                   40.74   \n",
       "std         1870.09           1513.88                    0.03   \n",
       "min           90.00             72.00                   40.66   \n",
       "25%          417.00            408.00                   40.72   \n",
       "50%          784.00           2003.00                   40.74   \n",
       "75%         1437.00           3374.00                   40.76   \n",
       "max        86384.00           3992.00                   40.84   \n",
       "\n",
       "       start station longitude  end station id  end station latitude  \\\n",
       "count               1727963.00      1727963.00            1727963.00   \n",
       "mean                    -73.98         1882.56                 40.74   \n",
       "std                       0.02         1514.29                  0.03   \n",
       "min                     -74.02           72.00                 40.66   \n",
       "25%                     -73.99          405.00                 40.72   \n",
       "50%                     -73.98         2002.00                 40.74   \n",
       "75%                     -73.96         3374.00                 40.76   \n",
       "max                     -73.90         3992.00                 40.84   \n",
       "\n",
       "       end station longitude      bikeid         age  distance(mi.)  \\\n",
       "count             1727963.00  1727963.00  1727963.00     1727963.00   \n",
       "mean                  -73.98    33756.79       40.03           1.25   \n",
       "std                     0.02     8276.76       12.22           1.05   \n",
       "min                   -74.05    14530.00       16.00           0.00   \n",
       "25%                   -73.99    29143.00       30.00           0.51   \n",
       "50%                   -73.98    36298.00       37.00           0.95   \n",
       "75%                   -73.96    40423.00       51.00           1.71   \n",
       "max                   -73.90    43912.00       89.00          11.57   \n",
       "\n",
       "       speed(miles per hr)  \n",
       "count           1727963.00  \n",
       "mean                  5.27  \n",
       "std                   2.50  \n",
       "min                   0.00  \n",
       "25%                   3.93  \n",
       "50%                   5.56  \n",
       "75%                   6.92  \n",
       "max                  24.33  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(df.describe(),2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the above summary, there were still some discrepencies. The min distance(mi.) is 0 which is possible if the rider started and ended in the same station. In the earlier cleaning proccess I removed only the ones that had short durations.It is possible that the rider went to a different destination and ended back at the starting station. For this reason I did not make any further changes to it. \n",
    "\n",
    "Next is the speed. According to the citi bike website the max speed that the citi bike can reach is 18 miles per hour but can reach up to 20 miles per hour and the speed limit for bikes in New York city is  also 20 miles per hour. So for this reason I dropped all the rows that had speed of more than 20 miles per hour."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop the rows with speed more than 20 miles per hour\n",
    "df = df[~(df[\"speed(miles per hr)\"] >20)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1727943, 17)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check the number of rows reduced\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save the cleaned data\n",
    "df.to_csv(\"Resources/202003_and_04_tripdata_clean.csv\")"
   ]
  },
  {
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}