Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Vine_Review_Analysis.ipynb",
      "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": "Eu0dTzrWutE4",
        "outputId": "f682105c-4fc1-4581-f3ca-5d2336b13d1c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rHit:1 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.91.38)] [Waiting for headers] [Con\r                                                                               \rGet:2 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "\r                                                                               \rGet:3 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "\r                                                                               \rHit:4 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "\r                                                                               \rHit:5 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "\r                                                                               \rHit:6 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "\r                                                                               \rHit:7 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:8 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:9 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Get:10 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Get:11 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [1,057 kB]\n",
            "Get:12 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,526 kB]\n",
            "Get:13 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,897 kB]\n",
            "Ign:14 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:15 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:16 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,302 kB]\n",
            "Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,329 kB]\n",
            "Fetched 11.4 MB in 3s (3,695 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ],
      "source": [
        "import os\n",
        "# Find the latest version of spark 3.0 from http://www.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.0.3'\n",
        "spark_version = 'spark-3.0.3'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "!wget -q http://www.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark\n",
        "\n",
        "# Set Environment Variables\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop2.7\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Download the Postgres driver that will allow Spark to interact with Postgres.\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B2qh4n7iu2Zc",
        "outputId": "9e5a2d3a-407f-4c3d-89a2-3c6f193a24bc"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-07-16 15:58:26--  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  1.61MB/s    in 0.6s    \n",
            "\n",
            "2022-07-16 15:58:27 (1.61 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Config part adds the driver to spark\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"M16-Amazon-Review-Analysis-Challenge\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "WSIEPhYAu5OW"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://amazon-vine-analysis-challenge.s3.us-east-2.amazonaws.com/amazon_reviews_us_Mobile_Electronics_v1_00.tsv\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Mobile_Electronics_v1_00.tsv\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NnSR6E2cu-GE",
        "outputId": "e4067314-2e58-456f-8f4a-9c45b7bdeaca"
      },
      "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|   20422322| R8MEA6IGAHO0B|B00MC4CED8|     217304173|BlackVue DR600GW-PMP|Mobile_Electronics|          5|            0|          0|   N|                Y|         Very Happy!|As advertised. Ev...| 2015-08-31|\n",
            "|         US|   40835037|R31LOQ8JGLPRLK|B00OQMFG1Q|     137313254|GENSSI GSM / GPS ...|Mobile_Electronics|          5|            0|          1|   N|                Y|           five star|          it's great| 2015-08-31|\n",
            "|         US|   51469641|R2Y0MM9YE6OP3P|B00QERR5CY|      82850235|iXCC Multi pack L...|Mobile_Electronics|          5|            0|          0|   N|                Y|        great cables|These work great ...| 2015-08-31|\n",
            "|         US|    4332923| RRB9C05HDOD4O|B00QUFTPV4|     221169481|abcGoodefg® FBI C...|Mobile_Electronics|          4|            0|          0|   N|                Y|Work very well bu...|Work very well bu...| 2015-08-31|\n",
            "|         US|   44855305|R26I2RI1GFV8QG|B0067XVNTG|     563475445|Generic Car Dashb...|Mobile_Electronics|          2|            0|          0|   N|                Y|Cameras has batte...|Be careful with t...| 2015-08-31|\n",
            "|         US|    7846966| RY8DDL22YG4R5|B00KA6CCVY|     713636156|Aweek® Air Acoust...|Mobile_Electronics|          3|            0|          1|   N|                Y|It appears to be ...|It appears to be ...| 2015-08-31|\n",
            "|         US|   21299354|R2AT2426ZHFUHH|B00MJCDPM2|     754171872|Sentey LS-4460 B-...|Mobile_Electronics|          3|            0|          1|   N|                Y|Didn't love the f...|First one arrived...| 2015-08-31|\n",
            "|         US|   28902968|R3RRXU2R23NMQ9|B00ET5AWBY|     508373185|iPad Car Headrest...|Mobile_Electronics|          5|            0|          0|   N|                Y|          Five Stars|Worked great for ...| 2015-08-31|\n",
            "|         US|    2074438|R250PR8VJUZ62F|B00Z9K379C|     759387370|ANWING Waterproof...|Mobile_Electronics|          4|            0|          2|   N|                Y|          Four Stars|this is a very go...| 2015-08-31|\n",
            "|         US|   48701722| RBEMQ29WJBHYG|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          5|          164|        168|   N|                Y|  I LOVE my recorder|I LOVE my recorde...| 2015-08-31|\n",
            "|         US|    2990247|R19VVIUT4BZCMT|B011W26BU2|     938399424|Bluetooth Speaker...|Mobile_Electronics|          5|            2|          2|   N|                Y|          Five Stars|Great sound great...| 2015-08-31|\n",
            "|         US|   52946117|R1DT8JJUQHUKUL|B00H8PI78C|     661515294|15Pcs DC 5V 85dB ...|Mobile_Electronics|          3|            0|          1|   N|                Y|More of a beeper ...|If what you want ...| 2015-08-31|\n",
            "|         US|   49359994| RLIL6S9OGM3YA|B00M49C6MK|      91176851|      Sentey Ls 4162|Mobile_Electronics|          5|            0|          0|   N|                Y|          Five Stars|             Love it| 2015-08-31|\n",
            "|         US|    4158732|R34EZZ68VYPHO0|B011Q291PK|     891142775|IcePACK 8 Can Ins...|Mobile_Electronics|          1|            1|          1|   N|                Y|            One Star|               Sucks| 2015-08-31|\n",
            "|         US|   18944032|R3TZNSOXS13SIG|B00W6OLUGQ|     577462547|DTK® AMI MDI MMI ...|Mobile_Electronics|          2|            1|          3|   N|                Y|           Two Stars|u need to plug in...| 2015-08-31|\n",
            "|         US|     279606|R12UBZT87UX3AP|B008MY1F8W|     680240984|INPA K+CAN K+DCAN...|Mobile_Electronics|          5|            2|          2|   N|                Y|          Five Stars|Worked with MHD F...| 2015-08-31|\n",
            "|         US|   10947966| R5KCRE927NYMU|B00KW3XNUE|     219920841|Magicfly Wireless...|Mobile_Electronics|          2|            0|          0|   N|                N|Battery Cover Poo...|Just arrived,  re...| 2015-08-31|\n",
            "|         US|   18387442| RT12RQFU0V2AX|B009NGR0AK|     254898339|Humminbird XNT 9 ...|Mobile_Electronics|          5|            1|          1|   N|                Y|        Works great.|I bought this uni...| 2015-08-31|\n",
            "|         US|   48950322|R2K0YQPV5W2SJ1|B011W26BU2|     938399424|Bluetooth Speaker...|Mobile_Electronics|          4|            2|          2|   N|                Y|A Lot of Sound In...|I am giving the s...| 2015-08-31|\n",
            "|         US|   48148197|R104MY31OKNGXH|B00PAP68WO|     366592530|Foxnovo 3.7V 380m...|Mobile_Electronics|          5|            0|          0|   N|                Y|          Five Stars|Met my applicatio...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Retrieve all rows where total_votes is equal or greater than 20\n",
        "df_filtered = df.filter(df.total_votes>=20)\n",
        "df_filtered.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "i5VCT3AqvGUx",
        "outputId": "db605f75-9448-4887-c44d-a4f27ffdf18f"
      },
      "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|   48701722| RBEMQ29WJBHYG|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          5|          164|        168|   N|                Y|  I LOVE my recorder|I LOVE my recorde...| 2015-08-31|\n",
            "|         US|   40963441|R2JK5Y8D5MXAGP|B00SU7B0JK|      82850235|iXCC Multi pack L...|Mobile_Electronics|          1|            5|        121|   N|                Y|Buyer Beware - Ca...|I've bought a lot...| 2015-08-30|\n",
            "|         US|   25750755|R13W3EMIUV120L|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          4|          288|        294|   N|                Y|The Jensen MCR-10...|I recently purcha...| 2015-08-28|\n",
            "|         US|   26155827|R30TJ8POYNVCRE|B00TQ0G130|       9677699|Coby ALL NEW 2.5\"...|Mobile_Electronics|          5|           15|         21|   N|                N|            dash cam|Great product, lo...| 2015-08-27|\n",
            "|         US|   23616875|R1H5Y9Z2DHRNVO|B00LAG4HN4|     536367292|iXCC Element II L...|Mobile_Electronics|          1|            4|         95|   N|                Y|Great design, Ter...|I wanted to love ...| 2015-08-26|\n",
            "|         US|   29092404|R1LK03P7BCJEJD|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          1|           31|         35|   N|                Y|            One Star|Playback is disto...| 2015-08-22|\n",
            "|         US|   33177916| RBXUICKGHL685|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          1|           48|         52|   N|                N|Chewed up within ...|Terrible!!  Chewe...| 2015-08-21|\n",
            "|         US|   42331191|R2E16WLZKP78GO|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           19|         21|   N|                Y|           Satisfied|Garmin nuvi 65lm ...| 2015-08-20|\n",
            "|         US|    3913139|R1EN4PGQIFSENW|B00LAG4HN4|     536367292|iXCC Element II L...|Mobile_Electronics|          5|           27|         28|   N|                Y|I've bought 4 of ...|So we've all had ...| 2015-08-10|\n",
            "|         US|   43755322| RXZWQU66AHIYG|B00TQYEONA|     376434637|Garmin dezl 760LM...|Mobile_Electronics|          5|           24|         25|   N|                Y|THE BEST UNIT I H...|Today 8/10/2015 w...| 2015-08-10|\n",
            "|         US|   45886406|R2IXCZCSUKH8FB|B00CJZLHN8|     869734493|Apple Ipod Nano 7...|Mobile_Electronics|          3|           78|         82|   N|                Y|Still limited to ...|As many other rev...| 2015-08-04|\n",
            "|         US|    3120094|R25RB38U9LL8OE|B010W4OF4K|     649814137|Escort Passport X...|Mobile_Electronics|          5|           24|         29|   N|                Y|best radar detect...|We have owned man...| 2015-08-04|\n",
            "|         US|   21862658|R1Q0XK7VKU3EKX|B00J46XO9U|     744008282|iXCC Lightning Ca...|Mobile_Electronics|          1|            7|        130|   N|                Y|  I caught on fire!!|I plugged it on m...| 2015-07-25|\n",
            "|         US|   40159163| RSHPPH6BRXYRS|B00QMMBXA6|     827642739|Tomtom VIA 1515M ...|Mobile_Electronics|          2|           42|         48|   N|                N|     Not Recommended|This is my third ...| 2015-07-22|\n",
            "|         US|    6867529|R39HYCYEWFZP3B|B00Y2100OO|     161663398|LiteXim All in On...|Mobile_Electronics|          5|           36|         36|   N|                N|Awesome mp3/mp4 P...|[[VIDEOID:1a66cff...| 2015-07-22|\n",
            "|         US|   26362086|R2C68DIKTG0M7W|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           18|         20|   N|                Y|      Love this GPS!|I have only used ...| 2015-07-20|\n",
            "|         US|   34123746|R2I6O4S2CZK8WQ|B00EDM5WV8|     998105706|SPOT-3O Spot Gen3...|Mobile_Electronics|          1|          175|        181|   N|                Y|Failed after firs...|I've had a spot t...| 2015-07-20|\n",
            "|         US|   18226253|R1EPU8R4MVRIQ7|B00XJJU4SC|     965917258|Juentai JT-6188 D...|Mobile_Electronics|          4|           21|         22|   N|                Y|Nice little dual ...|These are pretty ...| 2015-07-19|\n",
            "|         US|   52807030| RN55T65W485LS|B00VU7PGPQ|     973149575|[New Element Seri...|Mobile_Electronics|          1|            5|        222|   N|                N|Visually looks go...|I've never writte...| 2015-07-15|\n",
            "|         US|   48531577|R1AL1OVEHNX5JC|B010P73G76|     995824709|Phtronics 2015 La...|Mobile_Electronics|          5|           19|         22|   N|                Y|This is a great m...|This is a great m...| 2015-07-15|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Filter the created df to retrieve all rows where helpful_votes/total_votes is equal or greater than 50%\n",
        "df_helpful_votes_rate = df_filtered.filter((df.helpful_votes/df.total_votes) >=0.5)\n",
        "df_helpful_votes_rate.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_ByeLQVUvtJD",
        "outputId": "814859ed-32ce-4195-d53f-e0fa0543a440"
      },
      "execution_count": 7,
      "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|   48701722| RBEMQ29WJBHYG|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          5|          164|        168|   N|                Y|  I LOVE my recorder|I LOVE my recorde...| 2015-08-31|\n",
            "|         US|   25750755|R13W3EMIUV120L|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          4|          288|        294|   N|                Y|The Jensen MCR-10...|I recently purcha...| 2015-08-28|\n",
            "|         US|   26155827|R30TJ8POYNVCRE|B00TQ0G130|       9677699|Coby ALL NEW 2.5\"...|Mobile_Electronics|          5|           15|         21|   N|                N|            dash cam|Great product, lo...| 2015-08-27|\n",
            "|         US|   29092404|R1LK03P7BCJEJD|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          1|           31|         35|   N|                Y|            One Star|Playback is disto...| 2015-08-22|\n",
            "|         US|   33177916| RBXUICKGHL685|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          1|           48|         52|   N|                N|Chewed up within ...|Terrible!!  Chewe...| 2015-08-21|\n",
            "|         US|   42331191|R2E16WLZKP78GO|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           19|         21|   N|                Y|           Satisfied|Garmin nuvi 65lm ...| 2015-08-20|\n",
            "|         US|    3913139|R1EN4PGQIFSENW|B00LAG4HN4|     536367292|iXCC Element II L...|Mobile_Electronics|          5|           27|         28|   N|                Y|I've bought 4 of ...|So we've all had ...| 2015-08-10|\n",
            "|         US|   43755322| RXZWQU66AHIYG|B00TQYEONA|     376434637|Garmin dezl 760LM...|Mobile_Electronics|          5|           24|         25|   N|                Y|THE BEST UNIT I H...|Today 8/10/2015 w...| 2015-08-10|\n",
            "|         US|   45886406|R2IXCZCSUKH8FB|B00CJZLHN8|     869734493|Apple Ipod Nano 7...|Mobile_Electronics|          3|           78|         82|   N|                Y|Still limited to ...|As many other rev...| 2015-08-04|\n",
            "|         US|    3120094|R25RB38U9LL8OE|B010W4OF4K|     649814137|Escort Passport X...|Mobile_Electronics|          5|           24|         29|   N|                Y|best radar detect...|We have owned man...| 2015-08-04|\n",
            "|         US|   40159163| RSHPPH6BRXYRS|B00QMMBXA6|     827642739|Tomtom VIA 1515M ...|Mobile_Electronics|          2|           42|         48|   N|                N|     Not Recommended|This is my third ...| 2015-07-22|\n",
            "|         US|    6867529|R39HYCYEWFZP3B|B00Y2100OO|     161663398|LiteXim All in On...|Mobile_Electronics|          5|           36|         36|   N|                N|Awesome mp3/mp4 P...|[[VIDEOID:1a66cff...| 2015-07-22|\n",
            "|         US|   26362086|R2C68DIKTG0M7W|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           18|         20|   N|                Y|      Love this GPS!|I have only used ...| 2015-07-20|\n",
            "|         US|   34123746|R2I6O4S2CZK8WQ|B00EDM5WV8|     998105706|SPOT-3O Spot Gen3...|Mobile_Electronics|          1|          175|        181|   N|                Y|Failed after firs...|I've had a spot t...| 2015-07-20|\n",
            "|         US|   18226253|R1EPU8R4MVRIQ7|B00XJJU4SC|     965917258|Juentai JT-6188 D...|Mobile_Electronics|          4|           21|         22|   N|                Y|Nice little dual ...|These are pretty ...| 2015-07-19|\n",
            "|         US|   48531577|R1AL1OVEHNX5JC|B010P73G76|     995824709|Phtronics 2015 La...|Mobile_Electronics|          5|           19|         22|   N|                Y|This is a great m...|This is a great m...| 2015-07-15|\n",
            "|         US|   10044506| RVE1FMV48N1ZD|B00VU7PKZW|     595921774|iXCC Element Seri...|Mobile_Electronics|          5|           37|         41|   N|                N|Good solid constr...|Quality product w...| 2015-07-14|\n",
            "|         US|   14080076| R6SR9BXDPIYMZ|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           51|         57|   N|                Y|Fast delivery.  R...|This refurbed 6&#...| 2015-07-10|\n",
            "|         US|   28150579|R1JQPDB1WJ4CKV|B00OYVJLZQ|     312846790|Pioneer AVH-X7700...|Mobile_Electronics|          3|           25|         26|   N|                N|Basic MirrorLink ...|I would give this...| 2015-07-05|\n",
            "|         US|   47211331|R3L7LS0NW1L6PQ|B00J46XO9U|     744008282|iXCC Lightning Ca...|Mobile_Electronics|          5|           97|        101|   N|                Y|A less expensive ...|I use this cable ...| 2015-06-24|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Filter rows where the review was written as part of the Vine program (paid)\n",
        "df_vine = df_helpful_votes_rate.filter(df_helpful_votes_rate.vine ==\"Y\")\n",
        "df_vine.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wCz2IdOcws1L",
        "outputId": "8ab28513-083c-4fa8-c66f-24125240fd15"
      },
      "execution_count": 9,
      "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|   52490988|R3BOQTLUU3Y4L8|B00EP0SIOG|     628459233|Orbotix Sphero 2....|Mobile_Electronics|          4|           42|         55|   Y|                N|Lot of fun, but f...|Before receiving ...| 2013-11-19|\n",
            "|         US|   48814959| RWF03LXVXC22A|B00EP0SIOG|     628459233|Orbotix Sphero 2....|Mobile_Electronics|          3|          396|        445|   Y|                N|The coolest thing...|***** Update 12/3...| 2013-11-06|\n",
            "|         US|   34641748| REPU28WG1VZUE|B00EP0SIOG|     628459233|Orbotix Sphero 2....|Mobile_Electronics|          5|          242|        281|   Y|                N|The funest new to...|...or at least I ...| 2013-10-24|\n",
            "|         US|   52477683|R1D6REC9HPJVQY|B00EP0SIOG|     628459233|Orbotix Sphero 2....|Mobile_Electronics|          4|           31|         41|   Y|                N|Fun toy for kids,...|This new version ...| 2013-10-21|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Filter rows where the review was not part of the Vine program (unpaid)\n",
        "df_no_vine = df_helpful_votes_rate.filter(df_helpful_votes_rate.vine==\"N\")\n",
        "df_no_vine.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vzFVv9jqw-6y",
        "outputId": "56dc227d-1d8d-4f95-ec1d-a98a78a248e5"
      },
      "execution_count": 10,
      "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|   48701722| RBEMQ29WJBHYG|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          5|          164|        168|   N|                Y|  I LOVE my recorder|I LOVE my recorde...| 2015-08-31|\n",
            "|         US|   25750755|R13W3EMIUV120L|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          4|          288|        294|   N|                Y|The Jensen MCR-10...|I recently purcha...| 2015-08-28|\n",
            "|         US|   26155827|R30TJ8POYNVCRE|B00TQ0G130|       9677699|Coby ALL NEW 2.5\"...|Mobile_Electronics|          5|           15|         21|   N|                N|            dash cam|Great product, lo...| 2015-08-27|\n",
            "|         US|   29092404|R1LK03P7BCJEJD|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          1|           31|         35|   N|                Y|            One Star|Playback is disto...| 2015-08-22|\n",
            "|         US|   33177916| RBXUICKGHL685|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          1|           48|         52|   N|                N|Chewed up within ...|Terrible!!  Chewe...| 2015-08-21|\n",
            "|         US|   42331191|R2E16WLZKP78GO|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           19|         21|   N|                Y|           Satisfied|Garmin nuvi 65lm ...| 2015-08-20|\n",
            "|         US|    3913139|R1EN4PGQIFSENW|B00LAG4HN4|     536367292|iXCC Element II L...|Mobile_Electronics|          5|           27|         28|   N|                Y|I've bought 4 of ...|So we've all had ...| 2015-08-10|\n",
            "|         US|   43755322| RXZWQU66AHIYG|B00TQYEONA|     376434637|Garmin dezl 760LM...|Mobile_Electronics|          5|           24|         25|   N|                Y|THE BEST UNIT I H...|Today 8/10/2015 w...| 2015-08-10|\n",
            "|         US|   45886406|R2IXCZCSUKH8FB|B00CJZLHN8|     869734493|Apple Ipod Nano 7...|Mobile_Electronics|          3|           78|         82|   N|                Y|Still limited to ...|As many other rev...| 2015-08-04|\n",
            "|         US|    3120094|R25RB38U9LL8OE|B010W4OF4K|     649814137|Escort Passport X...|Mobile_Electronics|          5|           24|         29|   N|                Y|best radar detect...|We have owned man...| 2015-08-04|\n",
            "|         US|   40159163| RSHPPH6BRXYRS|B00QMMBXA6|     827642739|Tomtom VIA 1515M ...|Mobile_Electronics|          2|           42|         48|   N|                N|     Not Recommended|This is my third ...| 2015-07-22|\n",
            "|         US|    6867529|R39HYCYEWFZP3B|B00Y2100OO|     161663398|LiteXim All in On...|Mobile_Electronics|          5|           36|         36|   N|                N|Awesome mp3/mp4 P...|[[VIDEOID:1a66cff...| 2015-07-22|\n",
            "|         US|   26362086|R2C68DIKTG0M7W|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           18|         20|   N|                Y|      Love this GPS!|I have only used ...| 2015-07-20|\n",
            "|         US|   34123746|R2I6O4S2CZK8WQ|B00EDM5WV8|     998105706|SPOT-3O Spot Gen3...|Mobile_Electronics|          1|          175|        181|   N|                Y|Failed after firs...|I've had a spot t...| 2015-07-20|\n",
            "|         US|   18226253|R1EPU8R4MVRIQ7|B00XJJU4SC|     965917258|Juentai JT-6188 D...|Mobile_Electronics|          4|           21|         22|   N|                Y|Nice little dual ...|These are pretty ...| 2015-07-19|\n",
            "|         US|   48531577|R1AL1OVEHNX5JC|B010P73G76|     995824709|Phtronics 2015 La...|Mobile_Electronics|          5|           19|         22|   N|                Y|This is a great m...|This is a great m...| 2015-07-15|\n",
            "|         US|   10044506| RVE1FMV48N1ZD|B00VU7PKZW|     595921774|iXCC Element Seri...|Mobile_Electronics|          5|           37|         41|   N|                N|Good solid constr...|Quality product w...| 2015-07-14|\n",
            "|         US|   14080076| R6SR9BXDPIYMZ|B00TQYCU0Y|     328706129|Garmin nüvi 65LM ...|Mobile_Electronics|          5|           51|         57|   N|                Y|Fast delivery.  R...|This refurbed 6&#...| 2015-07-10|\n",
            "|         US|   28150579|R1JQPDB1WJ4CKV|B00OYVJLZQ|     312846790|Pioneer AVH-X7700...|Mobile_Electronics|          3|           25|         26|   N|                N|Basic MirrorLink ...|I would give this...| 2015-07-05|\n",
            "|         US|   47211331|R3L7LS0NW1L6PQ|B00J46XO9U|     744008282|iXCC Lightning Ca...|Mobile_Electronics|          5|           97|        101|   N|                Y|A less expensive ...|I use this cable ...| 2015-06-24|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total number of review for paid and unpaid\n",
        "count_paid_reviews=df_vine.count()\n",
        "print('Total number of paid reviews:', count_paid_reviews)\n",
        "count_unpaid_reviews=df_no_vine.count()\n",
        "print('Total number of unpaid reviews:', count_unpaid_reviews)\n",
        "total_reviews=count_paid_reviews+count_unpaid_reviews\n",
        "print('Total number of reviews is:',total_reviews)\n",
        "print(\"\")\n",
        "\n",
        "# Total 5-star reviews for paid and unpaid\n",
        "count_paid_5_stars = df_vine.filter(df_vine.star_rating == 5).count()\n",
        "print('Total number of 5-stars reviews paid:', count_paid_5_stars)\n",
        "count_unpaid_5_stars = df_no_vine.filter(df_no_vine.star_rating == 5).count()\n",
        "print('Total number of 5-stars reviews unpaid:', count_unpaid_5_stars)\n",
        "print('Total number of 5-stars reviews',count_paid_5_stars+count_unpaid_5_stars)\n",
        "print(\"\")\n",
        "\n",
        "# Percentage of 5-star reviews for paid and unpaid\n",
        "percentage_paid_5_stars = count_paid_5_stars/count_paid_reviews*100\n",
        "print(\"Percentage of 5-stars-reviews paid per total paid reviews\",round(percentage_paid_5_stars,2),'%')\n",
        "percentage_unpaid_5_stars = count_unpaid_5_stars/count_unpaid_reviews*100\n",
        "print(\"Percentage of 5-stars-reviews unpaid per total unpaid reviews\",round(percentage_unpaid_5_stars,2),'%')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "65ikKFmMxVcI",
        "outputId": "87ca708c-b4ac-447a-d78f-4038e08a51b9"
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Total number of paid reviews: 4\n",
            "Total number of unpaid reviews: 1064\n",
            "Total number of reviews is: 1068\n",
            "\n",
            "Total number of 5-stars reviews paid: 1\n",
            "Total number of 5-stars reviews unpaid: 527\n",
            "Total number of 5-stars reviews 528\n",
            "\n",
            "Percentage of 5-stars-reviews paid per total paid reviews 25.0 %\n",
            "Percentage of 5-stars-reviews unpaid per total unpaid reviews 49.53 %\n"
          ]
        }
      ]
    }
  ]
}