bikes2020.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The Data is  Bike-Share Navigate\n",
    "- data contain all months in 2020\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import all data from first month to the last month \n",
    "bikes_q1 = pd.read_csv(\"bikes_2020_1_4.csv\")\n",
    "bikes_5 = pd.read_csv(\"bikes_2020_5.csv\")\n",
    "bikes_6 = pd.read_csv(\"bikes_2020_6.csv\")\n",
    "bikes_7 = pd.read_csv(\"bikes_2020_7.csv\")\n",
    "bikes_8 = pd.read_csv(\"bikes_2020_8.csv\")\n",
    "bikes_9 = pd.read_csv(\"bikes_2020_9.csv\")\n",
    "bikes_10 = pd.read_csv(\"bikes_2020_10.csv\")\n",
    "bikes_11= pd.read_csv(\"bikes_2020_11.csv\")\n",
    "bikes_12 = pd.read_csv(\"bikes_2020_12.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(426887, 13)\n",
      "(200274, 13)\n",
      "(343005, 13)\n",
      "(551480, 13)\n",
      "(622361, 13)\n",
      "(532958, 13)\n",
      "(388653, 13)\n",
      "(259716, 13)\n",
      "(131573, 13)\n"
     ]
    }
   ],
   "source": [
    "# using .shape function to get the rows and columns\n",
    "print(bikes_q1.shape)\n",
    "print(bikes_5.shape)\n",
    "print(bikes_6.shape)\n",
    "print(bikes_7.shape)\n",
    "print(bikes_8.shape)\n",
    "print(bikes_9.shape)\n",
    "print(bikes_10.shape)\n",
    "print(bikes_11.shape)\n",
    "print(bikes_12.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sum_all_rows:  3456907\n"
     ]
    }
   ],
   "source": [
    "#all column should be 3 \n",
    "#let's sum all rows \n",
    "sum_all_rows = bikes_q1.shape[0]+bikes_5.shape[0]+bikes_6.shape[0]+bikes_7.shape[0]+bikes_8.shape[0]\\\n",
    "+bikes_9.shape[0]+bikes_10.shape[0]+bikes_11.shape[0]+bikes_12.shape[0]\n",
    "print(\"sum_all_rows: \",sum_all_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#add all months togher\n",
    "df_bikes_2020 = bikes_q1.append(bikes_5,ignore_index=True)\n",
    "df_bikes_2020 = df_bikes_2020.append(bikes_6,ignore_index=True)\n",
    "df_bikes_2020 = df_bikes_2020.append(bikes_7,ignore_index=True)\n",
    "df_bikes_2020 = df_bikes_2020.append(bikes_8,ignore_index=True)\n",
    "df_bikes_2020 = df_bikes_2020.append(bikes_9,ignore_index=True)\n",
    "df_bikes_2020 = df_bikes_2020.append(bikes_10,ignore_index=True)\n",
    "df_bikes_2020 = df_bikes_2020.append(bikes_11,ignore_index=True)\n",
    "df_bikes_2020 = df_bikes_2020.append(bikes_12,ignore_index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "\n",
      "IF True that's okay we did it will\n"
     ]
    }
   ],
   "source": [
    "# check if all rows equal to sum of all rows\n",
    "print(df_bikes_2020.shape[0]==sum_all_rows)\n",
    "print()\n",
    "print(\"IF True that's okay we did it will\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3456907 entries, 0 to 3456906\n",
      "Data columns (total 13 columns):\n",
      " #   Column              Dtype  \n",
      "---  ------              -----  \n",
      " 0   ride_id             object \n",
      " 1   rideable_type       object \n",
      " 2   started_at          object \n",
      " 3   ended_at            object \n",
      " 4   start_station_name  object \n",
      " 5   start_station_id    object \n",
      " 6   end_station_name    object \n",
      " 7   end_station_id      object \n",
      " 8   start_lat           float64\n",
      " 9   start_lng           float64\n",
      " 10  end_lat             float64\n",
      " 11  end_lng             float64\n",
      " 12  member_casual       object \n",
      "dtypes: float64(4), object(9)\n",
      "memory usage: 342.9+ MB\n"
     ]
    }
   ],
   "source": [
    "#using .info to get all information about the data\n",
    "df_bikes_2020.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# started_at ,ended_at  should be datatime needs to be converted\n",
    "df_bikes_2020['started_at'] = pd.to_datetime(df_bikes_2020['started_at'])\n",
    "df_bikes_2020['ended_at'] = pd.to_datetime(df_bikes_2020['ended_at'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3456907 entries, 0 to 3456906\n",
      "Data columns (total 13 columns):\n",
      " #   Column              Dtype         \n",
      "---  ------              -----         \n",
      " 0   ride_id             object        \n",
      " 1   rideable_type       object        \n",
      " 2   started_at          datetime64[ns]\n",
      " 3   ended_at            datetime64[ns]\n",
      " 4   start_station_name  object        \n",
      " 5   start_station_id    object        \n",
      " 6   end_station_name    object        \n",
      " 7   end_station_id      object        \n",
      " 8   start_lat           float64       \n",
      " 9   start_lng           float64       \n",
      " 10  end_lat             float64       \n",
      " 11  end_lng             float64       \n",
      " 12  member_casual       object        \n",
      "dtypes: datetime64[ns](2), float64(4), object(7)\n",
      "memory usage: 342.9+ MB\n"
     ]
    }
   ],
   "source": [
    "df_bikes_2020.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check if there a duplicated values\n",
    "sum(df_bikes_2020.duplicated())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id                    0\n",
       "rideable_type              0\n",
       "started_at                 0\n",
       "ended_at                   0\n",
       "start_station_name     94656\n",
       "start_station_id       95282\n",
       "end_station_name      110782\n",
       "end_station_id        111243\n",
       "start_lat                  0\n",
       "start_lng                  0\n",
       "end_lat                 4156\n",
       "end_lng                 4156\n",
       "member_casual              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for null values\n",
    "df_bikes_2020.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drope all null values\n",
    "df_bikes_2020.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ride_id               0\n",
       "rideable_type         0\n",
       "started_at            0\n",
       "ended_at              0\n",
       "start_station_name    0\n",
       "start_station_id      0\n",
       "end_station_name      0\n",
       "end_station_id        0\n",
       "start_lat             0\n",
       "start_lng             0\n",
       "end_lat               0\n",
       "end_lng               0\n",
       "member_casual         0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check again if there any null values\n",
    "df_bikes_2020.isna().sum()"
   ]
  },
  {
   "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>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>EACB19130B0CDA4A</td>\n",
       "      <td>docked_bike</td>\n",
       "      <td>2020-01-21 20:06:59</td>\n",
       "      <td>2020-01-21 20:14:30</td>\n",
       "      <td>Western Ave &amp; Leland Ave</td>\n",
       "      <td>239</td>\n",
       "      <td>Clark St &amp; Leland Ave</td>\n",
       "      <td>326</td>\n",
       "      <td>41.9665</td>\n",
       "      <td>-87.6884</td>\n",
       "      <td>41.9671</td>\n",
       "      <td>-87.6674</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8FED874C809DC021</td>\n",
       "      <td>docked_bike</td>\n",
       "      <td>2020-01-30 14:22:39</td>\n",
       "      <td>2020-01-30 14:26:22</td>\n",
       "      <td>Clark St &amp; Montrose Ave</td>\n",
       "      <td>234</td>\n",
       "      <td>Southport Ave &amp; Irving Park Rd</td>\n",
       "      <td>318</td>\n",
       "      <td>41.9616</td>\n",
       "      <td>-87.6660</td>\n",
       "      <td>41.9542</td>\n",
       "      <td>-87.6644</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>789F3C21E472CA96</td>\n",
       "      <td>docked_bike</td>\n",
       "      <td>2020-01-09 19:29:26</td>\n",
       "      <td>2020-01-09 19:32:17</td>\n",
       "      <td>Broadway &amp; Belmont Ave</td>\n",
       "      <td>296</td>\n",
       "      <td>Wilton Ave &amp; Belmont Ave</td>\n",
       "      <td>117</td>\n",
       "      <td>41.9401</td>\n",
       "      <td>-87.6455</td>\n",
       "      <td>41.9402</td>\n",
       "      <td>-87.6530</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>C9A388DAC6ABF313</td>\n",
       "      <td>docked_bike</td>\n",
       "      <td>2020-01-06 16:17:07</td>\n",
       "      <td>2020-01-06 16:25:56</td>\n",
       "      <td>Clark St &amp; Randolph St</td>\n",
       "      <td>51</td>\n",
       "      <td>Fairbanks Ct &amp; Grand Ave</td>\n",
       "      <td>24</td>\n",
       "      <td>41.8846</td>\n",
       "      <td>-87.6319</td>\n",
       "      <td>41.8918</td>\n",
       "      <td>-87.6206</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>943BC3CBECCFD662</td>\n",
       "      <td>docked_bike</td>\n",
       "      <td>2020-01-30 08:37:16</td>\n",
       "      <td>2020-01-30 08:42:48</td>\n",
       "      <td>Clinton St &amp; Lake St</td>\n",
       "      <td>66</td>\n",
       "      <td>Wells St &amp; Hubbard St</td>\n",
       "      <td>212</td>\n",
       "      <td>41.8856</td>\n",
       "      <td>-87.6418</td>\n",
       "      <td>41.8899</td>\n",
       "      <td>-87.6343</td>\n",
       "      <td>member</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ride_id rideable_type          started_at            ended_at  \\\n",
       "0  EACB19130B0CDA4A   docked_bike 2020-01-21 20:06:59 2020-01-21 20:14:30   \n",
       "1  8FED874C809DC021   docked_bike 2020-01-30 14:22:39 2020-01-30 14:26:22   \n",
       "2  789F3C21E472CA96   docked_bike 2020-01-09 19:29:26 2020-01-09 19:32:17   \n",
       "3  C9A388DAC6ABF313   docked_bike 2020-01-06 16:17:07 2020-01-06 16:25:56   \n",
       "4  943BC3CBECCFD662   docked_bike 2020-01-30 08:37:16 2020-01-30 08:42:48   \n",
       "\n",
       "         start_station_name start_station_id                end_station_name  \\\n",
       "0  Western Ave & Leland Ave              239           Clark St & Leland Ave   \n",
       "1   Clark St & Montrose Ave              234  Southport Ave & Irving Park Rd   \n",
       "2    Broadway & Belmont Ave              296        Wilton Ave & Belmont Ave   \n",
       "3    Clark St & Randolph St               51        Fairbanks Ct & Grand Ave   \n",
       "4      Clinton St & Lake St               66           Wells St & Hubbard St   \n",
       "\n",
       "  end_station_id  start_lat  start_lng  end_lat  end_lng member_casual  \n",
       "0            326    41.9665   -87.6884  41.9671 -87.6674        member  \n",
       "1            318    41.9616   -87.6660  41.9542 -87.6644        member  \n",
       "2            117    41.9401   -87.6455  41.9402 -87.6530        member  \n",
       "3             24    41.8846   -87.6319  41.8918 -87.6206        member  \n",
       "4            212    41.8856   -87.6418  41.8899 -87.6343        member  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bikes_2020.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# calcualte the minutes for every ride\n",
    "df_bikes_2020['ride_length'] = df_bikes_2020['ended_at'].dt.minute - df_bikes_2020['started_at'].dt.minute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add a column for day name\n",
    "df_bikes_2020['day_name'] = df_bikes_2020['started_at'].dt.day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add start and end stations name together\n",
    "df_bikes_2020['startName_endName'] = df_bikes_2020['start_station_name']+\":\"+df_bikes_2020['end_station_name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bikes_2020.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}