CitiBike - 2020 Q1-Q2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies & Setup\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Files to Load\n",
    "# Files from Citi Bike trip history data, Source: https://s3.amazonaws.com/tripdata/index.html\n",
    "jan_2020 = \"data/202001-citibike-tripdata.csv\"\n",
    "feb_2020 = \"data/202002-citibike-tripdata.csv\"\n",
    "mar_2020 = \"data/202003-citibike-tripdata.csv\"\n",
    "apr_2020 = \"data/202004-citibike-tripdata.csv\"\n",
    "may_2020 = \"data/202005-citibike-tripdata.csv\"\n",
    "jun_2020 = \"data/202006-citibike-tripdata.csv\"\n",
    "\n",
    "# Read Quarter 1 and Quarter 2 Citi Bike Data Files & Store into Pandas DataFrames\n",
    "df_jan_2020 = pd.read_csv(jan_2020, encoding=\"ISO-8859-1\")\n",
    "df_feb_2020 = pd.read_csv(feb_2020, encoding=\"ISO-8859-1\")\n",
    "df_mar_2020 = pd.read_csv(mar_2020, encoding=\"ISO-8859-1\")\n",
    "df_apr_2020 = pd.read_csv(apr_2020, encoding=\"ISO-8859-1\")\n",
    "df_may_2020 = pd.read_csv(may_2020, encoding=\"ISO-8859-1\")\n",
    "df_jun_2020 = pd.read_csv(jun_2020, encoding=\"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_jan_2020 \n",
    "# df_feb_2020 \n",
    "# df_mar_2020\n",
    "# df_apr_2020 \n",
    "# df_may_2020 \n",
    "# df_jun_2020 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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",
       "      <td>0</td>\n",
       "      <td>789</td>\n",
       "      <td>2020-01-01 00:00:55.3900</td>\n",
       "      <td>2020-01-01 00:14:05.1470</td>\n",
       "      <td>504</td>\n",
       "      <td>1 Ave &amp; E 16 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>307</td>\n",
       "      <td>Canal St &amp; Rutgers St</td>\n",
       "      <td>40.714275</td>\n",
       "      <td>-73.989900</td>\n",
       "      <td>30326</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1992</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1541</td>\n",
       "      <td>2020-01-01 00:01:08.1020</td>\n",
       "      <td>2020-01-01 00:26:49.1780</td>\n",
       "      <td>3423</td>\n",
       "      <td>West Drive &amp; Prospect Park West</td>\n",
       "      <td>40.661063</td>\n",
       "      <td>-73.979453</td>\n",
       "      <td>3300</td>\n",
       "      <td>Prospect Park West &amp; 8 St</td>\n",
       "      <td>40.665147</td>\n",
       "      <td>-73.976376</td>\n",
       "      <td>17105</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1969</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1464</td>\n",
       "      <td>2020-01-01 00:01:42.1400</td>\n",
       "      <td>2020-01-01 00:26:07.0110</td>\n",
       "      <td>3687</td>\n",
       "      <td>E 33 St &amp; 1 Ave</td>\n",
       "      <td>40.743227</td>\n",
       "      <td>-73.974498</td>\n",
       "      <td>259</td>\n",
       "      <td>South St &amp; Whitehall St</td>\n",
       "      <td>40.701221</td>\n",
       "      <td>-74.012342</td>\n",
       "      <td>40177</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1963</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>592</td>\n",
       "      <td>2020-01-01 00:01:45.5610</td>\n",
       "      <td>2020-01-01 00:11:38.1550</td>\n",
       "      <td>346</td>\n",
       "      <td>Bank St &amp; Hudson St</td>\n",
       "      <td>40.736529</td>\n",
       "      <td>-74.006180</td>\n",
       "      <td>490</td>\n",
       "      <td>8 Ave &amp; W 33 St</td>\n",
       "      <td>40.751551</td>\n",
       "      <td>-73.993934</td>\n",
       "      <td>27690</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>702</td>\n",
       "      <td>2020-01-01 00:01:45.7880</td>\n",
       "      <td>2020-01-01 00:13:28.2400</td>\n",
       "      <td>372</td>\n",
       "      <td>Franklin Ave &amp; Myrtle Ave</td>\n",
       "      <td>40.694546</td>\n",
       "      <td>-73.958014</td>\n",
       "      <td>3637</td>\n",
       "      <td>Fulton St &amp; Waverly Ave</td>\n",
       "      <td>40.683239</td>\n",
       "      <td>-73.965996</td>\n",
       "      <td>32583</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1982</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882268</td>\n",
       "      <td>685</td>\n",
       "      <td>2020-06-30 23:59:41.1160</td>\n",
       "      <td>2020-07-01 00:11:06.7790</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>3746</td>\n",
       "      <td>6 Ave &amp; Broome St</td>\n",
       "      <td>40.724308</td>\n",
       "      <td>-74.004730</td>\n",
       "      <td>21056</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1993</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882269</td>\n",
       "      <td>446</td>\n",
       "      <td>2020-06-30 23:59:46.4260</td>\n",
       "      <td>2020-07-01 00:07:13.0860</td>\n",
       "      <td>3699</td>\n",
       "      <td>W 50 St &amp; 9 Ave</td>\n",
       "      <td>40.763605</td>\n",
       "      <td>-73.989180</td>\n",
       "      <td>523</td>\n",
       "      <td>W 38 St &amp; 8 Ave</td>\n",
       "      <td>40.754666</td>\n",
       "      <td>-73.991382</td>\n",
       "      <td>30164</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1969</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882270</td>\n",
       "      <td>439</td>\n",
       "      <td>2020-06-30 23:59:47.4770</td>\n",
       "      <td>2020-07-01 00:07:06.5590</td>\n",
       "      <td>3699</td>\n",
       "      <td>W 50 St &amp; 9 Ave</td>\n",
       "      <td>40.763605</td>\n",
       "      <td>-73.989180</td>\n",
       "      <td>523</td>\n",
       "      <td>W 38 St &amp; 8 Ave</td>\n",
       "      <td>40.754666</td>\n",
       "      <td>-73.991382</td>\n",
       "      <td>43742</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882271</td>\n",
       "      <td>890</td>\n",
       "      <td>2020-06-30 23:59:53.3950</td>\n",
       "      <td>2020-07-01 00:14:43.4270</td>\n",
       "      <td>3852</td>\n",
       "      <td>Stewart Ave &amp; Johnson Ave</td>\n",
       "      <td>40.708690</td>\n",
       "      <td>-73.925870</td>\n",
       "      <td>3054</td>\n",
       "      <td>Greene Ave &amp; Throop Ave</td>\n",
       "      <td>40.689493</td>\n",
       "      <td>-73.942061</td>\n",
       "      <td>15787</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1994</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882272</td>\n",
       "      <td>433</td>\n",
       "      <td>2020-06-30 23:59:53.9010</td>\n",
       "      <td>2020-07-01 00:07:07.0950</td>\n",
       "      <td>324</td>\n",
       "      <td>DeKalb Ave &amp; Hudson Ave</td>\n",
       "      <td>40.689888</td>\n",
       "      <td>-73.981013</td>\n",
       "      <td>261</td>\n",
       "      <td>Johnson St &amp; Gold St</td>\n",
       "      <td>40.694749</td>\n",
       "      <td>-73.983625</td>\n",
       "      <td>38767</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1990</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7508808 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         tripduration                 starttime                  stoptime  \\\n",
       "0                 789  2020-01-01 00:00:55.3900  2020-01-01 00:14:05.1470   \n",
       "1                1541  2020-01-01 00:01:08.1020  2020-01-01 00:26:49.1780   \n",
       "2                1464  2020-01-01 00:01:42.1400  2020-01-01 00:26:07.0110   \n",
       "3                 592  2020-01-01 00:01:45.5610  2020-01-01 00:11:38.1550   \n",
       "4                 702  2020-01-01 00:01:45.7880  2020-01-01 00:13:28.2400   \n",
       "...               ...                       ...                       ...   \n",
       "1882268           685  2020-06-30 23:59:41.1160  2020-07-01 00:11:06.7790   \n",
       "1882269           446  2020-06-30 23:59:46.4260  2020-07-01 00:07:13.0860   \n",
       "1882270           439  2020-06-30 23:59:47.4770  2020-07-01 00:07:06.5590   \n",
       "1882271           890  2020-06-30 23:59:53.3950  2020-07-01 00:14:43.4270   \n",
       "1882272           433  2020-06-30 23:59:53.9010  2020-07-01 00:07:07.0950   \n",
       "\n",
       "         start station id               start station name  \\\n",
       "0                     504                  1 Ave & E 16 St   \n",
       "1                    3423  West Drive & Prospect Park West   \n",
       "2                    3687                  E 33 St & 1 Ave   \n",
       "3                     346              Bank St & Hudson St   \n",
       "4                     372        Franklin Ave & Myrtle Ave   \n",
       "...                   ...                              ...   \n",
       "1882268               503               E 20 St & Park Ave   \n",
       "1882269              3699                  W 50 St & 9 Ave   \n",
       "1882270              3699                  W 50 St & 9 Ave   \n",
       "1882271              3852        Stewart Ave & Johnson Ave   \n",
       "1882272               324          DeKalb Ave & Hudson Ave   \n",
       "\n",
       "         start station latitude  start station longitude  end station id  \\\n",
       "0                     40.732219               -73.981656             307   \n",
       "1                     40.661063               -73.979453            3300   \n",
       "2                     40.743227               -73.974498             259   \n",
       "3                     40.736529               -74.006180             490   \n",
       "4                     40.694546               -73.958014            3637   \n",
       "...                         ...                      ...             ...   \n",
       "1882268               40.738274               -73.987520            3746   \n",
       "1882269               40.763605               -73.989180             523   \n",
       "1882270               40.763605               -73.989180             523   \n",
       "1882271               40.708690               -73.925870            3054   \n",
       "1882272               40.689888               -73.981013             261   \n",
       "\n",
       "                  end station name  end station latitude  \\\n",
       "0            Canal St & Rutgers St             40.714275   \n",
       "1        Prospect Park West & 8 St             40.665147   \n",
       "2          South St & Whitehall St             40.701221   \n",
       "3                  8 Ave & W 33 St             40.751551   \n",
       "4          Fulton St & Waverly Ave             40.683239   \n",
       "...                            ...                   ...   \n",
       "1882268          6 Ave & Broome St             40.724308   \n",
       "1882269            W 38 St & 8 Ave             40.754666   \n",
       "1882270            W 38 St & 8 Ave             40.754666   \n",
       "1882271    Greene Ave & Throop Ave             40.689493   \n",
       "1882272       Johnson St & Gold St             40.694749   \n",
       "\n",
       "         end station longitude  bikeid    usertype  birth year  gender  \n",
       "0                   -73.989900   30326  Subscriber        1992       1  \n",
       "1                   -73.976376   17105    Customer        1969       1  \n",
       "2                   -74.012342   40177  Subscriber        1963       1  \n",
       "3                   -73.993934   27690  Subscriber        1980       1  \n",
       "4                   -73.965996   32583  Subscriber        1982       1  \n",
       "...                        ...     ...         ...         ...     ...  \n",
       "1882268             -74.004730   21056    Customer        1993       2  \n",
       "1882269             -73.991382   30164    Customer        1969       0  \n",
       "1882270             -73.991382   43742    Customer        1986       1  \n",
       "1882271             -73.942061   15787  Subscriber        1994       1  \n",
       "1882272             -73.983625   38767    Customer        1990       1  \n",
       "\n",
       "[7508808 rows x 15 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combining all csv files, since no unique join column id\n",
    "combined_citibike_df = df_jan_2020.append(df_feb_2020, sort=False)\n",
    "combined_citibike_df = combined_citibike_df.append(df_mar_2020, sort=False)\n",
    "combined_citibike_df = combined_citibike_df.append(df_apr_2020, sort=False)\n",
    "combined_citibike_df = combined_citibike_df.append(df_may_2020, sort=False)\n",
    "combined_citibike_df = combined_citibike_df.append(df_jun_2020, sort=False)\n",
    "\n",
    "combined_citibike_df"
   ]
  },
  {
   "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</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",
       "      <td>0</td>\n",
       "      <td>789</td>\n",
       "      <td>2020-01-01 00:00:55.3900</td>\n",
       "      <td>2020-01-01 00:14:05.1470</td>\n",
       "      <td>504</td>\n",
       "      <td>1 Ave &amp; E 16 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>307</td>\n",
       "      <td>Canal St &amp; Rutgers St</td>\n",
       "      <td>40.714275</td>\n",
       "      <td>-73.989900</td>\n",
       "      <td>30326</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1992</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1541</td>\n",
       "      <td>2020-01-01 00:01:08.1020</td>\n",
       "      <td>2020-01-01 00:26:49.1780</td>\n",
       "      <td>3423</td>\n",
       "      <td>West Drive &amp; Prospect Park West</td>\n",
       "      <td>40.661063</td>\n",
       "      <td>-73.979453</td>\n",
       "      <td>3300</td>\n",
       "      <td>Prospect Park West &amp; 8 St</td>\n",
       "      <td>40.665147</td>\n",
       "      <td>-73.976376</td>\n",
       "      <td>17105</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1969</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1464</td>\n",
       "      <td>2020-01-01 00:01:42.1400</td>\n",
       "      <td>2020-01-01 00:26:07.0110</td>\n",
       "      <td>3687</td>\n",
       "      <td>E 33 St &amp; 1 Ave</td>\n",
       "      <td>40.743227</td>\n",
       "      <td>-73.974498</td>\n",
       "      <td>259</td>\n",
       "      <td>South St &amp; Whitehall St</td>\n",
       "      <td>40.701221</td>\n",
       "      <td>-74.012342</td>\n",
       "      <td>40177</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1963</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>592</td>\n",
       "      <td>2020-01-01 00:01:45.5610</td>\n",
       "      <td>2020-01-01 00:11:38.1550</td>\n",
       "      <td>346</td>\n",
       "      <td>Bank St &amp; Hudson St</td>\n",
       "      <td>40.736529</td>\n",
       "      <td>-74.006180</td>\n",
       "      <td>490</td>\n",
       "      <td>8 Ave &amp; W 33 St</td>\n",
       "      <td>40.751551</td>\n",
       "      <td>-73.993934</td>\n",
       "      <td>27690</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>702</td>\n",
       "      <td>2020-01-01 00:01:45.7880</td>\n",
       "      <td>2020-01-01 00:13:28.2400</td>\n",
       "      <td>372</td>\n",
       "      <td>Franklin Ave &amp; Myrtle Ave</td>\n",
       "      <td>40.694546</td>\n",
       "      <td>-73.958014</td>\n",
       "      <td>3637</td>\n",
       "      <td>Fulton St &amp; Waverly Ave</td>\n",
       "      <td>40.683239</td>\n",
       "      <td>-73.965996</td>\n",
       "      <td>32583</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1982</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882268</td>\n",
       "      <td>685</td>\n",
       "      <td>2020-06-30 23:59:41.1160</td>\n",
       "      <td>2020-07-01 00:11:06.7790</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>3746</td>\n",
       "      <td>6 Ave &amp; Broome St</td>\n",
       "      <td>40.724308</td>\n",
       "      <td>-74.004730</td>\n",
       "      <td>21056</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1993</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882269</td>\n",
       "      <td>446</td>\n",
       "      <td>2020-06-30 23:59:46.4260</td>\n",
       "      <td>2020-07-01 00:07:13.0860</td>\n",
       "      <td>3699</td>\n",
       "      <td>W 50 St &amp; 9 Ave</td>\n",
       "      <td>40.763605</td>\n",
       "      <td>-73.989180</td>\n",
       "      <td>523</td>\n",
       "      <td>W 38 St &amp; 8 Ave</td>\n",
       "      <td>40.754666</td>\n",
       "      <td>-73.991382</td>\n",
       "      <td>30164</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1969</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882270</td>\n",
       "      <td>439</td>\n",
       "      <td>2020-06-30 23:59:47.4770</td>\n",
       "      <td>2020-07-01 00:07:06.5590</td>\n",
       "      <td>3699</td>\n",
       "      <td>W 50 St &amp; 9 Ave</td>\n",
       "      <td>40.763605</td>\n",
       "      <td>-73.989180</td>\n",
       "      <td>523</td>\n",
       "      <td>W 38 St &amp; 8 Ave</td>\n",
       "      <td>40.754666</td>\n",
       "      <td>-73.991382</td>\n",
       "      <td>43742</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1986</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882271</td>\n",
       "      <td>890</td>\n",
       "      <td>2020-06-30 23:59:53.3950</td>\n",
       "      <td>2020-07-01 00:14:43.4270</td>\n",
       "      <td>3852</td>\n",
       "      <td>Stewart Ave &amp; Johnson Ave</td>\n",
       "      <td>40.708690</td>\n",
       "      <td>-73.925870</td>\n",
       "      <td>3054</td>\n",
       "      <td>Greene Ave &amp; Throop Ave</td>\n",
       "      <td>40.689493</td>\n",
       "      <td>-73.942061</td>\n",
       "      <td>15787</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1994</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1882272</td>\n",
       "      <td>433</td>\n",
       "      <td>2020-06-30 23:59:53.9010</td>\n",
       "      <td>2020-07-01 00:07:07.0950</td>\n",
       "      <td>324</td>\n",
       "      <td>DeKalb Ave &amp; Hudson Ave</td>\n",
       "      <td>40.689888</td>\n",
       "      <td>-73.981013</td>\n",
       "      <td>261</td>\n",
       "      <td>Johnson St &amp; Gold St</td>\n",
       "      <td>40.694749</td>\n",
       "      <td>-73.983625</td>\n",
       "      <td>38767</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1990</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7508808 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         tripduration                 starttime                  stoptime  \\\n",
       "0                 789  2020-01-01 00:00:55.3900  2020-01-01 00:14:05.1470   \n",
       "1                1541  2020-01-01 00:01:08.1020  2020-01-01 00:26:49.1780   \n",
       "2                1464  2020-01-01 00:01:42.1400  2020-01-01 00:26:07.0110   \n",
       "3                 592  2020-01-01 00:01:45.5610  2020-01-01 00:11:38.1550   \n",
       "4                 702  2020-01-01 00:01:45.7880  2020-01-01 00:13:28.2400   \n",
       "...               ...                       ...                       ...   \n",
       "1882268           685  2020-06-30 23:59:41.1160  2020-07-01 00:11:06.7790   \n",
       "1882269           446  2020-06-30 23:59:46.4260  2020-07-01 00:07:13.0860   \n",
       "1882270           439  2020-06-30 23:59:47.4770  2020-07-01 00:07:06.5590   \n",
       "1882271           890  2020-06-30 23:59:53.3950  2020-07-01 00:14:43.4270   \n",
       "1882272           433  2020-06-30 23:59:53.9010  2020-07-01 00:07:07.0950   \n",
       "\n",
       "         start station id               start station name  \\\n",
       "0                     504                  1 Ave & E 16 St   \n",
       "1                    3423  West Drive & Prospect Park West   \n",
       "2                    3687                  E 33 St & 1 Ave   \n",
       "3                     346              Bank St & Hudson St   \n",
       "4                     372        Franklin Ave & Myrtle Ave   \n",
       "...                   ...                              ...   \n",
       "1882268               503               E 20 St & Park Ave   \n",
       "1882269              3699                  W 50 St & 9 Ave   \n",
       "1882270              3699                  W 50 St & 9 Ave   \n",
       "1882271              3852        Stewart Ave & Johnson Ave   \n",
       "1882272               324          DeKalb Ave & Hudson Ave   \n",
       "\n",
       "         start station latitude  start station longitude  end station id  \\\n",
       "0                     40.732219               -73.981656             307   \n",
       "1                     40.661063               -73.979453            3300   \n",
       "2                     40.743227               -73.974498             259   \n",
       "3                     40.736529               -74.006180             490   \n",
       "4                     40.694546               -73.958014            3637   \n",
       "...                         ...                      ...             ...   \n",
       "1882268               40.738274               -73.987520            3746   \n",
       "1882269               40.763605               -73.989180             523   \n",
       "1882270               40.763605               -73.989180             523   \n",
       "1882271               40.708690               -73.925870            3054   \n",
       "1882272               40.689888               -73.981013             261   \n",
       "\n",
       "                  end station name  end station latitude  \\\n",
       "0            Canal St & Rutgers St             40.714275   \n",
       "1        Prospect Park West & 8 St             40.665147   \n",
       "2          South St & Whitehall St             40.701221   \n",
       "3                  8 Ave & W 33 St             40.751551   \n",
       "4          Fulton St & Waverly Ave             40.683239   \n",
       "...                            ...                   ...   \n",
       "1882268          6 Ave & Broome St             40.724308   \n",
       "1882269            W 38 St & 8 Ave             40.754666   \n",
       "1882270            W 38 St & 8 Ave             40.754666   \n",
       "1882271    Greene Ave & Throop Ave             40.689493   \n",
       "1882272       Johnson St & Gold St             40.694749   \n",
       "\n",
       "         end station longitude  bikeid    usertype  birth year   gender  \n",
       "0                   -73.989900   30326  Subscriber        1992     Male  \n",
       "1                   -73.976376   17105    Customer        1969     Male  \n",
       "2                   -74.012342   40177  Subscriber        1963     Male  \n",
       "3                   -73.993934   27690  Subscriber        1980     Male  \n",
       "4                   -73.965996   32583  Subscriber        1982     Male  \n",
       "...                        ...     ...         ...         ...      ...  \n",
       "1882268             -74.004730   21056    Customer        1993   Female  \n",
       "1882269             -73.991382   30164    Customer        1969  Unknown  \n",
       "1882270             -73.991382   43742    Customer        1986     Male  \n",
       "1882271             -73.942061   15787  Subscriber        1994     Male  \n",
       "1882272             -73.983625   38767    Customer        1990     Male  \n",
       "\n",
       "[7508808 rows x 15 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replacing Numbers in Gender for Male, Female, Unknown\n",
    "\n",
    "combined_citibike_df['gender'] = combined_citibike_df ['gender'].replace([0,1,2],['Unknown','Male','Female'])\n",
    "combined_citibike_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration               7508808\n",
       "starttime                  7508808\n",
       "stoptime                   7508808\n",
       "start station id           7508808\n",
       "start station name         7508808\n",
       "start station latitude     7508808\n",
       "start station longitude    7508808\n",
       "end station id             7508808\n",
       "end station name           7508808\n",
       "end station latitude       7508808\n",
       "end station longitude      7508808\n",
       "bikeid                     7508808\n",
       "usertype                   7508808\n",
       "birth year                 7508808\n",
       "gender                     7508808\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking to see count of datapoints\n",
    "combined_citibike_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration               7508808\n",
       "starttime                  7508808\n",
       "stoptime                   7508808\n",
       "start station id           7508808\n",
       "start station name         7508808\n",
       "start station latitude     7508808\n",
       "start station longitude    7508808\n",
       "end station id             7508808\n",
       "end station name           7508808\n",
       "end station latitude       7508808\n",
       "end station longitude      7508808\n",
       "bikeid                     7508808\n",
       "usertype                   7508808\n",
       "birth year                 7508808\n",
       "gender                     7508808\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping NaN\n",
    "combined_citibike_df = combined_citibike_df.dropna(how='any')\n",
    "combined_citibike_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7508808, 15)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_citibike_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration                 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                      object\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "combined_citibike_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save DataFrame to CSV\n",
    "combined_citibike_df.to_csv(\"data/six-month-2020-citibike-tripdata.csv\", index=False, header=True)"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}