Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Vine_Review_Analysis.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "PythonData",
      "language": "python",
      "name": "pythondata"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.7"
    },
    "nteract": {
      "version": "0.12.3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ramyasnl/PySpark_Challenge/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",
      "metadata": {
        "id": "V58rxea0HqSa",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "81d99f56-07a9-4d77-9a09-6b4660b3c21c"
      },
      "source": [
        "import os\n",
        "# Find the latest version of spark 2.0  from http://www-us.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.0.0'\n",
        "spark_version = 'spark-3.0.2'\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-us.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()"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Get:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Get:2 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Ign:3 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:4 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Hit:5 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Ign:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:7 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release [697 B]\n",
            "Hit:8 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:9 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release.gpg [836 B]\n",
            "Get:10 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:11 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:12 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [423 kB]\n",
            "Get:13 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Hit:14 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,411 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,150 kB]\n",
            "Get:17 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:18 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ Packages [56.8 kB]\n",
            "Ign:20 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages\n",
            "Get:20 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [772 kB]\n",
            "Get:21 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,767 kB]\n",
            "Get:22 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [452 kB]\n",
            "Get:23 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,183 kB]\n",
            "Get:24 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [904 kB]\n",
            "Get:25 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [2,582 kB]\n",
            "Get:26 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [41.5 kB]\n",
            "Fetched 13.0 MB in 4s (3,347 kB/s)\n",
            "Reading package lists... Done\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_xKwTpATHqSe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "0dda7b71-0332-4109-c9fa-9e3444620100"
      },
      "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"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2021-05-18 21:26:46--  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  --.-KB/s    in 0.1s    \n",
            "\n",
            "2021-05-18 21:26:46 (6.57 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MMqDAjVS0KN9"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"BigData-Challenge\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cyBsySGuY-9V"
      },
      "source": [
        "### Load Amazon Data into Spark DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CtCmBhQJY-9Z",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "54f07d74-98c1-42f4-c3ee-418c8a74373e"
      },
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Wireless_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "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|   16414143|R3W4P9UBGNGH1U|B00YL0EKWE|     852431543|LG G4 Case Hard T...|        Wireless|          2|            1|          3|   N|                Y|Looks good, funct...|2 issues  -  Once...| 2015-08-31|\n",
            "|         US|   50800750|R15V54KBMTQWAY|B00XK95RPQ|     516894650|Selfie Stick Fibl...|        Wireless|          4|            0|          0|   N|                N| A fun little gadget|I’m embarrassed t...| 2015-08-31|\n",
            "|         US|   15184378| RY8I449HNXSVF|B00SXRXUKO|     984297154|Tribe AB40 Water ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Fits iPhone 6 well| 2015-08-31|\n",
            "|         US|   10203548|R18TLJYCKJFLSR|B009V5X1CE|     279912704|RAVPower® Element...|        Wireless|          5|            0|          0|   N|                Y|       Great charger|Great charger.  I...| 2015-08-31|\n",
            "|         US|     488280|R1NK26SWS53B8Q|B00D93OVF0|     662791300|Fosmon Micro USB ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great for the pri...| 2015-08-31|\n",
            "|         US|   13334021|R11LOHEDYJALTN|B00XVGJMDQ|     421688488|iPhone 6 Case, Vo...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great Case, bette...| 2015-08-31|\n",
            "|         US|   27520697|R3ALQVQB2P9LA7|B00KQW1X1C|     554285554|Nokia Lumia 630 R...|        Wireless|          4|            0|          0|   N|                Y|          Four Stars|Easy to set up an...| 2015-08-31|\n",
            "|         US|   48086021|R3MWLXLNO21PDQ|B00IP1MQNK|     488006702|Lumsing 10400mah ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|         Works great| 2015-08-31|\n",
            "|         US|   12738196|R2L15IS24CX0LI|B00HVORET8|     389677711|iPhone 5S Battery...|        Wireless|          5|            0|          0|   N|                Y|      So far so good|So far so good. I...| 2015-08-31|\n",
            "|         US|   15867807|R1DJ8976WPWVZU|B00HX3G6J6|     299654876|HTC One M8 Screen...|        Wireless|          3|            0|          0|   N|                Y|seems durable but...|seems durable but...| 2015-08-31|\n",
            "|         US|    1972249|R3MRWNNR8CBTB7|B00U4NATNQ|     577878727|S6 Case - Bear Mo...|        Wireless|          5|            0|          0|   N|                Y|Super thin, light...|Super thin, light...| 2015-08-31|\n",
            "|         US|   10956619|R1DS6DKTUXAQK3|B00SZEFDH8|     654620704|BLU Studio X, Unl...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|As good as the Sa...| 2015-08-31|\n",
            "|         US|   14805911| RWJM5E0TWUJD2|B00JRJUL9U|     391166958|EZOPower 5-Port U...|        Wireless|          5|            0|          0|   N|                Y|            EZOPower|I received this o...| 2015-08-31|\n",
            "|         US|   15611116|R1XTJKDYNCRGAC|B00KQ4T0HE|     481551630|iPhone 6S Case &i...|        Wireless|          1|            0|          0|   N|                Y|Very cheap case. ...|Very cheap case. ...| 2015-08-31|\n",
            "|         US|   39298603|R2UZL3DPWEU1XW|B00M0YWKPM|     685107474| iPhone 6s Plus Case|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|       son loves it.| 2015-08-31|\n",
            "|         US|   17552454|R2EZXET9KBFFU3|B00KDZEE68|     148320945|zBoost ZB575-A TR...|        Wireless|          1|            0|          0|   N|                Y|            One Star|you have to turn ...| 2015-08-31|\n",
            "|         US|   12218556|R26VY1L1FD3LPU|B00BJN45GM|      47788188|OtterBox Defender...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Awesome, thank you| 2015-08-31|\n",
            "|         US|   21872923|R2SSA4NSFCV18T|B00SA86SXW|     748759272|Aduro PowerUP 30W...|        Wireless|          5|            0|          0|   N|                N|          Five Stars|         nice, smart| 2015-08-31|\n",
            "|         US|   16264332|R1G6333JHJNEUQ|B00Q3I68TU|     974085141|LilGadgets Connec...|        Wireless|          5|            0|          0|   N|                Y|Great headphones ...|We love these hea...| 2015-08-31|\n",
            "|         US|    6042304|R2DRG0UZXJQ0PE|B00TN4J1TA|     716174627|Anker Aluminum Mu...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|This is well wort...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Rg7Qso0mCMWS",
        "outputId": "0ff4db82-a264-4bf9-91a7-b02c4c02f0f3"
      },
      "source": [
        "#to check the datatypes in the dataframe \n",
        "df.schema"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "StructType(List(StructField(marketplace,StringType,true),StructField(customer_id,IntegerType,true),StructField(review_id,StringType,true),StructField(product_id,StringType,true),StructField(product_parent,IntegerType,true),StructField(product_title,StringType,true),StructField(product_category,StringType,true),StructField(star_rating,StringType,true),StructField(helpful_votes,IntegerType,true),StructField(total_votes,IntegerType,true),StructField(vine,StringType,true),StructField(verified_purchase,StringType,true),StructField(review_headline,StringType,true),StructField(review_body,StringType,true),StructField(review_date,StringType,true)))"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2yUSe55VY-9t"
      },
      "source": [
        "### Create DataFrames to match tables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C8REmY1aY-9u",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "58125ad7-5073-4a20-f2ac-0d09f039d0bc"
      },
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n",
        "customer_clean_df = df.dropna()\n",
        "customer_clean_df.show()"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "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|   16414143|R3W4P9UBGNGH1U|B00YL0EKWE|     852431543|LG G4 Case Hard T...|        Wireless|          2|            1|          3|   N|                Y|Looks good, funct...|2 issues  -  Once...| 2015-08-31|\n",
            "|         US|   50800750|R15V54KBMTQWAY|B00XK95RPQ|     516894650|Selfie Stick Fibl...|        Wireless|          4|            0|          0|   N|                N| A fun little gadget|I’m embarrassed t...| 2015-08-31|\n",
            "|         US|   15184378| RY8I449HNXSVF|B00SXRXUKO|     984297154|Tribe AB40 Water ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Fits iPhone 6 well| 2015-08-31|\n",
            "|         US|   10203548|R18TLJYCKJFLSR|B009V5X1CE|     279912704|RAVPower® Element...|        Wireless|          5|            0|          0|   N|                Y|       Great charger|Great charger.  I...| 2015-08-31|\n",
            "|         US|     488280|R1NK26SWS53B8Q|B00D93OVF0|     662791300|Fosmon Micro USB ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great for the pri...| 2015-08-31|\n",
            "|         US|   13334021|R11LOHEDYJALTN|B00XVGJMDQ|     421688488|iPhone 6 Case, Vo...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great Case, bette...| 2015-08-31|\n",
            "|         US|   27520697|R3ALQVQB2P9LA7|B00KQW1X1C|     554285554|Nokia Lumia 630 R...|        Wireless|          4|            0|          0|   N|                Y|          Four Stars|Easy to set up an...| 2015-08-31|\n",
            "|         US|   48086021|R3MWLXLNO21PDQ|B00IP1MQNK|     488006702|Lumsing 10400mah ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|         Works great| 2015-08-31|\n",
            "|         US|   12738196|R2L15IS24CX0LI|B00HVORET8|     389677711|iPhone 5S Battery...|        Wireless|          5|            0|          0|   N|                Y|      So far so good|So far so good. I...| 2015-08-31|\n",
            "|         US|   15867807|R1DJ8976WPWVZU|B00HX3G6J6|     299654876|HTC One M8 Screen...|        Wireless|          3|            0|          0|   N|                Y|seems durable but...|seems durable but...| 2015-08-31|\n",
            "|         US|    1972249|R3MRWNNR8CBTB7|B00U4NATNQ|     577878727|S6 Case - Bear Mo...|        Wireless|          5|            0|          0|   N|                Y|Super thin, light...|Super thin, light...| 2015-08-31|\n",
            "|         US|   10956619|R1DS6DKTUXAQK3|B00SZEFDH8|     654620704|BLU Studio X, Unl...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|As good as the Sa...| 2015-08-31|\n",
            "|         US|   14805911| RWJM5E0TWUJD2|B00JRJUL9U|     391166958|EZOPower 5-Port U...|        Wireless|          5|            0|          0|   N|                Y|            EZOPower|I received this o...| 2015-08-31|\n",
            "|         US|   15611116|R1XTJKDYNCRGAC|B00KQ4T0HE|     481551630|iPhone 6S Case &i...|        Wireless|          1|            0|          0|   N|                Y|Very cheap case. ...|Very cheap case. ...| 2015-08-31|\n",
            "|         US|   39298603|R2UZL3DPWEU1XW|B00M0YWKPM|     685107474| iPhone 6s Plus Case|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|       son loves it.| 2015-08-31|\n",
            "|         US|   17552454|R2EZXET9KBFFU3|B00KDZEE68|     148320945|zBoost ZB575-A TR...|        Wireless|          1|            0|          0|   N|                Y|            One Star|you have to turn ...| 2015-08-31|\n",
            "|         US|   12218556|R26VY1L1FD3LPU|B00BJN45GM|      47788188|OtterBox Defender...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Awesome, thank you| 2015-08-31|\n",
            "|         US|   21872923|R2SSA4NSFCV18T|B00SA86SXW|     748759272|Aduro PowerUP 30W...|        Wireless|          5|            0|          0|   N|                N|          Five Stars|         nice, smart| 2015-08-31|\n",
            "|         US|   16264332|R1G6333JHJNEUQ|B00Q3I68TU|     974085141|LilGadgets Connec...|        Wireless|          5|            0|          0|   N|                Y|Great headphones ...|We love these hea...| 2015-08-31|\n",
            "|         US|    6042304|R2DRG0UZXJQ0PE|B00TN4J1TA|     716174627|Anker Aluminum Mu...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|This is well wort...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6Sg0fiDva_Df",
        "outputId": "b7310268-799b-4deb-db3b-57be50f28cff"
      },
      "source": [
        "# Drop duplicates\n",
        "customer_clean_1_df = customer_clean_df.drop_duplicates()\n",
        "customer_clean_1_df.show(10)"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "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|   34974850|R10048FCFWVJOH|B002OL2MU4|     471222439|Garmin Portable f...|        Wireless|          5|            0|          0|   N|                N|Garmin Friction M...|The friction moun...| 2011-04-07|\n",
            "|         US|   30903818|R1008YTRJEH6U7|B00LAHPLTW|     355012266|iPhone 6 case, Ca...|        Wireless|          1|            1|          2|   N|                Y|            One Star|           Okay item| 2014-12-15|\n",
            "|         US|   51491202|R100F3WWLNCX5S|B003T91K94|     816619821|BW 3.6mm Wide Ang...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|good very good us...| 2014-07-31|\n",
            "|         US|    2572903|R100IJSLJ25VPG|B00ENI0JM4|     653653620|ePartSolution-OEM...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  excellent products| 2014-11-30|\n",
            "|         US|   37174514|R100JPDEL3NLYL|B00EF1OGOG|     558284639|Anker 2nd Generat...|        Wireless|          3|            0|          0|   N|                N|          Anker 6000|Works fine for my...| 2014-07-01|\n",
            "|         US|   13654097|R100KWGZ8UZDA0|B0079A29L2|     198567336|Bluecell 3-Feet U...|        Wireless|          1|            0|          1|   N|                Y|               Cheap|I didn't get two ...| 2013-04-21|\n",
            "|         US|   23612091|R100LHWI1PCRDO|B007AQF0WK|      19433141|Motorola Roadster...|        Wireless|          4|            0|          0|   N|                Y|     Would recommend|Bluetooth works g...| 2014-12-21|\n",
            "|         US|   44895028|R100MJFI7QHR78|B00PNUBGGY|     740916364|     Lightning Cable|        Wireless|          5|            0|          0|   N|                N|          Five Stars| Works like a charm.| 2015-04-02|\n",
            "|         US|   35797687|R100MKHZ325MI7|B00DEKJEAA|      24966059|Fosmon Screen Pro...|        Wireless|          4|            2|          2|   N|                Y|           fantastic|I needed somethin...| 2014-06-11|\n",
            "|         US|   25608655|R100U625Y5RVRJ|B00CI4VKB4|     689535231|Galaxy S4 Case, E...|        Wireless|          5|            0|          0|   N|                Y|This is a great p...|great cover. love...| 2013-11-21|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lzMmkdKmY--D",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "aad3fdea-e175-4a8e-d0d6-738c8e1a2b54"
      },
      "source": [
        "# Create the vine_table. DataFrame\n",
        "vine_df = customer_clean_1_df.select([\"review_id\",\"star_rating\",\"helpful_votes\",\"total_votes\",\"vine\",\"verified_purchase\"])\n",
        "vine_df.show(20)"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R10048FCFWVJOH|          5|            0|          0|   N|                N|\n",
            "|R1008YTRJEH6U7|          1|            1|          2|   N|                Y|\n",
            "|R100F3WWLNCX5S|          5|            0|          0|   N|                Y|\n",
            "|R100IJSLJ25VPG|          5|            0|          0|   N|                Y|\n",
            "|R100JPDEL3NLYL|          3|            0|          0|   N|                N|\n",
            "|R100KWGZ8UZDA0|          1|            0|          1|   N|                Y|\n",
            "|R100LHWI1PCRDO|          4|            0|          0|   N|                Y|\n",
            "|R100MJFI7QHR78|          5|            0|          0|   N|                N|\n",
            "|R100MKHZ325MI7|          4|            2|          2|   N|                Y|\n",
            "|R100U625Y5RVRJ|          5|            0|          0|   N|                Y|\n",
            "|R100USE1L3IPHJ|          5|            0|          0|   N|                Y|\n",
            "|R101095URF10PU|          5|            0|          0|   N|                Y|\n",
            "|R101A6CFZU3VJG|          2|            0|          0|   N|                Y|\n",
            "|R101ETRYMFFQDZ|          5|            0|          0|   N|                Y|\n",
            "|R101LZQ2986Z0E|          4|            0|          0|   N|                Y|\n",
            "|R101OC4OKPIVHV|          5|            1|          2|   N|                Y|\n",
            "|R101QZ9MPKQ6JO|          2|            0|          0|   N|                Y|\n",
            "|R101WDY2ZLEC2S|          5|            1|          1|   N|                Y|\n",
            "|R101Y4H7ILF1BZ|          5|            0|          0|   N|                Y|\n",
            "|R1024IB0DVEH25|          4|            1|          2|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PyIl4h3dfRDp",
        "outputId": "96cdb76c-7747-4f36-d83f-8d9372861c12"
      },
      "source": [
        "vine_df.schema"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "StructType(List(StructField(review_id,StringType,true),StructField(star_rating,StringType,true),StructField(helpful_votes,IntegerType,true),StructField(total_votes,IntegerType,true),StructField(vine,StringType,true),StructField(verified_purchase,StringType,true)))"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hNr-qlIngblf",
        "outputId": "588c79a5-823e-4d70-826c-6c7e05cbc95f"
      },
      "source": [
        "#we need to convert the \"star_rating\" datatype into integer , since we have defined it as an integer in the \"vine_table\" we created in pgAdmin.\n",
        "#review_id_df = customer_clean_df.select([\"review_id\",\"customer_id\",\"product_id\",\"product_parent\",to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n",
        "#data_df = data_df.withColumn(\"Plays\", data_df[\"Plays\"].cast(IntegerType()))\n",
        "#customers_df = customers_df.withColumn(\"customer_count\",col(\"customer_count\").cast(\"int\"))\n",
        "vine1_df = vine_df.select([\"review_id\",vine_df[\"star_rating\"].cast(\"int\").alias(\"star_rating\"),\"helpful_votes\",\"total_votes\",\"vine\",\"verified_purchase\"])\n",
        "vine1_df .show(10)"
      ],
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R10048FCFWVJOH|          5|            0|          0|   N|                N|\n",
            "|R1008YTRJEH6U7|          1|            1|          2|   N|                Y|\n",
            "|R100F3WWLNCX5S|          5|            0|          0|   N|                Y|\n",
            "|R100IJSLJ25VPG|          5|            0|          0|   N|                Y|\n",
            "|R100JPDEL3NLYL|          3|            0|          0|   N|                N|\n",
            "|R100KWGZ8UZDA0|          1|            0|          1|   N|                Y|\n",
            "|R100LHWI1PCRDO|          4|            0|          0|   N|                Y|\n",
            "|R100MJFI7QHR78|          5|            0|          0|   N|                N|\n",
            "|R100MKHZ325MI7|          4|            2|          2|   N|                Y|\n",
            "|R100U625Y5RVRJ|          5|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "j4Z_3le8oYEv",
        "outputId": "89a17628-e931-420c-cc48-61440a3b97a1"
      },
      "source": [
        "vine1_df.schema"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "StructType(List(StructField(review_id,StringType,true),StructField(star_rating,IntegerType,true),StructField(helpful_votes,IntegerType,true),StructField(total_votes,IntegerType,true),StructField(vine,StringType,true),StructField(verified_purchase,StringType,true)))"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jITZhLkmY--J"
      },
      "source": [
        "### Analysing the vine data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Exuo6ebUsCqW",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "72807bd3-5603-496f-a860-f20172c9566b"
      },
      "source": [
        "# 1.Filter the Vine DataFrame by those that had 20+ total votes\n",
        "df1 = vine1_df.filter(vine1_df[\"total_votes\"] >= 20)\n",
        "df1.show(10)"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "| RI7WLRILVK1B0|          5|           31|         38|   N|                Y|\n",
            "|R31J96IMJ143Z3|          5|           93|         95|   N|                Y|\n",
            "|R2EX1UWV7ULXR0|          1|            7|         46|   N|                Y|\n",
            "|R2FG8CTCA2XYLO|          5|           21|         30|   N|                N|\n",
            "| RAX321OSHA01I|          5|           22|         23|   N|                N|\n",
            "|R24Z17F1ND33KS|          5|           49|         56|   N|                Y|\n",
            "|R2STHWTTUQJ4VB|          5|           24|         25|   N|                N|\n",
            "|R29SS9IM9NPPFE|          4|           37|         41|   N|                Y|\n",
            "|R2Z9W1GRHRTHP9|          5|           19|         20|   N|                Y|\n",
            "| RQK68GQIF4R6B|          5|           65|         69|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MFqR0WwonulM",
        "outputId": "ff6c3cef-e0ee-42d6-abdc-c9baaec4367c"
      },
      "source": [
        "#2.retrieve all the rows where the number of helpful_votes divided by total_votes is equal to or greater than 50%.\n",
        "df2 = df1.filter(df1[\"helpful_votes\"]/df1[\"total_votes\"] >= 0.5)\n",
        "df2.show(10)"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "| RI7WLRILVK1B0|          5|           31|         38|   N|                Y|\n",
            "|R31J96IMJ143Z3|          5|           93|         95|   N|                Y|\n",
            "|R2FG8CTCA2XYLO|          5|           21|         30|   N|                N|\n",
            "| RAX321OSHA01I|          5|           22|         23|   N|                N|\n",
            "|R24Z17F1ND33KS|          5|           49|         56|   N|                Y|\n",
            "|R2STHWTTUQJ4VB|          5|           24|         25|   N|                N|\n",
            "|R29SS9IM9NPPFE|          4|           37|         41|   N|                Y|\n",
            "|R2Z9W1GRHRTHP9|          5|           19|         20|   N|                Y|\n",
            "| RQK68GQIF4R6B|          5|           65|         69|   N|                Y|\n",
            "|R3OX39H666XSNP|          5|           23|         28|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "L3U6Niedoj8Z",
        "outputId": "1f92ef97-464b-4395-eb72-65e0f27d3f89"
      },
      "source": [
        "#3.Create a new DataFrame or table that retrieves all the rows where a review was written as part of the Vine program (paid), vine == 'Y'.\n",
        "vine_yes3_df = df2.filter(df2[\"vine\"] == \"Y\")\n",
        "vine_yes3_df.show(10)"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3VBGSOJVCSAC2|          5|           17|         20|   Y|                N|\n",
            "|R16TM8O9A3C5K7|          4|           95|        116|   Y|                N|\n",
            "| R9778FXEJZWE8|          5|           34|         36|   Y|                N|\n",
            "| R54KOWGL1NOPY|          5|          149|        157|   Y|                N|\n",
            "|R100G36GGWRVFT|          4|          172|        203|   Y|                N|\n",
            "|R13NLMRBSI0F8Y|          4|           25|         28|   Y|                N|\n",
            "| RRBM8RTSTA39C|          5|          272|        288|   Y|                N|\n",
            "| RNP01HW9YISJO|          4|           20|         23|   Y|                N|\n",
            "|R3IH9V45B5WJY5|          5|          245|        273|   Y|                N|\n",
            "|R2BX3X17IGCAKI|          3|           19|         32|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_t59bjl6pegI",
        "outputId": "9f3b37ef-b856-4e50-dd30-aa7312e9c35a"
      },
      "source": [
        "#4.Create a new DataFrame and retrieve all the rows where the review was not part of the Vine program (unpaid), vine == 'N'.\n",
        "vine_No4_df = df2.filter(df2[\"vine\"] == \"N\")\n",
        "vine_No4_df.show(10)\n"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "| RI7WLRILVK1B0|          5|           31|         38|   N|                Y|\n",
            "|R31J96IMJ143Z3|          5|           93|         95|   N|                Y|\n",
            "|R2FG8CTCA2XYLO|          5|           21|         30|   N|                N|\n",
            "| RAX321OSHA01I|          5|           22|         23|   N|                N|\n",
            "|R24Z17F1ND33KS|          5|           49|         56|   N|                Y|\n",
            "|R2STHWTTUQJ4VB|          5|           24|         25|   N|                N|\n",
            "|R29SS9IM9NPPFE|          4|           37|         41|   N|                Y|\n",
            "|R2Z9W1GRHRTHP9|          5|           19|         20|   N|                Y|\n",
            "| RQK68GQIF4R6B|          5|           65|         69|   N|                Y|\n",
            "|R3OX39H666XSNP|          5|           23|         28|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QsiO3ZWtsnVe",
        "outputId": "c00b1356-0fa5-4faa-9ced-7971a487cf75"
      },
      "source": [
        "vine_yes3_df.schema"
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "StructType(List(StructField(review_id,StringType,true),StructField(star_rating,IntegerType,true),StructField(helpful_votes,IntegerType,true),StructField(total_votes,IntegerType,true),StructField(vine,StringType,true),StructField(verified_purchase,StringType,true)))"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OR5CbNaIq6Io",
        "outputId": "89b4a2aa-1b27-4d80-e41a-0cd423f832a9"
      },
      "source": [
        "#5a.The number of 5-star reviews, and the percentage of 5-star reviews for the paid review vine == Y\n",
        "Paid_five_star_number = vine_yes3_df[vine_yes3_df['star_rating']== 5].count()\n",
        "Total_paid_number = vine_yes3_df.count()\n",
        "Percentage_five_star_vine = float(paid_five_star_number) / float(total_paid_number)\n",
        "print (\"Total_paid_number:\")\n",
        "print(Total_paid_number)\n",
        "print (\"Paid_five_star_number:\")\n",
        "print(Paid_five_star_number)\n",
        "print (\"Percentage_five_star_vine:\")\n",
        "print(Percentage_five_star_vine)"
      ],
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Total_paid_number:\n",
            "613\n",
            "Paid_five_star_number:\n",
            "222\n",
            "Percentage_five_star_vine:\n",
            "0.3621533442088091\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LmEqqAgCxjEx",
        "outputId": "f128f0ac-df64-45c1-d3c1-1f94fbb2b864"
      },
      "source": [
        "vine_No4_df.schema"
      ],
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "StructType(List(StructField(review_id,StringType,true),StructField(star_rating,IntegerType,true),StructField(helpful_votes,IntegerType,true),StructField(total_votes,IntegerType,true),StructField(vine,StringType,true),StructField(verified_purchase,StringType,true)))"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tFneYWZwxv8s",
        "outputId": "3e444690-f6c7-46d2-9456-96471df82359"
      },
      "source": [
        "#5b.The number of 5-star reviews, and the percentage of 5-star reviews for the unpaid review vine == N\n",
        "Unpaid_five_star_number = vine_No4_df[vine_No4_df['star_rating']== 5].count()\n",
        "Total_Unpaid_number = vine_No4_df.count()\n",
        "Percentage_five_star_vine_Unpaid = float(Unpaid_five_star_number) / float(Total_Unpaid_number)\n",
        "print (\"Total_Unpaid_number:\")\n",
        "print(Total_Unpaid_number)\n",
        "print (\"Unpaid_five_star_number:\")\n",
        "print(Unpaid_five_star_number)\n",
        "print (\"Percentage_five_star_vine_Unpaid:\")\n",
        "print(Percentage_five_star_vine_Unpaid)"
      ],
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Total_Unpaid_number:\n",
            "64934\n",
            "Unpaid_five_star_number:\n",
            "30530\n",
            "Percentage_five_star_vine_Unpaid:\n",
            "0.47016971078325687\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lFEto9RW0cNo"
      },
      "source": [
        "Conclusion: 36% of records that were part of the Vine program(paid) gave a 5-star rating.  \n",
        "47% of records that were not part of the Vine program(unpaid) also gave a 5-star rating. \n",
        "There were significantly less records that had Viner than those records that did have Viner, \n",
        "there is the possibility of biased in the Vine/Star-Rating reviews."
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}