2017-2020_ToMonthlyFiles.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>ID</th>\n",
       "      <th>Station Name</th>\n",
       "      <th>Total Docks</th>\n",
       "      <th>Docks in Service</th>\n",
       "      <th>Status</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1594046383808271024</td>\n",
       "      <td>Troy St &amp; Jackson Blvd</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>In Service</td>\n",
       "      <td>41.877505</td>\n",
       "      <td>-87.704850</td>\n",
       "      <td>(41.877505, -87.70485)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>641</td>\n",
       "      <td>Central Park Ave &amp; Bloomingdale Ave</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>In Service</td>\n",
       "      <td>41.914166</td>\n",
       "      <td>-87.716755</td>\n",
       "      <td>(41.914166, -87.716755)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1683527931525155814</td>\n",
       "      <td>Public Rack - Cornell Ave &amp; 87th Pl</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>In Service</td>\n",
       "      <td>41.736881</td>\n",
       "      <td>-87.583146</td>\n",
       "      <td>(41.73688127, -87.58314552)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>367</td>\n",
       "      <td>Racine Ave &amp; 35th St</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>In Service</td>\n",
       "      <td>41.830689</td>\n",
       "      <td>-87.656211</td>\n",
       "      <td>(41.83068856472101, -87.65621066093445)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1673852313397164648</td>\n",
       "      <td>Public Rack - Francisco Ave &amp; Touhy Ave</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>In Service</td>\n",
       "      <td>42.011865</td>\n",
       "      <td>-87.701317</td>\n",
       "      <td>(42.011865, -87.701317)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    ID                             Station Name  Total Docks  \\\n",
       "0  1594046383808271024                   Troy St & Jackson Blvd            9   \n",
       "1                  641      Central Park Ave & Bloomingdale Ave           11   \n",
       "2  1683527931525155814      Public Rack - Cornell Ave & 87th Pl            1   \n",
       "3                  367                     Racine Ave & 35th St           15   \n",
       "4  1673852313397164648  Public Rack - Francisco Ave & Touhy Ave            2   \n",
       "\n",
       "   Docks in Service      Status   Latitude  Longitude  \\\n",
       "0                 9  In Service  41.877505 -87.704850   \n",
       "1                11  In Service  41.914166 -87.716755   \n",
       "2                 1  In Service  41.736881 -87.583146   \n",
       "3                15  In Service  41.830689 -87.656211   \n",
       "4                 2  In Service  42.011865 -87.701317   \n",
       "\n",
       "                                  Location  \n",
       "0                   (41.877505, -87.70485)  \n",
       "1                  (41.914166, -87.716755)  \n",
       "2              (41.73688127, -87.58314552)  \n",
       "3  (41.83068856472101, -87.65621066093445)  \n",
       "4                  (42.011865, -87.701317)  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "stations = pd.read_csv(inDir +'Divvy_Bicycle_Stations.csv' )\n",
    "stations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "inDir = '/home/alp/Google Drive/Python/DataSets/Data_Divvy/CVS_archive/'\n",
    "\n",
    "inFile = 'Divvy_Trips_2019_Q4.csv'\n",
    "\n",
    "df = pd.read_csv(inDir + inFile)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 1:\n",
    "# change columns to new names\n",
    "df.rename(columns={df.columns[0]:'ride_id',\n",
    "                   df.columns[1]:'rideable_type'\n",
    "                   df.columns[1]:'started_at',\n",
    "                   df.columns[2]:'ended_at',\n",
    "                   df.columns[3]:'bikeid',\n",
    "                   df.columns[4]:'tripduration',\n",
    "                   df.columns[5]:'start_station_id',\n",
    "                   df.columns[6]:'start_station_name',\n",
    "                   df.columns[7]:'end_station_id',\n",
    "                   df.columns[8]:'end_station_name',\n",
    "                   df.columns[9]:'member_casual',\n",
    "                   df.columns[10]:'gender',\n",
    "                   df.columns[11]:'birthyear'\n",
    "                   },inplace=True)\n",
    "\n",
    "df['started_at'] = pd.to_datetime(df['started_at'],format = '%Y-%m-%d %H:%M:%S')\n",
    "df['ended_at'] = pd.to_datetime(df['started_at'],format = '%Y-%m-%d %H:%M:%S')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add lat/.ng columns\n",
    "df = pd.merge(df,stations[['ID','Latitude','Longitude']],'left', left_on = 'start_station_id',right_on = 'ID')\n",
    "df = pd.merge(df,stations[['ID','Latitude','Longitude']],'left',left_on = 'end_station_id',right_on = 'ID')\n",
    "df = df.drop(columns = ['ID_x','ID_y'])\n",
    "df = df.rename(columns={'Latitude_x':'start_lat','Longitude_x':'start_lng', 'Latitude_y':'end_lat','Longitude_y':'end_lng'})\n",
    "\n",
    "# add / change to recent data types\n",
    "df['rideable_type'] = 'classic_bike'\n",
    "df['member_casual'].replace(['Subscriber','Customer'],['member','casual'],inplace = True)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['month'] = pd.DatetimeIndex(df['started_at']).month\n",
    "months_list = df['month'].value_counts().index.to_list()\n",
    "for m in months_list:\n",
    "    temp = df[df['month'] == m]\n",
    "    temp = temp.drop('month',axis=1)\n",
    "    temp.to_csv(f\"{inDir}2020{m:02}-divvy-tripdata.csv\")\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For 2020_Q1, all that is needed is to split into monthly files -- file names and types are the same as 202004 and following..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "inFile = 'Divvy_Trips_2020_Q1.csv'\n",
    "\n",
    "df = pd.read_csv(inDir + inFile)\n",
    "\n",
    "df['started_at'] = pd.to_datetime(df['started_at'],format = '%Y-%m-%d %H:%M:%S')\n",
    "df['ended_at'] = pd.to_datetime(df['started_at'],format = '%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "df['month'] = pd.DatetimeIndex(df['started_at']).month\n",
    "months_list = df['month'].value_counts().index.to_list()\n",
    "for m in months_list:\n",
    "    temp = df[df['month'] == m]\n",
    "    temp = temp.drop('month',axis=1)\n",
    "    temp.to_csv(f\"{inDir}2020{m:02}-divvy-tripdata.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.10.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}