pyspark/Spark.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cf839f19",
   "metadata": {},
   "source": [
    "## Preliminary - Install and Import Library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "beccc717",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "0bd85316",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install pyspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "a4d425a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "d225215f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import *\n",
    "from pyspark.sql import functions as func\n",
    "from pyspark.sql.functions import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "1bfcc2d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c86a3c4",
   "metadata": {},
   "source": [
    "## Spark - 1 Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "fe145073",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "load_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "127739cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "listings = spark.read.format(\"com.databricks.spark.csv\") \\\n",
    "                .option('header',True) \\\n",
    "                .option(\"inferSchema\", \"true\")\\\n",
    "                .option(\"sep\", \",\")\\\n",
    "                .option('multiLine', True) \\\n",
    "                .option(\"escape\",'\\n') \\\n",
    "                .option(\"wholeFile\",\"True\") \\\n",
    "                .option(\"quoteAll\",\"true\") \\\n",
    "                .option(\"escape\",'\"') \\\n",
    "                .load('hdfs://localhost:9000/bdm/SINGAPORE/listings') \\\n",
    "                .withColumnRenamed(\"id\", \"listing_id\")\\\n",
    "                .withColumn(\"price\", expr(\"regexp_replace(price, '[$,]', '')\").cast('float')) \\\n",
    "                .withColumn(\"neighborhood_overview\", expr(\"regexp_replace(neighborhood_overview, '(<br />)|(<br/>)|(<b>)|(</b>)|(\\n)','')\")) \\\n",
    "                .withColumn(\"host_about\", expr(\"regexp_replace(host_about, '(<br />)|(<br/>)|(<b>)|(</b>)|(\\n)','')\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "5d181f9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+--------------+------------+--------------------+--------------------+---------------------+--------------------+-------+--------------------+---------+----------+--------------------+--------------------+------------------+------------------+--------------------+-----------------+--------------------+--------------------+------------------+-------------------+-------------------------+--------------------+--------------------+----------------------+--------------------+----------------------+----------------------------+--------+---------+--------------------+------------+------------+---------+----------------+--------+----+--------------------+-----+--------------+--------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------+----------------+---------------+---------------+---------------+----------------+---------------------+-----------------+---------------------+----------------------+------------+-----------+--------------------+----------------------+-------------------------+---------------------+---------------------------+----------------------+-------------------+-------+----------------+------------------------------+-------------------------------------------+--------------------------------------------+-------------------------------------------+-----------------+\n",
      "|listing_id|         listing_url|     scrape_id|last_scraped|                name|         description|neighborhood_overview|         picture_url|host_id|            host_url|host_name|host_since|       host_location|          host_about|host_response_time|host_response_rate|host_acceptance_rate|host_is_superhost|  host_thumbnail_url|    host_picture_url|host_neighbourhood|host_listings_count|host_total_listings_count|  host_verifications|host_has_profile_pic|host_identity_verified|       neighbourhood|neighbourhood_cleansed|neighbourhood_group_cleansed|latitude|longitude|       property_type|   room_type|accommodates|bathrooms|  bathrooms_text|bedrooms|beds|           amenities|price|minimum_nights|maximum_nights|minimum_minimum_nights|maximum_minimum_nights|minimum_maximum_nights|maximum_maximum_nights|minimum_nights_avg_ntm|maximum_nights_avg_ntm|calendar_updated|has_availability|availability_30|availability_60|availability_90|availability_365|calendar_last_scraped|number_of_reviews|number_of_reviews_ltm|number_of_reviews_l30d|first_review|last_review|review_scores_rating|review_scores_accuracy|review_scores_cleanliness|review_scores_checkin|review_scores_communication|review_scores_location|review_scores_value|license|instant_bookable|calculated_host_listings_count|calculated_host_listings_count_entire_homes|calculated_host_listings_count_private_rooms|calculated_host_listings_count_shared_rooms|reviews_per_month|\n",
      "+----------+--------------------+--------------+------------+--------------------+--------------------+---------------------+--------------------+-------+--------------------+---------+----------+--------------------+--------------------+------------------+------------------+--------------------+-----------------+--------------------+--------------------+------------------+-------------------+-------------------------+--------------------+--------------------+----------------------+--------------------+----------------------+----------------------------+--------+---------+--------------------+------------+------------+---------+----------------+--------+----+--------------------+-----+--------------+--------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------+----------------+---------------+---------------+---------------+----------------+---------------------+-----------------+---------------------+----------------------+------------+-----------+--------------------+----------------------+-------------------------+---------------------+---------------------------+----------------------+-------------------+-------+----------------+------------------------------+-------------------------------------------+--------------------------------------------+-------------------------------------------+-----------------+\n",
      "|     50646|https://www.airbn...|20211028055647|  2021-10-28|Pleasant Room alo...|Fully furnished b...| The serenity & qu...|https://a0.muscac...| 227796|https://www.airbn...|  Sujatha|2010-09-08|Singapore, Singapore|I am a working pr...|               N/A|               N/A|                 N/A|                f|https://a0.muscac...|https://a0.muscac...|       Bukit Timah|                  1|                        1|['email', 'phone'...|                   t|                     t|Singapore, Singapore|           Bukit Timah|              Central Region| 1.33432|103.78521|Private room in r...|Private room|           2|     null|          1 bath|       1|   1|[\"Elevator\", \"Lon...| 80.0|            90|           730|                    90|                    90|                   730|                   730|                  90.0|                 730.0|            null|               t|             30|             60|             90|             365|           2021-10-28|               18|                    0|                     0|  2014-10-14| 2014-07-08|                4.56|                  4.72|                     4.78|                 4.78|                       4.94|                  4.72|                4.5|   null|               f|                             1|                                          0|                                           1|                                          0|             0.21|\n",
      "|     71609|https://www.airbn...|20211028055647|  2021-10-28|Ensuite Room (Roo...|For 3 rooms.Book ...|                 null|https://a0.muscac...| 367042|https://www.airbn...|  Belinda|2011-01-29|           Singapore|Hi My name is Bel...|               N/A|               N/A|                 N/A|                f|https://a0.muscac...|https://a0.muscac...|          Tampines|                  8|                        8|['email', 'phone'...|                   t|                     t|                null|              Tampines|                 East Region| 1.34537|103.95887|Private room in v...|Private room|           6|     null|  1 private bath|       2|   3|[\"TV\", \"Hot tub\",...|177.0|            90|          1125|                    90|                    90|                  1125|                  1125|                  90.0|                1125.0|            null|               t|             30|             60|             90|             365|           2021-10-28|               20|                    0|                     0|  2015-10-05| 2019-12-28|                4.44|                  4.37|                      4.0|                 4.63|                       4.78|                  4.26|               4.32|   null|               t|                             4|                                          0|                                           4|                                          0|             0.27|\n",
      "|     71896|https://www.airbn...|20211028055647|  2021-10-28|B&B  Room 1 near ...|<b>The space</b><...|                 null|https://a0.muscac...| 367042|https://www.airbn...|  Belinda|2011-01-29|           Singapore|Hi My name is Bel...|               N/A|               N/A|                 N/A|                f|https://a0.muscac...|https://a0.muscac...|          Tampines|                  8|                        8|['email', 'phone'...|                   t|                     t|                null|              Tampines|                 East Region| 1.34754|103.95958|Private room in r...|Private room|           3|     null|Shared half-bath|       1|   1|[\"TV\", \"Hot tub\",...| 81.0|            90|          1125|                    90|                    90|                  1125|                  1125|                  90.0|                1125.0|            null|               t|             30|             60|             90|             365|           2021-10-28|               24|                    0|                     0|  2015-08-14| 2014-12-10|                4.16|                  4.22|                     4.09|                 4.43|                       4.43|                  4.17|               4.04|   null|               t|                             4|                                          0|                                           4|                                          0|             0.32|\n",
      "|     71903|https://www.airbn...|20211028055647|  2021-10-28|Room 2-near Airpo...|Like your own hom...| Quiet and view of...|https://a0.muscac...| 367042|https://www.airbn...|  Belinda|2011-01-29|           Singapore|Hi My name is Bel...|               N/A|               N/A|                 N/A|                f|https://a0.muscac...|https://a0.muscac...|          Tampines|                  8|                        8|['email', 'phone'...|                   t|                     t|Singapore, Singapore|              Tampines|                 East Region| 1.34531|  103.961|Private room in r...|Private room|           3|     null|Shared half-bath|       1|   2|[\"Free street par...| 81.0|            90|          1125|                    90|                    90|                  1125|                  1125|                  90.0|                1125.0|            null|               t|             30|             60|             90|             365|           2021-10-28|               48|                    0|                     0|  2015-08-16| 2019-10-21|                4.42|                   4.4|                     4.53|                 4.64|                       4.64|                  4.51|               4.38|   null|               f|                             4|                                          0|                                           4|                                          0|             0.64|\n",
      "|    275343|https://www.airbn...|20211028055647|  2021-10-28|Conveniently loca...|Awesome location ...|                 null|https://a0.muscac...|1439258|https://www.airbn...|    Joyce|2011-11-24|Singapore, Singapore|K2 Guesthouse is ...|    within an hour|               95%|                 94%|                f|https://a0.muscac...|https://a0.muscac...|       Bukit Merah|                 50|                       50|['email', 'phone'...|                   t|                     t|                null|           Bukit Merah|              Central Region| 1.29015|103.80814|Private room in r...|Private room|           1|     null|            null|       1|   1|[\"Elevator\", \"Lon...| 52.0|            14|           999|                    14|                    14|                   999|                   999|                  14.0|                 999.0|            null|               t|              8|             38|             68|             343|           2021-10-28|               20|                    0|                     0|  2013-04-20| 2019-10-17|                4.39|                  4.12|                     4.24|                 4.53|                       4.47|                  4.53|               4.59|   null|               f|                            50|                                          1|                                          49|                                          0|             0.19|\n",
      "+----------+--------------------+--------------+------------+--------------------+--------------------+---------------------+--------------------+-------+--------------------+---------+----------+--------------------+--------------------+------------------+------------------+--------------------+-----------------+--------------------+--------------------+------------------+-------------------+-------------------------+--------------------+--------------------+----------------------+--------------------+----------------------+----------------------------+--------+---------+--------------------+------------+------------+---------+----------------+--------+----+--------------------+-----+--------------+--------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------+----------------+---------------+---------------+---------------+----------------+---------------------+-----------------+---------------------+----------------------+------------+-----------+--------------------+----------------------+-------------------------+---------------------+---------------------------+----------------------+-------------------+-------+----------------+------------------------------+-------------------------------------------+--------------------------------------------+-------------------------------------------+-----------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "listings.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "1e969ea8",
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews = spark.read.format(\"com.databricks.spark.csv\") \\\n",
    "                .option('header',True) \\\n",
    "                .option(\"inferSchema\", \"true\")\\\n",
    "                .option(\"sep\", \",\")\\\n",
    "                .option('multiLine', True) \\\n",
    "                .option(\"escape\",'\\n') \\\n",
    "                .option(\"wholeFile\",\"True\") \\\n",
    "                .option(\"quoteAll\",\"true\") \\\n",
    "                .option(\"escape\",'\"') \\\n",
    "                .load('hdfs://localhost:9000/bdm/SINGAPORE/reviews') \\\n",
    "                .withColumn(\"comments\", expr(\"regexp_replace(comments, '(<br />)|(<br/>)|(<b>)|(</b>)|(\\n)','')\")) \\\n",
    "                .withColumn(\"date\", to_date(col(\"date\"), \"yyyy-MM-dd\")) \\\n",
    "                .withColumn(\"review_year\", year('date')) \\\n",
    "                .withColumn(\"review_month\", month('date'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "81585271",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------+----------+-----------+-------------+--------------------+-----------+------------+\n",
      "|listing_id|      id|      date|reviewer_id|reviewer_name|            comments|review_year|review_month|\n",
      "+----------+--------+----------+-----------+-------------+--------------------+-----------+------------+\n",
      "|     50646|11909864|2014-04-18|    1356099|        James|A comfortable roo...|       2014|           4|\n",
      "|     50646|13823948|2014-06-05|   15222393|        Welli|Stayed over at Su...|       2014|           6|\n",
      "|     50646|15117222|2014-07-02|    5543172|        Cyril|It's been a lovel...|       2014|           7|\n",
      "|     50646|15426462|2014-07-08|     817532|         Jake|We had a great ex...|       2014|           7|\n",
      "|     50646|15552912|2014-07-11|   10942382|        Subba|Quiet condo. Comf...|       2014|           7|\n",
      "+----------+--------+----------+-----------+-------------+--------------------+-----------+------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "reviews.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "3f89309c",
   "metadata": {},
   "outputs": [],
   "source": [
    "calendar = spark.read.format(\"com.databricks.spark.csv\") \\\n",
    "                .option('header',True) \\\n",
    "                .option(\"inferSchema\", \"true\")\\\n",
    "                .option(\"sep\", \",\")\\\n",
    "                .option('multiLine', True) \\\n",
    "                .option(\"escape\",'\\n') \\\n",
    "                .option(\"wholeFile\",\"True\") \\\n",
    "                .option(\"quoteAll\",\"true\") \\\n",
    "                .option(\"escape\",'\"') \\\n",
    "                .load('hdfs://localhost:9000/bdm/SINGAPORE/calendar') \\\n",
    "                .withColumn(\"price\", expr(\"regexp_replace(price, '[$,]', '')\").cast('float')) \\\n",
    "                .withColumn(\"adjusted_price\", expr(\"regexp_replace(adjusted_price, '[$,]', '')\").cast('float')) \\\n",
    "                .withColumn(\"date\", to_date(col(\"date\"), \"yyyy-MM-dd\")) \\\n",
    "                .withColumn(\"review_year\", year('date')) \\\n",
    "                .withColumn(\"review_month\", month('date'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "cae9a807",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------+---------+-----+--------------+--------------+--------------+-----------+------------+\n",
      "|listing_id|      date|available|price|adjusted_price|minimum_nights|maximum_nights|review_year|review_month|\n",
      "+----------+----------+---------+-----+--------------+--------------+--------------+-----------+------------+\n",
      "|   1833950|2021-10-28|        f|135.0|         135.0|             7|           365|       2021|          10|\n",
      "|   4712676|2021-10-28|        f| 58.0|          58.0|             7|           365|       2021|          10|\n",
      "|   4712676|2021-10-29|        f| 58.0|          58.0|             7|           365|       2021|          10|\n",
      "|   4712676|2021-10-30|        f| 58.0|          58.0|             7|           365|       2021|          10|\n",
      "|   4712676|2021-10-31|        f| 58.0|          58.0|             7|           365|       2021|          10|\n",
      "+----------+----------+---------+-----+--------------+--------------+--------------+-----------+------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "calendar.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "e6dce537",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to load data : 5.97432\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-load_start).total_seconds()\n",
    "print(\"Time taken to load data : {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "882745e6",
   "metadata": {},
   "source": [
    "## Spark - 2 Query Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "146c84d3",
   "metadata": {},
   "source": [
    "### 2_query_data - Prepare data with listing id having more than 50 reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "e0aadc84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "ff403928",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "reviews_temp = reviews.select('listing_id', 'comments')\n",
    "reviews_temp_groupby = reviews_temp.groupby('listing_id').count().withColumnRenamed('count','review_count')\n",
    "reviews_filter50 = reviews_temp_groupby.filter(reviews_temp_groupby.review_count > 50)\n",
    "listing_reviews = listings.select('listing_id', 'name', 'neighbourhood_cleansed', 'latitude', 'longitude', 'price', \n",
    "                                  'listing_url', 'review_scores_rating', 'review_scores_accuracy',\n",
    "                                  'review_scores_cleanliness', 'review_scores_checkin', 'review_scores_communication',\n",
    "                                  'review_scores_location', 'review_scores_value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "339f8a36",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------------------+------------+\n",
      "|listing_id|review_scores_value|review_count|\n",
      "+----------+-------------------+------------+\n",
      "|   1678744|               4.67|         150|\n",
      "|   6300043|                4.8|          51|\n",
      "|  28747912|               4.57|         103|\n",
      "|   5908083|               4.36|          77|\n",
      "|  11980197|               4.82|          71|\n",
      "+----------+-------------------+------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "join_listing_50review = listing_reviews.join(reviews_filter50, \n",
    "                                             listing_reviews.listing_id == reviews_filter50.listing_id, 'inner')\n",
    "\n",
    "join_listing_50review.select(listing_reviews.listing_id, listing_reviews.\n",
    "                                                     review_scores_value, reviews_filter50.review_count).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "c5f8ee9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Prepare data with listing id having more than 50 reviews: 1.483511\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Prepare data with listing id having more than 50 reviews: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "e73c563a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(280, 16)\n"
     ]
    }
   ],
   "source": [
    "print((join_listing_50review.count(), len(join_listing_50review.columns)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de0bdfa9",
   "metadata": {},
   "source": [
    "### 2_query_data_1 - [Overall Experience] Top 20 of listings in overall experience, with more than 50 reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "4cea2e63",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "4e27ca7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "top20_overall = join_listing_50review.sort(listing_reviews.review_scores_rating.desc())\n",
    "top20_overall = top20_overall.select(listing_reviews.listing_id, listing_reviews.listing_url, \n",
    "                                     listing_reviews.review_scores_rating, \n",
    "                                     reviews_filter50.review_count)\n",
    "top20_overall = top20_overall.limit(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "bd5c43f0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+--------------------+------------+\n",
      "|listing_id|         listing_url|review_scores_rating|review_count|\n",
      "+----------+--------------------+--------------------+------------+\n",
      "|  25435388|https://www.airbn...|                 5.0|          52|\n",
      "|  33112080|https://www.airbn...|                4.96|          53|\n",
      "|  35818699|https://www.airbn...|                4.95|          82|\n",
      "|  16989989|https://www.airbn...|                4.94|          95|\n",
      "|  31527262|https://www.airbn...|                4.94|         217|\n",
      "|  22745179|https://www.airbn...|                4.93|         139|\n",
      "|  20588444|https://www.airbn...|                4.93|          54|\n",
      "|  20703034|https://www.airbn...|                4.93|          59|\n",
      "|  23874999|https://www.airbn...|                4.93|          55|\n",
      "|   2129215|https://www.airbn...|                4.93|         370|\n",
      "|   9716208|https://www.airbn...|                4.93|          92|\n",
      "|  30687199|https://www.airbn...|                4.93|          99|\n",
      "|  43379651|https://www.airbn...|                4.92|          90|\n",
      "|  25998215|https://www.airbn...|                4.91|         169|\n",
      "|  11860123|https://www.airbn...|                4.91|          68|\n",
      "|   6718219|https://www.airbn...|                4.91|          58|\n",
      "|  35744548|https://www.airbn...|                4.91|          67|\n",
      "|  15310627|https://www.airbn...|                4.91|          65|\n",
      "|  33111805|https://www.airbn...|                 4.9|          72|\n",
      "|  16231781|https://www.airbn...|                 4.9|         127|\n",
      "+----------+--------------------+--------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top20_overall.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "c27862d6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Top 20 of listings in overall experience: 1.625937\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Top 20 of listings in overall experience: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "8fac0a3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top20_overall.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "313a2b69",
   "metadata": {},
   "source": [
    "### 2_query_data_2 -  [Cleanliness] Top 20 Listings Based on Review Score, with review number more than 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "147e01b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "b206d02f",
   "metadata": {},
   "outputs": [],
   "source": [
    "top20_clean = join_listing_50review.sort(listing_reviews.review_scores_cleanliness.desc())\n",
    "\n",
    "top20_clean = top20_clean.select(listing_reviews.listing_id, listing_reviews.listing_url, \n",
    "                                 listing_reviews.review_scores_cleanliness, reviews_filter50.review_count)\n",
    "\n",
    "top20_clean = top20_clean.limit(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "512bb932",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+-------------------------+------------+\n",
      "|listing_id|         listing_url|review_scores_cleanliness|review_count|\n",
      "+----------+--------------------+-------------------------+------------+\n",
      "|   8264717|https://www.airbn...|                     4.97|         146|\n",
      "|  15310627|https://www.airbn...|                     4.97|          65|\n",
      "|  31114726|https://www.airbn...|                     4.96|          54|\n",
      "|  23760158|https://www.airbn...|                     4.94|          89|\n",
      "|  25435388|https://www.airbn...|                     4.94|          52|\n",
      "|   8800763|https://www.airbn...|                     4.94|         215|\n",
      "|  31527262|https://www.airbn...|                     4.94|         217|\n",
      "|   9866917|https://www.airbn...|                     4.93|          70|\n",
      "|  20588444|https://www.airbn...|                     4.93|          54|\n",
      "|  25998215|https://www.airbn...|                     4.93|         169|\n",
      "|  38134989|https://www.airbn...|                     4.93|          56|\n",
      "|   2129215|https://www.airbn...|                     4.93|         370|\n",
      "|   9928008|https://www.airbn...|                     4.93|          92|\n",
      "|  30687199|https://www.airbn...|                     4.93|          99|\n",
      "|   4252548|https://www.airbn...|                     4.92|          66|\n",
      "|  10848771|https://www.airbn...|                     4.92|         354|\n",
      "|    982909|https://www.airbn...|                     4.91|         247|\n",
      "|  16231627|https://www.airbn...|                     4.91|         167|\n",
      "|   7605283|https://www.airbn...|                     4.91|         322|\n",
      "|  23874999|https://www.airbn...|                     4.91|          55|\n",
      "+----------+--------------------+-------------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top20_clean.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "583b535a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Cleanliness: 0.770067\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Cleanliness: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "5980beaf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top20_clean.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9fdc9c2",
   "metadata": {},
   "source": [
    "### 2_query_data_3 - [Accuracy] Top 20 Listings Based on Review Score, with review number more than 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "4f1ee9ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "9552bb9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "top20_acc = join_listing_50review.sort(listing_reviews.review_scores_accuracy.desc())\n",
    "\n",
    "top20_acc = top20_acc.select(listing_reviews.listing_id, listing_reviews.listing_url, \n",
    "                             listing_reviews.review_scores_accuracy, reviews_filter50.review_count)\n",
    "\n",
    "top20_acc = top20_acc.limit(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "c628f2da",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+----------------------+------------+\n",
      "|listing_id|         listing_url|review_scores_accuracy|review_count|\n",
      "+----------+--------------------+----------------------+------------+\n",
      "|  16989989|https://www.airbn...|                  4.99|          95|\n",
      "|  25435388|https://www.airbn...|                  4.98|          52|\n",
      "|  43379651|https://www.airbn...|                  4.97|          90|\n",
      "|  31114726|https://www.airbn...|                  4.96|          54|\n",
      "|  12614519|https://www.airbn...|                  4.96|          71|\n",
      "|  30687199|https://www.airbn...|                  4.96|          99|\n",
      "|  22745179|https://www.airbn...|                  4.95|         139|\n",
      "|  23874999|https://www.airbn...|                  4.95|          55|\n",
      "|  20703034|https://www.airbn...|                  4.95|          59|\n",
      "|  11381088|https://www.airbn...|                  4.95|         164|\n",
      "|  35818699|https://www.airbn...|                  4.94|          82|\n",
      "|  11860123|https://www.airbn...|                  4.94|          68|\n",
      "|  20815980|https://www.airbn...|                  4.94|          52|\n",
      "|  15310627|https://www.airbn...|                  4.94|          65|\n",
      "|  11980197|https://www.airbn...|                  4.93|          71|\n",
      "|   5827998|https://www.airbn...|                  4.93|         296|\n",
      "|  38134989|https://www.airbn...|                  4.93|          56|\n",
      "|  16231627|https://www.airbn...|                  4.93|         167|\n",
      "|  25998215|https://www.airbn...|                  4.93|         169|\n",
      "|  31527262|https://www.airbn...|                  4.93|         217|\n",
      "+----------+--------------------+----------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top20_acc.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "da0288c7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Accuracy: 0.857835\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Accuracy: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "012bf2fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top20_acc.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fb437d0",
   "metadata": {},
   "source": [
    "### 2_query_data_4 - [Check-in] Top 20 Listings Based on Review Score, with review number more than 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "9932e986",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "966efcd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "top20_checkin = join_listing_50review.sort(listing_reviews.review_scores_checkin.desc())\n",
    "\n",
    "top20_checkin = top20_checkin.select(listing_reviews.listing_id, listing_reviews.listing_url, \n",
    "                             listing_reviews.review_scores_checkin, reviews_filter50.review_count)\n",
    "\n",
    "top20_checkin = top20_checkin.limit(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "5f2b0704",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+---------------------+------------+\n",
      "|listing_id|         listing_url|review_scores_checkin|review_count|\n",
      "+----------+--------------------+---------------------+------------+\n",
      "|   9928008|https://www.airbn...|                  5.0|          92|\n",
      "|  30687199|https://www.airbn...|                  5.0|          99|\n",
      "|  15310627|https://www.airbn...|                  5.0|          65|\n",
      "|  21063861|https://www.airbn...|                 4.98|          81|\n",
      "|   3877859|https://www.airbn...|                 4.98|          63|\n",
      "|  38134989|https://www.airbn...|                 4.98|          56|\n",
      "|  23874999|https://www.airbn...|                 4.98|          55|\n",
      "|   6718219|https://www.airbn...|                 4.98|          58|\n",
      "|  21887155|https://www.airbn...|                 4.98|          90|\n",
      "|  31201327|https://www.airbn...|                 4.98|          59|\n",
      "|   5376240|https://www.airbn...|                 4.97|         240|\n",
      "|   9866917|https://www.airbn...|                 4.97|          70|\n",
      "|   5377342|https://www.airbn...|                 4.97|         295|\n",
      "|  22745179|https://www.airbn...|                 4.96|         139|\n",
      "|  14489603|https://www.airbn...|                 4.96|          60|\n",
      "|  31114726|https://www.airbn...|                 4.96|          54|\n",
      "|  18434466|https://www.airbn...|                 4.96|          68|\n",
      "|   5348703|https://www.airbn...|                 4.96|         199|\n",
      "|  11642282|https://www.airbn...|                 4.95|         101|\n",
      "|  16231781|https://www.airbn...|                 4.95|         127|\n",
      "+----------+--------------------+---------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top20_checkin.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "a46ee375",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Check-in: 0.678651\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Check-in: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "550f0684",
   "metadata": {},
   "source": [
    "### 2_query_data_5 - [Communication with host] Top 20 Listings Based on Review Score, with review number more than 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "7f90c77b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "56ca3add",
   "metadata": {},
   "outputs": [],
   "source": [
    "top20_comm = join_listing_50review.sort(listing_reviews.review_scores_communication.desc())\n",
    "\n",
    "top20_comm = top20_comm.select(listing_reviews.listing_id, listing_reviews.listing_url, \n",
    "                             listing_reviews.review_scores_communication, reviews_filter50.review_count)\n",
    "\n",
    "top20_comm = top20_comm.limit(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "2c5abc9b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+---------------------------+------------+\n",
      "|listing_id|         listing_url|review_scores_communication|review_count|\n",
      "+----------+--------------------+---------------------------+------------+\n",
      "|  11980197|https://www.airbn...|                        5.0|          71|\n",
      "|  23874999|https://www.airbn...|                        5.0|          55|\n",
      "|  31201327|https://www.airbn...|                        5.0|          59|\n",
      "|   1302185|https://www.airbn...|                       4.99|          91|\n",
      "|  30687199|https://www.airbn...|                       4.99|          99|\n",
      "|  35818699|https://www.airbn...|                       4.98|          82|\n",
      "|   7605283|https://www.airbn...|                       4.98|         322|\n",
      "|  25435388|https://www.airbn...|                       4.98|          52|\n",
      "|  16989989|https://www.airbn...|                       4.98|          95|\n",
      "|  38134989|https://www.airbn...|                       4.98|          56|\n",
      "|  14223124|https://www.airbn...|                       4.98|          62|\n",
      "|  28359844|https://www.airbn...|                       4.98|         106|\n",
      "|  23072323|https://www.airbn...|                       4.98|          65|\n",
      "|  15310627|https://www.airbn...|                       4.98|          65|\n",
      "|  22745179|https://www.airbn...|                       4.97|         139|\n",
      "|  16620686|https://www.airbn...|                       4.97|         113|\n",
      "|   6718219|https://www.airbn...|                       4.97|          58|\n",
      "|   5377342|https://www.airbn...|                       4.97|         295|\n",
      "|   9928008|https://www.airbn...|                       4.97|          92|\n",
      "|  20588444|https://www.airbn...|                       4.96|          54|\n",
      "+----------+--------------------+---------------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top20_comm.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "994b0c46",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Communication with Host: 1.121634\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Communication with Host: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77f04bea",
   "metadata": {},
   "source": [
    "### 2_query_data_6 - [Location] Top 20 Listings Based on Review Score, with review number more than 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "892c6638",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "930106fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "top20_location = join_listing_50review.sort(listing_reviews.review_scores_location.desc())\n",
    "\n",
    "top20_location = top20_location.select(listing_reviews.listing_id, listing_reviews.listing_url, \n",
    "                             listing_reviews.review_scores_location, reviews_filter50.review_count)\n",
    "\n",
    "top20_location = top20_location.limit(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "e1d3591f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+----------------------+------------+\n",
      "|listing_id|         listing_url|review_scores_location|review_count|\n",
      "+----------+--------------------+----------------------+------------+\n",
      "|  23446208|https://www.airbn...|                  4.99|         116|\n",
      "|  15792137|https://www.airbn...|                  4.99|         105|\n",
      "|  11863888|https://www.airbn...|                  4.99|          75|\n",
      "|  25892078|https://www.airbn...|                  4.99|          78|\n",
      "|  23874999|https://www.airbn...|                  4.98|          55|\n",
      "|  11817134|https://www.airbn...|                  4.97|         131|\n",
      "|  15310627|https://www.airbn...|                  4.97|          65|\n",
      "|  24276647|https://www.airbn...|                  4.97|          73|\n",
      "|  20588444|https://www.airbn...|                  4.96|          54|\n",
      "|  11381088|https://www.airbn...|                  4.96|         164|\n",
      "|  17928170|https://www.airbn...|                  4.96|          94|\n",
      "|  11865360|https://www.airbn...|                  4.95|          98|\n",
      "|  15722934|https://www.airbn...|                  4.95|          61|\n",
      "|  16309636|https://www.airbn...|                  4.94|          68|\n",
      "|   6300043|https://www.airbn...|                  4.94|          51|\n",
      "|  31851838|https://www.airbn...|                  4.94|          63|\n",
      "|  21820005|https://www.airbn...|                  4.93|          69|\n",
      "|  33506058|https://www.airbn...|                  4.93|          58|\n",
      "|   5889741|https://www.airbn...|                  4.93|         209|\n",
      "|  14740975|https://www.airbn...|                  4.93|          62|\n",
      "+----------+--------------------+----------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top20_location.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "a61da34e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Location: 0.971124\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Location: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05b0f3db",
   "metadata": {},
   "source": [
    "### 2_query_data_7 - [Value] Top 20 Listings Based on Review Score, with review number more than 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "c277d759",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "query_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "38af2227",
   "metadata": {},
   "outputs": [],
   "source": [
    "top20_value = join_listing_50review.sort(listing_reviews.review_scores_value.desc())\n",
    "\n",
    "top20_value = top20_value.select(listing_reviews.listing_id, listing_reviews.listing_url, \n",
    "                             listing_reviews.review_scores_value, reviews_filter50.review_count)\n",
    "\n",
    "top20_value = top20_value.limit(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "6c8c1547",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------------+-------------------+------------+\n",
      "|listing_id|         listing_url|review_scores_value|review_count|\n",
      "+----------+--------------------+-------------------+------------+\n",
      "|   9866917|https://www.airbn...|               4.94|          70|\n",
      "|  23874999|https://www.airbn...|               4.91|          55|\n",
      "|   9716208|https://www.airbn...|               4.91|          92|\n",
      "|  25435388|https://www.airbn...|                4.9|          52|\n",
      "|  16231781|https://www.airbn...|                4.9|         127|\n",
      "|  12614519|https://www.airbn...|                4.9|          71|\n",
      "|  25998215|https://www.airbn...|               4.88|         169|\n",
      "|   7605283|https://www.airbn...|               4.87|         322|\n",
      "|   5798211|https://www.airbn...|               4.87|         151|\n",
      "|   6630341|https://www.airbn...|               4.86|          98|\n",
      "|   6619955|https://www.airbn...|               4.86|          64|\n",
      "|  16231741|https://www.airbn...|               4.86|          88|\n",
      "|  43379651|https://www.airbn...|               4.86|          90|\n",
      "|   2129215|https://www.airbn...|               4.85|         370|\n",
      "|  16231627|https://www.airbn...|               4.85|         167|\n",
      "|   5827713|https://www.airbn...|               4.85|         286|\n",
      "|  10848771|https://www.airbn...|               4.85|         354|\n",
      "|   6718514|https://www.airbn...|               4.85|          75|\n",
      "|   4561484|https://www.airbn...|               4.84|          62|\n",
      "|  22745179|https://www.airbn...|               4.84|         139|\n",
      "+----------+--------------------+-------------------+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top20_value.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "cd0a2086",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Value: 0.876288\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-query_start).total_seconds()\n",
    "print(\"Time taken to run Part 2 Query Data_Top 20 Listings Based on Review Score_Value: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1edb3e66",
   "metadata": {},
   "source": [
    "## Spark - 3 Analysis Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2df062db",
   "metadata": {},
   "source": [
    "### 3_analysis_data_1 - Listing - Count by Room Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "68d8c8e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "analysis_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "ff7dcbea",
   "metadata": {},
   "outputs": [],
   "source": [
    "roomtype = listings.select('listing_id', 'room_type')\n",
    "count_roomtype = roomtype.groupby('room_type').count().withColumnRenamed('count','cnt')\n",
    "count_roomtype = count_roomtype.sort(count_roomtype.cnt.desc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "a48886bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+\n",
      "|      room_type| cnt|\n",
      "+---------------+----+\n",
      "|   Private room|2013|\n",
      "|Entire home/apt|1769|\n",
      "|     Hotel room| 182|\n",
      "|    Shared room| 159|\n",
      "+---------------+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "count_roomtype.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "78120cc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the file to HDFS\n",
    "count_roomtype.write.csv('hdfs://localhost:9000/bdm/spark/count_roomtype')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "d43ef682",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 3 Analysis Data_Count by Room Type: 1.517129\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-analysis_start).total_seconds()\n",
    "print(\"Time taken to run Part 3 Analysis Data_Count by Room Type: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b379bd24",
   "metadata": {},
   "source": [
    "### 3_analysis_data_2 - Listing - Count by Property Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "75231ae8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "analysis_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "6d3b9eeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "propertytype = listings.select('listing_id', 'property_type')\n",
    "count_propertytype = propertytype.groupby('property_type').count().withColumnRenamed('count','cnt')\n",
    "count_propertytype = count_propertytype.sort(count_propertytype.cnt.desc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "42c105b6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------------+---+\n",
      "|property_type                      |cnt|\n",
      "+-----------------------------------+---+\n",
      "|Private room in rental unit        |831|\n",
      "|Entire condominium (condo)         |657|\n",
      "|Entire rental unit                 |582|\n",
      "|Entire serviced apartment          |428|\n",
      "|Private room in condominium (condo)|341|\n",
      "|Room in boutique hotel             |239|\n",
      "|Private room in residential home   |187|\n",
      "|Room in hotel                      |138|\n",
      "|Private room in townhouse          |113|\n",
      "|Room in hostel                     |76 |\n",
      "|Shared room in hostel              |61 |\n",
      "|Entire residential home            |42 |\n",
      "|Private room in hostel             |36 |\n",
      "|Private room in serviced apartment |35 |\n",
      "|Private room in bungalow           |35 |\n",
      "|Shared room in rental unit         |31 |\n",
      "|Room in serviced apartment         |30 |\n",
      "|Private room in bed and breakfast  |28 |\n",
      "|Shared room in bed and breakfast   |27 |\n",
      "|Private room                       |26 |\n",
      "+-----------------------------------+---+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "count_propertytype.show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "e9242267",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the file to HDFS\n",
    "count_propertytype.write.csv('hdfs://localhost:9000/bdm/spark/count_propertytype')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "f8c03bbf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 3 Analysis Data_Count by Property Type: 1.231272\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-analysis_start).total_seconds()\n",
    "print(\"Time taken to run Part 3 Analysis Data_Count by Property Type: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3b19698",
   "metadata": {},
   "source": [
    "### 3_analysis_data_3 - Top 10 Property Type with Expensive Average Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "c12db94e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "analysis_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "f36db60d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "price_property = listings.select('listing_id', 'price', 'property_type')\n",
    "avgprice_property = price_property.groupby('property_type').avg('price') \\\n",
    "                    .withColumnRenamed('avg(price)','avg_price')\n",
    "avgprice_property = avgprice_property.sort(avgprice_property.avg_price.desc())\n",
    "top10_avgprice_property = avgprice_property.limit(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "5c46e604",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------------+------------------+\n",
      "|property_type              |avg_price         |\n",
      "+---------------------------+------------------+\n",
      "|Entire villa               |1016.3333333333334|\n",
      "|Boat                       |849.4             |\n",
      "|Entire place               |592.0             |\n",
      "|Entire townhouse           |526.2             |\n",
      "|Entire residential home    |470.6190476190476 |\n",
      "|Room in hotel              |329.89130434782606|\n",
      "|Shared room in bungalow    |307.5             |\n",
      "|Private room in guest suite|298.125           |\n",
      "|Entire serviced apartment  |289.20093457943926|\n",
      "|Tent                       |277.0             |\n",
      "+---------------------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "top10_avgprice_property.show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "1c8ec2eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the file to HDFS\n",
    "top10_avgprice_property.write.csv('hdfs://localhost:9000/bdm/spark/top10_avgprice_property')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "58b851a6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 3 Analysis Data_Top 10 Property Type with Expensive Average Price: 1.40381\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-analysis_start).total_seconds()\n",
    "print(\"Time taken to run Part 3 Analysis Data_Top 10 Property Type with Expensive Average Price: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31eda788",
   "metadata": {},
   "source": [
    "### 3_analysis_data_4 - Top 10 Property Type with Cheapest Average Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "3d7d56a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "analysis_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "e5f8f6e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "price_property = listings.select('listing_id', 'price', 'property_type')\n",
    "avgprice_property = price_property.groupby('property_type').avg('price') \\\n",
    "                    .withColumnRenamed('avg(price)','avg_price')\n",
    "avgprice_property = avgprice_property.sort(avgprice_property.avg_price.asc())\n",
    "bot10_avgprice_property = avgprice_property.limit(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "d4759214",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------------------+------------------+\n",
      "|property_type                   |avg_price         |\n",
      "+--------------------------------+------------------+\n",
      "|Shared room in guesthouse       |25.0              |\n",
      "|Shared room in residential home |36.5              |\n",
      "|Shared room                     |41.7              |\n",
      "|Private room in casa particular |44.0              |\n",
      "|Private room in lighthouse      |45.0              |\n",
      "|Shared room in villa            |45.0              |\n",
      "|Shared room in bed and breakfast|50.666666666666664|\n",
      "|Entire home/apt                 |58.333333333333336|\n",
      "|Shared room in hostel           |62.31147540983606 |\n",
      "|Room in hostel                  |66.3157894736842  |\n",
      "+--------------------------------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "bot10_avgprice_property.show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "2ec284c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the file to HDFS\n",
    "bot10_avgprice_property.write.csv('hdfs://localhost:9000/bdm/spark/bot10_avgprice_property')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "634f7af6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 3 Analysis Data_Top 10 Property Type with Cheapest Average Price: 1.158784\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-analysis_start).total_seconds()\n",
    "print(\"Time taken to run Part 3 Analysis Data_Top 10 Property Type with Cheapest Average Price: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c36c53f2",
   "metadata": {},
   "source": [
    "### 3_analysis_data_5 - Summary for Number of Reviews Comment by Year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "d8a627a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "analysis_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "f739e7e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "group_reviews_yr = reviews.groupby('review_year').count().withColumnRenamed('count','cnt')\n",
    "group_reviews_yr = group_reviews_yr.sort(reviews.review_year.desc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "d8e8dac5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-----+\n",
      "|review_year|  cnt|\n",
      "+-----------+-----+\n",
      "|       2021| 3216|\n",
      "|       2020| 6649|\n",
      "|       2019|14792|\n",
      "|       2018|10020|\n",
      "|       2017| 7392|\n",
      "|       2016| 4319|\n",
      "|       2015| 2127|\n",
      "|       2014|  482|\n",
      "|       2013|  222|\n",
      "|       2012|   98|\n",
      "|       2011|   11|\n",
      "+-----------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "group_reviews_yr.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "285de7ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the file to HDFS\n",
    "group_reviews_yr.write.csv('hdfs://localhost:9000/bdm/spark/group_reviews_yr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "2c4ac81f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 3 Analysis Data_Summary for Number of Reviews Comment by Year: 2.833815\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-analysis_start).total_seconds()\n",
    "print(\"Time taken to run Part 3 Analysis Data_Summary for Number of Reviews Comment by Year: {}\".format(duration_load_time))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc66bb30",
   "metadata": {},
   "source": [
    "### 3_analysis_data_6 - Average Price by Location (Neighbourhood_cleansed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "id": "6bd491d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start time\n",
    "analysis_start = datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "d66d77a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "neighborhood = listings.select('listing_id', 'neighbourhood_cleansed', 'price')\n",
    "neighborhood_price = neighborhood.groupby('neighbourhood_cleansed').avg('price') \\\n",
    "                                        .withColumnRenamed('avg(price)','avg_price')\n",
    "neighborhood_price = neighborhood_price.sort(neighborhood_price.avg_price.desc())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "b2f9f43d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------+------------------+\n",
      "|neighbourhood_cleansed|         avg_price|\n",
      "+----------------------+------------------+\n",
      "|                  Tuas|           10286.0|\n",
      "|      Southern Islands|1830.5263157894738|\n",
      "|               Orchard| 568.6666666666666|\n",
      "|          Marina South|             359.0|\n",
      "|             Pasir Ris| 332.4166666666667|\n",
      "|          Lim Chu Kang|             324.0|\n",
      "|               Pioneer|             305.0|\n",
      "|          Sungei Kadut|             290.0|\n",
      "|               Tanglin| 268.7391304347826|\n",
      "|                Newton| 254.6315789473684|\n",
      "|       Singapore River| 251.9181818181818|\n",
      "|              Clementi|230.02409638554218|\n",
      "|  Central Water Cat...|             227.5|\n",
      "|             Toa Payoh|225.89743589743588|\n",
      "|          River Valley| 217.6139240506329|\n",
      "|               Hougang| 210.2325581395349|\n",
      "|                Novena|193.06140350877192|\n",
      "|         Downtown Core|185.69716088328076|\n",
      "|                Rochor|171.74358974358975|\n",
      "|            Queenstown| 171.3217391304348|\n",
      "+----------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "neighborhood_price.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "9e4e4783",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the file to HDFS\n",
    "neighborhood_price.write.csv('hdfs://localhost:9000/bdm/spark/neighborhood_price')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "2e34c999",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time taken to run Part 3 Analysis Data_Average Price by Location: 1.314195\n"
     ]
    }
   ],
   "source": [
    "# Duration of Execution Time - Loading Data\n",
    "duration_load_time = (datetime.now()-analysis_start).total_seconds()\n",
    "print(\"Time taken to run Part 3 Analysis Data_Average Price by Location: {}\".format(duration_load_time))"
   ]
  }
 ],
 "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}