CitiBike_2017_08.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-08\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-08\n",
    "df_2017_08=pd.read_csv(\"Resources/Citibike_DataSource/JC-201708-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_08.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_08=df_2017_08.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>2110</td>\n",
       "      <td>2017-08-01 00:00:00</td>\n",
       "      <td>2017-08-01 00:35:11</td>\n",
       "      <td>470</td>\n",
       "      <td>W 20 St &amp; 8 Ave</td>\n",
       "      <td>40.743453</td>\n",
       "      <td>-74.000040</td>\n",
       "      <td>3289</td>\n",
       "      <td>W 90 St &amp; Amsterdam Ave</td>\n",
       "      <td>40.790179</td>\n",
       "      <td>-73.972889</td>\n",
       "      <td>20954</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1978.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160</td>\n",
       "      <td>2017-08-01 00:00:10</td>\n",
       "      <td>2017-08-01 00:02:51</td>\n",
       "      <td>348</td>\n",
       "      <td>W Broadway &amp; Spring St</td>\n",
       "      <td>40.724910</td>\n",
       "      <td>-74.001547</td>\n",
       "      <td>151</td>\n",
       "      <td>Cleveland Pl &amp; Spring St</td>\n",
       "      <td>40.722104</td>\n",
       "      <td>-73.997249</td>\n",
       "      <td>15164</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  \\\n",
       "0                   2110  2017-08-01 00:00:00  2017-08-01 00:35:11   \n",
       "1                    160  2017-08-01 00:00:10  2017-08-01 00:02:51   \n",
       "\n",
       "   start_station_id      start_station_name  start_station_latitude  \\\n",
       "0               470         W 20 St & 8 Ave               40.743453   \n",
       "1               348  W Broadway & Spring St               40.724910   \n",
       "\n",
       "   start_station_longitude  end_station_id          end_station_name  \\\n",
       "0               -74.000040            3289   W 90 St & Amsterdam Ave   \n",
       "1               -74.001547             151  Cleveland Pl & Spring St   \n",
       "\n",
       "   end_station_latitude  end_station_longitude  bikeid    usertype  \\\n",
       "0             40.790179             -73.972889   20954  Subscriber   \n",
       "1             40.722104             -73.997249   15164  Subscriber   \n",
       "\n",
       "   birth_year  gender  \n",
       "0      1978.0       2  \n",
       "1      1978.0       1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_08.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_08.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                        0\n",
       "birth_year                 224716\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_08.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Work on birth_year & calculate age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_08['birth_year']=df_2017_08['birth_year'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_08['birth_year']=df_2017_08.birth_year.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1978, 1962, 1990, 1983, 1967, 1986, 1972, 1989, 1955, 1985, 1987,\n",
       "       1952,    0, 1976, 1974, 1982, 1992, 1988, 1993, 1971, 1979, 1994,\n",
       "       1968, 1996, 1973, 1984, 1981, 1977, 1991, 1999, 1959, 1995, 1969,\n",
       "       1965, 1951, 1956, 2000, 1970, 1975, 1964, 1966, 1980, 1963, 1961,\n",
       "       1998, 1997, 1958, 1953, 1960, 1932, 1954, 1957, 1940, 1944, 1949,\n",
       "       1950, 1946, 1947, 1948, 1943, 1945, 1913, 1942, 1941, 1934, 1938,\n",
       "       1885, 1900, 1923, 2001, 1935, 1933, 1936, 1910, 1912, 1917, 1901,\n",
       "       1939, 1899, 1937, 1893, 1918, 1888, 1931, 1887, 1896, 1928, 1929,\n",
       "       1921, 1915, 1886, 1895, 1930, 1907, 1920, 1905, 1916, 1890],\n",
       "      dtype=int64)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_08.birth_year.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add column to calculate age\n",
    "df_2017_08['as_of_year']=2017\n",
    "df_2017_08['month']= 'August'\n",
    "df_2017_08['season']= 'Summer'\n",
    "\n",
    "# Use Data Year minus birth_year at the time to obtain raider's age\n",
    "df_2017_08['Age']=df_2017_08.as_of_year-df_2017_08.birth_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([  16,   17,   18,   19,   20,   21,   22,   23,   24,   25,   26,\n",
       "         27,   28,   29,   30,   31,   32,   33,   34,   35,   36,   37,\n",
       "         38,   39,   40,   41,   42,   43,   44,   45,   46,   47,   48,\n",
       "         49,   50,   51,   52,   53,   54,   55,   56,   57,   58,   59,\n",
       "         60,   61,   62,   63,   64,   65,   66,   67,   68,   69,   70,\n",
       "         71,   72,   73,   74,   75,   76,   77,   78,   79,   80,   81,\n",
       "         82,   83,   84,   85,   86,   87,   88,   89,   94,   96,   97,\n",
       "         99,  100,  101,  102,  104,  105,  107,  110,  112,  116,  117,\n",
       "        118,  121,  122,  124,  127,  129,  130,  131,  132, 2017],\n",
       "      dtype=int64)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = df_2017_08['Age'].unique()\n",
    "a.sort()\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_08['Age'].values[df_2017_08['Age'] > 85] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,\n",
       "       32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,\n",
       "       49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65,\n",
       "       66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82,\n",
       "       83, 84, 85], dtype=int64)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b = df_2017_08['Age'].unique()\n",
    "b.sort()\n",
    "b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_08.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2017_08['birth_year']=df_2017_08.birth_year.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2017_08.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": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Standarized Start Time\n",
    "start_time_201708=[datetime.strptime(x,\"%Y-%m-%d %H:%M:%S\").strftime(\"%Y/%m/%d %H:%M:%S\") for x in df_2017_08['starttime'].values.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Standarized Start Time\n",
    "end_time_201708=[datetime.strptime(x,\"%Y-%m-%d %H:%M:%S\").strftime(\"%Y/%m/%d %H:%M:%S\") for x in df_2017_08['stoptime'].values.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add Columns\n",
    "df_2017_08['trip_start_time']=start_time_201708\n",
    "df_2017_08['trip_end_time']=end_time_201708"
   ]
  },
  {
   "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_08['gender']=df_2017_08['gender'].replace([0], 'Unknown')\n",
    "df_2017_08['gender']=df_2017_08['gender'].replace([1], 'Male')\n",
    "df_2017_08['gender']=df_2017_08['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_08.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1816498 entries, 0 to 1816497\n",
      "Data columns (total 21 columns):\n",
      "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                     object\n",
      "as_of_year                 int64\n",
      "month                      object\n",
      "season                     object\n",
      "Age                        int64\n",
      "trip_start_time            object\n",
      "trip_end_time              object\n",
      "dtypes: float64(4), int32(1), int64(6), object(10)\n",
      "memory usage: 284.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df_2017_08.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write to CSV\n",
    "df_2017_08.to_csv(\"Resources/df_2017_08.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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
}