workfile_preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4ba784f1-9277-4a86-9bd7-2c33a87ac7a2",
   "metadata": {},
   "source": [
    "# **Data cleaning and exploration**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce305a25-af0a-4031-a634-46d8a520d896",
   "metadata": {},
   "source": [
    "This notebook is used to clean the column of listings dataset and explore."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dbc6bcc-7502-4a17-9565-615ced7c312e",
   "metadata": {},
   "source": [
    "### 1. Import packages and data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e0a5adf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Loading packages\n",
    "import pandas as pd\n",
    "import glob\n",
    "\n",
    "# Other\n",
    "import warnings\n",
    "warnings.filterwarnings(action=\"ignore\")\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "# setting\n",
    "pd.set_option('display.max_columns', None)  # or 1000\n",
    "pd.set_option('display.max_rows', None)  # or 1000\n",
    "# pd.set_option('display.max_colwidth', -1)  # or 199 - warning\n",
    "pd.options.display.max_colwidth = 100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9713f71-16b4-43c1-b327-8fafc701e77c",
   "metadata": {},
   "source": [
    "### 2. check sample dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ce092bc8",
   "metadata": {
    "tags": []
   },
   "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>name</th>\n",
       "      <th>description</th>\n",
       "      <th>neighborhood_overview</th>\n",
       "      <th>picture_url</th>\n",
       "      <th>host_id</th>\n",
       "      <th>host_url</th>\n",
       "      <th>host_name</th>\n",
       "      <th>host_since</th>\n",
       "      <th>host_location</th>\n",
       "      <th>host_about</th>\n",
       "      <th>host_response_time</th>\n",
       "      <th>host_response_rate</th>\n",
       "      <th>host_acceptance_rate</th>\n",
       "      <th>host_is_superhost</th>\n",
       "      <th>host_thumbnail_url</th>\n",
       "      <th>host_picture_url</th>\n",
       "      <th>host_neighbourhood</th>\n",
       "      <th>host_listings_count</th>\n",
       "      <th>host_total_listings_count</th>\n",
       "      <th>host_verifications</th>\n",
       "      <th>host_has_profile_pic</th>\n",
       "      <th>host_identity_verified</th>\n",
       "      <th>neighbourhood</th>\n",
       "      <th>neighbourhood_cleansed</th>\n",
       "      <th>neighbourhood_group_cleansed</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>property_type</th>\n",
       "      <th>room_type</th>\n",
       "      <th>accommodates</th>\n",
       "      <th>bathrooms</th>\n",
       "      <th>bathrooms_text</th>\n",
       "      <th>bedrooms</th>\n",
       "      <th>beds</th>\n",
       "      <th>amenities</th>\n",
       "      <th>price</th>\n",
       "      <th>minimum_nights</th>\n",
       "      <th>maximum_nights</th>\n",
       "      <th>minimum_minimum_nights</th>\n",
       "      <th>maximum_minimum_nights</th>\n",
       "      <th>minimum_maximum_nights</th>\n",
       "      <th>maximum_maximum_nights</th>\n",
       "      <th>minimum_nights_avg_ntm</th>\n",
       "      <th>maximum_nights_avg_ntm</th>\n",
       "      <th>calendar_updated</th>\n",
       "      <th>has_availability</th>\n",
       "      <th>availability_30</th>\n",
       "      <th>availability_60</th>\n",
       "      <th>availability_90</th>\n",
       "      <th>availability_365</th>\n",
       "      <th>calendar_last_scraped</th>\n",
       "      <th>number_of_reviews</th>\n",
       "      <th>number_of_reviews_ltm</th>\n",
       "      <th>number_of_reviews_l30d</th>\n",
       "      <th>first_review</th>\n",
       "      <th>last_review</th>\n",
       "      <th>review_scores_rating</th>\n",
       "      <th>review_scores_accuracy</th>\n",
       "      <th>review_scores_cleanliness</th>\n",
       "      <th>review_scores_checkin</th>\n",
       "      <th>review_scores_communication</th>\n",
       "      <th>review_scores_location</th>\n",
       "      <th>review_scores_value</th>\n",
       "      <th>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>11156</td>\n",
       "      <td>https://www.airbnb.com/rooms/11156</td>\n",
       "      <td>20210110040047</td>\n",
       "      <td>2021-01-13</td>\n",
       "      <td>An Oasis in the City</td>\n",
       "      <td>Very central to the city which can be reached by an easy walk or by bus, with transport at the d...</td>\n",
       "      <td>It is very close to everything and everywhere, has  many trees along the streets and is in a bea...</td>\n",
       "      <td>https://a0.muscache.com/pictures/2797669/17895d03_original.jpg</td>\n",
       "      <td>40855</td>\n",
       "      <td>https://www.airbnb.com/users/show/40855</td>\n",
       "      <td>Colleen</td>\n",
       "      <td>2009-09-23</td>\n",
       "      <td>Potts Point, New South Wales, Australia</td>\n",
       "      <td>Recently retired, I've lived &amp; worked on 4 continents &amp; over the years have travelled thru about...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100%</td>\n",
       "      <td>f</td>\n",
       "      <td>https://a0.muscache.com/im/users/40855/profile_pic/1259121939/original.jpg?aki_policy=profile_small</td>\n",
       "      <td>https://a0.muscache.com/im/users/40855/profile_pic/1259121939/original.jpg?aki_policy=profile_x_...</td>\n",
       "      <td>Potts Point</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>['email', 'phone', 'reviews']</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>Potts Point, New South Wales, Australia</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-33.86934</td>\n",
       "      <td>151.22688</td>\n",
       "      <td>Private room in apartment</td>\n",
       "      <td>Private room</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1 shared bath</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[\"Single level home\", \"Cooking basics\", \"Dishes and silverware\", \"Patio or balcony\", \"Heating\", ...</td>\n",
       "      <td>$65.00</td>\n",
       "      <td>2</td>\n",
       "      <td>180</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>180</td>\n",
       "      <td>180</td>\n",
       "      <td>2.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>t</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-13</td>\n",
       "      <td>196</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-12-05</td>\n",
       "      <td>2020-03-13</td>\n",
       "      <td>92.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12351</td>\n",
       "      <td>https://www.airbnb.com/rooms/12351</td>\n",
       "      <td>20210110040047</td>\n",
       "      <td>2021-01-11</td>\n",
       "      <td>Sydney City &amp; Harbour at the door</td>\n",
       "      <td>Come stay with Vinh &amp; Stuart (Awarded as one of Australia's top hosts by Airbnb CEO Brian Chesky...</td>\n",
       "      <td>Pyrmont is an inner-city village of Sydney, only about 2kms from the Sydney CBD (Central Busines...</td>\n",
       "      <td>https://a0.muscache.com/pictures/763ad5c8-c951-43e0-b926-4a98c25c45e8.jpg</td>\n",
       "      <td>17061</td>\n",
       "      <td>https://www.airbnb.com/users/show/17061</td>\n",
       "      <td>Stuart</td>\n",
       "      <td>2009-05-14</td>\n",
       "      <td>Sydney, New South Wales, Australia</td>\n",
       "      <td>G'Day from Australia!\\r\\n\\r\\nHe's Vinh, and I'm Stuart...\\r\\n\\r\\nWe're big fans of AirBnB for ne...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>https://a0.muscache.com/im/users/17061/profile_pic/1425534501/original.jpg?aki_policy=profile_small</td>\n",
       "      <td>https://a0.muscache.com/im/users/17061/profile_pic/1425534501/original.jpg?aki_policy=profile_x_...</td>\n",
       "      <td>Pyrmont</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>['email', 'phone', 'manual_online', 'reviews', 'manual_offline', 'offline_government_id', 'gover...</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>Pyrmont, New South Wales, Australia</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-33.86515</td>\n",
       "      <td>151.19190</td>\n",
       "      <td>Private room in townhouse</td>\n",
       "      <td>Private room</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1 shared bath</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[\"Carbon monoxide alarm\", \"Coffee maker\", \"Private entrance\", \"Cooking basics\", \"Dishes and silv...</td>\n",
       "      <td>$14,315.00</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>t</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11</td>\n",
       "      <td>526</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-24</td>\n",
       "      <td>2019-09-22</td>\n",
       "      <td>95.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14250</td>\n",
       "      <td>https://www.airbnb.com/rooms/14250</td>\n",
       "      <td>20210110040047</td>\n",
       "      <td>2021-01-13</td>\n",
       "      <td>Manly Harbour House</td>\n",
       "      <td>Beautifully renovated, spacious and quiet, our 3 Bedroom, 3 Bathroom home is only a  10 minute w...</td>\n",
       "      <td>Balgowlah Heights is one of the most prestigious areas on the Northern Beaches.  Filled with sea...</td>\n",
       "      <td>https://a0.muscache.com/pictures/56935671/fdb8c0bf_original.jpg</td>\n",
       "      <td>55948</td>\n",
       "      <td>https://www.airbnb.com/users/show/55948</td>\n",
       "      <td>Heidi</td>\n",
       "      <td>2009-11-20</td>\n",
       "      <td>Sydney, New South Wales, Australia</td>\n",
       "      <td>I am a Canadian who has made Australia her home for over ten years. I appreciate a good place to...</td>\n",
       "      <td>within an hour</td>\n",
       "      <td>100%</td>\n",
       "      <td>73%</td>\n",
       "      <td>t</td>\n",
       "      <td>https://a0.muscache.com/im/users/55948/profile_pic/1327370146/original.jpg?aki_policy=profile_small</td>\n",
       "      <td>https://a0.muscache.com/im/users/55948/profile_pic/1327370146/original.jpg?aki_policy=profile_x_...</td>\n",
       "      <td>Balgowlah</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>['email', 'phone', 'reviews', 'jumio', 'offline_government_id', 'government_id']</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>Balgowlah, New South Wales, Australia</td>\n",
       "      <td>Manly</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-33.80093</td>\n",
       "      <td>151.26172</td>\n",
       "      <td>Entire house</td>\n",
       "      <td>Entire home/apt</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3 baths</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>[\"Bathtub\", \"Cooking basics\", \"Dishes and silverware\", \"Patio or balcony\", \"Heating\", \"Smoke ala...</td>\n",
       "      <td>$470.00</td>\n",
       "      <td>5</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>5.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>t</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-13</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>90.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id                         listing_url       scrape_id last_scraped  \\\n",
       "0  11156  https://www.airbnb.com/rooms/11156  20210110040047   2021-01-13   \n",
       "1  12351  https://www.airbnb.com/rooms/12351  20210110040047   2021-01-11   \n",
       "2  14250  https://www.airbnb.com/rooms/14250  20210110040047   2021-01-13   \n",
       "\n",
       "                                name  \\\n",
       "0               An Oasis in the City   \n",
       "1  Sydney City & Harbour at the door   \n",
       "2                Manly Harbour House   \n",
       "\n",
       "                                                                                           description  \\\n",
       "0  Very central to the city which can be reached by an easy walk or by bus, with transport at the d...   \n",
       "1  Come stay with Vinh & Stuart (Awarded as one of Australia's top hosts by Airbnb CEO Brian Chesky...   \n",
       "2  Beautifully renovated, spacious and quiet, our 3 Bedroom, 3 Bathroom home is only a  10 minute w...   \n",
       "\n",
       "                                                                                 neighborhood_overview  \\\n",
       "0  It is very close to everything and everywhere, has  many trees along the streets and is in a bea...   \n",
       "1  Pyrmont is an inner-city village of Sydney, only about 2kms from the Sydney CBD (Central Busines...   \n",
       "2  Balgowlah Heights is one of the most prestigious areas on the Northern Beaches.  Filled with sea...   \n",
       "\n",
       "                                                                 picture_url  \\\n",
       "0             https://a0.muscache.com/pictures/2797669/17895d03_original.jpg   \n",
       "1  https://a0.muscache.com/pictures/763ad5c8-c951-43e0-b926-4a98c25c45e8.jpg   \n",
       "2            https://a0.muscache.com/pictures/56935671/fdb8c0bf_original.jpg   \n",
       "\n",
       "   host_id                                 host_url host_name  host_since  \\\n",
       "0    40855  https://www.airbnb.com/users/show/40855   Colleen  2009-09-23   \n",
       "1    17061  https://www.airbnb.com/users/show/17061    Stuart  2009-05-14   \n",
       "2    55948  https://www.airbnb.com/users/show/55948     Heidi  2009-11-20   \n",
       "\n",
       "                             host_location  \\\n",
       "0  Potts Point, New South Wales, Australia   \n",
       "1       Sydney, New South Wales, Australia   \n",
       "2       Sydney, New South Wales, Australia   \n",
       "\n",
       "                                                                                            host_about  \\\n",
       "0  Recently retired, I've lived & worked on 4 continents & over the years have travelled thru about...   \n",
       "1  G'Day from Australia!\\r\\n\\r\\nHe's Vinh, and I'm Stuart...\\r\\n\\r\\nWe're big fans of AirBnB for ne...   \n",
       "2  I am a Canadian who has made Australia her home for over ten years. I appreciate a good place to...   \n",
       "\n",
       "  host_response_time host_response_rate host_acceptance_rate  \\\n",
       "0                NaN                NaN                 100%   \n",
       "1                NaN                NaN                  NaN   \n",
       "2     within an hour               100%                  73%   \n",
       "\n",
       "  host_is_superhost  \\\n",
       "0                 f   \n",
       "1                 f   \n",
       "2                 t   \n",
       "\n",
       "                                                                                    host_thumbnail_url  \\\n",
       "0  https://a0.muscache.com/im/users/40855/profile_pic/1259121939/original.jpg?aki_policy=profile_small   \n",
       "1  https://a0.muscache.com/im/users/17061/profile_pic/1425534501/original.jpg?aki_policy=profile_small   \n",
       "2  https://a0.muscache.com/im/users/55948/profile_pic/1327370146/original.jpg?aki_policy=profile_small   \n",
       "\n",
       "                                                                                      host_picture_url  \\\n",
       "0  https://a0.muscache.com/im/users/40855/profile_pic/1259121939/original.jpg?aki_policy=profile_x_...   \n",
       "1  https://a0.muscache.com/im/users/17061/profile_pic/1425534501/original.jpg?aki_policy=profile_x_...   \n",
       "2  https://a0.muscache.com/im/users/55948/profile_pic/1327370146/original.jpg?aki_policy=profile_x_...   \n",
       "\n",
       "  host_neighbourhood  host_listings_count  host_total_listings_count  \\\n",
       "0        Potts Point                  1.0                        1.0   \n",
       "1            Pyrmont                  2.0                        2.0   \n",
       "2          Balgowlah                  2.0                        2.0   \n",
       "\n",
       "                                                                                    host_verifications  \\\n",
       "0                                                                        ['email', 'phone', 'reviews']   \n",
       "1  ['email', 'phone', 'manual_online', 'reviews', 'manual_offline', 'offline_government_id', 'gover...   \n",
       "2                     ['email', 'phone', 'reviews', 'jumio', 'offline_government_id', 'government_id']   \n",
       "\n",
       "  host_has_profile_pic host_identity_verified  \\\n",
       "0                    t                      f   \n",
       "1                    t                      t   \n",
       "2                    t                      t   \n",
       "\n",
       "                             neighbourhood neighbourhood_cleansed  \\\n",
       "0  Potts Point, New South Wales, Australia                 Sydney   \n",
       "1      Pyrmont, New South Wales, Australia                 Sydney   \n",
       "2    Balgowlah, New South Wales, Australia                  Manly   \n",
       "\n",
       "   neighbourhood_group_cleansed  latitude  longitude  \\\n",
       "0                           NaN -33.86934  151.22688   \n",
       "1                           NaN -33.86515  151.19190   \n",
       "2                           NaN -33.80093  151.26172   \n",
       "\n",
       "               property_type        room_type  accommodates  bathrooms  \\\n",
       "0  Private room in apartment     Private room             1        NaN   \n",
       "1  Private room in townhouse     Private room             2        NaN   \n",
       "2               Entire house  Entire home/apt             6        NaN   \n",
       "\n",
       "  bathrooms_text  bedrooms  beds  \\\n",
       "0  1 shared bath       1.0   0.0   \n",
       "1  1 shared bath       1.0   1.0   \n",
       "2        3 baths       3.0   3.0   \n",
       "\n",
       "                                                                                             amenities  \\\n",
       "0  [\"Single level home\", \"Cooking basics\", \"Dishes and silverware\", \"Patio or balcony\", \"Heating\", ...   \n",
       "1  [\"Carbon monoxide alarm\", \"Coffee maker\", \"Private entrance\", \"Cooking basics\", \"Dishes and silv...   \n",
       "2  [\"Bathtub\", \"Cooking basics\", \"Dishes and silverware\", \"Patio or balcony\", \"Heating\", \"Smoke ala...   \n",
       "\n",
       "        price  minimum_nights  maximum_nights  minimum_minimum_nights  \\\n",
       "0      $65.00               2             180                       2   \n",
       "1  $14,315.00               2               7                       2   \n",
       "2     $470.00               5              22                       5   \n",
       "\n",
       "   maximum_minimum_nights  minimum_maximum_nights  maximum_maximum_nights  \\\n",
       "0                       2                     180                     180   \n",
       "1                       2                       7                       7   \n",
       "2                       5                      22                      22   \n",
       "\n",
       "   minimum_nights_avg_ntm  maximum_nights_avg_ntm  calendar_updated  \\\n",
       "0                     2.0                   180.0               NaN   \n",
       "1                     2.0                     7.0               NaN   \n",
       "2                     5.0                    22.0               NaN   \n",
       "\n",
       "  has_availability  availability_30  availability_60  availability_90  \\\n",
       "0                t                1                1                1   \n",
       "1                t                0                0                0   \n",
       "2                t                1                1                1   \n",
       "\n",
       "   availability_365 calendar_last_scraped  number_of_reviews  \\\n",
       "0                 1            2021-01-13                196   \n",
       "1                 0            2021-01-11                526   \n",
       "2                 1            2021-01-13                  2   \n",
       "\n",
       "   number_of_reviews_ltm  number_of_reviews_l30d first_review last_review  \\\n",
       "0                      5                       0   2009-12-05  2020-03-13   \n",
       "1                      0                       0   2010-07-24  2019-09-22   \n",
       "2                      0                       0   2016-01-02  2019-01-02   \n",
       "\n",
       "   review_scores_rating  review_scores_accuracy  review_scores_cleanliness  \\\n",
       "0                  92.0                    10.0                        9.0   \n",
       "1                  95.0                    10.0                       10.0   \n",
       "2                  90.0                     8.0                        8.0   \n",
       "\n",
       "   review_scores_checkin  review_scores_communication  review_scores_location  \\\n",
       "0                   10.0                         10.0                    10.0   \n",
       "1                   10.0                         10.0                    10.0   \n",
       "2                    9.0                          8.0                     9.0   \n",
       "\n",
       "   review_scores_value  license instant_bookable  \\\n",
       "0                 10.0      NaN                f   \n",
       "1                 10.0      NaN                f   \n",
       "2                  8.0      NaN                f   \n",
       "\n",
       "   calculated_host_listings_count  \\\n",
       "0                               1   \n",
       "1                               2   \n",
       "2                               2   \n",
       "\n",
       "   calculated_host_listings_count_entire_homes  \\\n",
       "0                                            0   \n",
       "1                                            0   \n",
       "2                                            2   \n",
       "\n",
       "   calculated_host_listings_count_private_rooms  \\\n",
       "0                                             1   \n",
       "1                                             2   \n",
       "2                                             0   \n",
       "\n",
       "   calculated_host_listings_count_shared_rooms  reviews_per_month  \n",
       "0                                            0               1.45  \n",
       "1                                            0               4.13  \n",
       "2                                            0               0.03  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Random check for csv files\n",
    "df_sample = pd.read_csv('listings/01_2021.csv').rename(columns=str.lower)\n",
    "df_sample.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a41aaf39-d32e-4bff-bd31-51d0928c49b2",
   "metadata": {},
   "source": [
    "### 2. check the datasets in listings folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d53ab611",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['listings/03_2021.csv',\n",
       " 'listings/02_2021.csv',\n",
       " 'listings/08_2020.csv',\n",
       " 'listings/09_2020.csv',\n",
       " 'listings/04_2021.csv',\n",
       " 'listings/05_2020.csv',\n",
       " 'listings/12_2020.csv',\n",
       " 'listings/01_2021.csv',\n",
       " 'listings/11_2020.csv',\n",
       " 'listings/10_2020.csv',\n",
       " 'listings/06_2020.csv',\n",
       " 'listings/07_2020.csv']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "glob.glob(\"listings/*.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "69e060d7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of listings file: 12\n",
      "listings/03_2021.csv :  (33229, 74)\n",
      "listings/02_2021.csv :  (33630, 74)\n",
      "listings/08_2020.csv :  (31391, 74)\n",
      "listings/09_2020.csv :  (34829, 74)\n",
      "listings/04_2021.csv :  (32679, 74)\n",
      "listings/05_2020.csv :  (37562, 106)\n",
      "listings/12_2020.csv :  (33871, 74)\n",
      "listings/01_2021.csv :  (33902, 74)\n",
      "listings/11_2020.csv :  (33795, 74)\n",
      "listings/10_2020.csv :  (34276, 74)\n",
      "listings/06_2020.csv :  (36901, 106)\n",
      "listings/07_2020.csv :  (36057, 102)\n"
     ]
    }
   ],
   "source": [
    "# Check for csv dimensions\n",
    "files = glob.glob(\"listings/*.csv\")\n",
    "print(\"Number of listings file:\", len(files))\n",
    "for file in files:\n",
    "    print(file, \": \", pd.read_csv(file).shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "080213ec",
   "metadata": {},
   "source": [
    "**Note:**\n",
    "1. DtypeWarning here, need to be addressed in later part\n",
    "2. No. of columns are different, need to find out all the common columns for ELT pipeline\n",
    "\n",
    "There are mixed types in some columns in below files:    \n",
    "* listings/05_2020.csv : Columns (43,61,62,94) have mixed types.\n",
    "* listings/06_2020.csv :  Columns (43,61,62,94) have mixed types\n",
    "* listings/07_2020.csv :  (36057, 102) Columns (49,58,59,89) have mixed types."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a07d3172",
   "metadata": {},
   "source": [
    "### 4. Find common columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3f02626c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of common columns: 67\n",
      "{'host_acceptance_rate', 'room_type', 'calculated_host_listings_count_shared_rooms', 'property_type', 'review_scores_accuracy', 'host_picture_url', 'host_neighbourhood', 'listing_url', 'host_identity_verified', 'reviews_per_month', 'availability_90', 'has_availability', 'price', 'accommodates', 'calculated_host_listings_count', 'neighbourhood', 'scrape_id', 'minimum_minimum_nights', 'host_total_listings_count', 'description', 'host_about', 'calendar_updated', 'host_is_superhost', 'neighbourhood_group_cleansed', 'instant_bookable', 'host_name', 'host_listings_count', 'availability_365', 'host_thumbnail_url', 'host_has_profile_pic', 'maximum_nights_avg_ntm', 'number_of_reviews_ltm', 'review_scores_communication', 'license', 'host_since', 'latitude', 'review_scores_value', 'review_scores_cleanliness', 'calculated_host_listings_count_private_rooms', 'name', 'maximum_minimum_nights', 'number_of_reviews', 'id', 'host_url', 'availability_60', 'neighborhood_overview', 'review_scores_location', 'calculated_host_listings_count_entire_homes', 'host_verifications', 'availability_30', 'review_scores_rating', 'last_review', 'host_location', 'longitude', 'calendar_last_scraped', 'picture_url', 'host_response_time', 'review_scores_checkin', 'bathrooms', 'first_review', 'minimum_nights_avg_ntm', 'minimum_maximum_nights', 'last_scraped', 'host_response_rate', 'maximum_maximum_nights', 'neighbourhood_cleansed', 'host_id'}\n"
     ]
    }
   ],
   "source": [
    "cols = []\n",
    "\n",
    "for file in files:\n",
    "    current_col_list = pd.read_csv(file).columns.tolist()   \n",
    "    cols.append(current_col_list)\n",
    "    \n",
    "common_cols = set(cols[0]).intersection(*cols)\n",
    "print(\"Number of common columns:\", len(common_cols))\n",
    "print(common_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "52e70acb-e3e3-4bbe-aa8c-4b77defef580",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "listings/03_2021.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/02_2021.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/08_2020.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/09_2020.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/04_2021.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/05_2020.csv\n",
      "['summary', 'space', 'experiences_offered', 'notes', 'transit', 'access', 'interaction', 'house_rules', 'thumbnail_url', 'medium_url', 'xl_picture_url', 'street', 'city', 'state', 'zipcode', 'market', 'smart_location', 'country_code', 'country', 'is_location_exact', 'bedrooms', 'beds', 'bed_type', 'amenities', 'square_feet', 'weekly_price', 'monthly_price', 'security_deposit', 'cleaning_fee', 'guests_included', 'extra_people', 'minimum_nights', 'maximum_nights', 'requires_license', 'jurisdiction_names', 'is_business_travel_ready', 'cancellation_policy', 'require_guest_profile_picture', 'require_guest_phone_verification']\n",
      "\n",
      "listings/12_2020.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/01_2021.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/11_2020.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/10_2020.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/06_2020.csv\n",
      "['summary', 'space', 'experiences_offered', 'notes', 'transit', 'access', 'interaction', 'house_rules', 'thumbnail_url', 'medium_url', 'xl_picture_url', 'street', 'city', 'state', 'zipcode', 'market', 'smart_location', 'country_code', 'country', 'is_location_exact', 'bedrooms', 'beds', 'bed_type', 'amenities', 'square_feet', 'weekly_price', 'monthly_price', 'security_deposit', 'cleaning_fee', 'guests_included', 'extra_people', 'minimum_nights', 'maximum_nights', 'requires_license', 'jurisdiction_names', 'is_business_travel_ready', 'cancellation_policy', 'require_guest_profile_picture', 'require_guest_phone_verification']\n",
      "\n",
      "listings/07_2020.csv\n",
      "['summary', 'space', 'experiences_offered', 'notes', 'transit', 'access', 'interaction', 'house_rules', 'thumbnail_url', 'medium_url', 'xl_picture_url', 'street', 'city', 'state', 'zipcode', 'market', 'smart_location', 'country_code', 'country', 'is_location_exact', 'bed_type', 'square_feet', 'weekly_price', 'monthly_price', 'security_deposit', 'cleaning_fee', 'guests_included', 'extra_people', 'number_of_reviews_l30d', 'requires_license', 'jurisdiction_names', 'is_business_travel_ready', 'cancellation_policy', 'require_guest_profile_picture', 'require_guest_phone_verification']\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for file in files:\n",
    "    print(file)\n",
    "    col = pd.read_csv(file).columns.tolist()\n",
    "    print([x for x in col if x not in common_cols])\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6795d097",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "listings/03_2021.csv\n",
      "[]\n",
      "\n",
      "listings/02_2021.csv\n",
      "[]\n",
      "\n",
      "listings/08_2020.csv\n",
      "[]\n",
      "\n",
      "listings/09_2020.csv\n",
      "[]\n",
      "\n",
      "listings/04_2021.csv\n",
      "[]\n",
      "\n",
      "listings/05_2020.csv\n",
      "['bathrooms_text', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/12_2020.csv\n",
      "[]\n",
      "\n",
      "listings/01_2021.csv\n",
      "[]\n",
      "\n",
      "listings/11_2020.csv\n",
      "[]\n",
      "\n",
      "listings/10_2020.csv\n",
      "[]\n",
      "\n",
      "listings/06_2020.csv\n",
      "['bathrooms_text', 'number_of_reviews_l30d']\n",
      "\n",
      "listings/07_2020.csv\n",
      "['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights']\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# for an airbnb dataset, it is reasonable to include:\n",
    "more_cols = ['bathrooms_text', 'bedrooms', 'beds', 'amenities', 'minimum_nights', 'maximum_nights', 'number_of_reviews_l30d']\n",
    "          \n",
    "for file in files:\n",
    "    print(file)\n",
    "    col = pd.read_csv(file).columns.tolist()\n",
    "    print([x for x in more_cols if x not in col])\n",
    "    print()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28e1ac73",
   "metadata": {},
   "source": [
    "csv for May, June and July need to fill these columns with null in snowflake, not giving up these columns because of 1 file missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ae3e235e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "common_cols = list(common_cols)\n",
    "cols = common_cols + more_cols\n",
    "len(cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "180fd6ac-4a6f-44a2-8336-014450a66754",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_cols = ['id', 'listing_url', 'scrape_id', 'last_scraped', 'name', 'description', 'neighborhood_overview', 'picture_url', \n",
    "            'host_id', 'host_url', 'host_name', 'host_since', 'host_location', 'host_about', 'host_response_time', \n",
    "            'host_response_rate', 'host_acceptance_rate', 'host_is_superhost', 'host_thumbnail_url', 'host_picture_url', \n",
    "            'host_neighbourhood', 'host_listings_count', 'host_total_listings_count', 'host_verifications', 'host_has_profile_pic', \n",
    "            'host_identity_verified', 'neighbourhood', 'neighbourhood_cleansed', 'neighbourhood_group_cleansed', 'latitude', \n",
    "            'longitude', 'property_type', 'room_type', 'accommodates', 'bathrooms', 'bathrooms_text', 'bedrooms', 'beds', \n",
    "            'amenities', 'price', 'minimum_nights', 'maximum_nights', 'minimum_minimum_nights', 'maximum_minimum_nights', \n",
    "            'minimum_maximum_nights', 'maximum_maximum_nights', 'minimum_nights_avg_ntm', 'maximum_nights_avg_ntm', \n",
    "            'calendar_updated', 'has_availability', 'availability_30', 'availability_60', 'availability_90', 'availability_365', \n",
    "            'calendar_last_scraped', 'number_of_reviews', 'number_of_reviews_ltm', 'number_of_reviews_l30d', 'first_review', \n",
    "            'last_review', 'review_scores_rating', 'review_scores_accuracy', 'review_scores_cleanliness', 'review_scores_checkin', \n",
    "            'review_scores_communication', 'review_scores_location', 'review_scores_value', 'license', 'instant_bookable', \n",
    "            'calculated_host_listings_count', 'calculated_host_listings_count_entire_homes', 'calculated_host_listings_count_private_rooms', \n",
    "            'calculated_host_listings_count_shared_rooms', 'reviews_per_month']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "744c7229",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#check for these columns\n",
    "files = glob.glob(\"listings/*.csv\")\n",
    "for file in files:\n",
    "    file_name = file.split(\".\")[0]\n",
    "    save_file = file_name + \"_new.csv\"\n",
    "    \n",
    "    # read data\n",
    "    df = pd.read_csv(file)\n",
    "    \n",
    "    # change 'last_scraped' to datetime format\n",
    "    df['last_scraped'] = pd.to_datetime(df['last_scraped'])\n",
    "    \n",
    "    # fill na to csvs\n",
    "    df_col = df.columns.tolist()\n",
    "    for i in new_cols:\n",
    "        if i not in df_col:\n",
    "            df[i] = ''\n",
    "    df_new = df[new_cols]\n",
    "    df_new.to_csv(save_file, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d3a9fe61-f86b-4528-8167-44c034282bd1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of listings file: 12\n",
      "listings/05_2020_new.csv :  (37562, 74)\n",
      "listings/10_2020_new.csv :  (34277, 74)\n",
      "listings/03_2021_new.csv :  (33229, 74)\n",
      "listings/04_2021_new.csv :  (32679, 74)\n",
      "listings/11_2020_new.csv :  (33796, 74)\n",
      "listings/02_2021_new.csv :  (33630, 74)\n",
      "listings/09_2020_new.csv :  (34830, 74)\n",
      "listings/08_2020_new.csv :  (31391, 74)\n",
      "listings/06_2020_new.csv :  (36901, 74)\n",
      "listings/07_2020_new.csv :  (36057, 74)\n",
      "listings/12_2020_new.csv :  (33871, 74)\n",
      "listings/01_2021_new.csv :  (33902, 74)\n"
     ]
    }
   ],
   "source": [
    "# Check for csv dimensions\n",
    "files = glob.glob(\"listings/*new.csv\")\n",
    "print(\"Number of listings file:\", len(files))\n",
    "for file in files:\n",
    "    print(file, \": \", pd.read_csv(file).shape)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}