main.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Understanding NYC Taxi Trip Data\n",
    "\n",
    "The New York City Taxi and Limousine Commission (TLC) collect extensive data concerning taxi trips within the city. This data provides a rich resource for analysis and modeling, aiding in various business and urban planning decisions. In this article, we delve into leveraging Jupyter Notebook to dissect and organize this data into meaningful Dimension and Fact tables for analytics purposes.\n",
    "\n",
    "Dataset Download Link: https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page\n",
    "\n",
    "Note: For the purpose of this article we will be using dataset from year 2023 (Yellow Taxi)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction to the Data\n",
    "\n",
    "The provided dataset contains detailed records of taxi trips from the year 2023 trips. It captures pivotal information such as pick-up and drop-off dates/times, trip distances, fare details, passenger counts, and more. It's important to note that this data was collected and supplied to the TLC by technology providers under authorized programs. Therefore, while being valuable, the accuracy and completeness of the dataset might not be guaranteed.\n",
    "\n",
    "\n",
    "## Data Dictionary or Data Catalog\n",
    "Below image contains all the data points we will getting and their descriptions from the dataset.\n",
    "\n",
    "<img src=\"nyc_taxi_trip_data/images/data dictionary.png\" style=\"width:auto; height:600px\" >"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Process Flow of the Pipeline\n",
    "\n",
    "### Loading and Data Preprocessing \n",
    "The analysis starts with loading the data into a Pandas DataFrame within the Jupyter Notebook environment. This initial step involves crucial data preprocessing tasks such as:\n",
    "\n",
    "- Checking data types and ensuring appropriate formats for date-time columns.\n",
    "- Removing duplicate entries to maintain data integrity.\n",
    "- Generating a unique Trip ID for each trip record for identification purposes. (also known as surrogate key)\n",
    "\n",
    "### Constructing Dimension Tables\n",
    "Once, the data is fully cleaned. We creates dimension tables for different entities\n",
    "\n",
    "- **datetime_dim:** This table captures datetime-related attributes such as pickup and drop-off hours, days, months, years, and weekdays.\n",
    "- **passenger_count_dim:** Contains passenger count information.\n",
    "- **trip_distance_dim:** Holds details about trip distances.\n",
    "- **rate_code_dim:** Provides insights into different rate codes and their corresponding names.\n",
    "- **location_dim:** A look-up table for location which maps locationID for pickup/dropff into location_name\n",
    "- **pickup_location_dim:** Stores data about pickup locations.\n",
    "- **dropoff_location_dim:** Holds data regarding drop-off locations.\n",
    "- **payment_type_dim:** Contains information about various payment types.\n",
    "\n",
    "### Building the Fact Table\n",
    "\n",
    "The Fact table, the heart of a dimensional model, is constructed by merging various Dimension tables. It encapsulates essential information such as trip details, fare amounts, payment details, dropoff_location_dim, pickup_location_dim, passenger details and more.\n",
    "\n",
    "### Diagram of Data Model\n",
    "\n",
    "\n",
    "<img src=\"nyc_taxi_trip_data/images/diagram.png\" style=\"width:auto;height:500px\">\n",
    "\n",
    "### Some Complex Analytics using Our Data Model\n",
    "\n",
    "We will write some analytical queries using duckdb to query data from our data model. This will present and help us identify different usecases. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import duckdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "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>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",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:32:10</td>\n",
       "      <td>2023-01-01 00:40:36</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.97</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>161</td>\n",
       "      <td>141</td>\n",
       "      <td>2</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.30</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:55:08</td>\n",
       "      <td>2023-01-01 01:01:27</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>43</td>\n",
       "      <td>237</td>\n",
       "      <td>1</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.90</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:25:04</td>\n",
       "      <td>2023-01-01 00:37:49</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.51</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>48</td>\n",
       "      <td>238</td>\n",
       "      <td>1</td>\n",
       "      <td>14.9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34.90</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-01 00:03:48</td>\n",
       "      <td>2023-01-01 00:13:25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.90</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>12.1</td>\n",
       "      <td>7.25</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:10:29</td>\n",
       "      <td>2023-01-01 00:21:19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.43</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>107</td>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>11.4</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.68</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0         2  2023-01-01 00:32:10   2023-01-01 00:40:36              1.0   \n",
       "1         2  2023-01-01 00:55:08   2023-01-01 01:01:27              1.0   \n",
       "2         2  2023-01-01 00:25:04   2023-01-01 00:37:49              1.0   \n",
       "3         1  2023-01-01 00:03:48   2023-01-01 00:13:25              0.0   \n",
       "4         2  2023-01-01 00:10:29   2023-01-01 00:21:19              1.0   \n",
       "\n",
       "   trip_distance  RatecodeID store_and_fwd_flag  PULocationID  DOLocationID  \\\n",
       "0           0.97         1.0                  N           161           141   \n",
       "1           1.10         1.0                  N            43           237   \n",
       "2           2.51         1.0                  N            48           238   \n",
       "3           1.90         1.0                  N           138             7   \n",
       "4           1.43         1.0                  N           107            79   \n",
       "\n",
       "   payment_type  fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0             2          9.3   1.00      0.5        0.00           0.0   \n",
       "1             1          7.9   1.00      0.5        4.00           0.0   \n",
       "2             1         14.9   1.00      0.5       15.00           0.0   \n",
       "3             1         12.1   7.25      0.5        0.00           0.0   \n",
       "4             1         11.4   1.00      0.5        3.28           0.0   \n",
       "\n",
       "   improvement_surcharge  total_amount  congestion_surcharge  airport_fee  \n",
       "0                    1.0         14.30                   2.5         0.00  \n",
       "1                    1.0         16.90                   2.5         0.00  \n",
       "2                    1.0         34.90                   2.5         0.00  \n",
       "3                    1.0         20.85                   0.0         1.25  \n",
       "4                    1.0         19.68                   2.5         0.00  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset Contains: 3066766 rows,  19 columns\n"
     ]
    }
   ],
   "source": [
    "#load data from parquet file\n",
    "df =pd.read_parquet('./nyc_taxi_trip_data/2023/2023.parquet')\n",
    "\n",
    "display(df.head())\n",
    "\n",
    "print(f'Dataset Contains: {df.shape[0]} rows,  {df.shape[1]} columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Some details about parquet file format\n",
    "\n",
    "CSV files store data in rows with values separated by delimiters, while JSON uses key-value pairs for structured data. Parquet, however, arranges data by columns, allowing more efficient storage and retrieval of column-specific information. It employs advanced compression techniques and encoding methods, like Run-Length and Dictionary Encoding, resulting in smaller file sizes and faster query performance. This optimized structure makes Parquet well-suited for analytical workloads, big data processing, and data warehousing, as it significantly reduces storage needs and enhances processing speeds compared to CSV and JSON formats.\n",
    "\n",
    "personal note:\n",
    "I will always prefer working with parquet format for my analytical projects, even if the source file csv/json, i will always try to convert it into parquet format.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing Steps &  Surrogate Key\n",
    "We will perform a basic preprocessing step \n",
    " - drop duplicates\n",
    " - add a surrogate key \n",
    "\n",
    "Each record in dataset represents a trip. we can apply complex hashing (md5, sha256) of concatenating multiple columns to represent a unique record for each trip or we can just assign the monotically increasing id. Both can work, i will be applying monotonically increasing id. Pandas provide this out of the box when we load in the dataframe. it is called `index`.\n",
    "\n",
    "just for the reference, if some of the folks wants to take hashing route. one can apply the logic as below. please note it's gonna be a little slow\n",
    "\n",
    "``` python\n",
    "    import hashlib\n",
    "    \n",
    "    def genMd5Hash(*args) -> str:\n",
    "        string = ''.join([str(arg) for arg in args])\n",
    "        \"\"\" Function to generate md5 when given a string \"\"\"\n",
    "        return hashlib.md5(string.encode())\n",
    "    df['trip_id'] = df.apply(genMd5Hash,axis=1)\n",
    "    # Just adding for sequence. the trip_id columns would be located at the end, so i will filtered out all the cols except `trip_id`\n",
    "    cols = list(filter(lambda x: x != 'trip_id', df.columns))\n",
    "    # combining array in this way ensures `trip_id` is always on the first columns, just for readability purposes\n",
    "    cols = ['trip_id'] + cols\n",
    "    \n",
    "    df[cols].head()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates()\n",
    "# It will insert `trip_id` to the first location in columns\n",
    "df.insert(0,'trip_id',df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "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>trip_id</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",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:32:10</td>\n",
       "      <td>2023-01-01 00:40:36</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.97</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>161</td>\n",
       "      <td>141</td>\n",
       "      <td>2</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.30</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:55:08</td>\n",
       "      <td>2023-01-01 01:01:27</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>43</td>\n",
       "      <td>237</td>\n",
       "      <td>1</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.90</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:25:04</td>\n",
       "      <td>2023-01-01 00:37:49</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.51</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>48</td>\n",
       "      <td>238</td>\n",
       "      <td>1</td>\n",
       "      <td>14.9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34.90</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-01 00:03:48</td>\n",
       "      <td>2023-01-01 00:13:25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.90</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>12.1</td>\n",
       "      <td>7.25</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:10:29</td>\n",
       "      <td>2023-01-01 00:21:19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.43</td>\n",
       "      <td>1.0</td>\n",
       "      <td>N</td>\n",
       "      <td>107</td>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>11.4</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.68</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trip_id  VendorID tpep_pickup_datetime tpep_dropoff_datetime  \\\n",
       "0        0         2  2023-01-01 00:32:10   2023-01-01 00:40:36   \n",
       "1        1         2  2023-01-01 00:55:08   2023-01-01 01:01:27   \n",
       "2        2         2  2023-01-01 00:25:04   2023-01-01 00:37:49   \n",
       "3        3         1  2023-01-01 00:03:48   2023-01-01 00:13:25   \n",
       "4        4         2  2023-01-01 00:10:29   2023-01-01 00:21:19   \n",
       "\n",
       "   passenger_count  trip_distance  RatecodeID store_and_fwd_flag  \\\n",
       "0              1.0           0.97         1.0                  N   \n",
       "1              1.0           1.10         1.0                  N   \n",
       "2              1.0           2.51         1.0                  N   \n",
       "3              0.0           1.90         1.0                  N   \n",
       "4              1.0           1.43         1.0                  N   \n",
       "\n",
       "   PULocationID  DOLocationID  payment_type  fare_amount  extra  mta_tax  \\\n",
       "0           161           141             2          9.3   1.00      0.5   \n",
       "1            43           237             1          7.9   1.00      0.5   \n",
       "2            48           238             1         14.9   1.00      0.5   \n",
       "3           138             7             1         12.1   7.25      0.5   \n",
       "4           107            79             1         11.4   1.00      0.5   \n",
       "\n",
       "   tip_amount  tolls_amount  improvement_surcharge  total_amount  \\\n",
       "0        0.00           0.0                    1.0         14.30   \n",
       "1        4.00           0.0                    1.0         16.90   \n",
       "2       15.00           0.0                    1.0         34.90   \n",
       "3        0.00           0.0                    1.0         20.85   \n",
       "4        3.28           0.0                    1.0         19.68   \n",
       "\n",
       "   congestion_surcharge  airport_fee  \n",
       "0                   2.5         0.00  \n",
       "1                   2.5         0.00  \n",
       "2                   2.5         0.00  \n",
       "3                   0.0         1.25  \n",
       "4                   2.5         0.00  "
      ]
     },
     "execution_count": 341,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Modelling\n",
    "\n",
    "Now preprocessing of the data is completed. The next step would be to create data model. \n",
    "\n",
    "Some definations of `data model` and `dimnensions` in a layman term\n",
    "\n",
    "<dl>\n",
    "  <dt><strong>Data Model</strong></dt> <br>\n",
    "  <dd>A data model is like a blueprint or a plan that defines how data is organized, structured, and related within a database or system. <br><br>\n",
    "   <strong>\n",
    "      Why do we need it?\n",
    "    </strong>\n",
    "    </br>\n",
    "     <p>It helps in understanding the different types of data, their relationships, and how they can be accessed or retrieved.</p>\n",
    "     </br>\n",
    "     <strong>\n",
    "      Analogy\n",
    "      </strong>\n",
    "      <p>Imagine you're building a house. Before construction begins, you have architectural plans that detail where rooms will be located, how they connect, and what materials will be used. Similarly, a data model outlines how data will be stored, what types of data are needed, and how different pieces of data relate to each other.</p>\n",
    "  </dd>\n",
    "  <br>\n",
    "  <dt><strong>Dimension</strong></dt> <br>\n",
    "  <dd>A dimension in a model refers to a way of organizing or categorizing data to help understand and analyze information more effectively. It's like grouping or labeling data based on specific attributes or characteristics.<br><br>\n",
    "   <strong>\n",
    "      Why do we need it?\n",
    "    </strong>\n",
    "    </br>\n",
    "     <p>Dimensions provide context and structure to data, making it easier to study and gain insights. They help in organizing information in a meaningful way, allowing users to ask questions and perform analyses that can lead to better decision-making.</p>\n",
    "      <strong>\n",
    "        Analogy\n",
    "      </strong>\n",
    "      <p>In a sales model, time can be a dimension. You might break down time into years, months, and days to analyze sales performance over different time periods. Similarly, in a customer model, age or location can be dimensions to categorize customers based on their age groups or geographic regions.</p>\n",
    "\n",
    "  </dd>\n",
    "</dl>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `datetime_dim` dimension table\n",
    "\n",
    "Now lets build the datetime dimension as depicted in the picture. As you can see, i have broken down the time to  minute, hour, day, weekday, month, year for each pickup and dropoff. This is helpful for analysing the information. An analyst is now able to gain visibility upto minute level analytics of trips. This is also called `Granularity` of data. \n",
    "\n",
    "### Some notes about Granularity\n",
    "\n",
    "Granularity in simple terms means `The level of detail at which the attributes and characteristics (columns) of data are defined` so the more we dive deeper the granularity of data becomes (individual level info) and the more we aggregate data the granularity becomes higher (department level info). High Granularity can be related to as seeing a whole picture (bigger view) and low level granularity means seeing individual elements of the whole picture. e.g seeing an individual salary would be lower granular and seeing the department's average salary would be high granular level of detail.\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/datetime_dim.png)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "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>datetime_id</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>pick_hour</th>\n",
       "      <th>pick_day</th>\n",
       "      <th>pick_month</th>\n",
       "      <th>pick_year</th>\n",
       "      <th>pick_weekday</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>drop_hour</th>\n",
       "      <th>drop_day</th>\n",
       "      <th>drop_month</th>\n",
       "      <th>drop_year</th>\n",
       "      <th>drop_weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2023-01-01 00:32:10</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-01-01 00:40:36</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-01 00:55:08</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-01-01 01:01:27</td>\n",
       "      <td>1</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-01-01 00:25:04</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-01-01 00:37:49</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2023-01-01 00:03:48</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-01-01 00:13:25</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2023-01-01 00:10:29</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "      <td>2023-01-01 00:21:19</td>\n",
       "      <td>0</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066761</th>\n",
       "      <td>3066761</td>\n",
       "      <td>2023-01-31 23:58:34</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-02-01 00:12:33</td>\n",
       "      <td>0</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>2</td>\n",
       "      <td>2023</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066762</th>\n",
       "      <td>3066762</td>\n",
       "      <td>2023-01-31 23:31:09</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-31 23:50:36</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066763</th>\n",
       "      <td>3066763</td>\n",
       "      <td>2023-01-31 23:01:05</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-31 23:25:36</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066764</th>\n",
       "      <td>3066764</td>\n",
       "      <td>2023-01-31 23:40:00</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-31 23:53:00</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066765</th>\n",
       "      <td>3066765</td>\n",
       "      <td>2023-01-31 23:07:32</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "      <td>2023-01-31 23:21:56</td>\n",
       "      <td>23</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3066766 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         datetime_id tpep_pickup_datetime  pick_hour pick_day  pick_month  \\\n",
       "0                  0  2023-01-01 00:32:10          0   Sunday           1   \n",
       "1                  1  2023-01-01 00:55:08          0   Sunday           1   \n",
       "2                  2  2023-01-01 00:25:04          0   Sunday           1   \n",
       "3                  3  2023-01-01 00:03:48          0   Sunday           1   \n",
       "4                  4  2023-01-01 00:10:29          0   Sunday           1   \n",
       "...              ...                  ...        ...      ...         ...   \n",
       "3066761      3066761  2023-01-31 23:58:34         23  Tuesday           1   \n",
       "3066762      3066762  2023-01-31 23:31:09         23  Tuesday           1   \n",
       "3066763      3066763  2023-01-31 23:01:05         23  Tuesday           1   \n",
       "3066764      3066764  2023-01-31 23:40:00         23  Tuesday           1   \n",
       "3066765      3066765  2023-01-31 23:07:32         23  Tuesday           1   \n",
       "\n",
       "         pick_year  pick_weekday tpep_dropoff_datetime  drop_hour   drop_day  \\\n",
       "0             2023             6   2023-01-01 00:40:36          0     Sunday   \n",
       "1             2023             6   2023-01-01 01:01:27          1     Sunday   \n",
       "2             2023             6   2023-01-01 00:37:49          0     Sunday   \n",
       "3             2023             6   2023-01-01 00:13:25          0     Sunday   \n",
       "4             2023             6   2023-01-01 00:21:19          0     Sunday   \n",
       "...            ...           ...                   ...        ...        ...   \n",
       "3066761       2023             1   2023-02-01 00:12:33          0  Wednesday   \n",
       "3066762       2023             1   2023-01-31 23:50:36         23    Tuesday   \n",
       "3066763       2023             1   2023-01-31 23:25:36         23    Tuesday   \n",
       "3066764       2023             1   2023-01-31 23:53:00         23    Tuesday   \n",
       "3066765       2023             1   2023-01-31 23:21:56         23    Tuesday   \n",
       "\n",
       "         drop_month  drop_year  drop_weekday  \n",
       "0                 1       2023             6  \n",
       "1                 1       2023             6  \n",
       "2                 1       2023             6  \n",
       "3                 1       2023             6  \n",
       "4                 1       2023             6  \n",
       "...             ...        ...           ...  \n",
       "3066761           2       2023             2  \n",
       "3066762           1       2023             1  \n",
       "3066763           1       2023             1  \n",
       "3066764           1       2023             1  \n",
       "3066765           1       2023             1  \n",
       "\n",
       "[3066766 rows x 13 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "datetime_dim = df[['tpep_pickup_datetime','tpep_dropoff_datetime']].reset_index(drop=True)\n",
    "\n",
    "# PK\n",
    "datetime_dim['datetime_id'] = datetime_dim.index\n",
    "\n",
    "\n",
    "# pickup  datetime\n",
    "datetime_dim['pick_hour'] = datetime_dim['tpep_pickup_datetime'].dt.hour\n",
    "datetime_dim['pick_day'] = datetime_dim['tpep_pickup_datetime'].dt.day_name()\n",
    "datetime_dim['pick_month'] = datetime_dim['tpep_pickup_datetime'].dt.month\n",
    "datetime_dim['pick_year'] = datetime_dim['tpep_pickup_datetime'].dt.year\n",
    "datetime_dim['pick_weekday'] = datetime_dim['tpep_pickup_datetime'].dt.weekday\n",
    "\n",
    "# drop off datetime\n",
    "datetime_dim['drop_hour'] = datetime_dim['tpep_dropoff_datetime'].dt.hour\n",
    "datetime_dim['drop_day'] = datetime_dim['tpep_dropoff_datetime'].dt.day_name()\n",
    "datetime_dim['drop_month'] = datetime_dim['tpep_dropoff_datetime'].dt.month\n",
    "datetime_dim['drop_year'] = datetime_dim['tpep_dropoff_datetime'].dt.year\n",
    "datetime_dim['drop_weekday'] = datetime_dim['tpep_dropoff_datetime'].dt.weekday\n",
    "\n",
    "# giving proper ordering to columns for readability purpose\n",
    "\n",
    "cols_seq = ['datetime_id', 'tpep_pickup_datetime', 'pick_hour', 'pick_day', 'pick_month', 'pick_year', 'pick_weekday',\n",
    "             'tpep_dropoff_datetime', 'drop_hour', 'drop_day', 'drop_month', 'drop_year', 'drop_weekday']\n",
    "datetime_dim = datetime_dim[cols_seq]\n",
    "\n",
    "display(datetime_dim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `passenger_count_dim` dimension table\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/payment_type_dim.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 343,
   "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>passenger_count_id</th>\n",
       "      <th>passenger_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066761</th>\n",
       "      <td>3066761</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066762</th>\n",
       "      <td>3066762</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066763</th>\n",
       "      <td>3066763</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066764</th>\n",
       "      <td>3066764</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066765</th>\n",
       "      <td>3066765</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3066766 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         passenger_count_id  passenger_count\n",
       "0                         0              1.0\n",
       "1                         1              1.0\n",
       "2                         2              1.0\n",
       "3                         3              0.0\n",
       "4                         4              1.0\n",
       "...                     ...              ...\n",
       "3066761             3066761              NaN\n",
       "3066762             3066762              NaN\n",
       "3066763             3066763              NaN\n",
       "3066764             3066764              NaN\n",
       "3066765             3066765              NaN\n",
       "\n",
       "[3066766 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "passenger_count_dim = df[['passenger_count']].reset_index(drop=True)\n",
    "passenger_count_dim['passenger_count_id'] = passenger_count_dim.index\n",
    "\n",
    "# giving proper ordering to columns for readability purpose\n",
    "cols_seq = ['passenger_count_id','passenger_count']\n",
    "passenger_count_dim = passenger_count_dim[cols_seq]\n",
    "\n",
    "display(passenger_count_dim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `trip_distance_dim` dimension table\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/trip_distance_dim.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "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>trip_distance</th>\n",
       "      <th>trip_distance_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.97</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.51</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.90</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.43</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066761</th>\n",
       "      <td>3.05</td>\n",
       "      <td>3066761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066762</th>\n",
       "      <td>5.80</td>\n",
       "      <td>3066762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066763</th>\n",
       "      <td>4.67</td>\n",
       "      <td>3066763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066764</th>\n",
       "      <td>3.15</td>\n",
       "      <td>3066764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066765</th>\n",
       "      <td>2.85</td>\n",
       "      <td>3066765</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3066766 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         trip_distance  trip_distance_id\n",
       "0                 0.97                 0\n",
       "1                 1.10                 1\n",
       "2                 2.51                 2\n",
       "3                 1.90                 3\n",
       "4                 1.43                 4\n",
       "...                ...               ...\n",
       "3066761           3.05           3066761\n",
       "3066762           5.80           3066762\n",
       "3066763           4.67           3066763\n",
       "3066764           3.15           3066764\n",
       "3066765           2.85           3066765\n",
       "\n",
       "[3066766 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "trip_distance_dim = df[['trip_distance']].reset_index(drop=True)\n",
    "trip_distance_dim['trip_distance_id'] = trip_distance_dim.index\n",
    "\n",
    "\n",
    "# giving proper ordering to columns for readability purpose\n",
    "cols_seq = ['trip_distance_id', 'trip_distance']\n",
    "trip_distance_dim = trip_distance_dim\n",
    "\n",
    "display(trip_distance_dim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `rate_code_dim` dimension table\n",
    "\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/rate_code_dim.png)\n",
    "\n",
    "if we look in the data dictionary the information about rate code id are provided e.g 1 = 'Standard rate' etc. We can add this meta info in our dimension table to make it more helpful for our analysts.\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/rate_code_mapping.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 345,
   "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>rate_code_id</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>rate_code_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Standard rate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>JFK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Nassau or Westchester</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>99.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Negotiated fare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Newark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Group ride</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rate_code_id  RatecodeID         rate_code_name\n",
       "0             0         1.0          Standard rate\n",
       "1             1         2.0                    JFK\n",
       "2             2         4.0  Nassau or Westchester\n",
       "3             3        99.0                    NaN\n",
       "4             4         5.0        Negotiated fare\n",
       "5             5         3.0                 Newark\n",
       "6             6         6.0             Group ride\n",
       "7             7         NaN                    NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Mapping of rate code \n",
    "rate_code_type = {\n",
    "    1:\"Standard rate\",\n",
    "    2:\"JFK\",\n",
    "    3:\"Newark\",\n",
    "    4:\"Nassau or Westchester\",\n",
    "    5:\"Negotiated fare\",\n",
    "    6:\"Group ride\"\n",
    "}\n",
    "\n",
    "rate_code_dim = df[['RatecodeID']].drop_duplicates().reset_index(drop=True)\n",
    "rate_code_dim['rate_code_id'] = rate_code_dim.index\n",
    "\n",
    "# map `id` to the name of rate type\n",
    "rate_code_dim['rate_code_name'] = rate_code_dim['RatecodeID'].map(rate_code_type)\n",
    "\n",
    "# giving proper ordering to columns for readability purpose\n",
    "\n",
    "cols_seq = ['rate_code_id','RatecodeID','rate_code_name']\n",
    "rate_code_dim = rate_code_dim[cols_seq]\n",
    "\n",
    "display(rate_code_dim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `pickup_location_dim` dimension table\n",
    "\n",
    "\n",
    "We have `PULocationID` and `DOLocationID` column which doesn't mean anything to us unless we have a location name. Therefore, we will be using a lookup table which has mappings for each of location_id and their zone/location. If we refer to the above diagram, the lookup table which i am referring to is as follows\n",
    "\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/drop_location_pickup_location.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Location Dim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 346,
   "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>location_id</th>\n",
       "      <th>location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Newark Airport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jamaica Bay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Allerton/Pelham Gardens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Alphabet City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Arden Heights</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   location_id                 location\n",
       "0            1           Newark Airport\n",
       "1            2              Jamaica Bay\n",
       "2            3  Allerton/Pelham Gardens\n",
       "3            4            Alphabet City\n",
       "4            5            Arden Heights"
      ]
     },
     "execution_count": 346,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# loading lookup table\n",
    "lookup_location = pd.read_csv('./nyc_taxi_trip_data/taxi+_zone_lookup.csv')[['LocationID','Zone']].drop_duplicates().reset_index(drop=True)\n",
    "location_dim = lookup_location.rename(columns={'LocationID':'location_id','Zone':'location'})\n",
    "location_dim.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 347,
   "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>pickup_location_id</th>\n",
       "      <th>pickup_location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Midtown Center</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Midtown Center</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23</td>\n",
       "      <td>Midtown Center</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24</td>\n",
       "      <td>Midtown Center</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33</td>\n",
       "      <td>Midtown Center</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066761</th>\n",
       "      <td>2865086</td>\n",
       "      <td>City Island</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066762</th>\n",
       "      <td>2687592</td>\n",
       "      <td>Jamaica Bay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066763</th>\n",
       "      <td>2687593</td>\n",
       "      <td>Jamaica Bay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066764</th>\n",
       "      <td>3033499</td>\n",
       "      <td>Country Club</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066765</th>\n",
       "      <td>3035801</td>\n",
       "      <td>Country Club</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3066766 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         pickup_location_id pickup_location\n",
       "0                         0  Midtown Center\n",
       "1                         5  Midtown Center\n",
       "2                        23  Midtown Center\n",
       "3                        24  Midtown Center\n",
       "4                        33  Midtown Center\n",
       "...                     ...             ...\n",
       "3066761             2865086     City Island\n",
       "3066762             2687592     Jamaica Bay\n",
       "3066763             2687593     Jamaica Bay\n",
       "3066764             3033499    Country Club\n",
       "3066765             3035801    Country Club\n",
       "\n",
       "[3066766 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# preparing dimension table\n",
    "pickup_location_dim = df[['PULocationID']].reset_index(drop=True)\n",
    "pickup_location_dim['pickup_location_id'] = pickup_location_dim.index\n",
    "cols_select = ['pickup_location_id','location']\n",
    "pickup_location_dim = pickup_location_dim.merge(location_dim, left_on='PULocationID', right_on='location_id')[cols_select]\n",
    "\n",
    "# giving proper ordering to columns for readability purpose\n",
    "cols_seq = ['pickup_location_id','location']\n",
    "pickup_location_dim = pickup_location_dim[cols_seq] \n",
    "\n",
    "#rename `location` to `pickup_location` for good readability \n",
    "pickup_location_dim = pickup_location_dim.rename(columns={'location':'pickup_location'})\n",
    "\n",
    "\n",
    "display(pickup_location_dim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `dropoff_location_dim` dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 348,
   "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>dropoff_location_id</th>\n",
       "      <th>dropff_location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>120</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>161</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>197</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066761</th>\n",
       "      <td>1921746</td>\n",
       "      <td>Rossville/Woodrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066762</th>\n",
       "      <td>1947689</td>\n",
       "      <td>Rossville/Woodrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066763</th>\n",
       "      <td>2146389</td>\n",
       "      <td>Rossville/Woodrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066764</th>\n",
       "      <td>2780992</td>\n",
       "      <td>Rossville/Woodrow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3066765</th>\n",
       "      <td>2934582</td>\n",
       "      <td>Governor's Island/Ellis Island/Liberty Island</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3066766 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         dropoff_location_id                                dropff_location\n",
       "0                          0                                Lenox Hill West\n",
       "1                         45                                Lenox Hill West\n",
       "2                        120                                Lenox Hill West\n",
       "3                        161                                Lenox Hill West\n",
       "4                        197                                Lenox Hill West\n",
       "...                      ...                                            ...\n",
       "3066761              1921746                              Rossville/Woodrow\n",
       "3066762              1947689                              Rossville/Woodrow\n",
       "3066763              2146389                              Rossville/Woodrow\n",
       "3066764              2780992                              Rossville/Woodrow\n",
       "3066765              2934582  Governor's Island/Ellis Island/Liberty Island\n",
       "\n",
       "[3066766 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# preparing dimension table\n",
    "dropoff_location_dim = df[['DOLocationID']].reset_index(drop=True)\n",
    "dropoff_location_dim['dropoff_location_id'] = dropoff_location_dim.index\n",
    "cols_select = ['dropoff_location_id','location']\n",
    "dropoff_location_dim = dropoff_location_dim.merge(location_dim, left_on='DOLocationID', right_on='location_id')[cols_select]\n",
    "\n",
    "# giving proper ordering to columns for readability purpose\n",
    "cols_seq = ['dropoff_location_id','location']\n",
    "dropoff_location_dim = dropoff_location_dim[cols_seq] \n",
    "\n",
    "#rename `location` to `pickup_location` for good readability \n",
    "dropoff_location_dim = dropoff_location_dim.rename(columns={'location':'dropff_location'})\n",
    "\n",
    "\n",
    "display(dropoff_location_dim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `payment_type_dim` dimension table\n",
    "\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/payment_type_dim.png)\n",
    "\n",
    "Again, if we look in the data dictionary the information about payment_type is provided e.g 1 = 'Credit Card' etc. This would be helpful for analysts when making complex join on data for analytical purposes\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/payment_type_mapping.png)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 349,
   "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>payment_type_id</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>payment_type_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Cash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Credit card</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>Dispute</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>No charge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payment_type_id  payment_type payment_type_name\n",
       "0                0             2              Cash\n",
       "1                1             1       Credit card\n",
       "2                2             4           Dispute\n",
       "3                3             3         No charge\n",
       "4                4             0               NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "payment_type_name = {\n",
    "    1:\"Credit card\",\n",
    "    2:\"Cash\",\n",
    "    3:\"No charge\",\n",
    "    4:\"Dispute\",\n",
    "    5:\"Unknown\",\n",
    "    6:\"Voided trip\"\n",
    "}\n",
    "payment_type_dim = df[['payment_type']].drop_duplicates().reset_index(drop=True)\n",
    "payment_type_dim['payment_type_id'] = payment_type_dim.index\n",
    "payment_type_dim['payment_type_name'] = payment_type_dim['payment_type'].map(payment_type_name)\n",
    "\n",
    "# giving proper ordering to columns for readability purpose\n",
    "cols_seq = ['payment_type_id','payment_type','payment_type_name']\n",
    "payment_type_dim = payment_type_dim[cols_seq]\n",
    "\n",
    "display(payment_type_dim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating `fact_table` dimension table\n",
    "\n",
    "The most important table, where all of the data gets connected. This table doesn't hold descriptive information about the items themselves or the customers—instead, it links to other tables (like dimensions) that contain this additional information. So, while the fact table holds the numeric facts or measurements, it references other tables to provide the context or details about those facts. think of a fact table as the central hub of a data warehouse or database. It's where all the key metrics and measurements are stored, organized, and connected.\n",
    "\n",
    "![Alt text](nyc_taxi_trip_data/images/fact_table.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 350,
   "metadata": {},
   "outputs": [],
   "source": [
    "fact_table = df.merge(passenger_count_dim, left_on='trip_id', right_on='passenger_count_id', how='left') \\\n",
    "               .merge(trip_distance_dim, left_on='trip_id', right_on='trip_distance_id', how='left') \\\n",
    "               .merge(rate_code_dim, left_on='RatecodeID',right_on='RatecodeID',how='left') \\\n",
    "               .merge(pickup_location_dim, left_on='trip_id', right_on='pickup_location_id',how='left') \\\n",
    "               .merge(dropoff_location_dim, left_on='trip_id', right_on='dropoff_location_id',how='left') \\\n",
    "               .merge(datetime_dim, left_on='trip_id', right_on='datetime_id',how='left')\\\n",
    "               .merge(payment_type_dim, left_on='payment_type', right_on='payment_type',how='left') \n",
    "\n",
    "\n",
    "cols_required = ['trip_id','VendorID', 'datetime_id', 'passenger_count_id',\n",
    "               'trip_distance_id', 'rate_code_id', 'store_and_fwd_flag', 'pickup_location_id', 'dropoff_location_id',\n",
    "               'payment_type_id', 'fare_amount', 'extra', 'mta_tax', 'tip_amount', 'tolls_amount',\n",
    "               'improvement_surcharge', 'total_amount']\n",
    "fact_table = fact_table[cols_required]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 358,
   "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>trip_id</th>\n",
       "      <th>VendorID</th>\n",
       "      <th>datetime_id</th>\n",
       "      <th>passenger_count_id</th>\n",
       "      <th>trip_distance_id</th>\n",
       "      <th>rate_code_id</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>pickup_location_id</th>\n",
       "      <th>dropoff_location_id</th>\n",
       "      <th>payment_type_id</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>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9.3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7.9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>14.9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>12.1</td>\n",
       "      <td>7.25</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>11.4</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trip_id  VendorID  datetime_id  passenger_count_id  trip_distance_id  \\\n",
       "0        0         2            0                   0                 0   \n",
       "1        1         2            1                   1                 1   \n",
       "2        2         2            2                   2                 2   \n",
       "3        3         1            3                   3                 3   \n",
       "4        4         2            4                   4                 4   \n",
       "\n",
       "   rate_code_id store_and_fwd_flag  pickup_location_id  dropoff_location_id  \\\n",
       "0             0                  N                   0                    0   \n",
       "1             0                  N                   1                    1   \n",
       "2             0                  N                   2                    2   \n",
       "3             0                  N                   3                    3   \n",
       "4             0                  N                   4                    4   \n",
       "\n",
       "   payment_type_id  fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0                0          9.3   1.00      0.5        0.00           0.0   \n",
       "1                1          7.9   1.00      0.5        4.00           0.0   \n",
       "2                1         14.9   1.00      0.5       15.00           0.0   \n",
       "3                1         12.1   7.25      0.5        0.00           0.0   \n",
       "4                1         11.4   1.00      0.5        3.28           0.0   \n",
       "\n",
       "   improvement_surcharge  total_amount  \n",
       "0                    1.0         14.30  \n",
       "1                    1.0         16.90  \n",
       "2                    1.0         34.90  \n",
       "3                    1.0         20.85  \n",
       "4                    1.0         19.68  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "display(fact_table.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "as we can see the table contains foreign keys of the dimension table and it represent a consolidated table which can link different tables to get different information of the trips. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Applying Analytics\n",
    "\n",
    "What good a data model is when we can't apply analytics to it. I have prepared some questions in advance that we can answer. I will be using DuckDB for this exercise but in future articles (i hope :v ) i will be utilising clickhouse.\n",
    "\n",
    "### Basic Statistics:\n",
    "- What is the average trip distance?\n",
    "- What is the most common payment type?\n",
    "- Average trip distance for trips with a tip_amount greater than $10, excluding trips with a payment type of 'Cash'.\n",
    "\n",
    "### Vendor Comparison:\n",
    "- How many trips were made by each VendorID?\n",
    "- Which vendor has the highest average fare_amount?\n",
    "\n",
    "### Time Analysis:\n",
    "- What is the average number of passengers for trips that occurred on Sundays?\n",
    "- What are the peak hours for taxi trips based on the pick-up datetime?\n",
    "\n",
    "### Location-Based Analysis:\n",
    "- identify zones with the highest average total_amount and the lowest average trip_distance.\n",
    "- Which pickup location has the highest tip_amount amount?\n",
    "\n",
    "### Rate Code Insights:\n",
    "- How many trips were made for each rate code type?\n",
    "- What is the average total_amount for each rate code type?\n",
    "\n",
    "\n",
    "### Variables:\n",
    "The pandas variables which holds information about dimensions\n",
    "- **datetime_dim:** This table captures datetime-related attributes such as pickup and drop-off hours, days, months, years, and weekdays.\n",
    "- **passenger_count_dim:** Contains passenger count information.\n",
    "- **trip_distance_dim:** Holds details about trip distances.\n",
    "- **rate_code_dim:** Provides insights into different rate codes and their corresponding names.\n",
    "- **location_dim:** A look-up table for location which maps locationID for pickup/dropff into location_name\n",
    "- **pickup_location_dim:** Stores data about pickup locations.\n",
    "- **dropoff_location_dim:** Holds data regarding drop-off locations.\n",
    "- **payment_type_dim:** Contains information about various payment types.\n",
    "- **fact_table**: Info about all relation to dimensions\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 352,
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "\n",
    "def run(q):\n",
    "    out = duckdb.query(q)\n",
    "    display(out)\n",
    "    return out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Answering Basic Statistics Questions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "What is the average trip distance? \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌──────────────────┐\n",
       "│ average_distance │\n",
       "│      double      │\n",
       "├──────────────────┤\n",
       "│             3.85 │\n",
       "└──────────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "What is the most common payment type? \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌───────────────────┬─────────┐\n",
       "│ payment_type_name │ count_  │\n",
       "│      varchar      │  int64  │\n",
       "├───────────────────┼─────────┤\n",
       "│ Credit card       │ 2411462 │\n",
       "│ Cash              │  532241 │\n",
       "│ NULL              │   71743 │\n",
       "└───────────────────┴─────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "What is the Average trip distance for trips with a tip_amount greater than $10, excluding trips with a payment type of \"Cash\" \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌──────────────────────────────┐\n",
       "│ average_trip_distance(miles) │\n",
       "│            double            │\n",
       "├──────────────────────────────┤\n",
       "│                         15.0 │\n",
       "└──────────────────────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "┌──────────────────────────────┐\n",
       "│ average_trip_distance(miles) │\n",
       "│            double            │\n",
       "├──────────────────────────────┤\n",
       "│                         15.0 │\n",
       "└──────────────────────────────┘"
      ]
     },
     "execution_count": 353,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What is the average trip distance? \n",
    "\n",
    "q = \"\"\"\n",
    "    SELECT round(avg(trip_distance),2) as average_distance\n",
    "    FROM trip_distance_dim\n",
    "\"\"\"\n",
    "print('What is the average trip distance? ')\n",
    "run(q)\n",
    "\n",
    "# What is the most common payment type? \n",
    "TOP_K = 3\n",
    "q = f\"\"\"\n",
    "    WITH PAYMENT_NAME_AGG_COUNTS AS (\n",
    "         SELECT payment_type, payment_type_name , count(*) as count_\n",
    "        FROM payment_type_dim \n",
    "        JOIN fact_table ON fact_table.payment_type_id = payment_type_dim.payment_type_id\n",
    "        GROUP BY payment_type_dim.payment_type, payment_type_dim.payment_type_name \n",
    "    ), PAYMENT_RANK AS (\n",
    "        SELECT *, ROW_NUMBER() OVER(ORDER BY count_ DESC) as rnk\n",
    "        FROM PAYMENT_NAME_AGG_COUNTS\n",
    "    ) SELECT payment_type_name, count_ FROM PAYMENT_RANK WHERE rnk <= {TOP_K}\n",
    "\"\"\"\n",
    "\n",
    "# \"\"\"\n",
    "print('What is the most common payment type? ')\n",
    "run(q)\n",
    "\n",
    "\n",
    "\n",
    "# - Average trip distance for trips with a tip_amount greater than $10, excluding trips with a payment type of 'Cash'.\n",
    "q = \"\"\"\n",
    "    WITH TRIP_DISTANCE_BASE AS (\n",
    "        SELECT round(avg(trip_distance_dim.trip_distance)) as 'average_trip_distance(miles)'\n",
    "        FROM fact_table\n",
    "        JOIN trip_distance_dim ON  fact_table.trip_id = trip_distance_dim.trip_distance_id\n",
    "        JOIN payment_type_dim  ON  fact_table.payment_type_id = payment_type_dim.payment_type_id\n",
    "        WHERE fact_table.tip_amount > 10 and lower(payment_type_dim.payment_type_name) = 'cash'\n",
    "    ) select * from TRIP_DISTANCE_BASE\n",
    "\"\"\"\n",
    "print('What is the Average trip distance for trips with a tip_amount greater than $10, excluding trips with a payment type of \"Cash\" ')\n",
    "run(q)\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Vendor Comparison:\n",
    "- How many trips were made by each VendorID?\n",
    "- Which vendor has the highest average fare_amount?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "How many trips were made by each VendorID?\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌──────────┬─────────────┐\n",
       "│ VendorID │ total_trips │\n",
       "│  int64   │    int64    │\n",
       "├──────────┼─────────────┤\n",
       "│        2 │     2239399 │\n",
       "│        1 │      827367 │\n",
       "└──────────┴─────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Which vendor has the highest average fare_amount?\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌──────────┬─────────────────┐\n",
       "│ VendorID │ avg_fare_amount │\n",
       "│  int64   │     double      │\n",
       "├──────────┼─────────────────┤\n",
       "│        2 │           18.71 │\n",
       "└──────────┴─────────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "┌──────────┬─────────────────┐\n",
       "│ VendorID │ avg_fare_amount │\n",
       "│  int64   │     double      │\n",
       "├──────────┼─────────────────┤\n",
       "│        2 │           18.71 │\n",
       "└──────────┴─────────────────┘"
      ]
     },
     "execution_count": 354,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many trips were made by each VendorID?\n",
    "\n",
    "q = \"\"\"\n",
    "    SELECT VendorID, count(*) as total_trips\n",
    "    FROM fact_table\n",
    "    GROUP BY VendorID\n",
    "    ORDER BY count(*) DESC\n",
    "\"\"\"\n",
    "print(\"How many trips were made by each VendorID?\")\n",
    "run(q)\n",
    "\n",
    "\n",
    "\n",
    "# Which vendor has the highest average fare_amount?\n",
    "\n",
    "q = \"\"\"\n",
    "    WITH VENDOR_FARE_AMOUNT AS (\n",
    "        SELECT VendorID, round(avg(fare_amount),2) as avg_fare_amount\n",
    "        FROM fact_table\n",
    "        GROUP BY VendorID\n",
    "    ), VENDOR_MAX_AMOUNT_RNK AS (\n",
    "        SELECT VendorID, avg_fare_amount, row_number() OVER(ORDER BY avg_fare_amount DESC) as RNK\n",
    "        FROM VENDOR_FARE_AMOUNT\n",
    "    ) SELECT VendorID, avg_fare_amount FROM VENDOR_MAX_AMOUNT_RNK WHERE RNK = 1\n",
    "        \"\"\"\n",
    "print(\"Which vendor has the highest average fare_amount?\")\n",
    "run(q)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time Analysis:\n",
    "- What is the average number of passengers for trips that occurred on Sundays?\n",
    "- What are the peak hours for taxi trips based on the pick-up datetime?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 355,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "What is the average number of passengers for trips that occurred on Sundays?\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌─────────────────────────┐\n",
       "│ average_passenger_count │\n",
       "│         double          │\n",
       "├─────────────────────────┤\n",
       "│                    1.45 │\n",
       "└─────────────────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "What are the peak hours for taxi trips based on the pick-up datetime?\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌───────────┬─────────────┐\n",
       "│ pick_hour │ total_trips │\n",
       "│   int32   │    int64    │\n",
       "├───────────┼─────────────┤\n",
       "│         4 │       17835 │\n",
       "└───────────┴─────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "┌───────────┬─────────────┐\n",
       "│ pick_hour │ total_trips │\n",
       "│   int32   │    int64    │\n",
       "├───────────┼─────────────┤\n",
       "│         4 │       17835 │\n",
       "└───────────┴─────────────┘"
      ]
     },
     "execution_count": 355,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#- What is the average number of passengers for trips that occurred on Sundays?\n",
    "\n",
    "q = \"\"\"\n",
    "    SELECT round(avg(passenger_count_dim.passenger_count),2) as average_passenger_count\n",
    "    FROM fact_table\n",
    "    JOIN passenger_count_dim ON passenger_count_dim.passenger_count_id = fact_table.passenger_count_id\n",
    "    JOIN datetime_dim ON datetime_dim.datetime_id = fact_table.datetime_id\n",
    "    WHERE lower(datetime_dim.pick_day) = 'sunday' or datetime_dim.drop_day = 'sunday'\n",
    "\"\"\"\n",
    "print(\"What is the average number of passengers for trips that occurred on Sundays?\")\n",
    "run(q)\n",
    "\n",
    "\n",
    "#- What are the peak hours for taxi trips based on the pick-up datetime?\n",
    "\n",
    "print(\"What are the peak hours for taxi trips based on the pick-up datetime?\")\n",
    "q = \"\"\"\n",
    "        WITH TRIPS_BY_HOUR_COUNT AS (\n",
    "            SELECT datetime_dim.pick_hour, count(*) as total_trips\n",
    "            FROM fact_table\n",
    "            JOIN datetime_dim ON datetime_dim.datetime_id = fact_table.datetime_id\n",
    "            GROUP BY datetime_dim.pick_hour\n",
    "        ), TRIPS_BY_HOUR_RANK AS (\n",
    "            SELECT  pick_hour, total_trips, DENSE_RANK() OVER(ORDER BY total_trips) as rnk\n",
    "           FROM TRIPS_BY_HOUR_COUNT\n",
    "        ) SELECT pick_hour,total_trips  FROM TRIPS_BY_HOUR_RANK WHERE rnk = 1\n",
    "\"\"\"\n",
    "\n",
    "run(q)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Location-Based Analysis:\n",
    "- identify zones with the highest average total_amount and the lowest average trip_distance.\n",
    "- Which pickup location has the highest tip_amount amount?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 356,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "identify zones with the highest average total_amount and the lowest average trip_distance.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌────────────────┬─────────────────────────┬──────────────────────────┐\n",
       "│      zone      │ avg_highest_trip_amount │ avg_lowest_trip_distance │\n",
       "│    varchar     │         double          │          double          │\n",
       "├────────────────┼─────────────────────────┼──────────────────────────┤\n",
       "│ Newark Airport │                  104.38 │               1212392.68 │\n",
       "└────────────────┴─────────────────────────┴──────────────────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Which pickup location has the highest tip_amount amount?\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "87b00e165c654b709243bcc2956f9141",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "┌───────────────────────┬────────────────────┐\n",
       "│         zone          │ highest_tip_amount │\n",
       "│        varchar        │       double       │\n",
       "├───────────────────────┼────────────────────┤\n",
       "│ Upper East Side North │              380.8 │\n",
       "└───────────────────────┴────────────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "┌───────────────────────┬────────────────────┐\n",
       "│         zone          │ highest_tip_amount │\n",
       "│        varchar        │       double       │\n",
       "├───────────────────────┼────────────────────┤\n",
       "│ Upper East Side North │              380.8 │\n",
       "└───────────────────────┴────────────────────┘"
      ]
     },
     "execution_count": 356,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# - identify zones with the highest average total_amount and the lowest average trip_distance.\n",
    "# - Which pickup location has the highest tip_amount amount?\n",
    "\n",
    "q = \"\"\"\n",
    "    WITH BASE AS (\n",
    "        SELECT \n",
    "            pickup_location_dim.pickup_location as zone, \n",
    "            round(avg(total_amount),2) as avg_total_amount, \n",
    "            round(avg(trip_distance_dim.trip_distance_id),2) as avg_trip_distance\n",
    "        FROM fact_table \n",
    "        join trip_distance_dim ON fact_table.trip_id = trip_distance_dim.trip_distance_id\n",
    "        JOIN pickup_location_dim ON pickup_location_dim.pickup_location_id = fact_table.pickup_location_id\n",
    "        WHERE pickup_location_dim.pickup_location IS NOT NULL\n",
    "        GROUP BY pickup_location_dim.pickup_location\n",
    "    )  SELECT zone, avg_total_amount as avg_highest_trip_amount, avg_trip_distance  as avg_lowest_trip_distance \n",
    "       FROM BASE ORDER BY avg_total_amount DESC, avg_trip_distance ASC LIMIT 1\n",
    "\"\"\"\n",
    "print(\"identify zones with the highest average total_amount and the lowest average trip_distance.\")\n",
    "run(q)\n",
    "\n",
    "\n",
    "q = \"\"\"\n",
    "    WITH BASE AS(\n",
    "        \n",
    "        SELECT \n",
    "            pickup_location_dim.pickup_location as zone, \n",
    "            fact_table.tip_amount,\n",
    "            ROW_NUMBER() OVER(ORDER BY fact_table.tip_amount DESC) as rnk\n",
    "        FROM fact_table \n",
    "        JOIN pickup_location_dim ON pickup_location_dim.pickup_location_id = fact_table.pickup_location_id\n",
    "    ) SELECT zone, tip_amount as highest_tip_amount FROM BASE WHERE rnk = 1\n",
    "\"\"\"\n",
    "print(\"Which pickup location has the highest tip_amount amount?\")\n",
    "run(q)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rate Code Insights:\n",
    "- How many trips were made for each rate code type?\n",
    "- What is the average total_amount for each rate code type?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 357,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "How many trips were made for each rate code type?\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌───────────────────────┬────────────┐\n",
       "│    rate_code_name     │ trip_count │\n",
       "│        varchar        │   int64    │\n",
       "├───────────────────────┼────────────┤\n",
       "│ Standard rate         │     114239 │\n",
       "│ Nassau or Westchester │      15043 │\n",
       "│ Newark                │      13106 │\n",
       "│ Negotiated fare       │       8958 │\n",
       "│ JFK                   │       4366 │\n",
       "│ Group ride            │          6 │\n",
       "└───────────────────────┴────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "What is the average total_amount for each rate code type?\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "┌───────────────────────┬──────────────────┐\n",
       "│    rate_code_name     │ avg_total_amount │\n",
       "│        varchar        │      double      │\n",
       "├───────────────────────┼──────────────────┤\n",
       "│ Group ride            │           132.78 │\n",
       "│ JFK                   │           119.95 │\n",
       "│ Negotiated fare       │            107.4 │\n",
       "│ Standard rate         │            89.09 │\n",
       "│ Nassau or Westchester │            81.39 │\n",
       "│ Newark                │            37.76 │\n",
       "└───────────────────────┴──────────────────┘"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "┌───────────────────────┬──────────────────┐\n",
       "│    rate_code_name     │ avg_total_amount │\n",
       "│        varchar        │      double      │\n",
       "├───────────────────────┼──────────────────┤\n",
       "│ Group ride            │           132.78 │\n",
       "│ JFK                   │           119.95 │\n",
       "│ Negotiated fare       │            107.4 │\n",
       "│ Standard rate         │            89.09 │\n",
       "│ Nassau or Westchester │            81.39 │\n",
       "│ Newark                │            37.76 │\n",
       "└───────────────────────┴──────────────────┘"
      ]
     },
     "execution_count": 357,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many trips were made for each rate code type?\n",
    "q = \"\"\"\n",
    "    SELECT rate_code_dim.rate_code_name, count(*) as trip_count\n",
    "    FROM fact_table\n",
    "    JOIN rate_code_dim ON rate_code_dim.RatecodeID = fact_table.rate_code_id\n",
    "    GROUP BY rate_code_dim.rate_code_name\n",
    "    ORDER BY trip_count DESC\n",
    "\n",
    "\"\"\"\n",
    "print(\"How many trips were made for each rate code type?\")\n",
    "run(q)\n",
    "\n",
    "\n",
    "\n",
    "q = \"\"\"\n",
    "    SELECT rate_code_dim.rate_code_name, round(avg(fact_table.total_amount),2) as avg_total_amount\n",
    "    FROM fact_table\n",
    "    JOIN rate_code_dim ON rate_code_dim.RatecodeID = fact_table.rate_code_id\n",
    "    GROUP BY rate_code_dim.rate_code_name\n",
    "    ORDER BY avg_total_amount DESC\n",
    "\n",
    "\"\"\"\n",
    "print(\"What is the average total_amount for each rate code type?\")\n",
    "run(q)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}