Amazon_Reviews_spark.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import required libraries and functions\n",
    "import requests\n",
    "from pyspark.sql.functions import countDistinct, avg, stddev\n",
    "from pyspark.sql import SparkSession\n",
    "import pandas as pd\n",
    "# plotting \n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "sns.set(style=\"whitegrid\")\n",
    "from pandas import datetime\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# download data from source\n",
    "# data source: https://s3.amazonaws.com/amazon-reviews-pds/tsv/index.txt\n",
    "\n",
    "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_multilingual_US_v1_00.tsv.gz\"\n",
    "target_path = \"amazon_reviews_multilingual_US_v1_00.tsv.gz\"\n",
    "response = requests.get(url, stream=True)\n",
    "if response.status_code == 200:\n",
    "    with open(target_path, 'wb') as f:\n",
    "        f.write(response.raw.read())\n",
    "else:\n",
    "  print(\"Unable to download file\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sh\n",
    "gunzip amazon_reviews_multilingual_US_v1_00.tsv.gz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">Out[4]: True</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# moving data from driver node to dbfs file system\n",
    "dbutils.fs.mv(\"file:/databricks/driver/amazon_reviews_multilingual_US_v1_00.tsv\", \"dbfs:/tmp/amazon_reviews_multilingual_US_v1_00.tsv\")  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load data on spark cluster\n",
    "df = spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferSchema\", \"true\").option(\"sep\", \"\\t\").load(\"dbfs:/tmp/amazon_reviews_multilingual_US_v1_00.tsv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">Out[24]: [Row(marketplace=&#39;US&#39;, customer_id=53096384, review_id=&#39;R63J84G1LOX6R&#39;, product_id=&#39;1563890119&#39;, product_parent=763187671, product_title=&#39;The Sandman Vol. 1: Preludes and Nocturnes&#39;, product_category=&#39;Books&#39;, star_rating=4, helpful_votes=0, total_votes=1, vine=&#39;N&#39;, verified_purchase=&#39;N&#39;, review_headline=&#39;ignore the review below&#39;, review_body=&#39;this is the first 8 issues of the series. it is the starting point of all this... it also contains the sound of her wings. issue #8. which is the first appearance of death. and many peoples favorite issue. its not the best of the collected works. but its the start of them.&#39;, review_date=datetime.datetime(1995, 8, 13, 0, 0))]</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print first line of the data\n",
    "df.take(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">Out[3]: 6931166</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total no. of reviews\n",
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">+-----------------+\n",
       "Distinct products|\n",
       "+-----------------+\n",
       "            52380|\n",
       "+-----------------+\n",
       "\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of distinct product titles\n",
    "df.select(countDistinct(\"product_title\").alias(\"Distinct products\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">+-------------------------+\n",
       "Distinct product category|\n",
       "+-------------------------+\n",
       "                       38|\n",
       "+-------------------------+\n",
       "\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of different product caetegories\n",
    "df.select(countDistinct(\"product_category\").alias(\"Distinct product category\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">+--------------------+\n",
       "Number of Customers |\n",
       "+--------------------+\n",
       "             4112395|\n",
       "+--------------------+\n",
       "\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total number of unique cutsomers\n",
    "df.select(countDistinct(\"customer_id\").alias(\"Number of Customers \")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>marketplace</th><th>customer_id</th><th>review_id</th><th>product_id</th><th>product_parent</th><th>product_title</th><th>product_category</th><th>star_rating</th><th>helpful_votes</th><th>total_votes</th><th>vine</th><th>verified_purchase</th><th>review_headline</th><th>review_body</th><th>review_date</th></tr></thead><tbody><tr><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>1</td><td>12</td><td>572</td><td>615</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for null values\n",
    "from pyspark.sql.functions import isnull, when, count, col\n",
    "display(df.select([count(when(isnull(c), c)).alias(c) for c in df.columns]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.filter(df.star_rating.isNotNull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>star_rating</th><th>count</th></tr></thead><tbody><tr><td>3</td><td>536717</td></tr><tr><td>5</td><td>4441940</td></tr><tr><td>1</td><td>406653</td></tr><tr><td>4</td><td>1266311</td></tr><tr><td>2</td><td>279544</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# distribution of star ratings\n",
    "display(df.groupby('star_rating').count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>star_rating</th><th>count</th></tr></thead><tbody><tr><td>1</td><td>406653</td></tr><tr><td>2</td><td>279544</td></tr><tr><td>3</td><td>536717</td></tr><tr><td>4</td><td>1266311</td></tr><tr><td>5</td><td>4441940</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(df.groupby('star_rating').count().orderBy('star_rating'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>product_category</th><th>count</th></tr></thead><tbody><tr><td>PC</td><td>57041</td></tr><tr><td>Lawn and Garden</td><td>1212</td></tr><tr><td>Kitchen</td><td>1843</td></tr><tr><td>Home Entertainment</td><td>36524</td></tr><tr><td>Home Improvement</td><td>3731</td></tr><tr><td>Home</td><td>2007</td></tr><tr><td>Wireless</td><td>22762</td></tr><tr><td>Video</td><td>46715</td></tr><tr><td>Digital_Video_Download</td><td>1058097</td></tr><tr><td>Luggage</td><td>78</td></tr><tr><td>Video DVD</td><td>1096886</td></tr><tr><td>Sports</td><td>4136</td></tr><tr><td>Mobile_Electronics</td><td>184</td></tr><tr><td>Digital_Ebook_Purchase</td><td>1248890</td></tr><tr><td>Baby</td><td>5902</td></tr><tr><td>Pet Products</td><td>5</td></tr><tr><td>Apparel</td><td>122</td></tr><tr><td>Grocery</td><td>18</td></tr><tr><td>Tools</td><td>7510</td></tr><tr><td>Video Games</td><td>15474</td></tr><tr><td>Electronics</td><td>11459</td></tr><tr><td>Outdoors</td><td>3195</td></tr><tr><td>Automotive</td><td>223</td></tr><tr><td>Books</td><td>838800</td></tr><tr><td>Health & Personal Care</td><td>1071</td></tr><tr><td>Music</td><td>778705</td></tr><tr><td>Furniture</td><td>8</td></tr><tr><td>Personal_Care_Appliances</td><td>9</td></tr><tr><td>Software</td><td>53</td></tr><tr><td>Mobile_Apps</td><td>1474583</td></tr><tr><td>Digital_Music_Purchase</td><td>107866</td></tr><tr><td>Camera</td><td>16978</td></tr><tr><td>Shoes</td><td>7383</td></tr><tr><td>Office Products</td><td>2313</td></tr><tr><td>Watches</td><td>10590</td></tr><tr><td>Musical Instruments</td><td>10963</td></tr><tr><td>Beauty</td><td>52</td></tr><tr><td>Toys</td><td>57777</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reviews by product_category\n",
    "display(df.groupby('product_category').count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">/local_disk0/pythonVirtualEnvDirs/virtualEnv-a6cca529-7835-4bda-b1ae-efe9d1accde9/lib/python3.7/site-packages/pyarrow/__init__.py:152: UserWarning: pyarrow.open_stream is deprecated, please use pyarrow.ipc.open_stream\n",
       "  warnings.warn(&#34;pyarrow.open_stream is deprecated, please use &#34;\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count of reviews by product category \n",
    "df_product = df.groupby(['product_category','product_title']).count().orderBy('product_category').toPandas()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Product with maximum reviews and its product category\n",
    "# display(df_product)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\">/local_disk0/pythonVirtualEnvDirs/virtualEnv-7f646259-c1ff-402e-af03-72f0e10b196c/lib/python3.7/site-packages/pyarrow/__init__.py:152: UserWarning: pyarrow.open_stream is deprecated, please use pyarrow.ipc.open_stream\n",
       "  warnings.warn(&#34;pyarrow.open_stream is deprecated, please use &#34;\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# average star rating for every product category\n",
    "category_wise_rating = df.select('product_category','star_rating')\n",
    "category_avg_rating_df = category_wise_rating.groupby('product_category').agg({'star_rating':'avg'}).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>product_category</th><th>avg(star_rating)</th></tr></thead><tbody><tr><td>PC</td><td>4.197471993829001</td></tr><tr><td>Lawn and Garden</td><td>4.128712871287129</td></tr><tr><td>Kitchen</td><td>3.9934888768312535</td></tr><tr><td>Home Entertainment</td><td>4.036797722045778</td></tr><tr><td>Home Improvement</td><td>4.131064057893326</td></tr><tr><td>Home</td><td>4.052316890881913</td></tr><tr><td>Wireless</td><td>4.031939196907126</td></tr><tr><td>Video</td><td>4.2412715401905166</td></tr><tr><td>Digital_Video_Download</td><td>4.241790686487156</td></tr><tr><td>Luggage</td><td>4.064102564102564</td></tr><tr><td>Video DVD</td><td>4.36169756930073</td></tr><tr><td>Sports</td><td>4.115570599613153</td></tr><tr><td>Mobile_Electronics</td><td>4.2228260869565215</td></tr><tr><td>Digital_Ebook_Purchase</td><td>4.350831538406105</td></tr><tr><td>Baby</td><td>4.320908166723145</td></tr><tr><td>Pet Products</td><td>4.2</td></tr><tr><td>Apparel</td><td>4.311475409836065</td></tr><tr><td>Grocery</td><td>4.555555555555555</td></tr><tr><td>Tools</td><td>4.348335552596538</td></tr><tr><td>Video Games</td><td>4.177975959674292</td></tr><tr><td>Electronics</td><td>4.089536608779126</td></tr><tr><td>Outdoors</td><td>4.199374021909233</td></tr><tr><td>Automotive</td><td>4.596412556053812</td></tr><tr><td>Books</td><td>4.305090605627086</td></tr><tr><td>Health & Personal Care</td><td>3.830999066293184</td></tr><tr><td>Music</td><td>4.425878862984057</td></tr><tr><td>Furniture</td><td>4.25</td></tr><tr><td>Personal_Care_Appliances</td><td>4.555555555555555</td></tr><tr><td>Software</td><td>4.39622641509434</td></tr><tr><td>Mobile_Apps</td><td>4.211639494012884</td></tr><tr><td>Digital_Music_Purchase</td><td>4.5709398698385035</td></tr><tr><td>Camera</td><td>4.336553186476617</td></tr><tr><td>Shoes</td><td>4.295814709467696</td></tr><tr><td>Office Products</td><td>4.008646779074795</td></tr><tr><td>Watches</td><td>4.232200188857413</td></tr><tr><td>Musical Instruments</td><td>4.25850588342607</td></tr><tr><td>Beauty</td><td>4.25</td></tr><tr><td>Toys</td><td>4.453294563580664</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# average star rating for product category\n",
    "display(category_avg_rating_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot product category average star rating sorted in desceding order\n",
    "sns.set(rc={'figure.figsize':(20, 10)})\n",
    "sorted_df = category_avg_rating_df.sort_values(['avg(star_rating)'], ascending=False).reset_index(drop=True)\n",
    "sns.barplot(x=\"avg(star_rating)\", y=\"product_category\", data=sorted_df,\n",
    "            label=\"Total\", color=\"green\")\n",
    "display(sns.despine())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>product_category</th><th>avg(total_votes)</th><th>avg(helpful_votes)</th></tr></thead><tbody><tr><td>Mobile_Electronics</td><td>9.076086956521738</td><td>6.967391304347826</td></tr><tr><td>Video</td><td>6.967055549609333</td><td>3.9151022155624533</td></tr><tr><td>Books</td><td>6.0892584644730565</td><td>4.022758702908917</td></tr><tr><td>Video DVD</td><td>4.690281396608216</td><td>2.541025229604535</td></tr><tr><td>Music</td><td>4.437005027577838</td><td>2.7280228070963974</td></tr><tr><td>Office Products</td><td>3.8945092952875053</td><td>3.4198011240812796</td></tr><tr><td>Kitchen</td><td>3.252306022788931</td><td>2.8616386326641345</td></tr><tr><td>Mobile_Apps</td><td>3.2435875091466535</td><td>2.5243312855227544</td></tr><tr><td>Electronics</td><td>3.1278471070774065</td><td>2.3934025656689064</td></tr><tr><td>Health & Personal Care</td><td>3.118580765639589</td><td>2.6022408963585435</td></tr><tr><td>Lawn and Garden</td><td>3.08003300330033</td><td>2.3655115511551155</td></tr><tr><td>Sports</td><td>3.0582688588007736</td><td>2.392166344294004</td></tr><tr><td>Musical Instruments</td><td>2.962054182249384</td><td>2.2482897017239805</td></tr><tr><td>Home</td><td>2.735924265072247</td><td>2.2556053811659194</td></tr><tr><td>Camera</td><td>2.715514194840382</td><td>2.2135705030038872</td></tr><tr><td>Watches</td><td>2.6295561850802645</td><td>1.9992445703493862</td></tr><tr><td>Outdoors</td><td>2.1724569640062596</td><td>1.7671361502347418</td></tr><tr><td>Video Games</td><td>2.1282796949722114</td><td>1.3267416311231743</td></tr><tr><td>Digital_Music_Purchase</td><td>2.087868281015334</td><td>1.1623866649361245</td></tr><tr><td>Toys</td><td>1.9519185835193935</td><td>1.4447963722588573</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "prod_cat_votes_df = df.groupby('product_category').agg({'total_votes':'avg', 'helpful_votes':'avg'}).toPandas()\n",
    "prod_cat_votes_sorted_df = prod_cat_votes_df.sort_values(['avg(total_votes)'], ascending=False).reset_index(drop=True)\n",
    "prod_cat_votes_sorted_df_20 = prod_cat_votes_sorted_df[:20]\n",
    "display(prod_cat_votes_sorted_df_20)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Average votes and helpful votes proportion for top 10 most voted product category\n",
    "sns.set(rc={'figure.figsize':(16, 8)})\n",
    "#fig, axs = plt.subplots(1,2,sharex='col', sharey='row')\n",
    "prod_cat_hfvotes_sorted_df = prod_cat_votes_df.sort_values(['avg(helpful_votes)'], ascending=False).reset_index(drop=True)\n",
    "prod_cat_hfvotes_sorted_df_10 = prod_cat_hfvotes_sorted_df[:10]\n",
    "prod_cat_votes_sorted_df_10 = prod_cat_votes_sorted_df[:10]\n",
    "p1 = sns.barplot(x=\"avg(total_votes)\", y=\"product_category\", data=prod_cat_votes_sorted_df_10, label = \"Total\",color=\"blue\")\n",
    "#display(sns.despine())\n",
    "p2 = sns.barplot(x=\"avg(helpful_votes)\", y=\"product_category\", data=prod_cat_hfvotes_sorted_df_10, label = \"Total\",color=\"green\")\n",
    "display(sns.despine())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>review_date</th><th>count</th></tr></thead><tbody><tr><td>null</td><td>615</td></tr><tr><td>1995</td><td>9</td></tr><tr><td>1996</td><td>192</td></tr><tr><td>1997</td><td>1753</td></tr><tr><td>1998</td><td>11274</td></tr><tr><td>1999</td><td>38859</td></tr><tr><td>2000</td><td>79138</td></tr><tr><td>2001</td><td>70521</td></tr><tr><td>2002</td><td>77795</td></tr><tr><td>2003</td><td>90169</td></tr><tr><td>2004</td><td>91171</td></tr><tr><td>2005</td><td>94313</td></tr><tr><td>2006</td><td>81077</td></tr><tr><td>2007</td><td>101795</td></tr><tr><td>2008</td><td>101786</td></tr><tr><td>2009</td><td>112706</td></tr><tr><td>2010</td><td>129830</td></tr><tr><td>2011</td><td>208085</td></tr><tr><td>2012</td><td>615314</td></tr><tr><td>2013</td><td>1428474</td></tr><tr><td>2014</td><td>2109902</td></tr><tr><td>2015</td><td>1486388</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of reviews over the 20 years\n",
    "from pyspark.sql.functions import col, year\n",
    "display(df.withColumn(\"review_date\", year(col(\"review_date\"))).groupBy(\"review_date\").count().orderBy('review_date'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#from pyspark.mllib.feature import HashingTF, IDF\n",
    "from pyspark.ml.feature import Tokenizer, StopWordsRemover, CountVectorizer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#parsed_review_headline_text = df.rdd.map(lambda line: line['review_headline'].lower().split(' ') if line['review_headline'] else [])\n",
    "#parsed_review_headline_text.take(5)\n",
    "tokenizer = Tokenizer(inputCol=\"review_headline\", outputCol=\"headline_words\")\n",
    "tokenized = tokenizer.transform(df.filter(df.review_headline.isNotNull()))\n",
    "remover = StopWordsRemover(inputCol=\"headline_words\", outputCol=\"headline_words_filtered\")\n",
    "filtered_set = remover.transform(tokenized)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts = filtered_set.rdd.flatMap(lambda line:line['headline_words_filtered']) \\\n",
    "             .map(lambda word: (word, 1)) \\\n",
    "             .reduceByKey(lambda a, b: a + b)\n",
    "headline_word_counts = counts.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>_1</th><th>_2</th></tr></thead><tbody><tr><td>stars</td><td>970184</td></tr><tr><td>five</td><td>707029</td></tr><tr><td>great</td><td>641899</td></tr><tr><td>good</td><td>357925</td></tr><tr><td>love</td><td>266289</td></tr><tr><td>book</td><td>220111</td></tr><tr><td>best</td><td>209509</td></tr><tr><td>movie</td><td>206368</td></tr><tr><td>game</td><td>191459</td></tr><tr><td>fun</td><td>187522</td></tr><tr><td>one</td><td>166261</td></tr><tr><td>read</td><td>162397</td></tr><tr><td>four</td><td>162161</td></tr><tr><td>...</td><td>151389</td></tr><tr><td>awesome</td><td>138992</td></tr><tr><td></td><td>93707</td></tr><tr><td>excellent</td><td>93526</td></tr><tr><td>like</td><td>91524</td></tr><tr><td>amazing</td><td>81822</td></tr><tr><td>story</td><td>80898</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# top 20 most frequent words in review headline\n",
    "display(sorted(headline_word_counts, key= lambda x:x[1], reverse=True)[:20])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tokenize the words in the review body for length distribution\n",
    "body_tokenizer = Tokenizer(inputCol=\"review_body\", outputCol=\"body_words\")\n",
    "tokenized_body = body_tokenizer.transform(df.filter(df.review_body.isNotNull()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .ansiout {\n",
       "    display: block;\n",
       "    unicode-bidi: embed;\n",
       "    white-space: pre-wrap;\n",
       "    word-wrap: break-word;\n",
       "    word-break: break-all;\n",
       "    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n",
       "    font-size: 13px;\n",
       "    color: #555;\n",
       "    margin-left: 4px;\n",
       "    line-height: 19px;\n",
       "  }\n",
       "</style>\n",
       "<div class=\"ansiout\"></div>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buckets = [b for b in range(0, 200, 10)]\n",
    "rdd_histogram_data = tokenized_body.rdd.map(lambda line:len(line['body_words'])).histogram(buckets)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# distribution of length of words used by the customers in reviews\n",
    "sns.set(rc={'figure.figsize':(16, 8)})\n",
    "plt.hist(rdd_histogram_data[0][:-1], bins=rdd_histogram_data[0], weights=rdd_histogram_data[1])\n",
    "display(plt.show())\n"
   ]
  }
 ],
 "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.8"
  },
  "name": "Project_Amazon_Reviews",
  "notebookId": 1618970994997581
 },
 "nbformat": 4,
 "nbformat_minor": 1
}