Sprint1_AlexanderChen.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sprint1 Alexander Chen\n",
    "\n",
    "### Central question:\n",
    "Can the data provided predict either:\n",
    "\n",
    "1.\tTires wearage\n",
    "2.\tBattery/alternator/electrics breakdown\n",
    "3.\tBrakes breakdown\n",
    "\n",
    "The main question to aswer is: *\"can we use any of the data generated parameters to predict one (or multiple) failures discussed above so the user is not surprised*\n",
    "\n",
    "Below the reader will find a list of the  parameters that *could* influence the seperate. \n",
    "\n",
    "**Possible decreases of Tyres:**\n",
    "- Elevation ( downhill / level / uphill )\n",
    "- Driving area  (city / high way / dirt road) \n",
    "- Driving style (aggressive acceleration  / aggressive braking  / aggressive turning)\n",
    "- Kilometers driven (numeric per session)\n",
    "- Tire type ( winter / summer )\n",
    "- Weather condition ( snow / wet / dry / hot)\n",
    "- Road condition ( potholes / smooth / gravel)\n",
    "\n",
    "\n",
    "- Load in the car (heavy load / normal load )# \n",
    "- Tire pressure at t = 0 (low / normal )#\n",
    "- Tire quality at t = 0 (new / fairly new / used / worn )#\n",
    "- Wheel aliment ( off alignment / aligned )#\n",
    "\n",
    "\n",
    "**Possible decreases of brakes:**\n",
    "- Kilometers driven (total per driving session)\n",
    "- Driving area (city / high way / dirt road) \n",
    "- Weather condition ( snow / wet / dry / hot)\n",
    "- Elevation ( downhill / level / uphill )\n",
    "- Duration of braking ( short / normal / long )\n",
    "- Intensity of braking ( light / normal / collision avoidance)\n",
    "\n",
    "\n",
    "- state of the brakes at t = 0 and periodic #\n",
    "- State of brake system at t = 0 and periodic #\n",
    "- Load in the car (heavy load / normal load )# \n",
    "- Wheel aliment ( off alignment / aligned ) #\n",
    "\n",
    "**Possible decreases of battery/alternator/electrical system**\n",
    "- Frequency of heavy loads on the battery (numerical)\n",
    "- Frequency of charging (numerical)\n",
    "- number of cycles per driving session (cycle = charging a battery)\n",
    "- Number of downwards dipping of voltage, bellow 11V.\n",
    "- Number of upward spike of voltage, above 15,5V.\n",
    "- Outside temperature.\n",
    "\n",
    "\n",
    "*#The reader might have noticed that some customer feedback is needed. For instance, in Tires we need to known what the actual quality is of the tires beforehand and periodically during the test, to understand the rate of decline. *\n",
    "\n",
    "\n",
    "\n",
    "The next step is to see which of the above parameters can be found in the actual data and which need to be found by a proxy (I am assuming that the parameters discussed above are indeed broad enough). \n",
    "I started with Tires since it seemed the most straight forward.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Elevation**:\n",
    "\n",
    "To test if elevation can have an effect on the usages of the tires, I could use either of these two parameters:\n",
    "\n",
    "-\tgps_altitude_phone\n",
    "- \taltitude\n",
    "\n",
    "bellow is a sample test to find out if the data is correlated. Which helps us get confidence in the condence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# will will need this a lot\n",
    "import pandas as pd\n",
    "\n",
    "#the proces ID to get into bigquery, unfortunatly the connection of bigquery cannot be shared since its an active Startup\n",
    "projectid = \"vast-art-******\"\n",
    "\n",
    "fleet = [ 'car1' , 'car2' , 'car3' , 'car4' , 'car5' , 'car6' , 'car7']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requesting query... ok.\n",
      "Job ID: job_kpT-1lDu_ot6CvSstl0l0KaGBdia\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 63793 rows.\n",
      "\n",
      "Total time taken 3.8 s.\n",
      "Finished at 2017-10-13 11:11:51.\n",
      "Requesting query... ok.\n",
      "Job ID: job_EGTw7V2xFd9KEfNaBhZirxqyPm89\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.6 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 63764 rows.\n",
      "\n",
      "Total time taken 3.62 s.\n",
      "Finished at 2017-10-13 11:11:56.\n",
      "Requesting query... ok.\n",
      "Job ID: job_IS-k-7v54Gxosxtnrrc_amkNOchu\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 14033 rows.\n",
      "\n",
      "Total time taken 1.62 s.\n",
      "Finished at 2017-10-13 11:11:59.\n",
      "Requesting query... ok.\n",
      "Job ID: job_-zZqMh5O_Owc1RsORLtjdBEUkO0B\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.6 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 14029 rows.\n",
      "\n",
      "Total time taken 1.79 s.\n",
      "Finished at 2017-10-13 11:12:02.\n",
      "Requesting query... ok.\n",
      "Job ID: job_bO_kJU4jDREL1UtHhr4hPlyvnxoq\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 58162 rows.\n",
      "\n",
      "Total time taken 3.38 s.\n",
      "Finished at 2017-10-13 11:12:07.\n",
      "Requesting query... ok.\n",
      "Job ID: job_QZhMZdRRquTh6Ia52lCVao13qEqj\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.6 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 58149 rows.\n",
      "\n",
      "Total time taken 3.4 s.\n",
      "Finished at 2017-10-13 11:12:11.\n",
      "Requesting query... ok.\n",
      "Job ID: job_oFCjBZzQvke_hh2p2mPXaPbC_9AR\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 1; 40% done. Elapsed 11.22 s.\n",
      "  Got page: 2; 80% done. Elapsed 14.98 s.\n",
      "  Got page: 3; 100% done. Elapsed 16.78 s.\n",
      "Got 248954 rows.\n",
      "\n",
      "Total time taken 18.65 s.\n",
      "Finished at 2017-10-13 11:12:31.\n",
      "Requesting query... ok.\n",
      "Job ID: job_MdDAU9bboo0DD895TluF12Igc6TX\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.6 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 1; 40% done. Elapsed 11.77 s.\n",
      "  Got page: 2; 80% done. Elapsed 15.14 s.\n",
      "  Got page: 3; 100% done. Elapsed 17.27 s.\n",
      "Got 248913 rows.\n",
      "\n",
      "Total time taken 19.02 s.\n",
      "Finished at 2017-10-13 11:12:51.\n",
      "Requesting query... ok.\n",
      "Job ID: job_5j-kEfGGZW8XMoUAL-tMF6HczsGw\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 68833 rows.\n",
      "\n",
      "Total time taken 3.76 s.\n",
      "Finished at 2017-10-13 11:12:56.\n",
      "Requesting query... ok.\n",
      "Job ID: job__jvuNuksBwz3VG91jtkSBPXnkLkH\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.6 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 68777 rows.\n",
      "\n",
      "Total time taken 3.8 s.\n",
      "Finished at 2017-10-13 11:13:01.\n",
      "Requesting query... ok.\n",
      "Job ID: job_R2yBIRuys_2a8eOf4Y0cCBHIt6-W\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 1; 95% done. Elapsed 9.37 s.\n",
      "  Got page: 2; 100% done. Elapsed 10.02 s.\n",
      "Got 105002 rows.\n",
      "\n",
      "Total time taken 10.72 s.\n",
      "Finished at 2017-10-13 11:13:13.\n",
      "Requesting query... ok.\n",
      "Job ID: job_5dgc2JFkw1wYrRO93k6G_T7vve63\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.6 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 1; 95% done. Elapsed 10.47 s.\n",
      "  Got page: 2; 100% done. Elapsed 10.98 s.\n",
      "Got 104976 rows.\n",
      "\n",
      "Total time taken 11.8 s.\n",
      "Finished at 2017-10-13 11:13:26.\n",
      "Requesting query... ok.\n",
      "Job ID: job_NiASIUXPl2ix3b7HJ9L9ytk9IMfl\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 1; 100% done. Elapsed 11.36 s.\n",
      "Got 99092 rows.\n",
      "\n",
      "Total time taken 12.07 s.\n",
      "Finished at 2017-10-13 11:13:39.\n",
      "Requesting query... ok.\n",
      "Job ID: job_5TGDrfZ4XMftnAFiOAWHp5Y-svWI\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 13.6 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 1; 100% done. Elapsed 8.81 s.\n",
      "Got 91039 rows.\n",
      "\n",
      "Total time taken 9.43 s.\n",
      "Finished at 2017-10-13 11:13:50.\n"
     ]
    }
   ],
   "source": [
    "correlation_altitude = []\n",
    "\n",
    "for vehicle in fleet:\n",
    "    #retrieving the altitude data from bigquery\n",
    "    altitude_df = pd.read_gbq(\"select timestamp,value from [headingon_params.altitude] where vehicle = '%s' \" %vehicle, projectid)\n",
    "    #retrieving the gps_altitude data from bigquery\n",
    "    gps_altitude_df = pd.read_gbq(\"select timestamp,value from [headingon_params.gps_altitude_phone] where vehicle = '%s' \" %vehicle, projectid)\n",
    "    \n",
    "    #adjust column name for clarity\n",
    "    altitude_df.rename (columns={'value': 'OBD_value'}, inplace=True)\n",
    "    gps_altitude_df.rename (columns={'value': 'GPS_value'}, inplace=True)\n",
    "    #calculate the correlation \n",
    "    correlation_value = altitude_df['OBD_value'].corr(gps_altitude_df['GPS_value'])\n",
    "    correlation_altitude.append({'vehicle': vehicle, 'correlation': correlation_value})\n",
    "    \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vehicle</th>\n",
       "      <th>correlation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>car1</td>\n",
       "      <td>0.9850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>car2</td>\n",
       "      <td>0.9991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>car3</td>\n",
       "      <td>0.9946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>car4</td>\n",
       "      <td>0.8321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>car5</td>\n",
       "      <td>0.9352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>car6</td>\n",
       "      <td>0.9686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>car7</td>\n",
       "      <td>0.0132</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  vehicle  correlation\n",
       "0    car1       0.9850\n",
       "1    car2       0.9991\n",
       "2    car3       0.9946\n",
       "3    car4       0.8321\n",
       "4    car5       0.9352\n",
       "5    car6       0.9686\n",
       "6    car7       0.0132"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#formating the values and tranforming it in a dataset\n",
    "pd.set_option('precision', 4)\n",
    "correlation_altitude_pf = pd.DataFrame(correlation_altitude)\n",
    "#changing the column sequence for easy of interpretation\n",
    "correlation_column = ['vehicle', 'correlation']\n",
    "correlation_altitude_pf[correlation_column]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I would expect an extremely high correclation of minimum **.90 percent**, however:\n",
    "\n",
    "* *car4*\n",
    "* *car7*\n",
    "\n",
    "Do not achieve this level of correlation. a closer inspection shows the following:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requesting query... ok.\n",
      "Job ID: job_iJPjgahIlJspsruoEXkCNZTm9pLI\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.03 s.\n",
      "Finished at 2017-10-13 11:15:34.\n",
      "Requesting query... ok.\n",
      "Job ID: job_Hp0ktlH9uHIMRUtAj5yHeMgnI517\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.97 s.\n",
      "Finished at 2017-10-13 11:15:36.\n",
      "Requesting query... ok.\n",
      "Job ID: job_EYGMUr0FLCAV2rVrP7V29ebY8zXh\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.93 s.\n",
      "Finished at 2017-10-13 11:15:38.\n",
      "Requesting query... ok.\n",
      "Job ID: job_Yf0tZiKUdUJi_3AXQg9ZytjqQYMX\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.15 s.\n",
      "Finished at 2017-10-13 11:15:40.\n",
      "Requesting query... ok.\n",
      "Job ID: job_QffpMtMF96P_bHnUUbDCbDQKFhoC\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.87 s.\n",
      "Finished at 2017-10-13 11:15:42.\n",
      "Requesting query... ok.\n",
      "Job ID: job_G9tDNVVH444F30wnLNlPgDqtzEtx\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.94 s.\n",
      "Finished at 2017-10-13 11:15:44.\n",
      "Requesting query... ok.\n",
      "Job ID: job_Z2P8qYxI43HTdomgLVO4z9Eh9WUA\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.87 s.\n",
      "Finished at 2017-10-13 11:15:46.\n",
      "Requesting query... ok.\n",
      "Job ID: job_V_YNqtEpj3W6p3XHeLAsdRiqIvBo\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.45 s.\n",
      "Finished at 2017-10-13 11:15:49.\n",
      "Requesting query... ok.\n",
      "Job ID: job_ZTt4rwEfZnlMSO3rKyH3W4zBC1CX\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.01 s.\n",
      "Finished at 2017-10-13 11:15:51.\n",
      "Requesting query... ok.\n",
      "Job ID: job_ygwwpKdQjCoVUkGt2ouuw94PiPKR\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.83 s.\n",
      "Finished at 2017-10-13 11:15:53.\n",
      "Requesting query... ok.\n",
      "Job ID: job_XXzzmN_WLvpg8XVMetMIaR1wPk7v\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.89 s.\n",
      "Finished at 2017-10-13 11:15:55.\n",
      "Requesting query... ok.\n",
      "Job ID: job_WYJicrrMCC0gCEr6j3CFlGMVqBhO\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.98 s.\n",
      "Finished at 2017-10-13 11:15:57.\n",
      "Requesting query... ok.\n",
      "Job ID: job_htifc-Z-tKpzBnxDZlc2Osu3VwQo\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.14 s.\n",
      "Finished at 2017-10-13 11:15:59.\n",
      "Requesting query... ok.\n",
      "Job ID: job_K8zjnypI8hDb_GZVqm7bzDJfsksz\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.82 s.\n",
      "Finished at 2017-10-13 11:16:01.\n",
      "Requesting query... ok.\n",
      "Job ID: job_fKmlUtXvZ03dgQw8IOY5-xqjLJuQ\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.1 s.\n",
      "Finished at 2017-10-13 11:16:03.\n",
      "Requesting query... ok.\n",
      "Job ID: job_5TiKhrg1FeMU94OKRgUQex7OxDfd\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.94 s.\n",
      "Finished at 2017-10-13 11:16:05.\n",
      "Requesting query... ok.\n",
      "Job ID: job_gLodEQ2Qs_7i9MstXk6Bdwxq2s8K\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.88 s.\n",
      "Finished at 2017-10-13 11:16:06.\n",
      "Requesting query... ok.\n",
      "Job ID: job_hMHKr_san4qIgK2nXevQ0ZVcaxKJ\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.85 s.\n",
      "Finished at 2017-10-13 11:16:09.\n",
      "Requesting query... ok.\n",
      "Job ID: job_YVA6BZGB0DzPVfiOasc-yvyUTHDC\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.97 s.\n",
      "Finished at 2017-10-13 11:16:11.\n",
      "Requesting query... ok.\n",
      "Job ID: job_nKiz2RZGgLIDa174dE-G6msx5-C9\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.98 s.\n",
      "Finished at 2017-10-13 11:16:13.\n",
      "Requesting query... ok.\n",
      "Job ID: job_ne7e5i8S26iinZpT4Dbedc_ADCyT\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.01 s.\n",
      "Finished at 2017-10-13 11:16:15.\n",
      "Requesting query... ok.\n",
      "Job ID: job_GasjtEJxESVFUeFflzSrYFYfB4td\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.04 s.\n",
      "Finished at 2017-10-13 11:16:17.\n",
      "Requesting query... ok.\n",
      "Job ID: job_hijSj4u2vbHOQlFmtMHZeied3FFZ\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.41 s.\n",
      "Finished at 2017-10-13 11:16:20.\n",
      "Requesting query... ok.\n",
      "Job ID: job_vNpiE9s_yT6QCInOQIfwH2t-XgY2\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.16 s.\n",
      "Finished at 2017-10-13 11:16:22.\n",
      "Requesting query... ok.\n",
      "Job ID: job_uOQuE_M5OX2dS2dqhbGhGTuY4A7m\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.87 s.\n",
      "Finished at 2017-10-13 11:16:24.\n",
      "Requesting query... ok.\n",
      "Job ID: job_LL8hCB5ewVeejPrTE4UClMs25oXz\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 3.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.84 s.\n",
      "Finished at 2017-10-13 11:16:26.\n",
      "Requesting query... ok.\n",
      "Job ID: job_Gkb8sSh0HD5sHb7ap2jnURfqIZ1v\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.8 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.3 s.\n",
      "Finished at 2017-10-13 11:16:29.\n",
      "Requesting query... ok.\n",
      "Job ID: job_3M9IdsMhwvPb3fE5nX9eEFQP0a5A\n",
      "Query running...\n",
      "Query done.\n",
      "Processed: 8.7 MB\n",
      "Standard price: $0.00 USD\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.98 s.\n",
      "Finished at 2017-10-13 11:16:31.\n"
     ]
    }
   ],
   "source": [
    "altitude_stats = []\n",
    "\n",
    "for vehicle in fleet:\n",
    "    #counting the rows of both parameters for all vehicles \n",
    "    count_altitude_df=pd.read_gbq(\"select count (*) from [headingon_params.altitude] where vehicle = '%s'\" %vehicle, projectid)\n",
    "    count_gps_altitude_df=pd.read_gbq(\"select count (*) from [headingon_params.gps_altitude_phone] where vehicle = '%s'\" %vehicle, projectid)\n",
    "    #retrieving the mode and the frequency of that mode\n",
    "    mode_altitude_df=pd.read_gbq(\"SELECT value AS mode, COUNT(1) AS freq FROM [headingon_params.altitude] where vehicle = '%s' GROUP BY 1 ORDER BY 2 DESC LIMIT 1\" %vehicle, projectid) \n",
    "    mode_gps_altitude_df=pd.read_gbq(\"SELECT value AS mode, COUNT(1) AS freq FROM [headingon_params.gps_altitude_phone] where vehicle = '%s' GROUP BY 1 ORDER BY 2 DESC LIMIT 1\" %vehicle, projectid) \n",
    "    #appending them in a list for further processing\n",
    "    altitude_stats.append({'vehicle': vehicle, 'OBD_count': count_altitude_df.iat[0,0], 'GPS_count': count_gps_altitude_df.iat[0,0], 'OBD_mode': mode_altitude_df.at[0, 'mode'] , 'GPS_mode': mode_gps_altitude_df.at[ 0 , 'mode'], 'OBD_frequency': mode_altitude_df.at[0 , 'freq'] ,'GPS_frequency': mode_gps_altitude_df.at[0 ,'freq']}) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vehicle</th>\n",
       "      <th>OBD_mode</th>\n",
       "      <th>GPS_mode</th>\n",
       "      <th>OBD_frequency</th>\n",
       "      <th>GPS_frequency</th>\n",
       "      <th>OBD_count</th>\n",
       "      <th>GPS_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>car1</td>\n",
       "      <td>108</td>\n",
       "      <td>108</td>\n",
       "      <td>2337</td>\n",
       "      <td>2342</td>\n",
       "      <td>63793</td>\n",
       "      <td>63764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>car2</td>\n",
       "      <td>124</td>\n",
       "      <td>124</td>\n",
       "      <td>405</td>\n",
       "      <td>403</td>\n",
       "      <td>14033</td>\n",
       "      <td>14029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>car3</td>\n",
       "      <td>-22</td>\n",
       "      <td>-22</td>\n",
       "      <td>1614</td>\n",
       "      <td>1612</td>\n",
       "      <td>58162</td>\n",
       "      <td>58149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>car4</td>\n",
       "      <td>109</td>\n",
       "      <td>358</td>\n",
       "      <td>6772</td>\n",
       "      <td>6247</td>\n",
       "      <td>248954</td>\n",
       "      <td>248913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>car5</td>\n",
       "      <td>153</td>\n",
       "      <td>153</td>\n",
       "      <td>3391</td>\n",
       "      <td>3390</td>\n",
       "      <td>68833</td>\n",
       "      <td>68777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>car6</td>\n",
       "      <td>73</td>\n",
       "      <td>73</td>\n",
       "      <td>2317</td>\n",
       "      <td>2317</td>\n",
       "      <td>105002</td>\n",
       "      <td>104976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>car7</td>\n",
       "      <td>107</td>\n",
       "      <td>107</td>\n",
       "      <td>2169</td>\n",
       "      <td>1993</td>\n",
       "      <td>99092</td>\n",
       "      <td>91039</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  vehicle  OBD_mode  GPS_mode  OBD_frequency  GPS_frequency  OBD_count  \\\n",
       "0    car1       108       108           2337           2342      63793   \n",
       "1    car2       124       124            405            403      14033   \n",
       "2    car3       -22       -22           1614           1612      58162   \n",
       "3    car4       109       358           6772           6247     248954   \n",
       "4    car5       153       153           3391           3390      68833   \n",
       "5    car6        73        73           2317           2317     105002   \n",
       "6    car7       107       107           2169           1993      99092   \n",
       "\n",
       "   GPS_count  \n",
       "0      63764  \n",
       "1      14029  \n",
       "2      58149  \n",
       "3     248913  \n",
       "4      68777  \n",
       "5     104976  \n",
       "6      91039  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#adjusting the value formating and setting it up as a dataframe\n",
    "pd.set_option('precision', 0)\n",
    "altitude_stats_df = pd.DataFrame(altitude_stats)\n",
    "\n",
    "#setting up the column sequence\n",
    "column_arragment = ['vehicle', 'OBD_mode','GPS_mode', 'OBD_frequency', 'GPS_frequency', 'OBD_count', 'GPS_count' ]\n",
    "\n",
    "altitude_stats_df[column_arragment]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Explanation of columns:\n",
    "* *vehicle* : the seperate car being checked.\n",
    "* *OBD_mode* : the mode found in the OBD data per car.\n",
    "* *GPS_mode* : the mode foud in the GSP datbase.\n",
    "* *OBD_frequency* : the frequency this mode was found in the data.\n",
    "* *GPS_frequency* : the frequency this mode was found in the data.\n",
    "* *OBD_count* : total rows of OBD.\n",
    "* *GPS_count* : total rows of GPS.\n",
    "\n",
    "\n",
    "**car4**: There seems to be a large disparity between the *mode* and *frequency* of this vehicle. And this has an impact on the correlation between the two.\n",
    "\n",
    "**car7**: There is a stunning 8000 less rows in the GPS data base. Which has had a significant impact on the correlation.\n",
    "\n",
    "### **conclusion elevation: **\n",
    "Both OBD & GPS seem to reasonable valid eachother. And the discreprancies describe above are likely the result of unreliable GPS data. Thus i will focus on the OBD data of altitude as the best predictor."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Driving area:**\n",
    "\n",
    "It seems reasonable to test the assumption that tire usage depends on the average speed of the car, since the speed of a car is dependent on its location we can use driving area as an indicator. In the data there are two parameters that show this data:\n",
    "\n",
    "-    perc_of_city_driving\n",
    "-    perc_of_high_way_driving\n",
    "\n",
    "However, i prefer a more direct approach which shows the speed of the car directly, since it is possible that there is traffic on the highway or driver structurally speed on city roads. In the data we find 2 speed related parameters:\n",
    "\n",
    "-\tspeed_gps_phone\n",
    "-   speed_obd\n",
    "\n",
    "To quickly validate the data we use the parameter which compares the two:\n",
    "\n",
    "-   gps_vs_obd_speed_difference\n",
    "\n",
    "I split the data out in buckets, of 5 km/h. So 0 to 5 km/h, 5 to 10 km/h, etc until 25 to 30 km/h."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requesting query... ok.\n",
      "Job ID: job_sTalUyysdYvJFgACJ9UxR26kXYbU\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.81 s.\n",
      "Finished at 2017-10-13 11:55:20.\n",
      "Requesting query... ok.\n",
      "Job ID: job_iPy1S4XYTf6T6usKWqR13xkVVY5q\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.04 s.\n",
      "Finished at 2017-10-13 11:55:23.\n",
      "Requesting query... ok.\n",
      "Job ID: job_9V3rTKD21zK4xMp8BlAa_2SKmjSl\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.79 s.\n",
      "Finished at 2017-10-13 11:55:24.\n",
      "Requesting query... ok.\n",
      "Job ID: job_ye8Ma3JJXWmyzrbzh1nIqEgLuTcR\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.7 s.\n",
      "Finished at 2017-10-13 11:55:26.\n",
      "Requesting query... ok.\n",
      "Job ID: job_sv3fDPxd1A_y7ADl5f0EEqlKkzX5\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.88 s.\n",
      "Finished at 2017-10-13 11:55:28.\n",
      "Requesting query... ok.\n",
      "Job ID: job_YprbER-Fxx2Ay54BhAy_lWlJtr55\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.82 s.\n",
      "Finished at 2017-10-13 11:55:30.\n",
      "Requesting query... ok.\n",
      "Job ID: job_M0CRf744KjMtesnlqIMt4XgX9Lot\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.64 s.\n",
      "Finished at 2017-10-13 11:55:32.\n"
     ]
    }
   ],
   "source": [
    "difference_speed = []\n",
    "\n",
    "#taking the full count of the rows, to calculate the percentages later\n",
    "full_count_rows_df=pd.read_gbq(\"select count (*) from [headingon_params.gps_vs_obd_speed_difference]\", projectid)\n",
    "\n",
    "#while loop iterating through all the buckets\n",
    "i = 0\n",
    "n = 5\n",
    "while n <= 30:\n",
    "    full_count_rows_df=pd.read_gbq(\"select count (*) from [headingon_params.gps_vs_obd_speed_difference] where value between {} and {} \".format(str(i), str(n)), projectid)\n",
    "    difference_speed.append({'segment': str(i)+\"to\"+str(n) , 'total_count': full_count_rows_df.iat[0,0] })\n",
    "    i+=5\n",
    "    n+=5\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we make a visual representation of the data  to see which bucket is the biggest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>segment</th>\n",
       "      <th>percentage</th>\n",
       "      <th>total_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0to5</td>\n",
       "      <td>0.9276</td>\n",
       "      <td>602665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5to10</td>\n",
       "      <td>0.0606</td>\n",
       "      <td>39356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10to15</td>\n",
       "      <td>0.0083</td>\n",
       "      <td>5390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15to20</td>\n",
       "      <td>0.0017</td>\n",
       "      <td>1089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20to25</td>\n",
       "      <td>0.0007</td>\n",
       "      <td>463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>25to30</td>\n",
       "      <td>0.0009</td>\n",
       "      <td>568</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  segment  percentage  total_count\n",
       "0    0to5      0.9276       602665\n",
       "1   5to10      0.0606        39356\n",
       "2  10to15      0.0083         5390\n",
       "3  15to20      0.0017         1089\n",
       "4  20to25      0.0007          463\n",
       "5  25to30      0.0009          568"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY0AAAEjCAYAAADOsV1PAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAHVdJREFUeJzt3X2cHmV97/HP1wSEgjwvSJNgUFMVUZ7SELT2WLEQ0GM4\nrXjAh0SMTetBRG2PhtaWKqWG1iMWFWyUSLAqUqwSFYg5CHo8BSQ8CALapCCwDQ+BAEZ4iUW+/WOu\nlTvLvbtXNktm7833/Xrdr73v31xzzzV5Zee7M3PNjGwTERFR41ltdyAiInpHQiMiIqolNCIiolpC\nIyIiqiU0IiKiWkIjIiKqJTQiIqJaQiMiIqolNCIiotrktjsw1vbYYw9Pnz697W5ERPSU66677gHb\nfSO1m3ChMX36dFatWtV2NyIieoqkO2va5fBURERUS2hERES1hEZERFRLaERERLWERkREVEtoRERE\ntarQkLSLpIsk/VjSbZIOk7SbpJWSVpefu5a2knSWpDWSbpJ0cMf3zC/tV0ua31E/RNLNZZ6zJKnU\nuy4jIiLaUbun8Q/AZbZfDBwA3AYsAi63PQO4vHwGOAqYUV4LgXOgCQDgVOBQYBZwakcInFPaDsw3\np9SHWkZERLRgxIv7JO0E/C7wdgDbvwR+KWku8OrSbBlwJfBBYC5wvpuHj19d9lL2Lm1X2l5fvncl\nMEfSlcBOtq8q9fOBY4BLy3d1W8aYm77oW6Oa76eLXzfGPYmIGL9q9jSeD6wDPi/pBkmfk7QDsJft\newDKzz1L+ynA3R3z95facPX+LnWGWcZGJC2UtErSqnXr1lWsUkREjEZNaEwGDgbOsX0Q8CjDHyZS\nl5pHUa9me4ntmbZn9vWNeOuUiIgYpZrQ6Af6bV9TPl9EEyL3lcNOlJ/3d7Sf1jH/VGDtCPWpXeoM\ns4yIiGjBiKFh+17gbkkvKqXDgVuB5cDACKj5wMXl/XJgXhlFNRt4pBxaWgEcIWnXcgL8CGBFmbZB\n0uwyamreoO/qtoyIiGhB7V1uTwK+KGlb4HbgBJrAuVDSAuAu4NjS9hLgaGAN8Fhpi+31kk4Dri3t\nPjJwUhx4F3AesD3NCfBLS33xEMuIiIgWVIWG7RuBmV0mHd6lrYETh/iepcDSLvVVwP5d6g92W0ZE\nRLQjV4RHRES1hEZERFRLaERERLWERkREVEtoREREtYRGRERUS2hERES1hEZERFRLaERERLWERkRE\nVEtoREREtYRGRERUS2hERES1hEZERFRLaERERLWERkREVEtoREREtYRGRERUS2hERES1hEZERFRL\naERERLWERkREVEtoREREtYRGRERUS2hERES1qtCQ9FNJN0u6UdKqUttN0kpJq8vPXUtdks6StEbS\nTZIO7vie+aX9aknzO+qHlO9fU+bVcMuIiIh2bMqexu/ZPtD2zPJ5EXC57RnA5eUzwFHAjPJaCJwD\nTQAApwKHArOAUztC4JzSdmC+OSMsIyIiWrA5h6fmAsvK+2XAMR318924GthF0t7AkcBK2+ttPwSs\nBOaUaTvZvsq2gfMHfVe3ZURERAtqQ8PAtyVdJ2lhqe1l+x6A8nPPUp8C3N0xb3+pDVfv71IfbhkR\nEdGCyZXtXml7raQ9gZWSfjxMW3WpeRT1aiXIFgLss88+mzJrRERsgqo9Ddtry8/7ga/RnJO4rxxa\novy8vzTvB6Z1zD4VWDtCfWqXOsMsY3D/ltieaXtmX19fzSpFRMQojBgaknaQ9JyB98ARwI+A5cDA\nCKj5wMXl/XJgXhlFNRt4pBxaWgEcIWnXcgL8CGBFmbZB0uwyamreoO/qtoyIiGhBzeGpvYCvlVGw\nk4Ev2b5M0rXAhZIWAHcBx5b2lwBHA2uAx4ATAGyvl3QacG1p9xHb68v7dwHnAdsDl5YXwOIhlhER\nES0YMTRs3w4c0KX+IHB4l7qBE4f4rqXA0i71VcD+tcuIiIh25IrwiIioltCIiIhqCY2IiKiW0IiI\niGoJjYiIqJbQiIiIagmNiIioltCIiIhqCY2IiKiW0IiIiGoJjYiIqJbQiIiIagmNiIioltCIiIhq\nCY2IiKiW0IiIiGoJjYiIqJbQiIiIagmNiIioltCIiIhqCY2IiKiW0IiIiGoJjYiIqJbQiIiIagmN\niIioltCIiIhq1aEhaZKkGyR9s3zeV9I1klZL+oqkbUv92eXzmjJ9esd3nFLqP5F0ZEd9TqmtkbSo\no951GRER0Y5N2dM4Gbit4/MZwJm2ZwAPAQtKfQHwkO0XAmeWdkjaDzgOeCkwBzi7BNEk4NPAUcB+\nwPGl7XDLiIiIFlSFhqSpwOuAz5XPAl4DXFSaLAOOKe/nls+U6YeX9nOBC2w/bvsOYA0wq7zW2L7d\n9i+BC4C5IywjIiJaULun8QngA8CT5fPuwMO2nyif+4Ep5f0U4G6AMv2R0v7X9UHzDFUfbhkbkbRQ\n0ipJq9atW1e5ShERsalGDA1Jrwfut31dZ7lLU48wbazqTy/aS2zPtD2zr6+vW5OIiBgDkyvavBJ4\ng6Sjge2AnWj2PHaRNLnsCUwF1pb2/cA0oF/SZGBnYH1HfUDnPN3qDwyzjIiIaMGIexq2T7E91fZ0\nmhPZ37H9FuAK4I2l2Xzg4vJ+eflMmf4d2y7148roqn2BGcAPgGuBGWWk1LZlGcvLPEMtIyIiWrA5\n12l8EHi/pDU05x/OLfVzgd1L/f3AIgDbtwAXArcClwEn2v5V2Yt4N7CCZnTWhaXtcMuIiIgW1Bye\n+jXbVwJXlve304x8GtzmF8CxQ8x/OnB6l/olwCVd6l2XERER7cgV4RERUS2hERER1RIaERFRLaER\nERHVEhoREVEtoREREdUSGhERUS2hERER1RIaERFRLaERERHVEhoREVEtoREREdUSGhERUS2hERER\n1RIaERFRLaERERHVEhoREVEtoREREdUSGhERUS2hERER1RIaERFRLaERERHVEhoREVEtoREREdUS\nGhERUW3E0JC0naQfSPqhpFskfbjU95V0jaTVkr4iadtSf3b5vKZMn97xXaeU+k8kHdlRn1NqayQt\n6qh3XUZERLSjZk/jceA1tg8ADgTmSJoNnAGcaXsG8BCwoLRfADxk+4XAmaUdkvYDjgNeCswBzpY0\nSdIk4NPAUcB+wPGlLcMsIyIiWjBiaLjx8/Jxm/Iy8BrgolJfBhxT3s8tnynTD5ekUr/A9uO27wDW\nALPKa43t223/ErgAmFvmGWoZERHRgqpzGmWP4EbgfmAl8O/Aw7afKE36gSnl/RTgboAy/RFg9876\noHmGqu8+zDIG92+hpFWSVq1bt65mlSIiYhSqQsP2r2wfCEyl2TN4Sbdm5aeGmDZW9W79W2J7pu2Z\nfX193ZpERMQY2KTRU7YfBq4EZgO7SJpcJk0F1pb3/cA0gDJ9Z2B9Z33QPEPVHxhmGRER0YKa0VN9\nknYp77cHXgvcBlwBvLE0mw9cXN4vL58p079j26V+XBldtS8wA/gBcC0wo4yU2pbmZPnyMs9Qy4iI\niBZMHrkJewPLyiinZwEX2v6mpFuBCyT9DXADcG5pfy7wBUlraPYwjgOwfYukC4FbgSeAE23/CkDS\nu4EVwCRgqe1bynd9cIhlREREC0YMDds3AQd1qd9Oc35jcP0XwLFDfNfpwOld6pcAl9QuIyIi2pEr\nwiMiolpCIyIiqiU0IiKiWkIjIiKqJTQiIqJaQiMiIqolNCIiolpCIyIiqiU0IiKiWkIjIiKqJTQi\nIqJaQiMiIqolNCIiolpCIyIiqiU0IiKiWkIjIiKqJTQiIqJaQiMiIqolNCIiolpCIyIiqiU0IiKi\nWkIjIiKqJTQiIqJaQiMiIqolNCIiotqIoSFpmqQrJN0m6RZJJ5f6bpJWSlpdfu5a6pJ0lqQ1km6S\ndHDHd80v7VdLmt9RP0TSzWWesyRpuGVEREQ7avY0ngD+1PZLgNnAiZL2AxYBl9ueAVxePgMcBcwo\nr4XAOdAEAHAqcCgwCzi1IwTOKW0H5ptT6kMtIyIiWjBiaNi+x/b15f0G4DZgCjAXWFaaLQOOKe/n\nAue7cTWwi6S9gSOBlbbX234IWAnMKdN2sn2VbQPnD/qubsuIiIgWbNI5DUnTgYOAa4C9bN8DTbAA\ne5ZmU4C7O2brL7Xh6v1d6gyzjIiIaEF1aEjaEfgq8F7bPxuuaZeaR1GvJmmhpFWSVq1bt25TZo2I\niE1QFRqStqEJjC/a/pdSvq8cWqL8vL/U+4FpHbNPBdaOUJ/apT7cMjZie4ntmbZn9vX11axSRESM\nQs3oKQHnArfZ/njHpOXAwAio+cDFHfV5ZRTVbOCRcmhpBXCEpF3LCfAjgBVl2gZJs8uy5g36rm7L\niIiIFkyuaPNK4G3AzZJuLLU/BxYDF0paANwFHFumXQIcDawBHgNOALC9XtJpwLWl3Udsry/v3wWc\nB2wPXFpeDLOMiIhowYihYfv7dD/vAHB4l/YGThziu5YCS7vUVwH7d6k/2G0ZERHRjlwRHhER1RIa\nERFRLaERERHVEhoREVEtoREREdUSGhERUS2hERER1RIaERFRLaERERHVEhoREVEtoREREdUSGhER\nUS2hERER1RIaERFRLaERERHVEhoREVEtoREREdUSGhERUS2hERER1RIaERFRLaERERHVEhoREVEt\noREREdUSGhERUS2hERER1RIaERFRbcTQkLRU0v2SftRR203SSkmry89dS12SzpK0RtJNkg7umGd+\nab9a0vyO+iGSbi7znCVJwy0jIiLaU7OncR4wZ1BtEXC57RnA5eUzwFHAjPJaCJwDTQAApwKHArOA\nUztC4JzSdmC+OSMsIyIiWjJiaNj+HrB+UHkusKy8XwYc01E/342rgV0k7Q0cCay0vd72Q8BKYE6Z\ntpPtq2wbOH/Qd3VbRkREtGS05zT2sn0PQPm5Z6lPAe7uaNdfasPV+7vUh1vG00haKGmVpFXr1q0b\n5SpFRMRIxvpEuLrUPIr6JrG9xPZM2zP7+vo2dfaIiKg02tC4rxxaovy8v9T7gWkd7aYCa0eoT+1S\nH24ZERHRktGGxnJgYATUfODijvq8MopqNvBIObS0AjhC0q7lBPgRwIoybYOk2WXU1LxB39VtGRER\n0ZLJIzWQ9GXg1cAekvppRkEtBi6UtAC4Czi2NL8EOBpYAzwGnABge72k04BrS7uP2B44uf4umhFa\n2wOXlhfDLCMiIloyYmjYPn6ISYd3aWvgxCG+ZymwtEt9FbB/l/qD3ZYRERHtyRXhERFRLaERERHV\nEhoREVEtoREREdUSGhERUS2hERER1RIaERFRLaERERHVEhoREVEtoREREdUSGhERUS2hERER1RIa\nERFRLaERERHVEhoREVFtxOdpxMQwfdG3RjXfTxe/box7EhG9LHsaERFRLaERERHVEhoREVEtoRER\nEdUSGhERUS2hERER1RIaERFRLaERERHVEhoREVFt3IeGpDmSfiJpjaRFbfcnImJrNq5DQ9Ik4NPA\nUcB+wPGS9mu3VxERW6/xfu+pWcAa27cDSLoAmAvc2mqvYlzJfbUitpzxHhpTgLs7PvcDh7bUl4hW\nTPRQzPp1N17XT7bb7sOQJB0LHGn7neXz24BZtk8a1G4hsLB8fBHwky3YzT2AB7bg8ra0ibx+E3nd\nIOvX67b0+j3Pdt9Ijcb7nkY/MK3j81Rg7eBGtpcAS7ZUpzpJWmV7ZhvL3hIm8vpN5HWDrF+vG6/r\nN65PhAPXAjMk7StpW+A4YHnLfYqI2GqN6z0N209IejewApgELLV9S8vdiojYao3r0ACwfQlwSdv9\nGEYrh8W2oIm8fhN53SDr1+vG5fqN6xPhERExvoz3cxoRETGOJDQiIqJaQiMiIqqN+xPhsWVJ2ovm\nSnwDa23f13KXIp5G0r7AQcCttn/cdn82l6SdgTl0/O4BK2w/3GrHusiJ8EqS9rD9QMfnt9LcG+tH\nwGfd4/+Qkg4EPgPsDPxHKU8FHgb+l+3r2+rbM2GibXQAJL2Y5t5snRue5bZva7VjY0DS120fU97P\nBT4BXAm8Avio7fPa693mkTQPOBX4Nhv/7v0+8GHb57fVt24SGpUkXW/74PL+Q8CrgC8Brwf6bb+v\nzf5tLkk3An9s+5pB9dnAP9o+oJ2ejY2JvNEBkPRB4HjgApo7KUCz4TkOuMD24rb6NhYk3WD7oPL+\nX4G32L5D0h7A5b38/1PST4BDB+9VSNoVuMb2b7XTs+5yeKqeOt7/AfAq249K+hIwEf4K32FwYADY\nvlrSDm10aIw9r+P9B4HXdG50gPNa6dXYWQC81PZ/dhYlfRy4Bejp0KDZcxow2fYdALYfkPRkS30a\nK2Lj9RvwJBtvd8aFhEa97SUdRDN4YJLtRwFs/6ekX7XbtTFxqaRvAefz1J2FpwHzgMta69XYmcgb\nHWg2ML8J3DmovneZ1usOkPQzmo3osyU91/a95fZCk1ru2+Y6Hbhe0rd56ndvH5rDU6e11qsh5PBU\nJUlXDCq92fY9knanOWE17m4stqkkHcVTx8RFc5hjebkqv6eVYH+UstEB9unY6Kyy/fJWO7iZJM0B\nPgWsZuMNzwuBd9ueCMH/NJJ2AV5i+6q2+7I5yqGoI9n4d2+F7Yda7VgXCY3NJOlZwHa2H2u7L7Hp\nJspGB379f3EWG294rrU9EfaEtyqSdrO9vu1+dJPQGAVJbwB+t3z8ru1vtNmfZ5qkJbYXjtwy4pkh\naRrw9zSBeCnw9wPnbzoHOfQiSa8EPkdzGPEdwN8ALwC2Ad403v6gycV9m0jSYuBkmkfO3gq8R9JH\n2+3V5pO02xCv3YGj2+7fM0nSzW33YXNJermkqyXdLWlJOdwxMO0HbfZtjCylGe12Es15mu+W/5uw\n8SCHXnQm8CbgncC3aIbZPp/mUPHH2uxYNzkRvumOBg60/SSApGXADcAprfZq862jOYnaOVrD5fOe\nrfRoDEn6g6EmAc/dkn15hpwN/DVwNc3G5/uS3mD732n+Yu11fbY/U96fVK6T+l7Z6+/1wyXb2L4Z\nQNI6298HsH29pO3b7drTJTRGZxdg4Hjjzm12ZAzdDhxu+67BEyTd3aV9r/kK8EW6b2C228J9eSbs\n2HGy+2OSrgMuK49I7vWNKsA2kraz/QsA2/8k6V6aZ+30+pDwziM+g//43HZLdqRGQmPTfRS4oYym\nEs25jT9vt0tj4hPArsDTQgP4uy3cl2fCTcDHbP9o8ARJr22hP2NNkna2/QiA7Ssk/SHwVWC3drs2\nJj4HHAp8d6Bg+/9KOpbe///5l5J+w/Zjtr8+UJT0Apoh8ONKToSPgqS9gd+mCY1rbN/bcpdiBJJe\nBdw5xJ7UTNurWujWmJH0ZuB221cPqu8D/KXtP2qnZ7GpJD0HsO2ft92XbhIam0jS5bYPH6nWqyRt\nA7yLjtFhwGcGX2kc49t43/CMlqSpwCeB36EZbfR94GTb/cPO2AMkvYxmz2I3mj9I1wHzxtsjrjN6\nqpKk7cpojT0k7doxumg6zZW4E8U5wCE0J1bPBg4utQlB0lRJX5O0TtJ9kr5aNkQTgqSXSbqB5kaa\nt0q6TtJL2+7XGPo8sJxmBNUU4BulNhH8I/B+28+zvQ/wp8BnW+7T02RPo5Kkk4H30gTEf3RM2kBz\nl9tPtdKxMSbph4Nv/tat1qskraS50eQXSumtNDe/+/32ejV2ys38/sL2FeXzq4G/tf2KVjs2RiTd\naPvAkWq9qFd+93IivJLtfyj3hjkW6OOpW09fPBFuPd3hV5JeUIZqIun5wES6orjPdudfpudJem9r\nvRl7OwwEBoDtKyfIDScHPFCG2365fD4eeLDF/oyl2yX9JRv/QXNHi/3pKoenKkn6AM1fqI8D1wAD\nF0x9WdKi1jo29v43cIWkKyV9F/gO8Gct92ksPSDprZImlddbmTgbHSgbHknTy+tDjMMNz2Z4B82F\ncPcC9wBvBE5otUdj5x00f5D+S3ntAby9zQ51k8NTlST9G91vPb0tcIvtGe30bGxJenZ5+yKak3E/\nBrD9eGudGkNlNNGngMNo9hb/FXhPt1FVvahcCf5hmhPFAN8D/no8PgFuNCS90vb/H6nWiyQda/uf\nR6q1LXsa9QZuPT3YRLn19ICrbD9u+ybbPyxhMa7ufbOZptl+g+0+23uWexZNa7tTY+i1tt9j++Dy\nei/NLbYnik9W1npRt7tKjLs7TeScRr33ApdL6nrr6dZ6NUYkPZdmNMrAc0MGbieyE/AbrXVs7H2S\nZkTYSLVedQow+C/TbrWeIukwmqcs9kl6f8eknejx52mURxIcDUyRdFbHpJ2AJ9rp1dASGpVsXybp\nt5i4t54+kub46VTg46VmmtFhPX/F+0Te6EDvbXhGYVtgR5pt1nM66j+jOa/Ry9YCq4A3ANd11DcA\n4+4x0jmnERuR9Ie2v1quoJ4F3Gz72233a3NJ+m/Aq4E/AT7TMWkD8A3bq9vo11iRdABwIPAR4K86\nJm0ArhiPD/MZDUn7TJTzT4NJ2qYXLqJNaATQ3D7b9qzy/p3AicDXgSNoNqq9/oxpYGJvdKB3Njyb\nStLONIfZjqEZYQRwP3AxsLiXT/RLmjNws8mynh+nuU3Rj4D32b6vzf4NlhPhMaDz9tl/DBxh+8M0\nofGWdro0diTtrOZZKN+W9GB53SZpsZqn9/U0NY97HXhm/c6SzpV0k6QvSdqr7f6NgQuBh4BX297d\n9u7A7wEP0+Pna4C/7Xj/f2iGEv934Fqaq8THlYRGDHhWuT3K7jR7oOsAbD/KxDgmPpE3OtBjG55R\nmG77jM6bg9q+t+wB79Niv8baTNsfsn2n7TOB6W13aLCcCI8BO9OchBNgSc+1fa+kHdn4wUy9arrt\nMzoLZQO0WNJEuThswMyO22qcKWl+q70ZG3eWC2yXDRyuKXtQb+ep0Yy9as8yOEPATpLkp84bjLs/\n7BMaAYDt6UNMehL4H1uwK8+UibzRgR7b8IzC/wQW0TzmdS+akX330dy88E1tdmwMfJanRoQto7kS\nfF0ZBn9ja70aQk6Ex1ahXCm9iOa5ywOPrx3Y6Czu9dFFkk4dVDrb9sCG5+9sz2ujX2NJ0otphoRf\n3XnL984Tyb2qrNsUmufzjOt1S2jEVk/SCYNuYjihTIT1k/QemhF9t9EMLT7Z9sVl2vW2e/biTEkn\n0Vwg3BPrltCIrZ6ku8rzCyakibB+km4GDrP98/IMm4uAL5S7T99g+6BWO7gZem3dck4jtgqSbhpq\nEtDzQ1In+voBkwYO29j+aXlOyEWSnkfvD9ToqXVLaMTWYi+aW6UMPnchmjvd9rqJvn73SjrQ9o0A\n5a/y1wNLgZe127XN1lPrltCIrcU3gR0HfjE7Sbpyy3dnzE309ZvHoOuFbD8BzJPU69eh9NS65ZxG\nRERUmwjjtyMiYgtJaERERLWERkSPKc/+fnPb/YitU0IjovdMBxIa0YqcCI/oQtIONHfGnUrzZL/T\ngDU0zzrYEXgAeLvteyT9NnAu8CjwfeAo2/tLejvN8x8mAfvT3H12W+BtwOPA0bbXS3oB8Gma50Q8\nBvyR7R9LOo/myXQzgecCH7B9kaSrgZcAd9DcS+vMZ/rfI2JA9jQiupsDrLV9gO39gctoniX+RtuH\n0IyhP720/TzwJ7YPAwY/+nd/mr2CWaX9Y+UK36tohloCLAFOKt/7Z8DZHfPvDfwO8Hpg4EFYi4D/\nZ/vABEZsablOI6K7m4GPSTqD5hqIh2gCYKUkaPYe7ikPcHqO7YEL6L5Es4EfcIXtDcAGSY8A3+j4\n/peXW8+/Avjn8r0Az+6Y/+u2nwRunSAPU4oel9CI6ML2v0k6BDga+CiwEril7E38Wrl77nAe73j/\nZMfnJ2l+/54FPNzx/Ivh5h93t5SIrU8OT0V0Iek3aQ4l/RPwMeBQoE/SYWX6NpJeWm6pvkHS7DLr\ncZuyHNs/A+6QdGz5Xkk6YITZNvDU8xcitqiERkR3LwN+IOlG4C+AvwLeCJwh6Yc0D8d5RWm7AFgi\n6SqavYFHNnFZbwEWlO+9heaZH8O5CXhC0g8lvW8TlxWxWTJ6KmIzSdpx4C6lkhYBe9s+ueVuRTwj\nck4jYvO9TtIpNL9Pd9I8QjZiQsqeRkREVMs5jYiIqJbQiIiIagmNiIioltCIiIhqCY2IiKiW0IiI\niGr/BTMgTcQ2BLYBAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7f894cd6f2b0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "difference_speed_df = pd.DataFrame(difference_speed)\n",
    "\n",
    "# to make percetages x divided by the number of rows in the dataset\n",
    "f = lambda x: x/649694\n",
    "\n",
    "#adding the column to the dataframe\n",
    "difference_speed_df['percentage'] = difference_speed_df['total_count'].apply(f)\n",
    "\n",
    "# output to notebook\n",
    "%matplotlib inline\n",
    "\n",
    "#the actual plot (I have had real trouble getting bokeh to run properly)\n",
    "plot_difference_speed = difference_speed_df.plot(difference_speed_df['segment'], kind='bar', legend=None)\n",
    "\n",
    "pd.set_option('precision', 4)\n",
    "column_bucket = ['segment', 'percentage', 'total_count']\n",
    "difference_speed_df[column_bucket]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "from this we can gather that most of the difference lies in the smallest band range and thus both data streams validate each other. Thus we can use either OBD or GPS dataset to make a split in speed so we can get a ratio of ***local driving*** and ***highway driving***\n",
    "\n",
    "This subdivision is what we do next"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requesting query... ok.\n",
      "Job ID: job_5bPGyx1bqlG5MOC2UkX4tfmLnd0X\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 1.08 s.\n",
      "Finished at 2017-10-13 12:05:27.\n",
      "Requesting query... ok.\n",
      "Job ID: job_DZ2SPXuGkVAW__yxA0ShU-_5rz1b\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.64 s.\n",
      "Finished at 2017-10-13 12:05:29.\n",
      "Requesting query... ok.\n",
      "Job ID: job_63V40383UXS5sOFIPMxsHiRtyvSF\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.63 s.\n",
      "Finished at 2017-10-13 12:05:31.\n",
      "Requesting query... ok.\n",
      "Job ID: job_ncLD2jS6wpIrs0oLqL5bVY39FIlx\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.74 s.\n",
      "Finished at 2017-10-13 12:05:33.\n",
      "Requesting query... ok.\n",
      "Job ID: job_O2q2c-lwjPsgA6dXBEWyPHw0x6lF\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.76 s.\n",
      "Finished at 2017-10-13 12:05:35.\n",
      "Requesting query... ok.\n",
      "Job ID: job_gB7eGFFGZ_dI8DAUYkmasUXhhg7P\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.83 s.\n",
      "Finished at 2017-10-13 12:05:37.\n",
      "Requesting query... ok.\n",
      "Job ID: job_ngNrv5InF0QEOEjc2Wxbtla5fGng\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.81 s.\n",
      "Finished at 2017-10-13 12:05:38.\n",
      "Requesting query... ok.\n",
      "Job ID: job_OKGccVgVf3SKhiE1JYKjUgEE-j6h\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.62 s.\n",
      "Finished at 2017-10-13 12:05:40.\n",
      "Requesting query... ok.\n",
      "Job ID: job_MDtjL6iM20EDuaiFE06UOrpuLsOo\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.79 s.\n",
      "Finished at 2017-10-13 12:05:42.\n",
      "Requesting query... ok.\n",
      "Job ID: job_ToziR-YQzXv6eoPti4am4w1999bA\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.6 s.\n",
      "Finished at 2017-10-13 12:05:43.\n",
      "Requesting query... ok.\n",
      "Job ID: job_gSXTF4Tg78OnhJR6pSvAvlzRuleU\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.71 s.\n",
      "Finished at 2017-10-13 12:05:45.\n",
      "Requesting query... ok.\n",
      "Job ID: job_P3Jffk6zBQuSJIvC51com303oDJT\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.94 s.\n",
      "Finished at 2017-10-13 12:05:47.\n",
      "Requesting query... ok.\n",
      "Job ID: job_6lTnEOF_6xCbIl9oAiXBIhTy5244\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.69 s.\n",
      "Finished at 2017-10-13 12:05:49.\n",
      "Requesting query... ok.\n",
      "Job ID: job_2VvUTE71Vg0165v4OhuYD1DqCyTj\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 1 rows.\n",
      "\n",
      "Total time taken 0.64 s.\n",
      "Finished at 2017-10-13 12:05:51.\n"
     ]
    }
   ],
   "source": [
    "driving_area = []\n",
    "\n",
    "#bigquery on 70 km/h\n",
    "for vehicle in fleet:\n",
    "    local_df = pd.read_gbq(\"select count (*) from [headingon_params.speed_obd] where value <= 70 and vehicle = '%s' \" %vehicle ,projectid)\n",
    "    highways_df = pd.read_gbq(\"select count (*) from [headingon_params.speed_obd] where value > 70 and vehicle = '%s' \" %vehicle ,projectid)\n",
    "    #appending them in a list\n",
    "    driving_area.append({'vehicles': vehicle, 'highways': highways_df.iat[0,0], 'local': local_df.iat[0,0] })    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vehicles</th>\n",
       "      <th>highways</th>\n",
       "      <th>local</th>\n",
       "      <th>perc_highway</th>\n",
       "      <th>perc_local</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>car1</td>\n",
       "      <td>13606</td>\n",
       "      <td>50223</td>\n",
       "      <td>0.2132</td>\n",
       "      <td>0.7868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>car2</td>\n",
       "      <td>4600</td>\n",
       "      <td>9442</td>\n",
       "      <td>0.3276</td>\n",
       "      <td>0.6724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>car3</td>\n",
       "      <td>19754</td>\n",
       "      <td>38607</td>\n",
       "      <td>0.3385</td>\n",
       "      <td>0.6615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>car4</td>\n",
       "      <td>66522</td>\n",
       "      <td>182383</td>\n",
       "      <td>0.2673</td>\n",
       "      <td>0.7327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>car5</td>\n",
       "      <td>24488</td>\n",
       "      <td>44394</td>\n",
       "      <td>0.3555</td>\n",
       "      <td>0.6445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>car6</td>\n",
       "      <td>61940</td>\n",
       "      <td>43077</td>\n",
       "      <td>0.5898</td>\n",
       "      <td>0.4102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>car7</td>\n",
       "      <td>50358</td>\n",
       "      <td>40788</td>\n",
       "      <td>0.5525</td>\n",
       "      <td>0.4475</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  vehicles  highways   local  perc_highway  perc_local\n",
       "0     car1     13606   50223        0.2132      0.7868\n",
       "1     car2      4600    9442        0.3276      0.6724\n",
       "2     car3     19754   38607        0.3385      0.6615\n",
       "3     car4     66522  182383        0.2673      0.7327\n",
       "4     car5     24488   44394        0.3555      0.6445\n",
       "5     car6     61940   43077        0.5898      0.4102\n",
       "6     car7     50358   40788        0.5525      0.4475"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#putting into a dataframe\n",
    "driving_area_df = pd.DataFrame(driving_area)\n",
    "\n",
    "#adding totals\n",
    "driving_area_df['total'] = driving_area_df['highways'].add(driving_area_df['local'])\n",
    "\n",
    "#adding percentages\n",
    "driving_area_df['perc_highway'] = driving_area_df['highways'].div(driving_area_df['total'])\n",
    "driving_area_df['perc_local'] = driving_area_df['local'].div(driving_area_df['total'])\n",
    "\n",
    "#make the column sequence more readable\n",
    "column_list_driving_area = ['vehicles', 'highways', 'local', 'perc_highway', 'perc_local']\n",
    "\n",
    "#showing the data\n",
    "driving_area_df[column_list_driving_area]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **conclusion driving areas**\n",
    "\n",
    "We used the following parameter:\n",
    "\n",
    "* gps_vs_obd_speed_difference\n",
    "\n",
    "which compares the data of both the GPS and OBD speed data parameters to validate there accuracy. Which was sastisfactory \n",
    "acurate.\n",
    "\n",
    "Then we took the OBD parameter, since this is already in the preffered units (km/h). And than we took an arbitrary speed point, 70 km/h, to distinguish in which areas the drives mostly where situated.\n",
    "\n",
    "The result shows that *car6* and *car7* both drive more on the higways than the other cars. Which is an relevant discovery for the future."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Driving style:**\n",
    "To test if driving style has an influence on the usage of a tire, I need to use some form of proxy. \n",
    "I have settled on the following:\n",
    "\n",
    "- engine_RPM\n",
    "\n",
    "\n",
    "\n",
    "I will first explore the data and validate it. \n",
    "With *obd_speed* specifically *car4* for a specific time *timestamp < 2016-10-04 12:40:00 UTC* Then I will device a way to make it relevantBut it will become a little more arbitrary to choose where the difference lies between 'normal' and 'aggressive' driving? Best I can come up with is take some sampling averages and use the highest average accelarators as 'aggressive' and scale back from there."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requesting query... ok.\n",
      "Job ID: job_6-mVWyTqCnJygQetA81t9n6xOOJ4\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 63829 rows.\n",
      "\n",
      "Total time taken 3.52 s.\n",
      "Finished at 2017-10-13 14:06:10.\n",
      "Requesting query... ok.\n",
      "Job ID: job_bKqw0jegbSHgv6xwiN2i0-pjQGq7\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 13553 rows.\n",
      "\n",
      "Total time taken 1.37 s.\n",
      "Finished at 2017-10-13 14:06:12.\n",
      "Requesting query... ok.\n",
      "Job ID: job_V7_a7WPruNOCnGFVIGpz3uE3wu7U\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 14042 rows.\n",
      "\n",
      "Total time taken 1.52 s.\n",
      "Finished at 2017-10-13 14:06:15.\n",
      "Requesting query... ok.\n",
      "Job ID: job_Rn7yKT4SjED0yldJKr5RaNoqPZrx\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 14020 rows.\n",
      "\n",
      "Total time taken 1.58 s.\n",
      "Finished at 2017-10-13 14:06:18.\n",
      "Requesting query... ok.\n",
      "Job ID: job_V_EC0voWlTA4Cd4bI4rWX0DuLAmy\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 58361 rows.\n",
      "\n",
      "Total time taken 3.57 s.\n",
      "Finished at 2017-10-13 14:06:22.\n",
      "Requesting query... ok.\n",
      "Job ID: job_zNnLsUi7ZBkQWE9RXhjCJCFqy31Z\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 58344 rows.\n",
      "\n",
      "Total time taken 3.03 s.\n",
      "Finished at 2017-10-13 14:06:27.\n",
      "Requesting query... ok.\n",
      "Job ID: job_QIxuiVtoFA5sVMmuJFaHDjA4lM0n\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 2; 80% done. Elapsed 7.63 s.\n",
      "  Got page: 3; 100% done. Elapsed 9.4 s.\n",
      "Got 248905 rows.\n",
      "\n",
      "Total time taken 11.19 s.\n",
      "Finished at 2017-10-13 14:06:39.\n",
      "Requesting query... ok.\n",
      "Job ID: job_CAg3648_U__dBCUlcrQE0MFirFPz\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "  Got page: 2; 80% done. Elapsed 8.24 s.\n",
      "  Got page: 3; 100% done. Elapsed 10.1 s.\n",
      "Got 248804 rows.\n",
      "\n",
      "Total time taken 11.95 s.\n",
      "Finished at 2017-10-13 14:06:52.\n",
      "Requesting query... ok.\n",
      "Job ID: job_e-tt0bR3Ch9uFBvHk2Y3W44QGawr\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 68882 rows.\n",
      "\n",
      "Total time taken 3.42 s.\n",
      "Finished at 2017-10-13 14:06:56.\n",
      "Requesting query... ok.\n",
      "Job ID: job_fxMp-98WuRaVrMFfa8bGUKMIkUsO\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 68791 rows.\n",
      "\n",
      "Total time taken 3.25 s.\n",
      "Finished at 2017-10-13 14:07:01.\n",
      "Requesting query... ok.\n",
      "Job ID: job_cqJZ9OWqDWt50xyTWwX2CgWfTtrl\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 105017 rows.\n",
      "\n",
      "Total time taken 5.07 s.\n",
      "Finished at 2017-10-13 14:07:07.\n",
      "Requesting query... ok.\n",
      "Job ID: job_IrxvBRQ4fIQrot_JX4SetudL8fnZ\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 0 rows.\n",
      "\n",
      "Total time taken 0.66 s.\n",
      "Finished at 2017-10-13 14:07:10.\n",
      "Requesting query... ok.\n",
      "Job ID: job_K3iIDS7jXeFnq8Cn_ievX3BuFRje\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 91146 rows.\n",
      "\n",
      "Total time taken 4.64 s.\n",
      "Finished at 2017-10-13 14:07:15.\n",
      "Requesting query... ok.\n",
      "Job ID: job_oX45K4x4CkOO5hVE0I1-XANUkhHg\n",
      "Query running...\n",
      "Query done.\n",
      "Cache hit.\n",
      "\n",
      "Retrieving results...\n",
      "Got 0 rows.\n",
      "\n",
      "Total time taken 0.61 s.\n",
      "Finished at 2017-10-13 14:07:17.\n"
     ]
    }
   ],
   "source": [
    "correlation_speed_and_acceleration = []\n",
    "\n",
    "\n",
    "for vehicle in fleet:\n",
    "    speed_df = pd.read_gbq(\"select timestamp, value from [headingon_params.speed_obd] where vehicle = '%s' \"%vehicle, projectid)\n",
    "    accelerator_df = pd.read_gbq(\"select timestamp, value from [headingon_params.accelerator_pedalposition_d]  where vehicle = '%s' \"%vehicle, projectid)\n",
    "    merged_df = pd.merge(accelerator_df, speed_df, on='timestamp', how='inner')\n",
    "    correlation_speed_and_acceleration.append({'vehicles': vehicle, 'correlation_speed/acceleratio': accelerator_df['value'].corr(speed_df['value'])})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>correlation_speed/acceleratio</th>\n",
       "      <th>vehicles</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.0983</td>\n",
       "      <td>car1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.5881</td>\n",
       "      <td>car2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.5042</td>\n",
       "      <td>car3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.2143</td>\n",
       "      <td>car4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.2610</td>\n",
       "      <td>car5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>car6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>car7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   correlation_speed/acceleratio vehicles\n",
       "0                        -0.0983     car1\n",
       "1                         0.5881     car2\n",
       "2                         0.5042     car3\n",
       "3                         0.2143     car4\n",
       "4                         0.2610     car5\n",
       "5                            NaN     car6\n",
       "6                            NaN     car7"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(correlation_speed_and_acceleration)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Needless to say I did not expect such inconclusive results. This means that according to the data I cannot validated the data. \n",
    "\n",
    "Since my initial idea was to take the moment of 0 speed, eg standing still which could be several seconds and compare the acceleration of the first 5 seconds *after* the last total stop. \n",
    "\n",
    "By registering those timeframes we should see a pattern per car (assuming these are all the same driver) and how fast a car is accelerating from a full stop, by indexing these moments into buckets,  1 = really slow acceleration to 5 = really fast acceleration, we could use this in the regression model concerning the wearage of the tire.\n",
    "\n",
    "However, since I cannot confidently use the acceloration parameter, I will focus in **Sprint2** on doing this with the parameter *speed_obd* which has already validated before. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}