5310_final_project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b7ce657f",
   "metadata": {},
   "source": [
    "# APAN5310 Group7 SuperTravel Scenario Project"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "872e49a4",
   "metadata": {},
   "source": [
    "## Group Member: Boyu Wang, Caisen Mo, Qinyun Zhang, Zhenyu Wang"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc4f6925",
   "metadata": {},
   "source": [
    "### Load CSVs and Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9feeea9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read data from each CSV file into separate dataframes\n",
    "car_rental_df = pd.read_csv('CarRentalData.csv')\n",
    "flight_df = pd.read_csv('flight.csv')\n",
    "hotel_booking_df = pd.read_csv('hotel_booking.csv')\n",
    "\n",
    "# Add a prefix to the columns in each dataframe to differentiate them after concatenation\n",
    "car_rental_df = car_rental_df.add_prefix('car_rental_')\n",
    "flight_df = flight_df.add_prefix('flight_')\n",
    "hotel_booking_df = hotel_booking_df.add_prefix('hotel_booking_')\n",
    "\n",
    "# Concatenate the dataframes side by side\n",
    "merged_df = pd.concat([car_rental_df, flight_df, hotel_booking_df], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1cc4fbc1",
   "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>car_rental_fuelType</th>\n",
       "      <th>car_rental_rating</th>\n",
       "      <th>car_rental_renterTripsTaken</th>\n",
       "      <th>car_rental_reviewCount</th>\n",
       "      <th>car_rental_location.city</th>\n",
       "      <th>car_rental_location.country</th>\n",
       "      <th>car_rental_location.latitude</th>\n",
       "      <th>car_rental_location.longitude</th>\n",
       "      <th>car_rental_location.state</th>\n",
       "      <th>car_rental_owner.id</th>\n",
       "      <th>...</th>\n",
       "      <th>hotel_booking_customer_type</th>\n",
       "      <th>hotel_booking_adr</th>\n",
       "      <th>hotel_booking_required_car_parking_spaces</th>\n",
       "      <th>hotel_booking_total_of_special_requests</th>\n",
       "      <th>hotel_booking_reservation_status</th>\n",
       "      <th>hotel_booking_reservation_status_date</th>\n",
       "      <th>hotel_booking_name</th>\n",
       "      <th>hotel_booking_email</th>\n",
       "      <th>hotel_booking_phone-number</th>\n",
       "      <th>hotel_booking_credit_card</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ELECTRIC</td>\n",
       "      <td>5.00</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>US</td>\n",
       "      <td>47.449107</td>\n",
       "      <td>-122.308841</td>\n",
       "      <td>WA</td>\n",
       "      <td>12847615.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>Ernest Barnes</td>\n",
       "      <td>Ernest.Barnes31@outlook.com</td>\n",
       "      <td>669-792-1661</td>\n",
       "      <td>************4322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ELECTRIC</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Tijeras</td>\n",
       "      <td>US</td>\n",
       "      <td>35.111060</td>\n",
       "      <td>-106.276551</td>\n",
       "      <td>NM</td>\n",
       "      <td>15621242.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>Andrea Baker</td>\n",
       "      <td>Andrea_Baker94@aol.com</td>\n",
       "      <td>858-637-6955</td>\n",
       "      <td>************9157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HYBRID</td>\n",
       "      <td>4.92</td>\n",
       "      <td>28.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>US</td>\n",
       "      <td>35.127163</td>\n",
       "      <td>-106.566681</td>\n",
       "      <td>NM</td>\n",
       "      <td>10199256.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-02</td>\n",
       "      <td>Rebecca Parker</td>\n",
       "      <td>Rebecca_Parker@comcast.net</td>\n",
       "      <td>652-885-2745</td>\n",
       "      <td>************3734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GASOLINE</td>\n",
       "      <td>5.00</td>\n",
       "      <td>21.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>US</td>\n",
       "      <td>35.149726</td>\n",
       "      <td>-106.711425</td>\n",
       "      <td>NM</td>\n",
       "      <td>9365496.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-02</td>\n",
       "      <td>Laura Murray</td>\n",
       "      <td>Laura_M@gmail.com</td>\n",
       "      <td>364-656-8427</td>\n",
       "      <td>************5677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GASOLINE</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>US</td>\n",
       "      <td>35.208659</td>\n",
       "      <td>-106.601008</td>\n",
       "      <td>NM</td>\n",
       "      <td>3553565.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-03</td>\n",
       "      <td>Linda Hines</td>\n",
       "      <td>LHines@verizon.com</td>\n",
       "      <td>713-226-5883</td>\n",
       "      <td>************5498</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  car_rental_fuelType  car_rental_rating  car_rental_renterTripsTaken   \n",
       "0            ELECTRIC               5.00                         13.0  \\\n",
       "1            ELECTRIC               5.00                          2.0   \n",
       "2              HYBRID               4.92                         28.0   \n",
       "3            GASOLINE               5.00                         21.0   \n",
       "4            GASOLINE               5.00                          3.0   \n",
       "\n",
       "   car_rental_reviewCount car_rental_location.city   \n",
       "0                    12.0                  Seattle  \\\n",
       "1                     1.0                  Tijeras   \n",
       "2                    24.0              Albuquerque   \n",
       "3                    20.0              Albuquerque   \n",
       "4                     1.0              Albuquerque   \n",
       "\n",
       "  car_rental_location.country  car_rental_location.latitude   \n",
       "0                          US                     47.449107  \\\n",
       "1                          US                     35.111060   \n",
       "2                          US                     35.127163   \n",
       "3                          US                     35.149726   \n",
       "4                          US                     35.208659   \n",
       "\n",
       "   car_rental_location.longitude car_rental_location.state   \n",
       "0                    -122.308841                        WA  \\\n",
       "1                    -106.276551                        NM   \n",
       "2                    -106.566681                        NM   \n",
       "3                    -106.711425                        NM   \n",
       "4                    -106.601008                        NM   \n",
       "\n",
       "   car_rental_owner.id  ...  hotel_booking_customer_type hotel_booking_adr   \n",
       "0           12847615.0  ...                    Transient               0.0  \\\n",
       "1           15621242.0  ...                    Transient               0.0   \n",
       "2           10199256.0  ...                    Transient              75.0   \n",
       "3            9365496.0  ...                    Transient              75.0   \n",
       "4            3553565.0  ...                    Transient              98.0   \n",
       "\n",
       "  hotel_booking_required_car_parking_spaces   \n",
       "0                                         0  \\\n",
       "1                                         0   \n",
       "2                                         0   \n",
       "3                                         0   \n",
       "4                                         0   \n",
       "\n",
       "  hotel_booking_total_of_special_requests  hotel_booking_reservation_status   \n",
       "0                                       0                         Check-Out  \\\n",
       "1                                       0                         Check-Out   \n",
       "2                                       0                         Check-Out   \n",
       "3                                       0                         Check-Out   \n",
       "4                                       1                         Check-Out   \n",
       "\n",
       "   hotel_booking_reservation_status_date  hotel_booking_name   \n",
       "0                             2015-07-01       Ernest Barnes  \\\n",
       "1                             2015-07-01        Andrea Baker   \n",
       "2                             2015-07-02      Rebecca Parker   \n",
       "3                             2015-07-02        Laura Murray   \n",
       "4                             2015-07-03         Linda Hines   \n",
       "\n",
       "           hotel_booking_email hotel_booking_phone-number   \n",
       "0  Ernest.Barnes31@outlook.com               669-792-1661  \\\n",
       "1       Andrea_Baker94@aol.com               858-637-6955   \n",
       "2   Rebecca_Parker@comcast.net               652-885-2745   \n",
       "3            Laura_M@gmail.com               364-656-8427   \n",
       "4           LHines@verizon.com               713-226-5883   \n",
       "\n",
       "  hotel_booking_credit_card  \n",
       "0          ************4322  \n",
       "1          ************9157  \n",
       "2          ************3734  \n",
       "3          ************5677  \n",
       "4          ************5498  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4be6ee29",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in merged_df: 119390\n"
     ]
    }
   ],
   "source": [
    "num_rows = merged_df.shape[0]\n",
    "print(\"Number of rows in merged_df:\", num_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "38e9a16c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in car_rental_df: 5851\n",
      "Number of rows in flight_df: 28820\n",
      "Number of rows in hotel_booking_df: 119390\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of rows in car_rental_df:\", car_rental_df.shape[0])\n",
    "print(\"Number of rows in flight_df:\", flight_df.shape[0])\n",
    "print(\"Number of rows in hotel_booking_df:\", hotel_booking_df.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "abcc60fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_rows_to_keep = car_rental_df.shape[0]\n",
    "merged_df = merged_df.head(num_rows_to_keep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "28087f8f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in merged_df: 5851\n"
     ]
    }
   ],
   "source": [
    "num_rows = merged_df.shape[0]\n",
    "print(\"Number of rows in merged_df:\", num_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "281d1cbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_csv('merged_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5a7a4792",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('merged_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7e5c684d",
   "metadata": {
    "scrolled": true
   },
   "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>car_rental_fuelType</th>\n",
       "      <th>car_rental_rating</th>\n",
       "      <th>car_rental_renterTripsTaken</th>\n",
       "      <th>car_rental_reviewCount</th>\n",
       "      <th>car_rental_location.city</th>\n",
       "      <th>car_rental_location.country</th>\n",
       "      <th>car_rental_location.latitude</th>\n",
       "      <th>car_rental_location.longitude</th>\n",
       "      <th>car_rental_location.state</th>\n",
       "      <th>car_rental_owner.id</th>\n",
       "      <th>...</th>\n",
       "      <th>hotel_booking_customer_type</th>\n",
       "      <th>hotel_booking_adr</th>\n",
       "      <th>hotel_booking_required_car_parking_spaces</th>\n",
       "      <th>hotel_booking_total_of_special_requests</th>\n",
       "      <th>hotel_booking_reservation_status</th>\n",
       "      <th>hotel_booking_reservation_status_date</th>\n",
       "      <th>hotel_booking_name</th>\n",
       "      <th>hotel_booking_email</th>\n",
       "      <th>hotel_booking_phone-number</th>\n",
       "      <th>hotel_booking_credit_card</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ELECTRIC</td>\n",
       "      <td>5.00</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>US</td>\n",
       "      <td>47.449107</td>\n",
       "      <td>-122.308841</td>\n",
       "      <td>WA</td>\n",
       "      <td>12847615.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>Ernest Barnes</td>\n",
       "      <td>Ernest.Barnes31@outlook.com</td>\n",
       "      <td>669-792-1661</td>\n",
       "      <td>************4322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ELECTRIC</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Tijeras</td>\n",
       "      <td>US</td>\n",
       "      <td>35.111060</td>\n",
       "      <td>-106.276551</td>\n",
       "      <td>NM</td>\n",
       "      <td>15621242.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>Andrea Baker</td>\n",
       "      <td>Andrea_Baker94@aol.com</td>\n",
       "      <td>858-637-6955</td>\n",
       "      <td>************9157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HYBRID</td>\n",
       "      <td>4.92</td>\n",
       "      <td>28.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>US</td>\n",
       "      <td>35.127163</td>\n",
       "      <td>-106.566681</td>\n",
       "      <td>NM</td>\n",
       "      <td>10199256.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-02</td>\n",
       "      <td>Rebecca Parker</td>\n",
       "      <td>Rebecca_Parker@comcast.net</td>\n",
       "      <td>652-885-2745</td>\n",
       "      <td>************3734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GASOLINE</td>\n",
       "      <td>5.00</td>\n",
       "      <td>21.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>US</td>\n",
       "      <td>35.149726</td>\n",
       "      <td>-106.711425</td>\n",
       "      <td>NM</td>\n",
       "      <td>9365496.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-02</td>\n",
       "      <td>Laura Murray</td>\n",
       "      <td>Laura_M@gmail.com</td>\n",
       "      <td>364-656-8427</td>\n",
       "      <td>************5677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GASOLINE</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Albuquerque</td>\n",
       "      <td>US</td>\n",
       "      <td>35.208659</td>\n",
       "      <td>-106.601008</td>\n",
       "      <td>NM</td>\n",
       "      <td>3553565.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Transient</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Check-Out</td>\n",
       "      <td>2015-07-03</td>\n",
       "      <td>Linda Hines</td>\n",
       "      <td>LHines@verizon.com</td>\n",
       "      <td>713-226-5883</td>\n",
       "      <td>************5498</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  car_rental_fuelType  car_rental_rating  car_rental_renterTripsTaken   \n",
       "0            ELECTRIC               5.00                         13.0  \\\n",
       "1            ELECTRIC               5.00                          2.0   \n",
       "2              HYBRID               4.92                         28.0   \n",
       "3            GASOLINE               5.00                         21.0   \n",
       "4            GASOLINE               5.00                          3.0   \n",
       "\n",
       "   car_rental_reviewCount car_rental_location.city   \n",
       "0                    12.0                  Seattle  \\\n",
       "1                     1.0                  Tijeras   \n",
       "2                    24.0              Albuquerque   \n",
       "3                    20.0              Albuquerque   \n",
       "4                     1.0              Albuquerque   \n",
       "\n",
       "  car_rental_location.country  car_rental_location.latitude   \n",
       "0                          US                     47.449107  \\\n",
       "1                          US                     35.111060   \n",
       "2                          US                     35.127163   \n",
       "3                          US                     35.149726   \n",
       "4                          US                     35.208659   \n",
       "\n",
       "   car_rental_location.longitude car_rental_location.state   \n",
       "0                    -122.308841                        WA  \\\n",
       "1                    -106.276551                        NM   \n",
       "2                    -106.566681                        NM   \n",
       "3                    -106.711425                        NM   \n",
       "4                    -106.601008                        NM   \n",
       "\n",
       "   car_rental_owner.id  ...  hotel_booking_customer_type hotel_booking_adr   \n",
       "0           12847615.0  ...                    Transient               0.0  \\\n",
       "1           15621242.0  ...                    Transient               0.0   \n",
       "2           10199256.0  ...                    Transient              75.0   \n",
       "3            9365496.0  ...                    Transient              75.0   \n",
       "4            3553565.0  ...                    Transient              98.0   \n",
       "\n",
       "  hotel_booking_required_car_parking_spaces   \n",
       "0                                         0  \\\n",
       "1                                         0   \n",
       "2                                         0   \n",
       "3                                         0   \n",
       "4                                         0   \n",
       "\n",
       "  hotel_booking_total_of_special_requests  hotel_booking_reservation_status   \n",
       "0                                       0                         Check-Out  \\\n",
       "1                                       0                         Check-Out   \n",
       "2                                       0                         Check-Out   \n",
       "3                                       0                         Check-Out   \n",
       "4                                       1                         Check-Out   \n",
       "\n",
       "   hotel_booking_reservation_status_date  hotel_booking_name   \n",
       "0                             2015-07-01       Ernest Barnes  \\\n",
       "1                             2015-07-01        Andrea Baker   \n",
       "2                             2015-07-02      Rebecca Parker   \n",
       "3                             2015-07-02        Laura Murray   \n",
       "4                             2015-07-03         Linda Hines   \n",
       "\n",
       "           hotel_booking_email hotel_booking_phone-number   \n",
       "0  Ernest.Barnes31@outlook.com               669-792-1661  \\\n",
       "1       Andrea_Baker94@aol.com               858-637-6955   \n",
       "2   Rebecca_Parker@comcast.net               652-885-2745   \n",
       "3            Laura_M@gmail.com               364-656-8427   \n",
       "4           LHines@verizon.com               713-226-5883   \n",
       "\n",
       "  hotel_booking_credit_card  \n",
       "0          ************4322  \n",
       "1          ************9157  \n",
       "2          ************3734  \n",
       "3          ************5677  \n",
       "4          ************5498  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06f4a5ea",
   "metadata": {},
   "source": [
    "### Connect to PostgreSQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "44f9edf1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting to the PostgreSQL database...\n",
      "PostgreSQL database version:\n",
      "('PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit',)\n"
     ]
    }
   ],
   "source": [
    "#connect to postgressql database \n",
    "\n",
    "import psycopg, os\n",
    "print('Connecting to the PostgreSQL database...')\n",
    "conn = psycopg.connect(\n",
    "    host=\"localhost\",\n",
    "    port='5432',\n",
    "    dbname=\"5310_final_project\",\n",
    "    user=\"postgres\",\n",
    "    password=\"123\")\n",
    "\n",
    "# create a cursor\n",
    "cur = conn.cursor()\n",
    "\n",
    "# execute a statement\n",
    "print('PostgreSQL database version:')\n",
    "cur.execute('SELECT version()')\n",
    "\n",
    "# display the PostgreSQL database server version\n",
    "db_version = cur.fetchone()\n",
    "print(db_version)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77b17afe",
   "metadata": {},
   "source": [
    "### Create Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f316b956",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"DROP TABLE IF EXISTS car_rental_reservation\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS car_rental_user\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS rental\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS vehicle\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS renter\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS guest\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS customer_type\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS hotel_booking\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS room_type\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS hotel\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS flight_user\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS flight\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS airline\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS payment_info\")\n",
    "cur.execute(\"DROP TABLE IF EXISTS users\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "426613c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create Tables \n",
    "createCmd = \"\"\"\n",
    "CREATE TABLE users (\n",
    "    user_id SERIAL PRIMARY KEY,\n",
    "    first_name VARCHAR(50),\n",
    "    last_name VARCHAR(50),\n",
    "    email VARCHAR(100),\n",
    "    phone_number VARCHAR(20)\n",
    ");\n",
    "\n",
    "CREATE TABLE payment_info (\n",
    "    id SERIAL,\n",
    "    payment_type_id SERIAL NOT NULL PRIMARY KEY, \n",
    "    account_details VARCHAR(100),\n",
    "    FOREIGN KEY (id) REFERENCES users(user_id)\n",
    ");\n",
    "\n",
    "CREATE TABLE airline (\n",
    "    airline_id SERIAL PRIMARY KEY,\n",
    "    carrier_name VARCHAR(50)\n",
    ");\n",
    "\n",
    "CREATE TABLE flight (\n",
    "    flight_id SERIAL PRIMARY KEY,\n",
    "    airline_id SERIAL NOT NULL,\n",
    "    tail_num VARCHAR(50),\n",
    "    dest VARCHAR(50),\n",
    "    elapsed_time INT,\n",
    "    dep_time INT,\n",
    "    weather_condition VARCHAR(100),\n",
    "    FOREIGN KEY (airline_id) REFERENCES airline(airline_id)\n",
    ");\n",
    "\n",
    "CREATE TABLE flight_user (\n",
    "    id SERIAL PRIMARY KEY,\n",
    "    user_id INT NOT NULL,\n",
    "    flight_id INT NOT NULL,\n",
    "    FOREIGN KEY (user_id) REFERENCES users(user_id),\n",
    "    FOREIGN KEY (flight_id) REFERENCES flight(flight_id)\n",
    ");\n",
    "\n",
    "CREATE TABLE hotel (\n",
    "    hotel_id SERIAL PRIMARY KEY,\n",
    "    hotel_type VARCHAR(50)\n",
    ");\n",
    "\n",
    "CREATE TABLE room_type (\n",
    "    room_type_id SERIAL PRIMARY KEY,\n",
    "    room_type_name VARCHAR(50)\n",
    ");\n",
    "\n",
    "CREATE TABLE hotel_booking (\n",
    "    booking_id SERIAL PRIMARY KEY,\n",
    "    hotel_id INT NOT NULL,\n",
    "    room_type_id INT NOT NULL,\n",
    "    is_canceled BOOLEAN,\n",
    "    arrival_date_year INT,\n",
    "    arrival_date_month VARCHAR(20),\n",
    "    arrival_date_week_number INT,\n",
    "    arrival_date_day_of_month INT,\n",
    "    lead_time INT,\n",
    "    stays_in_weekend_nights INT,\n",
    "    stays_in_week_nights INT,\n",
    "    FOREIGN KEY (hotel_id) REFERENCES hotel(hotel_id),\n",
    "    FOREIGN KEY (room_type_id) REFERENCES room_type(room_type_id)\n",
    ");\n",
    "\n",
    "CREATE TABLE customer_type (\n",
    "    customer_type_id SERIAL PRIMARY KEY,\n",
    "    customer_type_name VARCHAR(50)\n",
    ");\n",
    "\n",
    "CREATE TABLE guest (\n",
    "    guest_id SERIAL,\n",
    "    booking_id INT NOT NULL,\n",
    "    adults INT,\n",
    "    children INT,\n",
    "    babies INT,\n",
    "    previous_cancellations INT,\n",
    "    previous_bookings_not_canceled INT,\n",
    "    is_repeated_guest BOOLEAN,\n",
    "    deposit_type VARCHAR(50),\n",
    "    customer_type_id INT,\n",
    "    required_car_parking_spaces INT,\n",
    "    FOREIGN KEY (guest_id) REFERENCES users(user_id),\n",
    "    FOREIGN KEY (booking_id) REFERENCES hotel_booking(booking_id),\n",
    "    FOREIGN KEY (customer_type_id) REFERENCES customer_type(customer_type_id)\n",
    ");\n",
    "\n",
    "CREATE TABLE renter (\n",
    "    renter_id INT PRIMARY KEY,\n",
    "    trips_taken INT,\n",
    "    review_count INT,\n",
    "    user_id INT NOT NULL,\n",
    "    FOREIGN KEY (user_id) REFERENCES users(user_id)\n",
    ");\n",
    "\n",
    "CREATE TABLE vehicle (\n",
    "    vehicle_id SERIAL PRIMARY KEY,\n",
    "    fuel_type VARCHAR(50),\n",
    "    rating FLOAT,\n",
    "    make VARCHAR(100),\n",
    "    model VARCHAR(100),\n",
    "    type VARCHAR(50),\n",
    "    year INT\n",
    ");\n",
    "\n",
    "CREATE TABLE rental (\n",
    "    rental_id SERIAL PRIMARY KEY,\n",
    "    vehicle_id INT NOT NULL,\n",
    "    daily_rate NUMERIC(10, 2),\n",
    "    FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id)\n",
    ");\n",
    "\n",
    "CREATE TABLE car_rental_user (\n",
    "    car_rental_user_id SERIAL PRIMARY KEY,\n",
    "    first_name VARCHAR(50),\n",
    "    last_name VARCHAR(50),\n",
    "    email VARCHAR(100),\n",
    "    phone_number VARCHAR(20)\n",
    ");\n",
    "\n",
    "CREATE TABLE car_rental_reservation (\n",
    "    reservation_id SERIAL PRIMARY KEY,\n",
    "    user_id INT NOT NULL,\n",
    "    rental_id INT NOT NULL,\n",
    "    renter_id INT NOT NULL,\n",
    "    vehicle_id INT NOT NULL,\n",
    "    FOREIGN KEY (user_id) REFERENCES car_rental_user(car_rental_user_id),\n",
    "    FOREIGN KEY (rental_id) REFERENCES rental(rental_id),\n",
    "    FOREIGN KEY (renter_id) REFERENCES renter(renter_id),\n",
    "    FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id)\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "cur.execute(createCmd)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07b5b27b",
   "metadata": {},
   "source": [
    "# Building ETL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db05c5fd",
   "metadata": {},
   "source": [
    "### for `user` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "54bbd142",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     first_name last_name                        email  phone_number\n",
      "0        Ernest    Barnes  Ernest.Barnes31@outlook.com  669-792-1661\n",
      "1        Andrea     Baker       Andrea_Baker94@aol.com  858-637-6955\n",
      "2       Rebecca    Parker   Rebecca_Parker@comcast.net  652-885-2745\n",
      "3         Laura    Murray            Laura_M@gmail.com  364-656-8427\n",
      "4         Linda     Hines           LHines@verizon.com  713-226-5883\n",
      "...         ...       ...                          ...           ...\n",
      "5846       John     Baker     John_Baker88@hotmail.com  348-959-7741\n",
      "5847    Jeffrey     Kelly    Jeffrey.Kelly@verizon.com  523-534-3341\n",
      "5848    Crystal    Turner  Crystal.Turner76@yandex.com  405-916-5125\n",
      "5849     Rodney    Morris           RMorris@yandex.com  272-323-1646\n",
      "5850    Cynthia  Williams           Cynthia_W@zoho.com  379-567-1171\n",
      "\n",
      "[5851 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "selected_columns = df[['hotel_booking_name', 'hotel_booking_email', 'hotel_booking_phone-number']].copy()\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "selected_columns.rename(columns={\n",
    "    'hotel_booking_name': 'name',\n",
    "    'hotel_booking_email': 'email',\n",
    "    'hotel_booking_phone-number': 'phone_number'\n",
    "}, inplace=True)\n",
    "\n",
    "# Separate the 'first_name' column into 'first_name' and 'last_name' columns\n",
    "selected_columns[['first_name', 'last_name']] = selected_columns['name'].str.split(' ',n=1, expand=True)\n",
    "\n",
    "# Rearrange the columns in the desired order\n",
    "user_df = selected_columns[['first_name', 'last_name', 'email', 'phone_number']]\n",
    "\n",
    "print(user_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8771ed8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO users (first_name, last_name, email, phone_number)\n",
    "    VALUES (%s, %s, %s, %s);\n",
    "               '''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'users' table\n",
    "for _, row in user_df.iterrows():\n",
    "    cur.execute(insert_query, (row['first_name'], row['last_name'], row['email'], row['phone_number']))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4f8fad8",
   "metadata": {},
   "source": [
    "### for `payment_info` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b6e38700",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       account_details\n",
      "0     ************4322\n",
      "1     ************9157\n",
      "2     ************3734\n",
      "3     ************5677\n",
      "4     ************5498\n",
      "...                ...\n",
      "5846  ************8928\n",
      "5847  ************4652\n",
      "5848  ************3002\n",
      "5849  ************3950\n",
      "5850  ************6759\n",
      "\n",
      "[5851 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "payment_info_df = df[['hotel_booking_credit_card']].copy()\n",
    "\n",
    "# Rename the column to 'account_details'\n",
    "payment_info_df.rename(columns={'hotel_booking_credit_card': 'account_details'}, inplace=True)\n",
    "\n",
    "# Display the 'payment_info_df' DataFrame\n",
    "print(payment_info_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6246a5e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO payment_info (account_details)\n",
    "    VALUES (%s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'payment_info' table\n",
    "for _, row in payment_info_df.iterrows():\n",
    "    cur.execute(insert_query, (row['account_details'],))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "280104f8",
   "metadata": {},
   "source": [
    "### for `airline` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "43134987",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     carrier_name\n",
      "0              B6\n",
      "1              B6\n",
      "2              B6\n",
      "3              B6\n",
      "4              DL\n",
      "...           ...\n",
      "5846           B6\n",
      "5847           DL\n",
      "5848           DL\n",
      "5849           DL\n",
      "5850           DL\n",
      "\n",
      "[5851 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "airline_df = df[['flight_OP_UNIQUE_CARRIER']].copy()\n",
    "\n",
    "# Rename the column to 'carrier_name'\n",
    "airline_df.rename(columns={'flight_OP_UNIQUE_CARRIER': 'carrier_name'}, inplace=True)\n",
    "\n",
    "# Display the 'airline_df' DataFrame\n",
    "print(airline_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "22701d9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO airline (carrier_name)\n",
    "    VALUES (%s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'airline' table\n",
    "for _, row in airline_df.iterrows():\n",
    "    cur.execute(insert_query, (row['carrier_name'],))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dd76d19",
   "metadata": {},
   "source": [
    "### for `flight` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "80fe23d7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     tail_num dest  elapsed_time  dep_time weather_condition\n",
      "0      N828JB  CHS         124.0     323.0      Fair / Windy\n",
      "1      N992JB  LAX         371.0     333.0      Fair / Windy\n",
      "2      N959JB  FLL         181.0     341.0      Fair / Windy\n",
      "3      N999JQ  MCO         168.0     343.0      Fair / Windy\n",
      "4      N880DN  ATL         139.0     356.0      Fair / Windy\n",
      "...       ...  ...           ...       ...               ...\n",
      "5846   N965JT  PHX         337.0    1143.0        Light Rain\n",
      "5847   N879DN  SJU         234.0    1146.0        Light Rain\n",
      "5848   N341NW  MIA         194.0    1156.0        Light Rain\n",
      "5849   N179DN  LAX         387.0    1157.0        Light Rain\n",
      "5850   N338NW  TPA         179.0    1159.0        Light Rain\n",
      "\n",
      "[5851 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "flight_df = df[['flight_TAIL_NUM', 'flight_DEST', 'flight_CRS_ELAPSED_TIME', 'flight_DEP_TIME_M', 'flight_Condition']].copy()\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "flight_df.rename(columns={\n",
    "    'flight_TAIL_NUM': 'tail_num',\n",
    "    'flight_DEST': 'dest',\n",
    "    'flight_CRS_ELAPSED_TIME': 'elapsed_time',\n",
    "    'flight_DEP_TIME_M': 'dep_time',\n",
    "    'flight_Condition': 'weather_condition'\n",
    "}, inplace=True)\n",
    "\n",
    "# Display the 'flight_df' DataFrame\n",
    "print(flight_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d0534375",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO flight (tail_num, dest, elapsed_time, dep_time, weather_condition)\n",
    "    VALUES (%s, %s, %s, %s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'flight' table\n",
    "for _, row in flight_df.iterrows():\n",
    "    cur.execute(insert_query, (\n",
    "        row['tail_num'],\n",
    "        row['dest'],\n",
    "        row['elapsed_time'],\n",
    "        row['dep_time'],\n",
    "        row['weather_condition']\n",
    "    ))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb7de939",
   "metadata": {},
   "source": [
    "### for `flight_user` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7399fbb6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      user_id  flight_id\n",
      "0        5464        635\n",
      "1        3373       4116\n",
      "2        4773       2215\n",
      "3         185       4480\n",
      "4        5725       1844\n",
      "...       ...        ...\n",
      "5846      204       2652\n",
      "5847     3841       1067\n",
      "5848     2273       4781\n",
      "5849     5678       1153\n",
      "5850      459       2878\n",
      "\n",
      "[5851 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# because we have no data in this relation table, so we generate some mock data to testify our schema.\n",
    "import random\n",
    "\n",
    "# Number of rows in the DataFrame\n",
    "num_rows = 5851\n",
    "\n",
    "# Generate random unique pairs of integers\n",
    "random_pairs = set()\n",
    "while len(random_pairs) < num_rows:\n",
    "    pair = (random.randint(1, 5851), random.randint(1, 5851))\n",
    "    random_pairs.add(pair)\n",
    "\n",
    "# Create the DataFrame\n",
    "flight_user_df = pd.DataFrame(random_pairs, columns=['random_int_1', 'random_int_2'])\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "flight_user_df.rename(columns={\n",
    "    'random_int_1': 'user_id',\n",
    "    'random_int_2': 'flight_id'\n",
    "}, inplace=True)\n",
    "\n",
    "# Display the 'flight_user_df' DataFrame\n",
    "print(flight_user_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "40ca07cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO flight_user (user_id, flight_id)\n",
    "    VALUES (%s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'flight_user' table\n",
    "for _, row in flight_user_df.iterrows():\n",
    "    user_id = int(row['user_id'])\n",
    "    flight_id = int(row['flight_id'])\n",
    "    cur.execute(insert_query, (user_id, flight_id))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef702c4e",
   "metadata": {},
   "source": [
    "### for `hotel` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "15de428f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        hotel_type\n",
      "0     Resort Hotel\n",
      "1     Resort Hotel\n",
      "2     Resort Hotel\n",
      "3     Resort Hotel\n",
      "4     Resort Hotel\n",
      "...            ...\n",
      "5846  Resort Hotel\n",
      "5847  Resort Hotel\n",
      "5848  Resort Hotel\n",
      "5849  Resort Hotel\n",
      "5850  Resort Hotel\n",
      "\n",
      "[5851 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "hotel_df = df[['hotel_booking_hotel']].copy()\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "hotel_df.rename(columns={\n",
    "    'hotel_booking_hotel': 'hotel_type',\n",
    "}, inplace=True)\n",
    "\n",
    "# Display the 'hotel_df' DataFrame\n",
    "print(hotel_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "33331072",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO hotel (hotel_type)\n",
    "    VALUES (%s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'hotel' table\n",
    "for _, row in hotel_df.iterrows():\n",
    "    hotel_type = row['hotel_type']\n",
    "    cur.execute(insert_query, (hotel_type,))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "167b6d26",
   "metadata": {},
   "source": [
    "### for `room_type` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "704bc8f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     room_type_name\n",
      "0                 C\n",
      "1                 C\n",
      "2                 A\n",
      "3                 A\n",
      "4                 A\n",
      "...             ...\n",
      "5846              A\n",
      "5847              A\n",
      "5848              A\n",
      "5849              A\n",
      "5850              A\n",
      "\n",
      "[5851 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "room_type_df = df[['hotel_booking_reserved_room_type']].copy()\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "room_type_df.rename(columns={\n",
    "    'hotel_booking_reserved_room_type': 'room_type_name',\n",
    "}, inplace=True)\n",
    "\n",
    "# Display the 'room_type_df' DataFrame\n",
    "print(room_type_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4756add7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO room_type (room_type_name)\n",
    "    VALUES (%s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'room_type' table\n",
    "for _, row in room_type_df.iterrows():\n",
    "    room_type_name = row['room_type_name']\n",
    "    cur.execute(insert_query, (room_type_name,))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9242819d",
   "metadata": {},
   "source": [
    "### for `hotel_booking` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "cd9c8b74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      is_canceled  arrival_date_year arrival_date_month   \n",
      "0               0               2015               July  \\\n",
      "1               0               2015               July   \n",
      "2               0               2015               July   \n",
      "3               0               2015               July   \n",
      "4               0               2015               July   \n",
      "...           ...                ...                ...   \n",
      "5846            1               2016                May   \n",
      "5847            1               2016                May   \n",
      "5848            1               2016                May   \n",
      "5849            1               2016                May   \n",
      "5850            1               2016                May   \n",
      "\n",
      "      arrival_date_week_number  arrival_date_day_of_month  lead_time   \n",
      "0                           27                          1        342  \\\n",
      "1                           27                          1        737   \n",
      "2                           27                          1          7   \n",
      "3                           27                          1         13   \n",
      "4                           27                          1         14   \n",
      "...                        ...                        ...        ...   \n",
      "5846                        21                         15          9   \n",
      "5847                        21                         15          9   \n",
      "5848                        21                         15          9   \n",
      "5849                        21                         15          9   \n",
      "5850                        21                         15          9   \n",
      "\n",
      "      stays_in_weekend_nights  stays_in_week_nights  \n",
      "0                           0                     0  \n",
      "1                           0                     0  \n",
      "2                           0                     1  \n",
      "3                           0                     1  \n",
      "4                           0                     2  \n",
      "...                       ...                   ...  \n",
      "5846                        1                     0  \n",
      "5847                        1                     0  \n",
      "5848                        1                     0  \n",
      "5849                        1                     0  \n",
      "5850                        1                     0  \n",
      "\n",
      "[5851 rows x 8 columns]\n"
     ]
    }
   ],
   "source": [
    "hotel_booking_df = df[['hotel_booking_is_canceled', 'hotel_booking_arrival_date_year', \n",
    "                       'hotel_booking_arrival_date_month', 'hotel_booking_arrival_date_week_number', \n",
    "                       'hotel_booking_arrival_date_day_of_month', 'hotel_booking_lead_time',\n",
    "                       'hotel_booking_stays_in_weekend_nights', 'hotel_booking_stays_in_week_nights']].copy()\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "hotel_booking_df.rename(columns={\n",
    "    'hotel_booking_is_canceled': 'is_canceled',\n",
    "    'hotel_booking_arrival_date_year': 'arrival_date_year',\n",
    "    'hotel_booking_arrival_date_month': 'arrival_date_month',\n",
    "    'hotel_booking_arrival_date_week_number': 'arrival_date_week_number',\n",
    "    'hotel_booking_arrival_date_day_of_month': 'arrival_date_day_of_month',\n",
    "    'hotel_booking_lead_time': 'lead_time',\n",
    "    'hotel_booking_stays_in_weekend_nights': 'stays_in_weekend_nights',\n",
    "    'hotel_booking_stays_in_week_nights': 'stays_in_week_nights'\n",
    "}, inplace=True)\n",
    "\n",
    "# Display the 'hotel_booking_df' DataFrame\n",
    "print(hotel_booking_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "5b5187ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetch the generated hotel_id and room_type_id after inserting into the 'hotel' and 'room_type' tables\n",
    "cur.execute(\"SELECT hotel_id FROM hotel;\")\n",
    "hotel_ids = [row[0] for row in cur.fetchall()]\n",
    "\n",
    "\n",
    "cur.execute(\"SELECT room_type_id FROM room_type;\")\n",
    "room_type_ids = [row[0] for row in cur.fetchall()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5e570507",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO hotel_booking (hotel_id, room_type_id, is_canceled, arrival_date_year, arrival_date_month, arrival_date_week_number, arrival_date_day_of_month, lead_time, stays_in_weekend_nights, stays_in_week_nights)\n",
    "    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'hotel_booking' table\n",
    "for i, row in hotel_booking_df.iterrows():\n",
    "    is_canceled = bool(row['is_canceled'])\n",
    "    arrival_date_year = int(row['arrival_date_year'])\n",
    "    arrival_date_month = row['arrival_date_month']\n",
    "    arrival_date_week_number = int(row['arrival_date_week_number'])\n",
    "    arrival_date_day_of_month = int(row['arrival_date_day_of_month'])\n",
    "    lead_time = int(row['lead_time'])\n",
    "    stays_in_weekend_nights = int(row['stays_in_weekend_nights'])\n",
    "    stays_in_week_nights = int(row['stays_in_week_nights'])\n",
    "\n",
    "    # Use the fetched hotel_id and room_type_id for each booking and insert into the 'hotel_booking' table\n",
    "    cur.execute(insert_query, (hotel_ids[i], room_type_ids[i], is_canceled, arrival_date_year, arrival_date_month, arrival_date_week_number, arrival_date_day_of_month, lead_time, stays_in_weekend_nights, stays_in_week_nights))\n",
    "    \n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f92cfb0d",
   "metadata": {},
   "source": [
    "### for `customer_type` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "5b23a853",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     customer_type_name\n",
      "0             Transient\n",
      "1             Transient\n",
      "2             Transient\n",
      "3             Transient\n",
      "4             Transient\n",
      "...                 ...\n",
      "5846    Transient-Party\n",
      "5847    Transient-Party\n",
      "5848    Transient-Party\n",
      "5849    Transient-Party\n",
      "5850    Transient-Party\n",
      "\n",
      "[5851 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "customer_type_df = df[['hotel_booking_customer_type']].copy()\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "customer_type_df.rename(columns={\n",
    "    'hotel_booking_customer_type': 'customer_type_name'\n",
    "}, inplace=True)\n",
    "\n",
    "# Display the 'customer_type_df' DataFrame\n",
    "print(customer_type_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7e232c9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO customer_type (customer_type_name)\n",
    "    VALUES (%s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'customer_type' table\n",
    "for _, row in customer_type_df.iterrows():\n",
    "    customer_type_name = row['customer_type_name']\n",
    "    cur.execute(insert_query, (customer_type_name,))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a83a484",
   "metadata": {},
   "source": [
    "### for `guest` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "6bc8ff09",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      adults  children  babies  previous_cancellations   \n",
      "0          2       0.0       0                       0  \\\n",
      "1          2       0.0       0                       0   \n",
      "2          1       0.0       0                       0   \n",
      "3          1       0.0       0                       0   \n",
      "4          2       0.0       0                       0   \n",
      "...      ...       ...     ...                     ...   \n",
      "5846       2       0.0       0                       0   \n",
      "5847       2       0.0       0                       0   \n",
      "5848       2       0.0       0                       0   \n",
      "5849       2       0.0       0                       0   \n",
      "5850       2       0.0       0                       0   \n",
      "\n",
      "      previous_bookings_not_canceled  is_repeated_guest deposit_type   \n",
      "0                                  0                  0   No Deposit  \\\n",
      "1                                  0                  0   No Deposit   \n",
      "2                                  0                  0   No Deposit   \n",
      "3                                  0                  0   No Deposit   \n",
      "4                                  0                  0   No Deposit   \n",
      "...                              ...                ...          ...   \n",
      "5846                               0                  0   No Deposit   \n",
      "5847                               0                  0   No Deposit   \n",
      "5848                               0                  0   No Deposit   \n",
      "5849                               0                  0   No Deposit   \n",
      "5850                               0                  0   No Deposit   \n",
      "\n",
      "      required_car_parking_spaces  \n",
      "0                               0  \n",
      "1                               0  \n",
      "2                               0  \n",
      "3                               0  \n",
      "4                               0  \n",
      "...                           ...  \n",
      "5846                            0  \n",
      "5847                            0  \n",
      "5848                            0  \n",
      "5849                            0  \n",
      "5850                            0  \n",
      "\n",
      "[5851 rows x 8 columns]\n"
     ]
    }
   ],
   "source": [
    "guest_df = df[['hotel_booking_adults','hotel_booking_children','hotel_booking_babies','hotel_booking_previous_cancellations',\n",
    "               'hotel_booking_previous_bookings_not_canceled','hotel_booking_is_repeated_guest',\n",
    "               'hotel_booking_deposit_type','hotel_booking_required_car_parking_spaces']].copy()\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "guest_df.rename(columns={\n",
    "    'hotel_booking_adults': 'adults',\n",
    "    'hotel_booking_children': 'children',\n",
    "    'hotel_booking_babies': 'babies',\n",
    "    'hotel_booking_previous_cancellations': 'previous_cancellations',\n",
    "    'hotel_booking_previous_bookings_not_canceled': 'previous_bookings_not_canceled',\n",
    "    'hotel_booking_is_repeated_guest': 'is_repeated_guest',\n",
    "    'hotel_booking_deposit_type': 'deposit_type',\n",
    "    'hotel_booking_required_car_parking_spaces': 'required_car_parking_spaces' \n",
    "}, inplace=True)\n",
    "\n",
    "# Display the 'guest_df' DataFrame\n",
    "print(guest_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7e7a7735",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"SELECT booking_id FROM hotel_booking;\")\n",
    "booking_ids = [row[0] for row in cur.fetchall()]\n",
    "\n",
    "\n",
    "cur.execute(\"SELECT customer_type_id FROM customer_type;\")\n",
    "customer_type_ids = [row[0] for row in cur.fetchall()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "81359669",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_query = '''\n",
    "    INSERT INTO guest (booking_id, adults, children, babies, previous_cancellations, previous_bookings_not_canceled, \n",
    "    is_repeated_guest, deposit_type, customer_type_id, required_car_parking_spaces)\n",
    "    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);\n",
    "'''\n",
    "\n",
    "for i, row in guest_df.iterrows():\n",
    "    adults = int(row['adults'])\n",
    "    children = int(row['children'])\n",
    "    babies = int(row['babies'])\n",
    "    previous_cancellations = int(row['previous_cancellations'])\n",
    "    previous_bookings_not_canceled = int(row['previous_bookings_not_canceled'])\n",
    "    is_repeated_guest = bool(row['is_repeated_guest'])\n",
    "    deposit_type = row['deposit_type']\n",
    "    required_car_parking_spaces = row['required_car_parking_spaces']\n",
    "    cur.execute(insert_query, (booking_ids[i], adults, children, babies, previous_cancellations, previous_bookings_not_canceled, is_repeated_guest, deposit_type, customer_type_ids[i], required_car_parking_spaces))\n",
    "    \n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94a15563",
   "metadata": {},
   "source": [
    "### for `renter` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "62d9f64c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      renter_id  user_id  trips_taken  review_count\n",
      "0           722     2423         13.0          12.0\n",
      "1          1653     2678          2.0           1.0\n",
      "2          3432     5301         28.0          24.0\n",
      "3          4779     5032         21.0          20.0\n",
      "4          3708     2767          3.0           1.0\n",
      "...         ...      ...          ...           ...\n",
      "5846        626     1798         32.0          27.0\n",
      "5847       4874     1650         17.0          16.0\n",
      "5848       2314     3083         18.0          17.0\n",
      "5849       4935     5069          1.0           0.0\n",
      "5850       4135     4305         16.0          14.0\n",
      "\n",
      "[5851 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "# because we have no data in this relation table, so we generate some mock data to testify our schema.\n",
    "import random\n",
    "import pandas as pd\n",
    "\n",
    "# Number of rows in the DataFrame\n",
    "num_rows = 5851\n",
    "\n",
    "# Generate random unique 'renter_id' values\n",
    "unique_renter_ids = random.sample(range(1, num_rows + 1), num_rows)\n",
    "\n",
    "# Generate random unique 'user_id' values\n",
    "unique_user_ids = random.sample(range(1, num_rows + 1), num_rows)\n",
    "\n",
    "# Create the DataFrame with random 'renter_id' and 'user_id' pairs\n",
    "renter_id_df = pd.DataFrame({'renter_id': unique_renter_ids, 'user_id': unique_user_ids})\n",
    "\n",
    "# Assuming you have already defined df (your original DataFrame) in your code, the following lines will work correctly.\n",
    "renter_df = df[['car_rental_renterTripsTaken', 'car_rental_reviewCount']].copy()\n",
    "# Rename the column\n",
    "renter_df.rename(columns={'car_rental_renterTripsTaken': 'trips_taken', 'car_rental_reviewCount': 'review_count'}, inplace=True)\n",
    "\n",
    "# Concatenate renter_id_df and renter_df along columns to create combined_renter_df\n",
    "combined_renter_df = pd.concat([renter_id_df, renter_df], axis=1)\n",
    "print(combined_renter_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2f05f716",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO renter (renter_id, trips_taken, review_count, user_id)\n",
    "    VALUES (%s, %s, %s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'payment_info' table\n",
    "for _, row in combined_renter_df.iterrows():\n",
    "    cur.execute(insert_query, (\n",
    "        row['renter_id'],\n",
    "        int(row['trips_taken']),\n",
    "        int(row['review_count']),\n",
    "        row['user_id']\n",
    "        ))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4474f2f7",
   "metadata": {},
   "source": [
    "### for `vehicle` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "ee52b13a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      vehicle_id\n",
      "0           2407\n",
      "1           5045\n",
      "2            493\n",
      "3           3179\n",
      "4           3307\n",
      "...          ...\n",
      "5846        3581\n",
      "5847        4773\n",
      "5848        4490\n",
      "5849        1610\n",
      "5850        4285\n",
      "\n",
      "[5851 rows x 1 columns]\n",
      "     fuel_type  rating       make    model type    year  daily_rate\n",
      "0     ELECTRIC    5.00      Tesla  Model X  suv  2019.0       135.0\n",
      "1     ELECTRIC    5.00      Tesla  Model X  suv  2018.0       190.0\n",
      "2       HYBRID    4.92     Toyota    Prius  car  2012.0        35.0\n",
      "3     GASOLINE    5.00       Ford  Mustang  car  2018.0        75.0\n",
      "4     GASOLINE    5.00   Chrysler  Sebring  car  2010.0        47.0\n",
      "...        ...     ...        ...      ...  ...     ...         ...\n",
      "5846  GASOLINE    5.00  Chevrolet    Cruze  car  2017.0        33.0\n",
      "5847    HYBRID    5.00      Lexus  HS 250h  car  2010.0        49.0\n",
      "5848  GASOLINE    4.94      smart   fortwo  car  2013.0        35.0\n",
      "5849  GASOLINE     NaN        GMC   Savana  van  2015.0        77.0\n",
      "5850  GASOLINE    5.00      smart   fortwo  car  2013.0        35.0\n",
      "\n",
      "[5851 rows x 7 columns]\n",
      "      vehicle_id fuel_type  rating       make    model type    year   \n",
      "0           2407  ELECTRIC    5.00      Tesla  Model X  suv  2019.0  \\\n",
      "1           5045  ELECTRIC    5.00      Tesla  Model X  suv  2018.0   \n",
      "2            493    HYBRID    4.92     Toyota    Prius  car  2012.0   \n",
      "3           3179  GASOLINE    5.00       Ford  Mustang  car  2018.0   \n",
      "4           3307  GASOLINE    5.00   Chrysler  Sebring  car  2010.0   \n",
      "...          ...       ...     ...        ...      ...  ...     ...   \n",
      "5846        3581  GASOLINE    5.00  Chevrolet    Cruze  car  2017.0   \n",
      "5847        4773    HYBRID    5.00      Lexus  HS 250h  car  2010.0   \n",
      "5848        4490  GASOLINE    4.94      smart   fortwo  car  2013.0   \n",
      "5849        1610  GASOLINE     NaN        GMC   Savana  van  2015.0   \n",
      "5850        4285  GASOLINE    5.00      smart   fortwo  car  2013.0   \n",
      "\n",
      "      daily_rate  \n",
      "0          135.0  \n",
      "1          190.0  \n",
      "2           35.0  \n",
      "3           75.0  \n",
      "4           47.0  \n",
      "...          ...  \n",
      "5846        33.0  \n",
      "5847        49.0  \n",
      "5848        35.0  \n",
      "5849        77.0  \n",
      "5850        35.0  \n",
      "\n",
      "[5851 rows x 8 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "\n",
    "# Number of rows in the DataFrame\n",
    "num_rows = 5851\n",
    "\n",
    "# Generate random unique integers\n",
    "random_vehicle_ids = random.sample(range(1, num_rows + 1), num_rows)\n",
    "\n",
    "# Create the DataFrame\n",
    "vehicle_id_df = pd.DataFrame({'vehicle_id': random_vehicle_ids})\n",
    "\n",
    "print(vehicle_id_df)\n",
    "\n",
    "\n",
    "vehicle_df = df[['car_rental_fuelType','car_rental_rating',\n",
    "                 'car_rental_vehicle.make','car_rental_vehicle.model',\n",
    "                 'car_rental_vehicle.type','car_rental_vehicle.year',\n",
    "                 'car_rental_rate.daily']].copy()\n",
    "# Rename the column\n",
    "vehicle_df.rename(columns={'car_rental_fuelType': 'fuel_type', \n",
    "                          'car_rental_rating': 'rating',\n",
    "                          'car_rental_vehicle.make': 'make', \n",
    "                          'car_rental_vehicle.model': 'model',  \n",
    "                          'car_rental_vehicle.type': 'type',\n",
    "                          'car_rental_vehicle.year': 'year',\n",
    "                          'car_rental_rate.daily': 'daily_rate'}, inplace=True)\n",
    "print(vehicle_df)\n",
    "\n",
    "combined_vehicle_df = pd.concat([vehicle_id_df, vehicle_df], axis=1)\n",
    "print(combined_vehicle_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "2de4c82f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO vehicle (fuel_type, rating, make, model, type, year)\n",
    "    VALUES (%s, %s, %s, %s, %s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'payment_info' table\n",
    "for _, row in vehicle_df.iterrows():\n",
    "    cur.execute(insert_query, (\n",
    "        row['fuel_type'],\n",
    "        float(row['rating']),\n",
    "        row['make'],\n",
    "        row['model'],\n",
    "        row['type'],\n",
    "        int(row['year'])\n",
    "        ))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7e11232",
   "metadata": {},
   "source": [
    "### for `rental` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "7e42554e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      rental_id\n",
      "0          1365\n",
      "1          4951\n",
      "2          4765\n",
      "3          2556\n",
      "4           946\n",
      "...         ...\n",
      "5846       4590\n",
      "5847       4314\n",
      "5848       1944\n",
      "5849       5717\n",
      "5850        507\n",
      "\n",
      "[5851 rows x 1 columns]\n",
      "      rental_id  vehicle_id fuel_type  rating       make    model type   \n",
      "0          1365        2407  ELECTRIC    5.00      Tesla  Model X  suv  \\\n",
      "1          4951        5045  ELECTRIC    5.00      Tesla  Model X  suv   \n",
      "2          4765         493    HYBRID    4.92     Toyota    Prius  car   \n",
      "3          2556        3179  GASOLINE    5.00       Ford  Mustang  car   \n",
      "4           946        3307  GASOLINE    5.00   Chrysler  Sebring  car   \n",
      "...         ...         ...       ...     ...        ...      ...  ...   \n",
      "5846       4590        3581  GASOLINE    5.00  Chevrolet    Cruze  car   \n",
      "5847       4314        4773    HYBRID    5.00      Lexus  HS 250h  car   \n",
      "5848       1944        4490  GASOLINE    4.94      smart   fortwo  car   \n",
      "5849       5717        1610  GASOLINE     NaN        GMC   Savana  van   \n",
      "5850        507        4285  GASOLINE    5.00      smart   fortwo  car   \n",
      "\n",
      "        year  daily_rate  \n",
      "0     2019.0       135.0  \n",
      "1     2018.0       190.0  \n",
      "2     2012.0        35.0  \n",
      "3     2018.0        75.0  \n",
      "4     2010.0        47.0  \n",
      "...      ...         ...  \n",
      "5846  2017.0        33.0  \n",
      "5847  2010.0        49.0  \n",
      "5848  2013.0        35.0  \n",
      "5849  2015.0        77.0  \n",
      "5850  2013.0        35.0  \n",
      "\n",
      "[5851 rows x 9 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "\n",
    "# Number of rows in the DataFrame\n",
    "num_rows = 5851\n",
    "\n",
    "# Generate random unique integers\n",
    "random_rental_ids = random.sample(range(1, num_rows + 1), num_rows)\n",
    "\n",
    "# Create the DataFrame\n",
    "rental_id_df = pd.DataFrame({'rental_id': random_rental_ids})\n",
    "\n",
    "print(rental_id_df)\n",
    "\n",
    "combined_rental_df = pd.concat([rental_id_df, combined_vehicle_df], axis=1)\n",
    "print(combined_rental_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "52284fd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO rental (vehicle_id, daily_rate)\n",
    "    VALUES (%s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'payment_info' table\n",
    "for _, row in combined_rental_df.iterrows():\n",
    "    cur.execute(insert_query, (\n",
    "        int(row['vehicle_id']),\n",
    "        row['daily_rate'],\n",
    "        ))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ea63a3c",
   "metadata": {},
   "source": [
    "### for `car_rental_user` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "8f21cce2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      car_rental_user_id\n",
      "0                   3085\n",
      "1                   1181\n",
      "2                   5109\n",
      "3                   4422\n",
      "4                   1467\n",
      "...                  ...\n",
      "5846                5693\n",
      "5847                3379\n",
      "5848                3895\n",
      "5849                3693\n",
      "5850                4068\n",
      "\n",
      "[5851 rows x 1 columns]\n",
      "      car_rental_user_id first_name last_name                        email   \n",
      "0                   3085     Ernest    Barnes  Ernest.Barnes31@outlook.com  \\\n",
      "1                   1181     Andrea     Baker       Andrea_Baker94@aol.com   \n",
      "2                   5109    Rebecca    Parker   Rebecca_Parker@comcast.net   \n",
      "3                   4422      Laura    Murray            Laura_M@gmail.com   \n",
      "4                   1467      Linda     Hines           LHines@verizon.com   \n",
      "...                  ...        ...       ...                          ...   \n",
      "5846                5693       John     Baker     John_Baker88@hotmail.com   \n",
      "5847                3379    Jeffrey     Kelly    Jeffrey.Kelly@verizon.com   \n",
      "5848                3895    Crystal    Turner  Crystal.Turner76@yandex.com   \n",
      "5849                3693     Rodney    Morris           RMorris@yandex.com   \n",
      "5850                4068    Cynthia  Williams           Cynthia_W@zoho.com   \n",
      "\n",
      "      phone_number  \n",
      "0     669-792-1661  \n",
      "1     858-637-6955  \n",
      "2     652-885-2745  \n",
      "3     364-656-8427  \n",
      "4     713-226-5883  \n",
      "...            ...  \n",
      "5846  348-959-7741  \n",
      "5847  523-534-3341  \n",
      "5848  405-916-5125  \n",
      "5849  272-323-1646  \n",
      "5850  379-567-1171  \n",
      "\n",
      "[5851 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "\n",
    "# Number of rows in the DataFrame\n",
    "num_rows = 5851\n",
    "\n",
    "# Generate random unique integers\n",
    "random_car_rental_user_id = random.sample(range(1, num_rows + 1), num_rows)\n",
    "\n",
    "# Create the DataFrame\n",
    "car_rental_user_id_df = pd.DataFrame({'car_rental_user_id': random_car_rental_user_id})\n",
    "\n",
    "print(car_rental_user_id_df)\n",
    "combined_car_rental_user=pd.concat([car_rental_user_id_df, user_df], axis=1)\n",
    "print(combined_car_rental_user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "5551299c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO car_rental_user (first_name, last_name, email, phone_number)\n",
    "    VALUES (%s, %s, %s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'payment_info' table\n",
    "for _, row in combined_car_rental_user.iterrows():\n",
    "    cur.execute(insert_query, (\n",
    "        row['first_name'],\n",
    "        row['last_name'],\n",
    "        row['email'],\n",
    "        row['phone_number']\n",
    "        ))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15002463",
   "metadata": {},
   "source": [
    "### for `car_rental_reservation` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "625e6758",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      reservation_id\n",
      "0               1748\n",
      "1               2220\n",
      "2               5428\n",
      "3               4827\n",
      "4               1382\n",
      "...              ...\n",
      "5846            4693\n",
      "5847            2542\n",
      "5848            1061\n",
      "5849            2667\n",
      "5850            1890\n",
      "\n",
      "[5851 rows x 1 columns]\n",
      "      reservation_id first_name last_name                        email   \n",
      "0               1748     Ernest    Barnes  Ernest.Barnes31@outlook.com  \\\n",
      "1               2220     Andrea     Baker       Andrea_Baker94@aol.com   \n",
      "2               5428    Rebecca    Parker   Rebecca_Parker@comcast.net   \n",
      "3               4827      Laura    Murray            Laura_M@gmail.com   \n",
      "4               1382      Linda     Hines           LHines@verizon.com   \n",
      "...              ...        ...       ...                          ...   \n",
      "5846            4693       John     Baker     John_Baker88@hotmail.com   \n",
      "5847            2542    Jeffrey     Kelly    Jeffrey.Kelly@verizon.com   \n",
      "5848            1061    Crystal    Turner  Crystal.Turner76@yandex.com   \n",
      "5849            2667     Rodney    Morris           RMorris@yandex.com   \n",
      "5850            1890    Cynthia  Williams           Cynthia_W@zoho.com   \n",
      "\n",
      "      phone_number  vehicle_id fuel_type  rating       make    model type   \n",
      "0     669-792-1661        2407  ELECTRIC    5.00      Tesla  Model X  suv  \\\n",
      "1     858-637-6955        5045  ELECTRIC    5.00      Tesla  Model X  suv   \n",
      "2     652-885-2745         493    HYBRID    4.92     Toyota    Prius  car   \n",
      "3     364-656-8427        3179  GASOLINE    5.00       Ford  Mustang  car   \n",
      "4     713-226-5883        3307  GASOLINE    5.00   Chrysler  Sebring  car   \n",
      "...            ...         ...       ...     ...        ...      ...  ...   \n",
      "5846  348-959-7741        3581  GASOLINE    5.00  Chevrolet    Cruze  car   \n",
      "5847  523-534-3341        4773    HYBRID    5.00      Lexus  HS 250h  car   \n",
      "5848  405-916-5125        4490  GASOLINE    4.94      smart   fortwo  car   \n",
      "5849  272-323-1646        1610  GASOLINE     NaN        GMC   Savana  van   \n",
      "5850  379-567-1171        4285  GASOLINE    5.00      smart   fortwo  car   \n",
      "\n",
      "        year  daily_rate  car_rental_user_id  renter_id  user_id  trips_taken   \n",
      "0     2019.0       135.0                3085        722     2423         13.0  \\\n",
      "1     2018.0       190.0                1181       1653     2678          2.0   \n",
      "2     2012.0        35.0                5109       3432     5301         28.0   \n",
      "3     2018.0        75.0                4422       4779     5032         21.0   \n",
      "4     2010.0        47.0                1467       3708     2767          3.0   \n",
      "...      ...         ...                 ...        ...      ...          ...   \n",
      "5846  2017.0        33.0                5693        626     1798         32.0   \n",
      "5847  2010.0        49.0                3379       4874     1650         17.0   \n",
      "5848  2013.0        35.0                3895       2314     3083         18.0   \n",
      "5849  2015.0        77.0                3693       4935     5069          1.0   \n",
      "5850  2013.0        35.0                4068       4135     4305         16.0   \n",
      "\n",
      "      review_count  rental_id  \n",
      "0             12.0       1365  \n",
      "1              1.0       4951  \n",
      "2             24.0       4765  \n",
      "3             20.0       2556  \n",
      "4              1.0        946  \n",
      "...            ...        ...  \n",
      "5846          27.0       4590  \n",
      "5847          16.0       4314  \n",
      "5848          17.0       1944  \n",
      "5849           0.0       5717  \n",
      "5850          14.0        507  \n",
      "\n",
      "[5851 rows x 19 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import random\n",
    "\n",
    "# Number of rows in the DataFrame\n",
    "num_rows = 5851\n",
    "\n",
    "# Generate random unique integers\n",
    "reservation_id = random.sample(range(1, num_rows + 1), num_rows)\n",
    "\n",
    "# Create the DataFrame\n",
    "reservation_id_df = pd.DataFrame({'reservation_id': reservation_id})\n",
    "\n",
    "print(reservation_id_df)\n",
    "combined_reservation=pd.concat([reservation_id_df, user_df, combined_vehicle_df,\n",
    "                                    car_rental_user_id_df, combined_renter_df, rental_id_df], axis=1)\n",
    "print(combined_reservation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0ffb1e49",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the INSERT query\n",
    "insert_query = '''\n",
    "    INSERT INTO car_rental_reservation (user_id, rental_id, renter_id, vehicle_id)\n",
    "    VALUES (%s, %s, %s, %s);\n",
    "'''\n",
    "\n",
    "# Insert each row of the DataFrame into the 'payment_info' table\n",
    "for _, row in combined_reservation.iterrows():\n",
    "    cur.execute(insert_query, (\n",
    "        row['user_id'],\n",
    "        row['rental_id'],\n",
    "        row['renter_id'],\n",
    "        row['vehicle_id']\n",
    "        ))\n",
    "\n",
    "# Commit the changes\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "1984e370",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the cursor and connection\n",
    "cur.close()\n",
    "conn.close()"
   ]
  }
 ],
 "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}