DataProcess.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "65612e83",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'bike_type'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "File \u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/core/indexes/base.py:3621\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   3620\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 3621\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_engine\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcasted_key\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   3622\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "File \u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/_libs/index.pyx:136\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/_libs/index.pyx:163\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:5198\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:5206\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'bike_type'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Input \u001b[0;32mIn [14]\u001b[0m, in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     27\u001b[0m df \u001b[38;5;241m=\u001b[39m df[(df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mstart_station\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m!=\u001b[39m \u001b[38;5;241m3000\u001b[39m) \u001b[38;5;241m&\u001b[39m (df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mend_station\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m!=\u001b[39m \u001b[38;5;241m3000\u001b[39m)]\n\u001b[1;32m     29\u001b[0m \u001b[38;5;66;03m# Identify bikes that have both \"standard\" and \"smart\" types\u001b[39;00m\n\u001b[0;32m---> 30\u001b[0m bikes_with_both_types \u001b[38;5;241m=\u001b[39m df[\u001b[43mdf\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mbike_type\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[38;5;241m.\u001b[39misin([\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mstandard\u001b[39m\u001b[38;5;124m'\u001b[39m, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124msmart\u001b[39m\u001b[38;5;124m'\u001b[39m])]\u001b[38;5;241m.\u001b[39mgroupby(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mbike_id\u001b[39m\u001b[38;5;124m'\u001b[39m)[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mbike_type\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39mnunique()\n\u001b[1;32m     31\u001b[0m bikes_with_both_types \u001b[38;5;241m=\u001b[39m bikes_with_both_types[bikes_with_both_types \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m]\u001b[38;5;241m.\u001b[39mindex\u001b[38;5;241m.\u001b[39mtolist()\n\u001b[1;32m     33\u001b[0m \u001b[38;5;66;03m# Update their `bike_type` to \"standard/smart\"\u001b[39;00m\n",
      "File \u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/core/frame.py:3506\u001b[0m, in \u001b[0;36mDataFrame.__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   3504\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcolumns\u001b[38;5;241m.\u001b[39mnlevels \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[1;32m   3505\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_multilevel(key)\n\u001b[0;32m-> 3506\u001b[0m indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcolumns\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   3507\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_integer(indexer):\n\u001b[1;32m   3508\u001b[0m     indexer \u001b[38;5;241m=\u001b[39m [indexer]\n",
      "File \u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/core/indexes/base.py:3623\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   3621\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_engine\u001b[38;5;241m.\u001b[39mget_loc(casted_key)\n\u001b[1;32m   3622\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n\u001b[0;32m-> 3623\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01merr\u001b[39;00m\n\u001b[1;32m   3624\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[1;32m   3625\u001b[0m     \u001b[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[1;32m   3626\u001b[0m     \u001b[38;5;66;03m#  InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[1;32m   3627\u001b[0m     \u001b[38;5;66;03m#  the TypeError.\u001b[39;00m\n\u001b[1;32m   3628\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_indexing_error(key)\n",
      "\u001b[0;31mKeyError\u001b[0m: 'bike_type'"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "\n",
    "for file in glob.glob('./Dataset/*.csv'):\n",
    "\n",
    "\n",
    "    # Load the CSV file into a DataFrame\n",
    "    df = pd.read_csv(file)\n",
    "\n",
    "    # Convert date formats in the `start_time` and `end_time` columns to be consistent (ISO format).\n",
    "    df['start_time'] = pd.to_datetime(df['start_time']).dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "    df['end_time'] = pd.to_datetime(df['end_time']).dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "    # Exclude trips that exceeded 8 hours (480 minutes) in the `duration` column.\n",
    "    df = df[df['duration'] <= 480]\n",
    "\n",
    "    # Exclude trips with `plan_duration` values of 999, 150, or blank.\n",
    "    df = df[~df['plan_duration'].isin([999, 150])]\n",
    "\n",
    "    # Modify the `passholder_type` column:\n",
    "    # Change \"Flex Pass\" to \"Annual Pass\".\n",
    "    df['passholder_type'] = df['passholder_type'].replace('Flex Pass', 'Annual Pass')\n",
    "    # If the `plan_duration` is 1 and the `passholder_type` is \"Walk-up\", change it to \"One Day Pass\".\n",
    "    df.loc[(df['plan_duration'] == 1) & (df['passholder_type'] == 'Walk-up'), 'passholder_type'] = 'One Day Pass'\n",
    "\n",
    "    # Exclude any trips with `start_station` or `end_station` values of 3000.\n",
    "    df = df[(df['start_station'] != 3000) & (df['end_station'] != 3000)]\n",
    "\n",
    "    # Identify bikes that have both \"standard\" and \"smart\" types\n",
    "    bikes_with_both_types = df[df['bike_type'].isin(['standard', 'smart'])].groupby('bike_id')['bike_type'].nunique()\n",
    "    bikes_with_both_types = bikes_with_both_types[bikes_with_both_types > 1].index.tolist()\n",
    "\n",
    "    # Update their `bike_type` to \"standard/smart\"\n",
    "    df.loc[df['bike_id'].isin(bikes_with_both_types), 'bike_type'] = 'standard/smart'\n",
    "\n",
    "    df.head()\n",
    "\n",
    "#put into new CSV file \n",
    "#df.to_csv(\"/path/to/save/cleaned_data.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "02858f0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This Python 3 environment comes with many helpful analytics libraries installed\n",
    "# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n",
    "# For example, here's several helpful packages to load\n",
    "\n",
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "\n",
    "# Input data files are available in the read-only \"../input/\" directory\n",
    "# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n",
    "\n",
    "import os\n",
    "for dirname, _, filenames in os.walk('/kaggle/input'):\n",
    "    for filename in filenames:\n",
    "        print(os.path.join(dirname, filename))\n",
    "\n",
    "# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n",
    "# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b6bc3998",
   "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>trip_id</th>\n",
       "      <th>duration</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>start_station</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_lon</th>\n",
       "      <th>end_station</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_lon</th>\n",
       "      <th>bike_id</th>\n",
       "      <th>plan_duration</th>\n",
       "      <th>trip_route_category</th>\n",
       "      <th>passholder_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33404951</td>\n",
       "      <td>36</td>\n",
       "      <td>7/1/2017 0:09</td>\n",
       "      <td>7/1/2017 0:45</td>\n",
       "      <td>3018</td>\n",
       "      <td>34.043732</td>\n",
       "      <td>-118.260139</td>\n",
       "      <td>3018</td>\n",
       "      <td>34.043732</td>\n",
       "      <td>-118.260139</td>\n",
       "      <td>5996</td>\n",
       "      <td>0</td>\n",
       "      <td>Round Trip</td>\n",
       "      <td>Walk-up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33404950</td>\n",
       "      <td>13</td>\n",
       "      <td>7/1/2017 0:10</td>\n",
       "      <td>7/1/2017 0:23</td>\n",
       "      <td>3055</td>\n",
       "      <td>34.044159</td>\n",
       "      <td>-118.251579</td>\n",
       "      <td>3082</td>\n",
       "      <td>34.046520</td>\n",
       "      <td>-118.237411</td>\n",
       "      <td>5777</td>\n",
       "      <td>0</td>\n",
       "      <td>One Way</td>\n",
       "      <td>Walk-up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33404947</td>\n",
       "      <td>34</td>\n",
       "      <td>7/1/2017 0:11</td>\n",
       "      <td>7/1/2017 0:45</td>\n",
       "      <td>3018</td>\n",
       "      <td>34.043732</td>\n",
       "      <td>-118.260139</td>\n",
       "      <td>3018</td>\n",
       "      <td>34.043732</td>\n",
       "      <td>-118.260139</td>\n",
       "      <td>6342</td>\n",
       "      <td>0</td>\n",
       "      <td>Round Trip</td>\n",
       "      <td>Walk-up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33404948</td>\n",
       "      <td>34</td>\n",
       "      <td>7/1/2017 0:11</td>\n",
       "      <td>7/1/2017 0:45</td>\n",
       "      <td>3018</td>\n",
       "      <td>34.043732</td>\n",
       "      <td>-118.260139</td>\n",
       "      <td>3018</td>\n",
       "      <td>34.043732</td>\n",
       "      <td>-118.260139</td>\n",
       "      <td>6478</td>\n",
       "      <td>0</td>\n",
       "      <td>Round Trip</td>\n",
       "      <td>Walk-up</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33404949</td>\n",
       "      <td>12</td>\n",
       "      <td>7/1/2017 0:11</td>\n",
       "      <td>7/1/2017 0:23</td>\n",
       "      <td>3055</td>\n",
       "      <td>34.044159</td>\n",
       "      <td>-118.251579</td>\n",
       "      <td>3082</td>\n",
       "      <td>34.046520</td>\n",
       "      <td>-118.237411</td>\n",
       "      <td>6411</td>\n",
       "      <td>0</td>\n",
       "      <td>One Way</td>\n",
       "      <td>Walk-up</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    trip_id  duration     start_time       end_time  start_station  start_lat  \\\n",
       "0  33404951        36  7/1/2017 0:09  7/1/2017 0:45           3018  34.043732   \n",
       "1  33404950        13  7/1/2017 0:10  7/1/2017 0:23           3055  34.044159   \n",
       "2  33404947        34  7/1/2017 0:11  7/1/2017 0:45           3018  34.043732   \n",
       "3  33404948        34  7/1/2017 0:11  7/1/2017 0:45           3018  34.043732   \n",
       "4  33404949        12  7/1/2017 0:11  7/1/2017 0:23           3055  34.044159   \n",
       "\n",
       "    start_lon  end_station    end_lat     end_lon  bike_id  plan_duration  \\\n",
       "0 -118.260139         3018  34.043732 -118.260139     5996              0   \n",
       "1 -118.251579         3082  34.046520 -118.237411     5777              0   \n",
       "2 -118.260139         3018  34.043732 -118.260139     6342              0   \n",
       "3 -118.260139         3018  34.043732 -118.260139     6478              0   \n",
       "4 -118.251579         3082  34.046520 -118.237411     6411              0   \n",
       "\n",
       "  trip_route_category passholder_type  \n",
       "0          Round Trip         Walk-up  \n",
       "1             One Way         Walk-up  \n",
       "2          Round Trip         Walk-up  \n",
       "3          Round Trip         Walk-up  \n",
       "4             One Way         Walk-up  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First five rows of LA Metro bike_sharing_data 2016Q3-2021Q3\n",
    "bike_sharing_data = pd.read_csv('./Dataset/metro-bike-share-trips-2017-q3.csv')\n",
    "bike_sharing_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f948b61",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}