Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Vine_Review_Analysis.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyPV1kUqUumH+gCyuYetAECb",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/suyinwb/Amazon_Vine_Analysis/blob/main/Vine_Review_Analysis.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kG0zZqWxJ2GD",
        "outputId": "6c5cf32b-d234-4804-a4bb-01cdb25872d7"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rIgn:1 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Connecting to security.u\r                                                                               \rIgn:2 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Connecting to security.u\r                                                                               \rGet:3 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release [696 B]\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Connecting to security.u\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Connecting to security.u\r                                                                               \rHit:4 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:5 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Get:6 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release.gpg [836 B]\n",
            "Hit:7 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:8 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Get:9 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Get:10 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:12 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:13 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ Packages [76.8 kB]\n",
            "Get:14 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Hit:15 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:16 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:17 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [930 kB]\n",
            "Get:18 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,827 kB]\n",
            "Get:19 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,474 kB]\n",
            "Get:20 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,596 kB]\n",
            "Get:21 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [936 kB]\n",
            "Get:22 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [806 kB]\n",
            "Get:23 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,252 kB]\n",
            "Get:24 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [840 kB]\n",
            "Get:25 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,035 kB]\n",
            "Get:26 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [42.8 kB]\n",
            "Fetched 15.1 MB in 6s (2,450 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ],
      "source": [
        "import os\n",
        "# Find the latest version of spark 3.0 from http://www.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.0.3'\n",
        "spark_version = 'spark-3.0.3'\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.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() "
      ]
    },
    {
      "cell_type": "code",
      "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"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oE_Dw4ZbJ9d3",
        "outputId": "7bf57c55-f0d5-4fff-f6e0-efc48baba74b"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-02-27 18:57:14--  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  6.02MB/s    in 0.2s    \n",
            "\n",
            "2022-02-27 18:57:15 (6.02 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"Vine_Review\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "eAAoiUvqJ90_"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://suyinwbs3.s3.amazonaws.com/amazon_reviews_us_Kitchen_v1_00.tsv\"\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()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PaeNN_YAKLtG",
        "outputId": "d9f2dbfe-1ae7-47e8-c91d-bae58d3f8792"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "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|   37000337|R3DT59XH7HXR9K|B00303FI0G|     529320574|Arthur Court Pape...|         Kitchen|          5|            0|          0|   N|                Y|Beautiful. Looks ...|Beautiful.  Looks...| 2015-08-31|\n",
            "|         US|   15272914|R1LFS11BNASSU8|B00JCZKZN6|     274237558|Olde Thompson Bav...|         Kitchen|          5|            0|          1|   N|                Y| Awesome & Self-ness|I personally have...| 2015-08-31|\n",
            "|         US|   36137863|R296RT05AG0AF6|B00JLIKA5C|     544675303|Progressive Inter...|         Kitchen|          5|            0|          0|   N|                Y|Fabulous and wort...|Fabulous and wort...| 2015-08-31|\n",
            "|         US|   43311049|R3V37XDZ7ZCI3L|B000GBNB8G|     491599489|Zyliss Jumbo Garl...|         Kitchen|          5|            0|          1|   N|                Y|          Five Stars|A must if you lov...| 2015-08-31|\n",
            "|         US|   13763148|R14GU232NQFYX2|B00VJ5KX9S|     353790155|1 X Premier Pizza...|         Kitchen|          5|            0|          0|   N|                Y|     Better than sex|Worth every penny...| 2015-08-31|\n",
            "|         US|   19009420| RZQH4V7L2O1PL|B00HYB5YY0|     432241873|       CHEF Aluminum|         Kitchen|          1|            1|          1|   N|                Y|Does not work on ...|The description s...| 2015-08-31|\n",
            "|         US|   40599388|R1F8JMOSPJ3KO7|B000HEBAV2|     584680984|Presto Dual Profr...|         Kitchen|          5|            0|          0|   N|                Y|Awesome! First fr...|Awesome! First fr...| 2015-08-31|\n",
            "|         US|   22719359|R1ZISGY2BWW4Z5|B0012DS4GG|     772637306|Rubbermaid Produc...|         Kitchen|          5|            0|          0|   N|                Y|          Five Stars|Very good item. Q...| 2015-08-31|\n",
            "|         US|   47478640|R17PW4I3AE5WZW|B00FLQ4EE6|     755416578|Cuisinart 12-Piec...|         Kitchen|          5|            0|          0|   N|                Y|          Five Stars|sharp and look great| 2015-08-31|\n",
            "|         US|   34195504|R3D93G1KTP6A8P|B00DBS9OTG|     648762742|Kegco 6\" Stainles...|         Kitchen|          3|            0|          0|   N|                Y|         Three Stars|Should have come ...| 2015-08-31|\n",
            "|         US|   19100570|R18TQIW1NKPUNU|B00AN9UJ68|     495720940|Cuisinart Smart S...|         Kitchen|          5|            0|          0|   N|                Y|          Five Stars|  my friend loves it| 2015-08-31|\n",
            "|         US|   10299811|R34KUNL21WU248|B00L2P0KNO|      41330497|Searzall Torch At...|         Kitchen|          4|            0|          0|   N|                Y|          Four Stars|works as expected...| 2015-08-31|\n",
            "|         US|   32687006|R2YA1ZA53X12IN|B00NQOJQXY|     191893454|Wilton 1512-1664 ...|         Kitchen|          5|            0|          0|   N|                Y|               great|               great| 2015-08-31|\n",
            "|         US|   43260893|R2ZD1IGC9UU55X|B00080QE1Q|     277442428|Magnalite Classic...|         Kitchen|          5|            1|          1|   N|                Y|         Great Pots!|Fantastic product...| 2015-08-31|\n",
            "|         US|    8067227| R9J2YMVZTUVZ7|B009VU17ZM|     414077276|Sun's Tea(TM) 20o...|         Kitchen|          5|            0|          0|   N|                Y|          Five Stars|Work great, well ...| 2015-08-31|\n",
            "|         US|   18139929|R2UUXJ0WQR0CNI|B00MY71KO2|     138697457|Adventure Time Be...|         Kitchen|          4|            0|          0|   N|                Y|              So big|holds a loooot of...| 2015-08-31|\n",
            "|         US|   12282702|R3S9QICITG73JZ|B00A6N18CK|      48180946|DecoBros 3 Tier D...|         Kitchen|          5|            0|          0|   N|                Y|It looks nice & n...|The DecoBros K cu...| 2015-08-31|\n",
            "|         US|   38649737|R3LISNJHS64PDA|B00AB8NOLS|      67759108|Brita Water Filte...|         Kitchen|          4|            0|          0|   N|                Y|          Four Stars| Met my expectations| 2015-08-31|\n",
            "|         US|   33460969|R28RB82UG4RDD5|B00FB4UPA0|      10711472|Nifty Home 24 K-C...|         Kitchen|          5|           20|         20|   N|                Y| Saves counter space|Fits under my ful...| 2015-08-31|\n",
            "|         US|   40274860|R3H0PRVII5991X|B00YMHBQ0A|     618617346|Eoonfirst Snoopy ...|         Kitchen|          5|            0|          0|   N|                Y|          Five Stars|            Love it!| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the vine_table. DataFrame\n",
        "\n",
        "vine_df = df.select(['review_id','star_rating','helpful_votes','total_votes','vine','verified_purchase'])\n",
        "vine_df.show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6zbCNOnvJ-AA",
        "outputId": "8272f9db-087e-48fa-9a74-18ee1f837d1f"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|review_id     |star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3DT59XH7HXR9K|5          |0            |0          |N   |Y                |\n",
            "|R1LFS11BNASSU8|5          |0            |1          |N   |Y                |\n",
            "|R296RT05AG0AF6|5          |0            |0          |N   |Y                |\n",
            "|R3V37XDZ7ZCI3L|5          |0            |1          |N   |Y                |\n",
            "|R14GU232NQFYX2|5          |0            |0          |N   |Y                |\n",
            "|RZQH4V7L2O1PL |1          |1            |1          |N   |Y                |\n",
            "|R1F8JMOSPJ3KO7|5          |0            |0          |N   |Y                |\n",
            "|R1ZISGY2BWW4Z5|5          |0            |0          |N   |Y                |\n",
            "|R17PW4I3AE5WZW|5          |0            |0          |N   |Y                |\n",
            "|R3D93G1KTP6A8P|3          |0            |0          |N   |Y                |\n",
            "|R18TQIW1NKPUNU|5          |0            |0          |N   |Y                |\n",
            "|R34KUNL21WU248|4          |0            |0          |N   |Y                |\n",
            "|R2YA1ZA53X12IN|5          |0            |0          |N   |Y                |\n",
            "|R2ZD1IGC9UU55X|5          |1            |1          |N   |Y                |\n",
            "|R9J2YMVZTUVZ7 |5          |0            |0          |N   |Y                |\n",
            "|R2UUXJ0WQR0CNI|4          |0            |0          |N   |Y                |\n",
            "|R3S9QICITG73JZ|5          |0            |0          |N   |Y                |\n",
            "|R3LISNJHS64PDA|4          |0            |0          |N   |Y                |\n",
            "|R28RB82UG4RDD5|5          |20           |20         |N   |Y                |\n",
            "|R3H0PRVII5991X|5          |0            |0          |N   |Y                |\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jITZhLkmY--J"
      },
      "source": [
        "### Filter the data and create a new DataFrame or table to retrieve all the rows where the total_votes count is equal to or greater than 20 to pick reviews that are more likely to be helpful and to avoid having division by zero errors later on.\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "helpful_votes_df = vine_df.filter(vine_df.total_votes > 19)\n",
        "\n",
        "helpful_votes_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "79RJL-UuLdWR",
        "outputId": "c15751bc-fa13-47a7-96f3-7c40b3165a54"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R28RB82UG4RDD5|          5|           20|         20|   N|                Y|\n",
            "|R3FB6BERWPEIJP|          4|           40|         43|   N|                Y|\n",
            "|R1D4Z38STRDQXK|          5|           53|         56|   N|                Y|\n",
            "|R1XMWJZICINIFX|          3|           20|         21|   N|                Y|\n",
            "|R20QKY1GABXFLM|          1|          272|        297|   N|                Y|\n",
            "|R3GZ7CK2O1PPG0|          2|           10|         30|   N|                Y|\n",
            "|R328FA1E6FY3F5|          5|           17|         20|   N|                N|\n",
            "|R3DH22AA5WGLLS|          5|           30|         30|   N|                N|\n",
            "|R1E0E5EFZSLJCS|          1|           66|         80|   N|                Y|\n",
            "|R1TXZQWEHYWEWN|          2|           48|         51|   N|                Y|\n",
            "| RQQAI8YL3UCY2|          5|           23|         25|   N|                Y|\n",
            "|R3PZNAQREFUNJ7|          4|           28|         35|   N|                Y|\n",
            "|R1ZUNUSUEA5D0T|          2|           17|         20|   N|                Y|\n",
            "|R3SHJSHTY8AXKR|          5|           69|         72|   N|                Y|\n",
            "|R2QM1JXA2XI7XA|          3|           61|         68|   N|                Y|\n",
            "|R1SVJ6XDBCQNSN|          3|           29|         30|   N|                Y|\n",
            "|R1IEABNX71GK3B|          1|           54|         58|   N|                N|\n",
            "|R16QL1GNQVT4G9|          5|          117|        119|   N|                Y|\n",
            "|R1MV4YDD6O5UME|          5|           30|         32|   N|                N|\n",
            "|R3A8EHXA0JX7WV|          5|           18|         24|   N|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yoLXmEQwNjFR"
      },
      "source": [
        "### Filter the new DataFrame or table created in Step 1 and 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"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "greater_than_fifty_df = helpful_votes_df.filter(helpful_votes_df.helpful_votes / helpful_votes_df.total_votes >= 0.5)\n",
        "greater_than_fifty_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qOZFujvrMAK2",
        "outputId": "c23d809e-232a-4661-dd23-ac369603cd43"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R28RB82UG4RDD5|          5|           20|         20|   N|                Y|\n",
            "|R3FB6BERWPEIJP|          4|           40|         43|   N|                Y|\n",
            "|R1D4Z38STRDQXK|          5|           53|         56|   N|                Y|\n",
            "|R1XMWJZICINIFX|          3|           20|         21|   N|                Y|\n",
            "|R20QKY1GABXFLM|          1|          272|        297|   N|                Y|\n",
            "|R328FA1E6FY3F5|          5|           17|         20|   N|                N|\n",
            "|R3DH22AA5WGLLS|          5|           30|         30|   N|                N|\n",
            "|R1E0E5EFZSLJCS|          1|           66|         80|   N|                Y|\n",
            "|R1TXZQWEHYWEWN|          2|           48|         51|   N|                Y|\n",
            "| RQQAI8YL3UCY2|          5|           23|         25|   N|                Y|\n",
            "|R3PZNAQREFUNJ7|          4|           28|         35|   N|                Y|\n",
            "|R1ZUNUSUEA5D0T|          2|           17|         20|   N|                Y|\n",
            "|R3SHJSHTY8AXKR|          5|           69|         72|   N|                Y|\n",
            "|R2QM1JXA2XI7XA|          3|           61|         68|   N|                Y|\n",
            "|R1SVJ6XDBCQNSN|          3|           29|         30|   N|                Y|\n",
            "|R1IEABNX71GK3B|          1|           54|         58|   N|                N|\n",
            "|R16QL1GNQVT4G9|          5|          117|        119|   N|                Y|\n",
            "|R1MV4YDD6O5UME|          5|           30|         32|   N|                N|\n",
            "|R3A8EHXA0JX7WV|          5|           18|         24|   N|                N|\n",
            "|R2M7TEVV9YSLTR|          5|           94|         96|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tsS2J_7_PPV3"
      },
      "source": [
        "### Filter the DataFrame or table created in Step 2, and create 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"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "paid_program_df = greater_than_fifty_df.filter(greater_than_fifty_df.vine == 'Y')\n",
        "\n",
        "paid_program_df.show()\n",
        "paid_program_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kuJlOaGnPa0o",
        "outputId": "f4bb5cf8-67e1-4e03-a47e-fa658d7c91b7"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R1Z71RW4J9IK93|          5|           20|         22|   Y|                N|\n",
            "|R3FVB5QI11KI9Q|          4|          192|        200|   Y|                N|\n",
            "|R2G027YBMVXV6Y|          5|           39|         48|   Y|                N|\n",
            "|R1QGBAN7BMGWRR|          5|          121|        129|   Y|                N|\n",
            "|R2NH2AU7XL9ZDZ|          3|           18|         20|   Y|                N|\n",
            "|R2YVVJ9NOPNX50|          4|           36|         41|   Y|                N|\n",
            "|R1XH1LK1FWX3OS|          4|          214|        238|   Y|                N|\n",
            "|R38LSQ71G2IZGS|          5|           26|         29|   Y|                N|\n",
            "|R2FLITQVKWXDF4|          3|           26|         34|   Y|                N|\n",
            "|R25LMMZF3DJTWY|          2|           18|         21|   Y|                N|\n",
            "|R18NC69T0KEC00|          5|           20|         23|   Y|                N|\n",
            "| ROYYZ0D9LMYS3|          5|           25|         33|   Y|                N|\n",
            "|R223CE6ERRBPRZ|          4|           82|         87|   Y|                N|\n",
            "|R1THPHKHO1BPRZ|          5|           15|         20|   Y|                N|\n",
            "|R11K4PQS62ROLS|          5|           72|         75|   Y|                N|\n",
            "| R6AZL5SA22ITL|          1|           66|         70|   Y|                N|\n",
            "| R4J2O4EHK73PR|          3|           46|         63|   Y|                N|\n",
            "| RMBQSVTJWY2A2|          5|           26|         28|   Y|                N|\n",
            "| RXLOQIB7S4OKN|          5|           31|         33|   Y|                N|\n",
            "| RLG9MEHTAGWL6|          5|           57|         63|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1207"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3VqAdd4dP8e6"
      },
      "source": [
        "### Repeat Step 3, but this time retrieve all the rows where the review was not part of the Vine program (unpaid), vine == 'N'.\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "unpaid_program_df = greater_than_fifty_df.filter(greater_than_fifty_df.vine == 'N')\n",
        "\n",
        "unpaid_program_df.show()\n",
        "unpaid_program_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "514914ae-2026-414a-dd3e-ad1255e6e5da",
        "id": "T5bGgrwbQETz"
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R28RB82UG4RDD5|          5|           20|         20|   N|                Y|\n",
            "|R3FB6BERWPEIJP|          4|           40|         43|   N|                Y|\n",
            "|R1D4Z38STRDQXK|          5|           53|         56|   N|                Y|\n",
            "|R1XMWJZICINIFX|          3|           20|         21|   N|                Y|\n",
            "|R20QKY1GABXFLM|          1|          272|        297|   N|                Y|\n",
            "|R328FA1E6FY3F5|          5|           17|         20|   N|                N|\n",
            "|R3DH22AA5WGLLS|          5|           30|         30|   N|                N|\n",
            "|R1E0E5EFZSLJCS|          1|           66|         80|   N|                Y|\n",
            "|R1TXZQWEHYWEWN|          2|           48|         51|   N|                Y|\n",
            "| RQQAI8YL3UCY2|          5|           23|         25|   N|                Y|\n",
            "|R3PZNAQREFUNJ7|          4|           28|         35|   N|                Y|\n",
            "|R1ZUNUSUEA5D0T|          2|           17|         20|   N|                Y|\n",
            "|R3SHJSHTY8AXKR|          5|           69|         72|   N|                Y|\n",
            "|R2QM1JXA2XI7XA|          3|           61|         68|   N|                Y|\n",
            "|R1SVJ6XDBCQNSN|          3|           29|         30|   N|                Y|\n",
            "|R1IEABNX71GK3B|          1|           54|         58|   N|                N|\n",
            "|R16QL1GNQVT4G9|          5|          117|        119|   N|                Y|\n",
            "|R1MV4YDD6O5UME|          5|           30|         32|   N|                N|\n",
            "|R3A8EHXA0JX7WV|          5|           18|         24|   N|                N|\n",
            "|R2M7TEVV9YSLTR|          5|           94|         96|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "97839"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jSAbeFBOQP9S"
      },
      "source": [
        "### Determine the total number of reviews, the number of 5-star reviews, and the percentage of 5-star reviews for the two types of review (paid vs unpaid)."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# total number of reviews\n",
        "\n",
        "vine_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ac5d2c49-b618-42b8-d2e4-009c4e96318c",
        "id": "YvmOfe21QR55"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "4880466"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# the number of 5-star reviews\n",
        "\n",
        "all_five_stars_df = vine_df.filter(vine_df.star_rating == 5)\n",
        "\n",
        "all_five_stars_df.show()\n",
        "all_five_stars_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IcJmSESmQrCJ",
        "outputId": "4ea373e9-6169-4429-bea6-a7ae6ca00099"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3DT59XH7HXR9K|          5|            0|          0|   N|                Y|\n",
            "|R1LFS11BNASSU8|          5|            0|          1|   N|                Y|\n",
            "|R296RT05AG0AF6|          5|            0|          0|   N|                Y|\n",
            "|R3V37XDZ7ZCI3L|          5|            0|          1|   N|                Y|\n",
            "|R14GU232NQFYX2|          5|            0|          0|   N|                Y|\n",
            "|R1F8JMOSPJ3KO7|          5|            0|          0|   N|                Y|\n",
            "|R1ZISGY2BWW4Z5|          5|            0|          0|   N|                Y|\n",
            "|R17PW4I3AE5WZW|          5|            0|          0|   N|                Y|\n",
            "|R18TQIW1NKPUNU|          5|            0|          0|   N|                Y|\n",
            "|R2YA1ZA53X12IN|          5|            0|          0|   N|                Y|\n",
            "|R2ZD1IGC9UU55X|          5|            1|          1|   N|                Y|\n",
            "| R9J2YMVZTUVZ7|          5|            0|          0|   N|                Y|\n",
            "|R3S9QICITG73JZ|          5|            0|          0|   N|                Y|\n",
            "|R28RB82UG4RDD5|          5|           20|         20|   N|                Y|\n",
            "|R3H0PRVII5991X|          5|            0|          0|   N|                Y|\n",
            "|R2XF4NLCR35WY1|          5|            0|          0|   N|                Y|\n",
            "|R1RPRYRORRW85E|          5|            0|          0|   N|                Y|\n",
            "| RI9U64JG8XAWF|          5|            0|          0|   N|                Y|\n",
            "| RUSCBX0GK79R3|          5|            0|          0|   N|                Y|\n",
            "|R2S0LEB4UYZBHL|          5|            1|          1|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "3128560"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#percentage of 5-star reviews for paid review\n",
        "\n",
        "five_star_paid = paid_program_df.filter(paid_program_df.star_rating == 5).count()\n",
        "\n",
        "five_star_paid_percentage = five_star_paid / paid_program_df.count() * 100\n",
        "\n",
        "print('Percentage of 5 stars paid review is %f%% at %d reviews' %(five_star_paid_percentage , five_star_paid))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WcSNl9INRSFW",
        "outputId": "9bf1d749-0430-4462-e979-2c090527b0a8"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Percentage of 5 stars paid review is 42.170671% at 509 reviews\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#percentage of 5-star reviews for paid review\n",
        "\n",
        "five_star_unpaid = unpaid_program_df.filter(unpaid_program_df.star_rating == 5).count()\n",
        "\n",
        "five_star_unpaid_percentage = five_star_unpaid / unpaid_program_df.count() * 100\n",
        "\n",
        "print('Percentage of 5 stars unpaid review is %f%% at %d reviews' %(five_star_unpaid_percentage , five_star_unpaid))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HU3pjVpOUejw",
        "outputId": "c1e238a3-726b-40c0-ee1f-4ed7f34f4ea9"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Percentage of 5 stars unpaid review is 46.870880% at 45858 reviews\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D_-9c50bWlNM"
      },
      "source": [
        "### ADDITIONAL INFORMATION for ReadMe Analysis\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# 5 stars from helpful votes df\n",
        "\n",
        "helpful_votes_df.filter(helpful_votes_df.star_rating == 5).count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3wOlsdXSWd96",
        "outputId": "af2c7a6b-4c8e-4c6f-8644-c459000e200e"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "47059"
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# total reviews from helpful votes df \n",
        "\n",
        "helpful_votes_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ozvTaJS0WePl",
        "outputId": "e66425a3-b13a-43b8-9f96-60611384c8db"
      },
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "107421"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# 5 stars from greater than fifty df\n",
        "\n",
        "greater_than_fifty_df.filter(greater_than_fifty_df.star_rating == 5).count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8JGBAT1IZpYq",
        "outputId": "649dfbb6-6650-47d8-bfef-42062bbcef1d"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "46367"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# total reviews from greater than fifty df\n",
        "\n",
        "greater_than_fifty_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7SUtg8dZZ8dA",
        "outputId": "ffdfd306-c6ff-4463-c5d6-38ae3a457197"
      },
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "99046"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    }
  ]
}