Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyOZhO8B89+CTgrZaZkYFvkk",
      "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/ADMaple/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": {
        "id": "x4rLUoJm4QZy"
      },
      "outputs": [],
      "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.2.2'\n",
        "os.environ['SPARK_VERSION']=spark_version"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "\n",
        "# install spark (change the version number if needed)\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",
        "\n",
        "# unzip the spark file to the current folder\n",
        "!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jtO1IZ7F4TWO",
        "outputId": "f6bdf52e-6937-48f2-f966-20ad86ba1bbc"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Ign:1 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "Hit:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:4 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Get:5 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Hit:6 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Hit:7 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Get:8 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Get:9 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [985 kB]\n",
            "Hit:10 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\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",
            "Hit:14 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Get:15 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,467 kB]\n",
            "Get:16 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,332 kB]\n",
            "Get:17 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [3,035 kB]\n",
            "Get:18 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,554 kB]\n",
            "Fetched 11.6 MB in 4s (2,901 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# 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\""
      ],
      "metadata": {
        "id": "o9W7oLiK4YPl"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ],
      "metadata": {
        "id": "uYOcGlNV4f5z"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "skmlL5zT5ZNt",
        "outputId": "65fd367e-80f5-4c50-d237-06bfbb74feaa"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-10-22 02:24:08--  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-10-22 02:24:08 (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(\"CloudETL\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "T-XtDBIU5xxW"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Read in data from S3 Buckets\n",
        "from pyspark import SparkFiles\n",
        "url =\"https://adrianmaplebucket.s3.amazonaws.com/amazon_reviews_us_Outdoors_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "amazon_data_df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"\"), sep=\"\\t\", header=True, inferSchema=True)"
      ],
      "metadata": {
        "id": "-nhy3ZIb6SnR"
      },
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Show DataFrame\n",
        "amazon_data_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LuSTeOpR_n_I",
        "outputId": "9480c2b1-8dbb-473c-ff9f-ebff0ed01414"
      },
      "execution_count": 8,
      "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|   18446823|R35T75OLUGHL5C|B000NV6H94|     110804376|Stearns Youth Boa...|        Outdoors|          4|            0|          0|   N|                Y|          Four Stars|          GOOD VALUE| 2015-08-31|\n",
            "|         US|   13724367|R2BV735O46BN33|B000IN0W3Y|     624096774|Primal Wear Men's...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|  Excellent quality.| 2015-08-31|\n",
            "|         US|   51001958|R2NBEUGPQQGXP1|B008RBJXFM|     278970944|Osprey Hydraulics...|        Outdoors|          4|            0|          0|   N|                Y|Only Flaw Is The Cap|3rd season using ...| 2015-08-31|\n",
            "|         US|   32866903|R17LLAOJ8ITK0S|B00FK8WUQY|     312877650|CamelBak eddy .75...|        Outdoors|          3|            1|          1|   N|                Y|Poor design leads...|poor construction...| 2015-08-31|\n",
            "|         US|   30907790|R39PEQBT5ISEF4|B00EZA3VW0|     305567912|Children Black Re...|        Outdoors|          1|            0|          0|   N|                Y|Very bad quality,...|Very bad quality,...| 2015-08-31|\n",
            "|         US|   20232229|R3GNM3SU9VHJFT|B006JA8WEG|     842306035|Ibera Bicycle Tri...|        Outdoors|          4|            1|          1|   N|                Y|Nice bag. Should ...|Nice bag. Should ...| 2015-08-31|\n",
            "|         US|   17698862| R2Y81OP0EK467|B002PWFSEO|     451480122|Therm-a-Rest Comp...|        Outdoors|          5|            0|          0|   N|                Y|Very comfortable ...|Gave this to my s...| 2015-08-31|\n",
            "|         US|   38486114|R2LFGSI6HAYH5F|B002DZGKHW|     124386306|Sawyer Products P...|        Outdoors|          5|            1|          1|   N|                Y| Worked like a charm|Went on vacation ...| 2015-08-31|\n",
            "|         US|   26319572|R297G6ED1IQO7W|B00ABA08F6|     991442421|Zippo Hand Warmer...|        Outdoors|          5|            1|          1|   N|                Y|Great item. Gets ...|Great item. Gets ...| 2015-08-31|\n",
            "|         US|   27152337| RE27RFC6101N6|B003Z8WIHC|     886483892|Camp Chef Dutch O...|        Outdoors|          5|            0|          0|   N|                Y|Great value for t...|I am so glad I bo...| 2015-08-31|\n",
            "|         US|   12516845|R3BPDME6E94W8Z|B007CP6UK0|     150224054|3CERA Portable Wi...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|        good to have| 2015-08-31|\n",
            "|         US|    3225242|R2P08O1RILUOX3|B003V3U9JK|     343847969|Texsport King Kot...|        Outdoors|          3|            0|          0|   N|                Y|Cot set up inconv...|VERY difficult to...| 2015-08-31|\n",
            "|         US|     961839|R37CVAB03PTDVI|B00Y846HN8|     858088629|Wallygadgets 2 Wh...|        Outdoors|          5|            0|          1|   N|                Y|          Five Stars|Thanks excellent ...| 2015-08-31|\n",
            "|         US|   47796452| RAWNWOGXPCPMD|B00IYQ84VY|     474493517|RainStoppers 34-I...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|This umbrella is ...| 2015-08-31|\n",
            "|         US|   32004835| R5DYGP6ASX77M|B002MYCKLY|     920014456|Alpha Deluxe Port...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|Love it !! I even...| 2015-08-31|\n",
            "|         US|   23972939|R1O0SAOOGF2KG7|B00EZV69JG|     128489321|Speedfil Z4 BTA B...|        Outdoors|          4|            0|          0|   N|                Y|        Good enough.|This is a fine mo...| 2015-08-31|\n",
            "|         US|   40889047|R35NJUT0U3MU3V|B00AWOT3T8|     571303876|O'Brien Kids Plat...|        Outdoors|          5|            0|          0|   N|                Y| Got Up on First Try|We just bought th...| 2015-08-31|\n",
            "|         US|   11244387|R242C08MF9D1AH|B0000AXTID|     739769424|Kwik-Tek F-5R Pla...|        Outdoors|          5|            0|          0|   N|                Y|They go over an a...|I have these on m...| 2015-08-31|\n",
            "|         US|   20121211| R3RYG8TJTO4E2|B00IFHFJXI|     984009972|Ivation Portable ...|        Outdoors|          5|            0|          0|   N|                Y|Greatest Item I b...|This is the best ...| 2015-08-31|\n",
            "|         US|   25657249|R3IKH1DNY0CP9F|B00KFILTWU|     405521681|GreenInsync Repla...|        Outdoors|          2|            0|          0|   N|                Y|I received this p...|I received this p...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Create the vine Data Frame**\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "ujVEkEIVIDvg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "vine_df = amazon_data_df.select([\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\", \"verified_purchase\"])\n",
        "vine_df.show()"
      ],
      "metadata": {
        "id": "5PikqRe3_xb2",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d7ab5f94-a966-4a06-cbe2-8d2b7dcd8c49"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R35T75OLUGHL5C|          4|            0|          0|   N|                Y|\n",
            "|R2BV735O46BN33|          5|            0|          0|   N|                Y|\n",
            "|R2NBEUGPQQGXP1|          4|            0|          0|   N|                Y|\n",
            "|R17LLAOJ8ITK0S|          3|            1|          1|   N|                Y|\n",
            "|R39PEQBT5ISEF4|          1|            0|          0|   N|                Y|\n",
            "|R3GNM3SU9VHJFT|          4|            1|          1|   N|                Y|\n",
            "| R2Y81OP0EK467|          5|            0|          0|   N|                Y|\n",
            "|R2LFGSI6HAYH5F|          5|            1|          1|   N|                Y|\n",
            "|R297G6ED1IQO7W|          5|            1|          1|   N|                Y|\n",
            "| RE27RFC6101N6|          5|            0|          0|   N|                Y|\n",
            "|R3BPDME6E94W8Z|          5|            0|          0|   N|                Y|\n",
            "|R2P08O1RILUOX3|          3|            0|          0|   N|                Y|\n",
            "|R37CVAB03PTDVI|          5|            0|          1|   N|                Y|\n",
            "| RAWNWOGXPCPMD|          5|            0|          0|   N|                Y|\n",
            "| R5DYGP6ASX77M|          5|            0|          0|   N|                Y|\n",
            "|R1O0SAOOGF2KG7|          4|            0|          0|   N|                Y|\n",
            "|R35NJUT0U3MU3V|          5|            0|          0|   N|                Y|\n",
            "|R242C08MF9D1AH|          5|            0|          0|   N|                Y|\n",
            "| R3RYG8TJTO4E2|          5|            0|          0|   N|                Y|\n",
            "|R3IKH1DNY0CP9F|          2|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# filter Data Frame for total_votes above or equal to 20\n",
        "votes_df = amazon_data_df.filter(vine_df.total_votes >= 20)\n",
        "votes_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TG5hiJLz_4Xg",
        "outputId": "3838eb5f-601b-4c2c-fbfe-9f26e94ae9a5"
      },
      "execution_count": 14,
      "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|   30222858|R2FP3U4NHNFNL2|B00YPISPNC|     468413405|Stanley Classic V...|        Outdoors|          5|           25|         29|   N|                Y|GREAT GROWLER! NO...|This item is awes...| 2015-08-31|\n",
            "|         US|   35677754|R1UUK1977O38MU|B00T8NEI3A|      32341693|Camping Wood Burn...|        Outdoors|          5|           31|         31|   N|                Y|    Dinner is served|worked great, lov...| 2015-08-31|\n",
            "|         US|   45781324| RXO216LWUDV6O|B00FLTZ2ZS|     361297724|DNM Mountain Bike...|        Outdoors|          3|           29|         31|   N|                Y|         Air leak :(|I just got this a...| 2015-08-31|\n",
            "|         US|   16699467|R3NMJF7EBMM19V|B007SZ4XJ4|     188745514|VeloChampion MLT1...|        Outdoors|          3|           26|         27|   N|                Y|Good quality, act...|The only reason I...| 2015-08-31|\n",
            "|         US|   16299390|R2ZY0ZBDUO0XUY|B00NAINBM8|     754535833|Earthtrekgear Fol...|        Outdoors|          3|           20|         21|   N|                Y|Short and (almost...|It is almost impo...| 2015-08-31|\n",
            "|         US|   30196513| RBDVHX7PMQEKM|B00OAUPSSA|     877639199|Insta-Bed Raised ...|        Outdoors|          5|           35|         39|   N|                Y|   Nice air mattress|Nice air mattress...| 2015-08-31|\n",
            "|         US|   13750464| RGO3KSDXYWTGU|B00V65XX0C|     379510044|OuterEQ 0.8L Outd...|        Outdoors|          4|           26|         26|   N|                Y|      Almost perfect|This is a nice li...| 2015-08-31|\n",
            "|         US|    6930362|R2O4P39D0ALCQK|B00NPLSZF8|     968147881|Ultra Bright LED ...|        Outdoors|          2|           14|         43|   N|                Y|You may be better...|After seeing this...| 2015-08-31|\n",
            "|         US|   14428825|R1E1PQGHWGO3Q1|B007ZYIFQ0|     184431888|Igloo Iceless The...|        Outdoors|          1|           89|         96|   N|                Y|        Doesn't work|Cord melted the f...| 2015-08-31|\n",
            "|         US|   42845702|R1XOXXUVYZEDJB|B0087NHUWS|     740768441|     Ribz Front Pack|        Outdoors|          5|           22|         22|   N|                Y|Nice compliment t...|Nicely compliment...| 2015-08-31|\n",
            "|         US|   13557708| R7SHITUU5UG9U|B00NFCFNUM|     608702364|Kelty Salida 2 Pe...|        Outdoors|          5|           27|         27|   N|                Y|Worth it with lot...|I bought this ite...| 2015-08-31|\n",
            "|         US|   15323553|R230TLHU91M5Y3|B010X5715I|     302429091|SYRINX High Quali...|        Outdoors|          4|           21|         21|   N|                Y|  Great little mount|Great little moun...| 2015-08-31|\n",
            "|         US|   32928343| RHR5MKO23K9TU|B00CMAG6BI|     401191323|Camp Chef Italia ...|        Outdoors|          5|           26|         30|   N|                Y|Well worth the pr...|I have been tryin...| 2015-08-31|\n",
            "|         US|    3046458|R1HGK3TCY161D7|B011OLKB3O|     653374459|Stanley Adventure...|        Outdoors|          5|           52|         55|   N|                N|Great for chunky ...|I got this thermo...| 2015-08-31|\n",
            "|         US|   51111929|R340QYAVNZZHJA|B005LURMSW|     340426618|Mountainsmith Con...|        Outdoors|          4|           25|         25|   N|                N|Spacious Tent, Hi...|My wife and I sea...| 2015-08-31|\n",
            "|         US|     923391|R2F1UVQK0JL4QH|B00AHSRM1A|      15004904|Raider Ultralight...|        Outdoors|          3|           20|         20|   N|                Y|Compact- Seems we...|The &#34;Guy Line...| 2015-08-31|\n",
            "|         US|   43108390|R1N21UY546VWMN|B00DZLVDAC|     212774904|ExOfficio Men's G...|        Outdoors|          2|           48|         50|   N|                Y|These fit great, ...|These fit great, ...| 2015-08-31|\n",
            "|         US|   13629614|R1E4L344EK0ZOT|B012BRMTHG|     283537352|Cal 7 22 Inch Com...|        Outdoors|          5|           20|         26|   N|                N|This was a perfec...|This was a perfec...| 2015-08-31|\n",
            "|         US|   35439949|R282QUZDDJW3ON|B000NVBH8A|     136509543|Stearns Adult Cla...|        Outdoors|          4|           45|         49|   N|                Y|Universal for all...|Get universal unl...| 2015-08-31|\n",
            "|         US|   13260359|R3H5T3T7QY3ZL2|B00Y846HN8|     858088629|Wallygadgets 2 Wh...|        Outdoors|          5|          247|        263|   N|                Y|BEWARE OF THE SCA...|first off let me ...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Filter Data Frame for helpful_votes ratio above or equal to 50%\n",
        "votes_df_2 = votes_df.filter((votes_df.helpful_votes / votes_df.total_votes) >= 0.5)\n",
        "votes_df_2.show()"
      ],
      "metadata": {
        "id": "Tu_eMV9RHEmJ",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "0db61911-2cc1-492b-85ac-9ea9f5547c3a"
      },
      "execution_count": 15,
      "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|   30222858|R2FP3U4NHNFNL2|B00YPISPNC|     468413405|Stanley Classic V...|        Outdoors|          5|           25|         29|   N|                Y|GREAT GROWLER! NO...|This item is awes...| 2015-08-31|\n",
            "|         US|   35677754|R1UUK1977O38MU|B00T8NEI3A|      32341693|Camping Wood Burn...|        Outdoors|          5|           31|         31|   N|                Y|    Dinner is served|worked great, lov...| 2015-08-31|\n",
            "|         US|   45781324| RXO216LWUDV6O|B00FLTZ2ZS|     361297724|DNM Mountain Bike...|        Outdoors|          3|           29|         31|   N|                Y|         Air leak :(|I just got this a...| 2015-08-31|\n",
            "|         US|   16699467|R3NMJF7EBMM19V|B007SZ4XJ4|     188745514|VeloChampion MLT1...|        Outdoors|          3|           26|         27|   N|                Y|Good quality, act...|The only reason I...| 2015-08-31|\n",
            "|         US|   16299390|R2ZY0ZBDUO0XUY|B00NAINBM8|     754535833|Earthtrekgear Fol...|        Outdoors|          3|           20|         21|   N|                Y|Short and (almost...|It is almost impo...| 2015-08-31|\n",
            "|         US|   30196513| RBDVHX7PMQEKM|B00OAUPSSA|     877639199|Insta-Bed Raised ...|        Outdoors|          5|           35|         39|   N|                Y|   Nice air mattress|Nice air mattress...| 2015-08-31|\n",
            "|         US|   13750464| RGO3KSDXYWTGU|B00V65XX0C|     379510044|OuterEQ 0.8L Outd...|        Outdoors|          4|           26|         26|   N|                Y|      Almost perfect|This is a nice li...| 2015-08-31|\n",
            "|         US|   14428825|R1E1PQGHWGO3Q1|B007ZYIFQ0|     184431888|Igloo Iceless The...|        Outdoors|          1|           89|         96|   N|                Y|        Doesn't work|Cord melted the f...| 2015-08-31|\n",
            "|         US|   42845702|R1XOXXUVYZEDJB|B0087NHUWS|     740768441|     Ribz Front Pack|        Outdoors|          5|           22|         22|   N|                Y|Nice compliment t...|Nicely compliment...| 2015-08-31|\n",
            "|         US|   13557708| R7SHITUU5UG9U|B00NFCFNUM|     608702364|Kelty Salida 2 Pe...|        Outdoors|          5|           27|         27|   N|                Y|Worth it with lot...|I bought this ite...| 2015-08-31|\n",
            "|         US|   15323553|R230TLHU91M5Y3|B010X5715I|     302429091|SYRINX High Quali...|        Outdoors|          4|           21|         21|   N|                Y|  Great little mount|Great little moun...| 2015-08-31|\n",
            "|         US|   32928343| RHR5MKO23K9TU|B00CMAG6BI|     401191323|Camp Chef Italia ...|        Outdoors|          5|           26|         30|   N|                Y|Well worth the pr...|I have been tryin...| 2015-08-31|\n",
            "|         US|    3046458|R1HGK3TCY161D7|B011OLKB3O|     653374459|Stanley Adventure...|        Outdoors|          5|           52|         55|   N|                N|Great for chunky ...|I got this thermo...| 2015-08-31|\n",
            "|         US|   51111929|R340QYAVNZZHJA|B005LURMSW|     340426618|Mountainsmith Con...|        Outdoors|          4|           25|         25|   N|                N|Spacious Tent, Hi...|My wife and I sea...| 2015-08-31|\n",
            "|         US|     923391|R2F1UVQK0JL4QH|B00AHSRM1A|      15004904|Raider Ultralight...|        Outdoors|          3|           20|         20|   N|                Y|Compact- Seems we...|The &#34;Guy Line...| 2015-08-31|\n",
            "|         US|   43108390|R1N21UY546VWMN|B00DZLVDAC|     212774904|ExOfficio Men's G...|        Outdoors|          2|           48|         50|   N|                Y|These fit great, ...|These fit great, ...| 2015-08-31|\n",
            "|         US|   13629614|R1E4L344EK0ZOT|B012BRMTHG|     283537352|Cal 7 22 Inch Com...|        Outdoors|          5|           20|         26|   N|                N|This was a perfec...|This was a perfec...| 2015-08-31|\n",
            "|         US|   35439949|R282QUZDDJW3ON|B000NVBH8A|     136509543|Stearns Adult Cla...|        Outdoors|          4|           45|         49|   N|                Y|Universal for all...|Get universal unl...| 2015-08-31|\n",
            "|         US|   13260359|R3H5T3T7QY3ZL2|B00Y846HN8|     858088629|Wallygadgets 2 Wh...|        Outdoors|          5|          247|        263|   N|                Y|BEWARE OF THE SCA...|first off let me ...| 2015-08-31|\n",
            "|         US|   48844345|R3W1A1P95IIGHX|B00Z8V5OV6|      37436668|Ezisoul Large 32o...|        Outdoors|          5|           23|         25|   N|                N|This one is HANDS...|I have a few wate...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create paid vine Data Frame\n",
        "paid_df = votes_df_2.filter(votes_df_2.vine == 'Y')\n",
        "paid_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uXQUPLLzBo3S",
        "outputId": "22b945cd-210b-449c-e3ab-984b0b5ba8a9"
      },
      "execution_count": 16,
      "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|   43335941|R3KPC0NBUDASX3|B00R8KC02Q|     872035750|Thule EnRoute Tri...|        Outdoors|          5|           25|         25|   Y|                N|Love the Thule ba...|THULE:<br /><br /...| 2015-08-24|\n",
            "|         US|   36470546|R119P2A95GGXX4|B00NOYKVSK|     165748383|Wenzel Temp Contr...|        Outdoors|          5|           26|         28|   Y|                N|Very Comfy--Here'...|This sleeping bag...| 2015-07-09|\n",
            "|         US|   50794278|R1HKIRME8AJ89Z|B00GK4LUXQ|     737005436|Klymit Inertia O ...|        Outdoors|          5|           79|         82|   Y|                N|A sleeping pad li...|[[VIDEOID:4513a9b...| 2015-07-04|\n",
            "|         US|   44173076|R3FY3GMBGOBR22|B00NFCFDRA|     585483297|Kelty Dualist 6 D...|        Outdoors|          5|           12|         20|   Y|                N|The Cadillac of s...|This Kelty Sleepi...| 2015-06-16|\n",
            "|         US|   43856165|R15KH3FBSVYGBU|B00NFCFIR0|     167877886|Kelty Tuck 22 Deg...|        Outdoors|          5|           32|         37|   Y|                N|Compared to Kelty...|I own both the [[...| 2015-06-14|\n",
            "|         US|   25821331|R22XZEQLVLWW49|B00NFCFAYQ|     771093954|Cosmic Down 20 De...|        Outdoors|          5|           28|         33|   Y|                N|A really great sl...|Wow!  Having just...| 2015-06-11|\n",
            "|         US|   11556116|R2OIQYVAL9D63R|B00NFCFIR0|     167877886|Kelty Tuck 22 Deg...|        Outdoors|          4|           59|         65|   Y|                N|Like the Foot fea...|I already own thr...| 2015-06-09|\n",
            "|         US|   11556116|R1W3KCDHM6BMZT|B00G6KURDI|     463959079|Kelty TN 2 Person...|        Outdoors|          4|           37|         38|   Y|                N|Light weight, roo...|I like the rolled...| 2015-06-05|\n",
            "|         US|   18267561|R3KDMPS96IJ10R|B00R8K9VZA|     306686399|Thule RideAlong M...|        Outdoors|          2|           15|         21|   Y|                N|Awkward, unstable...|Despite the slick...| 2015-05-10|\n",
            "|         US|   13722260|R1XWEI58A7VKLB|B00R6SBMPG|     141037234|Thule Vertex XT H...|        Outdoors|          5|          123|        128|   Y|                N|The best hitch ra...|First off, this t...| 2015-05-08|\n",
            "|         US|   50599697|R1CJWM6CMWKSA1|B00M542ILI|     410973207|Thule Raceway Pro...|        Outdoors|          4|           31|         33|   Y|                N|Recommended. Grea...|From an owner and...| 2015-05-05|\n",
            "|         US|   41604883|R39I3FUDBKZM3Y|B00R6SBMPG|     141037234|Thule Vertex XT H...|        Outdoors|          5|           34|         38|   Y|                N|  Solid in every way|Best bike carrier...| 2015-05-01|\n",
            "|         US|   51250504|R32LU0KSL7QWSF|B00R6SBML0|     485306161|Thule Vertex Swin...|        Outdoors|          5|           35|         37|   Y|                N|Excellent - Swing...|This is a wonderf...| 2015-05-01|\n",
            "|         US|   31302915|R2LGDXO3MG2WQW|B00U4HDLGO|     706335403|Thule Urban Glide...|        Outdoors|          5|           37|         41|   Y|                N|Thule Glide 2 for...|We might have a n...| 2015-04-19|\n",
            "|         US|   44341716|R16AVI2NTYWLKI|B00R8K9VZA|     306686399|Thule RideAlong M...|        Outdoors|          5|           31|         33|   Y|                N|Great way to ride...|We have 3 childre...| 2015-04-03|\n",
            "|         US|   45351550|R12K0ITIV6AYM3|B00FZLIK1U|     156536278|Thule Cadence Chi...|        Outdoors|          5|           34|         34|   Y|                N|Excellent dedicat...|Above the fold re...| 2015-03-24|\n",
            "|         US|   45351550| R9ILZLNNK93CO|B00FOUMCD4|     129685188|Thule Coaster2+ B...|        Outdoors|          5|           38|         39|   Y|                N|More dialed in to...|In 2011 Thule dec...| 2015-03-23|\n",
            "|         US|   45351550|R1CS8VSI1KSR1P|B00KNTHH2C|     347242708|Thule Glide - Per...|        Outdoors|          5|           21|         22|   Y|                N|Glide or Urban Gl...|Above the fold ve...| 2015-03-23|\n",
            "|         US|   11556116|R186TCIH8TEGPY|B00SISWNZG|     959754939|Mountain House Sc...|        Outdoors|          5|           40|         43|   Y|                N|Mountain house br...|I just love these...| 2015-03-04|\n",
            "|         US|   52532594| R3GBQRR2GY4SP|B00SISWXVK|     994639471|Mountain House Br...|        Outdoors|          4|          119|        125|   Y|                N|A postitive exper...|I have NEVER had ...| 2015-02-27|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create unpaid vine Data Frame\n",
        "unpaid_df = votes_df_2.filter(votes_df_2.vine == 'N')\n",
        "unpaid_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "09Pl9E4ONVLl",
        "outputId": "a603d448-3d6d-4512-d52e-9086a32445d1"
      },
      "execution_count": 17,
      "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|   30222858|R2FP3U4NHNFNL2|B00YPISPNC|     468413405|Stanley Classic V...|        Outdoors|          5|           25|         29|   N|                Y|GREAT GROWLER! NO...|This item is awes...| 2015-08-31|\n",
            "|         US|   35677754|R1UUK1977O38MU|B00T8NEI3A|      32341693|Camping Wood Burn...|        Outdoors|          5|           31|         31|   N|                Y|    Dinner is served|worked great, lov...| 2015-08-31|\n",
            "|         US|   45781324| RXO216LWUDV6O|B00FLTZ2ZS|     361297724|DNM Mountain Bike...|        Outdoors|          3|           29|         31|   N|                Y|         Air leak :(|I just got this a...| 2015-08-31|\n",
            "|         US|   16699467|R3NMJF7EBMM19V|B007SZ4XJ4|     188745514|VeloChampion MLT1...|        Outdoors|          3|           26|         27|   N|                Y|Good quality, act...|The only reason I...| 2015-08-31|\n",
            "|         US|   16299390|R2ZY0ZBDUO0XUY|B00NAINBM8|     754535833|Earthtrekgear Fol...|        Outdoors|          3|           20|         21|   N|                Y|Short and (almost...|It is almost impo...| 2015-08-31|\n",
            "|         US|   30196513| RBDVHX7PMQEKM|B00OAUPSSA|     877639199|Insta-Bed Raised ...|        Outdoors|          5|           35|         39|   N|                Y|   Nice air mattress|Nice air mattress...| 2015-08-31|\n",
            "|         US|   13750464| RGO3KSDXYWTGU|B00V65XX0C|     379510044|OuterEQ 0.8L Outd...|        Outdoors|          4|           26|         26|   N|                Y|      Almost perfect|This is a nice li...| 2015-08-31|\n",
            "|         US|   14428825|R1E1PQGHWGO3Q1|B007ZYIFQ0|     184431888|Igloo Iceless The...|        Outdoors|          1|           89|         96|   N|                Y|        Doesn't work|Cord melted the f...| 2015-08-31|\n",
            "|         US|   42845702|R1XOXXUVYZEDJB|B0087NHUWS|     740768441|     Ribz Front Pack|        Outdoors|          5|           22|         22|   N|                Y|Nice compliment t...|Nicely compliment...| 2015-08-31|\n",
            "|         US|   13557708| R7SHITUU5UG9U|B00NFCFNUM|     608702364|Kelty Salida 2 Pe...|        Outdoors|          5|           27|         27|   N|                Y|Worth it with lot...|I bought this ite...| 2015-08-31|\n",
            "|         US|   15323553|R230TLHU91M5Y3|B010X5715I|     302429091|SYRINX High Quali...|        Outdoors|          4|           21|         21|   N|                Y|  Great little mount|Great little moun...| 2015-08-31|\n",
            "|         US|   32928343| RHR5MKO23K9TU|B00CMAG6BI|     401191323|Camp Chef Italia ...|        Outdoors|          5|           26|         30|   N|                Y|Well worth the pr...|I have been tryin...| 2015-08-31|\n",
            "|         US|    3046458|R1HGK3TCY161D7|B011OLKB3O|     653374459|Stanley Adventure...|        Outdoors|          5|           52|         55|   N|                N|Great for chunky ...|I got this thermo...| 2015-08-31|\n",
            "|         US|   51111929|R340QYAVNZZHJA|B005LURMSW|     340426618|Mountainsmith Con...|        Outdoors|          4|           25|         25|   N|                N|Spacious Tent, Hi...|My wife and I sea...| 2015-08-31|\n",
            "|         US|     923391|R2F1UVQK0JL4QH|B00AHSRM1A|      15004904|Raider Ultralight...|        Outdoors|          3|           20|         20|   N|                Y|Compact- Seems we...|The &#34;Guy Line...| 2015-08-31|\n",
            "|         US|   43108390|R1N21UY546VWMN|B00DZLVDAC|     212774904|ExOfficio Men's G...|        Outdoors|          2|           48|         50|   N|                Y|These fit great, ...|These fit great, ...| 2015-08-31|\n",
            "|         US|   13629614|R1E4L344EK0ZOT|B012BRMTHG|     283537352|Cal 7 22 Inch Com...|        Outdoors|          5|           20|         26|   N|                N|This was a perfec...|This was a perfec...| 2015-08-31|\n",
            "|         US|   35439949|R282QUZDDJW3ON|B000NVBH8A|     136509543|Stearns Adult Cla...|        Outdoors|          4|           45|         49|   N|                Y|Universal for all...|Get universal unl...| 2015-08-31|\n",
            "|         US|   13260359|R3H5T3T7QY3ZL2|B00Y846HN8|     858088629|Wallygadgets 2 Wh...|        Outdoors|          5|          247|        263|   N|                Y|BEWARE OF THE SCA...|first off let me ...| 2015-08-31|\n",
            "|         US|   48844345|R3W1A1P95IIGHX|B00Z8V5OV6|      37436668|Ezisoul Large 32o...|        Outdoors|          5|           23|         25|   N|                N|This one is HANDS...|I have a few wate...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Find the percentage of paid and unpaid 5-star reviews**\n",
        "---\n",
        "\n"
      ],
      "metadata": {
        "id": "AZBidpcFILCJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# paid total number of reviews\n",
        "total_paid_reviews = paid_df.count()\n",
        "total_paid_reviews"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "w0Qi10PyIeFX",
        "outputId": "5c2b7c1f-2c36-4158-c4c4-065cf180e906"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "107"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# paid 5-star reviews\n",
        "paid_five_star_reviews = paid_df.filter(paid_df.star_rating == 5).count()\n",
        "paid_five_star_reviews"
      ],
      "metadata": {
        "id": "GU5UoX4dSia1",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ed1be155-beec-41ef-e5d7-3586e54929fc"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "56"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# paid 5-star reviews percentage\n",
        "paid_five_star_percent = (paid_five_star_reviews / total_paid_reviews) * 100\n",
        "paid_five_star_percent"
      ],
      "metadata": {
        "id": "QpKkoOsnTlos",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d718a992-3cc6-437f-e0ab-610f32ec6e6d"
      },
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "52.336448598130836"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# unpaid total number of reviews\n",
        "total_unpaid_reviews = unpaid_df.count()\n",
        "total_unpaid_reviews"
      ],
      "metadata": {
        "id": "Xuc_fzCoaiKF",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "753ea055-219e-4294-98f0-5b3f0e87292c"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "39869"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# unpaid 5-star reviews\n",
        "unpaid_five_star_reviews = unpaid_df.filter(unpaid_df.star_rating == 5).count()\n",
        "unpaid_five_star_reviews"
      ],
      "metadata": {
        "id": "9O-cIzsAajNV",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2b2d8c2d-4336-45fb-ebf3-41018238f54c"
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "21005"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# unpaid 5-star reviews percentage\n",
        "unpaid_five_star_percent = (unpaid_five_star_reviews / total_unpaid_reviews) * 100\n",
        "unpaid_five_star_percent"
      ],
      "metadata": {
        "id": "wdWbG0kHaoQS",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d0380d8d-a261-4566-befe-77be10bbba0c"
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "52.68504351751988"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    }
  ]
}