Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Vine_Review_Analysis.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "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.8.3"
    },
    "nteract": {
      "version": "0.12.3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "V58rxea0HqSa",
        "outputId": "5e350ee3-4a52-4cf3-9d0f-4ae68facc8c6"
      },
      "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            \rGet:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.152)] [Waiting for headers] [1 \r0% [Connecting to archive.ubuntu.com (91.189.88.152)] [Waiting for headers] [Co\r                                                                               \rIgn:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.152)] [Waiting for headers] [Co\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rIgn:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rGet:4 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rHit:5 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release\n",
            "Hit:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:7 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Hit:8 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:9 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:11 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:12 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Get:14 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,383 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [1,901 kB]\n",
            "Get:17 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,718 kB]\n",
            "Get:18 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [288 kB]\n",
            "Get:19 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,153 kB]\n",
            "Get:20 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [879 kB]\n",
            "Get:21 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [318 kB]\n",
            "Get:22 http://archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 Packages [42.7 kB]\n",
            "Get:23 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [2,332 kB]\n",
            "Get:24 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [49.2 kB]\n",
            "Fetched 11.4 MB in 4s (2,545 kB/s)\n",
            "Reading package lists... Done\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_xKwTpATHqSe",
        "outputId": "a436c283-2c2f-43d4-f234-7d2b36c736b1"
      },
      "source": [
        "# Download the Postgres driver that will allow Spark to interact with Postgres.\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2021-01-27 15:19:13--  https://jdbc.postgresql.org/download/postgresql-42.2.16.jar\n",
            "Resolving jdbc.postgresql.org (jdbc.postgresql.org)... 72.32.157.228, 2001:4800:3e1:1::228\n",
            "Connecting to jdbc.postgresql.org (jdbc.postgresql.org)|72.32.157.228|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 1002883 (979K) [application/java-archive]\n",
            "Saving to: ‘postgresql-42.2.16.jar’\n",
            "\n",
            "postgresql-42.2.16. 100%[===================>] 979.38K  4.72MB/s    in 0.2s    \n",
            "\n",
            "2021-01-27 15:19:13 (4.72 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MMqDAjVS0KN9"
      },
      "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": 3,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cyBsySGuY-9V"
      },
      "source": [
        "### Load Amazon Data into Spark DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CtCmBhQJY-9Z",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c19a242c-bff8-464a-af26-a890f3c67af9"
      },
      "source": [
        "# Extract one of the review datasets, amazon_reviews_us_Books_v1\n",
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Books_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "execution_count": 4,
      "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|   25933450| RJOVP071AVAJO|0439873800|      84656342|There Was an Old ...|           Books|          5|            0|          0|   N|                Y|          Five Stars|I love it and so ...| 2015-08-31|\n",
            "|         US|    1801372|R1ORGBETCDW3AI|1623953553|     729938122|      I Saw a Friend|           Books|          5|            0|          0|   N|                Y|Please buy \"I Saw...|My wife and I ord...| 2015-08-31|\n",
            "|         US|    5782091| R7TNRFQAOUTX5|142151981X|     678139048|Black Lagoon, Vol. 6|           Books|          5|            0|          0|   N|                Y|       Shipped fast.|Great book just l...| 2015-08-31|\n",
            "|         US|   32715830|R2GANXKDIFZ6OI|014241543X|     712432151|           If I Stay|           Books|          5|            0|          0|   N|                N|          Five Stars|        So beautiful| 2015-08-31|\n",
            "|         US|   14005703|R2NYB6C3R8LVN6|1604600527|     800572372|Stars 'N Strips F...|           Books|          5|            2|          2|   N|                Y|          Five Stars|Enjoyed the autho...| 2015-08-31|\n",
            "|         US|   36205738|R13U5PBJI1H94K|0399170863|     559876774|            The Liar|           Books|          2|            1|          1|   N|                N|PREDICTABLE ALMOS...|Two or three page...| 2015-08-31|\n",
            "|         US|   44121167|R1H8UVH990F8VE|1517007240|     299984591|Devil in the Deta...|           Books|          5|            2|          2|   N|                N|The Monastery Mur...|&#34;Secrets in t...| 2015-08-31|\n",
            "|         US|   16519255|R2MC0N30WZMRQ5|0671728725|     821650353|Knowing When to S...|           Books|          5|            0|          0|   N|                Y|          Five Stars|          I love it!| 2015-08-31|\n",
            "|         US|   49361350| R2NO2HXK16Y4J|1111349533|     419457767|The American Pageant|           Books|          5|            0|          0|   N|                Y|          Five Stars|It was a great pu...| 2015-08-31|\n",
            "|         US|   11423253|R245YIAVJK82ZL|812211637X|     285887177|Punjabi C.L. Bibl...|           Books|          5|            0|          0|   N|                Y|          Five Stars|Quality product f...| 2015-08-31|\n",
            "|         US|   37536260| RFBMLBWQOZ1UM|0679413359|      58983679|The Aeneid (Every...|           Books|          5|            0|          0|   N|                Y|          Five Stars|          Very happy| 2015-08-31|\n",
            "|         US|   23614950| RHE1FEWV3KTQH|0983990662|     222024699|The YMCA of Middl...|           Books|          5|            0|          0|   N|                Y|Great history boo...|Love reading all ...| 2015-08-31|\n",
            "|         US|   12641855|R2BB9U7PHO7ETO|156852062X|     388128348|Memoirs By Harry ...|           Books|          5|            0|          1|   N|                Y|          Five Stars|such a great purc...| 2015-08-31|\n",
            "|         US|   36327169|R28XJJP754GYXO|B0007E4WJ4|     842975364|Arms of our fight...|           Books|          4|            0|          0|   N|                Y|          Four Stars|book was fine Tha...| 2015-08-31|\n",
            "|         US|   49735028|R2G519UREHRO8M|0664254969|     248307276|Presbyterian Cree...|           Books|          3|            0|          1|   N|                Y|The Presbyterian ...|The Presbyterian ...| 2015-08-31|\n",
            "|         US|   39348901| RPBKCEIN3UISD|1570913722|       9340721|Hello Ocean/Hola mar|           Books|          5|            0|          0|   N|                Y|          Five Stars|beautiful work an...| 2015-08-31|\n",
            "|         US|    5782091|R2DOB8482VGJ8W|0345313860|     630892090|The Vampire Lesta...|           Books|          5|            0|          0|   N|                Y|       Shipped fast.|This is my favori...| 2015-08-31|\n",
            "|         US|   30063158|R2XKOET5M8W36S|0670025593|     479521754|The Book of Life ...|           Books|          5|            0|          0|   N|                Y|One of my favorites!|Love Deborah Harn...| 2015-08-31|\n",
            "|         US|     125518|R377BCSTZ4D2QP|161893158X|     399670838|Disney Descendant...|           Books|          5|            1|          1|   N|                Y|... has all three...|Now my daughter h...| 2015-08-31|\n",
            "|         US|   22297413|R3A39XZ5SB97MK|007179512X|      47243107|CompTIA A+ Certif...|           Books|          5|            0|          0|   N|                Y|He said it has be...|Bought this book ...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sEoKFZNDtToA",
        "outputId": "27e92347-c4ba-4831-fa67-d5cec191717e"
      },
      "source": [
        "# Create the vine_table DataFrame\r\n",
        "vine_df = df.select([\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\", \"verified_purchase\"])\r\n",
        "vine_df.show()"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "| RJOVP071AVAJO|          5|            0|          0|   N|                Y|\n",
            "|R1ORGBETCDW3AI|          5|            0|          0|   N|                Y|\n",
            "| R7TNRFQAOUTX5|          5|            0|          0|   N|                Y|\n",
            "|R2GANXKDIFZ6OI|          5|            0|          0|   N|                N|\n",
            "|R2NYB6C3R8LVN6|          5|            2|          2|   N|                Y|\n",
            "|R13U5PBJI1H94K|          2|            1|          1|   N|                N|\n",
            "|R1H8UVH990F8VE|          5|            2|          2|   N|                N|\n",
            "|R2MC0N30WZMRQ5|          5|            0|          0|   N|                Y|\n",
            "| R2NO2HXK16Y4J|          5|            0|          0|   N|                Y|\n",
            "|R245YIAVJK82ZL|          5|            0|          0|   N|                Y|\n",
            "| RFBMLBWQOZ1UM|          5|            0|          0|   N|                Y|\n",
            "| RHE1FEWV3KTQH|          5|            0|          0|   N|                Y|\n",
            "|R2BB9U7PHO7ETO|          5|            0|          1|   N|                Y|\n",
            "|R28XJJP754GYXO|          4|            0|          0|   N|                Y|\n",
            "|R2G519UREHRO8M|          3|            0|          1|   N|                Y|\n",
            "| RPBKCEIN3UISD|          5|            0|          0|   N|                Y|\n",
            "|R2DOB8482VGJ8W|          5|            0|          0|   N|                Y|\n",
            "|R2XKOET5M8W36S|          5|            0|          0|   N|                Y|\n",
            "|R377BCSTZ4D2QP|          5|            1|          1|   N|                Y|\n",
            "|R3A39XZ5SB97MK|          5|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IeK_HDP-pQL4",
        "outputId": "9519b750-87ad-432e-dd0c-f9b7ca12d1b1"
      },
      "source": [
        "# filter the data with 20 or more total votes \r\n",
        "cleaned_vine_df = vine_df.filter('total_votes>=20')\r\n",
        "cleaned_vine_df.show()"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3T6LR4H11XRAN|          5|           26|         26|   N|                Y|\n",
            "|R3QFNFMFWOQIBO|          3|           15|         25|   N|                Y|\n",
            "|R24S2SV0ZIFIMV|          1|           41|         78|   N|                N|\n",
            "|R2QAPWF1M5863L|          1|            5|         23|   N|                N|\n",
            "|R3OCONKYE1A047|          5|           33|         38|   N|                N|\n",
            "|R28WZIA84DUP6C|          2|            4|         43|   N|                N|\n",
            "|R2U7YNJZ5JHWVD|          5|           23|         25|   N|                Y|\n",
            "|R1XVYF74WCJKO3|          1|           47|         49|   N|                N|\n",
            "|R3KKWNG5VABDYC|          5|           31|         32|   Y|                N|\n",
            "|R3KK9MHFLY2BVW|          5|           21|         22|   N|                N|\n",
            "|R2OEP8MGX5K370|          5|           30|         34|   N|                Y|\n",
            "| RTX63ENBKWCXF|          1|           28|         35|   N|                N|\n",
            "|R38I0D0J8O6LB6|          5|          101|        102|   N|                Y|\n",
            "|R16H89H2ZPISOL|          5|           66|         73|   N|                Y|\n",
            "|R1O92J5SV8J8G6|          5|          100|        102|   N|                Y|\n",
            "|R2N899RTMS7B7A|          1|           14|         20|   N|                Y|\n",
            "| RNRGC6W09WQM2|          1|            0|         45|   N|                Y|\n",
            "|R1FB2TRCTRIWWL|          1|            3|         20|   N|                N|\n",
            "| RO7NYOSZ65X1T|          1|            1|         76|   N|                N|\n",
            "|R2D9XX1FUD30NT|          1|           21|        213|   N|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RbvL-CBZp4Sd",
        "outputId": "dc57005a-42f4-4bdb-a607-112477df524d"
      },
      "source": [
        "# filter the data with number of helpful_votes divided by total_votes >= 0.5\r\n",
        "helpful_vine_df = cleaned_vine_df.filter(\"helpful_votes/total_votes >=0.5 \")\r\n",
        "helpful_vine_df.show() "
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3T6LR4H11XRAN|          5|           26|         26|   N|                Y|\n",
            "|R3QFNFMFWOQIBO|          3|           15|         25|   N|                Y|\n",
            "|R24S2SV0ZIFIMV|          1|           41|         78|   N|                N|\n",
            "|R3OCONKYE1A047|          5|           33|         38|   N|                N|\n",
            "|R2U7YNJZ5JHWVD|          5|           23|         25|   N|                Y|\n",
            "|R1XVYF74WCJKO3|          1|           47|         49|   N|                N|\n",
            "|R3KKWNG5VABDYC|          5|           31|         32|   Y|                N|\n",
            "|R3KK9MHFLY2BVW|          5|           21|         22|   N|                N|\n",
            "|R2OEP8MGX5K370|          5|           30|         34|   N|                Y|\n",
            "| RTX63ENBKWCXF|          1|           28|         35|   N|                N|\n",
            "|R38I0D0J8O6LB6|          5|          101|        102|   N|                Y|\n",
            "|R16H89H2ZPISOL|          5|           66|         73|   N|                Y|\n",
            "|R1O92J5SV8J8G6|          5|          100|        102|   N|                Y|\n",
            "|R2N899RTMS7B7A|          1|           14|         20|   N|                Y|\n",
            "|R2K2D1XN0J8SD5|          4|           19|         21|   N|                Y|\n",
            "| RERKINI37W6KC|          5|           28|         31|   N|                Y|\n",
            "| R9GDG4O1ZK10T|          1|           71|         86|   N|                Y|\n",
            "|R3GOLJQC9OI7D4|          4|           28|         28|   N|                Y|\n",
            "|R37RUX3TXFJN24|          5|           52|         58|   N|                Y|\n",
            "|R1ESSNVJW3N2K4|          5|           42|         45|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jSTGXR1cq4Yt",
        "outputId": "dc2254d1-f684-4087-8469-63ddbb32fab0"
      },
      "source": [
        "# filter the review was written as part of the Vine program (paid), vine == 'Y'\r\n",
        "helpful_paid_df = helpful_vine_df.filter(helpful_vine_df[\"vine\"] == \"Y\")\r\n",
        "helpful_paid_df.show() "
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3KKWNG5VABDYC|          5|           31|         32|   Y|                N|\n",
            "|R397PIJYYVJ7PD|          4|           31|         32|   Y|                N|\n",
            "| RP6Y2CTIFUYD0|          2|           31|         32|   Y|                N|\n",
            "|R1FYRNM5U0S2VZ|          3|           20|         24|   Y|                N|\n",
            "| R1ME19UY4UNAO|          5|           53|         57|   Y|                N|\n",
            "|R1JW7ZHT2VLT1E|          3|           40|         45|   Y|                N|\n",
            "| R98UYXY43TURV|          4|           46|         50|   Y|                N|\n",
            "|R34VXM3EJDS78Z|          3|          374|        410|   Y|                N|\n",
            "|R1QX8UOW5JO6L0|          4|           20|         21|   Y|                N|\n",
            "| R5Y5SXCRQ2GNH|          3|           34|         37|   Y|                N|\n",
            "|R2PBQNQR80F6HP|          4|           20|         20|   Y|                N|\n",
            "|R1YI5U4BF4ZLRQ|          3|           16|         20|   Y|                N|\n",
            "|R1Q5ZXA44R3E76|          4|           28|         31|   Y|                N|\n",
            "| RCLSDAIJR6FUG|          5|          113|        123|   Y|                N|\n",
            "|R30QKO5AIO2828|          5|           17|         20|   Y|                N|\n",
            "|R3TPSU0ZG7K5RA|          5|           34|         37|   Y|                N|\n",
            "|R3HSQ5EH8IIUOM|          4|           49|         63|   Y|                N|\n",
            "|R2L4DAT0DWS9P3|          4|           28|         29|   Y|                N|\n",
            "|R2TSF7WN3DY921|          5|           22|         26|   Y|                N|\n",
            "|R1LY9Y3SS9JCTR|          2|           53|        100|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "94Twj5NpshlL",
        "outputId": "dbad4bf0-a493-46f2-c4ab-f00e8edfead0"
      },
      "source": [
        "# filter the review was written as part of the the Vine program(nonpaid), vine == 'N'\r\n",
        "helpful_nonpaid_df = helpful_vine_df.filter(helpful_vine_df[\"vine\"] == \"N\")\r\n",
        "helpful_nonpaid_df.show() "
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3T6LR4H11XRAN|          5|           26|         26|   N|                Y|\n",
            "|R3QFNFMFWOQIBO|          3|           15|         25|   N|                Y|\n",
            "|R24S2SV0ZIFIMV|          1|           41|         78|   N|                N|\n",
            "|R3OCONKYE1A047|          5|           33|         38|   N|                N|\n",
            "|R2U7YNJZ5JHWVD|          5|           23|         25|   N|                Y|\n",
            "|R1XVYF74WCJKO3|          1|           47|         49|   N|                N|\n",
            "|R3KK9MHFLY2BVW|          5|           21|         22|   N|                N|\n",
            "|R2OEP8MGX5K370|          5|           30|         34|   N|                Y|\n",
            "| RTX63ENBKWCXF|          1|           28|         35|   N|                N|\n",
            "|R38I0D0J8O6LB6|          5|          101|        102|   N|                Y|\n",
            "|R16H89H2ZPISOL|          5|           66|         73|   N|                Y|\n",
            "|R1O92J5SV8J8G6|          5|          100|        102|   N|                Y|\n",
            "|R2N899RTMS7B7A|          1|           14|         20|   N|                Y|\n",
            "|R2K2D1XN0J8SD5|          4|           19|         21|   N|                Y|\n",
            "| RERKINI37W6KC|          5|           28|         31|   N|                Y|\n",
            "| R9GDG4O1ZK10T|          1|           71|         86|   N|                Y|\n",
            "|R3GOLJQC9OI7D4|          4|           28|         28|   N|                Y|\n",
            "|R37RUX3TXFJN24|          5|           52|         58|   N|                Y|\n",
            "|R1ESSNVJW3N2K4|          5|           42|         45|   N|                Y|\n",
            "|R1Q1VLGAFGHLN7|          2|           78|         85|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6ivUiBz-uhkn",
        "outputId": "9db28d0d-ebdc-4581-dc2f-1ea399d56f02"
      },
      "source": [
        "# Total number of helpful reviews\r\n",
        "helful_revNum = helpful_vine_df.count()\r\n",
        "\r\n",
        "# Determine number of 5-star helpful reviews\r\n",
        "helful_fs_revNum = helpful_vine_df.filter(helpful_vine_df[\"star_rating\"] == 5).count()\r\n",
        "\r\n",
        "# Determine percentage of 5-star paid reviews for total helpful reviewers\r\n",
        "perc_fs_helful_rev = helful_fs_revNum/helful_revNum  * 100\r\n",
        "print(\"The number of helpful reviews are \" + str(helful_revNum) + \".\")\r\n",
        "print(\"Total number of 5-star helpful reviews are \" + str(helful_fs_revNum) + \".\")\r\n",
        "print(\"The percent of 5-star reviews in helpful reviews is \" + str(round(perc_fs_helful_rev,2)) + \"%.\")"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "The number of helpful reviews are 114309.\n",
            "Total number of 5-star helpful reviews are 51998.\n",
            "The percent of 5-star reviews in helpful reviews is 45.49%.\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yUGkxhYDuhnS",
        "outputId": "05711864-2416-43e4-ca33-49797f0a1e90"
      },
      "source": [
        "# Total number of helpful paid reviews\r\n",
        "paid_Tnum = helpful_paid_df.count()\r\n",
        "# Determine number of 5-star helpful paid reviews\r\n",
        "paid_fs_Tnum = helpful_paid_df.filter(helpful_paid_df[\"star_rating\"] == 5).count()\r\n",
        "\r\n",
        "# Determine percentage of 5-star paid reviews for total helpful reviewers\r\n",
        "perc_fs_paid_tot = paid_fs_Tnum/paid_Tnum * 100\r\n",
        "\r\n",
        "print(\"The number of helpful paid reviews are \" + str(paid_Tnum) + \".\")\r\n",
        "print(\"The number of 5-star helpful paid reviews are \" + str(paid_fs_Tnum) + \".\")\r\n",
        "print(\"The percent of 5-star reviews in helpful paid reviews is \" + str(round(perc_fs_paid_tot,2)) + \"%.\")"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "The number of helpful paid reviews are 5012.\n",
            "The number of 5-star helpful paid reviews are 2031.\n",
            "The percent of 5-star reviews in helpful paid reviews is 40.52%.\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Xrp8_icPuhp6",
        "outputId": "08404918-497a-4243-ae26-cb4bca6904a2"
      },
      "source": [
        "# Total number of helpful nonpaid reviews\r\n",
        "nonpaid_Tnum = helpful_nonpaid_df.count()\r\n",
        "# Determine number of 5-star helpful nonpaid reviews\r\n",
        "nonpaid_fs_Tnum = helpful_nonpaid_df.filter(helpful_nonpaid_df[\"star_rating\"] == 5).count()\r\n",
        "\r\n",
        "# Determine percentage of 5-star nonpaid reviews for total helpful reviewers\r\n",
        "perc_fs_nonpaid_tot = nonpaid_fs_Tnum/nonpaid_Tnum * 100\r\n",
        "\r\n",
        "print(\"The number of helpful nonpaid reviews are \" + str(nonpaid_Tnum) + \".\")\r\n",
        "print(\"The number of 5-star helpful nonpaid reviews are \" + str(nonpaid_fs_Tnum) + \".\")\r\n",
        "print(\"The percent of 5-star reviews in helpful nonpaid reviews is \" + str(round(perc_fs_nonpaid_tot,2)) + \"%.\")"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "The number of helpful nonpaid reviews are 109297.\n",
            "The number of 5-star helpful nonpaid reviews are 49967.\n",
            "The percent of 5-star reviews in helpful nonpaid reviews is 45.72%.\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MFllKkhXuhu6"
      },
      "source": [
        ""
      ],
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iOfB6olQuhxq"
      },
      "source": [
        ""
      ],
      "execution_count": 12,
      "outputs": []
    }
  ]
}