CitiBike_2017_01.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import csv\n",
    "import time\n",
    "from datetime import datetime\n",
    "from datetime import date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2017-01\n",
    "-User Type (Customer = 24-hour pass or 3-day pass user; Subscriber = Annual Member)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import Jan CSV for Year: 2017-01\n",
    "df_2017_01=pd.read_csv(\"Resources/Citibike_DataSource/JC-201701-citibike-tripdata.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['tripduration', 'starttime', 'stoptime', 'start station id',\n",
       "       'start station name', 'start station latitude',\n",
       "       'start station longitude', 'end station id', 'end station name',\n",
       "       'end station latitude', 'end station longitude', 'bikeid', 'usertype',\n",
       "       'birth year', 'gender'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename Trip Duration to reflect values are in seconds\n",
    "# https://www.citibikenyc.com/system-data\n",
    "df_2017_01=df_2017_01.rename(columns={'tripduration':'tripduration(seconds)'\n",
    "                                      ,'start station id':'start_station_id'\n",
    "                                      ,'start station name':'start_station_name'\n",
    "                                      ,'start station latitude': 'start_station_latitude'\n",
    "                                      ,'start station longitude':'start_station_longitude'\n",
    "                                      ,'end station id':'end_station_id'\n",
    "                                      ,'end station name': 'end_station_name'\n",
    "                                      ,'end station latitude':'end_station_latitude'\n",
    "                                      ,'end station longitude':'end_station_longitude'\n",
    "                                      ,'birth year':'birth_year'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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(seconds)</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>148</td>\n",
       "      <td>1/1/2017 0:21</td>\n",
       "      <td>1/1/2017 0:24</td>\n",
       "      <td>3276</td>\n",
       "      <td>Marin Light Rail</td>\n",
       "      <td>40.714584</td>\n",
       "      <td>-74.042817</td>\n",
       "      <td>3185</td>\n",
       "      <td>City Hall</td>\n",
       "      <td>40.717733</td>\n",
       "      <td>-74.043845</td>\n",
       "      <td>24575</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1283</td>\n",
       "      <td>1/1/2017 0:24</td>\n",
       "      <td>1/1/2017 0:45</td>\n",
       "      <td>3183</td>\n",
       "      <td>Exchange Place</td>\n",
       "      <td>40.716247</td>\n",
       "      <td>-74.033459</td>\n",
       "      <td>3198</td>\n",
       "      <td>Heights Elevator</td>\n",
       "      <td>40.748716</td>\n",
       "      <td>-74.040443</td>\n",
       "      <td>24723</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1978.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration(seconds)      starttime       stoptime  start_station_id  \\\n",
       "0                    148  1/1/2017 0:21  1/1/2017 0:24              3276   \n",
       "1                   1283  1/1/2017 0:24  1/1/2017 0:45              3183   \n",
       "\n",
       "  start_station_name  start_station_latitude  start_station_longitude  \\\n",
       "0   Marin Light Rail               40.714584               -74.042817   \n",
       "1     Exchange Place               40.716247               -74.033459   \n",
       "\n",
       "   end_station_id  end_station_name  end_station_latitude  \\\n",
       "0            3185         City Hall             40.717733   \n",
       "1            3198  Heights Elevator             40.748716   \n",
       "\n",
       "   end_station_longitude  bikeid    usertype  birth_year  gender  \n",
       "0             -74.043845   24575  Subscriber      1983.0       1  \n",
       "1             -74.040443   24723  Subscriber      1978.0       1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration(seconds)        int64\n",
       "starttime                   object\n",
       "stoptime                    object\n",
       "start_station_id             int64\n",
       "start_station_name          object\n",
       "start_station_latitude     float64\n",
       "start_station_longitude    float64\n",
       "end_station_id               int64\n",
       "end_station_name            object\n",
       "end_station_latitude       float64\n",
       "end_station_longitude      float64\n",
       "bikeid                       int64\n",
       "usertype                    object\n",
       "birth_year                 float64\n",
       "gender                       int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identified na values & filled with \"0\" or \"Unknown\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration(seconds)        0\n",
       "starttime                    0\n",
       "stoptime                     0\n",
       "start_station_id             0\n",
       "start_station_name           0\n",
       "start_station_latitude       0\n",
       "start_station_longitude      0\n",
       "end_station_id               0\n",
       "end_station_name             0\n",
       "end_station_latitude         0\n",
       "end_station_longitude        0\n",
       "bikeid                       0\n",
       "usertype                    18\n",
       "birth_year                 386\n",
       "gender                       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check how many null values in each column\n",
    "df_2017_01.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Subscriber', 'Customer', nan], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.usertype.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_01['usertype']=df_2017_01['usertype'].fillna('Unknown')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Subscriber', 'Customer', 'Unknown'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.usertype.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Work on birth_year & calculate age\n",
    "-Note: any rider age greater than '85', it will be replce with age '0'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_01['birth_year']=df_2017_01['birth_year'].fillna('0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_01['birth_year']=df_2017_01.birth_year.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1983, 1978, 1989, 1961, 1993, 1970, 1967, 1984, 1987, 1985, 1964,\n",
       "       1986,    0, 1972, 1962, 1992, 1977, 1981, 1958, 1975, 1979, 1980,\n",
       "       1957, 1974, 1982, 1949, 1960, 1969, 1990, 1976, 1991, 1999, 1968,\n",
       "       1959, 1988, 1998, 1966, 1965, 1954, 1956, 1973, 1953, 1971, 1952,\n",
       "       1963, 1994, 1995, 1996, 1955, 1931, 1950, 1951, 1941, 1947, 1997,\n",
       "       1948], dtype=int64)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.birth_year.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add column to calculate age\n",
    "df_2017_01['as_of_year']=2017\n",
    "df_2017_01['month']= 'January'\n",
    "df_2017_01['season']= 'Winter'\n",
    "\n",
    "# Use Data Year minus birth_year at the time to obtain raider's age\n",
    "df_2017_01['Age']=df_2017_01.as_of_year-df_2017_01.birth_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([  18,   19,   20,   21,   22,   23,   24,   25,   26,   27,   28,\n",
       "         29,   30,   31,   32,   33,   34,   35,   36,   37,   38,   39,\n",
       "         40,   41,   42,   43,   44,   45,   46,   47,   48,   49,   50,\n",
       "         51,   52,   53,   54,   55,   56,   57,   58,   59,   60,   61,\n",
       "         62,   63,   64,   65,   66,   67,   68,   69,   70,   76,   86,\n",
       "       2017], dtype=int64)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = df_2017_01['Age'].unique()\n",
    "a.sort()\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_01['Age'].values[df_2017_01['Age'] > 85] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33,\n",
       "       34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,\n",
       "       51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67,\n",
       "       68, 69, 70, 76], dtype=int64)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b = df_2017_01['Age'].unique()\n",
    "b.sort()\n",
    "b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration(seconds)        int64\n",
       "starttime                   object\n",
       "stoptime                    object\n",
       "start_station_id             int64\n",
       "start_station_name          object\n",
       "start_station_latitude     float64\n",
       "start_station_longitude    float64\n",
       "end_station_id               int64\n",
       "end_station_name            object\n",
       "end_station_latitude       float64\n",
       "end_station_longitude      float64\n",
       "bikeid                       int64\n",
       "usertype                    object\n",
       "birth_year                   int32\n",
       "gender                       int64\n",
       "as_of_year                   int64\n",
       "month                       object\n",
       "season                      object\n",
       "Age                          int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Standarized Format for Start Time and End Time \n",
    "-.strftime(\"%Y/%m/%d %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Standarized Start Time and End time \n",
    "start_time_201701=[datetime.strptime(x,\"%m/%d/%Y %H:%M\").strftime(\"%Y/%m/%d %H:%M:%S\") for x in df_2017_01['starttime'].values.tolist()]\n",
    "end_time_201701=[datetime.strptime(x,\"%m/%d/%Y %H:%M\").strftime(\"%Y/%m/%d %H:%M:%S\") for x in df_2017_01['stoptime'].values.tolist()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add Columns\n",
    "df_2017_01['trip_start_time']=start_time_201701\n",
    "df_2017_01['trip_end_time']=end_time_201701\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean the Gender column\n",
    "--Gender (Zero=unknown; 1=male; 2=female) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace Gender Column\n",
    "# Gender (Zero=unknown; 1=male; 2=female)\n",
    "df_2017_01['gender']=df_2017_01['gender'].replace([0], 'Unknown')\n",
    "df_2017_01['gender']=df_2017_01['gender'].replace([1], 'Male')\n",
    "df_2017_01['gender']=df_2017_01['gender'].replace([2], 'Female')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration(seconds)      0\n",
       "starttime                  0\n",
       "stoptime                   0\n",
       "start_station_id           0\n",
       "start_station_name         0\n",
       "start_station_latitude     0\n",
       "start_station_longitude    0\n",
       "end_station_id             0\n",
       "end_station_name           0\n",
       "end_station_latitude       0\n",
       "end_station_longitude      0\n",
       "bikeid                     0\n",
       "usertype                   0\n",
       "birth_year                 0\n",
       "gender                     0\n",
       "as_of_year                 0\n",
       "month                      0\n",
       "season                     0\n",
       "Age                        0\n",
       "trip_start_time            0\n",
       "trip_end_time              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_01.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 12926 entries, 0 to 12925\n",
      "Data columns (total 21 columns):\n",
      "tripduration(seconds)      12926 non-null int64\n",
      "starttime                  12926 non-null object\n",
      "stoptime                   12926 non-null object\n",
      "start_station_id           12926 non-null int64\n",
      "start_station_name         12926 non-null object\n",
      "start_station_latitude     12926 non-null float64\n",
      "start_station_longitude    12926 non-null float64\n",
      "end_station_id             12926 non-null int64\n",
      "end_station_name           12926 non-null object\n",
      "end_station_latitude       12926 non-null float64\n",
      "end_station_longitude      12926 non-null float64\n",
      "bikeid                     12926 non-null int64\n",
      "usertype                   12926 non-null object\n",
      "birth_year                 12926 non-null int32\n",
      "gender                     12926 non-null object\n",
      "as_of_year                 12926 non-null int64\n",
      "month                      12926 non-null object\n",
      "season                     12926 non-null object\n",
      "Age                        12926 non-null int64\n",
      "trip_start_time            12926 non-null object\n",
      "trip_end_time              12926 non-null object\n",
      "dtypes: float64(4), int32(1), int64(6), object(10)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_2017_01.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write to CSV\n",
    "df_2017_01.to_csv(\"Resources/df_2017_01.csv\", index=False)"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}