Vine_Review_Analysis_Instruments.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": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "64sJd5RKB_FJ",
        "outputId": "51dbea8f-b3a9-4bee-c816-b06a857c4c5d"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Get:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Get:2 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Ign:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:4 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "Hit:5 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Hit:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:7 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:8 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Hit:9 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:10 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [913 kB]\n",
            "Get:11 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [83.3 kB]\n",
            "Hit:12 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:13 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,990 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,546 kB]\n",
            "Get:17 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,323 kB]\n",
            "Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,422 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 (3,212 kB/s)\n",
            "Reading package lists... Done\n",
            "--2022-10-02 00:58:06--  https://jdbc.postgresql.org/download/postgresql-42.5.0.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: 1046274 (1022K) [application/java-archive]\n",
            "Saving to: ‘postgresql-42.5.0.jar’\n",
            "\n",
            "postgresql-42.5.0.j 100%[===================>]   1022K  6.38MB/s    in 0.2s    \n",
            "\n",
            "2022-10-02 00:58:07 (6.38 MB/s) - ‘postgresql-42.5.0.jar’ saved [1046274/1046274]\n",
            "\n"
          ]
        }
      ],
      "source": [
        "import os\n",
        "# Find the latest version of spark 3.0  from http://www.apache.org/dist/spark/ \n",
        "# and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.3.0'\n",
        "\n",
        "spark_version = 'spark-3.3.0'\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-hadoop3.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop3.tgz\n",
        "!pip install -q findspark\n",
        "\n",
        "# Install Postgres Driver\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.5.0.jar\n",
        "\n",
        "# Set Environment Variables\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop3\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"Vine_Review_Analysis_Instruments\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.5.0.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "jmNeLrSSCRQ2"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Musical_Instruments_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Musical_Instruments_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T1iFUtWUCW6Z",
        "outputId": "e03149cf-0681-457e-997e-78e3019780a1"
      },
      "execution_count": 3,
      "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|   45610553| RMDCHWD0Y5OZ9|B00HH62VB6|     618218723|AGPtek® 10 Isolat...|Musical Instruments|          3|            0|          1|   N|                N|         Three Stars|Works very good, ...|2015-08-31 00:00:00|\n",
            "|         US|   14640079| RZSL0BALIYUNU|B003LRN53I|     986692292|Sennheiser HD203 ...|Musical Instruments|          5|            0|          0|   N|                Y|          Five Stars|Nice headphones a...|2015-08-31 00:00:00|\n",
            "|         US|    6111003| RIZR67JKUDBI0|B0006VMBHI|     603261968|AudioQuest LP rec...|Musical Instruments|          3|            0|          1|   N|                Y|         Three Stars|removes dust. doe...|2015-08-31 00:00:00|\n",
            "|         US|    1546619|R27HL570VNL85F|B002B55TRG|     575084461|Hohner Inc. 560BX...|Musical Instruments|          5|            0|          0|   N|                Y|I purchase these ...|I purchase these ...|2015-08-31 00:00:00|\n",
            "|         US|   12222213|R34EBU9QDWJ1GD|B00N1YPXW2|     165236328|Blue Yeti USB Mic...|Musical Instruments|          5|            0|          0|   N|                Y|          Five Stars|This is an awesom...|2015-08-31 00:00:00|\n",
            "|         US|   46018513|R1WCUI4Z1SIQEO|B001N4GRGS|     134151483|Middle Atlantic P...|Musical Instruments|          5|            0|          0|   N|                N|          Five Stars|Used to cool equi...|2015-08-31 00:00:00|\n",
            "|         US|   10225065| RL5LNO26GAVJ1|B009PJRMHQ|     694166585|Kmise 1pc Pickgua...|Musical Instruments|          2|            3|          4|   N|                Y|Will not Fit Epip...|Note- Does not Fi...|2015-08-31 00:00:00|\n",
            "|         US|    6356995|R3GYQ5W8JHP8SB|B00NKBDAZS|     446431775|Kealoha Concert U...|Musical Instruments|          5|            0|          0|   N|                Y|          Five Stars|Well built Ukulel...|2015-08-31 00:00:00|\n",
            "|         US|   35297198|R30SHYQXGG5EYC|B006MIU7U2|     125871705|Halco 80000 - MR1...|Musical Instruments|          5|            0|          0|   N|                Y|Works fine. Hope ...|Had to replace a ...|2015-08-31 00:00:00|\n",
            "|         US|   32139520|R14YLXA56NP51I|B000FIBD0I|     771888534|Gator GPTBLACK Pl...|Musical Instruments|          5|            1|          1|   N|                N|I upgraded the po...|I've owned multip...|2015-08-31 00:00:00|\n",
            "|         US|   36060782|R1ZH0HSH38IOTZ|B0002E52GG|      68535945|Hetman 1 - Light ...|Musical Instruments|          5|            0|          0|   N|                Y|My son's favourit...|Consistent qualit...|2015-08-31 00:00:00|\n",
            "|         US|    5301309|R3H53KLLC210XI|B00RZIH52G|     725541773|Dragonpad pop fil...|Musical Instruments|          4|            0|          0|   N|                Y|Great pop filter ...|by far the best p...|2015-08-31 00:00:00|\n",
            "|         US|   37472935|R3OOR877NGA8JK|B001792BAU|      46570323|DharmaObjects Rel...|Musical Instruments|          3|            0|          0|   N|                Y|                  Ok|Beautiful set. On...|2015-08-31 00:00:00|\n",
            "|         US|   33578270|R1BY7WKOZ3KMH0|B009GSKW1Y|     547963417|Musiclily SSS Pla...|Musical Instruments|          2|            0|          0|   N|                Y|           Two Stars|Bridge pickup was...|2015-08-31 00:00:00|\n",
            "|         US|   22070226| RXP1TFSWE8EG9|B0002F4TKA|     436074323|Vic Firth America...|Musical Instruments|          5|            0|          0|   N|                Y|          Five Stars|Feels good and la...|2015-08-31 00:00:00|\n",
            "|         US|   52862655|R3J44DPP12OTLJ|B00K17YFBW|      81933093|Guitar Stand for ...|Musical Instruments|          5|            0|          0|   N|                Y|Great stand... on...|I love the stand....|2015-08-31 00:00:00|\n",
            "|         US|    4427243| RFOV69SK0T676|B00EQ24HJS|     669249276|Generic 3PLY Faux...|Musical Instruments|          5|            0|          0|   N|                Y|Looks great. You ...|On time. Looks gr...|2015-08-31 00:00:00|\n",
            "|         US|   14108571|R2HUWDNW62FOL3|B00IBOYTUE|     749537231|Audio 2000 6525 3...|Musical Instruments|          1|            0|          0|   N|                Y|  Poor sound quality|I was hoping it w...|2015-08-31 00:00:00|\n",
            "|         US|   27314089|R1KSU30XZGR452|B00FBRUSAE|     792472601|Sawtooth ST-AMP-1...|Musical Instruments|          5|            0|          0|   N|                Y|Perfect for the b...|Good sound for it...|2015-08-31 00:00:00|\n",
            "|         US|   16735445|R2TZVLLTSHA07N|B0113D2QUO|     269114019|Upado Unlimited G...|Musical Instruments|          5|            1|          1|   N|                Y|It really is a mu...|Wow! I didn't exp...|2015-08-31 00:00:00|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+-------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the vine_table. DataFrame\n",
        "vine_df = df.select([\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\", \"verified_purchase\"])\n",
        "vine_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TNaFpu-fC84r",
        "outputId": "e2e7a282-25cd-437e-c3a0-761561c71a9b"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "| RMDCHWD0Y5OZ9|          3|            0|          1|   N|                N|\n",
            "| RZSL0BALIYUNU|          5|            0|          0|   N|                Y|\n",
            "| RIZR67JKUDBI0|          3|            0|          1|   N|                Y|\n",
            "|R27HL570VNL85F|          5|            0|          0|   N|                Y|\n",
            "|R34EBU9QDWJ1GD|          5|            0|          0|   N|                Y|\n",
            "|R1WCUI4Z1SIQEO|          5|            0|          0|   N|                N|\n",
            "| RL5LNO26GAVJ1|          2|            3|          4|   N|                Y|\n",
            "|R3GYQ5W8JHP8SB|          5|            0|          0|   N|                Y|\n",
            "|R30SHYQXGG5EYC|          5|            0|          0|   N|                Y|\n",
            "|R14YLXA56NP51I|          5|            1|          1|   N|                N|\n",
            "|R1ZH0HSH38IOTZ|          5|            0|          0|   N|                Y|\n",
            "|R3H53KLLC210XI|          4|            0|          0|   N|                Y|\n",
            "|R3OOR877NGA8JK|          3|            0|          0|   N|                Y|\n",
            "|R1BY7WKOZ3KMH0|          2|            0|          0|   N|                Y|\n",
            "| RXP1TFSWE8EG9|          5|            0|          0|   N|                Y|\n",
            "|R3J44DPP12OTLJ|          5|            0|          0|   N|                Y|\n",
            "| RFOV69SK0T676|          5|            0|          0|   N|                Y|\n",
            "|R2HUWDNW62FOL3|          1|            0|          0|   N|                Y|\n",
            "|R1KSU30XZGR452|          5|            0|          0|   N|                Y|\n",
            "|R2TZVLLTSHA07N|          5|            1|          1|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create a new DataFrame to retrieve all rows where total_votes count is equal to or greater than 20\n",
        "vine_vote20_df = vine_df.filter(vine_df.total_votes > 20)\n",
        "vine_vote20_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XvoOXxU3DCc8",
        "outputId": "5d3fec37-f6d5-48f8-d4f4-109f6960069e"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2243Y3OD8U6KQ|          5|           47|         61|   N|                N|\n",
            "|R2TGT0CDTCAAHW|          5|           21|         23|   N|                Y|\n",
            "| RX4D22YSXEF4P|          1|           37|         38|   N|                Y|\n",
            "|R3FL2NTLFUSPTQ|          5|           33|         37|   N|                N|\n",
            "|R3QTP3YNZXAPPF|          3|           23|         24|   N|                Y|\n",
            "|R36V6V42VN5AS5|          5|           34|         37|   N|                Y|\n",
            "|R27LZWE27BJPOB|          5|           22|         23|   N|                N|\n",
            "|  RMRD6SMF2AUQ|          3|            4|         21|   N|                N|\n",
            "| RMPCXKWX3T57Y|          1|            1|         73|   N|                N|\n",
            "|R1P7GJ0IN2BRNH|          5|           37|         37|   N|                Y|\n",
            "|R3JM8Z4SP9N3H2|          1|            4|         23|   N|                N|\n",
            "|R2J0ZZGFXKM8KR|          2|           21|         22|   N|                Y|\n",
            "|R238ZSG6TSUBNX|          5|           27|         27|   N|                Y|\n",
            "| RC6Y8GYIQZ8YU|          3|           72|         85|   N|                Y|\n",
            "| RFL3TJED8C240|          5|           13|         24|   N|                N|\n",
            "|R3D4P7A8Q1C70F|          5|           22|         27|   N|                Y|\n",
            "|R3PRNAQVQBB9M6|          1|           21|         30|   N|                Y|\n",
            "|R3JNDLZ3KACRLG|          5|           29|         33|   N|                N|\n",
            "|R1PVVYYY0Q1HZ0|          5|           21|         21|   N|                Y|\n",
            "|R1R9RU7JW0MFR2|          4|           20|         23|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create a new DataFrame where helpful_votes / total_votes is greater than 50%\n",
        "vine_helpfulTotal_df = vine_vote20_df.filter(vine_vote20_df.helpful_votes / vine_vote20_df.total_votes >= 0.5)\n",
        "vine_helpfulTotal_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "i0JCXPbSD6Fa",
        "outputId": "1df7891f-0c9e-46a1-9cd2-30381d1bb771"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2243Y3OD8U6KQ|          5|           47|         61|   N|                N|\n",
            "|R2TGT0CDTCAAHW|          5|           21|         23|   N|                Y|\n",
            "| RX4D22YSXEF4P|          1|           37|         38|   N|                Y|\n",
            "|R3FL2NTLFUSPTQ|          5|           33|         37|   N|                N|\n",
            "|R3QTP3YNZXAPPF|          3|           23|         24|   N|                Y|\n",
            "|R36V6V42VN5AS5|          5|           34|         37|   N|                Y|\n",
            "|R27LZWE27BJPOB|          5|           22|         23|   N|                N|\n",
            "|R1P7GJ0IN2BRNH|          5|           37|         37|   N|                Y|\n",
            "|R2J0ZZGFXKM8KR|          2|           21|         22|   N|                Y|\n",
            "|R238ZSG6TSUBNX|          5|           27|         27|   N|                Y|\n",
            "| RC6Y8GYIQZ8YU|          3|           72|         85|   N|                Y|\n",
            "| RFL3TJED8C240|          5|           13|         24|   N|                N|\n",
            "|R3D4P7A8Q1C70F|          5|           22|         27|   N|                Y|\n",
            "|R3PRNAQVQBB9M6|          1|           21|         30|   N|                Y|\n",
            "|R3JNDLZ3KACRLG|          5|           29|         33|   N|                N|\n",
            "|R1PVVYYY0Q1HZ0|          5|           21|         21|   N|                Y|\n",
            "|R1R9RU7JW0MFR2|          4|           20|         23|   Y|                N|\n",
            "|R1G9N0CW2982PA|          5|           21|         24|   N|                Y|\n",
            "|R2N8ZFHQBWHMDL|          2|           26|         28|   N|                Y|\n",
            "|R32L3HQ3OONLPQ|          4|           51|         54|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Analysis of Reviews Paid for by Vine"
      ],
      "metadata": {
        "id": "LBfoAV-BOogd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create a new DataFrame where the review was written as part of the paid Vine program\n",
        "vine_paid_df = vine_helpfulTotal_df.filter(vine_helpfulTotal_df.vine == 'Y')\n",
        "vine_paid_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "EJwbRfINEkvm",
        "outputId": "45142406-b898-4db2-f81b-8a59c4bd9403"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R1R9RU7JW0MFR2|          4|           20|         23|   Y|                N|\n",
            "|R19EFYNN3W8Q07|          5|           26|         32|   Y|                N|\n",
            "|R34DJ1R8AEU0SG|          5|           29|         35|   Y|                N|\n",
            "|R25P5CXK5L9RHF|          5|          146|        161|   Y|                N|\n",
            "|R2E9VZB3I4LSN5|          5|           55|         59|   Y|                N|\n",
            "| RKYLHZL7EPELX|          4|           19|         25|   Y|                N|\n",
            "|R1U13EKGQD3ZE6|          5|           22|         25|   Y|                N|\n",
            "| RYW05F1MUEF01|          5|           87|        102|   Y|                N|\n",
            "|R2SW4NXNO7HZJ5|          4|           28|         33|   Y|                N|\n",
            "|R2016NFLSUR97Y|          2|           26|         37|   Y|                N|\n",
            "| RFCV9QXJ3D79X|          3|           20|         22|   Y|                N|\n",
            "|R36779ZAY4ZT0Y|          3|           46|         50|   Y|                N|\n",
            "|R13PPTBWWV10Q6|          5|           19|         25|   Y|                N|\n",
            "|R2A3FFNJTDUBMV|          5|           20|         27|   Y|                N|\n",
            "|R3QQG4HUB214TM|          5|          111|        128|   Y|                N|\n",
            "|R3J9OSJNJB7KGM|          5|           70|         80|   Y|                N|\n",
            "| RIMYDUAAFXY2U|          5|           22|         23|   Y|                N|\n",
            "|R22CYBPLKWDK6F|          5|           47|         54|   Y|                N|\n",
            "|R1ETBDZ1LJ8CEL|          4|           28|         32|   Y|                N|\n",
            "|R28T5ISQ8AA7E5|          5|           40|         42|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total number of reviews paid for by Vine\n",
        "vine_paid_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fNvpNAe9NNcx",
        "outputId": "6475ff45-d0f0-4ac1-83df-1df3073834b7"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "59"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# DataFrame of reviews paid for by Vine that had a 5star rating\n",
        "vine_paid_star_df = vine_paid_df.filter(vine_paid_df.star_rating == 5)\n",
        "vine_paid_star_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gcaACL2LNNk1",
        "outputId": "9594bfb8-7fac-4dfe-8b58-991f83bb1bff"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R19EFYNN3W8Q07|          5|           26|         32|   Y|                N|\n",
            "|R34DJ1R8AEU0SG|          5|           29|         35|   Y|                N|\n",
            "|R25P5CXK5L9RHF|          5|          146|        161|   Y|                N|\n",
            "|R2E9VZB3I4LSN5|          5|           55|         59|   Y|                N|\n",
            "|R1U13EKGQD3ZE6|          5|           22|         25|   Y|                N|\n",
            "| RYW05F1MUEF01|          5|           87|        102|   Y|                N|\n",
            "|R13PPTBWWV10Q6|          5|           19|         25|   Y|                N|\n",
            "|R2A3FFNJTDUBMV|          5|           20|         27|   Y|                N|\n",
            "|R3QQG4HUB214TM|          5|          111|        128|   Y|                N|\n",
            "|R3J9OSJNJB7KGM|          5|           70|         80|   Y|                N|\n",
            "| RIMYDUAAFXY2U|          5|           22|         23|   Y|                N|\n",
            "|R22CYBPLKWDK6F|          5|           47|         54|   Y|                N|\n",
            "|R28T5ISQ8AA7E5|          5|           40|         42|   Y|                N|\n",
            "|R1OIXASHDMAU84|          5|           35|         40|   Y|                N|\n",
            "|R30FS6HFPQ1WKT|          5|           41|         44|   Y|                N|\n",
            "| R4KIRC319GP61|          5|           19|         23|   Y|                N|\n",
            "|R1UZW5KV87WUA1|          5|           26|         30|   Y|                N|\n",
            "|R394FJYWZ0GNFG|          5|           40|         43|   Y|                N|\n",
            "|R3QE0Q8R65G2M9|          5|           52|         56|   Y|                N|\n",
            "| RARYFB85E8YSK|          5|           25|         31|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total number of reviews paid for by Vine with a 5star rating\n",
        "vine_paid_star_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Tx7-JtM3NNr-",
        "outputId": "f914b5ff-4760-41f2-e033-57b72c52f36d"
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "34"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Percentage of reviews not paid for by Vine with 5star ratings\n",
        "vine_paid_star_df.count() / vine_paid_df.count() * 100"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Fos7KRsGN6xM",
        "outputId": "a86cb04c-b372-4fd3-f811-03dd31749892"
      },
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "57.6271186440678"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Analysis of Reviews **Not** Paid for By Vine"
      ],
      "metadata": {
        "id": "CN8hHiwqOtUb"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create a new DataFrame where the review was written as part of the paid Vine program\n",
        "vine_notPaid_df = vine_helpfulTotal_df.filter(vine_helpfulTotal_df.vine == 'N')\n",
        "vine_notPaid_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "48RNan4qE-7P",
        "outputId": "e05e8699-00bc-40ca-8c1b-8d1c0e106182"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2243Y3OD8U6KQ|          5|           47|         61|   N|                N|\n",
            "|R2TGT0CDTCAAHW|          5|           21|         23|   N|                Y|\n",
            "| RX4D22YSXEF4P|          1|           37|         38|   N|                Y|\n",
            "|R3FL2NTLFUSPTQ|          5|           33|         37|   N|                N|\n",
            "|R3QTP3YNZXAPPF|          3|           23|         24|   N|                Y|\n",
            "|R36V6V42VN5AS5|          5|           34|         37|   N|                Y|\n",
            "|R27LZWE27BJPOB|          5|           22|         23|   N|                N|\n",
            "|R1P7GJ0IN2BRNH|          5|           37|         37|   N|                Y|\n",
            "|R2J0ZZGFXKM8KR|          2|           21|         22|   N|                Y|\n",
            "|R238ZSG6TSUBNX|          5|           27|         27|   N|                Y|\n",
            "| RC6Y8GYIQZ8YU|          3|           72|         85|   N|                Y|\n",
            "| RFL3TJED8C240|          5|           13|         24|   N|                N|\n",
            "|R3D4P7A8Q1C70F|          5|           22|         27|   N|                Y|\n",
            "|R3PRNAQVQBB9M6|          1|           21|         30|   N|                Y|\n",
            "|R3JNDLZ3KACRLG|          5|           29|         33|   N|                N|\n",
            "|R1PVVYYY0Q1HZ0|          5|           21|         21|   N|                Y|\n",
            "|R1G9N0CW2982PA|          5|           21|         24|   N|                Y|\n",
            "|R2N8ZFHQBWHMDL|          2|           26|         28|   N|                Y|\n",
            "|R32L3HQ3OONLPQ|          4|           51|         54|   N|                Y|\n",
            "|R2M6QP0EEROQRC|          1|           21|         26|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total number of reviews not paid for by Vine\n",
        "vine_notPaid_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "W__MokjLFXYp",
        "outputId": "76ccc2e7-b7db-4269-d752-6b3b9f7979cc"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "13480"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# DataFrame of reviews not paid for by Vine that had a 5star rating\n",
        "vine_notPaid_star_df = vine_notPaid_df.filter(vine_notPaid_df.star_rating == 5)\n",
        "vine_notPaid_star_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pzt4QF6FFmOQ",
        "outputId": "8f5a6d62-dd7e-47ab-99f1-b84d78a33db0"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2243Y3OD8U6KQ|          5|           47|         61|   N|                N|\n",
            "|R2TGT0CDTCAAHW|          5|           21|         23|   N|                Y|\n",
            "|R3FL2NTLFUSPTQ|          5|           33|         37|   N|                N|\n",
            "|R36V6V42VN5AS5|          5|           34|         37|   N|                Y|\n",
            "|R27LZWE27BJPOB|          5|           22|         23|   N|                N|\n",
            "|R1P7GJ0IN2BRNH|          5|           37|         37|   N|                Y|\n",
            "|R238ZSG6TSUBNX|          5|           27|         27|   N|                Y|\n",
            "| RFL3TJED8C240|          5|           13|         24|   N|                N|\n",
            "|R3D4P7A8Q1C70F|          5|           22|         27|   N|                Y|\n",
            "|R3JNDLZ3KACRLG|          5|           29|         33|   N|                N|\n",
            "|R1PVVYYY0Q1HZ0|          5|           21|         21|   N|                Y|\n",
            "|R1G9N0CW2982PA|          5|           21|         24|   N|                Y|\n",
            "|R2GW9A2O1PA3Z7|          5|          387|        404|   N|                Y|\n",
            "|R2YYNS9L1J2365|          5|           24|         25|   N|                N|\n",
            "|R2OYYMSBKNH7A0|          5|           42|         51|   N|                Y|\n",
            "|R2RIP03ZYSVVRD|          5|          110|        112|   N|                Y|\n",
            "| RRTTR0MCZFXV0|          5|           26|         31|   N|                Y|\n",
            "|R2DAAU19U3VDDD|          5|           22|         24|   N|                Y|\n",
            "| RG5J9VEIXE3Q8|          5|           24|         25|   N|                Y|\n",
            "|R1N3VK5XCV7F2E|          5|           58|         62|   N|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total number of reviews not paid for by Vine with a 5star rating\n",
        "vine_notPaid_star_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HWqklPo_KO16",
        "outputId": "19b5f0f1-4cda-4c76-9161-7973d3d94e9b"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "7678"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Percentage of reviews not paid for by Vine with 5star ratings\n",
        "vine_notPaid_star_df.count() / vine_notPaid_df.count() * 100"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fT2NODOHJSPP",
        "outputId": "1807717e-17b8-4454-f9e3-966ec8db7f11"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "56.95845697329377"
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    }
  ]
}