Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "POWMEMEzcaam",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "910ad694-92a1-4608-ff82-56b8512bb176"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rGet:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "\r0% [Waiting for headers] [Waiting for headers] [1 InRelease 0 B/3,626 B 0%] [Wa\r0% [Waiting for headers] [Waiting for headers] [Waiting for headers] [Waiting f\r                                                                               \rHit:2 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "\r0% [Waiting for headers] [Waiting for headers] [Waiting for headers] [Waiting f\r0% [1 InRelease gpgv 3,626 B] [Waiting for headers] [Waiting for headers] [Wait\r                                                                               \rGet:3 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "\r                                                                               \rGet:4 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Hit:5 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Get:6 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [83.3 kB]\n",
            "Ign:7 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:8 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:9 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "Hit:10 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:11 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:12 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:13 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,323 kB]\n",
            "Get:14 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,422 kB]\n",
            "Get:15 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [913 kB]\n",
            "Get:17 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,990 kB]\n",
            "Get:18 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,546 kB]\n",
            "Get:19 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [48.3 kB]\n",
            "Fetched 11.5 MB in 4s (2,618 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.1.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-3.1.3-bin-hadoop2.7\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install pyspark"
      ],
      "metadata": {
        "id": "ZTtbE9UjcgWA",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "cc7a8ae2-9ff2-4b7d-a383-d5e4d6938ec4"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting pyspark\n",
            "  Downloading pyspark-3.3.0.tar.gz (281.3 MB)\n",
            "\u001b[K     |████████████████████████████████| 281.3 MB 44 kB/s \n",
            "\u001b[?25hCollecting py4j==0.10.9.5\n",
            "  Downloading py4j-0.10.9.5-py2.py3-none-any.whl (199 kB)\n",
            "\u001b[K     |████████████████████████████████| 199 kB 52.1 MB/s \n",
            "\u001b[?25hBuilding wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.3.0-py2.py3-none-any.whl size=281764026 sha256=bb30cf9412918282a6e318d1f74306a6396e84b2a0594aabafa1e78c8d326a99\n",
            "  Stored in directory: /root/.cache/pip/wheels/7a/8e/1b/f73a52650d2e5f337708d9f6a1750d451a7349a867f928b885\n",
            "Successfully built pyspark\n",
            "Installing collected packages: py4j, pyspark\n",
            "Successfully installed py4j-0.10.9.5 pyspark-3.3.0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"BigData-ChallengeVineReviews\").getOrCreate()"
      ],
      "metadata": {
        "id": "LKRw6ty3cgZH"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Electronics_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()"
      ],
      "metadata": {
        "id": "0aZtzwrFcgb9",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "5ae83df3-5b53-4f1c-bcc3-df9547463a76"
      },
      "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|   41409413|R2MTG1GCZLR2DK|B00428R89M|     112201306|yoomall 5M Antenn...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|       As described.| 2015-08-31|\n",
            "|         US|   49668221|R2HBOEM8LE9928|B000068O48|     734576678|Hosa GPM-103 3.5m...|     Electronics|          5|            0|          0|   N|                Y|It works as adver...|It works as adver...| 2015-08-31|\n",
            "|         US|   12338275|R1P4RW1R9FDPEE|B000GGKOG8|     614448099|Channel Master Ti...|     Electronics|          5|            1|          1|   N|                Y|          Five Stars|         Works pissa| 2015-08-31|\n",
            "|         US|   38487968|R1EBPM82ENI67M|B000NU4OTA|      72265257|LIMTECH Wall char...|     Electronics|          1|            0|          0|   N|                Y|            One Star|Did not work at all.| 2015-08-31|\n",
            "|         US|   23732619|R372S58V6D11AT|B00JOQIO6S|     308169188|Skullcandy Air Ra...|     Electronics|          5|            1|          1|   N|                Y|Overall pleased w...|Works well. Bass ...| 2015-08-31|\n",
            "|         US|   21257820|R1A4514XOYI1PD|B008NCD2LG|     976385982|Pioneer SP-BS22-L...|     Electronics|          5|            1|          1|   N|                Y|          Five Stars|The quality on th...| 2015-08-31|\n",
            "|         US|    3084991|R20D9EHB7N20V6|B00007FGUF|     670878953|C2G/Cables to Go ...|     Electronics|          5|            0|          0|   N|                Y|           Lifesaver|Wish I could give...| 2015-08-31|\n",
            "|         US|    8153674|R1WUTD8MVSROJU|B00M9V2RMM|     508452933|COOLEAD-HDMI Swit...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|         works great| 2015-08-31|\n",
            "|         US|   52246189|R1QCYLT25812DM|B00J3O9DYI|     766372886|Philips Wireless ...|     Electronics|          4|            0|          0|   N|                Y|          Four Stars|Great sound and c...| 2015-08-31|\n",
            "|         US|   41463864| R904DQPBCEM7A|B00NS1A0E4|     458130381|PlayStation 3 3D ...|     Electronics|          4|            0|          0|   N|                Y|          Four Stars|    It works well~~~| 2015-08-31|\n",
            "|         US|    2781942|R1DGA6UQIVLKZ7|B007B5V092|     152891509|JVC HAFR201A Xtre...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|           Alll good| 2015-08-31|\n",
            "|         US|     707292| RLQT3V8SMNIBH|B00IODHGVG|     717335277|Sylvania Alarm Cl...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|Love clock radio ...| 2015-08-31|\n",
            "|         US|   31463514|R3T9GZS2TMXZGM|B0035PBHX6|     249533961|Coby 8 GB 1.8-Inc...|     Electronics|          1|            0|          0|   N|                Y|            One Star|Breaks very easil...| 2015-08-31|\n",
            "|         US|   33475055|R24HVAEYP5PLDN|B00K1JJWFO|     635791633|Diamond (Original...|     Electronics|          5|            0|          1|   N|                Y|          Five Stars|Excellent gain in...| 2015-08-31|\n",
            "|         US|   16543871|R32KMAPNV5NJPJ|B00S3LJ5EA|     659360184|Kingvom 8gb 50 Ho...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|everything I expe...| 2015-08-31|\n",
            "|         US|   38472651| RC7VLPHUT6UAF|B00B5QNGN6|     564421014|JBL Ultra-Portabl...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|Love this small s...| 2015-08-31|\n",
            "|         US|   26946211|R3G1II8P4KGUAR|B00PLE8QF6|     685446417|YIPBOWPT Surface ...|     Electronics|          5|            0|          0|   N|                Y|       great product|works as advertis...| 2015-08-31|\n",
            "|         US|   10195727|R1UBFCBUALL6S5|B00GHUSIU6|     595255086|StarTech.com Mini...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|very good especia...| 2015-08-31|\n",
            "|         US|   47386264|R1WI5NISM6GAUG|B0045EJY90|     892920832|TEAC CD-P650-B Co...|     Electronics|          2|            4|          5|   N|                Y|It does not copy ...|It does not copy ...| 2015-08-31|\n",
            "|         US|   13000908|R27F4OF4BIA4LU|B003BT6BM8|     631236454|Philips SHS8100/2...|     Electronics|          2|            1|          1|   N|                Y|Did not last long...|Did not last long...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "cleaned_df = df.dropna()\n",
        "cleaned_df.show()"
      ],
      "metadata": {
        "id": "z6uYGy4NdklH",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6bd5f9ec-3c92-49f0-d06f-1a332202e89d"
      },
      "execution_count": 5,
      "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|   41409413|R2MTG1GCZLR2DK|B00428R89M|     112201306|yoomall 5M Antenn...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|       As described.| 2015-08-31|\n",
            "|         US|   49668221|R2HBOEM8LE9928|B000068O48|     734576678|Hosa GPM-103 3.5m...|     Electronics|          5|            0|          0|   N|                Y|It works as adver...|It works as adver...| 2015-08-31|\n",
            "|         US|   12338275|R1P4RW1R9FDPEE|B000GGKOG8|     614448099|Channel Master Ti...|     Electronics|          5|            1|          1|   N|                Y|          Five Stars|         Works pissa| 2015-08-31|\n",
            "|         US|   38487968|R1EBPM82ENI67M|B000NU4OTA|      72265257|LIMTECH Wall char...|     Electronics|          1|            0|          0|   N|                Y|            One Star|Did not work at all.| 2015-08-31|\n",
            "|         US|   23732619|R372S58V6D11AT|B00JOQIO6S|     308169188|Skullcandy Air Ra...|     Electronics|          5|            1|          1|   N|                Y|Overall pleased w...|Works well. Bass ...| 2015-08-31|\n",
            "|         US|   21257820|R1A4514XOYI1PD|B008NCD2LG|     976385982|Pioneer SP-BS22-L...|     Electronics|          5|            1|          1|   N|                Y|          Five Stars|The quality on th...| 2015-08-31|\n",
            "|         US|    3084991|R20D9EHB7N20V6|B00007FGUF|     670878953|C2G/Cables to Go ...|     Electronics|          5|            0|          0|   N|                Y|           Lifesaver|Wish I could give...| 2015-08-31|\n",
            "|         US|    8153674|R1WUTD8MVSROJU|B00M9V2RMM|     508452933|COOLEAD-HDMI Swit...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|         works great| 2015-08-31|\n",
            "|         US|   52246189|R1QCYLT25812DM|B00J3O9DYI|     766372886|Philips Wireless ...|     Electronics|          4|            0|          0|   N|                Y|          Four Stars|Great sound and c...| 2015-08-31|\n",
            "|         US|   41463864| R904DQPBCEM7A|B00NS1A0E4|     458130381|PlayStation 3 3D ...|     Electronics|          4|            0|          0|   N|                Y|          Four Stars|    It works well~~~| 2015-08-31|\n",
            "|         US|    2781942|R1DGA6UQIVLKZ7|B007B5V092|     152891509|JVC HAFR201A Xtre...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|           Alll good| 2015-08-31|\n",
            "|         US|     707292| RLQT3V8SMNIBH|B00IODHGVG|     717335277|Sylvania Alarm Cl...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|Love clock radio ...| 2015-08-31|\n",
            "|         US|   31463514|R3T9GZS2TMXZGM|B0035PBHX6|     249533961|Coby 8 GB 1.8-Inc...|     Electronics|          1|            0|          0|   N|                Y|            One Star|Breaks very easil...| 2015-08-31|\n",
            "|         US|   33475055|R24HVAEYP5PLDN|B00K1JJWFO|     635791633|Diamond (Original...|     Electronics|          5|            0|          1|   N|                Y|          Five Stars|Excellent gain in...| 2015-08-31|\n",
            "|         US|   16543871|R32KMAPNV5NJPJ|B00S3LJ5EA|     659360184|Kingvom 8gb 50 Ho...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|everything I expe...| 2015-08-31|\n",
            "|         US|   38472651| RC7VLPHUT6UAF|B00B5QNGN6|     564421014|JBL Ultra-Portabl...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|Love this small s...| 2015-08-31|\n",
            "|         US|   26946211|R3G1II8P4KGUAR|B00PLE8QF6|     685446417|YIPBOWPT Surface ...|     Electronics|          5|            0|          0|   N|                Y|       great product|works as advertis...| 2015-08-31|\n",
            "|         US|   10195727|R1UBFCBUALL6S5|B00GHUSIU6|     595255086|StarTech.com Mini...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|very good especia...| 2015-08-31|\n",
            "|         US|   47386264|R1WI5NISM6GAUG|B0045EJY90|     892920832|TEAC CD-P650-B Co...|     Electronics|          2|            4|          5|   N|                Y|It does not copy ...|It does not copy ...| 2015-08-31|\n",
            "|         US|   13000908|R27F4OF4BIA4LU|B003BT6BM8|     631236454|Philips SHS8100/2...|     Electronics|          2|            1|          1|   N|                Y|Did not last long...|Did not last long...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# create the vine_table. dataframe\n",
        "vine_df = cleaned_df.select([\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\", \"verified_purchase\"])\n",
        "vine_df.show()"
      ],
      "metadata": {
        "id": "frjFtnDZdkr4",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "3c285e60-0c48-4558-bff5-75e48f349070"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2MTG1GCZLR2DK|          5|            0|          0|   N|                Y|\n",
            "|R2HBOEM8LE9928|          5|            0|          0|   N|                Y|\n",
            "|R1P4RW1R9FDPEE|          5|            1|          1|   N|                Y|\n",
            "|R1EBPM82ENI67M|          1|            0|          0|   N|                Y|\n",
            "|R372S58V6D11AT|          5|            1|          1|   N|                Y|\n",
            "|R1A4514XOYI1PD|          5|            1|          1|   N|                Y|\n",
            "|R20D9EHB7N20V6|          5|            0|          0|   N|                Y|\n",
            "|R1WUTD8MVSROJU|          5|            0|          0|   N|                Y|\n",
            "|R1QCYLT25812DM|          4|            0|          0|   N|                Y|\n",
            "| R904DQPBCEM7A|          4|            0|          0|   N|                Y|\n",
            "|R1DGA6UQIVLKZ7|          5|            0|          0|   N|                Y|\n",
            "| RLQT3V8SMNIBH|          5|            0|          0|   N|                Y|\n",
            "|R3T9GZS2TMXZGM|          1|            0|          0|   N|                Y|\n",
            "|R24HVAEYP5PLDN|          5|            0|          1|   N|                Y|\n",
            "|R32KMAPNV5NJPJ|          5|            0|          0|   N|                Y|\n",
            "| RC7VLPHUT6UAF|          5|            0|          0|   N|                Y|\n",
            "|R3G1II8P4KGUAR|          5|            0|          0|   N|                Y|\n",
            "|R1UBFCBUALL6S5|          5|            0|          0|   N|                Y|\n",
            "|R1WI5NISM6GAUG|          2|            4|          5|   N|                Y|\n",
            "|R27F4OF4BIA4LU|          2|            1|          1|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Deliverable 2: Determine Bias of Vine Reviews "
      ],
      "metadata": {
        "id": "nwkGGuSIoCd0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# step 1:filter the data and create a new DataFrame or table to retrieve all the rows where the \n",
        "# total_votes count is equal to or greater than 20 to pick reviews that are more likely to be helpful \n",
        "# and to avoid having division by zero errors later on.\n",
        "from pyspark.sql.functions import col\n",
        "total_votes_df = vine_df.filter(col(\"total_votes\") >= 20)\n",
        "total_votes_df.show()"
      ],
      "metadata": {
        "id": "Hhp_TDFldkyJ",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "333f050a-77bf-4459-b696-63b03fa4ba77"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R1FBO737KD9F2N|          5|           19|         23|   N|                Y|\n",
            "|R227GSNWI6BSZV|          1|           20|         20|   N|                Y|\n",
            "|R3SJTYZBYBG4EE|          4|           99|         99|   N|                Y|\n",
            "|R248FG65D76D5Y|          1|           42|         53|   N|                Y|\n",
            "|R3B6BXFKGW52SG|          1|           32|         32|   N|                Y|\n",
            "| ROTIV4VYL31R4|          5|           26|         26|   N|                Y|\n",
            "|R3VQ59LD2LSKY7|          5|           20|         25|   N|                Y|\n",
            "| RIIGLD8JB7PX8|          1|           32|         35|   N|                Y|\n",
            "|R3MUBV21QV0IJK|          3|           77|         84|   N|                Y|\n",
            "|R1V5W0X6BKIJYA|          5|          129|        132|   N|                Y|\n",
            "|R274AKHXHYGIN6|          5|           87|        100|   N|                N|\n",
            "|R184FOUNZZ7KO8|          5|           15|         20|   Y|                N|\n",
            "| RVSZIYT9NB29U|          5|           22|         46|   N|                Y|\n",
            "|R1RDAOSARQUVFJ|          2|            6|         26|   N|                N|\n",
            "| R88RERIQDYDT5|          2|           17|         26|   N|                N|\n",
            "| RLYK7DY9F6RUP|          4|           17|         20|   N|                Y|\n",
            "|R3T31I7IKQ5LFQ|          3|           52|         55|   N|                Y|\n",
            "| RZETLPHZJFYB8|          5|           23|         24|   N|                Y|\n",
            "|R1BG5NL9AX68QE|          1|           23|         29|   N|                Y|\n",
            "|R1Y05HDL2AWCSA|          5|          140|        145|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# step 2:retrieve all the rows where the number of helpful_votes \n",
        "# divided by total_votes is equal to or greater than 50%.\n",
        "percent_votes_df = total_votes_df.withColumn('percent_votes', col('helpful_votes')/col('total_votes')).alias('percent_votes').filter(col('percent_votes') >= 0.5)\n",
        "percent_votes_df.show()"
      ],
      "metadata": {
        "id": "x20Dj2Ahny3K",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "bac89e67-e8df-4691-d22b-97ee178d3dbc"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|     percent_votes|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "|R1FBO737KD9F2N|          5|           19|         23|   N|                Y|0.8260869565217391|\n",
            "|R227GSNWI6BSZV|          1|           20|         20|   N|                Y|               1.0|\n",
            "|R3SJTYZBYBG4EE|          4|           99|         99|   N|                Y|               1.0|\n",
            "|R248FG65D76D5Y|          1|           42|         53|   N|                Y|0.7924528301886793|\n",
            "|R3B6BXFKGW52SG|          1|           32|         32|   N|                Y|               1.0|\n",
            "| ROTIV4VYL31R4|          5|           26|         26|   N|                Y|               1.0|\n",
            "|R3VQ59LD2LSKY7|          5|           20|         25|   N|                Y|               0.8|\n",
            "| RIIGLD8JB7PX8|          1|           32|         35|   N|                Y|0.9142857142857143|\n",
            "|R3MUBV21QV0IJK|          3|           77|         84|   N|                Y|0.9166666666666666|\n",
            "|R1V5W0X6BKIJYA|          5|          129|        132|   N|                Y|0.9772727272727273|\n",
            "|R274AKHXHYGIN6|          5|           87|        100|   N|                N|              0.87|\n",
            "|R184FOUNZZ7KO8|          5|           15|         20|   Y|                N|              0.75|\n",
            "| R88RERIQDYDT5|          2|           17|         26|   N|                N|0.6538461538461539|\n",
            "| RLYK7DY9F6RUP|          4|           17|         20|   N|                Y|              0.85|\n",
            "|R3T31I7IKQ5LFQ|          3|           52|         55|   N|                Y|0.9454545454545454|\n",
            "| RZETLPHZJFYB8|          5|           23|         24|   N|                Y|0.9583333333333334|\n",
            "|R1BG5NL9AX68QE|          1|           23|         29|   N|                Y|0.7931034482758621|\n",
            "|R1Y05HDL2AWCSA|          5|          140|        145|   N|                Y|0.9655172413793104|\n",
            "|R20EPERDBI56K8|          1|           30|         37|   N|                Y|0.8108108108108109|\n",
            "| RPTN47ZNQBPWE|          5|           20|         24|   N|                Y|0.8333333333333334|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# step 3:retrieves all the rows where a review was written as part of the Vine program (paid)\n",
        "paid_df = percent_votes_df.filter(col(\"vine\") == \"Y\").show()"
      ],
      "metadata": {
        "id": "Cdg3Tcw-ny6h",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "fc7a9aa4-d3c6-4045-e902-6beaafa94da5"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|     percent_votes|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "|R184FOUNZZ7KO8|          5|           15|         20|   Y|                N|              0.75|\n",
            "| R82QWN2X2OCHB|          5|          176|        208|   Y|                N|0.8461538461538461|\n",
            "|R1UYHBYE6790BU|          5|           44|         53|   Y|                N|0.8301886792452831|\n",
            "|R2J3YLX1L4EH2B|          5|          299|        321|   Y|                N|0.9314641744548287|\n",
            "|R3QDI539WTXKE2|          5|           26|         32|   Y|                N|            0.8125|\n",
            "| RQTPWY6ND2NRV|          4|           37|         37|   Y|                N|               1.0|\n",
            "| RQZSTE0KOBY2G|          4|           98|        109|   Y|                N|0.8990825688073395|\n",
            "| RF2RNZEJO0K1N|          4|           23|         26|   Y|                N|0.8846153846153846|\n",
            "| ROB6VOW41E8O5|          4|          155|        172|   Y|                N|0.9011627906976745|\n",
            "|R3ASMXPEPYY28T|          3|           11|         20|   Y|                N|              0.55|\n",
            "|R1PSDADBJRKB7N|          5|          215|        234|   Y|                N|0.9188034188034188|\n",
            "|R379PSH3FBB6CG|          5|           22|         26|   Y|                N|0.8461538461538461|\n",
            "| R4TBG0DR9Q3R2|          4|           27|         35|   Y|                N|0.7714285714285715|\n",
            "|R3LFHK362GJ77T|          4|           31|         33|   Y|                N|0.9393939393939394|\n",
            "|R1GB60TUTJB5HP|          5|           41|         45|   Y|                N|0.9111111111111111|\n",
            "|R2471U9VXGOMON|          4|          197|        206|   Y|                N|0.9563106796116505|\n",
            "|R24N1S9H53A8QR|          5|           64|         70|   Y|                N|0.9142857142857143|\n",
            "|R2S4BSWNHNX2GG|          5|           54|         61|   Y|                N|0.8852459016393442|\n",
            "|R36FG1XJKLAK36|          5|           23|         27|   Y|                N|0.8518518518518519|\n",
            "|R2ENZ835SOCA46|          4|           26|         30|   Y|                N|0.8666666666666667|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# step 4:retrieve all the rows where the review was not part of the Vine program (unpaid)\n",
        "non_paid_df = percent_votes_df.filter(col(\"vine\") == \"N\").show()"
      ],
      "metadata": {
        "id": "BZT4H0d1ny9Y",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "f06bac21-85a4-49a3-811b-c60d024180f5"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|     percent_votes|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "|R1FBO737KD9F2N|          5|           19|         23|   N|                Y|0.8260869565217391|\n",
            "|R227GSNWI6BSZV|          1|           20|         20|   N|                Y|               1.0|\n",
            "|R3SJTYZBYBG4EE|          4|           99|         99|   N|                Y|               1.0|\n",
            "|R248FG65D76D5Y|          1|           42|         53|   N|                Y|0.7924528301886793|\n",
            "|R3B6BXFKGW52SG|          1|           32|         32|   N|                Y|               1.0|\n",
            "| ROTIV4VYL31R4|          5|           26|         26|   N|                Y|               1.0|\n",
            "|R3VQ59LD2LSKY7|          5|           20|         25|   N|                Y|               0.8|\n",
            "| RIIGLD8JB7PX8|          1|           32|         35|   N|                Y|0.9142857142857143|\n",
            "|R3MUBV21QV0IJK|          3|           77|         84|   N|                Y|0.9166666666666666|\n",
            "|R1V5W0X6BKIJYA|          5|          129|        132|   N|                Y|0.9772727272727273|\n",
            "|R274AKHXHYGIN6|          5|           87|        100|   N|                N|              0.87|\n",
            "| R88RERIQDYDT5|          2|           17|         26|   N|                N|0.6538461538461539|\n",
            "| RLYK7DY9F6RUP|          4|           17|         20|   N|                Y|              0.85|\n",
            "|R3T31I7IKQ5LFQ|          3|           52|         55|   N|                Y|0.9454545454545454|\n",
            "| RZETLPHZJFYB8|          5|           23|         24|   N|                Y|0.9583333333333334|\n",
            "|R1BG5NL9AX68QE|          1|           23|         29|   N|                Y|0.7931034482758621|\n",
            "|R1Y05HDL2AWCSA|          5|          140|        145|   N|                Y|0.9655172413793104|\n",
            "|R20EPERDBI56K8|          1|           30|         37|   N|                Y|0.8108108108108109|\n",
            "| RPTN47ZNQBPWE|          5|           20|         24|   N|                Y|0.8333333333333334|\n",
            "|R3VJ8U3A8MT8II|          5|           17|         21|   N|                Y|0.8095238095238095|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# step 5:determine the total number of reviews, the number of 5-star reviews, \n",
        "# and the percentage of 5-star reviews for the two types of review (paid vs unpaid)\n",
        "from pyspark.sql.functions import col,when,count,lit\n",
        "ratings_total_df = percent_votes_df.groupBy(\"vine\").agg(\n",
        "    count(col(\"vine\")).alias(\"Total_Reviews\"),\n",
        "    count(when(col(\"star_rating\") == 5, True)).alias(\"Total_5_Star_Reviews\"),\n",
        "    (count(when(col(\"star_rating\") == 5, True))/count(col(\"vine\"))*100).alias(\"%_5_Star_to_Total\")).show()"
      ],
      "metadata": {
        "id": "MDvjcwoRnzAP",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "1c04386d-c17b-473a-947d-048ccd85d8ff"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-------------+--------------------+-----------------+\n",
            "|vine|Total_Reviews|Total_5_Star_Reviews|%_5_Star_to_Total|\n",
            "+----+-------------+--------------------+-----------------+\n",
            "|   Y|         1080|                 454|42.03703703703704|\n",
            "|   N|        49659|               23034|46.38434120703196|\n",
            "+----+-------------+--------------------+-----------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "BDatQHOfcgfm"
      },
      "execution_count": 12,
      "outputs": []
    }
  ]
}