Uber.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c31d06a2-a422-4c23-8f14-85a385293cee",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg2\n",
    "import sqldf\n",
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "307cfc5e-0671-4ab3-9b71-23ad5bf031a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "host = '******.########.******.rds.amazonaws.com'\n",
    "database = '******'\n",
    "user = '******'\n",
    "password = '******'\n",
    "port = '0000'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "47fb6dc1-d48f-45bd-8cf2-8f649eb205f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\n",
    "    host=host,\n",
    "    database=database,\n",
    "    user=user,\n",
    "    password=password,\n",
    "    port=port\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cb90583b-7819-4979-8fe0-b6a845ec12b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "557d52df-0627-4a53-8cdb-e844881525d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop_table = '''DROP TABLE IF EXISTS uber_data_dump;'''\n",
    "create_table_query = '''\n",
    "    CREATE TABLE IF NOT EXISTS uber_data_dump (\n",
    "        VendorID integer,\n",
    "        tpep_pickup_datetime timestamp,\n",
    "        tpep_dropoff_datetime timestamp,\n",
    "        passenger_count\tinteger,\n",
    "        trip_distance decimal(8,4),\n",
    "        pickup_longitude decimal(30,20),\n",
    "        pickup_latitude\tdecimal(30,20),\n",
    "        RatecodeID\tinteger,\n",
    "        store_and_fwd_flag varchar(1),\n",
    "        dropoff_longitude decimal(30,20),\n",
    "        dropoff_latitude decimal(30,20),\n",
    "        payment_type integer,\n",
    "        fare_amount decimal(8,4),\n",
    "        extra numeric(8,4),\n",
    "        mta_tax\tdecimal(8,4),\n",
    "        tip_amount decimal(8,4),\n",
    "        tolls_amount decimal(8,4),\n",
    "        improvement_surcharge decimal(8,4),\n",
    "        total_amount decimal(8,4)\n",
    "    );\n",
    "'''\n",
    "# cur.execute(drop_table)\n",
    "# cur.execute(create_table_query)\n",
    "# conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4e88b17c-4aee-4950-99b7-8eaef2898ef4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# In Terminal:\n",
    "# psql -h postgres-1.cvp1daggwpce.us-east-1.rds.amazonaws.com -U postgres -d postgres -p 5432\n",
    "\n",
    "# \\copy uber_data_dump FROM '/Users/aditi/Downloads/uber_data.csv' DELIMITER ',' CSV HEADER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "811457e3-ff48-4daf-8535-819f6a7ef694",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop_table1 = '''DROP TABLE IF EXISTS uber_fact;'''\n",
    "# drop_table2 = '''DROP TABLE IF EXISTS vendor_dim;'''\n",
    "# drop_table3 = '''DROP TABLE IF EXISTS date_dim;'''\n",
    "# drop_table4 = '''DROP TABLE IF EXISTS loc_dim;'''\n",
    "# drop_table5 = '''DROP TABLE IF EXISTS rate_dim;'''\n",
    "# drop_table6 = '''DROP TABLE IF EXISTS payment_type;'''\n",
    "# cur.execute(drop_table1)\n",
    "# cur.execute(drop_table2)\n",
    "# cur.execute(drop_table3) \n",
    "# cur.execute(drop_table4)\n",
    "# cur.execute(drop_table5)\n",
    "# cur.execute(drop_table6)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4a8fb8f8-9879-4773-a853-81bab68edfcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_fact_table = '''\n",
    "    CREATE TABLE IF NOT EXISTS uber_fact (\n",
    "        trip_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\n",
    "        VendorID integer,\n",
    "        pickup_date_id integer,\n",
    "        drop_date_id integer,\n",
    "        pickup_loc_id integer,\n",
    "        drop_loc_id integer,\n",
    "        rate_code_id integer,\n",
    "        payment_type_id integer,\n",
    "        passenger_count\tinteger,\n",
    "        store_and_fwd_flag varchar(1),\n",
    "        trip_distance decimal(8,4),\n",
    "        fare_amount decimal(8,4),\n",
    "        extra numeric(8,4),\n",
    "        mta_tax\tdecimal(8,4),\n",
    "        tip_amount decimal(8,4),\n",
    "        tolls_amount decimal(8,4),\n",
    "        improvement_surcharge decimal(8,4),\n",
    "        total_amount decimal(8,4)\n",
    "    );\n",
    "'''\n",
    "cur.execute(create_fact_table)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ae4ed7ec-772c-4584-8fc8-c629dd7cd812",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_dim_table = '''\n",
    "    CREATE TABLE IF NOT EXISTS vendor_dim (\n",
    "        VendorID integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\n",
    "        Vendor_name varchar(20),\n",
    "        vendor_info varchar(50)\n",
    "    );\n",
    "'''\n",
    "cur.execute(create_dim_table)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4a3c3694-6d67-4512-b430-f8dd1e64c28b",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_dim_table = '''\n",
    "    CREATE TABLE IF NOT EXISTS date_dim (\n",
    "        date_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\n",
    "        date_time timestamp,\n",
    "        day integer,\n",
    "        month integer,\n",
    "        year integer,\n",
    "        hour integer,\n",
    "        min integer,\n",
    "        sec integer\n",
    "    );\n",
    "'''\n",
    "cur.execute(create_dim_table)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3dcd781f-b20f-4811-96ce-03f062488b73",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_dim_table = '''\n",
    "    CREATE TABLE IF NOT EXISTS loc_dim (\n",
    "        loc_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\n",
    "        longitude decimal(30,20),\n",
    "        latitude decimal(30,20)\n",
    "    );\n",
    "'''\n",
    "cur.execute(create_dim_table)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c577ee79-f846-4458-8f93-d6f419e9f824",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_dim_table = '''\n",
    "    CREATE TABLE IF NOT EXISTS rate_dim (\n",
    "        rate_code_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\n",
    "        rate_code_desc varchar(20)\n",
    "    );\n",
    "'''\n",
    "cur.execute(create_dim_table)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2390c2e9-dae9-4d2a-97be-9c24ad08c115",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_dim_table = '''\n",
    "    CREATE TABLE IF NOT EXISTS payment_type (\n",
    "        payment_type_code integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\n",
    "        Payment_type varchar(20)\n",
    "    );\n",
    "'''\n",
    "cur.execute(create_dim_table)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "231efa07-05b6-40c1-bfb4-a6375b95c126",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"/Users/aditi/Downloads/uber_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b54768e5-8db4-4296-810e-282e3b1a1989",
   "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": 16,
   "id": "304c25eb-5440-4ac8-9b2b-37a37ee2befd",
   "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": 17,
   "id": "45739574-1e27-47aa-a4b3-0aeeebe40b5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# reset_index(): resets the index numbers and stores the old indices in an index column\n",
    "# use drop = True to avoid adding extra column to store old indices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "72283eb9-7630-4245-9699-0e6435bce199",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8fb36b26-f2ff-40ad-871f-1f4c28ce5ac9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['trip_id'] = df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8ce69dc2-cf0d-46d8-b4cf-9931658c2c9d",
   "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>99998</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-01 06:17:10</td>\n",
       "      <td>2016-03-01 06:22:09</td>\n",
       "      <td>1</td>\n",
       "      <td>0.92</td>\n",
       "      <td>-73.984901</td>\n",
       "      <td>40.763111</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-73.970695</td>\n",
       "      <td>40.759148</td>\n",
       "      <td>1</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.36</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>8.16</td>\n",
       "      <td>99998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-03-01 06:17:11</td>\n",
       "      <td>2016-03-01 06:22:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "      <td>-73.990685</td>\n",
       "      <td>40.750473</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>-73.980354</td>\n",
       "      <td>40.754910</td>\n",
       "      <td>2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>6.80</td>\n",
       "      <td>99999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "99998         2  2016-03-01 06:17:10   2016-03-01 06:22:09                1   \n",
       "99999         1  2016-03-01 06:17:11   2016-03-01 06:22:00                1   \n",
       "\n",
       "       trip_distance  pickup_longitude  pickup_latitude  RatecodeID  \\\n",
       "99998           0.92        -73.984901        40.763111           1   \n",
       "99999           1.00        -73.990685        40.750473           1   \n",
       "\n",
       "      store_and_fwd_flag  dropoff_longitude  dropoff_latitude  payment_type  \\\n",
       "99998                  N         -73.970695         40.759148             1   \n",
       "99999                  N         -73.980354         40.754910             2   \n",
       "\n",
       "       fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "99998          5.5    0.5      0.5        1.36           0.0   \n",
       "99999          6.0    0.0      0.5        0.00           0.0   \n",
       "\n",
       "       improvement_surcharge  total_amount  trip_id  \n",
       "99998                    0.3          8.16    99998  \n",
       "99999                    0.3          6.80    99999  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "66d1eab4-4226-4926-9cb6-20bf9dcc0654",
   "metadata": {},
   "outputs": [],
   "source": [
    "date_dim = pd.Series.to_frame(df['tpep_pickup_datetime'].reset_index(drop=True), name = 'tpep_datetime')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6804c3a1-fa91-4a22-ad87-2a9263ed1b2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "date_dim = pd.Series.to_frame(pd.concat([date_dim['tpep_datetime'],df['tpep_dropoff_datetime'].\\\n",
    "                                         reset_index(drop=True)]), name = 'tpep_datetime')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "7a3b4c86-3c8b-4431-a48a-e76040dc9cda",
   "metadata": {},
   "outputs": [],
   "source": [
    "date_dim = date_dim.drop_duplicates().reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "f2b18324-4957-4352-b40e-6bdcf8df5498",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 47007 entries, 0 to 47006\n",
      "Data columns (total 1 columns):\n",
      " #   Column         Non-Null Count  Dtype         \n",
      "---  ------         --------------  -----         \n",
      " 0   tpep_datetime  47007 non-null  datetime64[ns]\n",
      "dtypes: datetime64[ns](1)\n",
      "memory usage: 367.4 KB\n"
     ]
    }
   ],
   "source": [
    "date_dim.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "b1a40194-02c5-48a5-b26d-6ef110f79262",
   "metadata": {},
   "outputs": [],
   "source": [
    "date_dim['date'] = date_dim['tpep_datetime'].dt.day\n",
    "date_dim['month'] = date_dim['tpep_datetime'].dt.month\n",
    "date_dim['year'] = date_dim['tpep_datetime'].dt.year\n",
    "date_dim['hour'] = date_dim['tpep_datetime'].dt.hour\n",
    "date_dim['minute'] = date_dim['tpep_datetime'].dt.minute\n",
    "date_dim['second'] = date_dim['tpep_datetime'].dt.second\n",
    "date_dim['date_id'] = date_dim.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "083c0100-cd2d-4f7f-891f-054b1d2785f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "date_dim = date_dim[['date_id', 'tpep_datetime', 'date', 'month', 'year', 'hour', 'minute', 'second']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3bde79b9-e15b-45b8-9e34-7a319b12d37f",
   "metadata": {},
   "outputs": [],
   "source": [
    "vendor_dim = pd.Series.to_frame(df['VendorID'].drop_duplicates().reset_index(drop=True), name = 'VendorID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "265dfa58-407b-4b3d-b520-8f56035fe442",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 1 columns):\n",
      " #   Column    Non-Null Count  Dtype\n",
      "---  ------    --------------  -----\n",
      " 0   VendorID  2 non-null      int64\n",
      "dtypes: int64(1)\n",
      "memory usage: 148.0 bytes\n"
     ]
    }
   ],
   "source": [
    "vendor_dim.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "bd7d9ac9-df37-45e2-8463-6aab2d8e1967",
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_dim = pd.Series.to_frame(df['pickup_longitude'].reset_index(drop=True), name = 'longitude')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7313c098-222a-4046-a983-a0d7531f23ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_dim = pd.Series.to_frame(pd.concat([loc_dim['longitude'],df['dropoff_longitude'].reset_index(drop=True)]),name = 'longitude')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "1b2a2b0a-a283-48b2-a5ea-5928301a6eda",
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_dim_lat = pd.Series.to_frame(df['pickup_latitude'].reset_index(drop=True), name = 'latitude')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "0a6cb6b0-d76e-42fa-bd3a-c94660fd7981",
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_dim_lat = pd.Series.to_frame(pd.concat([loc_dim_lat['latitude'],df['dropoff_latitude'].reset_index(drop=True)]),name = 'latitude')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "6ff2e0b2-25ef-4255-b0f2-b9ed24a1d973",
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_dim['latitude'] = loc_dim_lat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "8a70a9d5-cb0a-47d3-b7f7-80058825c4c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_dim['loc_id'] = loc_dim.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "206239eb-e7b5-46a7-9c49-a610860ce4b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_dim = loc_dim[['loc_id', 'longitude', 'latitude']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "8a03b91b-60d2-462e-b3c9-64e0535770a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "rate_dim = pd.Series.to_frame(df['RatecodeID'].drop_duplicates().reset_index(drop=True), name = 'RatecodeID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "75cb621c-f02e-4450-824e-ec619ce4586a",
   "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>RatecodeID</th>\n",
       "      <th>rate_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Standard rate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>JFK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Newark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Nassau or Westchester</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Negotiated fare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Group ride</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RatecodeID              rate_desc\n",
       "0           1          Standard rate\n",
       "1           3                    JFK\n",
       "2           2                 Newark\n",
       "3           5  Nassau or Westchester\n",
       "4           4        Negotiated fare\n",
       "5           6             Group ride"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rate_dim['rate_desc'] = ['Standard rate', 'JFK', 'Newark', 'Nassau or Westchester', 'Negotiated fare', 'Group ride']\n",
    "rate_dim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "32690b24-0f13-4420-8428-c342c05440ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "payment_type_dim = pd.DataFrame([1,2,3,4,5,6], columns = ['payment_type_id'])\n",
    "payment_type_dim['payment_type'] = ['Credit card', 'Cash', 'No charge', 'Dispute', 'Unknown', 'Voided trip']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "94bd135c-4ba3-4766-b23a-d717ba95a3db",
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Credit card</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Cash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>No charge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Dispute</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Voided trip</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   payment_type_id payment_type\n",
       "0                1  Credit card\n",
       "1                2         Cash\n",
       "2                3    No charge\n",
       "3                4      Dispute\n",
       "4                5      Unknown\n",
       "5                6  Voided trip"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payment_type_dim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "4ca0d6e8-151f-4cc8-9f45-a2252d794e78",
   "metadata": {},
   "outputs": [],
   "source": [
    "# the tables are: date_dim, vendor_dim, loc_dim, rate_dim, payment_type_dim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a414ceb-a022-4fab-82bd-dc7fda4676ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "043e690d-9d7e-45fc-b89f-b930e36c890a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}