Analysis of AirBnB Data in Milan city using SQL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import os\n",
    "from sqlalchemy import create_engine , text\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Configure the connection to your MySQL database\n",
    "engine = create_engine('mysql+mysqlconnector://root:@localhost/airbnb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import csv data from Inside Airbnb \" http://insideairbnb.com/get-the-data/ \"\n",
    "\n",
    "df = pd.read_csv(\"listings.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 23142 entries, 0 to 23141\n",
      "Data columns (total 75 columns):\n",
      " #   Column                                        Non-Null Count  Dtype  \n",
      "---  ------                                        --------------  -----  \n",
      " 0   id                                            23142 non-null  int64  \n",
      " 1   listing_url                                   23142 non-null  object \n",
      " 2   scrape_id                                     23142 non-null  int64  \n",
      " 3   last_scraped                                  23142 non-null  object \n",
      " 4   source                                        23142 non-null  object \n",
      " 5   name                                          23142 non-null  object \n",
      " 6   description                                   22744 non-null  object \n",
      " 7   neighborhood_overview                         12481 non-null  object \n",
      " 8   picture_url                                   23142 non-null  object \n",
      " 9   host_id                                       23142 non-null  int64  \n",
      " 10  host_url                                      23142 non-null  object \n",
      " 11  host_name                                     23142 non-null  object \n",
      " 12  host_since                                    23142 non-null  object \n",
      " 13  host_location                                 19235 non-null  object \n",
      " 14  host_about                                    12246 non-null  object \n",
      " 15  host_response_time                            17457 non-null  object \n",
      " 16  host_response_rate                            17457 non-null  object \n",
      " 17  host_acceptance_rate                          18683 non-null  object \n",
      " 18  host_is_superhost                             19220 non-null  object \n",
      " 19  host_thumbnail_url                            23142 non-null  object \n",
      " 20  host_picture_url                              23142 non-null  object \n",
      " 21  host_neighbourhood                            11353 non-null  object \n",
      " 22  host_listings_count                           23142 non-null  int64  \n",
      " 23  host_total_listings_count                     23142 non-null  int64  \n",
      " 24  host_verifications                            23142 non-null  object \n",
      " 25  host_has_profile_pic                          23142 non-null  object \n",
      " 26  host_identity_verified                        23142 non-null  object \n",
      " 27  neighbourhood                                 12481 non-null  object \n",
      " 28  neighbourhood_cleansed                        23142 non-null  object \n",
      " 29  neighbourhood_group_cleansed                  0 non-null      float64\n",
      " 30  latitude                                      23142 non-null  float64\n",
      " 31  longitude                                     23142 non-null  float64\n",
      " 32  property_type                                 23142 non-null  object \n",
      " 33  room_type                                     23142 non-null  object \n",
      " 34  accommodates                                  23142 non-null  int64  \n",
      " 35  bathrooms                                     0 non-null      float64\n",
      " 36  bathrooms_text                                23137 non-null  object \n",
      " 37  bedrooms                                      17825 non-null  float64\n",
      " 38  beds                                          22849 non-null  float64\n",
      " 39  amenities                                     23142 non-null  object \n",
      " 40  price                                         23142 non-null  object \n",
      " 41  minimum_nights                                23142 non-null  int64  \n",
      " 42  maximum_nights                                23142 non-null  int64  \n",
      " 43  minimum_minimum_nights                        23142 non-null  int64  \n",
      " 44  maximum_minimum_nights                        23142 non-null  int64  \n",
      " 45  minimum_maximum_nights                        23142 non-null  int64  \n",
      " 46  maximum_maximum_nights                        23142 non-null  int64  \n",
      " 47  minimum_nights_avg_ntm                        23142 non-null  float64\n",
      " 48  maximum_nights_avg_ntm                        23142 non-null  float64\n",
      " 49  calendar_updated                              0 non-null      float64\n",
      " 50  has_availability                              23142 non-null  object \n",
      " 51  availability_30                               23142 non-null  int64  \n",
      " 52  availability_60                               23142 non-null  int64  \n",
      " 53  availability_90                               23142 non-null  int64  \n",
      " 54  availability_365                              23142 non-null  int64  \n",
      " 55  calendar_last_scraped                         23142 non-null  object \n",
      " 56  number_of_reviews                             23142 non-null  int64  \n",
      " 57  number_of_reviews_ltm                         23142 non-null  int64  \n",
      " 58  number_of_reviews_l30d                        23142 non-null  int64  \n",
      " 59  first_review                                  18600 non-null  object \n",
      " 60  last_review                                   18600 non-null  object \n",
      " 61  review_scores_rating                          18600 non-null  float64\n",
      " 62  review_scores_accuracy                        18441 non-null  float64\n",
      " 63  review_scores_cleanliness                     18440 non-null  float64\n",
      " 64  review_scores_checkin                         18440 non-null  float64\n",
      " 65  review_scores_communication                   18441 non-null  float64\n",
      " 66  review_scores_location                        18439 non-null  float64\n",
      " 67  review_scores_value                           18438 non-null  float64\n",
      " 68  license                                       4784 non-null   object \n",
      " 69  instant_bookable                              23142 non-null  object \n",
      " 70  calculated_host_listings_count                23142 non-null  int64  \n",
      " 71  calculated_host_listings_count_entire_homes   23142 non-null  int64  \n",
      " 72  calculated_host_listings_count_private_rooms  23142 non-null  int64  \n",
      " 73  calculated_host_listings_count_shared_rooms   23142 non-null  int64  \n",
      " 74  reviews_per_month                             18600 non-null  float64\n",
      "dtypes: float64(17), int64(23), object(35)\n",
      "memory usage: 13.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": false
   },
   "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>6400</td>\n",
       "      <td>https://www.airbnb.com/rooms/6400</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...</td>\n",
       "      <td>Enjoy your stay at The Studio, a light-filled ...</td>\n",
       "      <td>The neighborhood is quiet and very well connec...</td>\n",
       "      <td>https://a0.muscache.com/pictures/474737/5955ba...</td>\n",
       "      <td>13822</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.56</td>\n",
       "      <td>4.67</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23986</td>\n",
       "      <td>https://www.airbnb.com/rooms/23986</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...</td>\n",
       "      <td>I look forward to welcoming you in my flat; it...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/623d63f8-56cf...</td>\n",
       "      <td>95941</td>\n",
       "      <td>...</td>\n",
       "      <td>4.72</td>\n",
       "      <td>4.68</td>\n",
       "      <td>4.44</td>\n",
       "      <td></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",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24107</td>\n",
       "      <td>https://www.airbnb.com/rooms/24107</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...</td>\n",
       "      <td>Un grazioso bilocale completamente rinnovato i...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/cd4112f3-37c2...</td>\n",
       "      <td>46951</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.67</td>\n",
       "      <td>4.67</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40470</td>\n",
       "      <td>https://www.airbnb.com/rooms/40470</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...</td>\n",
       "      <td>&lt;b&gt;The space&lt;/b&gt;&lt;br /&gt;If you want to visit Mil...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/891684/01c17b...</td>\n",
       "      <td>174203</td>\n",
       "      <td>...</td>\n",
       "      <td>4.87</td>\n",
       "      <td>4.39</td>\n",
       "      <td>4.5</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>46536</td>\n",
       "      <td>https://www.airbnb.com/rooms/46536</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...</td>\n",
       "      <td>Over the international fair in April we rent f...</td>\n",
       "      <td>Flat  It's located in north side of milan, jus...</td>\n",
       "      <td>https://a0.muscache.com/pictures/4eb8e0f5-e17b...</td>\n",
       "      <td>138683</td>\n",
       "      <td>...</td>\n",
       "      <td>4.93</td>\n",
       "      <td>4.38</td>\n",
       "      <td>4.66</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 75 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                         listing_url       scrape_id last_scraped  \\\n",
       "0   6400   https://www.airbnb.com/rooms/6400  20230621232643   2023-06-22   \n",
       "1  23986  https://www.airbnb.com/rooms/23986  20230621232643   2023-06-22   \n",
       "2  24107  https://www.airbnb.com/rooms/24107  20230621232643   2023-06-22   \n",
       "3  40470  https://www.airbnb.com/rooms/40470  20230621232643   2023-06-22   \n",
       "4  46536  https://www.airbnb.com/rooms/46536  20230621232643   2023-06-22   \n",
       "\n",
       "        source                                               name  \\\n",
       "0  city scrape  Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...   \n",
       "1  city scrape  Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...   \n",
       "2  city scrape  Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...   \n",
       "3  city scrape  Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...   \n",
       "4  city scrape  Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...   \n",
       "\n",
       "                                         description  \\\n",
       "0  Enjoy your stay at The Studio, a light-filled ...   \n",
       "1  I look forward to welcoming you in my flat; it...   \n",
       "2  Un grazioso bilocale completamente rinnovato i...   \n",
       "3  <b>The space</b><br />If you want to visit Mil...   \n",
       "4  Over the international fair in April we rent f...   \n",
       "\n",
       "                               neighborhood_overview  \\\n",
       "0  The neighborhood is quiet and very well connec...   \n",
       "1                                                      \n",
       "2                                                      \n",
       "3                                                      \n",
       "4  Flat  It's located in north side of milan, jus...   \n",
       "\n",
       "                                         picture_url  host_id  ...  \\\n",
       "0  https://a0.muscache.com/pictures/474737/5955ba...    13822  ...   \n",
       "1  https://a0.muscache.com/pictures/623d63f8-56cf...    95941  ...   \n",
       "2  https://a0.muscache.com/pictures/cd4112f3-37c2...    46951  ...   \n",
       "3  https://a0.muscache.com/pictures/891684/01c17b...   174203  ...   \n",
       "4  https://a0.muscache.com/pictures/4eb8e0f5-e17b...   138683  ...   \n",
       "\n",
       "  review_scores_communication review_scores_location review_scores_value  \\\n",
       "0                         5.0                   4.56                4.67   \n",
       "1                        4.72                   4.68                4.44   \n",
       "2                         5.0                   3.67                4.67   \n",
       "3                        4.87                   4.39                 4.5   \n",
       "4                        4.93                   4.38                4.66   \n",
       "\n",
       "  license instant_bookable calculated_host_listings_count  \\\n",
       "0                        f                              1   \n",
       "1                        f                              1   \n",
       "2                        f                              1   \n",
       "3                        f                              2   \n",
       "4                        f                              1   \n",
       "\n",
       "  calculated_host_listings_count_entire_homes  \\\n",
       "0                                           0   \n",
       "1                                           1   \n",
       "2                                           1   \n",
       "3                                           2   \n",
       "4                                           1   \n",
       "\n",
       "  calculated_host_listings_count_private_rooms  \\\n",
       "0                                            1   \n",
       "1                                            0   \n",
       "2                                            0   \n",
       "3                                            0   \n",
       "4                                            0   \n",
       "\n",
       "  calculated_host_listings_count_shared_rooms reviews_per_month  \n",
       "0                                           0              0.07  \n",
       "1                                           0              0.18  \n",
       "2                                           0              0.02  \n",
       "3                                           0              0.27  \n",
       "4                                           0              0.21  \n",
       "\n",
       "[5 rows x 75 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connection to MySQL database\n",
    "conn = mysql.connector.connect(\n",
    "    host='localhost',\n",
    "    user='root',\n",
    "    password='',\n",
    "    database='airbnb'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    # Creating the table in the database based on the columns in the DataFrame\n",
    "    create_table_query = f\"CREATE TABLE IF NOT EXISTS milan ({', '.join([f'{col} TEXT' for col in df.columns])})\"\n",
    "    cursor.execute(create_table_query)\n",
    "\n",
    "    # Insert data into the database\n",
    "    for _, row in df.iterrows():\n",
    "        insert_query = f\"INSERT INTO milan ({', '.join(df.columns)}) VALUES ({', '.join(['%s'] * len(df.columns))})\"\n",
    "        values = tuple(row)\n",
    "        cursor.execute(insert_query, values)\n",
    "\n",
    "    # Conferm\n",
    "    conn.commit()\n",
    "\n",
    "except Exception as e:\n",
    "    # Undo changes if you make an error\n",
    "    conn.rollback()\n",
    "    print(\"Error occured:\", str(e))\n",
    "\n",
    "finally:\n",
    "    # Closing the cursor and the connection\n",
    "    cursor.close()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>6400</td>\n",
       "      <td>https://www.airbnb.com/rooms/6400</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...</td>\n",
       "      <td>Enjoy your stay at The Studio, a light-filled ...</td>\n",
       "      <td>The neighborhood is quiet and very well connec...</td>\n",
       "      <td>https://a0.muscache.com/pictures/474737/5955ba...</td>\n",
       "      <td>13822</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.56</td>\n",
       "      <td>4.67</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23986</td>\n",
       "      <td>https://www.airbnb.com/rooms/23986</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...</td>\n",
       "      <td>I look forward to welcoming you in my flat; it...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/623d63f8-56cf...</td>\n",
       "      <td>95941</td>\n",
       "      <td>...</td>\n",
       "      <td>4.72</td>\n",
       "      <td>4.68</td>\n",
       "      <td>4.44</td>\n",
       "      <td></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",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24107</td>\n",
       "      <td>https://www.airbnb.com/rooms/24107</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...</td>\n",
       "      <td>Un grazioso bilocale completamente rinnovato i...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/cd4112f3-37c2...</td>\n",
       "      <td>46951</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.67</td>\n",
       "      <td>4.67</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40470</td>\n",
       "      <td>https://www.airbnb.com/rooms/40470</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...</td>\n",
       "      <td>&lt;b&gt;The space&lt;/b&gt;&lt;br /&gt;If you want to visit Mil...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/891684/01c17b...</td>\n",
       "      <td>174203</td>\n",
       "      <td>...</td>\n",
       "      <td>4.87</td>\n",
       "      <td>4.39</td>\n",
       "      <td>4.5</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>46536</td>\n",
       "      <td>https://www.airbnb.com/rooms/46536</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...</td>\n",
       "      <td>Over the international fair in April we rent f...</td>\n",
       "      <td>Flat  It's located in north side of milan, jus...</td>\n",
       "      <td>https://a0.muscache.com/pictures/4eb8e0f5-e17b...</td>\n",
       "      <td>138683</td>\n",
       "      <td>...</td>\n",
       "      <td>4.93</td>\n",
       "      <td>4.38</td>\n",
       "      <td>4.66</td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23137</th>\n",
       "      <td>917286381468465380</td>\n",
       "      <td>https://www.airbnb.com/rooms/917286381468465380</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>L'appartamento è un bellissimo bilocale di rin...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/miso/Hosting-...</td>\n",
       "      <td>497775861</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>t</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23138</th>\n",
       "      <td>915023142785359078</td>\n",
       "      <td>https://www.airbnb.com/rooms/915023142785359078</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Appartamento di recente costruzione a 3 min ca...</td>\n",
       "      <td></td>\n",
       "      <td>https://a0.muscache.com/pictures/miso/Hosting-...</td>\n",
       "      <td>405312215</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>t</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23139</th>\n",
       "      <td>917286635554478737</td>\n",
       "      <td>https://www.airbnb.com/rooms/917286635554478737</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milano · ★New · 3 bedrooms · 5 ...</td>\n",
       "      <td>A disposizione un'appartamento enorme, luminos...</td>\n",
       "      <td>Il quartiere gode di un'atmosfera tranquilla e...</td>\n",
       "      <td>https://a0.muscache.com/pictures/707cbcf7-5c2d...</td>\n",
       "      <td>420375498</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>t</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23140</th>\n",
       "      <td>915025934511019523</td>\n",
       "      <td>https://www.airbnb.com/rooms/915025934511019523</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milano · ★New · 2 bedrooms · 6 ...</td>\n",
       "      <td>Relax in this centrally located quiet space.&lt;b...</td>\n",
       "      <td>From an architectural point of view, the area ...</td>\n",
       "      <td>https://a0.muscache.com/pictures/miso/Hosting-...</td>\n",
       "      <td>4015119</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>f</td>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23141</th>\n",
       "      <td>915037822864139455</td>\n",
       "      <td>https://www.airbnb.com/rooms/915037822864139455</td>\n",
       "      <td>20230621232643</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>city scrape</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Cozy and bright studio apartment in the centra...</td>\n",
       "      <td>a stone's throw from Porta Romana, in a centra...</td>\n",
       "      <td>https://a0.muscache.com/pictures/miso/Hosting-...</td>\n",
       "      <td>464866073</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>t</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23142 rows × 75 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       id                                      listing_url  \\\n",
       "0                    6400                https://www.airbnb.com/rooms/6400   \n",
       "1                   23986               https://www.airbnb.com/rooms/23986   \n",
       "2                   24107               https://www.airbnb.com/rooms/24107   \n",
       "3                   40470               https://www.airbnb.com/rooms/40470   \n",
       "4                   46536               https://www.airbnb.com/rooms/46536   \n",
       "...                   ...                                              ...   \n",
       "23137  917286381468465380  https://www.airbnb.com/rooms/917286381468465380   \n",
       "23138  915023142785359078  https://www.airbnb.com/rooms/915023142785359078   \n",
       "23139  917286635554478737  https://www.airbnb.com/rooms/917286635554478737   \n",
       "23140  915025934511019523  https://www.airbnb.com/rooms/915025934511019523   \n",
       "23141  915037822864139455  https://www.airbnb.com/rooms/915037822864139455   \n",
       "\n",
       "            scrape_id last_scraped       source  \\\n",
       "0      20230621232643   2023-06-22  city scrape   \n",
       "1      20230621232643   2023-06-22  city scrape   \n",
       "2      20230621232643   2023-06-22  city scrape   \n",
       "3      20230621232643   2023-06-22  city scrape   \n",
       "4      20230621232643   2023-06-22  city scrape   \n",
       "...               ...          ...          ...   \n",
       "23137  20230621232643   2023-06-22  city scrape   \n",
       "23138  20230621232643   2023-06-22  city scrape   \n",
       "23139  20230621232643   2023-06-22  city scrape   \n",
       "23140  20230621232643   2023-06-22  city scrape   \n",
       "23141  20230621232643   2023-06-22  city scrape   \n",
       "\n",
       "                                                    name  \\\n",
       "0      Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...   \n",
       "1      Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...   \n",
       "2      Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...   \n",
       "3      Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...   \n",
       "4      Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...   \n",
       "...                                                  ...   \n",
       "23137  Rental unit in Milano · ★New · 1 bedroom · 1 b...   \n",
       "23138  Rental unit in Milano · ★New · 1 bedroom · 1 b...   \n",
       "23139  Rental unit in Milano · ★New · 3 bedrooms · 5 ...   \n",
       "23140  Rental unit in Milano · ★New · 2 bedrooms · 6 ...   \n",
       "23141  Rental unit in Milano · ★New · 1 bedroom · 1 b...   \n",
       "\n",
       "                                             description  \\\n",
       "0      Enjoy your stay at The Studio, a light-filled ...   \n",
       "1      I look forward to welcoming you in my flat; it...   \n",
       "2      Un grazioso bilocale completamente rinnovato i...   \n",
       "3      <b>The space</b><br />If you want to visit Mil...   \n",
       "4      Over the international fair in April we rent f...   \n",
       "...                                                  ...   \n",
       "23137  L'appartamento è un bellissimo bilocale di rin...   \n",
       "23138  Appartamento di recente costruzione a 3 min ca...   \n",
       "23139  A disposizione un'appartamento enorme, luminos...   \n",
       "23140  Relax in this centrally located quiet space.<b...   \n",
       "23141  Cozy and bright studio apartment in the centra...   \n",
       "\n",
       "                                   neighborhood_overview  \\\n",
       "0      The neighborhood is quiet and very well connec...   \n",
       "1                                                          \n",
       "2                                                          \n",
       "3                                                          \n",
       "4      Flat  It's located in north side of milan, jus...   \n",
       "...                                                  ...   \n",
       "23137                                                      \n",
       "23138                                                      \n",
       "23139  Il quartiere gode di un'atmosfera tranquilla e...   \n",
       "23140  From an architectural point of view, the area ...   \n",
       "23141  a stone's throw from Porta Romana, in a centra...   \n",
       "\n",
       "                                             picture_url    host_id  ...  \\\n",
       "0      https://a0.muscache.com/pictures/474737/5955ba...      13822  ...   \n",
       "1      https://a0.muscache.com/pictures/623d63f8-56cf...      95941  ...   \n",
       "2      https://a0.muscache.com/pictures/cd4112f3-37c2...      46951  ...   \n",
       "3      https://a0.muscache.com/pictures/891684/01c17b...     174203  ...   \n",
       "4      https://a0.muscache.com/pictures/4eb8e0f5-e17b...     138683  ...   \n",
       "...                                                  ...        ...  ...   \n",
       "23137  https://a0.muscache.com/pictures/miso/Hosting-...  497775861  ...   \n",
       "23138  https://a0.muscache.com/pictures/miso/Hosting-...  405312215  ...   \n",
       "23139  https://a0.muscache.com/pictures/707cbcf7-5c2d...  420375498  ...   \n",
       "23140  https://a0.muscache.com/pictures/miso/Hosting-...    4015119  ...   \n",
       "23141  https://a0.muscache.com/pictures/miso/Hosting-...  464866073  ...   \n",
       "\n",
       "      review_scores_communication review_scores_location review_scores_value  \\\n",
       "0                             5.0                   4.56                4.67   \n",
       "1                            4.72                   4.68                4.44   \n",
       "2                             5.0                   3.67                4.67   \n",
       "3                            4.87                   4.39                 4.5   \n",
       "4                            4.93                   4.38                4.66   \n",
       "...                           ...                    ...                 ...   \n",
       "23137                                                                          \n",
       "23138                                                                          \n",
       "23139                                                                          \n",
       "23140                                                                          \n",
       "23141                                                                          \n",
       "\n",
       "      license instant_bookable calculated_host_listings_count  \\\n",
       "0                            f                              1   \n",
       "1                            f                              1   \n",
       "2                            f                              1   \n",
       "3                            f                              2   \n",
       "4                            f                              1   \n",
       "...       ...              ...                            ...   \n",
       "23137                        t                              3   \n",
       "23138                        t                              1   \n",
       "23139                        t                              2   \n",
       "23140                        f                             49   \n",
       "23141                        t                             11   \n",
       "\n",
       "      calculated_host_listings_count_entire_homes  \\\n",
       "0                                               0   \n",
       "1                                               1   \n",
       "2                                               1   \n",
       "3                                               2   \n",
       "4                                               1   \n",
       "...                                           ...   \n",
       "23137                                           3   \n",
       "23138                                           1   \n",
       "23139                                           2   \n",
       "23140                                          49   \n",
       "23141                                          11   \n",
       "\n",
       "      calculated_host_listings_count_private_rooms  \\\n",
       "0                                                1   \n",
       "1                                                0   \n",
       "2                                                0   \n",
       "3                                                0   \n",
       "4                                                0   \n",
       "...                                            ...   \n",
       "23137                                            0   \n",
       "23138                                            0   \n",
       "23139                                            0   \n",
       "23140                                            0   \n",
       "23141                                            0   \n",
       "\n",
       "      calculated_host_listings_count_shared_rooms reviews_per_month  \n",
       "0                                               0              0.07  \n",
       "1                                               0              0.18  \n",
       "2                                               0              0.02  \n",
       "3                                               0              0.27  \n",
       "4                                               0              0.21  \n",
       "...                                           ...               ...  \n",
       "23137                                           0                    \n",
       "23138                                           0                    \n",
       "23139                                           0                    \n",
       "23140                                           0                    \n",
       "23141                                           0                    \n",
       "\n",
       "[23142 rows x 75 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Check table\n",
    "\n",
    "pd.read_sql('SELECT * FROM milan', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# top 10 most profitable on monthly basis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>name</th>\n",
       "      <th>host_name</th>\n",
       "      <th>availability_30</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6400</td>\n",
       "      <td>https://www.airbnb.com/rooms/6400</td>\n",
       "      <td>Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...</td>\n",
       "      <td>Francesca</td>\n",
       "      <td>23</td>\n",
       "      <td>$100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23986</td>\n",
       "      <td>https://www.airbnb.com/rooms/23986</td>\n",
       "      <td>Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...</td>\n",
       "      <td>Jeremy</td>\n",
       "      <td>3</td>\n",
       "      <td>$150.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24107</td>\n",
       "      <td>https://www.airbnb.com/rooms/24107</td>\n",
       "      <td>Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...</td>\n",
       "      <td>Valeria</td>\n",
       "      <td>30</td>\n",
       "      <td>$100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40470</td>\n",
       "      <td>https://www.airbnb.com/rooms/40470</td>\n",
       "      <td>Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...</td>\n",
       "      <td>Giacinto</td>\n",
       "      <td>22</td>\n",
       "      <td>$80.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>46536</td>\n",
       "      <td>https://www.airbnb.com/rooms/46536</td>\n",
       "      <td>Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...</td>\n",
       "      <td>Nico&amp;Cinzia</td>\n",
       "      <td>0</td>\n",
       "      <td>$90.00</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>23137</th>\n",
       "      <td>917286381468465380</td>\n",
       "      <td>https://www.airbnb.com/rooms/917286381468465380</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Simone</td>\n",
       "      <td>12</td>\n",
       "      <td>$61.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23138</th>\n",
       "      <td>915023142785359078</td>\n",
       "      <td>https://www.airbnb.com/rooms/915023142785359078</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Edoardo</td>\n",
       "      <td>15</td>\n",
       "      <td>$89.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23139</th>\n",
       "      <td>917286635554478737</td>\n",
       "      <td>https://www.airbnb.com/rooms/917286635554478737</td>\n",
       "      <td>Rental unit in Milano · ★New · 3 bedrooms · 5 ...</td>\n",
       "      <td>Simone</td>\n",
       "      <td>30</td>\n",
       "      <td>$300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23140</th>\n",
       "      <td>915025934511019523</td>\n",
       "      <td>https://www.airbnb.com/rooms/915025934511019523</td>\n",
       "      <td>Rental unit in Milano · ★New · 2 bedrooms · 6 ...</td>\n",
       "      <td>Igor</td>\n",
       "      <td>20</td>\n",
       "      <td>$289.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23141</th>\n",
       "      <td>915037822864139455</td>\n",
       "      <td>https://www.airbnb.com/rooms/915037822864139455</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Shura</td>\n",
       "      <td>7</td>\n",
       "      <td>$100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23142 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       id                                      listing_url  \\\n",
       "0                    6400                https://www.airbnb.com/rooms/6400   \n",
       "1                   23986               https://www.airbnb.com/rooms/23986   \n",
       "2                   24107               https://www.airbnb.com/rooms/24107   \n",
       "3                   40470               https://www.airbnb.com/rooms/40470   \n",
       "4                   46536               https://www.airbnb.com/rooms/46536   \n",
       "...                   ...                                              ...   \n",
       "23137  917286381468465380  https://www.airbnb.com/rooms/917286381468465380   \n",
       "23138  915023142785359078  https://www.airbnb.com/rooms/915023142785359078   \n",
       "23139  917286635554478737  https://www.airbnb.com/rooms/917286635554478737   \n",
       "23140  915025934511019523  https://www.airbnb.com/rooms/915025934511019523   \n",
       "23141  915037822864139455  https://www.airbnb.com/rooms/915037822864139455   \n",
       "\n",
       "                                                    name    host_name  \\\n",
       "0      Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...    Francesca   \n",
       "1      Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...       Jeremy   \n",
       "2      Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...      Valeria   \n",
       "3      Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...     Giacinto   \n",
       "4      Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...  Nico&Cinzia   \n",
       "...                                                  ...          ...   \n",
       "23137  Rental unit in Milano · ★New · 1 bedroom · 1 b...       Simone   \n",
       "23138  Rental unit in Milano · ★New · 1 bedroom · 1 b...      Edoardo   \n",
       "23139  Rental unit in Milano · ★New · 3 bedrooms · 5 ...       Simone   \n",
       "23140  Rental unit in Milano · ★New · 2 bedrooms · 6 ...         Igor   \n",
       "23141  Rental unit in Milano · ★New · 1 bedroom · 1 b...        Shura   \n",
       "\n",
       "      availability_30    price  \n",
       "0                  23  $100.00  \n",
       "1                   3  $150.00  \n",
       "2                  30  $100.00  \n",
       "3                  22   $80.00  \n",
       "4                   0   $90.00  \n",
       "...               ...      ...  \n",
       "23137              12   $61.00  \n",
       "23138              15   $89.00  \n",
       "23139              30  $300.00  \n",
       "23140              20  $289.00  \n",
       "23141               7  $100.00  \n",
       "\n",
       "[23142 rows x 6 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT id, listing_url, name, host_name, availability_30 , price FROM milan', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# availability_30 means how many days the house still has no guests so you have to subtract it from 30 days in a month\n",
    "# also $ sign in price column must be removed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = engine.connect()\n",
    "query = text(\"UPDATE milan SET price = CAST(SUBSTRING(price, 2) AS SIGNED)\")\n",
    "conn.execute(query)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>name</th>\n",
       "      <th>host_name</th>\n",
       "      <th>availability_30</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6400</td>\n",
       "      <td>https://www.airbnb.com/rooms/6400</td>\n",
       "      <td>Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...</td>\n",
       "      <td>Francesca</td>\n",
       "      <td>23</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23986</td>\n",
       "      <td>https://www.airbnb.com/rooms/23986</td>\n",
       "      <td>Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...</td>\n",
       "      <td>Jeremy</td>\n",
       "      <td>3</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24107</td>\n",
       "      <td>https://www.airbnb.com/rooms/24107</td>\n",
       "      <td>Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...</td>\n",
       "      <td>Valeria</td>\n",
       "      <td>30</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40470</td>\n",
       "      <td>https://www.airbnb.com/rooms/40470</td>\n",
       "      <td>Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...</td>\n",
       "      <td>Giacinto</td>\n",
       "      <td>22</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>46536</td>\n",
       "      <td>https://www.airbnb.com/rooms/46536</td>\n",
       "      <td>Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...</td>\n",
       "      <td>Nico&amp;Cinzia</td>\n",
       "      <td>0</td>\n",
       "      <td>90</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>23137</th>\n",
       "      <td>917286381468465380</td>\n",
       "      <td>https://www.airbnb.com/rooms/917286381468465380</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Simone</td>\n",
       "      <td>12</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23138</th>\n",
       "      <td>915023142785359078</td>\n",
       "      <td>https://www.airbnb.com/rooms/915023142785359078</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Edoardo</td>\n",
       "      <td>15</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23139</th>\n",
       "      <td>917286635554478737</td>\n",
       "      <td>https://www.airbnb.com/rooms/917286635554478737</td>\n",
       "      <td>Rental unit in Milano · ★New · 3 bedrooms · 5 ...</td>\n",
       "      <td>Simone</td>\n",
       "      <td>30</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23140</th>\n",
       "      <td>915025934511019523</td>\n",
       "      <td>https://www.airbnb.com/rooms/915025934511019523</td>\n",
       "      <td>Rental unit in Milano · ★New · 2 bedrooms · 6 ...</td>\n",
       "      <td>Igor</td>\n",
       "      <td>20</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23141</th>\n",
       "      <td>915037822864139455</td>\n",
       "      <td>https://www.airbnb.com/rooms/915037822864139455</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Shura</td>\n",
       "      <td>7</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23142 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       id                                      listing_url  \\\n",
       "0                    6400                https://www.airbnb.com/rooms/6400   \n",
       "1                   23986               https://www.airbnb.com/rooms/23986   \n",
       "2                   24107               https://www.airbnb.com/rooms/24107   \n",
       "3                   40470               https://www.airbnb.com/rooms/40470   \n",
       "4                   46536               https://www.airbnb.com/rooms/46536   \n",
       "...                   ...                                              ...   \n",
       "23137  917286381468465380  https://www.airbnb.com/rooms/917286381468465380   \n",
       "23138  915023142785359078  https://www.airbnb.com/rooms/915023142785359078   \n",
       "23139  917286635554478737  https://www.airbnb.com/rooms/917286635554478737   \n",
       "23140  915025934511019523  https://www.airbnb.com/rooms/915025934511019523   \n",
       "23141  915037822864139455  https://www.airbnb.com/rooms/915037822864139455   \n",
       "\n",
       "                                                    name    host_name  \\\n",
       "0      Rental unit in Milan · ★4.89 · 3 bedrooms · 1 ...    Francesca   \n",
       "1      Rental unit in Milan · ★4.63 · 1 bedroom · 1 b...       Jeremy   \n",
       "2      Condo in Milan · ★4.50 · 1 bedroom · 6 beds · ...      Valeria   \n",
       "3      Rental unit in Milan · ★4.66 · 2 bedrooms · 4 ...     Giacinto   \n",
       "4      Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...  Nico&Cinzia   \n",
       "...                                                  ...          ...   \n",
       "23137  Rental unit in Milano · ★New · 1 bedroom · 1 b...       Simone   \n",
       "23138  Rental unit in Milano · ★New · 1 bedroom · 1 b...      Edoardo   \n",
       "23139  Rental unit in Milano · ★New · 3 bedrooms · 5 ...       Simone   \n",
       "23140  Rental unit in Milano · ★New · 2 bedrooms · 6 ...         Igor   \n",
       "23141  Rental unit in Milano · ★New · 1 bedroom · 1 b...        Shura   \n",
       "\n",
       "      availability_30 price  \n",
       "0                  23   100  \n",
       "1                   3   150  \n",
       "2                  30   100  \n",
       "3                  22    80  \n",
       "4                   0    90  \n",
       "...               ...   ...  \n",
       "23137              12    61  \n",
       "23138              15    89  \n",
       "23139              30   300  \n",
       "23140              20   289  \n",
       "23141               7   100  \n",
       "\n",
       "[23142 rows x 6 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#price fixed\n",
    "pd.read_sql('SELECT id, listing_url, name, host_name, availability_30 , price FROM milan', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>name</th>\n",
       "      <th>host_name</th>\n",
       "      <th>availability_30</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>46536</td>\n",
       "      <td>https://www.airbnb.com/rooms/46536</td>\n",
       "      <td>Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...</td>\n",
       "      <td>Nico&amp;Cinzia</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55055</td>\n",
       "      <td>https://www.airbnb.com/rooms/55055</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 1 bed · 1...</td>\n",
       "      <td>Cristina</td>\n",
       "      <td>0</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>93025</td>\n",
       "      <td>https://www.airbnb.com/rooms/93025</td>\n",
       "      <td>Rental unit in Milan · ★4.86 · 1 bedroom · 2 b...</td>\n",
       "      <td>Angela</td>\n",
       "      <td>0</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>94233</td>\n",
       "      <td>https://www.airbnb.com/rooms/94233</td>\n",
       "      <td>Rental unit in Milan · ★4.94 · 1 bedroom · 2 b...</td>\n",
       "      <td>Andre E Ale</td>\n",
       "      <td>0</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>74835</td>\n",
       "      <td>https://www.airbnb.com/rooms/74835</td>\n",
       "      <td>Rental unit in Milan · ★5.0 · 1 bedroom · 1 be...</td>\n",
       "      <td>Augusto</td>\n",
       "      <td>0</td>\n",
       "      <td>280</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>7965</th>\n",
       "      <td>918004230955889147</td>\n",
       "      <td>https://www.airbnb.com/rooms/918004230955889147</td>\n",
       "      <td>Rental unit in Milano · ★New · 2 bedrooms · 3 ...</td>\n",
       "      <td>Hintown</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7966</th>\n",
       "      <td>916561513053933712</td>\n",
       "      <td>https://www.airbnb.com/rooms/916561513053933712</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Simone</td>\n",
       "      <td>0</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7967</th>\n",
       "      <td>914965997042368609</td>\n",
       "      <td>https://www.airbnb.com/rooms/914965997042368609</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Mahnoosh</td>\n",
       "      <td>0</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7968</th>\n",
       "      <td>917065365885178621</td>\n",
       "      <td>https://www.airbnb.com/rooms/917065365885178621</td>\n",
       "      <td>Rental unit in Milano · ★New · 2 bedrooms · 1 ...</td>\n",
       "      <td>Silvia</td>\n",
       "      <td>0</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7969</th>\n",
       "      <td>914982280588785521</td>\n",
       "      <td>https://www.airbnb.com/rooms/914982280588785521</td>\n",
       "      <td>Rental unit in Milano · ★New · 1 bedroom · 1 b...</td>\n",
       "      <td>Rebecca</td>\n",
       "      <td>0</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7970 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      id                                      listing_url  \\\n",
       "0                  46536               https://www.airbnb.com/rooms/46536   \n",
       "1                  55055               https://www.airbnb.com/rooms/55055   \n",
       "2                  93025               https://www.airbnb.com/rooms/93025   \n",
       "3                  94233               https://www.airbnb.com/rooms/94233   \n",
       "4                  74835               https://www.airbnb.com/rooms/74835   \n",
       "...                  ...                                              ...   \n",
       "7965  918004230955889147  https://www.airbnb.com/rooms/918004230955889147   \n",
       "7966  916561513053933712  https://www.airbnb.com/rooms/916561513053933712   \n",
       "7967  914965997042368609  https://www.airbnb.com/rooms/914965997042368609   \n",
       "7968  917065365885178621  https://www.airbnb.com/rooms/917065365885178621   \n",
       "7969  914982280588785521  https://www.airbnb.com/rooms/914982280588785521   \n",
       "\n",
       "                                                   name    host_name  \\\n",
       "0     Rental unit in Milan · ★4.59 · 2 bedrooms · 3 ...  Nico&Cinzia   \n",
       "1     Rental unit in Milano · 2 bedrooms · 1 bed · 1...     Cristina   \n",
       "2     Rental unit in Milan · ★4.86 · 1 bedroom · 2 b...       Angela   \n",
       "3     Rental unit in Milan · ★4.94 · 1 bedroom · 2 b...  Andre E Ale   \n",
       "4     Rental unit in Milan · ★5.0 · 1 bedroom · 1 be...      Augusto   \n",
       "...                                                 ...          ...   \n",
       "7965  Rental unit in Milano · ★New · 2 bedrooms · 3 ...      Hintown   \n",
       "7966  Rental unit in Milano · ★New · 1 bedroom · 1 b...       Simone   \n",
       "7967  Rental unit in Milano · ★New · 1 bedroom · 1 b...     Mahnoosh   \n",
       "7968  Rental unit in Milano · ★New · 2 bedrooms · 1 ...       Silvia   \n",
       "7969  Rental unit in Milano · ★New · 1 bedroom · 1 b...      Rebecca   \n",
       "\n",
       "     availability_30 price  \n",
       "0                  0    90  \n",
       "1                  0   145  \n",
       "2                  0    90  \n",
       "3                  0    95  \n",
       "4                  0   280  \n",
       "...              ...   ...  \n",
       "7965               0     1  \n",
       "7966               0    84  \n",
       "7967               0    61  \n",
       "7968               0   130  \n",
       "7969               0   122  \n",
       "\n",
       "[7970 rows x 6 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Just a check to see who already as no more free rooms\n",
    "pd.read_sql('SELECT id, listing_url, name, host_name, availability_30 , price FROM milan WHERE availability_30 = 0', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>name</th>\n",
       "      <th>host_name</th>\n",
       "      <th>price</th>\n",
       "      <th>days_booked</th>\n",
       "      <th>revenues</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51863754</td>\n",
       "      <td>https://www.airbnb.com/rooms/51863754</td>\n",
       "      <td>Condo in Milano · 1 bedroom · 1 bath</td>\n",
       "      <td>Una Esperienze</td>\n",
       "      <td>999</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29970.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>549152546766700702</td>\n",
       "      <td>https://www.airbnb.com/rooms/549152546766700702</td>\n",
       "      <td>Condo in Milano · 1 bedroom · 1 bed · 1 bath</td>\n",
       "      <td>Una Esperienze</td>\n",
       "      <td>999</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29970.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>863610123219134402</td>\n",
       "      <td>https://www.airbnb.com/rooms/863610123219134402</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Ilia</td>\n",
       "      <td>999</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29970.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>859294043937656110</td>\n",
       "      <td>https://www.airbnb.com/rooms/859294043937656110</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1....</td>\n",
       "      <td>Selva</td>\n",
       "      <td>992</td>\n",
       "      <td>30.0</td>\n",
       "      <td>29760.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22708045</td>\n",
       "      <td>https://www.airbnb.com/rooms/22708045</td>\n",
       "      <td>Rental unit in Milano · Studio · 2 beds · 1 bath</td>\n",
       "      <td>Daniela</td>\n",
       "      <td>950</td>\n",
       "      <td>30.0</td>\n",
       "      <td>28500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>29959334</td>\n",
       "      <td>https://www.airbnb.com/rooms/29959334</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Giuseppe</td>\n",
       "      <td>950</td>\n",
       "      <td>30.0</td>\n",
       "      <td>28500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>39550934</td>\n",
       "      <td>https://www.airbnb.com/rooms/39550934</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 3 beds · ...</td>\n",
       "      <td>Giorgio</td>\n",
       "      <td>950</td>\n",
       "      <td>30.0</td>\n",
       "      <td>28500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>634455278163373759</td>\n",
       "      <td>https://www.airbnb.com/rooms/634455278163373759</td>\n",
       "      <td>Condo in Milano · ★4.92 · 3 bedrooms · 3 beds ...</td>\n",
       "      <td>Prestige</td>\n",
       "      <td>950</td>\n",
       "      <td>30.0</td>\n",
       "      <td>28500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10982650</td>\n",
       "      <td>https://www.airbnb.com/rooms/10982650</td>\n",
       "      <td>Loft in Milano · Studio · 1 bed · 2.5 baths</td>\n",
       "      <td>Max</td>\n",
       "      <td>900</td>\n",
       "      <td>30.0</td>\n",
       "      <td>27000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>17809188</td>\n",
       "      <td>https://www.airbnb.com/rooms/17809188</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 2 ...</td>\n",
       "      <td>Marilinda</td>\n",
       "      <td>900</td>\n",
       "      <td>30.0</td>\n",
       "      <td>27000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id                                      listing_url  \\\n",
       "0            51863754            https://www.airbnb.com/rooms/51863754   \n",
       "1  549152546766700702  https://www.airbnb.com/rooms/549152546766700702   \n",
       "2  863610123219134402  https://www.airbnb.com/rooms/863610123219134402   \n",
       "3  859294043937656110  https://www.airbnb.com/rooms/859294043937656110   \n",
       "4            22708045            https://www.airbnb.com/rooms/22708045   \n",
       "5            29959334            https://www.airbnb.com/rooms/29959334   \n",
       "6            39550934            https://www.airbnb.com/rooms/39550934   \n",
       "7  634455278163373759  https://www.airbnb.com/rooms/634455278163373759   \n",
       "8            10982650            https://www.airbnb.com/rooms/10982650   \n",
       "9            17809188            https://www.airbnb.com/rooms/17809188   \n",
       "\n",
       "                                                name       host_name price  \\\n",
       "0               Condo in Milano · 1 bedroom · 1 bath  Una Esperienze   999   \n",
       "1       Condo in Milano · 1 bedroom · 1 bed · 1 bath  Una Esperienze   999   \n",
       "2  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...            Ilia   999   \n",
       "3  Rental unit in Milano · 1 bedroom · 1 bed · 1....           Selva   992   \n",
       "4   Rental unit in Milano · Studio · 2 beds · 1 bath         Daniela   950   \n",
       "5  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...        Giuseppe   950   \n",
       "6  Rental unit in Milano · 2 bedrooms · 3 beds · ...         Giorgio   950   \n",
       "7  Condo in Milano · ★4.92 · 3 bedrooms · 3 beds ...        Prestige   950   \n",
       "8        Loft in Milano · Studio · 1 bed · 2.5 baths             Max   900   \n",
       "9  Rental unit in Milano · 1 bedroom · 1 bed · 2 ...       Marilinda   900   \n",
       "\n",
       "   days_booked  revenues  \n",
       "0         30.0   29970.0  \n",
       "1         30.0   29970.0  \n",
       "2         30.0   29970.0  \n",
       "3         30.0   29760.0  \n",
       "4         30.0   28500.0  \n",
       "5         30.0   28500.0  \n",
       "6         30.0   28500.0  \n",
       "7         30.0   28500.0  \n",
       "8         30.0   27000.0  \n",
       "9         30.0   27000.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL query to see who has the highest monthly revenues\n",
    "pd.read_sql('SELECT id, listing_url, name, host_name, price,'\n",
    "            '30-availability_30 AS days_booked,'\n",
    "            '(30-availability_30)*price AS revenues FROM milan ORDER BY revenues DESC LIMIT 10',\n",
    "            engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>name</th>\n",
       "      <th>host_name</th>\n",
       "      <th>price</th>\n",
       "      <th>days_booked</th>\n",
       "      <th>revenues</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8696049</td>\n",
       "      <td>https://www.airbnb.com/rooms/8696049</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 2 beds · 1...</td>\n",
       "      <td>Antonia</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23765630</td>\n",
       "      <td>https://www.airbnb.com/rooms/23765630</td>\n",
       "      <td>Loft in Milano · ★5.0 · 2 bedrooms · 4 beds · ...</td>\n",
       "      <td>Sara</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26374702</td>\n",
       "      <td>https://www.airbnb.com/rooms/26374702</td>\n",
       "      <td>Condo in Milano · 1 bedroom · 1 bed · 1 bath</td>\n",
       "      <td>Cinzia</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>52314044</td>\n",
       "      <td>https://www.airbnb.com/rooms/52314044</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Duomo Luxury Apartments</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>52314048</td>\n",
       "      <td>https://www.airbnb.com/rooms/52314048</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Duomo Luxury Apartments</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>636046255828034867</td>\n",
       "      <td>https://www.airbnb.com/rooms/636046255828034867</td>\n",
       "      <td>Home in Milano · 1 bedroom · 2 beds · 1 bath</td>\n",
       "      <td>Travelnest</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>636046258124500642</td>\n",
       "      <td>https://www.airbnb.com/rooms/636046258124500642</td>\n",
       "      <td>Rental unit in Milano · 3 bedrooms · 3 beds · ...</td>\n",
       "      <td>Travelnest</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>826148349614272727</td>\n",
       "      <td>https://www.airbnb.com/rooms/826148349614272727</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...</td>\n",
       "      <td>Maria Grazia</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>853664039537902978</td>\n",
       "      <td>https://www.airbnb.com/rooms/853664039537902978</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 2 beds · ...</td>\n",
       "      <td>Midori</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>864949219616772810</td>\n",
       "      <td>https://www.airbnb.com/rooms/864949219616772810</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 2 beds · ...</td>\n",
       "      <td>Francesco Antonio</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id                                      listing_url  \\\n",
       "0             8696049             https://www.airbnb.com/rooms/8696049   \n",
       "1            23765630            https://www.airbnb.com/rooms/23765630   \n",
       "2            26374702            https://www.airbnb.com/rooms/26374702   \n",
       "3            52314044            https://www.airbnb.com/rooms/52314044   \n",
       "4            52314048            https://www.airbnb.com/rooms/52314048   \n",
       "5  636046255828034867  https://www.airbnb.com/rooms/636046255828034867   \n",
       "6  636046258124500642  https://www.airbnb.com/rooms/636046258124500642   \n",
       "7  826148349614272727  https://www.airbnb.com/rooms/826148349614272727   \n",
       "8  853664039537902978  https://www.airbnb.com/rooms/853664039537902978   \n",
       "9  864949219616772810  https://www.airbnb.com/rooms/864949219616772810   \n",
       "\n",
       "                                                name                host_name  \\\n",
       "0  Rental unit in Milano · 1 bedroom · 2 beds · 1...                  Antonia   \n",
       "1  Loft in Milano · ★5.0 · 2 bedrooms · 4 beds · ...                     Sara   \n",
       "2       Condo in Milano · 1 bedroom · 1 bed · 1 bath                   Cinzia   \n",
       "3  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...  Duomo Luxury Apartments   \n",
       "4  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...  Duomo Luxury Apartments   \n",
       "5       Home in Milano · 1 bedroom · 2 beds · 1 bath               Travelnest   \n",
       "6  Rental unit in Milano · 3 bedrooms · 3 beds · ...               Travelnest   \n",
       "7  Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...             Maria Grazia   \n",
       "8  Rental unit in Milano · 2 bedrooms · 2 beds · ...                   Midori   \n",
       "9  Rental unit in Milano · 2 bedrooms · 2 beds · ...        Francesco Antonio   \n",
       "\n",
       "  price  days_booked  revenues  \n",
       "0     1          1.0       1.0  \n",
       "1     1          1.0       1.0  \n",
       "2     1          1.0       1.0  \n",
       "3     1          1.0       1.0  \n",
       "4     1          1.0       1.0  \n",
       "5     1          1.0       1.0  \n",
       "6     1          1.0       1.0  \n",
       "7     1          1.0       1.0  \n",
       "8     1          1.0       1.0  \n",
       "9     1          1.0       1.0  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL query to see who has the lowest monthly revenues with at least 1 day booked\n",
    "pd.read_sql('SELECT id, listing_url, name, host_name, price,'\n",
    "            '30-availability_30 AS days_booked,'\n",
    "            '(30-availability_30)*price AS revenues FROM milan WHERE 30-availability_30 >= 1 ORDER BY revenues ASC LIMIT 10',\n",
    "            engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>name</th>\n",
       "      <th>host_name</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>759260243762860753</td>\n",
       "      <td>https://www.airbnb.com/rooms/759260243762860753</td>\n",
       "      <td>Rental unit in Milano · 3 bedrooms · 4 beds · ...</td>\n",
       "      <td>Aaron</td>\n",
       "      <td>5.0</td>\n",
       "      <td>995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32535420</td>\n",
       "      <td>https://www.airbnb.com/rooms/32535420</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 4 beds · 1...</td>\n",
       "      <td>Sara</td>\n",
       "      <td>5.0</td>\n",
       "      <td>990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7640976</td>\n",
       "      <td>https://www.airbnb.com/rooms/7640976</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 2 bedrooms · 2 ...</td>\n",
       "      <td>Adele</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17544479</td>\n",
       "      <td>https://www.airbnb.com/rooms/17544479</td>\n",
       "      <td>Rental unit in Milano · ★4.75 · 1 bedroom · 2 ...</td>\n",
       "      <td>Gianpiero</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17846540</td>\n",
       "      <td>https://www.airbnb.com/rooms/17846540</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Daniela Marcella</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>27723922</td>\n",
       "      <td>https://www.airbnb.com/rooms/27723922</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 2 ...</td>\n",
       "      <td>Walter</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>28536904</td>\n",
       "      <td>https://www.airbnb.com/rooms/28536904</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 2 bedrooms · 3 ...</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>32623946</td>\n",
       "      <td>https://www.airbnb.com/rooms/32623946</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 3 beds · 1...</td>\n",
       "      <td>Pamela</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>36203995</td>\n",
       "      <td>https://www.airbnb.com/rooms/36203995</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Federico</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>39252831</td>\n",
       "      <td>https://www.airbnb.com/rooms/39252831</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...</td>\n",
       "      <td>Ombretta</td>\n",
       "      <td>5.0</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   id                                      listing_url  \\\n",
       "0  759260243762860753  https://www.airbnb.com/rooms/759260243762860753   \n",
       "1            32535420            https://www.airbnb.com/rooms/32535420   \n",
       "2             7640976             https://www.airbnb.com/rooms/7640976   \n",
       "3            17544479            https://www.airbnb.com/rooms/17544479   \n",
       "4            17846540            https://www.airbnb.com/rooms/17846540   \n",
       "5            27723922            https://www.airbnb.com/rooms/27723922   \n",
       "6            28536904            https://www.airbnb.com/rooms/28536904   \n",
       "7            32623946            https://www.airbnb.com/rooms/32623946   \n",
       "8            36203995            https://www.airbnb.com/rooms/36203995   \n",
       "9            39252831            https://www.airbnb.com/rooms/39252831   \n",
       "\n",
       "                                                name         host_name  \\\n",
       "0  Rental unit in Milano · 3 bedrooms · 4 beds · ...             Aaron   \n",
       "1  Rental unit in Milano · 1 bedroom · 4 beds · 1...              Sara   \n",
       "2  Rental unit in Milano · ★5.0 · 2 bedrooms · 2 ...             Adele   \n",
       "3  Rental unit in Milano · ★4.75 · 1 bedroom · 2 ...         Gianpiero   \n",
       "4  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...  Daniela Marcella   \n",
       "5  Rental unit in Milano · 1 bedroom · 1 bed · 2 ...            Walter   \n",
       "6  Rental unit in Milano · ★5.0 · 2 bedrooms · 3 ...            Andrea   \n",
       "7  Rental unit in Milano · 1 bedroom · 3 beds · 1...            Pamela   \n",
       "8  Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...          Federico   \n",
       "9  Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...          Ombretta   \n",
       "\n",
       "  review_scores_value price  \n",
       "0                 5.0   995  \n",
       "1                 5.0   990  \n",
       "2                 5.0    99  \n",
       "3                 5.0    99  \n",
       "4                 5.0    99  \n",
       "5                 5.0    99  \n",
       "6                 5.0    99  \n",
       "7                 5.0    99  \n",
       "8                 5.0    99  \n",
       "9                 5.0    99  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL query to see who has the highest review score value and the highest price for night\n",
    "\n",
    "pd.read_sql('SELECT id, listing_url, name, host_name,review_scores_value , price FROM milan ORDER BY review_scores_value DESC , price DESC LIMIT 10', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>name</th>\n",
       "      <th>host_name</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21903718</td>\n",
       "      <td>https://www.airbnb.com/rooms/21903718</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 2 beds · ...</td>\n",
       "      <td>Maria</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38663139</td>\n",
       "      <td>https://www.airbnb.com/rooms/38663139</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 2 ...</td>\n",
       "      <td>Edoardo</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>746168462892951033</td>\n",
       "      <td>https://www.airbnb.com/rooms/746168462892951033</td>\n",
       "      <td>Home in Milano · ★5.0 · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>I-Host</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5080494</td>\n",
       "      <td>https://www.airbnb.com/rooms/5080494</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 2 beds · 2...</td>\n",
       "      <td>Alessandra</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20549408</td>\n",
       "      <td>https://www.airbnb.com/rooms/20549408</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Fabrizio</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>22244736</td>\n",
       "      <td>https://www.airbnb.com/rooms/22244736</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1....</td>\n",
       "      <td>Alessio</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>26750044</td>\n",
       "      <td>https://www.airbnb.com/rooms/26750044</td>\n",
       "      <td>Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...</td>\n",
       "      <td>Alessandro</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>30562371</td>\n",
       "      <td>https://www.airbnb.com/rooms/30562371</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Rosanna</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>33514317</td>\n",
       "      <td>https://www.airbnb.com/rooms/33514317</td>\n",
       "      <td>Rental unit in Milano · ★4.91 · 1 bedroom · 1 ...</td>\n",
       "      <td>Mario</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>37647967</td>\n",
       "      <td>https://www.airbnb.com/rooms/37647967</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1....</td>\n",
       "      <td>Luca</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>40254200</td>\n",
       "      <td>https://www.airbnb.com/rooms/40254200</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Federica</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>41806836</td>\n",
       "      <td>https://www.airbnb.com/rooms/41806836</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...</td>\n",
       "      <td>Microaffitto</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>42760690</td>\n",
       "      <td>https://www.airbnb.com/rooms/42760690</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 3 beds · ...</td>\n",
       "      <td>Mattia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>48275305</td>\n",
       "      <td>https://www.airbnb.com/rooms/48275305</td>\n",
       "      <td>Rental unit in Milano · ★4.75 · 1 bedroom · 1 ...</td>\n",
       "      <td>Bovisa Urban Garden</td>\n",
       "      <td>5.0</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>23348490</td>\n",
       "      <td>https://www.airbnb.com/rooms/23348490</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Federico</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>33672441</td>\n",
       "      <td>https://www.airbnb.com/rooms/33672441</td>\n",
       "      <td>Rental unit in Milano · ★4.75 · 1 bedroom · 1 ...</td>\n",
       "      <td>Serena</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>748129484443939869</td>\n",
       "      <td>https://www.airbnb.com/rooms/748129484443939869</td>\n",
       "      <td>Condo in Milano · 1 bedroom · 2 beds · 2 share...</td>\n",
       "      <td>Pietro Antonio</td>\n",
       "      <td>5.0</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>33551708</td>\n",
       "      <td>https://www.airbnb.com/rooms/33551708</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>5.0</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>53374987</td>\n",
       "      <td>https://www.airbnb.com/rooms/53374987</td>\n",
       "      <td>Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...</td>\n",
       "      <td>Ciro</td>\n",
       "      <td>5.0</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>794225384535281896</td>\n",
       "      <td>https://www.airbnb.com/rooms/794225384535281896</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 3 bedrooms · 3 ...</td>\n",
       "      <td>Pietro Antonio</td>\n",
       "      <td>5.0</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5526647</td>\n",
       "      <td>https://www.airbnb.com/rooms/5526647</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 1 bed · 1...</td>\n",
       "      <td>Marta</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>27357876</td>\n",
       "      <td>https://www.airbnb.com/rooms/27357876</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Melania</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>47167043</td>\n",
       "      <td>https://www.airbnb.com/rooms/47167043</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 shared bath</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>49283439</td>\n",
       "      <td>https://www.airbnb.com/rooms/49283439</td>\n",
       "      <td>Condo in Milano · 1 bedroom · 1 bed · 1 bath</td>\n",
       "      <td>Claudio</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>54191245</td>\n",
       "      <td>https://www.airbnb.com/rooms/54191245</td>\n",
       "      <td>Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...</td>\n",
       "      <td>Maura</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>757102610729300532</td>\n",
       "      <td>https://www.airbnb.com/rooms/757102610729300532</td>\n",
       "      <td>Condo in Milano · 1 bedroom · 1 bed · 1 shared...</td>\n",
       "      <td>Emmanuele Ettore</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>769452711067062799</td>\n",
       "      <td>https://www.airbnb.com/rooms/769452711067062799</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Roberta</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>816482689478682174</td>\n",
       "      <td>https://www.airbnb.com/rooms/816482689478682174</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Elena</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>832571285825030473</td>\n",
       "      <td>https://www.airbnb.com/rooms/832571285825030473</td>\n",
       "      <td>Condo in Milano · 1 bedroom · 1 bed · 1 bath</td>\n",
       "      <td>Angela</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>842110365786260956</td>\n",
       "      <td>https://www.airbnb.com/rooms/842110365786260956</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Elia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>413624</td>\n",
       "      <td>https://www.airbnb.com/rooms/413624</td>\n",
       "      <td>Rental unit in Milan · ★5.0 · 1 bedroom · 1 be...</td>\n",
       "      <td>Lucia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>794813</td>\n",
       "      <td>https://www.airbnb.com/rooms/794813</td>\n",
       "      <td>Rental unit in Milan · 1 bedroom · 1 bed · 1 p...</td>\n",
       "      <td>Claudia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>3382210</td>\n",
       "      <td>https://www.airbnb.com/rooms/3382210</td>\n",
       "      <td>Loft in Milano · Studio · 2 beds · 1 bath</td>\n",
       "      <td>Andrea</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>4304271</td>\n",
       "      <td>https://www.airbnb.com/rooms/4304271</td>\n",
       "      <td>Rental unit in Milan · 1 bedroom · 1 bed · 1 bath</td>\n",
       "      <td>Lorenzo</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>5090037</td>\n",
       "      <td>https://www.airbnb.com/rooms/5090037</td>\n",
       "      <td>Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...</td>\n",
       "      <td>Magda</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>5577271</td>\n",
       "      <td>https://www.airbnb.com/rooms/5577271</td>\n",
       "      <td>Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...</td>\n",
       "      <td>Raffaella</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>8200770</td>\n",
       "      <td>https://www.airbnb.com/rooms/8200770</td>\n",
       "      <td>Rental unit in Milano · ★4.67 · Studio · 2 bed...</td>\n",
       "      <td>Mary</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>12309501</td>\n",
       "      <td>https://www.airbnb.com/rooms/12309501</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Francesco</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>13820529</td>\n",
       "      <td>https://www.airbnb.com/rooms/13820529</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 2 beds · 2...</td>\n",
       "      <td>Giulia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>18007252</td>\n",
       "      <td>https://www.airbnb.com/rooms/18007252</td>\n",
       "      <td>Home in Milano · 1 bedroom · 1 bed · 1 bath</td>\n",
       "      <td>Reem</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>18780943</td>\n",
       "      <td>https://www.airbnb.com/rooms/18780943</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Felipe</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>20946198</td>\n",
       "      <td>https://www.airbnb.com/rooms/20946198</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Irina</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>23661650</td>\n",
       "      <td>https://www.airbnb.com/rooms/23661650</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Alessandro</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>26343156</td>\n",
       "      <td>https://www.airbnb.com/rooms/26343156</td>\n",
       "      <td>Rental unit in Milano · 2 bedrooms · 2 beds · ...</td>\n",
       "      <td>Giacomo</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>28773125</td>\n",
       "      <td>https://www.airbnb.com/rooms/28773125</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Carolina</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>29050905</td>\n",
       "      <td>https://www.airbnb.com/rooms/29050905</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 2 ...</td>\n",
       "      <td>Francesca</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>31920960</td>\n",
       "      <td>https://www.airbnb.com/rooms/31920960</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 2 beds · 1...</td>\n",
       "      <td>Fulvio</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>33746509</td>\n",
       "      <td>https://www.airbnb.com/rooms/33746509</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 2 beds · 1...</td>\n",
       "      <td>Ilaria</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>35938752</td>\n",
       "      <td>https://www.airbnb.com/rooms/35938752</td>\n",
       "      <td>Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...</td>\n",
       "      <td>Federica</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>36803128</td>\n",
       "      <td>https://www.airbnb.com/rooms/36803128</td>\n",
       "      <td>Rental unit in Milano · 1 bedroom · 1 bed · 1 ...</td>\n",
       "      <td>Enzo</td>\n",
       "      <td>5.0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    id                                      listing_url  \\\n",
       "0             21903718            https://www.airbnb.com/rooms/21903718   \n",
       "1             38663139            https://www.airbnb.com/rooms/38663139   \n",
       "2   746168462892951033  https://www.airbnb.com/rooms/746168462892951033   \n",
       "3              5080494             https://www.airbnb.com/rooms/5080494   \n",
       "4             20549408            https://www.airbnb.com/rooms/20549408   \n",
       "5             22244736            https://www.airbnb.com/rooms/22244736   \n",
       "6             26750044            https://www.airbnb.com/rooms/26750044   \n",
       "7             30562371            https://www.airbnb.com/rooms/30562371   \n",
       "8             33514317            https://www.airbnb.com/rooms/33514317   \n",
       "9             37647967            https://www.airbnb.com/rooms/37647967   \n",
       "10            40254200            https://www.airbnb.com/rooms/40254200   \n",
       "11            41806836            https://www.airbnb.com/rooms/41806836   \n",
       "12            42760690            https://www.airbnb.com/rooms/42760690   \n",
       "13            48275305            https://www.airbnb.com/rooms/48275305   \n",
       "14            23348490            https://www.airbnb.com/rooms/23348490   \n",
       "15            33672441            https://www.airbnb.com/rooms/33672441   \n",
       "16  748129484443939869  https://www.airbnb.com/rooms/748129484443939869   \n",
       "17            33551708            https://www.airbnb.com/rooms/33551708   \n",
       "18            53374987            https://www.airbnb.com/rooms/53374987   \n",
       "19  794225384535281896  https://www.airbnb.com/rooms/794225384535281896   \n",
       "20             5526647             https://www.airbnb.com/rooms/5526647   \n",
       "21            27357876            https://www.airbnb.com/rooms/27357876   \n",
       "22            47167043            https://www.airbnb.com/rooms/47167043   \n",
       "23            49283439            https://www.airbnb.com/rooms/49283439   \n",
       "24            54191245            https://www.airbnb.com/rooms/54191245   \n",
       "25  757102610729300532  https://www.airbnb.com/rooms/757102610729300532   \n",
       "26  769452711067062799  https://www.airbnb.com/rooms/769452711067062799   \n",
       "27  816482689478682174  https://www.airbnb.com/rooms/816482689478682174   \n",
       "28  832571285825030473  https://www.airbnb.com/rooms/832571285825030473   \n",
       "29  842110365786260956  https://www.airbnb.com/rooms/842110365786260956   \n",
       "30              413624              https://www.airbnb.com/rooms/413624   \n",
       "31              794813              https://www.airbnb.com/rooms/794813   \n",
       "32             3382210             https://www.airbnb.com/rooms/3382210   \n",
       "33             4304271             https://www.airbnb.com/rooms/4304271   \n",
       "34             5090037             https://www.airbnb.com/rooms/5090037   \n",
       "35             5577271             https://www.airbnb.com/rooms/5577271   \n",
       "36             8200770             https://www.airbnb.com/rooms/8200770   \n",
       "37            12309501            https://www.airbnb.com/rooms/12309501   \n",
       "38            13820529            https://www.airbnb.com/rooms/13820529   \n",
       "39            18007252            https://www.airbnb.com/rooms/18007252   \n",
       "40            18780943            https://www.airbnb.com/rooms/18780943   \n",
       "41            20946198            https://www.airbnb.com/rooms/20946198   \n",
       "42            23661650            https://www.airbnb.com/rooms/23661650   \n",
       "43            26343156            https://www.airbnb.com/rooms/26343156   \n",
       "44            28773125            https://www.airbnb.com/rooms/28773125   \n",
       "45            29050905            https://www.airbnb.com/rooms/29050905   \n",
       "46            31920960            https://www.airbnb.com/rooms/31920960   \n",
       "47            33746509            https://www.airbnb.com/rooms/33746509   \n",
       "48            35938752            https://www.airbnb.com/rooms/35938752   \n",
       "49            36803128            https://www.airbnb.com/rooms/36803128   \n",
       "\n",
       "                                                 name            host_name  \\\n",
       "0   Rental unit in Milano · 2 bedrooms · 2 beds · ...                Maria   \n",
       "1   Rental unit in Milano · 1 bedroom · 1 bed · 2 ...              Edoardo   \n",
       "2   Home in Milano · ★5.0 · 1 bedroom · 1 bed · 1 ...               I-Host   \n",
       "3   Rental unit in Milano · 1 bedroom · 2 beds · 2...           Alessandra   \n",
       "4   Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...             Fabrizio   \n",
       "5   Rental unit in Milano · 1 bedroom · 1 bed · 1....              Alessio   \n",
       "6   Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...           Alessandro   \n",
       "7   Rental unit in Milano · 1 bedroom · 1 bed · 1 ...              Rosanna   \n",
       "8   Rental unit in Milano · ★4.91 · 1 bedroom · 1 ...                Mario   \n",
       "9   Rental unit in Milano · 1 bedroom · 1 bed · 1....                 Luca   \n",
       "10  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...             Federica   \n",
       "11  Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...         Microaffitto   \n",
       "12  Rental unit in Milano · 2 bedrooms · 3 beds · ...               Mattia   \n",
       "13  Rental unit in Milano · ★4.75 · 1 bedroom · 1 ...  Bovisa Urban Garden   \n",
       "14  Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...             Federico   \n",
       "15  Rental unit in Milano · ★4.75 · 1 bedroom · 1 ...               Serena   \n",
       "16  Condo in Milano · 1 bedroom · 2 beds · 2 share...       Pietro Antonio   \n",
       "17  Rental unit in Milano · ★5.0 · 1 bedroom · 2 b...               Andrea   \n",
       "18  Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...                 Ciro   \n",
       "19  Rental unit in Milano · ★5.0 · 3 bedrooms · 3 ...       Pietro Antonio   \n",
       "20  Rental unit in Milano · 2 bedrooms · 1 bed · 1...                Marta   \n",
       "21  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...              Melania   \n",
       "22  Rental unit in Milano · 1 bedroom · 1 shared bath               Andrea   \n",
       "23       Condo in Milano · 1 bedroom · 1 bed · 1 bath              Claudio   \n",
       "24  Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...                Maura   \n",
       "25  Condo in Milano · 1 bedroom · 1 bed · 1 shared...     Emmanuele Ettore   \n",
       "26  Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...              Roberta   \n",
       "27  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...                Elena   \n",
       "28       Condo in Milano · 1 bedroom · 1 bed · 1 bath               Angela   \n",
       "29  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...                 Elia   \n",
       "30  Rental unit in Milan · ★5.0 · 1 bedroom · 1 be...                Lucia   \n",
       "31  Rental unit in Milan · 1 bedroom · 1 bed · 1 p...              Claudia   \n",
       "32          Loft in Milano · Studio · 2 beds · 1 bath               Andrea   \n",
       "33  Rental unit in Milan · 1 bedroom · 1 bed · 1 bath              Lorenzo   \n",
       "34  Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...                Magda   \n",
       "35  Condo in Milano · ★5.0 · 1 bedroom · 1 bed · 1...            Raffaella   \n",
       "36  Rental unit in Milano · ★4.67 · Studio · 2 bed...                 Mary   \n",
       "37  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...            Francesco   \n",
       "38  Rental unit in Milano · 1 bedroom · 2 beds · 2...               Giulia   \n",
       "39        Home in Milano · 1 bedroom · 1 bed · 1 bath                 Reem   \n",
       "40  Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...               Felipe   \n",
       "41  Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...                Irina   \n",
       "42  Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...           Alessandro   \n",
       "43  Rental unit in Milano · 2 bedrooms · 2 beds · ...              Giacomo   \n",
       "44  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...             Carolina   \n",
       "45  Rental unit in Milano · 1 bedroom · 1 bed · 2 ...            Francesca   \n",
       "46  Rental unit in Milano · 1 bedroom · 2 beds · 1...               Fulvio   \n",
       "47  Rental unit in Milano · 1 bedroom · 2 beds · 1...               Ilaria   \n",
       "48  Rental unit in Milano · ★5.0 · 1 bedroom · 1 b...             Federica   \n",
       "49  Rental unit in Milano · 1 bedroom · 1 bed · 1 ...                 Enzo   \n",
       "\n",
       "   review_scores_value price  \n",
       "0                  5.0     9  \n",
       "1                  5.0     5  \n",
       "2                  5.0     5  \n",
       "3                  5.0    49  \n",
       "4                  5.0    49  \n",
       "5                  5.0    49  \n",
       "6                  5.0    49  \n",
       "7                  5.0    49  \n",
       "8                  5.0    49  \n",
       "9                  5.0    49  \n",
       "10                 5.0    49  \n",
       "11                 5.0    49  \n",
       "12                 5.0    49  \n",
       "13                 5.0    49  \n",
       "14                 5.0    48  \n",
       "15                 5.0    48  \n",
       "16                 5.0    48  \n",
       "17                 5.0    47  \n",
       "18                 5.0    47  \n",
       "19                 5.0    47  \n",
       "20                 5.0    46  \n",
       "21                 5.0    46  \n",
       "22                 5.0    46  \n",
       "23                 5.0    46  \n",
       "24                 5.0    46  \n",
       "25                 5.0    46  \n",
       "26                 5.0    46  \n",
       "27                 5.0    46  \n",
       "28                 5.0    46  \n",
       "29                 5.0    46  \n",
       "30                 5.0    45  \n",
       "31                 5.0    45  \n",
       "32                 5.0    45  \n",
       "33                 5.0    45  \n",
       "34                 5.0    45  \n",
       "35                 5.0    45  \n",
       "36                 5.0    45  \n",
       "37                 5.0    45  \n",
       "38                 5.0    45  \n",
       "39                 5.0    45  \n",
       "40                 5.0    45  \n",
       "41                 5.0    45  \n",
       "42                 5.0    45  \n",
       "43                 5.0    45  \n",
       "44                 5.0    45  \n",
       "45                 5.0    45  \n",
       "46                 5.0    45  \n",
       "47                 5.0    45  \n",
       "48                 5.0    45  \n",
       "49                 5.0    45  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL query to find best rooms for a night under 50 Euros\n",
    "\n",
    "pd.read_sql('SELECT id, listing_url, name, host_name,review_scores_value , price FROM milan WHERE price < 50 ORDER BY review_scores_value DESC , price DESC LIMIT 50', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "vscode": {
   "interpreter": {
    "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}