Process_DC_Uber_Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import cv2\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "%matplotlib inline\n",
    "import glob\n",
    "import csv\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def extract_geodata(data,colname,new_colname_longitude,new_colname_latitude):\n",
    "    \"\"\"\n",
    "    This function read all the longitudes and latitudes of the origin/destination (as specified) and store in two new columns.\n",
    "    Instead of writing two functions to reach the origin and destination geometry data respectively, I just wrote one\n",
    "    to be used in both scenario\n",
    "    data: the Uber Movement Dataset\n",
    "    colname: the name of the column that store the geodata: 'Origin Geometry' / 'Desination Geometry'\n",
    "    new_colname_longitude: the name for the new column that stores the extracted longitude information\n",
    "    new_colname_latitude: the name for the new column that stores the extracted latitude information\n",
    "    \"\"\"\n",
    "    rows_longitudes = [] # Store the longitude info of each row\n",
    "    rows_latitudes = [] # Store the latitude info of each row\n",
    "\n",
    "    # Read the Geometry data from each line\n",
    "    for line in data[colname]: # The index is subject to change when we merged multiple dataset together\n",
    "        longitudes = [] # Record the longitudes in each row\n",
    "        latitudes = [] # Record the latitudes in each row\n",
    "\n",
    "        array = re.split(r'[|[|,|]',line) # Split the cell contents\n",
    "        # First two components are negligible, every three units (as a pack) after contain: longitutde, latitutde, and null. \n",
    "        # Each latitude contains a ']' in the end\n",
    "        # The last latitutde contains 2 ']'s\n",
    "        # The last pack contains only two units: a longitude, and a latitutde\n",
    "        total_coords = int((len(array)-2+1)/3); # Find the total number of pairs of longitudes and latitudes\n",
    "        for i in range(0,total_coords): # Loop until the second last \n",
    "            longitudes.append(float(array[i*3+2]))\n",
    "            latitude_pieces = array[i*3+3].split(']')\n",
    "            latitudes.append(float(latitude_pieces[0]))\n",
    "        rows_longitudes.append(longitudes)\n",
    "        rows_latitudes.append(latitudes)\n",
    "    data[new_colname_longitude] = rows_longitudes # Add one more column for destination longitudes\n",
    "    data[new_colname_latitude]= rows_latitudes # Add one more column for destination latitudes\n",
    "    \n",
    "    # To read the first destination longitude of the first row:\n",
    "    # data['dest_longtitudes'][0][0]\n",
    "    return data\n",
    "\n",
    "def extract_time_data(data):\n",
    "    \"\"\"\n",
    "    This function extract the time data including the Months, Day of Week, and Hour of Day\n",
    "    \"\"\"\n",
    "    Months = []\n",
    "    Weekdays = []\n",
    "    Times = []\n",
    "    for line in data['Date Range']:\n",
    "        array = line.split(',')\n",
    "        Month = array[0]\n",
    "        Weekday = array[1]\n",
    "        Time = array[2]\n",
    "\n",
    "        Months.append(Month)\n",
    "        Weekdays.append(Weekday)\n",
    "        Times.append(Time)\n",
    "    data['Months']= Months\n",
    "    data['Weekdays'] = Weekdays\n",
    "    data['Times'] = Times\n",
    "    return data\n",
    "\n",
    "def measure_geo_distance(pt1_longitude,pt1_latitude,pt2_longtide,pt2_latitude):\n",
    "    \"\"\"\n",
    "    This function compute the geo_distance between two points using Euclidean distance\n",
    "    \"\"\"\n",
    "    distance = ((pt1_longitude-pt2_longtide)**2+(pt1_latitude-pt2_latitude)**2)**0.5\n",
    "    return distance\n",
    "\n",
    "def find_centroid(data_uber, data_regions):\n",
    "    \"\"\"\n",
    "    This function find the centroid that is the closest to the origin geometry in the given uber dataset\n",
    "    \"\"\"\n",
    "    # Get the geodata of all the origins geodata in the given dataset\n",
    "    # The origins in every row in the given dataset are the same, so we can just use the first row\n",
    "    data_uber_longitude = data_uber['Origin Longitude'][1]\n",
    "    data_uber_latitude = data_uber['Origin Latitude'][1]\n",
    "    num_origins = len(data_uber_longitude) # Number of origin coordinates\n",
    "\n",
    "    # Measure the distance between all the origin coordinates and each of the centroids, find the average\n",
    "    avg_distance_origins_centroids = [] # Initialize the list to record the average distance between the origins and centroids\n",
    "    # Iterate through each centroid\n",
    "    for i in range(0,len(data_regions)): \n",
    "        # Read the Geodata of each centroid\n",
    "        region_latitude = data_regions[\"Latitude\"][i] # Read the latitude\n",
    "        region_longitude = data_regions[\"Longitude\"][i] # Read the longitude\n",
    "        # region_radius = data_regions[\"Radius\"][i] # Read the Radius\n",
    "\n",
    "        # Measure the Distance between each origin coordiante with the centroid\n",
    "        distances = []\n",
    "        for j in range(0,num_origins):\n",
    "            #distance = ((data_uber_longitude[j]-region_longitude)**2+(data_uber_latitude[j]-region_latitude)**2)**0.5\n",
    "            distance = measure_geo_distance(data_uber_longitude[j],data_uber_latitude[j],region_longitude,region_latitude)\n",
    "            distances.append(distance)\n",
    "        avg_distance_origins_centroids.append(sum(distances)/len(distances))\n",
    "\n",
    "    # Find the minimum average distance between the origins and the centroids\n",
    "    min_distance = min(avg_distance_origins_centroids)\n",
    "    # Select the centroid that is the distance is cooresponding to, used that as our desired centroid\n",
    "    # Note: by using argmin, we get the index of the centroids on the dataset, and the index starts with 0\n",
    "    closest_centroid = np.argmin(avg_distance_origins_centroids)\n",
    "    return min_distance,closest_centroid\n",
    "\n",
    "def find_dest_zones_in_region(data_uber,data_regions,closest_centroid,threshold = 1):\n",
    "    \"\"\"\n",
    "    This function find the indices of destination zones that fall into the selected region\n",
    "    data_uber: one uber movement dataset - represent all the info of trips from one zone in a given time\n",
    "    data_regions: the dataset storing all the centroids info\n",
    "    closest_centroid: the index of the closest centroid in the data_region\n",
    "    threshold: We only select the destination destrict that have enough percentage of coordinates that fall into the region\n",
    "    \"\"\"\n",
    "    # Find the geodata of the closest centroid\n",
    "    region_latitude = data_regions[\"Latitude\"][closest_centroid] # Read the latitude\n",
    "    region_longitude = data_regions[\"Longitude\"][closest_centroid] # Read the longitude\n",
    "    region_radius = data_regions[\"Radius\"][closest_centroid] # Read the Radius\n",
    "\n",
    "    # Initialize the list to record the percentage\n",
    "    # in_region_percentage_list = []\n",
    "    \n",
    "    # Initialize the list to record the index of the destination district that meet the requirement (fall in the region)\n",
    "    in_region_destination_list = []\n",
    "    #distance_list = []\n",
    "\n",
    "    # Loop through each destination district (each row)\n",
    "    for i in range(0,len(data_uber)):\n",
    "        dest_longitudes = data_uber['Destination Longitude'][i]\n",
    "        dest_latitudes = data_uber['Destination Latitude'][i]\n",
    "        num_coords = len(dest_longitudes) # Number of origin coordinates\n",
    "        distances = [] \n",
    "        in_region_count = 0 # Reset the count of coordiantes that fall into the region\n",
    "        # Loop through each coordinates of the destination district\n",
    "        for j in range(0,num_coords):\n",
    "            # Compare the distance of the coordinate and the centroid\n",
    "            distance = measure_geo_distance(dest_longitudes[j],dest_latitudes[j],region_longitude,region_latitude)\n",
    "            # distances.append(distance)\n",
    "            # count if the coordinate fall into the region\n",
    "            if distance <= region_radius:\n",
    "                in_region_count = in_region_count + 1\n",
    "            # Store the percentage of the coordinates that fall into the region\n",
    "        # If the percentage of the coordinates that fall into the region exceeds the threshold, record the index of the district\n",
    "        if in_region_count/num_coords >= threshold:\n",
    "            in_region_destination_list.append(i)\n",
    "        #in_region_percentage_list.append(in_region_count/num_coords)\n",
    "        #distance_list.append(distances)\n",
    "    return in_region_destination_list\n",
    "\n",
    "def compute_variability(data,destination_list):\n",
    "    \"\"\"\n",
    "    This function compute the trip duration variability: mean/range.\n",
    "    data: a given uber movement dataset as an input\n",
    "    destination_list: a list of indices of the destination that fall into the target regions\n",
    "    \"\"\"\n",
    "    # Initialize the list to record the variability\n",
    "    variability_list = []\n",
    "    mean_list = []\n",
    "    min_list = []\n",
    "    max_list = []\n",
    "\n",
    "    for i in destination_list:\n",
    "        mean = data['Mean Travel Time (Seconds)'][i]\n",
    "        lower_bound = data['Range - Lower Bound Travel Time (Seconds)'][i]\n",
    "        upper_bound = data['Range - Upper Bound Travel Time (Seconds)'][i]\n",
    "        variability_list.append(mean/(upper_bound-lower_bound))\n",
    "        mean_list.append(mean)\n",
    "        min_list.append(lower_bound)\n",
    "        max_list.append(upper_bound)\n",
    "    return variability_list,mean_list,min_list,max_list\n",
    "\n",
    "def find_avg_variability(data,destination_list):\n",
    "    \"\"\"\n",
    "    This function find a list of variabilities of trips to the destinations of interest, and compute the average\n",
    "    data: a given uber movement dataset as an input\n",
    "    destination_list: a list of indices of the destination that fall into the target regions\n",
    "    \"\"\"\n",
    "    variability_list,mean_list,min_list,max_list = compute_variability(data,destination_list)\n",
    "    avg_var = sum(variability_list)/len(variability_list) # Variability: defined as Range/Mean\n",
    "    avg_mean = sum(mean_list)/len(mean_list)\n",
    "    avg_min = sum(min_list)/len(min_list)\n",
    "    avg_max = sum(max_list)/len(max_list)\n",
    "    return avg_var, avg_mean, avg_min,avg_max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def process_pipeline(data_uber, data_region):\n",
    "    \"\"\"\n",
    "    This function set up an automated pipeline of the processes on the uber dataset to extract the useful information\n",
    "    \"\"\"\n",
    "    # Extract the Origin Information\n",
    "    data_after_extraction = extract_geodata(data_uber,'Origin Geometry','Origin Longitude','Origin Latitude')\n",
    "    # Extract the destination Information\n",
    "    data_after_extraction = extract_geodata(data_after_extraction,'Destination Geometry','Destination Longitude','Destination Latitude')\n",
    "    # Extract the time information\n",
    "    data_after_extraction = extract_time_data(data_after_extraction)\n",
    "\n",
    "    # Find the centroid that is closest to the origin district in the given dataset\n",
    "    [min_distance,closest_centroid] = find_centroid(data_after_extraction,data_regions)\n",
    "    centroid_longitude = data_regions[\"Longitude\"][closest_centroid]\n",
    "    centroid_latitude = data_regions[\"Latitude\"][closest_centroid]\n",
    "    # If the shortest distance between the origin district and the selected centroid is \n",
    "    # longer than the radius of the centroid, report wrong\n",
    "    if (min_distance > data_regions[\"Radius\"][closest_centroid]):\n",
    "        print(\"The origin is outside of the selected region!\")\n",
    "    \n",
    "    # Find the indices and number of destinations in the selected region\n",
    "    in_region_destination_list = find_dest_zones_in_region(data_after_extraction,data_regions,closest_centroid)\n",
    "    num_destinations_in_region = len(in_region_destination_list)\n",
    "    #data_in_region.ix[in_region_destination_list]\n",
    "\n",
    "    # Find Average Variability of the current data W.R.T. interested destination district\n",
    "    avg_var, avg_mean, avg_min,avg_max = find_avg_variability(data_after_extraction,in_region_destination_list)\n",
    "\n",
    "    # Get the Time Information\n",
    "    # Remove space from String\n",
    "    Weekday = data_after_extraction['Weekdays'][1].replace(' ', '')\n",
    "    Time = data_after_extraction['Times'][1].replace(' ', '')\n",
    "    return Weekday,Time,centroid_longitude,centroid_latitude,num_destinations_in_region,avg_var, avg_mean, avg_min,avg_max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def read_all_Uber_data():\n",
    "    \"\"\"\n",
    "    This function load all the Uber data in the folder and merge them together into one data frame\n",
    "    \"\"\"\n",
    "    # Read in all the dataset\n",
    "    data_file_paths = glob.glob(\"DC_Uber_Data/Uber_DC*\")\n",
    "    \n",
    "    all_data = []\n",
    "    for fname in data_file_paths: # Read file name\n",
    "        data= pd.read_csv(fname)\n",
    "        all_data.append(data)\n",
    "        # Find column names\n",
    "        # list(data)\n",
    "\n",
    "    # Merge all the datasets and store in one dataframe called data_merge\n",
    "    data_merge = all_data[0]\n",
    "    for i in range(1,len(all_data)):\n",
    "        next_data = all_data[i]\n",
    "        frames = [data_merge,next_data]\n",
    "        data_merge = pd.concat(frames)\n",
    "    return data_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Read the pre-defined region's data\n",
    "data_regions=pd.read_csv('Data/DC_Cab_Pickup/CoordinateWrenches.csv')\n",
    "\n",
    "# Set up the list of region name\n",
    "total_regions = 5\n",
    "region_list = []\n",
    "for i in range(0,total_regions):\n",
    "    region_list.append('R'+str(i+1))\n",
    "\n",
    "# Initialize the lists to record important info that will be included in the final table\n",
    "Region_Name_list = [] # Name of the region\n",
    "centroid_longitude_list = [] # Longitude of the centroid of the region\n",
    "centroid_latitude_list = [] # Latitude of the centroid of the region\n",
    "Weekday_list = [] # Day of the Week\n",
    "Time_list = [] # Time of the Day\n",
    "num_dest_in_region_list = [] # Number of destination districts that are included in the region\n",
    "avg_var_list = [] # Average Variability\n",
    "avg_mean_list= [] # Average Mean Trip Duration\n",
    "avg_min_list = [] # Average Min Trip Duration\n",
    "avg_max_list = [] # Average Max Trip Duration\n",
    "\n",
    "\n",
    "for region in region_list:\n",
    "    data_file_paths = glob.glob(\"Data/DC_Uber_Data/Uber_DC_\"+region + \"*\")\n",
    "    for file in data_file_paths:\n",
    "        data_uber = pd.read_csv(file) # Read Uber Data\n",
    "        [Weekday,Time,centroid_longitude,centroid_latitude,num_destinations_in_region,avg_var, avg_mean, avg_min,avg_max] = process_pipeline(data_uber, data_regions)\n",
    "        Region_Name_list.append(region) # Name of the region\n",
    "        centroid_longitude_list.append(centroid_longitude) # Longitude of the centroid of the region\n",
    "        centroid_latitude_list.append(centroid_latitude) # Latitude of the centroid of the region\n",
    "        Weekday_list.append(Weekday) # Day of the Week\n",
    "        Time_list.append(Time) # Time of the Day\n",
    "        num_dest_in_region_list.append(num_destinations_in_region) # Number of destination districts that are included in the region\n",
    "        avg_var_list.append(avg_var) # Average Variability\n",
    "        avg_mean_list.append(avg_mean) # Average Mean Trip Duration\n",
    "        avg_min_list.append(avg_min) # Average Min Trip Duration\n",
    "        avg_max_list.append(avg_max) # Average Max Trip Duration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create Dataframe\n",
    "variability_data_frame = pd.DataFrame(\n",
    "    {\n",
    "     'Average Variability':avg_var_list,\n",
    "     'Average Mean': avg_mean_list,\n",
    "     'Average Min': avg_min_list,\n",
    "     'Average Max': avg_max_list,\n",
    "     'Number_Destination_Zones_in_Region': num_dest_in_region_list,\n",
    "     'Time': Time_list, \n",
    "     'Weekday': Weekday_list,\n",
    "     'Centroid_Latitude': centroid_latitude_list,\n",
    "     'Centroid_Longitude': centroid_longitude_list,\n",
    "     'Region_Name': Region_Name_list\n",
    "    })\n",
    "\n",
    "# Save Dataframe\n",
    "variability_data_frame.to_csv(\"Data/Processed_Data/DC_Uber_Variability_Data.csv\",index=False)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}