01 Investigating and Understanding the Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DtNBZFHO3M7n"
   },
   "source": [
    "# **Taxi Cab Fares Prediction Project**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align: justify;\">\n",
    "I have just started as a data professional in a fictional data consulting firm, Automatidata (fictionally🙂). Their client, the New York City Taxi and Limousine Commission (New York City TLC), has hired the Automatidata team for its reputation in helping their clients develop data-based solutions.\n",
    "\n",
    "The team is still in the early stages of the project. Previously, I was asked to complete a project proposal by my supervisor, DeShawn Washington. I have received notice that my project proposal has been approved and that New York City TLC has given the Automatidata team access to their data. To get clear insights, New York TLC's data must be analyzed, key variables identified, and the dataset ensured it is ready for analysis.\n",
    "\n",
    "In this notebook, the data will be analyzed, identify variables, and I will ensure the dataset is ready for analysis. Also, I will look for answers to some questions.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rgSbVJvomcVa"
   },
   "source": [
    "# **Inspect and Analyze The Data**\n",
    "\n",
    "<p style=\"text-align: justify;\">\n",
    "In this task, I will examine the data provided and prepare it for analysis. I need to ensure the information is:\n",
    "\n",
    "1.   Ready to answer questions and yield insights\n",
    "\n",
    "2.   Ready for visualizations\n",
    "\n",
    "3.   Ready for future hypothesis testing and statistical methods\n",
    "<br/>   \n",
    "\n",
    "**The purpose** of this task is to investigate and understand the data provided.\n",
    "  \n",
    "**The goal** is to use a dataframe constructed within Python, perform a cursory inspection of the provided dataset, and inform team members of your findings.\n",
    "\n",
    "*This activity has three parts:*\n",
    "\n",
    "**Part 1:** Understand the Situation \n",
    "* Prepare to understand and organize the provided taxi cab dataset and information.\n",
    "\n",
    "**Part 2:** Understand the Data\n",
    "\n",
    "* Create a pandas dataframe for data learning, future exploratory data analysis (EDA), and statistical activities.\n",
    "\n",
    "* Compile summary information about the data to inform next steps.\n",
    "\n",
    "**Part 3:** Understand the Variables\n",
    "\n",
    "* Use insights from my examination of the summary data to guide deeper investigation into specific variables.\n",
    "\n",
    "\n",
    "<br/> \n",
    "After completing this task, I will complete an Executive Summary using the questions listed on the PACE Strategy Document.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Identify Data Types and Relevant Variables using Python**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MRUYfzCb4vop"
   },
   "source": [
    "<img src=\"images/Pace.png\" width=\"100\" height=\"100\" align=left>\n",
    "\n",
    "# **PACE stages**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align: justify;\">\n",
    "Throughout these project notebooks, I'll see references to the problem-solving framework PACE. The following notebook components are labeled with the respective PACE stage: Plan, Analyze, Construct, and Execute.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"images/Plan.png\" width=\"100\" height=\"100\" align=left>\n",
    "\n",
    "## PACE: **Plan**\n",
    "\n",
    "Considering the questions in my PACE Strategy Document and those below to craft my response:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pWEfG5zJV5oG"
   },
   "source": [
    "### **Task 1. Understand the Situation**\n",
    "\n",
    "* How can I best prepare to understand and organize the provided taxi cab information?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "H6ogC5ut6NSH"
   },
   "source": [
    "**Exemplar response:** \n",
    "<p style=\"text-align: justify;\">\n",
    "One can prepare to understand the information by reading the taxi cab data fields and understanding the impact of each one. Reviewing the fact sheet could also provide helpful background information. Loading data into a dataframe and reviewing the Data Dictionary, then inspect and understand the columns and other variables, finally, I will build visualization for better understanding and get a good intuition about data.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1E9Y5aC0IAA-"
   },
   "source": [
    "<img src=\"images/Analyze.png\" width=\"100\" height=\"100\" align=left>\n",
    "\n",
    "## PACE: **Analyze**\n",
    "\n",
    "Considering the questions in my PACE Strategy Document to reflect on the Analyze stage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "L6KgK-M8o3we"
   },
   "source": [
    "### **Task 2a. Build dataframe**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "D4WK_AxP_S__"
   },
   "source": [
    "Creating a pandas dataframe for data learning, and future exploratory data analysis (EDA) and statistical activities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "5yNpkDYibvXk",
    "outputId": "469269c4-de60-41a5-87db-414cdf589591"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd \n",
    "import numpy as np  \n",
    "\n",
    "df = pd.read_csv(r'D:\\Predicting Taxi Gratuities in New York City\\Datasets\\2017_Yellow_Taxi_Trip_Data.csv')\n",
    "print(\"done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gYx1emvno7U_"
   },
   "source": [
    "### **Task 2b. Understand The Data - Inspect The Data**\n",
    "\n",
    "<p style=\"text-align: justify;\">\n",
    "Consider the following two questions:\n",
    "\n",
    "**Question 1:** When reviewing the `df.info()` output, what do you notice about the different variables? Are there any null values? Are all of the variables numeric? Does anything else stand out?\n",
    "\n",
    "**Question 2:** When reviewing the `df.describe()` output, what do you notice about the distributions of each variable? Are there any questionable values?\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align: justify;\">\n",
    "**Answer 1:** The dataframe consists of 22699 observations with 18 columns (including an unnamed column), containing no null values; it includes eight columns with float datatype, two datetime columns, one object column, and seven integer columns.\n",
    "\n",
    "**Answer 2:** Regarding fare amount, the distribution is worth considering. The maximum fare amount is a much larger value ($1000) than the 25-75 percent range of values. Why are there columns with minimum negative values like fare_amount, extra, mta_tax, improvement_surcharge, and total_amount? Regarding trip distance, most rides are between 1-3 miles, but the maximum is over 33 miles.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 427
    },
    "executionInfo": {
     "elapsed": 172,
     "status": "ok",
     "timestamp": 1665171557893,
     "user": {
      "displayName": "Brian Hogan",
      "userId": "18426261707847008999"
     },
     "user_tz": 240
    },
    "id": "5HTB82SLFlpH",
    "outputId": "1e956581-5b23-4f61-a9fc-3b985593545f"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24870114</td>\n",
       "      <td>2</td>\n",
       "      <td>03/25/2017 8:55:43 AM</td>\n",
       "      <td>03/25/2017 9:09:47 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3.34</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>100</td>\n",
       "      <td>231</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.76</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>16.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35634249</td>\n",
       "      <td>1</td>\n",
       "      <td>04/11/2017 2:53:28 PM</td>\n",
       "      <td>04/11/2017 3:19:58 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>1.80</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>186</td>\n",
       "      <td>43</td>\n",
       "      <td>1</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>20.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>106203690</td>\n",
       "      <td>1</td>\n",
       "      <td>12/15/2017 7:26:56 AM</td>\n",
       "      <td>12/15/2017 7:34:08 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>262</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.45</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38942136</td>\n",
       "      <td>2</td>\n",
       "      <td>05/07/2017 1:17:59 PM</td>\n",
       "      <td>05/07/2017 1:48:14 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>3.70</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>188</td>\n",
       "      <td>97</td>\n",
       "      <td>1</td>\n",
       "      <td>20.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.39</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>27.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30841670</td>\n",
       "      <td>2</td>\n",
       "      <td>04/15/2017 11:32:20 PM</td>\n",
       "      <td>04/15/2017 11:49:03 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>4.37</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>4</td>\n",
       "      <td>112</td>\n",
       "      <td>2</td>\n",
       "      <td>16.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>17.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23345809</td>\n",
       "      <td>2</td>\n",
       "      <td>03/25/2017 8:34:11 PM</td>\n",
       "      <td>03/25/2017 8:42:11 PM</td>\n",
       "      <td>6</td>\n",
       "      <td>2.30</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>161</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>37660487</td>\n",
       "      <td>2</td>\n",
       "      <td>05/03/2017 7:04:09 PM</td>\n",
       "      <td>05/03/2017 8:03:47 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>12.83</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>79</td>\n",
       "      <td>241</td>\n",
       "      <td>1</td>\n",
       "      <td>47.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>9.86</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>59.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>69059411</td>\n",
       "      <td>2</td>\n",
       "      <td>08/15/2017 5:41:06 PM</td>\n",
       "      <td>08/15/2017 6:03:05 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>2.98</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>237</td>\n",
       "      <td>114</td>\n",
       "      <td>1</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.78</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>19.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8433159</td>\n",
       "      <td>2</td>\n",
       "      <td>02/04/2017 4:17:07 PM</td>\n",
       "      <td>02/04/2017 4:29:14 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>234</td>\n",
       "      <td>249</td>\n",
       "      <td>2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>9.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>95294817</td>\n",
       "      <td>1</td>\n",
       "      <td>11/10/2017 3:20:29 PM</td>\n",
       "      <td>11/10/2017 3:40:55 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>239</td>\n",
       "      <td>237</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>16.55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  VendorID    tpep_pickup_datetime   tpep_dropoff_datetime  \\\n",
       "0    24870114         2   03/25/2017 8:55:43 AM   03/25/2017 9:09:47 AM   \n",
       "1    35634249         1   04/11/2017 2:53:28 PM   04/11/2017 3:19:58 PM   \n",
       "2   106203690         1   12/15/2017 7:26:56 AM   12/15/2017 7:34:08 AM   \n",
       "3    38942136         2   05/07/2017 1:17:59 PM   05/07/2017 1:48:14 PM   \n",
       "4    30841670         2  04/15/2017 11:32:20 PM  04/15/2017 11:49:03 PM   \n",
       "5    23345809         2   03/25/2017 8:34:11 PM   03/25/2017 8:42:11 PM   \n",
       "6    37660487         2   05/03/2017 7:04:09 PM   05/03/2017 8:03:47 PM   \n",
       "7    69059411         2   08/15/2017 5:41:06 PM   08/15/2017 6:03:05 PM   \n",
       "8     8433159         2   02/04/2017 4:17:07 PM   02/04/2017 4:29:14 PM   \n",
       "9    95294817         1   11/10/2017 3:20:29 PM   11/10/2017 3:40:55 PM   \n",
       "\n",
       "   passenger_count  trip_distance  RatecodeID store_and_fwd_flag  \\\n",
       "0                6           3.34           1                  N   \n",
       "1                1           1.80           1                  N   \n",
       "2                1           1.00           1                  N   \n",
       "3                1           3.70           1                  N   \n",
       "4                1           4.37           1                  N   \n",
       "5                6           2.30           1                  N   \n",
       "6                1          12.83           1                  N   \n",
       "7                1           2.98           1                  N   \n",
       "8                1           1.20           1                  N   \n",
       "9                1           1.60           1                  N   \n",
       "\n",
       "   PULocationID  DOLocationID  payment_type  fare_amount  extra  mta_tax  \\\n",
       "0           100           231             1         13.0    0.0      0.5   \n",
       "1           186            43             1         16.0    0.0      0.5   \n",
       "2           262           236             1          6.5    0.0      0.5   \n",
       "3           188            97             1         20.5    0.0      0.5   \n",
       "4             4           112             2         16.5    0.5      0.5   \n",
       "5           161           236             1          9.0    0.5      0.5   \n",
       "6            79           241             1         47.5    1.0      0.5   \n",
       "7           237           114             1         16.0    1.0      0.5   \n",
       "8           234           249             2          9.0    0.0      0.5   \n",
       "9           239           237             1         13.0    0.0      0.5   \n",
       "\n",
       "   tip_amount  tolls_amount  improvement_surcharge  total_amount  \n",
       "0        2.76           0.0                    0.3         16.56  \n",
       "1        4.00           0.0                    0.3         20.80  \n",
       "2        1.45           0.0                    0.3          8.75  \n",
       "3        6.39           0.0                    0.3         27.69  \n",
       "4        0.00           0.0                    0.3         17.80  \n",
       "5        2.06           0.0                    0.3         12.36  \n",
       "6        9.86           0.0                    0.3         59.16  \n",
       "7        1.78           0.0                    0.3         19.58  \n",
       "8        0.00           0.0                    0.3          9.80  \n",
       "9        2.75           0.0                    0.3         16.55  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 175,
     "status": "ok",
     "timestamp": 1665171561166,
     "user": {
      "displayName": "Brian Hogan",
      "userId": "18426261707847008999"
     },
     "user_tz": 240
    },
    "id": "yXsNTsteu5zZ",
    "outputId": "be9a4450-7d01-49e0-de38-88a336410e02"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 22699 entries, 0 to 22698\n",
      "Data columns (total 18 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   Unnamed: 0             22699 non-null  int64  \n",
      " 1   VendorID               22699 non-null  int64  \n",
      " 2   tpep_pickup_datetime   22699 non-null  object \n",
      " 3   tpep_dropoff_datetime  22699 non-null  object \n",
      " 4   passenger_count        22699 non-null  int64  \n",
      " 5   trip_distance          22699 non-null  float64\n",
      " 6   RatecodeID             22699 non-null  int64  \n",
      " 7   store_and_fwd_flag     22699 non-null  object \n",
      " 8   PULocationID           22699 non-null  int64  \n",
      " 9   DOLocationID           22699 non-null  int64  \n",
      " 10  payment_type           22699 non-null  int64  \n",
      " 11  fare_amount            22699 non-null  float64\n",
      " 12  extra                  22699 non-null  float64\n",
      " 13  mta_tax                22699 non-null  float64\n",
      " 14  tip_amount             22699 non-null  float64\n",
      " 15  tolls_amount           22699 non-null  float64\n",
      " 16  improvement_surcharge  22699 non-null  float64\n",
      " 17  total_amount           22699 non-null  float64\n",
      "dtypes: float64(8), int64(7), object(3)\n",
      "memory usage: 3.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>5.675849e+07</td>\n",
       "      <td>3.274493e+07</td>\n",
       "      <td>12127.0</td>\n",
       "      <td>28520556.00</td>\n",
       "      <td>56731504.00</td>\n",
       "      <td>85374524.00</td>\n",
       "      <td>1.134863e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VendorID</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.556236e+00</td>\n",
       "      <td>4.968384e-01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>passenger_count</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.642319e+00</td>\n",
       "      <td>1.285231e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>6.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trip_distance</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>2.913313e+00</td>\n",
       "      <td>3.653171e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1.61</td>\n",
       "      <td>3.06</td>\n",
       "      <td>3.396000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RatecodeID</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.043394e+00</td>\n",
       "      <td>7.083909e-01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>9.900000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PULocationID</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.624124e+02</td>\n",
       "      <td>6.663337e+01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>114.00</td>\n",
       "      <td>162.00</td>\n",
       "      <td>233.00</td>\n",
       "      <td>2.650000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DOLocationID</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.615280e+02</td>\n",
       "      <td>7.013969e+01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>112.00</td>\n",
       "      <td>162.00</td>\n",
       "      <td>233.00</td>\n",
       "      <td>2.650000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>payment_type</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.336887e+00</td>\n",
       "      <td>4.962111e-01</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fare_amount</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.302663e+01</td>\n",
       "      <td>1.324379e+01</td>\n",
       "      <td>-120.0</td>\n",
       "      <td>6.50</td>\n",
       "      <td>9.50</td>\n",
       "      <td>14.50</td>\n",
       "      <td>9.999900e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>extra</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>3.332746e-01</td>\n",
       "      <td>4.630966e-01</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.50</td>\n",
       "      <td>4.500000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mta_tax</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>4.974448e-01</td>\n",
       "      <td>3.946499e-02</td>\n",
       "      <td>-0.5</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.50</td>\n",
       "      <td>5.000000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tip_amount</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.835781e+00</td>\n",
       "      <td>2.800626e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.35</td>\n",
       "      <td>2.45</td>\n",
       "      <td>2.000000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tolls_amount</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>3.125415e-01</td>\n",
       "      <td>1.399212e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.910000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>2.995506e-01</td>\n",
       "      <td>1.567274e-02</td>\n",
       "      <td>-0.3</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.30</td>\n",
       "      <td>3.000000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total_amount</th>\n",
       "      <td>22699.0</td>\n",
       "      <td>1.631050e+01</td>\n",
       "      <td>1.609730e+01</td>\n",
       "      <td>-120.3</td>\n",
       "      <td>8.75</td>\n",
       "      <td>11.80</td>\n",
       "      <td>17.80</td>\n",
       "      <td>1.200290e+03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         count          mean           std      min  \\\n",
       "Unnamed: 0             22699.0  5.675849e+07  3.274493e+07  12127.0   \n",
       "VendorID               22699.0  1.556236e+00  4.968384e-01      1.0   \n",
       "passenger_count        22699.0  1.642319e+00  1.285231e+00      0.0   \n",
       "trip_distance          22699.0  2.913313e+00  3.653171e+00      0.0   \n",
       "RatecodeID             22699.0  1.043394e+00  7.083909e-01      1.0   \n",
       "PULocationID           22699.0  1.624124e+02  6.663337e+01      1.0   \n",
       "DOLocationID           22699.0  1.615280e+02  7.013969e+01      1.0   \n",
       "payment_type           22699.0  1.336887e+00  4.962111e-01      1.0   \n",
       "fare_amount            22699.0  1.302663e+01  1.324379e+01   -120.0   \n",
       "extra                  22699.0  3.332746e-01  4.630966e-01     -1.0   \n",
       "mta_tax                22699.0  4.974448e-01  3.946499e-02     -0.5   \n",
       "tip_amount             22699.0  1.835781e+00  2.800626e+00      0.0   \n",
       "tolls_amount           22699.0  3.125415e-01  1.399212e+00      0.0   \n",
       "improvement_surcharge  22699.0  2.995506e-01  1.567274e-02     -0.3   \n",
       "total_amount           22699.0  1.631050e+01  1.609730e+01   -120.3   \n",
       "\n",
       "                               25%          50%          75%           max  \n",
       "Unnamed: 0             28520556.00  56731504.00  85374524.00  1.134863e+08  \n",
       "VendorID                      1.00         2.00         2.00  2.000000e+00  \n",
       "passenger_count               1.00         1.00         2.00  6.000000e+00  \n",
       "trip_distance                 0.99         1.61         3.06  3.396000e+01  \n",
       "RatecodeID                    1.00         1.00         1.00  9.900000e+01  \n",
       "PULocationID                114.00       162.00       233.00  2.650000e+02  \n",
       "DOLocationID                112.00       162.00       233.00  2.650000e+02  \n",
       "payment_type                  1.00         1.00         2.00  4.000000e+00  \n",
       "fare_amount                   6.50         9.50        14.50  9.999900e+02  \n",
       "extra                         0.00         0.00         0.50  4.500000e+00  \n",
       "mta_tax                       0.50         0.50         0.50  5.000000e-01  \n",
       "tip_amount                    0.00         1.35         2.45  2.000000e+02  \n",
       "tolls_amount                  0.00         0.00         0.00  1.910000e+01  \n",
       "improvement_surcharge         0.30         0.30         0.30  3.000000e-01  \n",
       "total_amount                  8.75        11.80        17.80  1.200290e+03  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BMNnIoc51_1N"
   },
   "source": [
    "### **Task 2c. Understand The Data - Investigate The Variables**\n",
    "\n",
    "Sorting and interpreting the data table for two variables:`trip_distance` and `total_amount`.\n",
    "\n",
    "**Questions to be considered:**\n",
    "\n",
    "**Question 1:** Sort your first variable (`trip_distance`) from maximum to minimum value, do the values seem normal?\n",
    "\n",
    "**Question 2:** Sort by your second variable (`total_amount`), are any values unusual?\n",
    "\n",
    "**Question 3:** Are the resulting rows similar for both sorts? Why or why not?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Response:**\n",
    "It is expected that if `trip_distance` increases, the `total_amount` increases. Some data points don't meet this concept. After sorting by `total_amount`, we can figure out that there are some zero-values within column `trip_distance` with corresponding values in `total_amount` columns which does not make sense.\n",
    "\n",
    "**Question 1:** The values align with our earlier data discovery, where we noticed that the longest rides are approximately 33 miles.\n",
    "\n",
    "**Question 2:** Yes, the first two values are significantly higher than the others. \n",
    "\n",
    "**Question 3:** The most expensive rides are not necessarily the longest ones. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9280</th>\n",
       "      <td>51810714</td>\n",
       "      <td>2</td>\n",
       "      <td>06/18/2017 11:33:25 PM</td>\n",
       "      <td>06/19/2017 12:12:38 AM</td>\n",
       "      <td>2</td>\n",
       "      <td>33.96</td>\n",
       "      <td>5</td>\n",
       "      <td>N</td>\n",
       "      <td>132</td>\n",
       "      <td>265</td>\n",
       "      <td>2</td>\n",
       "      <td>150.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>150.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13861</th>\n",
       "      <td>40523668</td>\n",
       "      <td>2</td>\n",
       "      <td>05/19/2017 8:20:21 AM</td>\n",
       "      <td>05/19/2017 9:20:30 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>33.92</td>\n",
       "      <td>5</td>\n",
       "      <td>N</td>\n",
       "      <td>229</td>\n",
       "      <td>265</td>\n",
       "      <td>1</td>\n",
       "      <td>200.01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>51.64</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.3</td>\n",
       "      <td>258.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6064</th>\n",
       "      <td>49894023</td>\n",
       "      <td>2</td>\n",
       "      <td>06/13/2017 12:30:22 PM</td>\n",
       "      <td>06/13/2017 1:37:51 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>32.72</td>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>107.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.50</td>\n",
       "      <td>16.26</td>\n",
       "      <td>0.3</td>\n",
       "      <td>179.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10291</th>\n",
       "      <td>76319330</td>\n",
       "      <td>2</td>\n",
       "      <td>09/11/2017 11:41:04 AM</td>\n",
       "      <td>09/11/2017 12:18:58 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>31.95</td>\n",
       "      <td>4</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>265</td>\n",
       "      <td>2</td>\n",
       "      <td>131.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>131.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>94052446</td>\n",
       "      <td>2</td>\n",
       "      <td>11/06/2017 8:30:50 PM</td>\n",
       "      <td>11/07/2017 12:00:00 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>30.83</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>132</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>80.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>18.56</td>\n",
       "      <td>11.52</td>\n",
       "      <td>0.3</td>\n",
       "      <td>111.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18130</th>\n",
       "      <td>90375786</td>\n",
       "      <td>1</td>\n",
       "      <td>10/26/2017 2:45:01 PM</td>\n",
       "      <td>10/26/2017 4:12:49 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>30.50</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>132</td>\n",
       "      <td>220</td>\n",
       "      <td>1</td>\n",
       "      <td>90.50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>19.85</td>\n",
       "      <td>8.16</td>\n",
       "      <td>0.3</td>\n",
       "      <td>119.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5792</th>\n",
       "      <td>68023798</td>\n",
       "      <td>2</td>\n",
       "      <td>08/11/2017 2:14:01 PM</td>\n",
       "      <td>08/11/2017 3:17:31 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>30.33</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>132</td>\n",
       "      <td>158</td>\n",
       "      <td>1</td>\n",
       "      <td>52.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>14.64</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.3</td>\n",
       "      <td>73.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15350</th>\n",
       "      <td>77309977</td>\n",
       "      <td>2</td>\n",
       "      <td>09/14/2017 1:44:44 PM</td>\n",
       "      <td>09/14/2017 2:34:29 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>28.23</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>13</td>\n",
       "      <td>132</td>\n",
       "      <td>1</td>\n",
       "      <td>52.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.40</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.3</td>\n",
       "      <td>62.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10302</th>\n",
       "      <td>43431843</td>\n",
       "      <td>1</td>\n",
       "      <td>05/15/2017 8:11:34 AM</td>\n",
       "      <td>05/15/2017 9:03:16 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>28.20</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>90</td>\n",
       "      <td>132</td>\n",
       "      <td>1</td>\n",
       "      <td>52.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>11.71</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.3</td>\n",
       "      <td>70.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2592</th>\n",
       "      <td>51094874</td>\n",
       "      <td>2</td>\n",
       "      <td>06/16/2017 6:51:20 PM</td>\n",
       "      <td>06/16/2017 7:41:42 PM</td>\n",
       "      <td>1</td>\n",
       "      <td>27.97</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>261</td>\n",
       "      <td>132</td>\n",
       "      <td>2</td>\n",
       "      <td>52.00</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.76</td>\n",
       "      <td>0.3</td>\n",
       "      <td>63.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  VendorID    tpep_pickup_datetime   tpep_dropoff_datetime  \\\n",
       "9280     51810714         2  06/18/2017 11:33:25 PM  06/19/2017 12:12:38 AM   \n",
       "13861    40523668         2   05/19/2017 8:20:21 AM   05/19/2017 9:20:30 AM   \n",
       "6064     49894023         2  06/13/2017 12:30:22 PM   06/13/2017 1:37:51 PM   \n",
       "10291    76319330         2  09/11/2017 11:41:04 AM  09/11/2017 12:18:58 PM   \n",
       "29       94052446         2   11/06/2017 8:30:50 PM  11/07/2017 12:00:00 AM   \n",
       "18130    90375786         1   10/26/2017 2:45:01 PM   10/26/2017 4:12:49 PM   \n",
       "5792     68023798         2   08/11/2017 2:14:01 PM   08/11/2017 3:17:31 PM   \n",
       "15350    77309977         2   09/14/2017 1:44:44 PM   09/14/2017 2:34:29 PM   \n",
       "10302    43431843         1   05/15/2017 8:11:34 AM   05/15/2017 9:03:16 AM   \n",
       "2592     51094874         2   06/16/2017 6:51:20 PM   06/16/2017 7:41:42 PM   \n",
       "\n",
       "       passenger_count  trip_distance  RatecodeID store_and_fwd_flag  \\\n",
       "9280                 2          33.96           5                  N   \n",
       "13861                1          33.92           5                  N   \n",
       "6064                 1          32.72           3                  N   \n",
       "10291                1          31.95           4                  N   \n",
       "29                   1          30.83           1                  N   \n",
       "18130                1          30.50           1                  N   \n",
       "5792                 1          30.33           2                  N   \n",
       "15350                1          28.23           2                  N   \n",
       "10302                1          28.20           2                  N   \n",
       "2592                 1          27.97           2                  N   \n",
       "\n",
       "       PULocationID  DOLocationID  payment_type  fare_amount  extra  mta_tax  \\\n",
       "9280            132           265             2       150.00    0.0      0.0   \n",
       "13861           229           265             1       200.01    0.0      0.5   \n",
       "6064            138             1             1       107.00    0.0      0.0   \n",
       "10291           138           265             2       131.00    0.0      0.5   \n",
       "29              132            23             1        80.00    0.5      0.5   \n",
       "18130           132           220             1        90.50    0.0      0.5   \n",
       "5792            132           158             1        52.00    0.0      0.5   \n",
       "15350            13           132             1        52.00    0.0      0.5   \n",
       "10302            90           132             1        52.00    0.0      0.5   \n",
       "2592            261           132             2        52.00    4.5      0.5   \n",
       "\n",
       "       tip_amount  tolls_amount  improvement_surcharge  total_amount  \n",
       "9280         0.00          0.00                    0.3        150.30  \n",
       "13861       51.64          5.76                    0.3        258.21  \n",
       "6064        55.50         16.26                    0.3        179.06  \n",
       "10291        0.00          0.00                    0.3        131.80  \n",
       "29          18.56         11.52                    0.3        111.38  \n",
       "18130       19.85          8.16                    0.3        119.31  \n",
       "5792        14.64          5.76                    0.3         73.20  \n",
       "15350        4.40          5.76                    0.3         62.96  \n",
       "10302       11.71          5.76                    0.3         70.27  \n",
       "2592         0.00          5.76                    0.3         63.06  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort the data by trip distance from maximum to minimum value\n",
    "df_sort = df.sort_values(by=['trip_distance'],ascending=False)\n",
    "df_sort.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8476     1200.29\n",
       "20312     450.30\n",
       "13861     258.21\n",
       "12511     233.74\n",
       "15474     211.80\n",
       "6064      179.06\n",
       "16379     157.06\n",
       "3582      152.30\n",
       "11269     151.82\n",
       "9280      150.30\n",
       "1928      137.80\n",
       "10291     131.80\n",
       "6708      126.00\n",
       "11608     123.30\n",
       "908       121.56\n",
       "7281      120.96\n",
       "18130     119.31\n",
       "13621     115.94\n",
       "13359     111.95\n",
       "29        111.38\n",
       "Name: total_amount, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort the data by total amount and print the top 20 values\n",
    "total_amount_sorted = df.sort_values(\n",
    "    ['total_amount'], ascending=False)['total_amount']\n",
    "total_amount_sorted.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14283      0.31\n",
       "19067      0.30\n",
       "10506      0.00\n",
       "5722       0.00\n",
       "4402       0.00\n",
       "22566      0.00\n",
       "1646      -3.30\n",
       "18565     -3.80\n",
       "314       -3.80\n",
       "5758      -3.80\n",
       "5448      -4.30\n",
       "4423      -4.30\n",
       "10281     -4.30\n",
       "8204      -4.80\n",
       "20317     -4.80\n",
       "11204     -5.30\n",
       "14714     -5.30\n",
       "17602     -5.80\n",
       "20698     -5.80\n",
       "12944   -120.30\n",
       "Name: total_amount, dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort the data by total amount and print the bottom 20 values\n",
    "total_amount_sorted.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    15265\n",
       "2     7267\n",
       "3      121\n",
       "4       46\n",
       "Name: payment_type, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many of each payment type are represented in the data?\n",
    "df['payment_type'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "According to the data dictionary, the payment method was encoded as follows:\n",
    "\n",
    "`1` = Credit card\n",
    "`2` = Cash\n",
    "`3` = No charge\n",
    "`4` = Dispute\n",
    "`5` = Unknown\n",
    "`6` = Voided trip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The average tip for trips paid for with credit card is  2.73\n",
      "The average tip for trips paid for with cash is   0.0\n"
     ]
    }
   ],
   "source": [
    "# What is the average tip for trips paid for with credit card?\n",
    "avg_tip_credit_card = df[df['payment_type']==1]['tip_amount'].mean()\n",
    "print('The average tip for trips paid for with credit card is {:>5.2f}'.format(avg_tip_credit_card))\n",
    "\n",
    "# What is the average tip for trips paid for with cash?\n",
    "avg_tip_cash = df[df['payment_type']==2]['tip_amount'].mean()\n",
    "print('The average tip for trips paid for with cash is {:>5}'.format(avg_tip_cash))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    12626\n",
       "1    10073\n",
       "Name: VendorID, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many times is each vendor ID represented in the data?\n",
    "df['VendorID'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VendorID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16.298119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16.320382</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          total_amount\n",
       "VendorID              \n",
       "1            16.298119\n",
       "2            16.320382"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What is the mean total amount for each vendor?\n",
    "df.groupby(['VendorID']).mean(numeric_only=True)[['total_amount']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter the data for credit card payments only\n",
    "credit_card = df[df['payment_type']==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    10977\n",
       "2     2168\n",
       "5      775\n",
       "3      600\n",
       "6      451\n",
       "4      267\n",
       "0       27\n",
       "Name: passenger_count, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter the credit-card-only data for passenger count only\n",
    "credit_card['passenger_count'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tip_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>passenger_count</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.610370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.714681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.829949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.726800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.607753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.762645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2.643326</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 tip_amount\n",
       "passenger_count            \n",
       "0                  2.610370\n",
       "1                  2.714681\n",
       "2                  2.829949\n",
       "3                  2.726800\n",
       "4                  2.607753\n",
       "5                  2.762645\n",
       "6                  2.643326"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the average tip amount for each passenger count (credit card payments only)\n",
    "credit_card.groupby(['passenger_count']).mean(numeric_only=True)[['tip_amount']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tF_82VLgzrQm"
   },
   "source": [
    "<img src=\"images/Construct.png\" width=\"100\" height=\"100\" align=left>\n",
    "\n",
    "## PACE: **Construct**\n",
    "\n",
    "**Note**: The Construct stage does not apply to this workflow. The PACE framework can be adapted to fit the specific requirements of any project.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BMHV86A6zrQo"
   },
   "source": [
    "<img src=\"images/Execute.png\" width=\"100\" height=\"100\" align=left>\n",
    "\n",
    "## PACE: **Execute**\n",
    "\n",
    "Consider the questions in my PACE Strategy Document and the following questions to craft my response.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "u3HxcMZgz6iW"
   },
   "source": [
    "### **What can I summarize for DeShawn and the Data Team?**\n",
    "<p style=\"text-align: justify;\">\n",
    "\n",
    "*This notebook should contain data that can address Luana's requests (Senior Data Analyst). Which two variables are most helpful for building a predictive model for the client: NYC TLC?*\n",
    "</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Response:**\n",
    "\n",
    "<p style=\"text-align: justify;\">\n",
    "After looking at the dataset, the two variables that are most likely to help build a predictive model for taxi ride fares are `total_amount` and `trip_distance` because those variables show a picture of a taxi cab ride.\n",
    "</p>"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "u3HxcMZgz6iW"
   ],
   "provenance": []
  },
  "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}