01_T5_reviews_comments.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ace8d541",
   "metadata": {},
   "source": [
    "## Data preparation for user reviews + comments"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72d6dc53",
   "metadata": {},
   "source": [
    "We calculated the average of the final review scores from listings.csv and merged it with \"final_review_df\" dataset. \"final_review_df\" is created in \"detect_lang\" notebook. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6c42b065",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "850f097f",
   "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>id</th>\n",
       "      <th>listing_url</th>\n",
       "      <th>scrape_id</th>\n",
       "      <th>last_scraped</th>\n",
       "      <th>source</th>\n",
       "      <th>name</th>\n",
       "      <th>description</th>\n",
       "      <th>neighborhood_overview</th>\n",
       "      <th>picture_url</th>\n",
       "      <th>host_id</th>\n",
       "      <th>...</th>\n",
       "      <th>review_scores_communication</th>\n",
       "      <th>review_scores_location</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>license</th>\n",
       "      <th>instant_bookable</th>\n",
       "      <th>calculated_host_listings_count</th>\n",
       "      <th>calculated_host_listings_count_entire_homes</th>\n",
       "      <th>calculated_host_listings_count_private_rooms</th>\n",
       "      <th>calculated_host_listings_count_shared_rooms</th>\n",
       "      <th>reviews_per_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777451666060243581</td>\n",
       "      <td>https://www.airbnb.com/rooms/777451666060243581</td>\n",
       "      <td>20230307175359</td>\n",
       "      <td>2023-03-07</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Family oriented home</td>\n",
       "      <td>Relax with the whole family at this peaceful p...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/pictures/miso/Hosting-...</td>\n",
       "      <td>490709958</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>698097753730921190</td>\n",
       "      <td>https://www.airbnb.com/rooms/698097753730921190</td>\n",
       "      <td>20230307175359</td>\n",
       "      <td>2023-03-07</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>方便易居驿站</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/pictures/d5afff58-d576...</td>\n",
       "      <td>444692513</td>\n",
       "      <td>...</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063118</td>\n",
       "      <td>https://www.airbnb.com/rooms/13063118</td>\n",
       "      <td>20230307175359</td>\n",
       "      <td>2023-03-07</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>TERRANEA OCEANFRNT 1BR CASITA -203 RSRT AMENIT...</td>\n",
       "      <td>LOCATION! Luxury upstairs Oceanside Casita gue...</td>\n",
       "      <td>No need to fly away from Los Angeles for a get...</td>\n",
       "      <td>https://a0.muscache.com/pictures/6227287c-7073...</td>\n",
       "      <td>41736985</td>\n",
       "      <td>...</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.83</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39337076</td>\n",
       "      <td>https://www.airbnb.com/rooms/39337076</td>\n",
       "      <td>20230307175359</td>\n",
       "      <td>2023-03-07</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Brand New Duplex Near World Cruise Center</td>\n",
       "      <td>Welcome to our new cozy duplex!&lt;br /&gt;&lt;br /&gt;Loc...</td>\n",
       "      <td>Our property is close to downtown San Pedro, t...</td>\n",
       "      <td>https://a0.muscache.com/pictures/miso/Hosting-...</td>\n",
       "      <td>208375458</td>\n",
       "      <td>...</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.66</td>\n",
       "      <td>4.82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>t</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13077628</td>\n",
       "      <td>https://www.airbnb.com/rooms/13077628</td>\n",
       "      <td>20230307175359</td>\n",
       "      <td>2023-03-07</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Zuma Malibu Retreat</td>\n",
       "      <td>Our Private 16 Acre Malibu Retreat is located ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/pictures/miso/Hosting-...</td>\n",
       "      <td>4182067</td>\n",
       "      <td>...</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.64</td>\n",
       "      <td>4.36</td>\n",
       "      <td>STR21-0228</td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 75 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id                                      listing_url  \\\n",
       "0  777451666060243581  https://www.airbnb.com/rooms/777451666060243581   \n",
       "1  698097753730921190  https://www.airbnb.com/rooms/698097753730921190   \n",
       "2            13063118            https://www.airbnb.com/rooms/13063118   \n",
       "3            39337076            https://www.airbnb.com/rooms/39337076   \n",
       "4            13077628            https://www.airbnb.com/rooms/13077628   \n",
       "\n",
       "        scrape_id last_scraped       source  \\\n",
       "0  20230307175359   2023-03-07  city scrape   \n",
       "1  20230307175359   2023-03-07  city scrape   \n",
       "2  20230307175359   2023-03-07  city scrape   \n",
       "3  20230307175359   2023-03-07  city scrape   \n",
       "4  20230307175359   2023-03-07  city scrape   \n",
       "\n",
       "                                                name  \\\n",
       "0                               Family oriented home   \n",
       "1                                             方便易居驿站   \n",
       "2  TERRANEA OCEANFRNT 1BR CASITA -203 RSRT AMENIT...   \n",
       "3          Brand New Duplex Near World Cruise Center   \n",
       "4                                Zuma Malibu Retreat   \n",
       "\n",
       "                                         description  \\\n",
       "0  Relax with the whole family at this peaceful p...   \n",
       "1                                                NaN   \n",
       "2  LOCATION! Luxury upstairs Oceanside Casita gue...   \n",
       "3  Welcome to our new cozy duplex!<br /><br />Loc...   \n",
       "4  Our Private 16 Acre Malibu Retreat is located ...   \n",
       "\n",
       "                               neighborhood_overview  \\\n",
       "0                                                NaN   \n",
       "1                                                NaN   \n",
       "2  No need to fly away from Los Angeles for a get...   \n",
       "3  Our property is close to downtown San Pedro, t...   \n",
       "4                                                NaN   \n",
       "\n",
       "                                         picture_url    host_id  ...  \\\n",
       "0  https://a0.muscache.com/pictures/miso/Hosting-...  490709958  ...   \n",
       "1  https://a0.muscache.com/pictures/d5afff58-d576...  444692513  ...   \n",
       "2  https://a0.muscache.com/pictures/6227287c-7073...   41736985  ...   \n",
       "3  https://a0.muscache.com/pictures/miso/Hosting-...  208375458  ...   \n",
       "4  https://a0.muscache.com/pictures/miso/Hosting-...    4182067  ...   \n",
       "\n",
       "  review_scores_communication review_scores_location review_scores_value  \\\n",
       "0                         NaN                    NaN                 NaN   \n",
       "1                        5.00                   5.00                4.40   \n",
       "2                        4.96                   4.92                4.83   \n",
       "3                        4.86                   4.66                4.82   \n",
       "4                        4.79                   4.64                4.36   \n",
       "\n",
       "      license instant_bookable calculated_host_listings_count  \\\n",
       "0         NaN                f                              1   \n",
       "1         NaN                f                              4   \n",
       "2         NaN                f                             10   \n",
       "3         NaN                t                              4   \n",
       "4  STR21-0228                f                              1   \n",
       "\n",
       "  calculated_host_listings_count_entire_homes  \\\n",
       "0                                           0   \n",
       "1                                           0   \n",
       "2                                           5   \n",
       "3                                           0   \n",
       "4                                           1   \n",
       "\n",
       "  calculated_host_listings_count_private_rooms  \\\n",
       "0                                            1   \n",
       "1                                            4   \n",
       "2                                            5   \n",
       "3                                            4   \n",
       "4                                            0   \n",
       "\n",
       "  calculated_host_listings_count_shared_rooms reviews_per_month  \n",
       "0                                           0               NaN  \n",
       "1                                           0              0.86  \n",
       "2                                           0              0.64  \n",
       "3                                           0              1.09  \n",
       "4                                           0              0.18  \n",
       "\n",
       "[5 rows x 75 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "listings_df = pd.read_csv('listings.csv')\n",
    "listings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e10b6a24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42451, 75)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "listings_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3388a542",
   "metadata": {},
   "source": [
    "#### Remove the useless columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9301cff3",
   "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>id</th>\n",
       "      <th>review_scores_rating</th>\n",
       "      <th>review_scores_accuracy</th>\n",
       "      <th>review_scores_cleanliness</th>\n",
       "      <th>review_scores_checkin</th>\n",
       "      <th>review_scores_communication</th>\n",
       "      <th>review_scores_location</th>\n",
       "      <th>review_scores_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777451666060243581</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>698097753730921190</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063118</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.90</td>\n",
       "      <td>4.94</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39337076</td>\n",
       "      <td>4.80</td>\n",
       "      <td>4.75</td>\n",
       "      <td>4.93</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.66</td>\n",
       "      <td>4.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13077628</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.71</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.64</td>\n",
       "      <td>4.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  review_scores_rating  review_scores_accuracy   \n",
       "0  777451666060243581                   NaN                     NaN  \\\n",
       "1  698097753730921190                  5.00                    5.00   \n",
       "2            13063118                  4.92                    4.90   \n",
       "3            39337076                  4.80                    4.75   \n",
       "4            13077628                  4.79                    4.86   \n",
       "\n",
       "   review_scores_cleanliness  review_scores_checkin   \n",
       "0                        NaN                    NaN  \\\n",
       "1                       5.00                   5.00   \n",
       "2                       4.94                   4.96   \n",
       "3                       4.93                   4.86   \n",
       "4                       4.71                   5.00   \n",
       "\n",
       "   review_scores_communication  review_scores_location  review_scores_value  \n",
       "0                          NaN                     NaN                  NaN  \n",
       "1                         5.00                    5.00                 4.40  \n",
       "2                         4.96                    4.92                 4.83  \n",
       "3                         4.86                    4.66                 4.82  \n",
       "4                         4.79                    4.64                 4.36  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "key_features = ['id', 'review_scores_rating','review_scores_accuracy', 'review_scores_cleanliness',\n",
    "                'review_scores_checkin', 'review_scores_communication','review_scores_location',\n",
    "                'review_scores_value']\n",
    "\n",
    "filtered_listings_df = listings_df[key_features]\n",
    "filtered_listings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2d373ea6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42451, 8)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_listings_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "975d71e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                                 0\n",
       "review_scores_rating           11671\n",
       "review_scores_accuracy         11920\n",
       "review_scores_cleanliness      11918\n",
       "review_scores_checkin          11925\n",
       "review_scores_communication    11919\n",
       "review_scores_location         11927\n",
       "review_scores_value            11929\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check nan value\n",
    "filtered_listings_df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "591020fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace nan as 0\n",
    "filtered_listings_df = filtered_listings_df.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53e30aea",
   "metadata": {},
   "source": [
    "#### Now, we can calculate the score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "56eec412",
   "metadata": {},
   "outputs": [],
   "source": [
    "scores = ['review_scores_rating','review_scores_accuracy', 'review_scores_cleanliness',\n",
    "          'review_scores_checkin', 'review_scores_communication','review_scores_location',\n",
    "          'review_scores_value']\n",
    "\n",
    "filtered_listings_df['final_review_score'] = filtered_listings_df[scores].mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "4512ef2d",
   "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>id</th>\n",
       "      <th>review_scores_rating</th>\n",
       "      <th>review_scores_accuracy</th>\n",
       "      <th>review_scores_cleanliness</th>\n",
       "      <th>review_scores_checkin</th>\n",
       "      <th>review_scores_communication</th>\n",
       "      <th>review_scores_location</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>final_review_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>777451666060243581</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>698097753730921190</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.40</td>\n",
       "      <td>4.914286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063118</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.90</td>\n",
       "      <td>4.94</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.83</td>\n",
       "      <td>4.918571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39337076</td>\n",
       "      <td>4.80</td>\n",
       "      <td>4.75</td>\n",
       "      <td>4.93</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.66</td>\n",
       "      <td>4.82</td>\n",
       "      <td>4.811429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13077628</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.71</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.64</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4.735714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  review_scores_rating  review_scores_accuracy   \n",
       "0  777451666060243581                  0.00                    0.00  \\\n",
       "1  698097753730921190                  5.00                    5.00   \n",
       "2            13063118                  4.92                    4.90   \n",
       "3            39337076                  4.80                    4.75   \n",
       "4            13077628                  4.79                    4.86   \n",
       "\n",
       "   review_scores_cleanliness  review_scores_checkin   \n",
       "0                       0.00                   0.00  \\\n",
       "1                       5.00                   5.00   \n",
       "2                       4.94                   4.96   \n",
       "3                       4.93                   4.86   \n",
       "4                       4.71                   5.00   \n",
       "\n",
       "   review_scores_communication  review_scores_location  review_scores_value   \n",
       "0                         0.00                    0.00                 0.00  \\\n",
       "1                         5.00                    5.00                 4.40   \n",
       "2                         4.96                    4.92                 4.83   \n",
       "3                         4.86                    4.66                 4.82   \n",
       "4                         4.79                    4.64                 4.36   \n",
       "\n",
       "   final_review_score  \n",
       "0            0.000000  \n",
       "1            4.914286  \n",
       "2            4.918571  \n",
       "3            4.811429  \n",
       "4            4.735714  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_listings_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d066a5ab",
   "metadata": {},
   "source": [
    "#### Now, we will drop the 'final_review_score' = 0. Because this mean there are no data for any of the 7 scores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3c099c5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_listings_df = filtered_listings_df[filtered_listings_df['final_review_score'] != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "372a6ab0",
   "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>id</th>\n",
       "      <th>review_scores_rating</th>\n",
       "      <th>review_scores_accuracy</th>\n",
       "      <th>review_scores_cleanliness</th>\n",
       "      <th>review_scores_checkin</th>\n",
       "      <th>review_scores_communication</th>\n",
       "      <th>review_scores_location</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>final_review_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>698097753730921190</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.40</td>\n",
       "      <td>4.914286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063118</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.90</td>\n",
       "      <td>4.94</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.83</td>\n",
       "      <td>4.918571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39337076</td>\n",
       "      <td>4.80</td>\n",
       "      <td>4.75</td>\n",
       "      <td>4.93</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.66</td>\n",
       "      <td>4.82</td>\n",
       "      <td>4.811429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13077628</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.71</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.64</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4.735714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51859630</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  review_scores_rating  review_scores_accuracy   \n",
       "1  698097753730921190                  5.00                    5.00  \\\n",
       "2            13063118                  4.92                    4.90   \n",
       "3            39337076                  4.80                    4.75   \n",
       "4            13077628                  4.79                    4.86   \n",
       "5            51859630                  5.00                    5.00   \n",
       "\n",
       "   review_scores_cleanliness  review_scores_checkin   \n",
       "1                       5.00                   5.00  \\\n",
       "2                       4.94                   4.96   \n",
       "3                       4.93                   4.86   \n",
       "4                       4.71                   5.00   \n",
       "5                       5.00                   5.00   \n",
       "\n",
       "   review_scores_communication  review_scores_location  review_scores_value   \n",
       "1                         5.00                    5.00                 4.40  \\\n",
       "2                         4.96                    4.92                 4.83   \n",
       "3                         4.86                    4.66                 4.82   \n",
       "4                         4.79                    4.64                 4.36   \n",
       "5                         5.00                    5.00                 5.00   \n",
       "\n",
       "   final_review_score  \n",
       "1            4.914286  \n",
       "2            4.918571  \n",
       "3            4.811429  \n",
       "4            4.735714  \n",
       "5            5.000000  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_listings_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53d4f9d9",
   "metadata": {},
   "source": [
    "#### Create label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6f613fba",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/jh/nyglx1sn7pn4r0h2m50ghdbm0000gn/T/ipykernel_24295/1741078580.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  cleaned_listings_df.loc[low, 'score_label'] = 'low'\n"
     ]
    }
   ],
   "source": [
    "# Create label by score\n",
    "low = cleaned_listings_df['final_review_score'] < 1.667\n",
    "high = cleaned_listings_df['final_review_score'] > 3.333\n",
    "\n",
    "# Set the 'label' column based on the conditions using .loc\n",
    "cleaned_listings_df.loc[low, 'score_label'] = 'low'\n",
    "cleaned_listings_df.loc[high, 'score_label'] = 'high'\n",
    "cleaned_listings_df.loc[~(low | high), 'score_label'] = 'middle'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6a916702",
   "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>id</th>\n",
       "      <th>review_scores_rating</th>\n",
       "      <th>review_scores_accuracy</th>\n",
       "      <th>review_scores_cleanliness</th>\n",
       "      <th>review_scores_checkin</th>\n",
       "      <th>review_scores_communication</th>\n",
       "      <th>review_scores_location</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>final_review_score</th>\n",
       "      <th>score_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>698097753730921190</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.40</td>\n",
       "      <td>4.914286</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063118</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.90</td>\n",
       "      <td>4.94</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.96</td>\n",
       "      <td>4.92</td>\n",
       "      <td>4.83</td>\n",
       "      <td>4.918571</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39337076</td>\n",
       "      <td>4.80</td>\n",
       "      <td>4.75</td>\n",
       "      <td>4.93</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.66</td>\n",
       "      <td>4.82</td>\n",
       "      <td>4.811429</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13077628</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.86</td>\n",
       "      <td>4.71</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.79</td>\n",
       "      <td>4.64</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4.735714</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51859630</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  review_scores_rating  review_scores_accuracy   \n",
       "1  698097753730921190                  5.00                    5.00  \\\n",
       "2            13063118                  4.92                    4.90   \n",
       "3            39337076                  4.80                    4.75   \n",
       "4            13077628                  4.79                    4.86   \n",
       "5            51859630                  5.00                    5.00   \n",
       "\n",
       "   review_scores_cleanliness  review_scores_checkin   \n",
       "1                       5.00                   5.00  \\\n",
       "2                       4.94                   4.96   \n",
       "3                       4.93                   4.86   \n",
       "4                       4.71                   5.00   \n",
       "5                       5.00                   5.00   \n",
       "\n",
       "   review_scores_communication  review_scores_location  review_scores_value   \n",
       "1                         5.00                    5.00                 4.40  \\\n",
       "2                         4.96                    4.92                 4.83   \n",
       "3                         4.86                    4.66                 4.82   \n",
       "4                         4.79                    4.64                 4.36   \n",
       "5                         5.00                    5.00                 5.00   \n",
       "\n",
       "   final_review_score score_label  \n",
       "1            4.914286        high  \n",
       "2            4.918571        high  \n",
       "3            4.811429        high  \n",
       "4            4.735714        high  \n",
       "5            5.000000        high  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_listings_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8aec94a",
   "metadata": {},
   "source": [
    "#### Remove the useless columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "43e8ff76",
   "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>id</th>\n",
       "      <th>final_review_score</th>\n",
       "      <th>score_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>698097753730921190</td>\n",
       "      <td>4.914286</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063118</td>\n",
       "      <td>4.918571</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39337076</td>\n",
       "      <td>4.811429</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13077628</td>\n",
       "      <td>4.735714</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51859630</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  final_review_score score_label\n",
       "1  698097753730921190            4.914286        high\n",
       "2            13063118            4.918571        high\n",
       "3            39337076            4.811429        high\n",
       "4            13077628            4.735714        high\n",
       "5            51859630            5.000000        high"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_listings_df = cleaned_listings_df[['id','final_review_score','score_label']]\n",
    "final_listings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "e2b856c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30557, 3)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_listings_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7e3f5256",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    30557.0000\n",
       "mean         4.7605\n",
       "std          0.4198\n",
       "min          0.2857\n",
       "25%          4.7243\n",
       "50%          4.8700\n",
       "75%          4.9529\n",
       "max          5.0000\n",
       "Name: final_review_score, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_listings_df['final_review_score'].describe().apply(lambda x: format(x, '.4f'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7780d00f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create label by score\n",
    "low = cleaned_listings_df['final_review_score'] < 1.667\n",
    "high = cleaned_listings_df['final_review_score'] > 3.333\n",
    "\n",
    "# Set the 'label' column based on the conditions using .loc\n",
    "cleaned_listings_df.loc[low, 'score_label'] = 'low'\n",
    "cleaned_listings_df.loc[high, 'score_label'] = 'high'\n",
    "cleaned_listings_df.loc[~(low | high), 'score_label'] = 'middle'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0275a7a8",
   "metadata": {},
   "source": [
    "____"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd626054",
   "metadata": {},
   "source": [
    "#### Load cleaned review csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c214d8bd",
   "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>listing_id</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>reviewer_id</th>\n",
       "      <th>reviewer_name</th>\n",
       "      <th>comments</th>\n",
       "      <th>language</th>\n",
       "      <th>comments2</th>\n",
       "      <th>text_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>449036</td>\n",
       "      <td>2011-08-15</td>\n",
       "      <td>927861</td>\n",
       "      <td>Edwin</td>\n",
       "      <td>The host canceled my reservation the day befor...</td>\n",
       "      <td>en</td>\n",
       "      <td>The host canceled my reservation the day befor...</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>109</td>\n",
       "      <td>74506539</td>\n",
       "      <td>2016-05-15</td>\n",
       "      <td>22509885</td>\n",
       "      <td>Jenn</td>\n",
       "      <td>Me and two friends stayed for four and a half ...</td>\n",
       "      <td>en</td>\n",
       "      <td>Me and two friends stayed for four and a half ...</td>\n",
       "      <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2708</td>\n",
       "      <td>13994902</td>\n",
       "      <td>2014-06-09</td>\n",
       "      <td>10905424</td>\n",
       "      <td>Kuberan</td>\n",
       "      <td>i had a wonderful stay. Everything from start ...</td>\n",
       "      <td>en</td>\n",
       "      <td>i had a wonderful stay. Everything from start ...</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180926</td>\n",
       "      <td>424635</td>\n",
       "      <td>2011-08-06</td>\n",
       "      <td>892106</td>\n",
       "      <td>Brian</td>\n",
       "      <td>Rebecca was a great host!  She was very friend...</td>\n",
       "      <td>en</td>\n",
       "      <td>Rebecca was a great host!  She was very friend...</td>\n",
       "      <td>671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>180926</td>\n",
       "      <td>13396837</td>\n",
       "      <td>2014-05-26</td>\n",
       "      <td>3220350</td>\n",
       "      <td>Julia</td>\n",
       "      <td>We had a wonderful time in this cabin with our...</td>\n",
       "      <td>en</td>\n",
       "      <td>We had a wonderful time in this cabin with our...</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   listing_id        id        date  reviewer_id reviewer_name   \n",
       "0         109    449036  2011-08-15       927861         Edwin  \\\n",
       "1         109  74506539  2016-05-15     22509885          Jenn   \n",
       "2        2708  13994902  2014-06-09     10905424       Kuberan   \n",
       "3      180926    424635  2011-08-06       892106         Brian   \n",
       "4      180926  13396837  2014-05-26      3220350         Julia   \n",
       "\n",
       "                                            comments language   \n",
       "0  The host canceled my reservation the day befor...       en  \\\n",
       "1  Me and two friends stayed for four and a half ...       en   \n",
       "2  i had a wonderful stay. Everything from start ...       en   \n",
       "3  Rebecca was a great host!  She was very friend...       en   \n",
       "4  We had a wonderful time in this cabin with our...       en   \n",
       "\n",
       "                                           comments2  text_length  \n",
       "0  The host canceled my reservation the day befor...           56  \n",
       "1  Me and two friends stayed for four and a half ...          340  \n",
       "2  i had a wonderful stay. Everything from start ...           92  \n",
       "3  Rebecca was a great host!  She was very friend...          671  \n",
       "4  We had a wonderful time in this cabin with our...          456  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_review_df = pd.read_csv('final_review_df.csv')\n",
    "cleaned_review_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a9e5dad8",
   "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>listing_id</th>\n",
       "      <th>reviewer_name</th>\n",
       "      <th>comments2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>Edwin</td>\n",
       "      <td>The host canceled my reservation the day befor...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>109</td>\n",
       "      <td>Jenn</td>\n",
       "      <td>Me and two friends stayed for four and a half ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2708</td>\n",
       "      <td>Kuberan</td>\n",
       "      <td>i had a wonderful stay. Everything from start ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>180926</td>\n",
       "      <td>Brian</td>\n",
       "      <td>Rebecca was a great host!  She was very friend...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>180926</td>\n",
       "      <td>Julia</td>\n",
       "      <td>We had a wonderful time in this cabin with our...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   listing_id reviewer_name                                          comments2\n",
       "0         109         Edwin  The host canceled my reservation the day befor...\n",
       "1         109          Jenn  Me and two friends stayed for four and a half ...\n",
       "2        2708       Kuberan  i had a wonderful stay. Everything from start ...\n",
       "3      180926         Brian  Rebecca was a great host!  She was very friend...\n",
       "4      180926         Julia  We had a wonderful time in this cabin with our..."
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores2 = ['listing_id', 'reviewer_name','comments2']\n",
    "\n",
    "final_review_df = cleaned_review_df[scores2]\n",
    "final_review_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef1f62eb",
   "metadata": {},
   "source": [
    "#### Merge the cleaned csv files together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "2b4c9369",
   "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>id</th>\n",
       "      <th>final_review_score</th>\n",
       "      <th>score_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>698097753730921190</td>\n",
       "      <td>4.914286</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13063118</td>\n",
       "      <td>4.918571</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39337076</td>\n",
       "      <td>4.811429</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13077628</td>\n",
       "      <td>4.735714</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51859630</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id  final_review_score score_label\n",
       "1  698097753730921190            4.914286        high\n",
       "2            13063118            4.918571        high\n",
       "3            39337076            4.811429        high\n",
       "4            13077628            4.735714        high\n",
       "5            51859630            5.000000        high"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_listings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c466eda6",
   "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>listing_id</th>\n",
       "      <th>reviewer_name</th>\n",
       "      <th>comments2</th>\n",
       "      <th>id</th>\n",
       "      <th>final_review_score</th>\n",
       "      <th>score_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>109</td>\n",
       "      <td>Edwin</td>\n",
       "      <td>The host canceled my reservation the day befor...</td>\n",
       "      <td>109</td>\n",
       "      <td>4.285714</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>109</td>\n",
       "      <td>Jenn</td>\n",
       "      <td>Me and two friends stayed for four and a half ...</td>\n",
       "      <td>109</td>\n",
       "      <td>4.285714</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2708</td>\n",
       "      <td>Kuberan</td>\n",
       "      <td>i had a wonderful stay. Everything from start ...</td>\n",
       "      <td>2708</td>\n",
       "      <td>4.912857</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2708</td>\n",
       "      <td>Camilla</td>\n",
       "      <td>Charles is just amazing and he made my stay sp...</td>\n",
       "      <td>2708</td>\n",
       "      <td>4.912857</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2708</td>\n",
       "      <td>Fallon</td>\n",
       "      <td>Staying with Chas was an absolute pleasure. He...</td>\n",
       "      <td>2708</td>\n",
       "      <td>4.912857</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   listing_id reviewer_name   \n",
       "0         109         Edwin  \\\n",
       "1         109          Jenn   \n",
       "2        2708       Kuberan   \n",
       "3        2708       Camilla   \n",
       "4        2708        Fallon   \n",
       "\n",
       "                                           comments2    id   \n",
       "0  The host canceled my reservation the day befor...   109  \\\n",
       "1  Me and two friends stayed for four and a half ...   109   \n",
       "2  i had a wonderful stay. Everything from start ...  2708   \n",
       "3  Charles is just amazing and he made my stay sp...  2708   \n",
       "4  Staying with Chas was an absolute pleasure. He...  2708   \n",
       "\n",
       "   final_review_score score_label  \n",
       "0            4.285714        high  \n",
       "1            4.285714        high  \n",
       "2            4.912857        high  \n",
       "3            4.912857        high  \n",
       "4            4.912857        high  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = pd.merge(final_review_df, final_listings_df, left_on='listing_id', right_on='id')\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "b8a0d5ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1311631, 6)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "781dd4bb",
   "metadata": {},
   "source": [
    "#### Find duplicated data\n",
    "There are 479 rows that have exactly the same data, so we will drop these data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f64bd734",
   "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>listing_id</th>\n",
       "      <th>reviewer_name</th>\n",
       "      <th>comments2</th>\n",
       "      <th>id</th>\n",
       "      <th>final_review_score</th>\n",
       "      <th>score_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>5728</td>\n",
       "      <td>Amos</td>\n",
       "      <td>Great experience in this crazy house. Small bu...</td>\n",
       "      <td>5728</td>\n",
       "      <td>4.828571</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3368</th>\n",
       "      <td>210855</td>\n",
       "      <td>Jocelyn</td>\n",
       "      <td>Calvin is really kind, his room is clean and c...</td>\n",
       "      <td>210855</td>\n",
       "      <td>4.628571</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3373</th>\n",
       "      <td>210855</td>\n",
       "      <td>Matt</td>\n",
       "      <td>I couldnt have asked for a better place to sta...</td>\n",
       "      <td>210855</td>\n",
       "      <td>4.628571</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10376</th>\n",
       "      <td>84636</td>\n",
       "      <td>Sebastian</td>\n",
       "      <td>Good spot for a quick stop heading to differen...</td>\n",
       "      <td>84636</td>\n",
       "      <td>4.514286</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16293</th>\n",
       "      <td>120005</td>\n",
       "      <td>Taya</td>\n",
       "      <td>Great place to stay</td>\n",
       "      <td>120005</td>\n",
       "      <td>4.385714</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1298388</th>\n",
       "      <td>733163802225696617</td>\n",
       "      <td>James</td>\n",
       "      <td>Great place</td>\n",
       "      <td>733163802225696617</td>\n",
       "      <td>4.871429</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1298389</th>\n",
       "      <td>733163802225696617</td>\n",
       "      <td>James</td>\n",
       "      <td>Love this place</td>\n",
       "      <td>733163802225696617</td>\n",
       "      <td>4.871429</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1298394</th>\n",
       "      <td>733163802225696617</td>\n",
       "      <td>James</td>\n",
       "      <td>Love this place</td>\n",
       "      <td>733163802225696617</td>\n",
       "      <td>4.871429</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1304429</th>\n",
       "      <td>759131810053436000</td>\n",
       "      <td>Melissa</td>\n",
       "      <td>Great place</td>\n",
       "      <td>759131810053436000</td>\n",
       "      <td>4.568571</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1307249</th>\n",
       "      <td>776900976145428256</td>\n",
       "      <td>Ashley</td>\n",
       "      <td>Great overnight stay!</td>\n",
       "      <td>776900976145428256</td>\n",
       "      <td>4.970000</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>479 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 listing_id reviewer_name   \n",
       "431                    5728          Amos  \\\n",
       "3368                 210855       Jocelyn   \n",
       "3373                 210855          Matt   \n",
       "10376                 84636     Sebastian   \n",
       "16293                120005          Taya   \n",
       "...                     ...           ...   \n",
       "1298388  733163802225696617         James   \n",
       "1298389  733163802225696617         James   \n",
       "1298394  733163802225696617         James   \n",
       "1304429  759131810053436000       Melissa   \n",
       "1307249  776900976145428256        Ashley   \n",
       "\n",
       "                                                 comments2   \n",
       "431      Great experience in this crazy house. Small bu...  \\\n",
       "3368     Calvin is really kind, his room is clean and c...   \n",
       "3373     I couldnt have asked for a better place to sta...   \n",
       "10376    Good spot for a quick stop heading to differen...   \n",
       "16293                                  Great place to stay   \n",
       "...                                                    ...   \n",
       "1298388                                        Great place   \n",
       "1298389                                    Love this place   \n",
       "1298394                                    Love this place   \n",
       "1304429                                        Great place   \n",
       "1307249                              Great overnight stay!   \n",
       "\n",
       "                         id  final_review_score score_label  \n",
       "431                    5728            4.828571        high  \n",
       "3368                 210855            4.628571        high  \n",
       "3373                 210855            4.628571        high  \n",
       "10376                 84636            4.514286        high  \n",
       "16293                120005            4.385714        high  \n",
       "...                     ...                 ...         ...  \n",
       "1298388  733163802225696617            4.871429        high  \n",
       "1298389  733163802225696617            4.871429        high  \n",
       "1298394  733163802225696617            4.871429        high  \n",
       "1304429  759131810053436000            4.568571        high  \n",
       "1307249  776900976145428256            4.970000        high  \n",
       "\n",
       "[479 rows x 6 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicate_rows = merged_df[merged_df.duplicated()]\n",
    "duplicate_rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "9e408a7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "dropped_df = merged_df.copy()\n",
    "\n",
    "dropped_df = dropped_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "fa57b070",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1311152, 6)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dropped_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "f47f95fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save file\n",
    "dropped_df.to_csv('supervised_learning_df.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed1ca6c1",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}