NY Taxi - Uber Data Pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bc0fc254-dee0-4929-b7d9-3b7ab862d358",
   "metadata": {},
   "outputs": [],
   "source": [
    "import io\n",
    "import pandas as pd\n",
    "import requests\n",
    "\n",
    "url = 'https://storage.googleapis.com/ny-taxi-uber-data-engineering-project/uber_data.csv'\n",
    "response = requests.get(url)\n",
    "\n",
    "df = pd.read_csv(io.StringIO(response.text), sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "72ffec25-91c1-4daa-873a-9e1ac2ecc8be",
   "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>pickup_longitude</th>\n",
       "      <th>pickup_latitude</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>dropoff_longitude</th>\n",
       "      <th>dropoff_latitude</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>2016-03-01 00:07:55</td>\n",
       "      <td>1</td>\n",
       "      <td>2.50</td>\n",
       "      <td>-73.976746</td>\n",
       "      <td>40.765152</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-74.004265</td>\n",
       "      <td>40.746128</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>2016-03-01 00:11:06</td>\n",
       "      <td>1</td>\n",
       "      <td>2.90</td>\n",
       "      <td>-73.983482</td>\n",
       "      <td>40.767925</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-74.005943</td>\n",
       "      <td>40.733166</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>15.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>2016-03-01 00:31:06</td>\n",
       "      <td>2</td>\n",
       "      <td>19.98</td>\n",
       "      <td>-73.782021</td>\n",
       "      <td>40.644810</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-73.974541</td>\n",
       "      <td>40.675770</td>\n",
       "      <td>1</td>\n",
       "      <td>54.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>63.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>10.78</td>\n",
       "      <td>-73.863419</td>\n",
       "      <td>40.769814</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-73.969650</td>\n",
       "      <td>40.757767</td>\n",
       "      <td>1</td>\n",
       "      <td>31.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.78</td>\n",
       "      <td>5.54</td>\n",
       "      <td>0.3</td>\n",
       "      <td>41.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>5</td>\n",
       "      <td>30.43</td>\n",
       "      <td>-73.971741</td>\n",
       "      <td>40.792183</td>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td>-74.177170</td>\n",
       "      <td>40.695053</td>\n",
       "      <td>1</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>15.50</td>\n",
       "      <td>0.3</td>\n",
       "      <td>113.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0         1  2016-03-01 00:00:00   2016-03-01 00:07:55                1   \n",
       "1         1  2016-03-01 00:00:00   2016-03-01 00:11:06                1   \n",
       "2         2  2016-03-01 00:00:00   2016-03-01 00:31:06                2   \n",
       "3         2  2016-03-01 00:00:00   2016-03-01 00:00:00                3   \n",
       "4         2  2016-03-01 00:00:00   2016-03-01 00:00:00                5   \n",
       "\n",
       "   trip_distance  pickup_longitude  pickup_latitude  RatecodeID  \\\n",
       "0           2.50        -73.976746        40.765152           1   \n",
       "1           2.90        -73.983482        40.767925           1   \n",
       "2          19.98        -73.782021        40.644810           1   \n",
       "3          10.78        -73.863419        40.769814           1   \n",
       "4          30.43        -73.971741        40.792183           3   \n",
       "\n",
       "  store_and_fwd_flag  dropoff_longitude  dropoff_latitude  payment_type  \\\n",
       "0                  N         -74.004265         40.746128             1   \n",
       "1                  N         -74.005943         40.733166             1   \n",
       "2                  N         -73.974541         40.675770             1   \n",
       "3                  N         -73.969650         40.757767             1   \n",
       "4                  N         -74.177170         40.695053             1   \n",
       "\n",
       "   fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0          9.0    0.5      0.5        2.05          0.00   \n",
       "1         11.0    0.5      0.5        3.05          0.00   \n",
       "2         54.5    0.5      0.5        8.00          0.00   \n",
       "3         31.5    0.0      0.5        3.78          5.54   \n",
       "4         98.0    0.0      0.0        0.00         15.50   \n",
       "\n",
       "   improvement_surcharge  total_amount  \n",
       "0                    0.3         12.35  \n",
       "1                    0.3         15.35  \n",
       "2                    0.3         63.80  \n",
       "3                    0.3         41.62  \n",
       "4                    0.3        113.80  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is a flat file and we will convert it into fact (parameters which are changing) and dimension (parameters which are const) tables - Data Modellling\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4281c2cd-b671-47a8-977e-5e19481dbbb3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100000 entries, 0 to 99999\n",
      "Data columns (total 19 columns):\n",
      " #   Column                 Non-Null Count   Dtype  \n",
      "---  ------                 --------------   -----  \n",
      " 0   VendorID               100000 non-null  int64  \n",
      " 1   tpep_pickup_datetime   100000 non-null  object \n",
      " 2   tpep_dropoff_datetime  100000 non-null  object \n",
      " 3   passenger_count        100000 non-null  int64  \n",
      " 4   trip_distance          100000 non-null  float64\n",
      " 5   pickup_longitude       100000 non-null  float64\n",
      " 6   pickup_latitude        100000 non-null  float64\n",
      " 7   RatecodeID             100000 non-null  int64  \n",
      " 8   store_and_fwd_flag     100000 non-null  object \n",
      " 9   dropoff_longitude      100000 non-null  float64\n",
      " 10  dropoff_latitude       100000 non-null  float64\n",
      " 11  payment_type           100000 non-null  int64  \n",
      " 12  fare_amount            100000 non-null  float64\n",
      " 13  extra                  100000 non-null  float64\n",
      " 14  mta_tax                100000 non-null  float64\n",
      " 15  tip_amount             100000 non-null  float64\n",
      " 16  tolls_amount           100000 non-null  float64\n",
      " 17  improvement_surcharge  100000 non-null  float64\n",
      " 18  total_amount           100000 non-null  float64\n",
      "dtypes: float64(12), int64(4), object(3)\n",
      "memory usage: 14.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b0255e0b-8c96-4a08-b86e-e7ec670739ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['tpep_pickup_datetime'] = pd.to_datetime(df['tpep_pickup_datetime'])\n",
    "df['tpep_dropoff_datetime'] = pd.to_datetime(df['tpep_dropoff_datetime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "63472967-3af5-482c-a717-ef3949494c84",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates().reset_index(drop=True)\n",
    "df['trip_id'] = df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ed708d3f-8834-44e5-abea-03cb03c38a4d",
   "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>pickup_longitude</th>\n",
       "      <th>pickup_latitude</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>dropoff_longitude</th>\n",
       "      <th>dropoff_latitude</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>trip_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>2016-03-01 00:07:55</td>\n",
       "      <td>1</td>\n",
       "      <td>2.50</td>\n",
       "      <td>-73.976746</td>\n",
       "      <td>40.765152</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-74.004265</td>\n",
       "      <td>40.746128</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.35</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>2016-03-01 00:11:06</td>\n",
       "      <td>1</td>\n",
       "      <td>2.90</td>\n",
       "      <td>-73.983482</td>\n",
       "      <td>40.767925</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-74.005943</td>\n",
       "      <td>40.733166</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>15.35</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>2016-03-01 00:31:06</td>\n",
       "      <td>2</td>\n",
       "      <td>19.98</td>\n",
       "      <td>-73.782021</td>\n",
       "      <td>40.644810</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-73.974541</td>\n",
       "      <td>40.675770</td>\n",
       "      <td>1</td>\n",
       "      <td>54.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>63.80</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>10.78</td>\n",
       "      <td>-73.863419</td>\n",
       "      <td>40.769814</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-73.969650</td>\n",
       "      <td>40.757767</td>\n",
       "      <td>1</td>\n",
       "      <td>31.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.78</td>\n",
       "      <td>5.54</td>\n",
       "      <td>0.3</td>\n",
       "      <td>41.62</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>5</td>\n",
       "      <td>30.43</td>\n",
       "      <td>-73.971741</td>\n",
       "      <td>40.792183</td>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td>-74.177170</td>\n",
       "      <td>40.695053</td>\n",
       "      <td>1</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>15.50</td>\n",
       "      <td>0.3</td>\n",
       "      <td>113.80</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0         1           2016-03-01   2016-03-01 00:07:55                1   \n",
       "1         1           2016-03-01   2016-03-01 00:11:06                1   \n",
       "2         2           2016-03-01   2016-03-01 00:31:06                2   \n",
       "3         2           2016-03-01   2016-03-01 00:00:00                3   \n",
       "4         2           2016-03-01   2016-03-01 00:00:00                5   \n",
       "\n",
       "   trip_distance  pickup_longitude  pickup_latitude  RatecodeID  \\\n",
       "0           2.50        -73.976746        40.765152           1   \n",
       "1           2.90        -73.983482        40.767925           1   \n",
       "2          19.98        -73.782021        40.644810           1   \n",
       "3          10.78        -73.863419        40.769814           1   \n",
       "4          30.43        -73.971741        40.792183           3   \n",
       "\n",
       "  store_and_fwd_flag  dropoff_longitude  dropoff_latitude  payment_type  \\\n",
       "0                  N         -74.004265         40.746128             1   \n",
       "1                  N         -74.005943         40.733166             1   \n",
       "2                  N         -73.974541         40.675770             1   \n",
       "3                  N         -73.969650         40.757767             1   \n",
       "4                  N         -74.177170         40.695053             1   \n",
       "\n",
       "   fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0          9.0    0.5      0.5        2.05          0.00   \n",
       "1         11.0    0.5      0.5        3.05          0.00   \n",
       "2         54.5    0.5      0.5        8.00          0.00   \n",
       "3         31.5    0.0      0.5        3.78          5.54   \n",
       "4         98.0    0.0      0.0        0.00         15.50   \n",
       "\n",
       "   improvement_surcharge  total_amount  trip_id  \n",
       "0                    0.3         12.35        0  \n",
       "1                    0.3         15.35        1  \n",
       "2                    0.3         63.80        2  \n",
       "3                    0.3         41.62        3  \n",
       "4                    0.3        113.80        4  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f4fa7118-3846-46c4-936f-b473773bdccc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100000 entries, 0 to 99999\n",
      "Data columns (total 20 columns):\n",
      " #   Column                 Non-Null Count   Dtype         \n",
      "---  ------                 --------------   -----         \n",
      " 0   VendorID               100000 non-null  int64         \n",
      " 1   tpep_pickup_datetime   100000 non-null  datetime64[ns]\n",
      " 2   tpep_dropoff_datetime  100000 non-null  datetime64[ns]\n",
      " 3   passenger_count        100000 non-null  int64         \n",
      " 4   trip_distance          100000 non-null  float64       \n",
      " 5   pickup_longitude       100000 non-null  float64       \n",
      " 6   pickup_latitude        100000 non-null  float64       \n",
      " 7   RatecodeID             100000 non-null  int64         \n",
      " 8   store_and_fwd_flag     100000 non-null  object        \n",
      " 9   dropoff_longitude      100000 non-null  float64       \n",
      " 10  dropoff_latitude       100000 non-null  float64       \n",
      " 11  payment_type           100000 non-null  int64         \n",
      " 12  fare_amount            100000 non-null  float64       \n",
      " 13  extra                  100000 non-null  float64       \n",
      " 14  mta_tax                100000 non-null  float64       \n",
      " 15  tip_amount             100000 non-null  float64       \n",
      " 16  tolls_amount           100000 non-null  float64       \n",
      " 17  improvement_surcharge  100000 non-null  float64       \n",
      " 18  total_amount           100000 non-null  float64       \n",
      " 19  trip_id                100000 non-null  int64         \n",
      "dtypes: datetime64[ns](2), float64(12), int64(5), object(1)\n",
      "memory usage: 15.3+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ad915e45-3192-45f3-a326-3e4f7d7e0835",
   "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>pickup_hour</th>\n",
       "      <th>pickup_day</th>\n",
       "      <th>pickup_month</th>\n",
       "      <th>pickup_year</th>\n",
       "      <th>pickup_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>2016-03-01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 00:07:55</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 00:11:06</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 00:31:06</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   datetime_id tpep_pickup_datetime  pickup_hour  pickup_day  pickup_month  \\\n",
       "0            0           2016-03-01            0           1             3   \n",
       "1            1           2016-03-01            0           1             3   \n",
       "2            2           2016-03-01            0           1             3   \n",
       "3            3           2016-03-01            0           1             3   \n",
       "4            4           2016-03-01            0           1             3   \n",
       "\n",
       "   pickup_year  pickup_weekday tpep_dropoff_datetime  drop_hour  drop_day  \\\n",
       "0         2016               1   2016-03-01 00:07:55          0         1   \n",
       "1         2016               1   2016-03-01 00:11:06          0         1   \n",
       "2         2016               1   2016-03-01 00:31:06          0         1   \n",
       "3         2016               1   2016-03-01 00:00:00          0         1   \n",
       "4         2016               1   2016-03-01 00:00:00          0         1   \n",
       "\n",
       "   drop_month  drop_year  drop_weekday  \n",
       "0           3       2016             1  \n",
       "1           3       2016             1  \n",
       "2           3       2016             1  \n",
       "3           3       2016             1  \n",
       "4           3       2016             1  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime_dim = df[['tpep_pickup_datetime','tpep_dropoff_datetime']].reset_index(drop = True)\n",
    "\n",
    "datetime_dim['datetime_id'] = datetime_dim.index\n",
    "\n",
    "datetime_dim['pickup_hour'] = datetime_dim['tpep_pickup_datetime'].dt.hour\n",
    "datetime_dim['pickup_day'] = datetime_dim['tpep_pickup_datetime'].dt.day\n",
    "datetime_dim['pickup_month'] = datetime_dim['tpep_pickup_datetime'].dt.month\n",
    "datetime_dim['pickup_year'] = datetime_dim['tpep_pickup_datetime'].dt.year\n",
    "datetime_dim['pickup_weekday'] = datetime_dim['tpep_pickup_datetime'].dt.weekday\n",
    "\n",
    "\n",
    "datetime_dim['drop_hour'] = datetime_dim['tpep_dropoff_datetime'].dt.hour\n",
    "datetime_dim['drop_day'] = datetime_dim['tpep_dropoff_datetime'].dt.day\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",
    "datetime_dim = datetime_dim[['datetime_id', 'tpep_pickup_datetime', 'pickup_hour', 'pickup_day', 'pickup_month', 'pickup_year', 'pickup_weekday',\n",
    "                             'tpep_dropoff_datetime', 'drop_hour', 'drop_day', 'drop_month', 'drop_year', 'drop_weekday']]\n",
    "\n",
    "datetime_dim.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "22d1356b-d1a1-4ed7-b4ba-37b41b1226a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([3])"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime_dim.pickup_month.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5a6218fa-38ae-408d-9679-3c5c209d686b",
   "metadata": {},
   "outputs": [],
   "source": [
    "passenger_count_dim = df[['passenger_count']].reset_index(drop=True)\n",
    "passenger_count_dim['passenger_count_id'] = passenger_count_dim.index\n",
    "passenger_count_dim = passenger_count_dim[['passenger_count_id', 'passenger_count']]\n",
    "\n",
    "trip_distance_dim = df[['trip_distance']].reset_index(drop=True)\n",
    "trip_distance_dim['trip_distance_id'] = trip_distance_dim.index\n",
    "trip_distance_dim = trip_distance_dim[['trip_distance_id','trip_distance']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a67fcdbe-9935-465a-a017-62f75fe1a9c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "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']].reset_index(drop=True)\n",
    "rate_code_dim['rate_code_id'] = rate_code_dim.index\n",
    "rate_code_dim['rate_code_name'] = rate_code_dim['RatecodeID'].map(rate_code_type)\n",
    "rate_code_dim = rate_code_dim[['rate_code_id','RatecodeID','rate_code_name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3dc29148-4d79-4cf9-966b-6165b6090704",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickup_location_dim = df[['pickup_longitude', 'pickup_latitude']].reset_index(drop=True)\n",
    "pickup_location_dim['pickup_location_id'] = pickup_location_dim.index\n",
    "pickup_location_dim = pickup_location_dim[['pickup_location_id','pickup_latitude','pickup_longitude']] \n",
    "\n",
    "dropoff_location_dim = df[['dropoff_longitude', 'dropoff_latitude']].reset_index(drop=True)\n",
    "dropoff_location_dim['dropoff_location_id'] = dropoff_location_dim.index\n",
    "dropoff_location_dim = dropoff_location_dim[['dropoff_location_id','dropoff_latitude','dropoff_longitude']] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "15a56cf5-9c95-45d2-9fac-ca6a92341703",
   "metadata": {},
   "outputs": [],
   "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",
    "\n",
    "payment_type_dim = df[['payment_type']].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",
    "payment_type_dim = payment_type_dim[['payment_type_id','payment_type','payment_type_name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "484cf299-eeec-43d9-8b13-08c63c43cc39",
   "metadata": {},
   "outputs": [],
   "source": [
    "fact_table = df.merge(passenger_count_dim, left_on='trip_id', right_on='passenger_count_id') \\\n",
    "             .merge(trip_distance_dim, left_on='trip_id', right_on='trip_distance_id') \\\n",
    "             .merge(rate_code_dim, left_on='trip_id', right_on='rate_code_id') \\\n",
    "             .merge(pickup_location_dim, left_on='trip_id', right_on='pickup_location_id') \\\n",
    "             .merge(dropoff_location_dim, left_on='trip_id', right_on='dropoff_location_id')\\\n",
    "             .merge(datetime_dim, left_on='trip_id', right_on='datetime_id') \\\n",
    "             .merge(payment_type_dim, left_on='trip_id', right_on='payment_type_id') \\\n",
    "             [['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']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f1682866-971c-4d20-8449-dd6f1024fdac",
   "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>1</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.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>12.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.05</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>15.35</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>2</td>\n",
       "      <td>N</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>54.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>8.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>63.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>31.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.78</td>\n",
       "      <td>5.54</td>\n",
       "      <td>0.3</td>\n",
       "      <td>41.62</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>4</td>\n",
       "      <td>N</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>15.50</td>\n",
       "      <td>0.3</td>\n",
       "      <td>113.80</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>99995</td>\n",
       "      <td>1</td>\n",
       "      <td>99995</td>\n",
       "      <td>99995</td>\n",
       "      <td>99995</td>\n",
       "      <td>99995</td>\n",
       "      <td>N</td>\n",
       "      <td>99995</td>\n",
       "      <td>99995</td>\n",
       "      <td>99995</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>5.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>99996</td>\n",
       "      <td>1</td>\n",
       "      <td>99996</td>\n",
       "      <td>99996</td>\n",
       "      <td>99996</td>\n",
       "      <td>99996</td>\n",
       "      <td>N</td>\n",
       "      <td>99996</td>\n",
       "      <td>99996</td>\n",
       "      <td>99996</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>16.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>99997</td>\n",
       "      <td>1</td>\n",
       "      <td>99997</td>\n",
       "      <td>99997</td>\n",
       "      <td>99997</td>\n",
       "      <td>99997</td>\n",
       "      <td>N</td>\n",
       "      <td>99997</td>\n",
       "      <td>99997</td>\n",
       "      <td>99997</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>8.80</td>\n",
       "      <td>5.54</td>\n",
       "      <td>0.3</td>\n",
       "      <td>44.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>99998</td>\n",
       "      <td>2</td>\n",
       "      <td>99998</td>\n",
       "      <td>99998</td>\n",
       "      <td>99998</td>\n",
       "      <td>99998</td>\n",
       "      <td>N</td>\n",
       "      <td>99998</td>\n",
       "      <td>99998</td>\n",
       "      <td>99998</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.36</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>99999</td>\n",
       "      <td>1</td>\n",
       "      <td>99999</td>\n",
       "      <td>99999</td>\n",
       "      <td>99999</td>\n",
       "      <td>99999</td>\n",
       "      <td>N</td>\n",
       "      <td>99999</td>\n",
       "      <td>99999</td>\n",
       "      <td>99999</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.3</td>\n",
       "      <td>6.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       trip_id  VendorID  datetime_id  passenger_count_id  trip_distance_id  \\\n",
       "0            0         1            0                   0                 0   \n",
       "1            1         1            1                   1                 1   \n",
       "2            2         2            2                   2                 2   \n",
       "3            3         2            3                   3                 3   \n",
       "4            4         2            4                   4                 4   \n",
       "...        ...       ...          ...                 ...               ...   \n",
       "99995    99995         1        99995               99995             99995   \n",
       "99996    99996         1        99996               99996             99996   \n",
       "99997    99997         1        99997               99997             99997   \n",
       "99998    99998         2        99998               99998             99998   \n",
       "99999    99999         1        99999               99999             99999   \n",
       "\n",
       "       rate_code_id store_and_fwd_flag  pickup_location_id  \\\n",
       "0                 0                  N                   0   \n",
       "1                 1                  N                   1   \n",
       "2                 2                  N                   2   \n",
       "3                 3                  N                   3   \n",
       "4                 4                  N                   4   \n",
       "...             ...                ...                 ...   \n",
       "99995         99995                  N               99995   \n",
       "99996         99996                  N               99996   \n",
       "99997         99997                  N               99997   \n",
       "99998         99998                  N               99998   \n",
       "99999         99999                  N               99999   \n",
       "\n",
       "       dropoff_location_id  payment_type_id  fare_amount  extra  mta_tax  \\\n",
       "0                        0                0          9.0    0.5      0.5   \n",
       "1                        1                1         11.0    0.5      0.5   \n",
       "2                        2                2         54.5    0.5      0.5   \n",
       "3                        3                3         31.5    0.0      0.5   \n",
       "4                        4                4         98.0    0.0      0.0   \n",
       "...                    ...              ...          ...    ...      ...   \n",
       "99995                99995            99995          5.0    0.0      0.5   \n",
       "99996                99996            99996         14.0    0.0      0.5   \n",
       "99997                99997            99997         29.0    0.0      0.5   \n",
       "99998                99998            99998          5.5    0.5      0.5   \n",
       "99999                99999            99999          6.0    0.0      0.5   \n",
       "\n",
       "       tip_amount  tolls_amount  improvement_surcharge  total_amount  \n",
       "0            2.05          0.00                    0.3         12.35  \n",
       "1            3.05          0.00                    0.3         15.35  \n",
       "2            8.00          0.00                    0.3         63.80  \n",
       "3            3.78          5.54                    0.3         41.62  \n",
       "4            0.00         15.50                    0.3        113.80  \n",
       "...           ...           ...                    ...           ...  \n",
       "99995        0.00          0.00                    0.3          5.80  \n",
       "99996        2.00          0.00                    0.3         16.80  \n",
       "99997        8.80          5.54                    0.3         44.14  \n",
       "99998        1.36          0.00                    0.3          8.16  \n",
       "99999        0.00          0.00                    0.3          6.80  \n",
       "\n",
       "[100000 rows x 17 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fact_table"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}