Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Vine_Review_Analysis.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "lamYQjrf9Ewm",
        "outputId": "cd281f6d-9da1-4f05-d5ce-4818e1e1e6aa",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "import os\n",
        "# Find the latest version of spark 2.0  from http://www-us.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.0.0'\n",
        "spark_version = 'spark-3.0.1'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "!wget -q http://www-us.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark\n",
        "\n",
        "# Set Environment Variables\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop2.7\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\r0% [Working]\r            \rHit:1 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "\r0% [Waiting for headers] [Connected to cloud.r-project.org (13.227.209.45)] [Wa\r                                                                               \rGet:2 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "\r                                                                               \rGet:3 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "\r                                                                               \rGet:4 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "\r                                                                               \rGet:5 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "\r0% [Waiting for headers] [Connected to cloud.r-project.org (13.227.209.45)] [Co\r                                                                               \rGet:6 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "\r0% [1 InRelease gpgv 242 kB] [Connected to cloud.r-project.org (13.227.209.45)]\r                                                                               \rGet:7 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Ign:8 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Ign:9 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:10 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release [697 B]\n",
            "Hit:11 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:12 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release.gpg [836 B]\n",
            "Get:13 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [2,167 kB]\n",
            "Get:14 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,119 kB]\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,354 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [1,750 kB]\n",
            "Get:17 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,688 kB]\n",
            "Get:18 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [864 kB]\n",
            "Get:19 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [48.9 kB]\n",
            "Get:20 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ Packages [40.3 kB]\n",
            "Ign:22 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages\n",
            "Get:22 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [405 kB]\n",
            "Fetched 10.7 MB in 2s (5,104 kB/s)\n",
            "Reading package lists... Done\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dYU1Pv2PFfwF"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"BigData-Challenge\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0nG3hFFPQqe-",
        "outputId": "cdf1cd9f-8e1e-4dca-e975-0cd568d58ff1",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Video_Games_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|         US|   12039526| RTIS3L2M1F5SM|B001CXYMFS|     737716809|Thrustmaster T-Fl...|     Video Games|          5|            0|          0|   N|                Y|an amazing joysti...|Used this for Eli...| 2015-08-31|\n",
            "|         US|    9636577| R1ZV7R40OLHKD|B00M920ND6|     569686175|Tonsee 6 buttons ...|     Video Games|          5|            0|          0|   N|                Y|Definitely a sile...|Loved it,  I didn...| 2015-08-31|\n",
            "|         US|    2331478|R3BH071QLH8QMC|B0029CSOD2|      98937668|Hidden Mysteries:...|     Video Games|          1|            0|          1|   N|                Y|            One Star|poor quality work...| 2015-08-31|\n",
            "|         US|   52495923|R127K9NTSXA2YH|B00GOOSV98|      23143350|GelTabz Performan...|     Video Games|          3|            0|          0|   N|                Y|good, but could b...|nice, but tend to...| 2015-08-31|\n",
            "|         US|   14533949|R32ZWUXDJPW27Q|B00Y074JOM|     821342511|Zero Suit Samus a...|     Video Games|          4|            0|          0|   N|                Y|   Great but flawed.|Great amiibo, gre...| 2015-08-31|\n",
            "|         US|    2377552|R3AQQ4YUKJWBA6|B002UBI6W6|     328764615|Psyclone Recharge...|     Video Games|          1|            0|          0|   N|                Y|            One Star|The remote consta...| 2015-08-31|\n",
            "|         US|   17521011|R2F0POU5K6F73F|B008XHCLFO|      24234603|Protection for yo...|     Video Games|          5|            0|          0|   N|                Y|              A Must|I have a 2012-201...| 2015-08-31|\n",
            "|         US|   19676307|R3VNR804HYSMR6|B00BRA9R6A|     682267517|   Nerf 3DS XL Armor|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Perfect, kids lov...| 2015-08-31|\n",
            "|         US|     224068| R3GZTM72WA2QH|B009EPWJLA|     435241890|One Piece: Pirate...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|            Excelent| 2015-08-31|\n",
            "|         US|   48467989| RNQOY62705W1K|B0000AV7GB|     256572651|Playstation 2 Dan...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Slippery but expe...| 2015-08-31|\n",
            "|         US|     106569|R1VTIA3JTYBY02|B00008KTNN|     384411423|Metal Arms: Glitc...|     Video Games|          5|            0|          0|   N|                N|          Five Stars|Love the game. Se...| 2015-08-31|\n",
            "|         US|   48269642|R29DOU8791QZL8|B000A3IA0Y|     472622859|72 Pin Connector ...|     Video Games|          1|            0|          0|   N|                Y| Game will get stuck|Does not fit prop...| 2015-08-31|\n",
            "|         US|   52738710|R15DUT1VIJ9RJZ|B0053BQN34|     577628462|uDraw Gametablet ...|     Video Games|          2|            0|          0|   N|                Y|We have tried it ...|This was way too ...| 2015-08-31|\n",
            "|         US|   10556786|R3IMF2MQ3OU9ZM|B002I0HIMI|     988218515|NBA 2K12(Covers M...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Works great good ...| 2015-08-31|\n",
            "|         US|    2963837|R23H79DHOZTYAU|B0081EH12M|     770100932|New Trigger Grips...|     Video Games|          1|            1|          1|   N|                Y|Now i have to buy...|It did not fit th...| 2015-08-31|\n",
            "|         US|   23092109| RIV24EQAIXA4O|B005FMLZQQ|      24647669|Xbox 360 Media Re...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|perfect lightweig...| 2015-08-31|\n",
            "|         US|   23091728|R3UCNGYDVN24YB|B002BSA388|      33706205|Super Mario Galaxy 2|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|               great| 2015-08-31|\n",
            "|         US|   10712640| RUL4H4XTTN2DY|B00BUSLSAC|     829667834|Nintendo 3DS XL -...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Works beautifully...| 2015-08-31|\n",
            "|         US|   17455376|R20JF7Z4DHTNX5|B00KWF38AW|     110680188|Captain Toad:  Tr...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Kids loved the ga...| 2015-08-31|\n",
            "|         US|   14754850|R2T1AJ5MFI2260|B00BRQJYA8|     616463426|Lego Batman 2: DC...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|           Goodngame| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1bRe0SWHRBpi"
      },
      "source": [
        "#filter the dataset for total_votes being greater than or equal to 20\n",
        "vine_df = df.select([\"review_id\",\"star_rating\",\"helpful_votes\",\"total_votes\",\"vine\",\"verified_purchase\"])\n",
        "vote_count = df.filter(\"total_votes >= 20\")"
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "s7cr8elBRhuX",
        "outputId": "2f7b42ba-925d-4335-8834-de460ef3e809",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "#create a new DataFrame or table to retrieve all the rows where the number of helpful_votes divided by total_votes is equal to or greater than 50%.\n",
        "new_table = vote_count.filter(vote_count[\"helpful_votes\"]/vote_count[\"total_votes\"]>=0.5)\n",
        "new_table.show()"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|         US|    7142190| R4PKAZRQJJX14|B00QZLVCU0|     210935604|Valve - DOTA 2 St...|     Video Games|          1|           21|         34|   N|                N|What store doesn'...|Who pays 4 dollar...| 2015-08-31|\n",
            "|         US|    1085641|R2CI0Y288CC7E2|B00RHI62GY|     626589765|ONE PIECE Pirate ...|     Video Games|          1|           21|         35|   N|                Y|i request change ...|Yesterday i recei...| 2015-08-31|\n",
            "|         US|   34170274|R127WEQY2FM1T3|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          1|          147|        175|   N|                Y|Hot Mess. Don't Buy.|This is an absolu...| 2015-08-31|\n",
            "|         US|   37932477|R2FJ94555FZH32|B00WJ0Z194|     116945357|Infinity 3.0 Star...|     Video Games|          2|           55|         60|   N|                N|Some improvements...|While Infinity 3....| 2015-08-31|\n",
            "|         US|   45782027|R1U3AR67RE273L|B00W435C0Y|     406569386|       Madden NFL 16|     Video Games|          1|           51|         65|   N|                Y|CAN'T PLAY - NO E...|I've owned a vers...| 2015-08-31|\n",
            "|         US|   26953574|R3PZOXA5X1U8KW|B00WJ0Z10S|     418299376|Infinity 3.0 Star...|     Video Games|          4|           31|         36|   N|                N|A Really Great Game!|I have never play...| 2015-08-31|\n",
            "|         US|   38235948| R6KTC1OPIOIIG|B0118GJKIW|     120888661|Xbox One 500GB Co...|     Video Games|          2|           19|         34|   N|                Y| What an experience!|When I open a pro...| 2015-08-30|\n",
            "|         US|   52553659|R36O341WWXXKNP|B00ZSJU97C|     838780144|Disney Infinity 3...|     Video Games|          5|           28|         31|   N|                N|Upgrade to Disney...|We have been play...| 2015-08-30|\n",
            "|         US|   38235948|R10LZVBLQHBVJ0|B00KAI3KW2|     289195641|Microsoft Xbox On...|     Video Games|          2|          151|        198|   N|                N| What an experience!|When I open a pro...| 2015-08-30|\n",
            "|         US|   18680918|R1VR5GLGY1GE7N|B00VE2VASY|     908454240|SmaAcc Cooling Fa...|     Video Games|          1|           49|         51|   N|                Y|If you wanted a c...|Very weak fan. Sy...| 2015-08-30|\n",
            "|         US|    3407493|R2OWDAR0EDEPTF|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          1|           23|         28|   N|                N|Same game differe...|Sick of each mono...| 2015-08-29|\n",
            "|         US|   14073759|R29BOS5HMAY1LO|B00W435BL4|     281495895|Madden NFL 16 - P...|     Video Games|          5|           88|        110|   N|                N|The wife will nev...|This game is grea...| 2015-08-29|\n",
            "|         US|   52542000| R8WD0136CETA7|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          2|           99|        119|   N|                N|Same Madden as la...|The same thing pl...| 2015-08-29|\n",
            "|         US|   49781693|R16621F16PQN5A|B00W435C0Y|     406569386|       Madden NFL 16|     Video Games|          3|           24|         31|   N|                Y|The Almost, Almos...|Let me first say ...| 2015-08-29|\n",
            "|         US|   30943085|R1LXBPG1M9DDOC|B0138CZY9S|     154182641|     Google Play $10|     Video Games|          1|          102|        106|   N|                N|    Not a chance bro|This is just a st...| 2015-08-29|\n",
            "|         US|   48181118| RNEMGSUKJ4535|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          2|           21|         25|   N|                Y|  Bring Back NFL 2K!|Not happy with th...| 2015-08-29|\n",
            "|         US|   19648728|R3R2UKBFKP1IQ4|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          1|           19|         23|   N|                Y|         Still Buggy|Freezes after eve...| 2015-08-28|\n",
            "|         US|   10138904|R2T90XM5G2DBZA|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          3|           15|         22|   N|                Y|Built for lowest ...|This game feels l...| 2015-08-28|\n",
            "|         US|   18106383| R5ZMK0Y0KRBRZ|B00W435CPY|     889404124|       Madden NFL 16|     Video Games|          1|           79|         91|   N|                N|Should not be $60!!!|Same as last year...| 2015-08-28|\n",
            "|         US|   16969949|R12E2W0XOV3I2S|B00W435BL4|     281495895|Madden NFL 16 - P...|     Video Games|          1|           24|         30|   N|                Y|Misleading \"New\" ...|Finally arrived a...| 2015-08-28|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PMBbbSiMQTBf",
        "outputId": "06aab103-db56-4bb2-8f1e-e2b5ba2a02ae",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "#reate a new DataFrame or table that retrieves all the rows where a review was written as part of the Vine program (paid), vine == 'Y'\n",
        "new_table.filter(new_table[\"vine\"] == \"Y\").show()"
      ],
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|         US|   48771843|R3KKUSGFZWSUIY|B00YXO5UXG|     555176890|Turtle Beach - Ea...|     Video Games|          5|           56|         63|   Y|                N|Quality Chat Headset|Not every situati...| 2015-08-04|\n",
            "|         US|   53080186|R10FO5UKKVZBK2|B00XO041RQ|     238654494|PDP AG7 True Wire...|     Video Games|          3|           23|         23|   Y|                N|Great sounding he...|[[VIDEOID:a9ebb8e...| 2015-08-03|\n",
            "|         US|   29202184| RM4KSGEOR7MU1|B00YXO5X4M|     832423177|Turtle Beach - Ea...|     Video Games|          5|           19|         24|   Y|                N|Definitely Worth ...|[[VIDEOID:5a58574...| 2015-07-03|\n",
            "|         US|   42836457| RG7VRMYLEXD23|B00P45U43O|     231578141|Antec X-1 Cooler ...|     Video Games|          4|           22|         26|   Y|                N|A whisper quiet c...|The Antec X-1 Coo...| 2015-04-16|\n",
            "|         US|   24021862|R11O4YSCPSNL6L|B00MNPDMEQ|     925909080|Turtle Beach - Ea...|     Video Games|          3|           20|         26|   Y|                N|A high-end gaming...|As a DJ and avid ...| 2015-02-19|\n",
            "|         US|   52846213|R286MFBAJ8NPD6|B00Q5JD2DM|      11681279|Turtle Beach Ear ...|     Video Games|          5|           46|         51|   Y|                N|Sound the Way it ...|My son has been g...| 2014-12-31|\n",
            "|         US|   51369093|R1JRR530H4COA2|B00O9GVKZU|     330572424|Turtle Beach - Ea...|     Video Games|          5|           22|         28|   Y|                N|I really like the...|I really like thi...| 2014-12-16|\n",
            "|         US|   49681391| RQ5WD90PUNBU9|B00Q5JD2DM|      11681279|Turtle Beach Ear ...|     Video Games|          5|           21|         24|   Y|                N|So Vividly Clear ...|This Turtle Beach...| 2014-12-15|\n",
            "|         US|   40649038|R12648VHCQWUV9|B00Q5JD2DM|      11681279|Turtle Beach Ear ...|     Video Games|          4|           21|         28|   Y|                N|Wireless capabili...|This is a very pr...| 2014-12-09|\n",
            "|         US|   51744990|R3KAW29CJ8L6DQ|B00EDNLO32|     308920528|Turtle Beach - i3...|     Video Games|          5|           17|         20|   Y|                N|            Awesome!|These over the ea...| 2014-12-05|\n",
            "|         US|   33354780|R1OWK33OPI45KT|B00NHV0XV0|     930864747|Turtle Beach Ear ...|     Video Games|          5|           55|         58|   Y|                N|Perfect for late ...|PROS:<br /><br />...| 2014-11-09|\n",
            "|         US|   12206087|R2UP7VTED8O425|B00LSBNSJA|     221875832|PDP Wired Fight P...|     Video Games|          5|           35|         41|   Y|                N|This is the Gamec...|I love how Ninten...| 2014-11-08|\n",
            "|         US|   42247018|R3N4QAXTQCA1ED|B00KVQQJBW|     298562340|Turtle Beach - Ea...|     Video Games|          5|           18|         20|   Y|                N|Another In Turtle...|Both my son and I...| 2014-10-17|\n",
            "|         US|   16449486|R3EPHK0JGQGE91|B00NHV0XV0|     930864747|Turtle Beach Ear ...|     Video Games|          5|           21|         28|   Y|                N|      Gaming headset|When I ordered th...| 2014-10-08|\n",
            "|         US|   52689144| RCP3KRO47S0GG|B00KWHOXGI|     493304060|Disney INFINITY 2...|     Video Games|          4|           51|         58|   Y|                N|Fun for youngster...|Older players be ...| 2014-10-05|\n",
            "|         US|   11393110|R3JT907UUA1WUI|B00KSVXSZU|     523326094|Turtle Beach - Ea...|     Video Games|          4|           85|        103|   Y|                N|   Bass is excellent|I own a couple pa...| 2014-10-01|\n",
            "|         US|   49653593| RY0555ILXIYXW|B00KSQAACO|     927248163|Turtle Beach - Ea...|     Video Games|          5|           38|         50|   Y|                N|Excellent sound a...|Turtle Beach has ...| 2014-10-01|\n",
            "|         US|   49129501|R24PS9N276OXF4|B00KSQAACO|     927248163|Turtle Beach - Ea...|     Video Games|          5|          241|        262|   Y|                N|Works Great on th...|[[VIDEOID:mo2CKF0...| 2014-09-28|\n",
            "|         US|   51865210|R3R0F8ETAGTIOT|B00KSVXSZU|     523326094|Turtle Beach - Ea...|     Video Games|          5|           14|         20|   Y|                N|Great headset for...|This is an over t...| 2014-09-23|\n",
            "|         US|   52818244| ROXZOMHBY8Y9S|B00HS5GS38|     467482132|Turtle Beach - Ea...|     Video Games|          4|           21|         23|   Y|                N|Dude, Take the shot!|The Turtle Beach ...| 2014-09-13|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D09LetDmROSq",
        "outputId": "35c3f843-5edb-44dd-e3af-6ecdf1a502f7",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "#Create a new DataFrame or table that retrieves all the rows where a review was written as part of the Vine program (paid), vine == 'N'\n",
        "new_table.filter(new_table[\"vine\"] == \"N\").show()"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|         US|    7142190| R4PKAZRQJJX14|B00QZLVCU0|     210935604|Valve - DOTA 2 St...|     Video Games|          1|           21|         34|   N|                N|What store doesn'...|Who pays 4 dollar...| 2015-08-31|\n",
            "|         US|    1085641|R2CI0Y288CC7E2|B00RHI62GY|     626589765|ONE PIECE Pirate ...|     Video Games|          1|           21|         35|   N|                Y|i request change ...|Yesterday i recei...| 2015-08-31|\n",
            "|         US|   34170274|R127WEQY2FM1T3|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          1|          147|        175|   N|                Y|Hot Mess. Don't Buy.|This is an absolu...| 2015-08-31|\n",
            "|         US|   37932477|R2FJ94555FZH32|B00WJ0Z194|     116945357|Infinity 3.0 Star...|     Video Games|          2|           55|         60|   N|                N|Some improvements...|While Infinity 3....| 2015-08-31|\n",
            "|         US|   45782027|R1U3AR67RE273L|B00W435C0Y|     406569386|       Madden NFL 16|     Video Games|          1|           51|         65|   N|                Y|CAN'T PLAY - NO E...|I've owned a vers...| 2015-08-31|\n",
            "|         US|   26953574|R3PZOXA5X1U8KW|B00WJ0Z10S|     418299376|Infinity 3.0 Star...|     Video Games|          4|           31|         36|   N|                N|A Really Great Game!|I have never play...| 2015-08-31|\n",
            "|         US|   38235948| R6KTC1OPIOIIG|B0118GJKIW|     120888661|Xbox One 500GB Co...|     Video Games|          2|           19|         34|   N|                Y| What an experience!|When I open a pro...| 2015-08-30|\n",
            "|         US|   52553659|R36O341WWXXKNP|B00ZSJU97C|     838780144|Disney Infinity 3...|     Video Games|          5|           28|         31|   N|                N|Upgrade to Disney...|We have been play...| 2015-08-30|\n",
            "|         US|   38235948|R10LZVBLQHBVJ0|B00KAI3KW2|     289195641|Microsoft Xbox On...|     Video Games|          2|          151|        198|   N|                N| What an experience!|When I open a pro...| 2015-08-30|\n",
            "|         US|   18680918|R1VR5GLGY1GE7N|B00VE2VASY|     908454240|SmaAcc Cooling Fa...|     Video Games|          1|           49|         51|   N|                Y|If you wanted a c...|Very weak fan. Sy...| 2015-08-30|\n",
            "|         US|    3407493|R2OWDAR0EDEPTF|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          1|           23|         28|   N|                N|Same game differe...|Sick of each mono...| 2015-08-29|\n",
            "|         US|   14073759|R29BOS5HMAY1LO|B00W435BL4|     281495895|Madden NFL 16 - P...|     Video Games|          5|           88|        110|   N|                N|The wife will nev...|This game is grea...| 2015-08-29|\n",
            "|         US|   52542000| R8WD0136CETA7|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          2|           99|        119|   N|                N|Same Madden as la...|The same thing pl...| 2015-08-29|\n",
            "|         US|   49781693|R16621F16PQN5A|B00W435C0Y|     406569386|       Madden NFL 16|     Video Games|          3|           24|         31|   N|                Y|The Almost, Almos...|Let me first say ...| 2015-08-29|\n",
            "|         US|   30943085|R1LXBPG1M9DDOC|B0138CZY9S|     154182641|     Google Play $10|     Video Games|          1|          102|        106|   N|                N|    Not a chance bro|This is just a st...| 2015-08-29|\n",
            "|         US|   48181118| RNEMGSUKJ4535|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          2|           21|         25|   N|                Y|  Bring Back NFL 2K!|Not happy with th...| 2015-08-29|\n",
            "|         US|   19648728|R3R2UKBFKP1IQ4|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          1|           19|         23|   N|                Y|         Still Buggy|Freezes after eve...| 2015-08-28|\n",
            "|         US|   10138904|R2T90XM5G2DBZA|B00W435BU0|     487294121|       Madden NFL 16|     Video Games|          3|           15|         22|   N|                Y|Built for lowest ...|This game feels l...| 2015-08-28|\n",
            "|         US|   18106383| R5ZMK0Y0KRBRZ|B00W435CPY|     889404124|       Madden NFL 16|     Video Games|          1|           79|         91|   N|                N|Should not be $60!!!|Same as last year...| 2015-08-28|\n",
            "|         US|   16969949|R12E2W0XOV3I2S|B00W435BL4|     281495895|Madden NFL 16 - P...|     Video Games|          1|           24|         30|   N|                Y|Misleading \"New\" ...|Finally arrived a...| 2015-08-28|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "teCKOUNHYk_A",
        "outputId": "a3031409-5425-43d1-c13c-1858c4fdfc70",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# number of five star reviews\n",
        "five_star = new_table.filter(new_table[\"star_rating\"]== 5)\n",
        "five_star.cache()"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[marketplace: string, customer_id: int, review_id: string, product_id: string, product_parent: int, product_title: string, product_category: string, star_rating: int, helpful_votes: int, total_votes: int, vine: string, verified_purchase: string, review_headline: string, review_body: string, review_date: string]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O-Xo4lOqZrLY",
        "outputId": "550b732b-df37-4ee0-f853-54f1fb68f972",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "five_star.count()"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "15711"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WMTNawcgZ02f",
        "outputId": "3910bcce-f206-48fd-bd4c-0a5114527634",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "#total reviews\n",
        "new_table.count()"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "40565"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gNOvGRGxaNrq",
        "outputId": "7683294f-2f03-4e25-dff5-46680e40a963",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "#percentage of five star reviews\n",
        "five_star.count() / new_table.count()"
      ],
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.3873043263897449"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WtyXNtF1acOT",
        "outputId": "40d54d33-3a26-4aae-e9b5-adb8977f369b",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "#percentage of paid reviews\n",
        "five_star.filter(five_star[\"verified_purchase\"] == \"Y\").count()/new_table.filter(new_table[\"verified_purchase\"]== \"Y\").count()"
      ],
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.38214921828616666"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zm7bEwT9bUTJ",
        "outputId": "cfac67b8-7e28-4d1b-8af3-323f9f1a0262",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "#percentage of unpaid reviews\n",
        "five_star.filter(five_star[\"verified_purchase\"] == \"N\").count()/new_table.filter(new_table[\"verified_purchase\"]== \"N\").count()"
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.3890147411274172"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    }
  ]
}