cleaned.merged.dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7a3123b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e180c900",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of file names to be combined\n",
    "\n",
    "d1 = pd.read_csv(\"Resources/201307-citibike-tripdata.csv\")\n",
    "d2 = pd.read_csv(\"Resources/201308-citibike-tripdata.csv\")\n",
    "d3 = pd.read_csv(\"Resources/201309-citibike-tripdata.csv\")\n",
    "d4 = pd.read_csv(\"Resources/201310-citibike-tripdata.csv\")\n",
    "d5 = pd.read_csv(\"Resources/201311-citibike-tripdata.csv\")\n",
    "d6 = pd.read_csv(\"Resources/201312-citibike-tripdata.csv\")\n",
    "d7 = pd.read_csv(\"Resources/201401-citibike-tripdata.csv\")\n",
    "d8 = pd.read_csv(\"Resources/201402-citibike-tripdata.csv\")\n",
    "d9 = pd.read_csv(\"Resources/201306-citibike-tripdata.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3920e6ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tripduration               6140024\n",
       "starttime                  6140024\n",
       "stoptime                   6140024\n",
       "start station id           6140024\n",
       "start station name         6140024\n",
       "start station latitude     6140024\n",
       "start station longitude    6140024\n",
       "end station id             6121965\n",
       "end station name           6121965\n",
       "end station latitude       6121965\n",
       "end station longitude      6121965\n",
       "bikeid                     6140024\n",
       "usertype                   6140024\n",
       "birth year                 5899703\n",
       "gender                     6140024\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenate all data to one dataframe\n",
    "\n",
    "combined_df = pd.concat([d1,d2,d3,d4,d5,d6,d7,d8,d9])\n",
    "combined_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1d0d7438",
   "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",
       "      <th>0</th>\n",
       "      <td>634</td>\n",
       "      <td>2013-07-01 00:00:00</td>\n",
       "      <td>2013-07-01 00:10:34</td>\n",
       "      <td>164</td>\n",
       "      <td>E 47 St &amp; 2 Ave</td>\n",
       "      <td>40.753231</td>\n",
       "      <td>-73.970325</td>\n",
       "      <td>504.0</td>\n",
       "      <td>1 Ave &amp; E 15 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>16950</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1547</td>\n",
       "      <td>2013-07-01 00:00:02</td>\n",
       "      <td>2013-07-01 00:25:49</td>\n",
       "      <td>388</td>\n",
       "      <td>W 26 St &amp; 10 Ave</td>\n",
       "      <td>40.749718</td>\n",
       "      <td>-74.002950</td>\n",
       "      <td>459.0</td>\n",
       "      <td>W 20 St &amp; 11 Ave</td>\n",
       "      <td>40.746745</td>\n",
       "      <td>-74.007756</td>\n",
       "      <td>19816</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>178</td>\n",
       "      <td>2013-07-01 00:01:04</td>\n",
       "      <td>2013-07-01 00:04:02</td>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>40.730287</td>\n",
       "      <td>-73.990765</td>\n",
       "      <td>237.0</td>\n",
       "      <td>E 11 St &amp; 2 Ave</td>\n",
       "      <td>40.730473</td>\n",
       "      <td>-73.986724</td>\n",
       "      <td>14548</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1580</td>\n",
       "      <td>2013-07-01 00:01:06</td>\n",
       "      <td>2013-07-01 00:27:26</td>\n",
       "      <td>531</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>499.0</td>\n",
       "      <td>Broadway &amp; W 60 St</td>\n",
       "      <td>40.769155</td>\n",
       "      <td>-73.981918</td>\n",
       "      <td>16063</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>757</td>\n",
       "      <td>2013-07-01 00:01:10</td>\n",
       "      <td>2013-07-01 00:13:47</td>\n",
       "      <td>382</td>\n",
       "      <td>University Pl &amp; E 14 St</td>\n",
       "      <td>40.734927</td>\n",
       "      <td>-73.992005</td>\n",
       "      <td>410.0</td>\n",
       "      <td>Suffolk St &amp; Stanton St</td>\n",
       "      <td>40.720664</td>\n",
       "      <td>-73.985180</td>\n",
       "      <td>19213</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1986</td>\n",
       "      <td>Male</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577698</th>\n",
       "      <td>925</td>\n",
       "      <td>2013-06-30 23:59:27</td>\n",
       "      <td>2013-07-01 00:14:52</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>462.0</td>\n",
       "      <td>W 22 St &amp; 10 Ave</td>\n",
       "      <td>40.746920</td>\n",
       "      <td>-74.004519</td>\n",
       "      <td>15250</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577699</th>\n",
       "      <td>279</td>\n",
       "      <td>2013-06-30 23:59:36</td>\n",
       "      <td>2013-07-01 00:04:15</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>494.0</td>\n",
       "      <td>W 26 St &amp; 8 Ave</td>\n",
       "      <td>40.747348</td>\n",
       "      <td>-73.997236</td>\n",
       "      <td>19411</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577700</th>\n",
       "      <td>161</td>\n",
       "      <td>2013-06-30 23:59:33</td>\n",
       "      <td>2013-07-01 00:02:14</td>\n",
       "      <td>443</td>\n",
       "      <td>Bedford Ave &amp; S 9 St</td>\n",
       "      <td>40.708531</td>\n",
       "      <td>-73.964090</td>\n",
       "      <td>282.0</td>\n",
       "      <td>Kent Ave &amp; S 11 St</td>\n",
       "      <td>40.707645</td>\n",
       "      <td>-73.968415</td>\n",
       "      <td>18910</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1972.0</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577701</th>\n",
       "      <td>909</td>\n",
       "      <td>2013-06-30 23:59:47</td>\n",
       "      <td>2013-07-01 00:14:56</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>462.0</td>\n",
       "      <td>W 22 St &amp; 10 Ave</td>\n",
       "      <td>40.746920</td>\n",
       "      <td>-74.004519</td>\n",
       "      <td>16108</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577702</th>\n",
       "      <td>634</td>\n",
       "      <td>2013-07-01 00:00:00</td>\n",
       "      <td>2013-07-01 00:10:34</td>\n",
       "      <td>164</td>\n",
       "      <td>E 47 St &amp; 2 Ave</td>\n",
       "      <td>40.753231</td>\n",
       "      <td>-73.970325</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>16950</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6140024 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        tripduration            starttime             stoptime  \\\n",
       "0                634  2013-07-01 00:00:00  2013-07-01 00:10:34   \n",
       "1               1547  2013-07-01 00:00:02  2013-07-01 00:25:49   \n",
       "2                178  2013-07-01 00:01:04  2013-07-01 00:04:02   \n",
       "3               1580  2013-07-01 00:01:06  2013-07-01 00:27:26   \n",
       "4                757  2013-07-01 00:01:10  2013-07-01 00:13:47   \n",
       "...              ...                  ...                  ...   \n",
       "577698           925  2013-06-30 23:59:27  2013-07-01 00:14:52   \n",
       "577699           279  2013-06-30 23:59:36  2013-07-01 00:04:15   \n",
       "577700           161  2013-06-30 23:59:33  2013-07-01 00:02:14   \n",
       "577701           909  2013-06-30 23:59:47  2013-07-01 00:14:56   \n",
       "577702           634  2013-07-01 00:00:00  2013-07-01 00:10:34   \n",
       "\n",
       "        start station id       start station name  start station latitude  \\\n",
       "0                    164          E 47 St & 2 Ave               40.753231   \n",
       "1                    388         W 26 St & 10 Ave               40.749718   \n",
       "2                    293    Lafayette St & E 8 St               40.730287   \n",
       "3                    531   Forsyth St & Broome St               40.718939   \n",
       "4                    382  University Pl & E 14 St               40.734927   \n",
       "...                  ...                      ...                     ...   \n",
       "577698               509          9 Ave & W 22 St               40.745497   \n",
       "577699               116          W 17 St & 8 Ave               40.741776   \n",
       "577700               443     Bedford Ave & S 9 St               40.708531   \n",
       "577701               509          9 Ave & W 22 St               40.745497   \n",
       "577702               164          E 47 St & 2 Ave               40.753231   \n",
       "\n",
       "        start station longitude  end station id         end station name  \\\n",
       "0                    -73.970325           504.0          1 Ave & E 15 St   \n",
       "1                    -74.002950           459.0         W 20 St & 11 Ave   \n",
       "2                    -73.990765           237.0          E 11 St & 2 Ave   \n",
       "3                    -73.992663           499.0       Broadway & W 60 St   \n",
       "4                    -73.992005           410.0  Suffolk St & Stanton St   \n",
       "...                         ...             ...                      ...   \n",
       "577698               -74.001971           462.0         W 22 St & 10 Ave   \n",
       "577699               -74.001497           494.0          W 26 St & 8 Ave   \n",
       "577700               -73.964090           282.0       Kent Ave & S 11 St   \n",
       "577701               -74.001971           462.0         W 22 St & 10 Ave   \n",
       "577702               -73.970325           504.0          1 Ave & E 16 St   \n",
       "\n",
       "        end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0                  40.732219             -73.981656   16950    Customer   \n",
       "1                  40.746745             -74.007756   19816    Customer   \n",
       "2                  40.730473             -73.986724   14548  Subscriber   \n",
       "3                  40.769155             -73.981918   16063    Customer   \n",
       "4                  40.720664             -73.985180   19213  Subscriber   \n",
       "...                      ...                    ...     ...         ...   \n",
       "577698             40.746920             -74.004519   15250  Subscriber   \n",
       "577699             40.747348             -73.997236   19411    Customer   \n",
       "577700             40.707645             -73.968415   18910  Subscriber   \n",
       "577701             40.746920             -74.004519   16108    Customer   \n",
       "577702             40.732219             -73.981656   16950    Customer   \n",
       "\n",
       "       birth year   gender  \n",
       "0              \\N  Unknown  \n",
       "1              \\N  Unknown  \n",
       "2            1980   Female  \n",
       "3              \\N  Unknown  \n",
       "4            1986     Male  \n",
       "...           ...      ...  \n",
       "577698     1964.0     Male  \n",
       "577699        NaN  Unknown  \n",
       "577700     1972.0   Female  \n",
       "577701        NaN  Unknown  \n",
       "577702        NaN  Unknown  \n",
       "\n",
       "[6140024 rows x 15 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dictionary mapping values in the 'gender' column to new values\n",
    "mapping = {0: 'Unknown', 1: 'Male', 2: 'Female'}\n",
    "\n",
    "# Use the 'map' method to change the values in the 'gender' column\n",
    "combined_df['gender'] = combined_df['gender'].map(mapping)\n",
    "\n",
    "# Show DataFrame\n",
    "combined_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8dc07962",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the 'date' column to a datetime object, handling errors\n",
    "combined_df['starttime'] = pd.to_datetime(combined_df['starttime'], errors='coerce')\n",
    "\n",
    "# Extract the year and create a new column\n",
    "combined_df['year'] = combined_df['starttime'].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8f8e910f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract the month and create a new column\n",
    "combined_df['month'] = combined_df['starttime'].dt.strftime('%B')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5ee02abb",
   "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",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>634</td>\n",
       "      <td>2013-07-01 00:00:00</td>\n",
       "      <td>2013-07-01 00:10:34</td>\n",
       "      <td>164</td>\n",
       "      <td>E 47 St &amp; 2 Ave</td>\n",
       "      <td>40.753231</td>\n",
       "      <td>-73.970325</td>\n",
       "      <td>504.0</td>\n",
       "      <td>1 Ave &amp; E 15 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>16950</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1547</td>\n",
       "      <td>2013-07-01 00:00:02</td>\n",
       "      <td>2013-07-01 00:25:49</td>\n",
       "      <td>388</td>\n",
       "      <td>W 26 St &amp; 10 Ave</td>\n",
       "      <td>40.749718</td>\n",
       "      <td>-74.002950</td>\n",
       "      <td>459.0</td>\n",
       "      <td>W 20 St &amp; 11 Ave</td>\n",
       "      <td>40.746745</td>\n",
       "      <td>-74.007756</td>\n",
       "      <td>19816</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>178</td>\n",
       "      <td>2013-07-01 00:01:04</td>\n",
       "      <td>2013-07-01 00:04:02</td>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>40.730287</td>\n",
       "      <td>-73.990765</td>\n",
       "      <td>237.0</td>\n",
       "      <td>E 11 St &amp; 2 Ave</td>\n",
       "      <td>40.730473</td>\n",
       "      <td>-73.986724</td>\n",
       "      <td>14548</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>Female</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1580</td>\n",
       "      <td>2013-07-01 00:01:06</td>\n",
       "      <td>2013-07-01 00:27:26</td>\n",
       "      <td>531</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>499.0</td>\n",
       "      <td>Broadway &amp; W 60 St</td>\n",
       "      <td>40.769155</td>\n",
       "      <td>-73.981918</td>\n",
       "      <td>16063</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>757</td>\n",
       "      <td>2013-07-01 00:01:10</td>\n",
       "      <td>2013-07-01 00:13:47</td>\n",
       "      <td>382</td>\n",
       "      <td>University Pl &amp; E 14 St</td>\n",
       "      <td>40.734927</td>\n",
       "      <td>-73.992005</td>\n",
       "      <td>410.0</td>\n",
       "      <td>Suffolk St &amp; Stanton St</td>\n",
       "      <td>40.720664</td>\n",
       "      <td>-73.985180</td>\n",
       "      <td>19213</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1986</td>\n",
       "      <td>Male</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577698</th>\n",
       "      <td>925</td>\n",
       "      <td>2013-06-30 23:59:27</td>\n",
       "      <td>2013-07-01 00:14:52</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>462.0</td>\n",
       "      <td>W 22 St &amp; 10 Ave</td>\n",
       "      <td>40.746920</td>\n",
       "      <td>-74.004519</td>\n",
       "      <td>15250</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>Male</td>\n",
       "      <td>2013</td>\n",
       "      <td>June</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577699</th>\n",
       "      <td>279</td>\n",
       "      <td>2013-06-30 23:59:36</td>\n",
       "      <td>2013-07-01 00:04:15</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>494.0</td>\n",
       "      <td>W 26 St &amp; 8 Ave</td>\n",
       "      <td>40.747348</td>\n",
       "      <td>-73.997236</td>\n",
       "      <td>19411</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>June</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577700</th>\n",
       "      <td>161</td>\n",
       "      <td>2013-06-30 23:59:33</td>\n",
       "      <td>2013-07-01 00:02:14</td>\n",
       "      <td>443</td>\n",
       "      <td>Bedford Ave &amp; S 9 St</td>\n",
       "      <td>40.708531</td>\n",
       "      <td>-73.964090</td>\n",
       "      <td>282.0</td>\n",
       "      <td>Kent Ave &amp; S 11 St</td>\n",
       "      <td>40.707645</td>\n",
       "      <td>-73.968415</td>\n",
       "      <td>18910</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1972.0</td>\n",
       "      <td>Female</td>\n",
       "      <td>2013</td>\n",
       "      <td>June</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577701</th>\n",
       "      <td>909</td>\n",
       "      <td>2013-06-30 23:59:47</td>\n",
       "      <td>2013-07-01 00:14:56</td>\n",
       "      <td>509</td>\n",
       "      <td>9 Ave &amp; W 22 St</td>\n",
       "      <td>40.745497</td>\n",
       "      <td>-74.001971</td>\n",
       "      <td>462.0</td>\n",
       "      <td>W 22 St &amp; 10 Ave</td>\n",
       "      <td>40.746920</td>\n",
       "      <td>-74.004519</td>\n",
       "      <td>16108</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>June</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577702</th>\n",
       "      <td>634</td>\n",
       "      <td>2013-07-01 00:00:00</td>\n",
       "      <td>2013-07-01 00:10:34</td>\n",
       "      <td>164</td>\n",
       "      <td>E 47 St &amp; 2 Ave</td>\n",
       "      <td>40.753231</td>\n",
       "      <td>-73.970325</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>16950</td>\n",
       "      <td>Customer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6140024 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        tripduration           starttime             stoptime  \\\n",
       "0                634 2013-07-01 00:00:00  2013-07-01 00:10:34   \n",
       "1               1547 2013-07-01 00:00:02  2013-07-01 00:25:49   \n",
       "2                178 2013-07-01 00:01:04  2013-07-01 00:04:02   \n",
       "3               1580 2013-07-01 00:01:06  2013-07-01 00:27:26   \n",
       "4                757 2013-07-01 00:01:10  2013-07-01 00:13:47   \n",
       "...              ...                 ...                  ...   \n",
       "577698           925 2013-06-30 23:59:27  2013-07-01 00:14:52   \n",
       "577699           279 2013-06-30 23:59:36  2013-07-01 00:04:15   \n",
       "577700           161 2013-06-30 23:59:33  2013-07-01 00:02:14   \n",
       "577701           909 2013-06-30 23:59:47  2013-07-01 00:14:56   \n",
       "577702           634 2013-07-01 00:00:00  2013-07-01 00:10:34   \n",
       "\n",
       "        start station id       start station name  start station latitude  \\\n",
       "0                    164          E 47 St & 2 Ave               40.753231   \n",
       "1                    388         W 26 St & 10 Ave               40.749718   \n",
       "2                    293    Lafayette St & E 8 St               40.730287   \n",
       "3                    531   Forsyth St & Broome St               40.718939   \n",
       "4                    382  University Pl & E 14 St               40.734927   \n",
       "...                  ...                      ...                     ...   \n",
       "577698               509          9 Ave & W 22 St               40.745497   \n",
       "577699               116          W 17 St & 8 Ave               40.741776   \n",
       "577700               443     Bedford Ave & S 9 St               40.708531   \n",
       "577701               509          9 Ave & W 22 St               40.745497   \n",
       "577702               164          E 47 St & 2 Ave               40.753231   \n",
       "\n",
       "        start station longitude  end station id         end station name  \\\n",
       "0                    -73.970325           504.0          1 Ave & E 15 St   \n",
       "1                    -74.002950           459.0         W 20 St & 11 Ave   \n",
       "2                    -73.990765           237.0          E 11 St & 2 Ave   \n",
       "3                    -73.992663           499.0       Broadway & W 60 St   \n",
       "4                    -73.992005           410.0  Suffolk St & Stanton St   \n",
       "...                         ...             ...                      ...   \n",
       "577698               -74.001971           462.0         W 22 St & 10 Ave   \n",
       "577699               -74.001497           494.0          W 26 St & 8 Ave   \n",
       "577700               -73.964090           282.0       Kent Ave & S 11 St   \n",
       "577701               -74.001971           462.0         W 22 St & 10 Ave   \n",
       "577702               -73.970325           504.0          1 Ave & E 16 St   \n",
       "\n",
       "        end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0                  40.732219             -73.981656   16950    Customer   \n",
       "1                  40.746745             -74.007756   19816    Customer   \n",
       "2                  40.730473             -73.986724   14548  Subscriber   \n",
       "3                  40.769155             -73.981918   16063    Customer   \n",
       "4                  40.720664             -73.985180   19213  Subscriber   \n",
       "...                      ...                    ...     ...         ...   \n",
       "577698             40.746920             -74.004519   15250  Subscriber   \n",
       "577699             40.747348             -73.997236   19411    Customer   \n",
       "577700             40.707645             -73.968415   18910  Subscriber   \n",
       "577701             40.746920             -74.004519   16108    Customer   \n",
       "577702             40.732219             -73.981656   16950    Customer   \n",
       "\n",
       "       birth year   gender  year month  \n",
       "0              \\N  Unknown  2013  July  \n",
       "1              \\N  Unknown  2013  July  \n",
       "2            1980   Female  2013  July  \n",
       "3              \\N  Unknown  2013  July  \n",
       "4            1986     Male  2013  July  \n",
       "...           ...      ...   ...   ...  \n",
       "577698     1964.0     Male  2013  June  \n",
       "577699        NaN  Unknown  2013  June  \n",
       "577700     1972.0   Female  2013  June  \n",
       "577701        NaN  Unknown  2013  June  \n",
       "577702        NaN  Unknown  2013  July  \n",
       "\n",
       "[6140024 rows x 17 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show dataframe\n",
    "combined_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0011257f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the dataframe to a CSV file\n",
    "\n",
    "combined_df.to_csv('Resources/citibike_tripdata.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2cbd806a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Asabe Chioma\\AppData\\Local\\Temp\\ipykernel_4260\\2078476386.py:1: DtypeWarning: Columns (13) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  check = pd.read_csv('Resources/citibike_tripdata.csv')\n"
     ]
    },
    {
     "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",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>634</td>\n",
       "      <td>2013-07-01 00:00:00</td>\n",
       "      <td>2013-07-01 00:10:34</td>\n",
       "      <td>164</td>\n",
       "      <td>E 47 St &amp; 2 Ave</td>\n",
       "      <td>40.753231</td>\n",
       "      <td>-73.970325</td>\n",
       "      <td>504.0</td>\n",
       "      <td>1 Ave &amp; E 15 St</td>\n",
       "      <td>40.732219</td>\n",
       "      <td>-73.981656</td>\n",
       "      <td>16950</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1547</td>\n",
       "      <td>2013-07-01 00:00:02</td>\n",
       "      <td>2013-07-01 00:25:49</td>\n",
       "      <td>388</td>\n",
       "      <td>W 26 St &amp; 10 Ave</td>\n",
       "      <td>40.749718</td>\n",
       "      <td>-74.002950</td>\n",
       "      <td>459.0</td>\n",
       "      <td>W 20 St &amp; 11 Ave</td>\n",
       "      <td>40.746745</td>\n",
       "      <td>-74.007756</td>\n",
       "      <td>19816</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>178</td>\n",
       "      <td>2013-07-01 00:01:04</td>\n",
       "      <td>2013-07-01 00:04:02</td>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>40.730287</td>\n",
       "      <td>-73.990765</td>\n",
       "      <td>237.0</td>\n",
       "      <td>E 11 St &amp; 2 Ave</td>\n",
       "      <td>40.730473</td>\n",
       "      <td>-73.986724</td>\n",
       "      <td>14548</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>Female</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1580</td>\n",
       "      <td>2013-07-01 00:01:06</td>\n",
       "      <td>2013-07-01 00:27:26</td>\n",
       "      <td>531</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>499.0</td>\n",
       "      <td>Broadway &amp; W 60 St</td>\n",
       "      <td>40.769155</td>\n",
       "      <td>-73.981918</td>\n",
       "      <td>16063</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>757</td>\n",
       "      <td>2013-07-01 00:01:10</td>\n",
       "      <td>2013-07-01 00:13:47</td>\n",
       "      <td>382</td>\n",
       "      <td>University Pl &amp; E 14 St</td>\n",
       "      <td>40.734927</td>\n",
       "      <td>-73.992005</td>\n",
       "      <td>410.0</td>\n",
       "      <td>Suffolk St &amp; Stanton St</td>\n",
       "      <td>40.720664</td>\n",
       "      <td>-73.985180</td>\n",
       "      <td>19213</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1986</td>\n",
       "      <td>Male</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>861</td>\n",
       "      <td>2013-07-01 00:01:23</td>\n",
       "      <td>2013-07-01 00:15:44</td>\n",
       "      <td>511</td>\n",
       "      <td>E 14 St &amp; Avenue B</td>\n",
       "      <td>40.729387</td>\n",
       "      <td>-73.977724</td>\n",
       "      <td>454.0</td>\n",
       "      <td>E 51 St &amp; 1 Ave</td>\n",
       "      <td>40.754557</td>\n",
       "      <td>-73.965930</td>\n",
       "      <td>16223</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>Male</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>550</td>\n",
       "      <td>2013-07-01 00:01:59</td>\n",
       "      <td>2013-07-01 00:11:09</td>\n",
       "      <td>293</td>\n",
       "      <td>Lafayette St &amp; E 8 St</td>\n",
       "      <td>40.730287</td>\n",
       "      <td>-73.990765</td>\n",
       "      <td>394.0</td>\n",
       "      <td>E 9 St &amp; Avenue C</td>\n",
       "      <td>40.725213</td>\n",
       "      <td>-73.977688</td>\n",
       "      <td>16746</td>\n",
       "      <td>Customer</td>\n",
       "      <td>\\N</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>288</td>\n",
       "      <td>2013-07-01 00:02:16</td>\n",
       "      <td>2013-07-01 00:07:04</td>\n",
       "      <td>224</td>\n",
       "      <td>Spruce St &amp; Nassau St</td>\n",
       "      <td>40.711464</td>\n",
       "      <td>-74.005524</td>\n",
       "      <td>376.0</td>\n",
       "      <td>John St &amp; William St</td>\n",
       "      <td>40.708621</td>\n",
       "      <td>-74.007222</td>\n",
       "      <td>16062</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1985</td>\n",
       "      <td>Female</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>766</td>\n",
       "      <td>2013-07-01 00:02:16</td>\n",
       "      <td>2013-07-01 00:15:02</td>\n",
       "      <td>432</td>\n",
       "      <td>E 7 St &amp; Avenue A</td>\n",
       "      <td>40.726218</td>\n",
       "      <td>-73.983799</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>17963</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980</td>\n",
       "      <td>Female</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>773</td>\n",
       "      <td>2013-07-01 00:02:23</td>\n",
       "      <td>2013-07-01 00:15:16</td>\n",
       "      <td>173</td>\n",
       "      <td>Broadway &amp; W 49 St</td>\n",
       "      <td>40.760647</td>\n",
       "      <td>-73.984427</td>\n",
       "      <td>479.0</td>\n",
       "      <td>9 Ave &amp; W 45 St</td>\n",
       "      <td>40.760193</td>\n",
       "      <td>-73.991255</td>\n",
       "      <td>19365</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1989</td>\n",
       "      <td>Male</td>\n",
       "      <td>2013</td>\n",
       "      <td>July</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration            starttime             stoptime  start station id  \\\n",
       "0           634  2013-07-01 00:00:00  2013-07-01 00:10:34               164   \n",
       "1          1547  2013-07-01 00:00:02  2013-07-01 00:25:49               388   \n",
       "2           178  2013-07-01 00:01:04  2013-07-01 00:04:02               293   \n",
       "3          1580  2013-07-01 00:01:06  2013-07-01 00:27:26               531   \n",
       "4           757  2013-07-01 00:01:10  2013-07-01 00:13:47               382   \n",
       "5           861  2013-07-01 00:01:23  2013-07-01 00:15:44               511   \n",
       "6           550  2013-07-01 00:01:59  2013-07-01 00:11:09               293   \n",
       "7           288  2013-07-01 00:02:16  2013-07-01 00:07:04               224   \n",
       "8           766  2013-07-01 00:02:16  2013-07-01 00:15:02               432   \n",
       "9           773  2013-07-01 00:02:23  2013-07-01 00:15:16               173   \n",
       "\n",
       "        start station name  start station latitude  start station longitude  \\\n",
       "0          E 47 St & 2 Ave               40.753231               -73.970325   \n",
       "1         W 26 St & 10 Ave               40.749718               -74.002950   \n",
       "2    Lafayette St & E 8 St               40.730287               -73.990765   \n",
       "3   Forsyth St & Broome St               40.718939               -73.992663   \n",
       "4  University Pl & E 14 St               40.734927               -73.992005   \n",
       "5       E 14 St & Avenue B               40.729387               -73.977724   \n",
       "6    Lafayette St & E 8 St               40.730287               -73.990765   \n",
       "7    Spruce St & Nassau St               40.711464               -74.005524   \n",
       "8        E 7 St & Avenue A               40.726218               -73.983799   \n",
       "9       Broadway & W 49 St               40.760647               -73.984427   \n",
       "\n",
       "   end station id             end station name  end station latitude  \\\n",
       "0           504.0              1 Ave & E 15 St             40.732219   \n",
       "1           459.0             W 20 St & 11 Ave             40.746745   \n",
       "2           237.0              E 11 St & 2 Ave             40.730473   \n",
       "3           499.0           Broadway & W 60 St             40.769155   \n",
       "4           410.0      Suffolk St & Stanton St             40.720664   \n",
       "5           454.0              E 51 St & 1 Ave             40.754557   \n",
       "6           394.0            E 9 St & Avenue C             40.725213   \n",
       "7           376.0         John St & William St             40.708621   \n",
       "8           336.0  Sullivan St & Washington Sq             40.730477   \n",
       "9           479.0              9 Ave & W 45 St             40.760193   \n",
       "\n",
       "   end station longitude  bikeid    usertype birth year   gender  year month  \n",
       "0             -73.981656   16950    Customer         \\N  Unknown  2013  July  \n",
       "1             -74.007756   19816    Customer         \\N  Unknown  2013  July  \n",
       "2             -73.986724   14548  Subscriber       1980   Female  2013  July  \n",
       "3             -73.981918   16063    Customer         \\N  Unknown  2013  July  \n",
       "4             -73.985180   19213  Subscriber       1986     Male  2013  July  \n",
       "5             -73.965930   16223  Subscriber       1988     Male  2013  July  \n",
       "6             -73.977688   16746    Customer         \\N  Unknown  2013  July  \n",
       "7             -74.007222   16062  Subscriber       1985   Female  2013  July  \n",
       "8             -73.999061   17963  Subscriber       1980   Female  2013  July  \n",
       "9             -73.991255   19365  Subscriber       1989     Male  2013  July  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check = pd.read_csv('Resources/citibike_tripdata.csv')\n",
    "check.head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}