merge_files.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tableau Homework CitiBike Analysis\n",
    "\n",
    "Data Retrieved From: https://www.citibikenyc.com/system-data - https://s3.amazonaws.com/tripdata/index.html\n",
    "Data Format: Indiviual zipped csv files for each month JAN-DEC of year 2019\n",
    "\n",
    "This notebook is used to combine this data into one large dataset that can easily be used in Tableu without the need to join each monthly file individually. This notebook is also used to reduce the size of the final dataset as Tableu public limits the data size of each workbook to 15,000,000 rows and this dataset is in excess of 20,000,000. \n",
    "\n",
    "To reduce the size of the dataset any rows with missing gender data were removed. The birth year column was removed as I did not use that in my analysis. I also removed rows with trip duration of less than 6 minutes to get the dataset down to the appropriate size. I also believe it is unlikely many users take rides of less than 6 minutes so some of this may be suspicious data that I felt was save to remove."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#found a way to merge many files at once with something called glob\n",
    "\n",
    "import pandas as pd\n",
    "import glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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>Unnamed: 0</th>\n",
       "      <th>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>320</td>\n",
       "      <td>2019-01-01 00:01:47.4010</td>\n",
       "      <td>2019-01-01 00:07:07.5810</td>\n",
       "      <td>3160.0</td>\n",
       "      <td>Central Park West &amp; W 76 St</td>\n",
       "      <td>40.778968</td>\n",
       "      <td>-73.973747</td>\n",
       "      <td>3283.0</td>\n",
       "      <td>W 89 St &amp; Columbus Ave</td>\n",
       "      <td>40.788221</td>\n",
       "      <td>-73.970416</td>\n",
       "      <td>15839</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1971</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>316</td>\n",
       "      <td>2019-01-01 00:04:43.7360</td>\n",
       "      <td>2019-01-01 00:10:00.6080</td>\n",
       "      <td>519.0</td>\n",
       "      <td>Pershing Square North</td>\n",
       "      <td>40.751873</td>\n",
       "      <td>-73.977706</td>\n",
       "      <td>518.0</td>\n",
       "      <td>E 39 St &amp; 2 Ave</td>\n",
       "      <td>40.747804</td>\n",
       "      <td>-73.973442</td>\n",
       "      <td>32723</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1964</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>591</td>\n",
       "      <td>2019-01-01 00:06:03.9970</td>\n",
       "      <td>2019-01-01 00:15:55.4380</td>\n",
       "      <td>3171.0</td>\n",
       "      <td>Amsterdam Ave &amp; W 82 St</td>\n",
       "      <td>40.785247</td>\n",
       "      <td>-73.976673</td>\n",
       "      <td>3154.0</td>\n",
       "      <td>E 77 St &amp; 3 Ave</td>\n",
       "      <td>40.773142</td>\n",
       "      <td>-73.958562</td>\n",
       "      <td>27451</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1987</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2719</td>\n",
       "      <td>2019-01-01 00:07:03.5450</td>\n",
       "      <td>2019-01-01 00:52:22.6500</td>\n",
       "      <td>504.0</td>\n",
       "      <td>1 Ave &amp; E 16 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>3709.0</td>\n",
       "      <td>W 15 St &amp; 6 Ave</td>\n",
       "      <td>40.738046</td>\n",
       "      <td>-73.996430</td>\n",
       "      <td>21579</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1990</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>303</td>\n",
       "      <td>2019-01-01 00:07:35.9450</td>\n",
       "      <td>2019-01-01 00:12:39.5020</td>\n",
       "      <td>229.0</td>\n",
       "      <td>Great Jones St</td>\n",
       "      <td>40.727434</td>\n",
       "      <td>-73.993790</td>\n",
       "      <td>503.0</td>\n",
       "      <td>E 20 St &amp; Park Ave</td>\n",
       "      <td>40.738274</td>\n",
       "      <td>-73.987520</td>\n",
       "      <td>35379</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1979</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551692</th>\n",
       "      <td>955205</td>\n",
       "      <td>93</td>\n",
       "      <td>2019-12-31 23:58:18.0160</td>\n",
       "      <td>2019-12-31 23:59:51.6360</td>\n",
       "      <td>3141.0</td>\n",
       "      <td>1 Ave &amp; E 68 St</td>\n",
       "      <td>40.765005</td>\n",
       "      <td>-73.958185</td>\n",
       "      <td>3142.0</td>\n",
       "      <td>1 Ave &amp; E 62 St</td>\n",
       "      <td>40.761227</td>\n",
       "      <td>-73.960940</td>\n",
       "      <td>28734</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1985</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551693</th>\n",
       "      <td>955206</td>\n",
       "      <td>786</td>\n",
       "      <td>2019-12-31 23:58:59.8960</td>\n",
       "      <td>2020-01-01 00:12:06.3460</td>\n",
       "      <td>490.0</td>\n",
       "      <td>8 Ave &amp; W 33 St</td>\n",
       "      <td>40.751551</td>\n",
       "      <td>-73.993934</td>\n",
       "      <td>513.0</td>\n",
       "      <td>W 56 St &amp; 10 Ave</td>\n",
       "      <td>40.768254</td>\n",
       "      <td>-73.988639</td>\n",
       "      <td>41418</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1991</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551694</th>\n",
       "      <td>955207</td>\n",
       "      <td>351</td>\n",
       "      <td>2019-12-31 23:59:03.6950</td>\n",
       "      <td>2020-01-01 00:04:54.8730</td>\n",
       "      <td>3349.0</td>\n",
       "      <td>Grand Army Plaza &amp; Plaza St West</td>\n",
       "      <td>40.672968</td>\n",
       "      <td>-73.970880</td>\n",
       "      <td>3368.0</td>\n",
       "      <td>5 Ave &amp; 3 St</td>\n",
       "      <td>40.672816</td>\n",
       "      <td>-73.983524</td>\n",
       "      <td>27746</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1935</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551695</th>\n",
       "      <td>955208</td>\n",
       "      <td>1571</td>\n",
       "      <td>2019-12-31 23:59:21.3610</td>\n",
       "      <td>2020-01-01 00:25:32.9420</td>\n",
       "      <td>252.0</td>\n",
       "      <td>MacDougal St &amp; Washington Sq</td>\n",
       "      <td>40.732264</td>\n",
       "      <td>-73.998522</td>\n",
       "      <td>366.0</td>\n",
       "      <td>Clinton Ave &amp; Myrtle Ave</td>\n",
       "      <td>40.693261</td>\n",
       "      <td>-73.968896</td>\n",
       "      <td>28506</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551696</th>\n",
       "      <td>955209</td>\n",
       "      <td>310</td>\n",
       "      <td>2019-12-31 23:59:55.2960</td>\n",
       "      <td>2020-01-01 00:05:05.7270</td>\n",
       "      <td>3351.0</td>\n",
       "      <td>E 102 St &amp; 1 Ave</td>\n",
       "      <td>40.786995</td>\n",
       "      <td>-73.941648</td>\n",
       "      <td>3379.0</td>\n",
       "      <td>E 103 St &amp; Lexington Ave</td>\n",
       "      <td>40.790305</td>\n",
       "      <td>-73.947558</td>\n",
       "      <td>21013</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1969</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20551697 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Unnamed: 0  tripduration                 starttime  \\\n",
       "0                  0           320  2019-01-01 00:01:47.4010   \n",
       "1                  1           316  2019-01-01 00:04:43.7360   \n",
       "2                  2           591  2019-01-01 00:06:03.9970   \n",
       "3                  3          2719  2019-01-01 00:07:03.5450   \n",
       "4                  4           303  2019-01-01 00:07:35.9450   \n",
       "...              ...           ...                       ...   \n",
       "20551692      955205            93  2019-12-31 23:58:18.0160   \n",
       "20551693      955206           786  2019-12-31 23:58:59.8960   \n",
       "20551694      955207           351  2019-12-31 23:59:03.6950   \n",
       "20551695      955208          1571  2019-12-31 23:59:21.3610   \n",
       "20551696      955209           310  2019-12-31 23:59:55.2960   \n",
       "\n",
       "                          stoptime  start station id  \\\n",
       "0         2019-01-01 00:07:07.5810            3160.0   \n",
       "1         2019-01-01 00:10:00.6080             519.0   \n",
       "2         2019-01-01 00:15:55.4380            3171.0   \n",
       "3         2019-01-01 00:52:22.6500             504.0   \n",
       "4         2019-01-01 00:12:39.5020             229.0   \n",
       "...                            ...               ...   \n",
       "20551692  2019-12-31 23:59:51.6360            3141.0   \n",
       "20551693  2020-01-01 00:12:06.3460             490.0   \n",
       "20551694  2020-01-01 00:04:54.8730            3349.0   \n",
       "20551695  2020-01-01 00:25:32.9420             252.0   \n",
       "20551696  2020-01-01 00:05:05.7270            3351.0   \n",
       "\n",
       "                        start station name  start station latitude  \\\n",
       "0              Central Park West & W 76 St               40.778968   \n",
       "1                    Pershing Square North               40.751873   \n",
       "2                  Amsterdam Ave & W 82 St               40.785247   \n",
       "3                          1 Ave & E 16 St               40.732219   \n",
       "4                           Great Jones St               40.727434   \n",
       "...                                    ...                     ...   \n",
       "20551692                   1 Ave & E 68 St               40.765005   \n",
       "20551693                   8 Ave & W 33 St               40.751551   \n",
       "20551694  Grand Army Plaza & Plaza St West               40.672968   \n",
       "20551695      MacDougal St & Washington Sq               40.732264   \n",
       "20551696                  E 102 St & 1 Ave               40.786995   \n",
       "\n",
       "          start station longitude  end station id          end station name  \\\n",
       "0                      -73.973747          3283.0    W 89 St & Columbus Ave   \n",
       "1                      -73.977706           518.0           E 39 St & 2 Ave   \n",
       "2                      -73.976673          3154.0           E 77 St & 3 Ave   \n",
       "3                      -73.981656          3709.0           W 15 St & 6 Ave   \n",
       "4                      -73.993790           503.0        E 20 St & Park Ave   \n",
       "...                           ...             ...                       ...   \n",
       "20551692               -73.958185          3142.0           1 Ave & E 62 St   \n",
       "20551693               -73.993934           513.0          W 56 St & 10 Ave   \n",
       "20551694               -73.970880          3368.0              5 Ave & 3 St   \n",
       "20551695               -73.998522           366.0  Clinton Ave & Myrtle Ave   \n",
       "20551696               -73.941648          3379.0  E 103 St & Lexington Ave   \n",
       "\n",
       "          end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0                    40.788221             -73.970416   15839  Subscriber   \n",
       "1                    40.747804             -73.973442   32723  Subscriber   \n",
       "2                    40.773142             -73.958562   27451  Subscriber   \n",
       "3                    40.738046             -73.996430   21579  Subscriber   \n",
       "4                    40.738274             -73.987520   35379  Subscriber   \n",
       "...                        ...                    ...     ...         ...   \n",
       "20551692             40.761227             -73.960940   28734  Subscriber   \n",
       "20551693             40.768254             -73.988639   41418  Subscriber   \n",
       "20551694             40.672816             -73.983524   27746  Subscriber   \n",
       "20551695             40.693261             -73.968896   28506  Subscriber   \n",
       "20551696             40.790305             -73.947558   21013  Subscriber   \n",
       "\n",
       "          birth year  gender  \n",
       "0               1971       1  \n",
       "1               1964       1  \n",
       "2               1987       1  \n",
       "3               1990       1  \n",
       "4               1979       1  \n",
       "...              ...     ...  \n",
       "20551692        1985       2  \n",
       "20551693        1991       1  \n",
       "20551694        1935       2  \n",
       "20551695        1980       1  \n",
       "20551696        1969       0  \n",
       "\n",
       "[20551697 rows x 16 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "citi_files = glob.glob(\"*.csv\") \n",
    "df = pd.concat((pd.read_csv(f, header = 0) for f in citi_files))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>320</td>\n",
       "      <td>2019-01-01 00:01:47.4010</td>\n",
       "      <td>2019-01-01 00:07:07.5810</td>\n",
       "      <td>3160.0</td>\n",
       "      <td>Central Park West &amp; W 76 St</td>\n",
       "      <td>40.778968</td>\n",
       "      <td>-73.973747</td>\n",
       "      <td>3283.0</td>\n",
       "      <td>W 89 St &amp; Columbus Ave</td>\n",
       "      <td>40.788221</td>\n",
       "      <td>-73.970416</td>\n",
       "      <td>15839</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>316</td>\n",
       "      <td>2019-01-01 00:04:43.7360</td>\n",
       "      <td>2019-01-01 00:10:00.6080</td>\n",
       "      <td>519.0</td>\n",
       "      <td>Pershing Square North</td>\n",
       "      <td>40.751873</td>\n",
       "      <td>-73.977706</td>\n",
       "      <td>518.0</td>\n",
       "      <td>E 39 St &amp; 2 Ave</td>\n",
       "      <td>40.747804</td>\n",
       "      <td>-73.973442</td>\n",
       "      <td>32723</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>591</td>\n",
       "      <td>2019-01-01 00:06:03.9970</td>\n",
       "      <td>2019-01-01 00:15:55.4380</td>\n",
       "      <td>3171.0</td>\n",
       "      <td>Amsterdam Ave &amp; W 82 St</td>\n",
       "      <td>40.785247</td>\n",
       "      <td>-73.976673</td>\n",
       "      <td>3154.0</td>\n",
       "      <td>E 77 St &amp; 3 Ave</td>\n",
       "      <td>40.773142</td>\n",
       "      <td>-73.958562</td>\n",
       "      <td>27451</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2719</td>\n",
       "      <td>2019-01-01 00:07:03.5450</td>\n",
       "      <td>2019-01-01 00:52:22.6500</td>\n",
       "      <td>504.0</td>\n",
       "      <td>1 Ave &amp; E 16 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>3709.0</td>\n",
       "      <td>W 15 St &amp; 6 Ave</td>\n",
       "      <td>40.738046</td>\n",
       "      <td>-73.996430</td>\n",
       "      <td>21579</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>303</td>\n",
       "      <td>2019-01-01 00:07:35.9450</td>\n",
       "      <td>2019-01-01 00:12:39.5020</td>\n",
       "      <td>229.0</td>\n",
       "      <td>Great Jones St</td>\n",
       "      <td>40.727434</td>\n",
       "      <td>-73.993790</td>\n",
       "      <td>503.0</td>\n",
       "      <td>E 20 St &amp; Park Ave</td>\n",
       "      <td>40.738274</td>\n",
       "      <td>-73.987520</td>\n",
       "      <td>35379</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551692</th>\n",
       "      <td>93</td>\n",
       "      <td>2019-12-31 23:58:18.0160</td>\n",
       "      <td>2019-12-31 23:59:51.6360</td>\n",
       "      <td>3141.0</td>\n",
       "      <td>1 Ave &amp; E 68 St</td>\n",
       "      <td>40.765005</td>\n",
       "      <td>-73.958185</td>\n",
       "      <td>3142.0</td>\n",
       "      <td>1 Ave &amp; E 62 St</td>\n",
       "      <td>40.761227</td>\n",
       "      <td>-73.960940</td>\n",
       "      <td>28734</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551693</th>\n",
       "      <td>786</td>\n",
       "      <td>2019-12-31 23:58:59.8960</td>\n",
       "      <td>2020-01-01 00:12:06.3460</td>\n",
       "      <td>490.0</td>\n",
       "      <td>8 Ave &amp; W 33 St</td>\n",
       "      <td>40.751551</td>\n",
       "      <td>-73.993934</td>\n",
       "      <td>513.0</td>\n",
       "      <td>W 56 St &amp; 10 Ave</td>\n",
       "      <td>40.768254</td>\n",
       "      <td>-73.988639</td>\n",
       "      <td>41418</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551694</th>\n",
       "      <td>351</td>\n",
       "      <td>2019-12-31 23:59:03.6950</td>\n",
       "      <td>2020-01-01 00:04:54.8730</td>\n",
       "      <td>3349.0</td>\n",
       "      <td>Grand Army Plaza &amp; Plaza St West</td>\n",
       "      <td>40.672968</td>\n",
       "      <td>-73.970880</td>\n",
       "      <td>3368.0</td>\n",
       "      <td>5 Ave &amp; 3 St</td>\n",
       "      <td>40.672816</td>\n",
       "      <td>-73.983524</td>\n",
       "      <td>27746</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551695</th>\n",
       "      <td>1571</td>\n",
       "      <td>2019-12-31 23:59:21.3610</td>\n",
       "      <td>2020-01-01 00:25:32.9420</td>\n",
       "      <td>252.0</td>\n",
       "      <td>MacDougal St &amp; Washington Sq</td>\n",
       "      <td>40.732264</td>\n",
       "      <td>-73.998522</td>\n",
       "      <td>366.0</td>\n",
       "      <td>Clinton Ave &amp; Myrtle Ave</td>\n",
       "      <td>40.693261</td>\n",
       "      <td>-73.968896</td>\n",
       "      <td>28506</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551696</th>\n",
       "      <td>310</td>\n",
       "      <td>2019-12-31 23:59:55.2960</td>\n",
       "      <td>2020-01-01 00:05:05.7270</td>\n",
       "      <td>3351.0</td>\n",
       "      <td>E 102 St &amp; 1 Ave</td>\n",
       "      <td>40.786995</td>\n",
       "      <td>-73.941648</td>\n",
       "      <td>3379.0</td>\n",
       "      <td>E 103 St &amp; Lexington Ave</td>\n",
       "      <td>40.790305</td>\n",
       "      <td>-73.947558</td>\n",
       "      <td>21013</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20551697 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          tripduration                 starttime                  stoptime  \\\n",
       "0                  320  2019-01-01 00:01:47.4010  2019-01-01 00:07:07.5810   \n",
       "1                  316  2019-01-01 00:04:43.7360  2019-01-01 00:10:00.6080   \n",
       "2                  591  2019-01-01 00:06:03.9970  2019-01-01 00:15:55.4380   \n",
       "3                 2719  2019-01-01 00:07:03.5450  2019-01-01 00:52:22.6500   \n",
       "4                  303  2019-01-01 00:07:35.9450  2019-01-01 00:12:39.5020   \n",
       "...                ...                       ...                       ...   \n",
       "20551692            93  2019-12-31 23:58:18.0160  2019-12-31 23:59:51.6360   \n",
       "20551693           786  2019-12-31 23:58:59.8960  2020-01-01 00:12:06.3460   \n",
       "20551694           351  2019-12-31 23:59:03.6950  2020-01-01 00:04:54.8730   \n",
       "20551695          1571  2019-12-31 23:59:21.3610  2020-01-01 00:25:32.9420   \n",
       "20551696           310  2019-12-31 23:59:55.2960  2020-01-01 00:05:05.7270   \n",
       "\n",
       "          start station id                start station name  \\\n",
       "0                   3160.0       Central Park West & W 76 St   \n",
       "1                    519.0             Pershing Square North   \n",
       "2                   3171.0           Amsterdam Ave & W 82 St   \n",
       "3                    504.0                   1 Ave & E 16 St   \n",
       "4                    229.0                    Great Jones St   \n",
       "...                    ...                               ...   \n",
       "20551692            3141.0                   1 Ave & E 68 St   \n",
       "20551693             490.0                   8 Ave & W 33 St   \n",
       "20551694            3349.0  Grand Army Plaza & Plaza St West   \n",
       "20551695             252.0      MacDougal St & Washington Sq   \n",
       "20551696            3351.0                  E 102 St & 1 Ave   \n",
       "\n",
       "          start station latitude  start station longitude  end station id  \\\n",
       "0                      40.778968               -73.973747          3283.0   \n",
       "1                      40.751873               -73.977706           518.0   \n",
       "2                      40.785247               -73.976673          3154.0   \n",
       "3                      40.732219               -73.981656          3709.0   \n",
       "4                      40.727434               -73.993790           503.0   \n",
       "...                          ...                      ...             ...   \n",
       "20551692               40.765005               -73.958185          3142.0   \n",
       "20551693               40.751551               -73.993934           513.0   \n",
       "20551694               40.672968               -73.970880          3368.0   \n",
       "20551695               40.732264               -73.998522           366.0   \n",
       "20551696               40.786995               -73.941648          3379.0   \n",
       "\n",
       "                  end station name  end station latitude  \\\n",
       "0           W 89 St & Columbus Ave             40.788221   \n",
       "1                  E 39 St & 2 Ave             40.747804   \n",
       "2                  E 77 St & 3 Ave             40.773142   \n",
       "3                  W 15 St & 6 Ave             40.738046   \n",
       "4               E 20 St & Park Ave             40.738274   \n",
       "...                            ...                   ...   \n",
       "20551692           1 Ave & E 62 St             40.761227   \n",
       "20551693          W 56 St & 10 Ave             40.768254   \n",
       "20551694              5 Ave & 3 St             40.672816   \n",
       "20551695  Clinton Ave & Myrtle Ave             40.693261   \n",
       "20551696  E 103 St & Lexington Ave             40.790305   \n",
       "\n",
       "          end station longitude  bikeid    usertype  gender  \n",
       "0                    -73.970416   15839  Subscriber       1  \n",
       "1                    -73.973442   32723  Subscriber       1  \n",
       "2                    -73.958562   27451  Subscriber       1  \n",
       "3                    -73.996430   21579  Subscriber       1  \n",
       "4                    -73.987520   35379  Subscriber       1  \n",
       "...                         ...     ...         ...     ...  \n",
       "20551692             -73.960940   28734  Subscriber       2  \n",
       "20551693             -73.988639   41418  Subscriber       1  \n",
       "20551694             -73.983524   27746  Subscriber       2  \n",
       "20551695             -73.968896   28506  Subscriber       1  \n",
       "20551696             -73.947558   21013  Subscriber       0  \n",
       "\n",
       "[20551697 rows x 14 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Table is too big for Tableau public so deleting a column I dont need\n",
    "df = df.drop(columns=['birth year', 'Unnamed: 0'])\n",
    "df"
   ]
  },
  {
   "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>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>320</td>\n",
       "      <td>2019-01-01 00:01:47.4010</td>\n",
       "      <td>2019-01-01 00:07:07.5810</td>\n",
       "      <td>3160.0</td>\n",
       "      <td>Central Park West &amp; W 76 St</td>\n",
       "      <td>40.778968</td>\n",
       "      <td>-73.973747</td>\n",
       "      <td>3283.0</td>\n",
       "      <td>W 89 St &amp; Columbus Ave</td>\n",
       "      <td>40.788221</td>\n",
       "      <td>-73.970416</td>\n",
       "      <td>15839</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>316</td>\n",
       "      <td>2019-01-01 00:04:43.7360</td>\n",
       "      <td>2019-01-01 00:10:00.6080</td>\n",
       "      <td>519.0</td>\n",
       "      <td>Pershing Square North</td>\n",
       "      <td>40.751873</td>\n",
       "      <td>-73.977706</td>\n",
       "      <td>518.0</td>\n",
       "      <td>E 39 St &amp; 2 Ave</td>\n",
       "      <td>40.747804</td>\n",
       "      <td>-73.973442</td>\n",
       "      <td>32723</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>591</td>\n",
       "      <td>2019-01-01 00:06:03.9970</td>\n",
       "      <td>2019-01-01 00:15:55.4380</td>\n",
       "      <td>3171.0</td>\n",
       "      <td>Amsterdam Ave &amp; W 82 St</td>\n",
       "      <td>40.785247</td>\n",
       "      <td>-73.976673</td>\n",
       "      <td>3154.0</td>\n",
       "      <td>E 77 St &amp; 3 Ave</td>\n",
       "      <td>40.773142</td>\n",
       "      <td>-73.958562</td>\n",
       "      <td>27451</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2719</td>\n",
       "      <td>2019-01-01 00:07:03.5450</td>\n",
       "      <td>2019-01-01 00:52:22.6500</td>\n",
       "      <td>504.0</td>\n",
       "      <td>1 Ave &amp; E 16 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>3709.0</td>\n",
       "      <td>W 15 St &amp; 6 Ave</td>\n",
       "      <td>40.738046</td>\n",
       "      <td>-73.996430</td>\n",
       "      <td>21579</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>303</td>\n",
       "      <td>2019-01-01 00:07:35.9450</td>\n",
       "      <td>2019-01-01 00:12:39.5020</td>\n",
       "      <td>229.0</td>\n",
       "      <td>Great Jones St</td>\n",
       "      <td>40.727434</td>\n",
       "      <td>-73.993790</td>\n",
       "      <td>503.0</td>\n",
       "      <td>E 20 St &amp; Park Ave</td>\n",
       "      <td>40.738274</td>\n",
       "      <td>-73.987520</td>\n",
       "      <td>35379</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551691</th>\n",
       "      <td>1838</td>\n",
       "      <td>2019-12-31 23:57:04.6040</td>\n",
       "      <td>2020-01-01 00:27:43.3920</td>\n",
       "      <td>3711.0</td>\n",
       "      <td>E 13 St &amp; Avenue A</td>\n",
       "      <td>40.729667</td>\n",
       "      <td>-73.980680</td>\n",
       "      <td>515.0</td>\n",
       "      <td>W 43 St &amp; 10 Ave</td>\n",
       "      <td>40.760094</td>\n",
       "      <td>-73.994618</td>\n",
       "      <td>41688</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551692</th>\n",
       "      <td>93</td>\n",
       "      <td>2019-12-31 23:58:18.0160</td>\n",
       "      <td>2019-12-31 23:59:51.6360</td>\n",
       "      <td>3141.0</td>\n",
       "      <td>1 Ave &amp; E 68 St</td>\n",
       "      <td>40.765005</td>\n",
       "      <td>-73.958185</td>\n",
       "      <td>3142.0</td>\n",
       "      <td>1 Ave &amp; E 62 St</td>\n",
       "      <td>40.761227</td>\n",
       "      <td>-73.960940</td>\n",
       "      <td>28734</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551693</th>\n",
       "      <td>786</td>\n",
       "      <td>2019-12-31 23:58:59.8960</td>\n",
       "      <td>2020-01-01 00:12:06.3460</td>\n",
       "      <td>490.0</td>\n",
       "      <td>8 Ave &amp; W 33 St</td>\n",
       "      <td>40.751551</td>\n",
       "      <td>-73.993934</td>\n",
       "      <td>513.0</td>\n",
       "      <td>W 56 St &amp; 10 Ave</td>\n",
       "      <td>40.768254</td>\n",
       "      <td>-73.988639</td>\n",
       "      <td>41418</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551694</th>\n",
       "      <td>351</td>\n",
       "      <td>2019-12-31 23:59:03.6950</td>\n",
       "      <td>2020-01-01 00:04:54.8730</td>\n",
       "      <td>3349.0</td>\n",
       "      <td>Grand Army Plaza &amp; Plaza St West</td>\n",
       "      <td>40.672968</td>\n",
       "      <td>-73.970880</td>\n",
       "      <td>3368.0</td>\n",
       "      <td>5 Ave &amp; 3 St</td>\n",
       "      <td>40.672816</td>\n",
       "      <td>-73.983524</td>\n",
       "      <td>27746</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551695</th>\n",
       "      <td>1571</td>\n",
       "      <td>2019-12-31 23:59:21.3610</td>\n",
       "      <td>2020-01-01 00:25:32.9420</td>\n",
       "      <td>252.0</td>\n",
       "      <td>MacDougal St &amp; Washington Sq</td>\n",
       "      <td>40.732264</td>\n",
       "      <td>-73.998522</td>\n",
       "      <td>366.0</td>\n",
       "      <td>Clinton Ave &amp; Myrtle Ave</td>\n",
       "      <td>40.693261</td>\n",
       "      <td>-73.968896</td>\n",
       "      <td>28506</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18991389 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          tripduration                 starttime                  stoptime  \\\n",
       "0                  320  2019-01-01 00:01:47.4010  2019-01-01 00:07:07.5810   \n",
       "1                  316  2019-01-01 00:04:43.7360  2019-01-01 00:10:00.6080   \n",
       "2                  591  2019-01-01 00:06:03.9970  2019-01-01 00:15:55.4380   \n",
       "3                 2719  2019-01-01 00:07:03.5450  2019-01-01 00:52:22.6500   \n",
       "4                  303  2019-01-01 00:07:35.9450  2019-01-01 00:12:39.5020   \n",
       "...                ...                       ...                       ...   \n",
       "20551691          1838  2019-12-31 23:57:04.6040  2020-01-01 00:27:43.3920   \n",
       "20551692            93  2019-12-31 23:58:18.0160  2019-12-31 23:59:51.6360   \n",
       "20551693           786  2019-12-31 23:58:59.8960  2020-01-01 00:12:06.3460   \n",
       "20551694           351  2019-12-31 23:59:03.6950  2020-01-01 00:04:54.8730   \n",
       "20551695          1571  2019-12-31 23:59:21.3610  2020-01-01 00:25:32.9420   \n",
       "\n",
       "          start station id                start station name  \\\n",
       "0                   3160.0       Central Park West & W 76 St   \n",
       "1                    519.0             Pershing Square North   \n",
       "2                   3171.0           Amsterdam Ave & W 82 St   \n",
       "3                    504.0                   1 Ave & E 16 St   \n",
       "4                    229.0                    Great Jones St   \n",
       "...                    ...                               ...   \n",
       "20551691            3711.0                E 13 St & Avenue A   \n",
       "20551692            3141.0                   1 Ave & E 68 St   \n",
       "20551693             490.0                   8 Ave & W 33 St   \n",
       "20551694            3349.0  Grand Army Plaza & Plaza St West   \n",
       "20551695             252.0      MacDougal St & Washington Sq   \n",
       "\n",
       "          start station latitude  start station longitude  end station id  \\\n",
       "0                      40.778968               -73.973747          3283.0   \n",
       "1                      40.751873               -73.977706           518.0   \n",
       "2                      40.785247               -73.976673          3154.0   \n",
       "3                      40.732219               -73.981656          3709.0   \n",
       "4                      40.727434               -73.993790           503.0   \n",
       "...                          ...                      ...             ...   \n",
       "20551691               40.729667               -73.980680           515.0   \n",
       "20551692               40.765005               -73.958185          3142.0   \n",
       "20551693               40.751551               -73.993934           513.0   \n",
       "20551694               40.672968               -73.970880          3368.0   \n",
       "20551695               40.732264               -73.998522           366.0   \n",
       "\n",
       "                  end station name  end station latitude  \\\n",
       "0           W 89 St & Columbus Ave             40.788221   \n",
       "1                  E 39 St & 2 Ave             40.747804   \n",
       "2                  E 77 St & 3 Ave             40.773142   \n",
       "3                  W 15 St & 6 Ave             40.738046   \n",
       "4               E 20 St & Park Ave             40.738274   \n",
       "...                            ...                   ...   \n",
       "20551691          W 43 St & 10 Ave             40.760094   \n",
       "20551692           1 Ave & E 62 St             40.761227   \n",
       "20551693          W 56 St & 10 Ave             40.768254   \n",
       "20551694              5 Ave & 3 St             40.672816   \n",
       "20551695  Clinton Ave & Myrtle Ave             40.693261   \n",
       "\n",
       "          end station longitude  bikeid    usertype  gender  \n",
       "0                    -73.970416   15839  Subscriber       1  \n",
       "1                    -73.973442   32723  Subscriber       1  \n",
       "2                    -73.958562   27451  Subscriber       1  \n",
       "3                    -73.996430   21579  Subscriber       1  \n",
       "4                    -73.987520   35379  Subscriber       1  \n",
       "...                         ...     ...         ...     ...  \n",
       "20551691             -73.994618   41688  Subscriber       1  \n",
       "20551692             -73.960940   28734  Subscriber       2  \n",
       "20551693             -73.988639   41418  Subscriber       1  \n",
       "20551694             -73.983524   27746  Subscriber       2  \n",
       "20551695             -73.968896   28506  Subscriber       1  \n",
       "\n",
       "[18991389 rows x 14 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop rows with no gender data to decrease dataset size\n",
    "index_names = df[(df['gender']< 1)].index \n",
    "df.drop(index_names, inplace = True) \n",
    "df \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>591</td>\n",
       "      <td>2019-01-01 00:06:03.9970</td>\n",
       "      <td>2019-01-01 00:15:55.4380</td>\n",
       "      <td>3171.0</td>\n",
       "      <td>Amsterdam Ave &amp; W 82 St</td>\n",
       "      <td>40.785247</td>\n",
       "      <td>-73.976673</td>\n",
       "      <td>3154.0</td>\n",
       "      <td>E 77 St &amp; 3 Ave</td>\n",
       "      <td>40.773142</td>\n",
       "      <td>-73.958562</td>\n",
       "      <td>27451</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2719</td>\n",
       "      <td>2019-01-01 00:07:03.5450</td>\n",
       "      <td>2019-01-01 00:52:22.6500</td>\n",
       "      <td>504.0</td>\n",
       "      <td>1 Ave &amp; E 16 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>3709.0</td>\n",
       "      <td>W 15 St &amp; 6 Ave</td>\n",
       "      <td>40.738046</td>\n",
       "      <td>-73.996430</td>\n",
       "      <td>21579</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>535</td>\n",
       "      <td>2019-01-01 00:08:33.1790</td>\n",
       "      <td>2019-01-01 00:17:28.3540</td>\n",
       "      <td>3630.0</td>\n",
       "      <td>Frederick Douglass Blvd &amp; W 115 St</td>\n",
       "      <td>40.803865</td>\n",
       "      <td>-73.955931</td>\n",
       "      <td>3529.0</td>\n",
       "      <td>Lenox Ave &amp; W 130 St</td>\n",
       "      <td>40.810792</td>\n",
       "      <td>-73.943068</td>\n",
       "      <td>30315</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1547</td>\n",
       "      <td>2019-01-01 00:11:22.9070</td>\n",
       "      <td>2019-01-01 00:37:10.0550</td>\n",
       "      <td>285.0</td>\n",
       "      <td>Broadway &amp; E 14 St</td>\n",
       "      <td>40.734546</td>\n",
       "      <td>-73.990741</td>\n",
       "      <td>3358.0</td>\n",
       "      <td>Garfield Pl &amp; 8 Ave</td>\n",
       "      <td>40.671198</td>\n",
       "      <td>-73.974841</td>\n",
       "      <td>35551</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>932</td>\n",
       "      <td>2019-01-01 00:14:02.7970</td>\n",
       "      <td>2019-01-01 00:29:35.0450</td>\n",
       "      <td>379.0</td>\n",
       "      <td>W 31 St &amp; 7 Ave</td>\n",
       "      <td>40.749156</td>\n",
       "      <td>-73.991600</td>\n",
       "      <td>336.0</td>\n",
       "      <td>Sullivan St &amp; Washington Sq</td>\n",
       "      <td>40.730477</td>\n",
       "      <td>-73.999061</td>\n",
       "      <td>35148</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551689</th>\n",
       "      <td>1775</td>\n",
       "      <td>2019-12-31 23:55:21.1960</td>\n",
       "      <td>2020-01-01 00:24:56.6520</td>\n",
       "      <td>3306.0</td>\n",
       "      <td>10 St &amp; 7 Ave</td>\n",
       "      <td>40.666208</td>\n",
       "      <td>-73.981999</td>\n",
       "      <td>3416.0</td>\n",
       "      <td>7 Ave &amp; Park Pl</td>\n",
       "      <td>40.677615</td>\n",
       "      <td>-73.973243</td>\n",
       "      <td>35047</td>\n",
       "      <td>Customer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551690</th>\n",
       "      <td>122099</td>\n",
       "      <td>2019-12-31 23:55:30.7260</td>\n",
       "      <td>2020-01-02 09:50:30.6710</td>\n",
       "      <td>3712.0</td>\n",
       "      <td>W 35 St &amp; Dyer Ave</td>\n",
       "      <td>40.754692</td>\n",
       "      <td>-73.997402</td>\n",
       "      <td>347.0</td>\n",
       "      <td>Greenwich St &amp; W Houston St</td>\n",
       "      <td>40.728846</td>\n",
       "      <td>-74.008591</td>\n",
       "      <td>38866</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551691</th>\n",
       "      <td>1838</td>\n",
       "      <td>2019-12-31 23:57:04.6040</td>\n",
       "      <td>2020-01-01 00:27:43.3920</td>\n",
       "      <td>3711.0</td>\n",
       "      <td>E 13 St &amp; Avenue A</td>\n",
       "      <td>40.729667</td>\n",
       "      <td>-73.980680</td>\n",
       "      <td>515.0</td>\n",
       "      <td>W 43 St &amp; 10 Ave</td>\n",
       "      <td>40.760094</td>\n",
       "      <td>-73.994618</td>\n",
       "      <td>41688</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551693</th>\n",
       "      <td>786</td>\n",
       "      <td>2019-12-31 23:58:59.8960</td>\n",
       "      <td>2020-01-01 00:12:06.3460</td>\n",
       "      <td>490.0</td>\n",
       "      <td>8 Ave &amp; W 33 St</td>\n",
       "      <td>40.751551</td>\n",
       "      <td>-73.993934</td>\n",
       "      <td>513.0</td>\n",
       "      <td>W 56 St &amp; 10 Ave</td>\n",
       "      <td>40.768254</td>\n",
       "      <td>-73.988639</td>\n",
       "      <td>41418</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20551695</th>\n",
       "      <td>1571</td>\n",
       "      <td>2019-12-31 23:59:21.3610</td>\n",
       "      <td>2020-01-01 00:25:32.9420</td>\n",
       "      <td>252.0</td>\n",
       "      <td>MacDougal St &amp; Washington Sq</td>\n",
       "      <td>40.732264</td>\n",
       "      <td>-73.998522</td>\n",
       "      <td>366.0</td>\n",
       "      <td>Clinton Ave &amp; Myrtle Ave</td>\n",
       "      <td>40.693261</td>\n",
       "      <td>-73.968896</td>\n",
       "      <td>28506</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14070248 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          tripduration                 starttime                  stoptime  \\\n",
       "2                  591  2019-01-01 00:06:03.9970  2019-01-01 00:15:55.4380   \n",
       "3                 2719  2019-01-01 00:07:03.5450  2019-01-01 00:52:22.6500   \n",
       "5                  535  2019-01-01 00:08:33.1790  2019-01-01 00:17:28.3540   \n",
       "7                 1547  2019-01-01 00:11:22.9070  2019-01-01 00:37:10.0550   \n",
       "10                 932  2019-01-01 00:14:02.7970  2019-01-01 00:29:35.0450   \n",
       "...                ...                       ...                       ...   \n",
       "20551689          1775  2019-12-31 23:55:21.1960  2020-01-01 00:24:56.6520   \n",
       "20551690        122099  2019-12-31 23:55:30.7260  2020-01-02 09:50:30.6710   \n",
       "20551691          1838  2019-12-31 23:57:04.6040  2020-01-01 00:27:43.3920   \n",
       "20551693           786  2019-12-31 23:58:59.8960  2020-01-01 00:12:06.3460   \n",
       "20551695          1571  2019-12-31 23:59:21.3610  2020-01-01 00:25:32.9420   \n",
       "\n",
       "          start station id                  start station name  \\\n",
       "2                   3171.0             Amsterdam Ave & W 82 St   \n",
       "3                    504.0                     1 Ave & E 16 St   \n",
       "5                   3630.0  Frederick Douglass Blvd & W 115 St   \n",
       "7                    285.0                  Broadway & E 14 St   \n",
       "10                   379.0                     W 31 St & 7 Ave   \n",
       "...                    ...                                 ...   \n",
       "20551689            3306.0                       10 St & 7 Ave   \n",
       "20551690            3712.0                  W 35 St & Dyer Ave   \n",
       "20551691            3711.0                  E 13 St & Avenue A   \n",
       "20551693             490.0                     8 Ave & W 33 St   \n",
       "20551695             252.0        MacDougal St & Washington Sq   \n",
       "\n",
       "          start station latitude  start station longitude  end station id  \\\n",
       "2                      40.785247               -73.976673          3154.0   \n",
       "3                      40.732219               -73.981656          3709.0   \n",
       "5                      40.803865               -73.955931          3529.0   \n",
       "7                      40.734546               -73.990741          3358.0   \n",
       "10                     40.749156               -73.991600           336.0   \n",
       "...                          ...                      ...             ...   \n",
       "20551689               40.666208               -73.981999          3416.0   \n",
       "20551690               40.754692               -73.997402           347.0   \n",
       "20551691               40.729667               -73.980680           515.0   \n",
       "20551693               40.751551               -73.993934           513.0   \n",
       "20551695               40.732264               -73.998522           366.0   \n",
       "\n",
       "                     end station name  end station latitude  \\\n",
       "2                     E 77 St & 3 Ave             40.773142   \n",
       "3                     W 15 St & 6 Ave             40.738046   \n",
       "5                Lenox Ave & W 130 St             40.810792   \n",
       "7                 Garfield Pl & 8 Ave             40.671198   \n",
       "10        Sullivan St & Washington Sq             40.730477   \n",
       "...                               ...                   ...   \n",
       "20551689              7 Ave & Park Pl             40.677615   \n",
       "20551690  Greenwich St & W Houston St             40.728846   \n",
       "20551691             W 43 St & 10 Ave             40.760094   \n",
       "20551693             W 56 St & 10 Ave             40.768254   \n",
       "20551695     Clinton Ave & Myrtle Ave             40.693261   \n",
       "\n",
       "          end station longitude  bikeid    usertype  gender  \n",
       "2                    -73.958562   27451  Subscriber       1  \n",
       "3                    -73.996430   21579  Subscriber       1  \n",
       "5                    -73.943068   30315  Subscriber       2  \n",
       "7                    -73.974841   35551  Subscriber       1  \n",
       "10                   -73.999061   35148  Subscriber       1  \n",
       "...                         ...     ...         ...     ...  \n",
       "20551689             -73.973243   35047    Customer       2  \n",
       "20551690             -74.008591   38866    Customer       1  \n",
       "20551691             -73.994618   41688  Subscriber       1  \n",
       "20551693             -73.988639   41418  Subscriber       1  \n",
       "20551695             -73.968896   28506  Subscriber       1  \n",
       "\n",
       "[14070248 rows x 14 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop rows with low trip duration <6 minutes to decrease dataset size\n",
    "index_names = df[(df['tripduration']< 360)].index \n",
    "df.drop(index_names, inplace = True) \n",
    "df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export file to csv\n",
    "df.to_csv(\"2019-citibikedata_combined.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}