CitiBike_2019_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": [
    "## 2019-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: 2019-08\n",
    "df_2019_08=pd.read_csv(\"Resources/Citibike_DataSource/JC-201908-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_2019_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_2019_08=df_2019_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>261</td>\n",
       "      <td>2019-08-01 00:14:55.9900</td>\n",
       "      <td>2019-08-01 00:19:17.4780</td>\n",
       "      <td>3205</td>\n",
       "      <td>JC Medical Center</td>\n",
       "      <td>40.71654</td>\n",
       "      <td>-74.049638</td>\n",
       "      <td>3192</td>\n",
       "      <td>Liberty Light Rail</td>\n",
       "      <td>40.711242</td>\n",
       "      <td>-74.055701</td>\n",
       "      <td>26268</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>172</td>\n",
       "      <td>2019-08-01 00:23:06.9910</td>\n",
       "      <td>2019-08-01 00:25:59.1480</td>\n",
       "      <td>3279</td>\n",
       "      <td>Dixon Mills</td>\n",
       "      <td>40.72163</td>\n",
       "      <td>-74.049968</td>\n",
       "      <td>3186</td>\n",
       "      <td>Grove St PATH</td>\n",
       "      <td>40.719586</td>\n",
       "      <td>-74.043117</td>\n",
       "      <td>26162</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1996</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration(seconds)                 starttime                  stoptime  \\\n",
       "0                    261  2019-08-01 00:14:55.9900  2019-08-01 00:19:17.4780   \n",
       "1                    172  2019-08-01 00:23:06.9910  2019-08-01 00:25:59.1480   \n",
       "\n",
       "   start_station_id start_station_name  start_station_latitude  \\\n",
       "0              3205  JC Medical Center                40.71654   \n",
       "1              3279        Dixon Mills                40.72163   \n",
       "\n",
       "   start_station_longitude  end_station_id    end_station_name  \\\n",
       "0               -74.049638            3192  Liberty Light Rail   \n",
       "1               -74.049968            3186       Grove St PATH   \n",
       "\n",
       "   end_station_latitude  end_station_longitude  bikeid    usertype  \\\n",
       "0             40.711242             -74.055701   26268  Subscriber   \n",
       "1             40.719586             -74.043117   26162  Subscriber   \n",
       "\n",
       "   birth_year  gender  \n",
       "0        1980       1  \n",
       "1        1996       1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2019_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                   int64\n",
       "gender                       int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2019_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                 0\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_2019_08.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Work on birth_year & calculate age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2019_08['birth_year']=df_2019_08['birth_year'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2019_08['birth_year']=df_2019_08.birth_year.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1980, 1996, 1991, 1988, 1960, 1981, 1985, 1978, 1969, 1987, 1984,\n",
       "       1971, 1986, 1998, 1964, 1995, 1982, 1966, 1967, 1994, 1989, 1962,\n",
       "       1973, 1961, 1977, 1975, 1974, 1979, 1976, 1958, 1983, 1970, 1990,\n",
       "       1993, 1972, 1992, 1963, 1954, 1968, 1956, 1965, 1951, 1955, 1997,\n",
       "       1959, 1953, 1944, 2000, 2002, 1999, 1888, 1957, 1949, 1952, 1940,\n",
       "       2001, 2003, 1950, 1887, 1946, 1947, 1948, 1900, 1905], dtype=int64)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2019_08.birth_year.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add column to calculate age\n",
    "df_2019_08['as_of_year']=2019\n",
    "df_2019_08['month']= 'August'\n",
    "df_2019_08['season']= 'Summer'\n",
    "\n",
    "# Use Data Year minus birth_year at the time to obtain raider's age\n",
    "df_2019_08['Age']=df_2019_08.as_of_year-df_2019_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,  27,  28,\n",
       "        29,  30,  31,  32,  33,  34,  35,  36,  37,  38,  39,  40,  41,\n",
       "        42,  43,  44,  45,  46,  47,  48,  49,  50,  51,  52,  53,  54,\n",
       "        55,  56,  57,  58,  59,  60,  61,  62,  63,  64,  65,  66,  67,\n",
       "        68,  69,  70,  71,  72,  73,  75,  79, 114, 119, 131, 132],\n",
       "      dtype=int64)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = df_2019_08['Age'].unique()\n",
    "a.sort()\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2019_08['Age'].values[df_2019_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, 75, 79], dtype=int64)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b = df_2019_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_2019_08.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2019_08['birth_year']=df_2019_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_2019_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_201908=[datetime.strptime(x,\"%Y-%m-%d %H:%M:%S.%f\").strftime(\"%Y/%m/%d %H:%M:%S\") for x in df_2019_08['starttime'].values.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Standarized Start Time\n",
    "end_time_201908=[datetime.strptime(x,\"%Y-%m-%d %H:%M:%S.%f\").strftime(\"%Y/%m/%d %H:%M:%S\") for x in df_2019_08['stoptime'].values.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add Columns\n",
    "df_2019_08['trip_start_time']=start_time_201908\n",
    "df_2019_08['trip_end_time']=end_time_201908"
   ]
  },
  {
   "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_2019_08['gender']=df_2019_08['gender'].replace([0], 'Unknown')\n",
    "df_2019_08['gender']=df_2019_08['gender'].replace([1], 'Male')\n",
    "df_2019_08['gender']=df_2019_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_2019_08.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 48711 entries, 0 to 48710\n",
      "Data columns (total 21 columns):\n",
      "tripduration(seconds)      48711 non-null int64\n",
      "starttime                  48711 non-null object\n",
      "stoptime                   48711 non-null object\n",
      "start_station_id           48711 non-null int64\n",
      "start_station_name         48711 non-null object\n",
      "start_station_latitude     48711 non-null float64\n",
      "start_station_longitude    48711 non-null float64\n",
      "end_station_id             48711 non-null int64\n",
      "end_station_name           48711 non-null object\n",
      "end_station_latitude       48711 non-null float64\n",
      "end_station_longitude      48711 non-null float64\n",
      "bikeid                     48711 non-null int64\n",
      "usertype                   48711 non-null object\n",
      "birth_year                 48711 non-null int32\n",
      "gender                     48711 non-null object\n",
      "as_of_year                 48711 non-null int64\n",
      "month                      48711 non-null object\n",
      "season                     48711 non-null object\n",
      "Age                        48711 non-null int64\n",
      "trip_start_time            48711 non-null object\n",
      "trip_end_time              48711 non-null object\n",
      "dtypes: float64(4), int32(1), int64(6), object(10)\n",
      "memory usage: 7.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df_2019_08.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write to CSV\n",
    "df_2019_08.to_csv(\"Resources/df_2019_08.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
}