citi_bike_tripdata_csv_file_cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ecb6a66a",
   "metadata": {},
   "source": [
    "## Citi Bike Tripdata CSV File Cleanup\n",
    "This notebook is used to convert a tripdata CSV file from either before or after Feb/2021 (there was a format change that month) <br>\n",
    "into a modified version of the newer format that is easier for Tableau to handle.<br><br>\n",
    "This notebook also performs several cleaning operations:\n",
    "* Renaming column headers to match new format\n",
    "* Making sure that all stations have the same names, latitudes, and longitudes as their matching station in the 'station_information.json'.\n",
    "* Changing the usertype attributes to the newer member_casual attributes.\n",
    "* Adding calculated fields for trip minutes and trip miles.\n",
    "* Dropping fields that are no longer tracked.\n",
    "* Making sure the fields are the correct data type.\n",
    "* Making sure the lats and lngs have the correct number of decimal places.\n",
    "* Making sure that stations don't have multiple IDs refering to them.\n",
    "* This notebook also adds new entries into the Station Information file as they are discovered."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6cb320d",
   "metadata": {},
   "source": [
    "### Step 1:\n",
    "#### IMPORTANT -  Before starting, you must provide the names of the following files into the cell below:\n",
    "* The CSV file that contains the Citi Bike Trip Data\n",
    "* the JSON file that contains the bike stations' information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "id": "cc2f1a71",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Name of Citi Bike Trip Data CSV file:\n",
    "csvTripDataFile = 'JC-202112-citibike-tripdata.csv'\n",
    "\n",
    "# Name of Citi Bike Station Information JSON file:\n",
    "jsonStationsFile = 'station_information.json'\n",
    "\n",
    "# Name of updated Citi Bike Station Information CSV file (it will be created if it doesn't already exist):\n",
    "csvStationsFile = 'station_information.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d9fba6d",
   "metadata": {},
   "source": [
    "### Step 2:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "id": "3a87adae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1660 entries, 0 to 1659\n",
      "Data columns (total 10 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   new_id          1660 non-null   object \n",
      " 1   old_id          1660 non-null   object \n",
      " 2   name            1660 non-null   object \n",
      " 3   region_id       1660 non-null   int64  \n",
      " 4   latitude        1660 non-null   float64\n",
      " 5   longitude       1660 non-null   float64\n",
      " 6   station_type    1660 non-null   object \n",
      " 7   capacity        1660 non-null   int64  \n",
      " 8   has_kiosk       1660 non-null   bool   \n",
      " 9   rental_methods  1660 non-null   object \n",
      "dtypes: bool(1), float64(2), int64(2), object(5)\n",
      "memory usage: 118.5+ KB\n",
      "\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>new_id</th>\n",
       "      <th>old_id</th>\n",
       "      <th>name</th>\n",
       "      <th>region_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>station_type</th>\n",
       "      <th>capacity</th>\n",
       "      <th>has_kiosk</th>\n",
       "      <th>rental_methods</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6926.01</td>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>71</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>classic</td>\n",
       "      <td>55</td>\n",
       "      <td>True</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5430.08</td>\n",
       "      <td>79</td>\n",
       "      <td>Franklin St &amp; W Broadway</td>\n",
       "      <td>71</td>\n",
       "      <td>40.719116</td>\n",
       "      <td>-74.006667</td>\n",
       "      <td>classic</td>\n",
       "      <td>33</td>\n",
       "      <td>True</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5167.06</td>\n",
       "      <td>82</td>\n",
       "      <td>St James Pl &amp; Pearl St</td>\n",
       "      <td>71</td>\n",
       "      <td>40.711174</td>\n",
       "      <td>-74.000165</td>\n",
       "      <td>classic</td>\n",
       "      <td>27</td>\n",
       "      <td>True</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4354.07</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>71</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>classic</td>\n",
       "      <td>62</td>\n",
       "      <td>True</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6148.02</td>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>71</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>classic</td>\n",
       "      <td>50</td>\n",
       "      <td>True</td>\n",
       "      <td>['KEY', 'CREDITCARD']</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    new_id old_id                           name  region_id   latitude  \\\n",
       "0  6926.01     72               W 52 St & 11 Ave         71  40.767272   \n",
       "1  5430.08     79       Franklin St & W Broadway         71  40.719116   \n",
       "2  5167.06     82         St James Pl & Pearl St         71  40.711174   \n",
       "3  4354.07     83  Atlantic Ave & Fort Greene Pl         71  40.683826   \n",
       "4  6148.02    116                W 17 St & 8 Ave         71  40.741776   \n",
       "\n",
       "   longitude station_type  capacity  has_kiosk         rental_methods  \n",
       "0 -73.993929      classic        55       True  ['KEY', 'CREDITCARD']  \n",
       "1 -74.006667      classic        33       True  ['KEY', 'CREDITCARD']  \n",
       "2 -74.000165      classic        27       True  ['KEY', 'CREDITCARD']  \n",
       "3 -73.976323      classic        62       True  ['KEY', 'CREDITCARD']  \n",
       "4 -74.001497      classic        50       True  ['KEY', 'CREDITCARD']  "
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import external dependencies:\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "\n",
    "# Read in Citi Bike Station Information CSV file (or JSON file if it doesn't exist):\n",
    "successful = False\n",
    "try:\n",
    "    DF1 = pd.read_csv(csvStationsFile, low_memory=False)\n",
    "    successful = True\n",
    "except:\n",
    "    try:\n",
    "        with open(jsonStationsFile) as f:\n",
    "            jsonData = json.load(f)\n",
    "            DF1 = pd.DataFrame.from_dict(jsonData['data']['stations'])\n",
    "            successful = True\n",
    "            #print(json.dumps(jsonData['data']['stations'], indent=4, sort_keys=True))\n",
    "            DF1.drop(['electric_bike_surcharge_waiver', 'station_id', 'eightd_has_key_dispenser'], axis=1, inplace=True)\n",
    "            DF1.drop(['eightd_station_services', 'external_id', 'rental_uris'], axis=1, inplace=True)\n",
    "    except:\n",
    "        print(\"An error occured.\\n\" + \n",
    "              \"Please make sure that the files indicated in Step 1 above exist in this directory and are spelled correctly.\")\n",
    "\n",
    "if successful:\n",
    "    \n",
    "    # Rearrange and rename columns:\n",
    "    DF1.rename(columns = {'short_name': 'new_id', 'legacy_id': 'old_id', 'lat': 'latitude', 'lon': 'longitude'}, inplace=True)\n",
    "    DF1 = DF1[['new_id', 'old_id', 'name', 'region_id','latitude', 'longitude', 'station_type','capacity', 'has_kiosk', 'rental_methods']]\n",
    "\n",
    "    # Correct data types to enable merging:\n",
    "    DF1 = DF1.astype({\"new_id\": 'str', \"old_id\": 'str'})      \n",
    "    \n",
    "    # Round the number of decimal places to 6 for latitude and longitude:\n",
    "    DF1['latitude'].astype(float).round(decimals=6)\n",
    "    DF1['longitude'].astype(float).round(decimals=6)\n",
    "    \n",
    "    # Display:\n",
    "    DF1.info()\n",
    "    print()\n",
    "    \n",
    "DF1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bff63c0",
   "metadata": {},
   "source": [
    "### Step 3:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "id": "e37287c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 48021 entries, 0 to 48020\n",
      "Data columns (total 13 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   ride_id             48021 non-null  object \n",
      " 1   rideable_type       48021 non-null  object \n",
      " 2   started_at          48021 non-null  object \n",
      " 3   ended_at            48021 non-null  object \n",
      " 4   start_station_name  48021 non-null  object \n",
      " 5   start_station_id    48021 non-null  object \n",
      " 6   end_station_name    47662 non-null  object \n",
      " 7   end_station_id      47662 non-null  object \n",
      " 8   start_lat           48021 non-null  float64\n",
      " 9   start_lng           48021 non-null  float64\n",
      " 10  end_lat             47875 non-null  float64\n",
      " 11  end_lng             47875 non-null  float64\n",
      " 12  member_casual       48021 non-null  object \n",
      "dtypes: float64(4), object(9)\n",
      "memory usage: 4.8+ MB\n",
      "\n",
      "This file has 48,021 rows in the newer format.  Please skip to Step 5a below.\n",
      "\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>ride_id</th>\n",
       "      <th>rideable_type</th>\n",
       "      <th>started_at</th>\n",
       "      <th>ended_at</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lng</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_lng</th>\n",
       "      <th>member_casual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DE8FDF918B766B34</td>\n",
       "      <td>classic_bike</td>\n",
       "      <td>2021-12-22 18:44:12</td>\n",
       "      <td>2021-12-22 18:49:56</td>\n",
       "      <td>Marin Light Rail</td>\n",
       "      <td>JC013</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>JC006</td>\n",
       "      <td>40.714584</td>\n",
       "      <td>-74.042817</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DFFA588D5D188F27</td>\n",
       "      <td>classic_bike</td>\n",
       "      <td>2021-12-11 12:32:58</td>\n",
       "      <td>2021-12-11 12:57:13</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>JC006</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3A2A9CA5E1CC449A</td>\n",
       "      <td>classic_bike</td>\n",
       "      <td>2021-12-18 11:08:40</td>\n",
       "      <td>2021-12-18 11:21:10</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>JC006</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72216E8C562A4E02</td>\n",
       "      <td>classic_bike</td>\n",
       "      <td>2021-12-11 17:28:08</td>\n",
       "      <td>2021-12-11 17:42:16</td>\n",
       "      <td>5 Corners Library</td>\n",
       "      <td>JC018</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>JC006</td>\n",
       "      <td>40.734961</td>\n",
       "      <td>-74.059503</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A7B30B3C966FD8A1</td>\n",
       "      <td>classic_bike</td>\n",
       "      <td>2021-12-11 10:39:41</td>\n",
       "      <td>2021-12-11 10:54:56</td>\n",
       "      <td>City Hall - Washington St &amp; 1 St</td>\n",
       "      <td>HB105</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>JC006</td>\n",
       "      <td>40.737360</td>\n",
       "      <td>-74.030970</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>casual</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ride_id rideable_type           started_at             ended_at  \\\n",
       "0  DE8FDF918B766B34  classic_bike  2021-12-22 18:44:12  2021-12-22 18:49:56   \n",
       "1  DFFA588D5D188F27  classic_bike  2021-12-11 12:32:58  2021-12-11 12:57:13   \n",
       "2  3A2A9CA5E1CC449A  classic_bike  2021-12-18 11:08:40  2021-12-18 11:21:10   \n",
       "3  72216E8C562A4E02  classic_bike  2021-12-11 17:28:08  2021-12-11 17:42:16   \n",
       "4  A7B30B3C966FD8A1  classic_bike  2021-12-11 10:39:41  2021-12-11 10:54:56   \n",
       "\n",
       "                 start_station_name start_station_id end_station_name  \\\n",
       "0                  Marin Light Rail            JC013        Warren St   \n",
       "1                         Warren St            JC006        Warren St   \n",
       "2                         Warren St            JC006        Warren St   \n",
       "3                 5 Corners Library            JC018        Warren St   \n",
       "4  City Hall - Washington St & 1 St            HB105        Warren St   \n",
       "\n",
       "  end_station_id  start_lat  start_lng    end_lat    end_lng member_casual  \n",
       "0          JC006  40.714584 -74.042817  40.721124 -74.038051        member  \n",
       "1          JC006  40.721124 -74.038051  40.721124 -74.038051        member  \n",
       "2          JC006  40.721124 -74.038051  40.721124 -74.038051        member  \n",
       "3          JC006  40.734961 -74.059503  40.721124 -74.038051        member  \n",
       "4          JC006  40.737360 -74.030970  40.721124 -74.038051        casual  "
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read in Citi Bike Trip Data CSV file and convert to dataframe:\n",
    "DF2 = pd.read_csv(csvTripDataFile, low_memory=False)\n",
    "\n",
    "# Display column names/types:\n",
    "DF2.info()\n",
    "print()\n",
    "\n",
    "# Determine if the CSV file has the old or new format and display user message:\n",
    "if 'tripduration' in DF2.columns:\n",
    "    old_format = True\n",
    "    print(f\"This file has {len(DF2):,} rows in the older format.  Please run Step 4a below.\")\n",
    "else:\n",
    "    old_format = False\n",
    "    print(f\"This file has {len(DF2):,} rows in the newer format.  Please skip to Step 5a below.\")\n",
    "\n",
    "print()\n",
    "DF2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4b281b3",
   "metadata": {},
   "source": [
    "### Step 4a:  (Run this if the CSV file has the older format.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "id": "a475f46e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This file has the newer format.  Please run Step 5a below.\n"
     ]
    }
   ],
   "source": [
    "# If the CSV file has the newer format, alert user and exit, otherwise run this cell:\n",
    "if not old_format:\n",
    "    print(\"This file has the newer format.  Please run Step 5a below.\")\n",
    "else:\n",
    "    \n",
    "    # Set some counting variables:\n",
    "    append_count = 0\n",
    "    correction_count = 0\n",
    "    \n",
    "    # Correct data types to enable accurate searching:\n",
    "    DF2 = DF2.astype({\"start station id\": 'str', \"end station id\": 'str'}) \n",
    "    \n",
    "    # Drop rows with duplicated start station ids:\n",
    "    DFtemp = DF2.drop_duplicates(subset=['start station id']).dropna(subset=['start station id'])\n",
    "    \n",
    "    # Search through all rows for start station ids that do not exist in the station information json:\n",
    "    for index, row in DFtemp.iterrows():\n",
    "        if str(row[3]) not in DF1['old_id'].values:\n",
    "            \n",
    "            # If the start station name does not exist in the station information json, append this station to it:\n",
    "            if str(row[4]) not in DF1['name'].values:\n",
    "                \n",
    "                # But only append if the name, lat, and lng are not missing:\n",
    "                if not pd.isnull([row[4], row[5], row[6]]).any():\n",
    "                    new_row = {\n",
    "                        'new_id': str(row[3]),\n",
    "                        'old_id': str(row[3]),\n",
    "                        'name': str(row[4]),\n",
    "                        'region_id': 71,\n",
    "                        'latitude': round(float(row[5]), 6),\n",
    "                        'longitude': round(float(row[6]), 6),\n",
    "                        'station_type': 'classic',\n",
    "                        'capacity': 0,\n",
    "                        'has_kiosk': True,\n",
    "                        'rental_methods': '[KEY, CREDITCARD]'\n",
    "                    }\n",
    "                    DF1 = DF1.append(new_row, ignore_index=True)\n",
    "                    append_count += 1\n",
    "            \n",
    "            # Otherwise, change the start station id to match the entry in the station information json with the same name:\n",
    "            else:\n",
    "                DF2.loc[DF2['start station name']==str(row[4]), ['start station id']] = DF1.loc[DF1['name']==str(row[4]), ['old_id']]\n",
    "                correction_count += 1\n",
    "    \n",
    "    # Drop rows with duplicated end station ids:\n",
    "    DFtemp = DF2.drop_duplicates(subset=['end station id']).dropna(subset=['end station id'])\n",
    "    \n",
    "    # Search through all rows for end station ids that do not exist in the station information json:\n",
    "    for index, row in DFtemp.iterrows():\n",
    "        if str(row[7]) not in DF1['old_id'].values:\n",
    "            \n",
    "            # If the end station name does not exist in the station information json, append this station to it:\n",
    "            if str(row[8]) not in DF1['name'].values:\n",
    "                \n",
    "                # But only append if the name, lat, and lng are not missing:\n",
    "                if not pd.isnull([row[8], row[9], row[10]]).any():\n",
    "                    new_row = {\n",
    "                        'new_id': str(row[7]),\n",
    "                        'old_id': str(row[7]),\n",
    "                        'name': str(row[8]),\n",
    "                        'region_id': 71,\n",
    "                        'latitude': round(float(row[9]), 6),\n",
    "                        'longitude': round(float(row[10]), 6),\n",
    "                        'station_type': 'classic',\n",
    "                        'capacity': 0,\n",
    "                        'has_kiosk': True,\n",
    "                        'rental_methods': '[KEY, CREDITCARD]'\n",
    "                    }\n",
    "                    DF1 = DF1.append(new_row, ignore_index=True)\n",
    "                    append_count += 1\n",
    "            \n",
    "            # Otherwise, change the end station id to match the entry in the station information json with the same name:\n",
    "            else:\n",
    "                DF2.loc[DF2['end station name']==str(row[8]), ['end station id']] = DF1.loc[DF1['name']==str(row[8]), ['old_id']]\n",
    "                correction_count += 1\n",
    "    \n",
    "    # Display:\n",
    "    print(f\"{append_count} entries added to {csvStationsFile}.\")\n",
    "    print(f\"{correction_count} station IDs corrected in the {csvTripDataFile.split('-')[0]} data set.\")\n",
    "    print(\"Please run Step 4b below.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "077864f1",
   "metadata": {},
   "source": [
    "### Step 4b:  (Run this if the CSV file has the older format.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "id": "9d4624a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This file has the newer format.  Please run Step 5a below.\n"
     ]
    }
   ],
   "source": [
    "# If the CSV file has the newer format, alert user and exit, otherwise run this cell:\n",
    "if not old_format:\n",
    "    print(\"This file has the newer format.  Please run Step 5a below.\")\n",
    "else:\n",
    "\n",
    "    # If any rows are missing ending station info, assume the bikes were returned to the starting station:\n",
    "    DF2['end station id'].fillna(DF2['start station id'], inplace=True)\n",
    "    DF2['end station name'].fillna(DF2['start station name'], inplace=True)\n",
    "    DF2['end station latitude'].fillna(DF2['start station latitude'], inplace=True)\n",
    "    DF2['end station longitude'].fillna(DF2['start station longitude'], inplace=True)\n",
    "    \n",
    "    # Correct data types to enable merging:\n",
    "    DF2 = DF2.astype({\"start station id\": 'str', \"end station id\": 'str'})    \n",
    "\n",
    "    # Replace all starting stations' latitudes, longitudes, and names with the official lats, lons, and names from JSON dataframe:\n",
    "    DF3 = pd.merge(DF2, DF1, left_on='start station id', right_on='old_id', how='left')\n",
    "    DF3['start_lat'] = DF3['latitude']\n",
    "    DF3['start_lng'] = DF3['longitude']\n",
    "    DF3['start_station_name'] = DF3['name']\n",
    "    DF3['start_station_id'] = DF3['new_id']\n",
    "    DF3.drop(['new_id', 'old_id', 'name', 'region_id', 'latitude', 'longitude'], axis=1, inplace=True) \n",
    "    DF3.drop(['station_type', 'capacity', 'has_kiosk', 'rental_methods'], axis=1, inplace=True)\n",
    "\n",
    "    # Replace all ending stations' latitudes, longitudes, and names with the official lats, lons, and names from JSON dataframe:\n",
    "    DF3 = pd.merge(DF3, DF1, left_on='end station id', right_on='old_id', how='left')\n",
    "    DF3['end_lat'] = DF3['latitude']\n",
    "    DF3['end_lng'] = DF3['longitude']\n",
    "    DF3['end_station_name'] = DF3['name']\n",
    "    DF3['end_station_id'] = DF3['new_id']\n",
    "    DF3.drop(['new_id', 'old_id', 'name', 'region_id', 'latitude', 'longitude'], axis=1, inplace=True) \n",
    "    DF3.drop(['station_type', 'capacity', 'has_kiosk', 'rental_methods'], axis=1, inplace=True)\n",
    "    \n",
    "    # Fill NaN values in new columns with old values from the original dataframe:\n",
    "    DF3['start_lat'] = DF3['start_lat'].fillna(DF3['start station latitude'])\n",
    "    DF3['start_lng'] = DF3['start_lng'].fillna(DF3['start station longitude'])\n",
    "    DF3['start_station_name'] = DF3['start_station_name'].fillna(DF3['start station name'])\n",
    "    DF3['end_lat'] = DF3['end_lat'].fillna(DF3['end station latitude'])\n",
    "    DF3['end_lng'] = DF3['end_lng'].fillna(DF3['end station longitude'])\n",
    "    DF3['end_station_name'] = DF3['end_station_name'].fillna(DF3['end station name'])\n",
    "    \n",
    "    # Drop any remaining rows containing NaN values (if any):\n",
    "    DF3.dropna()\n",
    "\n",
    "    # Drop unwanted columns:\n",
    "    DF3.drop(['tripduration', 'bikeid', 'birth year', 'gender'], axis=1, inplace=True)\n",
    "    DF3.drop(['start station name', 'start station id', 'start station latitude', 'start station longitude'], axis=1, inplace=True)\n",
    "    DF3.drop(['end station name', 'end station id', 'end station latitude', 'end station longitude'], axis=1, inplace=True)\n",
    "\n",
    "    # Rename columns:\n",
    "    DF3.rename(columns = {'starttime':'started_at', 'stoptime':'ended_at'}, inplace=True)\n",
    "    DF3.rename(columns = {'usertype':'member_casual'}, inplace=True)\n",
    "\n",
    "    # Replace the older attributes in member_casual field with newer attributes:\n",
    "    DF3.loc[DF3['member_casual'] == 'Subscriber', 'member_casual'] = 'member'\n",
    "    DF3.loc[DF3['member_casual'] == 'Customer', 'member_casual'] = 'casual'\n",
    "\n",
    "    # Create Trip ID column:\n",
    "    DF3['trip_id'] = csvTripDataFile.split(\"-\")[0] + DF3.index.map(str)\n",
    "    \n",
    "    # Round the number of decimal places to 6 for lats and lngs:\n",
    "    DF3['start_lat'].astype(float).round(decimals=6)\n",
    "    DF3['start_lng'].astype(float).round(decimals=6)\n",
    "    DF3['end_lat'].astype(float).round(decimals=6)\n",
    "    DF3['end_lng'].astype(float).round(decimals=6)\n",
    "\n",
    "    # Display\n",
    "    print(\"Dataframe successfully cleaned.\")\n",
    "    print(f\"{len(DF3):,} rows processed.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d853ddf",
   "metadata": {},
   "source": [
    "### Step 5a: (Run this if the CSV file has the newer format.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "id": "0076c5ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 entries added to station_information.csv.\n",
      "0 station IDs corrected in the JC data set.\n",
      "Please run Step 5b below.\n"
     ]
    }
   ],
   "source": [
    "# If the CSV file has the newer format, alert user and exit, otherwise run this cell:\n",
    "if old_format:\n",
    "    print(\"This file has the older format.  Please run Step 4a above.\")\n",
    "else:\n",
    "    \n",
    "    # Set some counting variables:\n",
    "    append_count = 0\n",
    "    correction_count = 0\n",
    "    \n",
    "    # Correct data types to enable accurate searching:\n",
    "    DF2 = DF2.astype({\"start_station_id\": 'str', \"end_station_id\": 'str'}) \n",
    "    \n",
    "    # Drop rows with duplicated start station ids:\n",
    "    DFtemp = DF2.drop_duplicates(subset=['start_station_id']).dropna(subset=['start_station_id'])\n",
    "    \n",
    "    # Search through all rows for start station ids that do not exist in the station information json:\n",
    "    for index, row in DFtemp.iterrows():\n",
    "        if str(row[5]) not in DF1['new_id'].values:\n",
    "            \n",
    "            # If the start station name does not exist in the station information json, append this station to it:\n",
    "            if str(row[4]) not in DF1['name'].values:\n",
    "\n",
    "                # But only append if the name, lat, and lng are not missing:\n",
    "                if not pd.isnull([row[4], row[8], row[9]]).any():                \n",
    "                    new_row = {\n",
    "                        'new_id': str(row[5]),\n",
    "                        'old_id': str(row[5]),\n",
    "                        'name': str(row[4]),\n",
    "                        'region_id': 71,\n",
    "                        'latitude': round(float(row[8]), 6),\n",
    "                        'longitude': round(float(row[9]), 6),\n",
    "                        'station_type': 'docked_bike',\n",
    "                        'capacity': 0,\n",
    "                        'has_kiosk': True,\n",
    "                        'rental_methods': '[KEY, CREDITCARD]'\n",
    "                    }\n",
    "                    DF1 = DF1.append(new_row, ignore_index=True)\n",
    "                    append_count += 1\n",
    "            \n",
    "            # Otherwise, change the start station id to match the entry in the station information json with the same name:\n",
    "            else:\n",
    "                DF2.loc[DF2['start_station_name']==str(row[4]), ['start_station_id']] = DF1.loc[DF1['name']==str(row[4]), ['new_id']]\n",
    "                correction_count += 1\n",
    "    \n",
    "    # Drop rows with duplicated end station ids:\n",
    "    DFtemp = DF2.drop_duplicates(subset=['end_station_id']).dropna(subset=['end_station_id'])\n",
    "    \n",
    "    # Search through all rows for end station ids that do not exist in the station information json:\n",
    "    for index, row in DFtemp.iterrows():\n",
    "        if str(row[7]) not in DF1['new_id'].values:\n",
    "            \n",
    "            # If the end station name does not exist in the station information json, append this station to it:\n",
    "            if str(row[6]) not in DF1['name'].values:\n",
    "                \n",
    "                # But only append if the name, lat, and lng are not missing:\n",
    "                if not pd.isnull([row[6], row[10], row[11]]).any():                \n",
    "                    new_row = {\n",
    "                        'new_id': str(row[7]),\n",
    "                        'old_id': str(row[7]),\n",
    "                        'name': str(row[6]),\n",
    "                        'region_id': 71,\n",
    "                        'latitude': round(float(row[10]), 6),\n",
    "                        'longitude': round(float(row[11]), 6),\n",
    "                        'station_type': 'docked_bike',\n",
    "                        'capacity': 0,\n",
    "                        'has_kiosk': True,\n",
    "                        'rental_methods': '[KEY, CREDITCARD]'\n",
    "                    }\n",
    "                    DF1 = DF1.append(new_row, ignore_index=True)\n",
    "                    append_count += 1\n",
    "            \n",
    "            # Otherwise, change the end station id to match the entry in the station information json with the same name:\n",
    "            else:\n",
    "                DF2.loc[DF2['end_station_name']==str(row[6]), ['end_station_id']] = DF1.loc[DF1['name']==str(row[6]), ['new_id']]\n",
    "                correction_count += 1\n",
    "    \n",
    "    # Display:\n",
    "    print(f\"{append_count} entries added to {csvStationsFile}.\")\n",
    "    print(f\"{correction_count} station IDs corrected in the {csvTripDataFile.split('-')[0]} data set.\")\n",
    "    print(\"Please run Step 5b below.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7254f69e",
   "metadata": {},
   "source": [
    "### Step 5b: (Run this if the CSV file has the newer format.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "id": "915d571b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe successfully cleaned.\n",
      "48,021 rows processed.\n"
     ]
    }
   ],
   "source": [
    "# If the CSV file has the older format, alert user and exit, otherwise run this cell:\n",
    "if old_format:\n",
    "    print(\"This file has the older format.  Please run Step 4a above.\")\n",
    "else:\n",
    "\n",
    "    # If any rows are missing ending station info, assume the bikes were returned to the starting station:\n",
    "    DF2['end_station_id'].fillna(DF2['start_station_id'], inplace=True)\n",
    "    DF2['end_station_name'].fillna(DF2['start_station_name'], inplace=True)\n",
    "    DF2['end_lat'].fillna(DF2['start_lat'], inplace=True)\n",
    "    DF2['end_lng'].fillna(DF2['start_lng'], inplace=True)\n",
    "\n",
    "    # Correct data types to enable merging:\n",
    "    DF2 = DF2.astype({\"start_station_id\": 'str', \"end_station_id\": 'str'})\n",
    "    \n",
    "    # Rename columns:\n",
    "    DF2.rename(columns = {'start_station_name':'start station name', 'end_station_name':'end station name'}, inplace=True)\n",
    "    DF2.rename(columns = {'start_station_id':'start station id', 'end_station_id':'end station id'}, inplace=True)\n",
    "    DF2.rename(columns = {'start_lat':'start station latitude', 'start_lng':'start station longitude'}, inplace=True)    \n",
    "    DF2.rename(columns = {'end_lat':'end station latitude', 'end_lng':'end station longitude'}, inplace=True)\n",
    "    DF2.rename(columns = {'ride_id': 'trip_id'}, inplace=True)\n",
    "    \n",
    "    # Replace all starting stations' latitudes, longitudes, and names with the official lats, lons, and names from JSON dataframe:\n",
    "    DF3 = pd.merge(DF2, DF1, left_on='start station id', right_on='new_id', how='left')\n",
    "    DF3['start_lat'] = DF3['latitude']\n",
    "    DF3['start_lng'] = DF3['longitude']\n",
    "    DF3['start_station_name'] = DF3['name']\n",
    "    DF3['start_station_id'] = DF3['new_id']\n",
    "    DF3.drop(['new_id', 'old_id', 'name', 'region_id', 'latitude', 'longitude'], axis=1, inplace=True) \n",
    "    DF3.drop(['station_type', 'capacity', 'has_kiosk', 'rental_methods'], axis=1, inplace=True)\n",
    "\n",
    "    # Replace all ending stations' latitudes, longitudes, and names with the official lats, lons, and names from JSON dataframe:\n",
    "    DF3 = pd.merge(DF3, DF1, left_on='end station id', right_on='new_id', how='left')\n",
    "    DF3['end_lat'] = DF3['latitude']\n",
    "    DF3['end_lng'] = DF3['longitude']\n",
    "    DF3['end_station_name'] = DF3['name']\n",
    "    DF3['end_station_id'] = DF3['new_id']\n",
    "    DF3.drop(['new_id', 'old_id', 'name', 'region_id', 'latitude', 'longitude'], axis=1, inplace=True) \n",
    "    DF3.drop(['station_type', 'capacity', 'has_kiosk', 'rental_methods'], axis=1, inplace=True)\n",
    "    \n",
    "    # Fill NaN values in new columns with old values from the original dataframe:\n",
    "    DF3['start_lat'] = DF3['start_lat'].fillna(DF3['start station latitude'])\n",
    "    DF3['start_lng'] = DF3['start_lng'].fillna(DF3['start station longitude'])\n",
    "    DF3['start_station_name'] = DF3['start_station_name'].fillna(DF3['start station name'])\n",
    "    DF3['start_station_id'] = DF3['start_station_id'].fillna(DF3['start station id'])\n",
    "    DF3['end_lat'] = DF3['end_lat'].fillna(DF3['end station latitude'])\n",
    "    DF3['end_lng'] = DF3['end_lng'].fillna(DF3['end station longitude'])\n",
    "    DF3['end_station_name'] = DF3['end_station_name'].fillna(DF3['end station name'])\n",
    "    DF3['end_station_id'] = DF3['end_station_id'].fillna(DF3['end station id'])\n",
    "    \n",
    "    # Drop any remaining rows containing NaN values (if any):\n",
    "    DF3.dropna()\n",
    "    \n",
    "    # Drop unwanted columns:\n",
    "    DF3.drop(['rideable_type'], axis=1, inplace=True)\n",
    "    DF3.drop(['start station name', 'start station latitude', 'start station longitude'], axis=1, inplace=True)\n",
    "    DF3.drop(['end station name', 'end station latitude', 'end station longitude'], axis=1, inplace=True)\n",
    " \n",
    "    # Round the number of decimal places to 6 for lats and lngs:\n",
    "    DF3['start_lat'].astype(float).round(decimals=6)\n",
    "    DF3['start_lng'].astype(float).round(decimals=6)\n",
    "    DF3['end_lat'].astype(float).round(decimals=6)\n",
    "    DF3['end_lng'].astype(float).round(decimals=6)\n",
    "\n",
    "    # Display\n",
    "    print(\"Dataframe successfully cleaned.\")\n",
    "    print(f\"{len(DF3):,} rows processed.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46d0c8dd",
   "metadata": {},
   "source": [
    "### Step 6:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "id": "9f2ea88c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 48021 entries, 0 to 48020\n",
      "Data columns (total 14 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   trip_id             48021 non-null  object \n",
      " 1   member_casual       48021 non-null  object \n",
      " 2   started_at          48021 non-null  object \n",
      " 3   start_station_id    48021 non-null  object \n",
      " 4   start_station_name  48021 non-null  object \n",
      " 5   start_lat           48021 non-null  float64\n",
      " 6   start_lng           48021 non-null  float64\n",
      " 7   ended_at            48021 non-null  object \n",
      " 8   end_station_id      48021 non-null  object \n",
      " 9   end_station_name    48021 non-null  object \n",
      " 10  end_lat             48021 non-null  float64\n",
      " 11  end_lng             48021 non-null  float64\n",
      " 12  trip_minutes        48021 non-null  int32  \n",
      " 13  trip_miles          48021 non-null  float64\n",
      "dtypes: float64(5), int32(1), object(8)\n",
      "memory usage: 5.3+ MB\n",
      "\n",
      "Final Cleaned Citi Bike Trip Data Table (48,021 Rows):\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>trip_id</th>\n",
       "      <th>member_casual</th>\n",
       "      <th>started_at</th>\n",
       "      <th>start_station_id</th>\n",
       "      <th>start_station_name</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lng</th>\n",
       "      <th>ended_at</th>\n",
       "      <th>end_station_id</th>\n",
       "      <th>end_station_name</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_lng</th>\n",
       "      <th>trip_minutes</th>\n",
       "      <th>trip_miles</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DE8FDF918B766B34</td>\n",
       "      <td>member</td>\n",
       "      <td>2021-12-22 18:44:12</td>\n",
       "      <td>JC013</td>\n",
       "      <td>Marin Light Rail</td>\n",
       "      <td>40.714584</td>\n",
       "      <td>-74.042817</td>\n",
       "      <td>2021-12-22 18:49:56</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>5</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DFFA588D5D188F27</td>\n",
       "      <td>member</td>\n",
       "      <td>2021-12-11 12:32:58</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>2021-12-11 12:57:13</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>24</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3A2A9CA5E1CC449A</td>\n",
       "      <td>member</td>\n",
       "      <td>2021-12-18 11:08:40</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>2021-12-18 11:21:10</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>12</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>72216E8C562A4E02</td>\n",
       "      <td>member</td>\n",
       "      <td>2021-12-11 17:28:08</td>\n",
       "      <td>JC018</td>\n",
       "      <td>5 Corners Library</td>\n",
       "      <td>40.734961</td>\n",
       "      <td>-74.059503</td>\n",
       "      <td>2021-12-11 17:42:16</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>14</td>\n",
       "      <td>1.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A7B30B3C966FD8A1</td>\n",
       "      <td>casual</td>\n",
       "      <td>2021-12-11 10:39:41</td>\n",
       "      <td>HB105</td>\n",
       "      <td>City Hall - Washington St &amp; 1 St</td>\n",
       "      <td>40.737360</td>\n",
       "      <td>-74.030970</td>\n",
       "      <td>2021-12-11 10:54:56</td>\n",
       "      <td>JC006</td>\n",
       "      <td>Warren St</td>\n",
       "      <td>40.721124</td>\n",
       "      <td>-74.038051</td>\n",
       "      <td>15</td>\n",
       "      <td>1.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            trip_id member_casual           started_at start_station_id  \\\n",
       "0  DE8FDF918B766B34        member  2021-12-22 18:44:12            JC013   \n",
       "1  DFFA588D5D188F27        member  2021-12-11 12:32:58            JC006   \n",
       "2  3A2A9CA5E1CC449A        member  2021-12-18 11:08:40            JC006   \n",
       "3  72216E8C562A4E02        member  2021-12-11 17:28:08            JC018   \n",
       "4  A7B30B3C966FD8A1        casual  2021-12-11 10:39:41            HB105   \n",
       "\n",
       "                 start_station_name  start_lat  start_lng  \\\n",
       "0                  Marin Light Rail  40.714584 -74.042817   \n",
       "1                         Warren St  40.721124 -74.038051   \n",
       "2                         Warren St  40.721124 -74.038051   \n",
       "3                 5 Corners Library  40.734961 -74.059503   \n",
       "4  City Hall - Washington St & 1 St  40.737360 -74.030970   \n",
       "\n",
       "              ended_at end_station_id end_station_name    end_lat    end_lng  \\\n",
       "0  2021-12-22 18:49:56          JC006        Warren St  40.721124 -74.038051   \n",
       "1  2021-12-11 12:57:13          JC006        Warren St  40.721124 -74.038051   \n",
       "2  2021-12-18 11:21:10          JC006        Warren St  40.721124 -74.038051   \n",
       "3  2021-12-11 17:42:16          JC006        Warren St  40.721124 -74.038051   \n",
       "4  2021-12-11 10:54:56          JC006        Warren St  40.721124 -74.038051   \n",
       "\n",
       "   trip_minutes  trip_miles  \n",
       "0             5        0.52  \n",
       "1            24        0.00  \n",
       "2            12        0.00  \n",
       "3            14        1.48  \n",
       "4            15        1.18  "
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add trip duration column:\n",
    "DF3['trip_minutes'] = (pd.to_datetime(DF3['ended_at']) - pd.to_datetime(DF3['started_at'])).astype('timedelta64[m]').astype(int)\n",
    "\n",
    "# Add trip distance column:\n",
    "latdeg2miles = 69   # degrees lat to miles conversion factor for NYC\n",
    "lngdeg2miles = 53   # degrees lon to miles conversion factor for NYC\n",
    "DF3['trip_miles'] = np.sqrt(((DF3['end_lng'] - DF3['start_lng']) * lngdeg2miles)**2 + ((DF3['end_lat'] -DF3['start_lat']) * latdeg2miles)**2).round(decimals=2)\n",
    "\n",
    "# Rearrange the columns:\n",
    "DF3 = DF3[['trip_id', 'member_casual', 'started_at', 'start_station_id', 'start_station_name', 'start_lat', 'start_lng',\n",
    "           'ended_at', 'end_station_id', 'end_station_name', 'end_lat', 'end_lng', 'trip_minutes', 'trip_miles']]\n",
    "\n",
    "# Display\n",
    "DF3.info()\n",
    "print()\n",
    "print(f\"Final Cleaned Citi Bike Trip Data Table ({len(DF3):,} Rows):\")\n",
    "DF3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed11c6a3",
   "metadata": {},
   "source": [
    "### Step 7:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "id": "8c4c1ad3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "JC-202112-citibike-tripdata-cleaned.csv successfully created.\n",
      "station_information.csv successfully created/updated.\n"
     ]
    }
   ],
   "source": [
    "# Save the cleaned and modified Citi Bike Trip Data as a CSV file:\n",
    "outputFile = csvTripDataFile.split(\".\")[0] + \"-cleaned.csv\"\n",
    "DF3.to_csv(outputFile, index=False)\n",
    "\n",
    "# Save the cleaned and modified Citi Bike Station Information Data as a CSV file:\n",
    "DF1.to_csv(csvStationsFile, index=False)\n",
    "\n",
    "# Display user message:\n",
    "print(f\"{outputFile} successfully created.\")\n",
    "print(f\"{csvStationsFile} successfully created/updated.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "id": "372071f4",
   "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>new_id</th>\n",
       "      <th>old_id</th>\n",
       "      <th>name</th>\n",
       "      <th>region_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>station_type</th>\n",
       "      <th>capacity</th>\n",
       "      <th>has_kiosk</th>\n",
       "      <th>rental_methods</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [new_id, old_id, name, region_id, latitude, longitude, station_type, capacity, has_kiosk, rental_methods]\n",
       "Index: []"
      ]
     },
     "execution_count": 240,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicates = DF1[DF1.duplicated(['old_id', 'new_id', 'name'])]\n",
    "duplicates"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}