Project 3 - Airbnb_final-Copy1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project 3: Airbnb\n",
    "**This is the third of three mandatory projects to be handed in as part of the assessment for the course 02807 Computational Tools for Data Science at Technical University of Denmark, autumn 2019.**\n",
    "\n",
    "#### Practical info\n",
    "- **The project is to be done in groups of at most 3 students**\n",
    "- **Each group has to hand in _one_ Jupyter notebook (this notebook) with their solution**\n",
    "- **The hand-in of the notebook is due 2019-12-05, 23:59 on DTU Inside**\n",
    "\n",
    "#### Your solution\n",
    "- **Your solution should be in Python/PySpark**\n",
    "- **For each question you may use as many cells for your solution as you like**\n",
    "- **You should not remove the problem statements**\n",
    "- **Your notebook should be runnable, i.e., clicking [>>] in Jupyter should generate the result that you want to be assessed**\n",
    "- **You are not expected to use machine learning to solve any of the exercises**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "[Airbnb](http://airbnb.com) is an online marketplace for arranging or offering lodgings. In this project you will use Spark to analyze data obtained from the Airbnb website. The purpose of the analysis is to extract information about trends and patterns from the data.\n",
    "\n",
    "The project has two parts.\n",
    "\n",
    "### Part 1: Loading, describing and preparing the data\n",
    "There's quite a lot of data. Make sure that you can load and correctly parse the data, and that you understand what the dataset contains. You should also prepare the data for the analysis in part two. This means cleaning it and staging it so that subsequent queries are fast.\n",
    "\n",
    "### Par 2: Analysis\n",
    "In this part your goal is to learn about trends and usage patterns from the data. You should give solutions to the tasks defined in this notebook, and you should use Spark to do the data processing. You may use other libraries like for instance Pandas and matplotlib for visualisation.\n",
    "\n",
    "## Guidelines\n",
    "- Processing data should be done using Spark. Once data has been reduced to aggregate form, you may use collect to extract it into Python for visualisation.\n",
    "- Your solutions will be evaluated by correctness, code quality and interpretability of the output. This means that you have to write clean and efficient Spark code that will generate sensible execution plans, and that the tables and visualisations that you produce are meaningful and easy to read.\n",
    "- You may add more cells for your solutions, but you should not modify the notebook otherwise."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Spark session and define imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import *\n",
    "from pyspark.sql import functions as f\n",
    "from pyspark.sql.types import *\n",
    "\n",
    "import numpy as np\n",
    "from scipy import stats\n",
    "import pandas as pd\n",
    "import re\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "plt.style.use(['seaborn-whitegrid'])\n",
    "\n",
    "spark = SparkSession.builder.appName(\"SparkIntro\").getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1: Loading, describing and preparing the data\n",
    "The data comes in two files. Start by downloading the files and putting them in your `data/` folder.\n",
    "\n",
    "- [Listings](https://files.dtu.dk/u/siPzAasj8w2gI_ME/listings.csv?l) (5 GB)\n",
    "- [Reviews](https://files.dtu.dk/u/k3oaPYp6GjKBeho4/reviews.csv?l) (9.5 GB)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load the data\n",
    "The data has multiline rows (rows that span multiple lines in the file). To correctly parse these you should use the `multiline` option and set the `escape` character to be `\"`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load listings\n",
    "df = spark.read.option('header', True).option('inferSchema', True).option('multiline', True).option('escape','\"').csv('listings_sub_10000.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load reviews\n",
    "df_review = spark.read.option('header', True).option('inferSchema', True).option('multiline', True).option('escape','\"').csv('reviews_sub_10000.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Describe the data\n",
    "List the features (schema) and sizes of the datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Print schema of listings\n",
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_review.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare the data for analysis\n",
    "You should prepare two dataframes to be used in the analysis part of the project. You should not be concerned with cleaning the data. There's a lot of it, so it will be sufficient to drop rows that have bad values. You may want to go back and refine this step at a later point when doing the analysis.\n",
    "\n",
    "You may also want to consider if you can stage your data so that subsequent processing is more efficient (this is not strictly necessary for Spark to run, but you may be able to decrease the time you sit around waiting for Spark to finish things)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Description of data cleaning\n",
    "*In this section only columns of the data that is used in the analysis is kept. Furthermore, rows containing NA values in any of the selected columns are dropped. The new reduced dataframes are saved as csv in order to stage the data. They are forced to be partitioned into 16 parts, yielding 16 CSV files. We need to repartion because it is not automatically partitioned due to the multiline nature of the raw data. They are then loaded again, so that further processing is faster.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop rows with NA values in columns city, neighbourhood, property_type, price\n",
    "df = df.select('id','country','city', 'neighbourhood', 'property_type', 'price','review_scores_rating')\n",
    "df = df.dropna(how='any')\n",
    "# Write file\n",
    "df.repartition(16).write.csv('data/clean_data',header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load reduced CSV files\n",
    "df = spark.read.option(\"header\", \"true\").csv(\"data/clean_data/*.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Subset of Review data and change column name \n",
    "df_review = df_review.select('listing_id', f.col('id').alias('review_id'),\\\n",
    "                             'date','comments')\n",
    "\n",
    "# Write CSV\n",
    "df_review = df_review.dropna(how='any')\n",
    "df_review.repartition(16).write.csv('data/clean_review_data', header=True)#.option('escape','\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load CSV\n",
    "df_review_ = spark.read.option(\"header\", \"true\").csv(\"data/clean_review_data/*.csv\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2: Analysis\n",
    "Use Spark and your favorite tool for data visualization to solve the following tasks.\n",
    "\n",
    "## The basics\n",
    "Compute and show a dataframe with the number of listings and neighbourhoods per city.\n",
    "\n",
    "\n",
    "#### Description of implementation of the basics\n",
    "*We are computing the desired dataframe in Spark using a group by 'city' clause and then a count of listings and an approximate count of distinct neighbourhoods. The distinct count is done internally by using a HyperLogLog sketch, which is faster for large amount of data. The data frame is then ordered by the distinct number of neighbourhoods, so that we below show the cities with the highest number of neighbourhoods.*\n",
    "\n",
    "*Afterwards the data frame is filtered using a user defined function, so that only listings with either Copenhagen or København in the city column is still in the data. The data is grouped by property type and neighbourhood to get an overview of the city.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count number of listings and distinct neighbourhoods per city, using hyperloglog.\n",
    "df.groupby('city').agg(f.count('id').alias('Listings'), f.approxCountDistinct('neighbourhood').alias('Neighbourhoods')).orderBy(f.desc('Neighbourhoods')).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the table above, you should choose a city that you want to continue your analysis for. The city should have mulitple neighbourhoods with listings in them.\n",
    "\n",
    "Compute and visualize the number of listings of different property types per neighbourhood in your city."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# User defined function to extract all listings contaning Copenhagen or København in city column\n",
    "def extract_cph(city):\n",
    "    regex_pattern = r'(.*København.*|.*Copenhagen.*)'\n",
    "    if re.match(regex_pattern, city, re.IGNORECASE):\n",
    "        return True\n",
    "    return False\n",
    "\n",
    "extract_cph_udf = f.udf(extract_cph, BooleanType())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter data frame to only contain Copenhagen and group by property type\n",
    "prop_neigh_city = df.filter(extract_cph_udf(df.city)).groupby('property_type','neighbourhood').agg(f.count('id')).orderBy('count(id)').toPandas()\n",
    "prop_neigh_city.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the property type count per neighbourhood\n",
    "prop_neigh_city = prop_neigh_city[prop_neigh_city['count(id)']>10]\n",
    "prop_neigh_city.pivot(index='neighbourhood',columns='property_type',\\\n",
    "                      values='count(id)').plot(kind='bar',figsize=(16,6),stacked=True)\n",
    "plt.title('Property type by neighbourhood')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prices\n",
    "Compute the minimum, maximum and average listing price in your city. \n",
    "\n",
    "\n",
    "#### Description of Implementation of Prices-section\n",
    "*We see that 20,526 listings appears in Copenhagen. The price is cast to double, so that it is ready to use for some statistics. The minimum, maximum and average price of the Copenhagen is computed. It should be noted that we have data with a price of 0, that could indicate a need for removing price outliers. The distribution of the prices is also visualized and here it is clear that both the extremely low and high prices are outliers and only occurs very rarely.*\n",
    "\n",
    "*At the end of the section we compute a new attribute called value, that says something about the value for money, based on the average rating score and price per night. In order to show the top three value for money listings in each Copenhagen neighbourhood, we implemented a window function that orders by value and is used for filtering.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Listings in Copenhagen\n",
    "df_cph = df.filter(extract_cph_udf(df.city))\n",
    "df_cph.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cast price into double and remove $\n",
    "df_cph = df_cph.withColumn(\"price\",f.expr(\"substring(price, 2, length(price))\"))\n",
    "df_cph = df_cph.withColumn(\"price\", f.regexp_replace(f.col(\"price\"), \",\", \"\"))\n",
    "df_cph = df_cph.withColumn(\"price\",df_cph[\"price\"].cast(DoubleType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Min, max and average price data frame\n",
    "print(\"Basic statistics for Copenhagen\")\n",
    "df_cph.select(f.avg('price').alias(\"Average price\"), f.max('price').alias(\"Max price\"), f.min('price').alias(\"Min price\")).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute and visualize the distribution of listing prices in your city."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.set_palette(\"cubehelix\", 4)\n",
    "plt.style.use(['seaborn-whitegrid'])\n",
    "sns.set(rc={'figure.figsize':(16,6)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_city_p = df_cph.toPandas()\n",
    "plt.hist(df_city_p.price,bins=1500)\n",
    "plt.title('Distribution of prices in Copenhagen')\n",
    "plt.xlabel('Price')\n",
    "plt.xlim(0,10000)\n",
    "plt.ylabel('Count');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The value of a listing is its rating divided by its price.\n",
    "\n",
    "Compute and show a dataframe with the 3 highest valued listings in each neighbourhood."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create new value column\n",
    "df_value = df_cph.withColumn(\"review_scores_rating\",df_cph[\"review_scores_rating\"].cast(DoubleType()))\n",
    "df_value = df_value.withColumn(\"value\",f.lit(f.col(\"review_scores_rating\")/f.col(\"price\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Window function to select top three\n",
    "sorted_by_value = Window.partitionBy('neighbourhood').orderBy(f.desc('value'))\n",
    "# Apply window function\n",
    "ranked_by_value = df_value.withColumn('value_rank', f.rank().over(sorted_by_value))\n",
    "# Filter top three\n",
    "ranked_df = ranked_by_value.filter(f.col('value_rank') <= 3).drop('value_rank').orderBy('neighbourhood', f.desc('value'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "ranked_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Trends\n",
    "Now we want to analyze the \"popularity\" of your city. The data does not contain the number of bookings per listing, but we have a large number of reviews, and we will assume that this is a good indicator of activity on listings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute and visualize the popularity (i.e., number of reviews) of your city over time.\n",
    "\n",
    "#### Description of Trends\n",
    "*In this section we first join (left) the Review data on the Listing data (filtered on Copenhagen) and then count the number of reviews pr. unique date using group by. The data is transformed to Pandas Dataframe and the Date column is changed to Pandas Datetime type. We then sum the number of reviews pr. month, again using groupby and plot the result. To vizualize the popularity of neighbourhoods over time we do almost the same as just described. The only difference is that we also group by \"neighbourhood\". This is done to show the number of reviews pr. neighbourhood. Lastly, to show seasonality we sum the number of reviews pr. month and plot the result.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# left-join Review-data on Listing-data on listing_id and id\n",
    "df_join = df_cph.join(df_review, (df_cph.id == df_review.listing_id), how='left')\n",
    "# Groupby date count reviews\n",
    "df_plot = df_join.groupby('date').agg(f.count('review_id')).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To datetime and groupby date at month level\n",
    "df_plot.date = pd.to_datetime(df_plot.date)\n",
    "df_plot = df_plot.groupby(pd.Grouper(key='date',freq='M')).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plotting number of reveis over entire time period\n",
    "df_plot.plot(figsize=(16,6))\n",
    "\n",
    "plt.title('Reviews per month over entire time period')\n",
    "plt.ylabel('Count')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute and visualize the popularity of neighbourhoods over time. If there are many neighbourhoods in your city, you should select a few interesting ones for comparison."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by date and neighbourhood\n",
    "df_plot = df_join.groupby('date','neighbourhood').agg(f.count('review_id')).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To datetime and group by date at month level\n",
    "df_plot.date = pd.to_datetime(df_plot.date)\n",
    "df_plot = df_plot.groupby(['neighbourhood',pd.Grouper(key='date',freq='M')]).sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_plot = df_plot.pivot(index='date',columns='neighbourhood',\\\n",
    "                      values='count(review_id)')\n",
    "df_plot = df_plot[df_plot.index.notnull()].reset_index()\n",
    "df_plot = df_plot.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot review count of four largest neighbourhoods\n",
    "df_plot.plot(x='date', y=['Østerbro','Nørrebro','Vesterbro','Indre By'],figsize=(16,6))\n",
    "plt.title('Number of reviews for different neighbourhoods')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute and visualize the popularity of your city by season. For example, visualize the popularity of your city per month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by date\n",
    "df_plot = df_join.groupby('date').agg(f.count('review_id')).toPandas()\n",
    "# To datetime and select month\n",
    "df_plot.date = pd.to_datetime(df_plot.date)\n",
    "df_plot['date'] = df_plot['date'].dt.month\n",
    "\n",
    "# Group by name of month\n",
    "df_plot = df_plot.groupby('date',as_index=False).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot season popularity\n",
    "fig = df_plot.plot(x='date', y='count(review_id)',figsize=(16,6))\n",
    "fig.set_xticks(np.arange(1,13))\n",
    "fig.set_xticklabels(['January','February','March','April','May','June','July','August','Septmenber','Oktober','November','December'])\n",
    "plt.title('Season popularity of Copenhagen')\n",
    "plt.xlabel('Month')\n",
    "plt.ylabel('Number of reviews')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reviews\n",
    "In this part you should determine which words used in reviews that are the most positive. \n",
    "\n",
    "The individual reviews do not have a rating of the listing, so we will assume that each review gave the average rating to the listing, i.e., the one on the listing.\n",
    "\n",
    "You should assign a positivity weight to each word seen in reviews and list the words with the highest weight. It is up to you to decide what the weight should be. For example, it can be a function of the rating on the listing on which it occurs, the number of reviews it occurs in, and the number of unique listings for which it was used to review.\n",
    "\n",
    "Depending on your choice of weight function, you may also want to do some filtering of words. For example, remove words that only occur in a few reviews."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Description of Reviews\n",
    "\n",
    "*To assess the possitivity score for each word in the reviews, we are merging the listing and review dataframe, such that all reviews are getting the average score for the respective listing. Before analyzing the words, we are handling things that might tweak the correct positivity-score of a given word. This includes things like lowercasing and removal of symbols.*\n",
    "\n",
    "*The weight measure is calculated by exploding each review such that every word in the `comments` column gets its own row. This means, that every review, which initially consitis of one row, gets exploded into as many rows as the number of words in the review comment. Thereby, we will have distinct words with the score for that particular listing. We can then group by words and use an aggregate mean to get the final score for each word.*\n",
    "\n",
    "*We are ignoring all words which occurs less than 500 times in total, in order to ensure that words are actually having an impotance before they can occur as a positive word. This hinders, that a word which are mentioned very few times in a review that has been very positive, will outweigh other more relevant words.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing packages\n",
    "from pyspark.sql.functions import lower, regexp_replace, split\n",
    "from pyspark.ml.feature import Tokenizer, RegexTokenizer\n",
    "from pyspark.ml.feature import StopWordsRemover\n",
    "from pyspark.sql.functions import udf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load listings\n",
    "dfl = spark.read.option('header', True).option('inferSchema', True).option('multiline', True).option('escape','\"').csv('../listings.csv')\n",
    "# Load reviews\n",
    "dfr = spark.read.option('header', True).option('inferSchema', True).option('multiline', True).option('escape','\"').csv('../reviews.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Selecting the relevant columns in the two dataframes\n",
    "dfr = dfr.select('listing_id','comments')\n",
    "dfl = dfl.select('id','review_scores_rating')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After selecting the correct columns, we have to join the two dataframes. As we do not have a rating for each of the reviews, each review will get the average rating which is contained in the listing dataset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Joining the reviews and listings on listing ID\n",
    "df = dfr.join(dfl, (f.col('listing_id') == f.col('id')), 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Cleaning text in the comments column \n",
    "def clean_text(c):\n",
    "    c = lower(c)\n",
    "    c = regexp_replace(c, \"[^a-zA-Z0-9\\\\s]\", \"\")\n",
    "    return c\n",
    "\n",
    "df = df.withColumn(\"text_clean\", clean_text(f.col(\"comments\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating a tokenizer and tokenizing all the comments, enabling us to analyze each word seperately \n",
    "tokenizer = Tokenizer(inputCol=\"text_clean\", outputCol=\"words\")\n",
    "df = tokenizer.transform(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Defining a stopwords remover\n",
    "remover = StopWordsRemover()\n",
    "stopwords = remover.getStopWords() \n",
    "\n",
    "#Defining the input column on which the stopwords remover is applied and what to call the output column\n",
    "remover.setInputCol(\"words\")\n",
    "remover.setOutputCol(\"comments_wo_stopwords\")\n",
    "\n",
    "#Apply the stopwords remover to the dataframe \n",
    "df = remover.transform(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Displaying results of which words are the most positive given the reviews\n",
    "df.withColumn('Word', f.explode(f.col('comments_wo_stopwords'))).select('review_scores_rating','Word')\\\n",
    "    .groupBy('Word').agg(f.mean('review_scores_rating').alias(\"Average word-review\"), f.count('Word').alias(\"Word count\"))\\\n",
    "    .sort('Average word-review', ascending=False)\\\n",
    "    .filter(f.col('Word count') > 500)\\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we see above, the words are very relevant from the perspective of an AirBnB review. Words like \"home\", \"beautiful\", \"quiet\", \"restaurants\", are all words we associate with positive holiday experiences. "
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}