clean_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# do not run this file- data paths are no longer vailid. files are too large to save to repo. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>393</td>\n",
       "      <td>2019-08-01 00:00:01.4680</td>\n",
       "      <td>2019-08-01 00:06:35.3780</td>\n",
       "      <td>531.0</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>408.0</td>\n",
       "      <td>Market St &amp; Cherry St</td>\n",
       "      <td>40.710762</td>\n",
       "      <td>-73.994004</td>\n",
       "      <td>35305</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1996</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>627</td>\n",
       "      <td>2019-08-01 00:00:01.9290</td>\n",
       "      <td>2019-08-01 00:10:29.7840</td>\n",
       "      <td>274.0</td>\n",
       "      <td>Lafayette Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.686919</td>\n",
       "      <td>-73.976682</td>\n",
       "      <td>3409.0</td>\n",
       "      <td>Bergen St &amp; Smith St</td>\n",
       "      <td>40.686744</td>\n",
       "      <td>-73.990632</td>\n",
       "      <td>38822</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1998</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1132</td>\n",
       "      <td>2019-08-01 00:00:04.0480</td>\n",
       "      <td>2019-08-01 00:18:56.1650</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>Front St &amp; Washington St</td>\n",
       "      <td>40.702551</td>\n",
       "      <td>-73.989402</td>\n",
       "      <td>3388.0</td>\n",
       "      <td>President St &amp; Henry St</td>\n",
       "      <td>40.682800</td>\n",
       "      <td>-73.999904</td>\n",
       "      <td>18373</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1780</td>\n",
       "      <td>2019-08-01 00:00:04.1630</td>\n",
       "      <td>2019-08-01 00:29:44.7940</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>473.0</td>\n",
       "      <td>Rivington St &amp; Chrystie St</td>\n",
       "      <td>40.721101</td>\n",
       "      <td>-73.991925</td>\n",
       "      <td>25002</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1517</td>\n",
       "      <td>2019-08-01 00:00:05.4580</td>\n",
       "      <td>2019-08-01 00:25:23.4550</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>31198</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1965</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration                 starttime                  stoptime  \\\n",
       "0           393  2019-08-01 00:00:01.4680  2019-08-01 00:06:35.3780   \n",
       "1           627  2019-08-01 00:00:01.9290  2019-08-01 00:10:29.7840   \n",
       "2          1132  2019-08-01 00:00:04.0480  2019-08-01 00:18:56.1650   \n",
       "3          1780  2019-08-01 00:00:04.1630  2019-08-01 00:29:44.7940   \n",
       "4          1517  2019-08-01 00:00:05.4580  2019-08-01 00:25:23.4550   \n",
       "\n",
       "   start station id              start station name  start station latitude  \\\n",
       "0             531.0          Forsyth St & Broome St               40.718939   \n",
       "1             274.0  Lafayette Ave & Fort Greene Pl               40.686919   \n",
       "2            2000.0        Front St & Washington St               40.702551   \n",
       "3             479.0                 9 Ave & W 45 St               40.760193   \n",
       "4            3312.0                 1 Ave & E 94 St               40.781721   \n",
       "\n",
       "   start station longitude  end station id            end station name  \\\n",
       "0               -73.992663           408.0       Market St & Cherry St   \n",
       "1               -73.976682          3409.0        Bergen St & Smith St   \n",
       "2               -73.989402          3388.0     President St & Henry St   \n",
       "3               -73.991255           473.0  Rivington St & Chrystie St   \n",
       "4               -73.945940          3312.0             1 Ave & E 94 St   \n",
       "\n",
       "   end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0             40.710762             -73.994004   35305  Subscriber   \n",
       "1             40.686744             -73.990632   38822  Subscriber   \n",
       "2             40.682800             -73.999904   18373  Subscriber   \n",
       "3             40.721101             -73.991925   25002  Subscriber   \n",
       "4             40.781721             -73.945940   31198  Subscriber   \n",
       "\n",
       "   birth year  gender  \n",
       "0        1996       2  \n",
       "1        1998       2  \n",
       "2        1988       1  \n",
       "3        1988       1  \n",
       "4        1965       2  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aug_19 = pd.read_csv(\"Data/201908-citibike-tripdata.csv\")\n",
    "sept_19 = pd.read_csv(\"Data/201909-citibike-tripdata.csv\")\n",
    "oct_19 = pd.read_csv(\"Data/201910-citibike-tripdata.csv\")\n",
    "\n",
    "aug_20 = pd.read_csv(\"Data/202008-citibike-tripdata.csv\")\n",
    "sept_20 = pd.read_csv(\"Data/202009-citibike-tripdata.csv\")\n",
    "oct_20 = pd.read_csv(\"Data/202010-citibike-tripdata.csv\")\n",
    "\n",
    "\n",
    "aug_19.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>393</td>\n",
       "      <td>2019-08-01 00:00:01.4680</td>\n",
       "      <td>2019-08-01 00:06:35.3780</td>\n",
       "      <td>531.0</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>408.0</td>\n",
       "      <td>Market St &amp; Cherry St</td>\n",
       "      <td>40.710762</td>\n",
       "      <td>-73.994004</td>\n",
       "      <td>35305</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1996</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>627</td>\n",
       "      <td>2019-08-01 00:00:01.9290</td>\n",
       "      <td>2019-08-01 00:10:29.7840</td>\n",
       "      <td>274.0</td>\n",
       "      <td>Lafayette Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.686919</td>\n",
       "      <td>-73.976682</td>\n",
       "      <td>3409.0</td>\n",
       "      <td>Bergen St &amp; Smith St</td>\n",
       "      <td>40.686744</td>\n",
       "      <td>-73.990632</td>\n",
       "      <td>38822</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1998</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1132</td>\n",
       "      <td>2019-08-01 00:00:04.0480</td>\n",
       "      <td>2019-08-01 00:18:56.1650</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>Front St &amp; Washington St</td>\n",
       "      <td>40.702551</td>\n",
       "      <td>-73.989402</td>\n",
       "      <td>3388.0</td>\n",
       "      <td>President St &amp; Henry St</td>\n",
       "      <td>40.682800</td>\n",
       "      <td>-73.999904</td>\n",
       "      <td>18373</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1780</td>\n",
       "      <td>2019-08-01 00:00:04.1630</td>\n",
       "      <td>2019-08-01 00:29:44.7940</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>473.0</td>\n",
       "      <td>Rivington St &amp; Chrystie St</td>\n",
       "      <td>40.721101</td>\n",
       "      <td>-73.991925</td>\n",
       "      <td>25002</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1517</td>\n",
       "      <td>2019-08-01 00:00:05.4580</td>\n",
       "      <td>2019-08-01 00:25:23.4550</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>3312.0</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>31198</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1965</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration                 starttime                  stoptime  \\\n",
       "0           393  2019-08-01 00:00:01.4680  2019-08-01 00:06:35.3780   \n",
       "1           627  2019-08-01 00:00:01.9290  2019-08-01 00:10:29.7840   \n",
       "2          1132  2019-08-01 00:00:04.0480  2019-08-01 00:18:56.1650   \n",
       "3          1780  2019-08-01 00:00:04.1630  2019-08-01 00:29:44.7940   \n",
       "4          1517  2019-08-01 00:00:05.4580  2019-08-01 00:25:23.4550   \n",
       "\n",
       "   start station id              start station name  start station latitude  \\\n",
       "0             531.0          Forsyth St & Broome St               40.718939   \n",
       "1             274.0  Lafayette Ave & Fort Greene Pl               40.686919   \n",
       "2            2000.0        Front St & Washington St               40.702551   \n",
       "3             479.0                 9 Ave & W 45 St               40.760193   \n",
       "4            3312.0                 1 Ave & E 94 St               40.781721   \n",
       "\n",
       "   start station longitude  end station id            end station name  \\\n",
       "0               -73.992663           408.0       Market St & Cherry St   \n",
       "1               -73.976682          3409.0        Bergen St & Smith St   \n",
       "2               -73.989402          3388.0     President St & Henry St   \n",
       "3               -73.991255           473.0  Rivington St & Chrystie St   \n",
       "4               -73.945940          3312.0             1 Ave & E 94 St   \n",
       "\n",
       "   end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0             40.710762             -73.994004   35305  Subscriber   \n",
       "1             40.686744             -73.990632   38822  Subscriber   \n",
       "2             40.682800             -73.999904   18373  Subscriber   \n",
       "3             40.721101             -73.991925   25002  Subscriber   \n",
       "4             40.781721             -73.945940   31198  Subscriber   \n",
       "\n",
       "   birth year  gender  \n",
       "0        1996       2  \n",
       "1        1998       2  \n",
       "2        1988       1  \n",
       "3        1988       1  \n",
       "4        1965       2  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# combine all csv files into 1 db\n",
    "\n",
    "combined_df = pd.concat([aug_19, sept_19, oct_19, aug_20, sept_20, oct_20])\n",
    "\n",
    "combined_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop columns\n",
    "combined_df.drop(columns=['bikeid', 'stoptime', 'start station id', 'end station id'], inplace=True)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</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>393</td>\n",
       "      <td>2019-08-01 00:00:01.4680</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>Market St &amp; Cherry St</td>\n",
       "      <td>40.710762</td>\n",
       "      <td>-73.994004</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1996</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>627</td>\n",
       "      <td>2019-08-01 00:00:01.9290</td>\n",
       "      <td>Lafayette Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.686919</td>\n",
       "      <td>-73.976682</td>\n",
       "      <td>Bergen St &amp; Smith St</td>\n",
       "      <td>40.686744</td>\n",
       "      <td>-73.990632</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1998</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1132</td>\n",
       "      <td>2019-08-01 00:00:04.0480</td>\n",
       "      <td>Front St &amp; Washington St</td>\n",
       "      <td>40.702551</td>\n",
       "      <td>-73.989402</td>\n",
       "      <td>President St &amp; Henry St</td>\n",
       "      <td>40.682800</td>\n",
       "      <td>-73.999904</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1780</td>\n",
       "      <td>2019-08-01 00:00:04.1630</td>\n",
       "      <td>9 Ave &amp; W 45 St</td>\n",
       "      <td>40.760193</td>\n",
       "      <td>-73.991255</td>\n",
       "      <td>Rivington St &amp; Chrystie St</td>\n",
       "      <td>40.721101</td>\n",
       "      <td>-73.991925</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1517</td>\n",
       "      <td>2019-08-01 00:00:05.4580</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1965</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration                 starttime              start station name  \\\n",
       "0           393  2019-08-01 00:00:01.4680          Forsyth St & Broome St   \n",
       "1           627  2019-08-01 00:00:01.9290  Lafayette Ave & Fort Greene Pl   \n",
       "2          1132  2019-08-01 00:00:04.0480        Front St & Washington St   \n",
       "3          1780  2019-08-01 00:00:04.1630                 9 Ave & W 45 St   \n",
       "4          1517  2019-08-01 00:00:05.4580                 1 Ave & E 94 St   \n",
       "\n",
       "   start station latitude  start station longitude  \\\n",
       "0               40.718939               -73.992663   \n",
       "1               40.686919               -73.976682   \n",
       "2               40.702551               -73.989402   \n",
       "3               40.760193               -73.991255   \n",
       "4               40.781721               -73.945940   \n",
       "\n",
       "             end station name  end station latitude  end station longitude  \\\n",
       "0       Market St & Cherry St             40.710762             -73.994004   \n",
       "1        Bergen St & Smith St             40.686744             -73.990632   \n",
       "2     President St & Henry St             40.682800             -73.999904   \n",
       "3  Rivington St & Chrystie St             40.721101             -73.991925   \n",
       "4             1 Ave & E 94 St             40.781721             -73.945940   \n",
       "\n",
       "     usertype  birth year  gender  \n",
       "0  Subscriber        1996  Female  \n",
       "1  Subscriber        1998  Female  \n",
       "2  Subscriber        1988    Male  \n",
       "3  Subscriber        1988    Male  \n",
       "4  Subscriber        1965  Female  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change gender to male / female\n",
    "combined_df[\"gender\"].replace({1: 'Male', 2: 'Female', 0: 'Unknown'}, inplace=True)\n",
    "\n",
    "combined_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "      <th>Trip Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>393</td>\n",
       "      <td>2019-08-01 00:00:01.4680</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>Market St &amp; Cherry St</td>\n",
       "      <td>40.710762</td>\n",
       "      <td>-73.994004</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1996</td>\n",
       "      <td>Female</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>627</td>\n",
       "      <td>2019-08-01 00:00:01.9290</td>\n",
       "      <td>Lafayette Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.686919</td>\n",
       "      <td>-73.976682</td>\n",
       "      <td>Bergen St &amp; Smith St</td>\n",
       "      <td>40.686744</td>\n",
       "      <td>-73.990632</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1998</td>\n",
       "      <td>Female</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1132</td>\n",
       "      <td>2019-08-01 00:00:04.0480</td>\n",
       "      <td>Front St &amp; Washington St</td>\n",
       "      <td>40.702551</td>\n",
       "      <td>-73.989402</td>\n",
       "      <td>President St &amp; Henry St</td>\n",
       "      <td>40.682800</td>\n",
       "      <td>-73.999904</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>Male</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1780</td>\n",
       "      <td>2019-08-01 00:00:04.1630</td>\n",
       "      <td>9 Ave &amp; W 45 St</td>\n",
       "      <td>40.760193</td>\n",
       "      <td>-73.991255</td>\n",
       "      <td>Rivington St &amp; Chrystie St</td>\n",
       "      <td>40.721101</td>\n",
       "      <td>-73.991925</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>Male</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1517</td>\n",
       "      <td>2019-08-01 00:00:05.4580</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1965</td>\n",
       "      <td>Female</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration                 starttime              start station name  \\\n",
       "0           393  2019-08-01 00:00:01.4680          Forsyth St & Broome St   \n",
       "1           627  2019-08-01 00:00:01.9290  Lafayette Ave & Fort Greene Pl   \n",
       "2          1132  2019-08-01 00:00:04.0480        Front St & Washington St   \n",
       "3          1780  2019-08-01 00:00:04.1630                 9 Ave & W 45 St   \n",
       "4          1517  2019-08-01 00:00:05.4580                 1 Ave & E 94 St   \n",
       "\n",
       "   start station latitude  start station longitude  \\\n",
       "0               40.718939               -73.992663   \n",
       "1               40.686919               -73.976682   \n",
       "2               40.702551               -73.989402   \n",
       "3               40.760193               -73.991255   \n",
       "4               40.781721               -73.945940   \n",
       "\n",
       "             end station name  end station latitude  end station longitude  \\\n",
       "0       Market St & Cherry St             40.710762             -73.994004   \n",
       "1        Bergen St & Smith St             40.686744             -73.990632   \n",
       "2     President St & Henry St             40.682800             -73.999904   \n",
       "3  Rivington St & Chrystie St             40.721101             -73.991925   \n",
       "4             1 Ave & E 94 St             40.781721             -73.945940   \n",
       "\n",
       "     usertype  birth year  gender Trip Year  \n",
       "0  Subscriber        1996  Female      2019  \n",
       "1  Subscriber        1998  Female      2019  \n",
       "2  Subscriber        1988    Male      2019  \n",
       "3  Subscriber        1988    Male      2019  \n",
       "4  Subscriber        1965  Female      2019  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#break out year\n",
    "\n",
    "combined_df['Trip Year'] = combined_df.starttime.str.split(\"-\", expand=True)[0]\n",
    "combined_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "      <th>Trip Year</th>\n",
       "      <th>Trip Month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>393</td>\n",
       "      <td>2019-08-01 00:00:01.4680</td>\n",
       "      <td>Forsyth St &amp; Broome St</td>\n",
       "      <td>40.718939</td>\n",
       "      <td>-73.992663</td>\n",
       "      <td>Market St &amp; Cherry St</td>\n",
       "      <td>40.710762</td>\n",
       "      <td>-73.994004</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1996</td>\n",
       "      <td>Female</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>627</td>\n",
       "      <td>2019-08-01 00:00:01.9290</td>\n",
       "      <td>Lafayette Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.686919</td>\n",
       "      <td>-73.976682</td>\n",
       "      <td>Bergen St &amp; Smith St</td>\n",
       "      <td>40.686744</td>\n",
       "      <td>-73.990632</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1998</td>\n",
       "      <td>Female</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1132</td>\n",
       "      <td>2019-08-01 00:00:04.0480</td>\n",
       "      <td>Front St &amp; Washington St</td>\n",
       "      <td>40.702551</td>\n",
       "      <td>-73.989402</td>\n",
       "      <td>President St &amp; Henry St</td>\n",
       "      <td>40.682800</td>\n",
       "      <td>-73.999904</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>Male</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1780</td>\n",
       "      <td>2019-08-01 00:00:04.1630</td>\n",
       "      <td>9 Ave &amp; W 45 St</td>\n",
       "      <td>40.760193</td>\n",
       "      <td>-73.991255</td>\n",
       "      <td>Rivington St &amp; Chrystie St</td>\n",
       "      <td>40.721101</td>\n",
       "      <td>-73.991925</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1988</td>\n",
       "      <td>Male</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1517</td>\n",
       "      <td>2019-08-01 00:00:05.4580</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>1 Ave &amp; E 94 St</td>\n",
       "      <td>40.781721</td>\n",
       "      <td>-73.945940</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1965</td>\n",
       "      <td>Female</td>\n",
       "      <td>2019</td>\n",
       "      <td>August</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2248864</th>\n",
       "      <td>1120</td>\n",
       "      <td>2020-10-31 23:59:48.4470</td>\n",
       "      <td>Grand St &amp; Havemeyer St</td>\n",
       "      <td>40.712868</td>\n",
       "      <td>-73.956981</td>\n",
       "      <td>Bushwick Ave &amp; McKibbin St</td>\n",
       "      <td>40.705517</td>\n",
       "      <td>-73.939360</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1996</td>\n",
       "      <td>Male</td>\n",
       "      <td>2020</td>\n",
       "      <td>October</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2248865</th>\n",
       "      <td>859</td>\n",
       "      <td>2020-10-31 23:59:49.3770</td>\n",
       "      <td>St. Nicholas Terrace &amp; Convent Ave</td>\n",
       "      <td>40.821294</td>\n",
       "      <td>-73.947868</td>\n",
       "      <td>Hamilton Pl &amp; W 140 St</td>\n",
       "      <td>40.822756</td>\n",
       "      <td>-73.951346</td>\n",
       "      <td>Customer</td>\n",
       "      <td>1999</td>\n",
       "      <td>Male</td>\n",
       "      <td>2020</td>\n",
       "      <td>October</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2248866</th>\n",
       "      <td>546</td>\n",
       "      <td>2020-10-31 23:59:52.1040</td>\n",
       "      <td>W 145 St &amp; Amsterdam Ave</td>\n",
       "      <td>40.825244</td>\n",
       "      <td>-73.947257</td>\n",
       "      <td>Amsterdam Ave &amp; W 167 St</td>\n",
       "      <td>40.839443</td>\n",
       "      <td>-73.937044</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1976</td>\n",
       "      <td>Male</td>\n",
       "      <td>2020</td>\n",
       "      <td>October</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2248867</th>\n",
       "      <td>748</td>\n",
       "      <td>2020-10-31 23:59:53.0290</td>\n",
       "      <td>Cliff St &amp; Fulton St</td>\n",
       "      <td>40.708380</td>\n",
       "      <td>-74.004950</td>\n",
       "      <td>E 7 St &amp; Avenue A</td>\n",
       "      <td>40.726218</td>\n",
       "      <td>-73.983799</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1997</td>\n",
       "      <td>Female</td>\n",
       "      <td>2020</td>\n",
       "      <td>October</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2248868</th>\n",
       "      <td>1366</td>\n",
       "      <td>2020-10-31 23:59:57.9770</td>\n",
       "      <td>Broadway &amp; E 22 St</td>\n",
       "      <td>40.740343</td>\n",
       "      <td>-73.989551</td>\n",
       "      <td>Broadway &amp; W 56 St</td>\n",
       "      <td>40.765265</td>\n",
       "      <td>-73.981923</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1974</td>\n",
       "      <td>Female</td>\n",
       "      <td>2020</td>\n",
       "      <td>October</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13948305 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         tripduration                 starttime  \\\n",
       "0                 393  2019-08-01 00:00:01.4680   \n",
       "1                 627  2019-08-01 00:00:01.9290   \n",
       "2                1132  2019-08-01 00:00:04.0480   \n",
       "3                1780  2019-08-01 00:00:04.1630   \n",
       "4                1517  2019-08-01 00:00:05.4580   \n",
       "...               ...                       ...   \n",
       "2248864          1120  2020-10-31 23:59:48.4470   \n",
       "2248865           859  2020-10-31 23:59:49.3770   \n",
       "2248866           546  2020-10-31 23:59:52.1040   \n",
       "2248867           748  2020-10-31 23:59:53.0290   \n",
       "2248868          1366  2020-10-31 23:59:57.9770   \n",
       "\n",
       "                         start station name  start station latitude  \\\n",
       "0                    Forsyth St & Broome St               40.718939   \n",
       "1            Lafayette Ave & Fort Greene Pl               40.686919   \n",
       "2                  Front St & Washington St               40.702551   \n",
       "3                           9 Ave & W 45 St               40.760193   \n",
       "4                           1 Ave & E 94 St               40.781721   \n",
       "...                                     ...                     ...   \n",
       "2248864             Grand St & Havemeyer St               40.712868   \n",
       "2248865  St. Nicholas Terrace & Convent Ave               40.821294   \n",
       "2248866            W 145 St & Amsterdam Ave               40.825244   \n",
       "2248867                Cliff St & Fulton St               40.708380   \n",
       "2248868                  Broadway & E 22 St               40.740343   \n",
       "\n",
       "         start station longitude            end station name  \\\n",
       "0                     -73.992663       Market St & Cherry St   \n",
       "1                     -73.976682        Bergen St & Smith St   \n",
       "2                     -73.989402     President St & Henry St   \n",
       "3                     -73.991255  Rivington St & Chrystie St   \n",
       "4                     -73.945940             1 Ave & E 94 St   \n",
       "...                          ...                         ...   \n",
       "2248864               -73.956981  Bushwick Ave & McKibbin St   \n",
       "2248865               -73.947868      Hamilton Pl & W 140 St   \n",
       "2248866               -73.947257    Amsterdam Ave & W 167 St   \n",
       "2248867               -74.004950           E 7 St & Avenue A   \n",
       "2248868               -73.989551          Broadway & W 56 St   \n",
       "\n",
       "         end station latitude  end station longitude    usertype  birth year  \\\n",
       "0                   40.710762             -73.994004  Subscriber        1996   \n",
       "1                   40.686744             -73.990632  Subscriber        1998   \n",
       "2                   40.682800             -73.999904  Subscriber        1988   \n",
       "3                   40.721101             -73.991925  Subscriber        1988   \n",
       "4                   40.781721             -73.945940  Subscriber        1965   \n",
       "...                       ...                    ...         ...         ...   \n",
       "2248864             40.705517             -73.939360    Customer        1996   \n",
       "2248865             40.822756             -73.951346    Customer        1999   \n",
       "2248866             40.839443             -73.937044  Subscriber        1976   \n",
       "2248867             40.726218             -73.983799  Subscriber        1997   \n",
       "2248868             40.765265             -73.981923  Subscriber        1974   \n",
       "\n",
       "         gender Trip Year Trip Month  \n",
       "0        Female      2019     August  \n",
       "1        Female      2019     August  \n",
       "2          Male      2019     August  \n",
       "3          Male      2019     August  \n",
       "4        Female      2019     August  \n",
       "...         ...       ...        ...  \n",
       "2248864    Male      2020    October  \n",
       "2248865    Male      2020    October  \n",
       "2248866    Male      2020    October  \n",
       "2248867  Female      2020    October  \n",
       "2248868  Female      2020    October  \n",
       "\n",
       "[13948305 rows x 13 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#break out month\n",
    "combined_df['Trip Month'] = combined_df.starttime.str.split(\"-\", expand=True)[1]\n",
    "combined_df.head()\n",
    "\n",
    "combined_df[\"Trip Month\"].replace({\"08\": \"August\", \"09\": \"September\", \"10\": \"October\"}, inplace=True) \n",
    "combined_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save new csv\n",
    "\n",
    "combined_df.to_csv(r'Data/clean_df.csv')"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}