Project_One_DataCleaningAndFormatting.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Dependencies and Setup\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Read in the data\n",
    "# Data files\n",
    "ride_data_orig = \"Resources/Metro_Bike_Share_Trip_Data.csv\"\n",
    "ride_data_17_Q2 = \"Resources/la_metro_gbfs_trips_Q2_2017.csv\"\n",
    "ride_data_17_Q3 = \"Resources/metro-bike-share-trips-2017-q3.csv\"\n",
    "ride_data_17_Q4 = \"Resources/metro-bike-share-trips-2017-q4-v2.csv\"\n",
    "ride_data_18_Q1 = \"Resources/metro-bike-share-trips-2018-q1.csv\"\n",
    "\n",
    "# Create dataframes\n",
    "ride_orig_df = pd.read_csv(ride_data_orig, low_memory=False)\n",
    "ride_17_Q2_df = pd.read_csv(ride_data_17_Q2, low_memory=False)\n",
    "ride_17_Q3_df = pd.read_csv(ride_data_17_Q3, low_memory=False)\n",
    "ride_17_Q4_df = pd.read_csv(ride_data_17_Q4, low_memory=False)\n",
    "ride_18_Q1_df = pd.read_csv(ride_data_18_Q1, low_memory=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Datetime data after Q1 2017 is not zero padded and it has to be zero padded\n",
    "def datetime_reformat(dt_str):\n",
    "    # seperate date and time\n",
    "    dt_strings = dt_str.split()\n",
    "    date_str = dt_strings[0]\n",
    "    time_str = dt_strings[1]\n",
    "    \n",
    "    # split up the date field\n",
    "    date_fields = date_str.split(\"/\")\n",
    "    \n",
    "    # do the formatting date\n",
    "    if (len(date_str) == 6):\n",
    "        # add zero to month and date\n",
    "        new_date = \"0\" + date_fields[0] + \"/0\" + date_fields[1] + \"/\" + date_fields[2]\n",
    "    elif (len(date_str) == 7):\n",
    "        if (len(date_fields[0]) == 2):\n",
    "            # add zero to day\n",
    "            new_date = date_fields[0] + \"/0\" + date_fields[1] + \"/\" + date_fields[2]\n",
    "        else:\n",
    "            # add zero to month\n",
    "            new_date = \"0\" + date_fields[0] + \"/\" + date_fields[1] + \"/\" + date_fields[2]\n",
    "    else:\n",
    "        new_date = date_str\n",
    "    \n",
    "    # do the formatting time\n",
    "    if (len(time_str) == 4):\n",
    "        # add zero to hour\n",
    "        new_time = \"0\" + time_str\n",
    "    else:\n",
    "        new_time = time_str\n",
    "        \n",
    "    # final string\n",
    "    new_str = new_date + \" \" + new_time\n",
    "    \n",
    "    return new_str\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Rename start time column so it is consistant between data files\n",
    "# For this inital analysis, the start time is used for every plot (both date and time)\n",
    "ride_orig_df.rename(columns={'Start Time':'start_time'}, inplace=True)\n",
    "\n",
    "# Drop columns that aren't needed (leaving some for future analysis)\n",
    "# Note (Starting Lat-Long and Ending Lat-Long) do not exist in the newer data\n",
    "ride_orig_df.drop(['Starting Station Latitude', 'Starting Station Longitude', 'Ending Station Latitude',\n",
    "                   'Ending Station Longitude','Starting Lat-Long', 'Ending Lat-Long'], axis=1, inplace=True)\n",
    "ride_17_Q2_df.drop(['start_lat', 'start_lon', 'end_lat', 'end_lon'], axis=1, inplace=True)\n",
    "ride_17_Q3_df.drop(['start_lat', 'start_lon', 'end_lat', 'end_lon'], axis=1, inplace=True)\n",
    "ride_17_Q4_df.drop(['start_lat', 'start_lon', 'end_lat', 'end_lon'], axis=1, inplace=True)\n",
    "ride_18_Q1_df.drop(['start_lat', 'start_lon', 'end_lat', 'end_lon'], axis=1, inplace=True)\n",
    "\n",
    "# Drop rows that have any NaN values\n",
    "ride_orig_df = ride_orig_df.dropna()\n",
    "ride_17_Q2_df = ride_17_Q2_df.dropna()\n",
    "ride_17_Q3_df = ride_17_Q3_df.dropna()\n",
    "ride_17_Q4_df = ride_17_Q4_df.dropna()\n",
    "ride_18_Q1_df = ride_18_Q1_df.dropna()\n",
    "\n",
    "# Converting start time to datetime object in the original data\n",
    "ride_orig_df['start_time'] = pd.to_datetime(ride_orig_df['start_time'], format='%m/%d/%Y %I:%M:%S %p')\n",
    "\n",
    "# The format of the start date and time changed significantly from the original data file\n",
    "# to the newer ones (17Q2-18Q1). Basically, the original datetime was zero padded, the new\n",
    "# data wasn't. Converting the date and time into a datetime object was simple for the original\n",
    "# The later data had to be zero padded first.\n",
    "#\n",
    "# From https://stackoverflow.com/questions/41191365/python-datetime-strptime-error-is-a-bad-directive-in-format-m-d-y-h\n",
    "# The use of %-m (for non-zero padded month value), will not work on a platform that dosn't have the\n",
    "# proper GNU strftime C library function installed. Or from the Python datetime module documentation, the format\n",
    "# codes that the C standard (1989 version) supports does not include %-m (and the others with a - indicating no\n",
    "# zero padding). The 1999 version of the C standard added additional format codes. Interpreting these codes is not\n",
    "# part of Python.\n",
    "\n",
    "# Converting the newer data date/time strings\n",
    "# Setup to use datetime_reformat function\n",
    "v_format = np.vectorize(datetime_reformat)\n",
    "\n",
    "# Format\n",
    "ride_17_Q2_df['start_time'] = v_format(ride_17_Q2_df.start_time)\n",
    "ride_17_Q3_df['start_time'] = v_format(ride_17_Q3_df.start_time)\n",
    "ride_17_Q4_df['start_time'] = v_format(ride_17_Q4_df.start_time)\n",
    "ride_18_Q1_df['start_time'] = v_format(ride_18_Q1_df.start_time)\n",
    "\n",
    "# Now that its zero-padded, convert to datetime object\n",
    "ride_17_Q2_df['start_time'] = pd.to_datetime(ride_17_Q2_df['start_time'], format='%m/%d/%y %H:%M')\n",
    "ride_17_Q3_df['start_time'] = pd.to_datetime(ride_17_Q3_df['start_time'], format='%m/%d/%y %H:%M')\n",
    "ride_17_Q4_df['start_time'] = pd.to_datetime(ride_17_Q4_df['start_time'], format='%m/%d/%y %H:%M')\n",
    "ride_18_Q1_df['start_time'] = pd.to_datetime(ride_18_Q1_df['start_time'], format='%m/%d/%y %H:%M')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the 2016 and 2017 Quarters from the original file\n",
    "\n",
    "# Add Year, Month, and Day columns to the original data file\n",
    "ride_orig_df['Year'] = ride_orig_df['start_time'].dt.year\n",
    "ride_orig_df['Month'] = ride_orig_df['start_time'].dt.month\n",
    "\n",
    "# Get 2016 data (should already have new columns)\n",
    "ride_orig_2016_df = ride_orig_df.loc[ride_orig_df['Year'] == 2016]\n",
    "\n",
    "# Get Months\n",
    "ride_16_Q3_7 = ride_orig_2016_df.loc[ride_orig_2016_df['Month'] == 7]\n",
    "ride_16_Q3_8 = ride_orig_2016_df.loc[ride_orig_2016_df['Month'] == 8]\n",
    "ride_16_Q3_9 = ride_orig_2016_df.loc[ride_orig_2016_df['Month'] == 9]\n",
    "ride_16_Q4_10 = ride_orig_2016_df.loc[ride_orig_2016_df['Month'] == 10]\n",
    "ride_16_Q4_11 = ride_orig_2016_df.loc[ride_orig_2016_df['Month'] == 11]\n",
    "ride_16_Q4_12 = ride_orig_2016_df.loc[ride_orig_2016_df['Month'] == 12]\n",
    "\n",
    "# Create quarters dataframes\n",
    "frames = [ride_16_Q3_7, ride_16_Q3_8, ride_16_Q3_9]\n",
    "ride_16_Q3_df = pd.concat(frames, ignore_index=True, sort=False).copy()\n",
    "\n",
    "frames = [ride_16_Q4_10, ride_16_Q4_11, ride_16_Q4_12]\n",
    "ride_16_Q4_df = pd.concat(frames, ignore_index=True, sort=False).copy()\n",
    "\n",
    "# Get the 2017 Q1 data (the 2017 data is from 1/1 to 3/31 so all 2017 is Q1)\n",
    "ride_17_Q1_df = ride_orig_df.loc[ride_orig_df['Year'] == 2017].copy()\n"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}