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"
    },
    "nteract": {
      "version": "0.12.3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "V58rxea0HqSa",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "307c89c4-432d-41cf-f5a6-e622ab0657a0"
      },
      "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()"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rGet:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "\r0% [Connecting to archive.ubuntu.com (185.125.190.36)] [1 InRelease 14.2 kB/88.\r                                                                               \rGet:2 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "\r0% [Waiting for headers] [1 InRelease 20.0 kB/88.7 kB 23%] [Connected to cloud.\r                                                                               \rHit:3 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "\r0% [Waiting for headers] [1 InRelease 88.7 kB/88.7 kB 100%] [Connected to cloud\r                                                                               \rGet:4 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "\r                                                                               \rGet:5 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "\r                                                                               \rHit:6 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "\r                                                                               \rGet:7 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "\r0% [4 InRelease 56.2 kB/88.7 kB 63%] [Connecting to ppa.launchpad.net (185.125.\r0% [3 InRelease gpgv 242 kB] [4 InRelease 56.2 kB/88.7 kB 63%] [Waiting for hea\r0% [3 InRelease gpgv 242 kB] [Waiting for headers] [Waiting for headers] [Waiti\r                                                                               \rHit:8 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "\r0% [3 InRelease gpgv 242 kB] [Waiting for headers] [Connecting to ppa.launchpad\r                                                                               \rGet:9 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Hit:10 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Ign:11 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:12 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:13 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [1,063 kB]\n",
            "Get:14 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,901 kB]\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,527 kB]\n",
            "Get:16 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ Packages [85.6 kB]\n",
            "Get:17 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [2,075 kB]\n",
            "Get:18 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [1,063 kB]\n",
            "Get:19 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [824 kB]\n",
            "Get:20 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,304 kB]\n",
            "Get:21 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [1,107 kB]\n",
            "Get:22 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,336 kB]\n",
            "Fetched 16.6 MB in 4s (4,262 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_xKwTpATHqSe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9a9b05a4-2b10-4dba-d434-879c14e0991c"
      },
      "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",
          "name": "stdout",
          "text": [
            "--2022-07-26 00:56:10--  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-26 00:56:11 (1.61 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "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": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Beauty_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Beauty_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VivGtPztATsU",
        "outputId": "814d30ad-2ea2-495d-830e-11c496aabf9e"
      },
      "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|    1797882|R3I2DHQBR577SS|B001ANOOOE|       2102612|The Naked Bee Vit...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| Love this, excell...| 2015-08-31|\n",
            "|         US|   18381298|R1QNE9NQFJC2Y4|B0016J22EQ|     106393691|Alba Botanica Sun...|          Beauty|          5|            0|          0|   N|                Y|Thank you Alba Bo...| The great thing a...| 2015-08-31|\n",
            "|         US|   19242472|R3LIDG2Q4LJBAO|B00HU6UQAG|     375449471|Elysee Infusion S...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| Great Product, I'...| 2015-08-31|\n",
            "|         US|   19551372|R3KSZHPAEVPEAL|B002HWS7RM|     255651889|Diane D722 Color,...|          Beauty|          5|            0|          0|   N|                Y|          GOOD DEAL!| I use them as sho...| 2015-08-31|\n",
            "|         US|   14802407| RAI2OIG50KZ43|B00SM99KWU|     116158747|Biore UV Aqua Ric...|          Beauty|          5|            0|          0|   N|                Y|this soaks in qui...| This is my go-to ...| 2015-08-31|\n",
            "|         US|    2909389|R1R30FA4RB5P54|B000NYL1Z6|     166146615|Murad Clarifying ...|          Beauty|          4|            0|          0|   N|                Y|          Four Stars|                 Good| 2015-08-31|\n",
            "|         US|   19397215|R30IJKCGJBGPJH|B001SYWTFG|     111742328|CoverGirl Queen C...|          Beauty|          5|            0|          0|   N|                Y|            Good buy| Great eyeliner, d...| 2015-08-31|\n",
            "|         US|    3195210|R18GLJJPVQ1OVH|B005F2EVMQ|     255803087|Bifesta Mandom Ey...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars|Best makeup remover！| 2015-08-31|\n",
            "|         US|   52216383| R8TVYIJXLYJT0|B00M1SUW7K|     246816549|Can You Handlebar...|          Beauty|          5|            0|          0|   N|                Y|Tame the wild mus...| This is a great p...| 2015-08-31|\n",
            "|         US|   10278216|R1CJGF6M3PVHEZ|B001KYQA1S|       9612905|Maybelline Great ...|          Beauty|          1|            0|          2|   N|                Y|but it's like hav...| I thought it woul...| 2015-08-31|\n",
            "|         US|   24655453|R2A30ALEGLMCGN|B00SAQ9DZY|     292127037|12 New, High Qual...|          Beauty|          4|            1|          1|   N|                Y|        Good Product| These are great f...| 2015-08-31|\n",
            "|         US|   30788223|R134GP1Y3EQZ1L|B00HFQQ0VU|     311309827|Waterpik Aquarius...|          Beauty|          5|            0|          0|   N|                Y| Optimum Oral Health| Just got this.  N...| 2015-08-31|\n",
            "|         US|   11257536| R6CE3SOIUJGP4|B00PYL8MAA|     390030149|Proganix Agave Ne...|          Beauty|          4|            1|          1|   N|                N|   Love this cream !| Wish I had discov...| 2015-08-31|\n",
            "|         US|   29605778|R37CDLQZUPDZVM|B00D9NV2D4|     794858888|         Shea Butter|          Beauty|          5|            0|          0|   N|                Y|It works so much ...| I bought this to ...| 2015-08-31|\n",
            "|         US|   41238422|R1KH9XCK6GYT26|B008Y9M412|     874223919|Etude House Fresh...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| The tint is aweso...| 2015-08-31|\n",
            "|         US|   23620123|R3I4BQ6654MQNA|B00FWXBLHG|     464001209|Vintage Lil' Spon...|          Beauty|          5|            1|          1|   N|                Y|Great product, fa...| I'm in love with ...| 2015-08-31|\n",
            "|         US|   25564245|R3LUPG356F1D40|B003KL8CB0|     328680790|Jenna Jameson Hea...|          Beauty|          5|            3|          3|   N|                N|Does not smell ch...| I was given this ...| 2015-08-31|\n",
            "|         US|     801375|R3HNVRTHL9S2ZB|B006R2LNV0|     620737389|Korean Cosmetics_...|          Beauty|          5|            0|          0|   N|                N|          Five Stars| Magical everybody...| 2015-08-31|\n",
            "|         US|    9478730| R5HKL5RD4IUT8|B00SMIIR0W|     351973146|Schick Hydro Silk...|          Beauty|          5|            0|          0|   N|                Y|Love this Razor/T...| Works fantastic! ...| 2015-08-31|\n",
            "|         US|   18418407|R37Z8NBTHPR853|B003UKKH6O|      10034795|Oral-B Vitality P...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| excellent battery...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+---------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "64iOehgqATup",
        "outputId": "32559363-de45-492c-8af6-4ce47ab441c5"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5115666"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "  from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_e3zWoY5dMM8",
        "outputId": "6febdf96-12a6-4582-ae0a-b4866b395742"
      },
      "execution_count": 22,
      "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|    1797882|R3I2DHQBR577SS|B001ANOOOE|       2102612|The Naked Bee Vit...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| Love this, excell...| 2015-08-31|\n",
            "|         US|   18381298|R1QNE9NQFJC2Y4|B0016J22EQ|     106393691|Alba Botanica Sun...|          Beauty|          5|            0|          0|   N|                Y|Thank you Alba Bo...| The great thing a...| 2015-08-31|\n",
            "|         US|   19242472|R3LIDG2Q4LJBAO|B00HU6UQAG|     375449471|Elysee Infusion S...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| Great Product, I'...| 2015-08-31|\n",
            "|         US|   19551372|R3KSZHPAEVPEAL|B002HWS7RM|     255651889|Diane D722 Color,...|          Beauty|          5|            0|          0|   N|                Y|          GOOD DEAL!| I use them as sho...| 2015-08-31|\n",
            "|         US|   14802407| RAI2OIG50KZ43|B00SM99KWU|     116158747|Biore UV Aqua Ric...|          Beauty|          5|            0|          0|   N|                Y|this soaks in qui...| This is my go-to ...| 2015-08-31|\n",
            "|         US|    2909389|R1R30FA4RB5P54|B000NYL1Z6|     166146615|Murad Clarifying ...|          Beauty|          4|            0|          0|   N|                Y|          Four Stars|                 Good| 2015-08-31|\n",
            "|         US|   19397215|R30IJKCGJBGPJH|B001SYWTFG|     111742328|CoverGirl Queen C...|          Beauty|          5|            0|          0|   N|                Y|            Good buy| Great eyeliner, d...| 2015-08-31|\n",
            "|         US|    3195210|R18GLJJPVQ1OVH|B005F2EVMQ|     255803087|Bifesta Mandom Ey...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars|Best makeup remover！| 2015-08-31|\n",
            "|         US|   52216383| R8TVYIJXLYJT0|B00M1SUW7K|     246816549|Can You Handlebar...|          Beauty|          5|            0|          0|   N|                Y|Tame the wild mus...| This is a great p...| 2015-08-31|\n",
            "|         US|   10278216|R1CJGF6M3PVHEZ|B001KYQA1S|       9612905|Maybelline Great ...|          Beauty|          1|            0|          2|   N|                Y|but it's like hav...| I thought it woul...| 2015-08-31|\n",
            "|         US|   24655453|R2A30ALEGLMCGN|B00SAQ9DZY|     292127037|12 New, High Qual...|          Beauty|          4|            1|          1|   N|                Y|        Good Product| These are great f...| 2015-08-31|\n",
            "|         US|   30788223|R134GP1Y3EQZ1L|B00HFQQ0VU|     311309827|Waterpik Aquarius...|          Beauty|          5|            0|          0|   N|                Y| Optimum Oral Health| Just got this.  N...| 2015-08-31|\n",
            "|         US|   11257536| R6CE3SOIUJGP4|B00PYL8MAA|     390030149|Proganix Agave Ne...|          Beauty|          4|            1|          1|   N|                N|   Love this cream !| Wish I had discov...| 2015-08-31|\n",
            "|         US|   29605778|R37CDLQZUPDZVM|B00D9NV2D4|     794858888|         Shea Butter|          Beauty|          5|            0|          0|   N|                Y|It works so much ...| I bought this to ...| 2015-08-31|\n",
            "|         US|   41238422|R1KH9XCK6GYT26|B008Y9M412|     874223919|Etude House Fresh...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| The tint is aweso...| 2015-08-31|\n",
            "|         US|   23620123|R3I4BQ6654MQNA|B00FWXBLHG|     464001209|Vintage Lil' Spon...|          Beauty|          5|            1|          1|   N|                Y|Great product, fa...| I'm in love with ...| 2015-08-31|\n",
            "|         US|   25564245|R3LUPG356F1D40|B003KL8CB0|     328680790|Jenna Jameson Hea...|          Beauty|          5|            3|          3|   N|                N|Does not smell ch...| I was given this ...| 2015-08-31|\n",
            "|         US|     801375|R3HNVRTHL9S2ZB|B006R2LNV0|     620737389|Korean Cosmetics_...|          Beauty|          5|            0|          0|   N|                N|          Five Stars| Magical everybody...| 2015-08-31|\n",
            "|         US|    9478730| R5HKL5RD4IUT8|B00SMIIR0W|     351973146|Schick Hydro Silk...|          Beauty|          5|            0|          0|   N|                Y|Love this Razor/T...| Works fantastic! ...| 2015-08-31|\n",
            "|         US|   18418407|R37Z8NBTHPR853|B003UKKH6O|      10034795|Oral-B Vitality P...|          Beauty|          5|            0|          0|   N|                Y|          Five Stars| excellent battery...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+---------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oQNF5b_Od1f5",
        "outputId": "b5ed64d6-6f9b-4a25-946e-4668d5c99069"
      },
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5115666"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the vine_table. DataFrame\n",
        "vine_df = df.select(['review_id', 'star_rating','helpful_votes', 'total_votes', 'vine', 'verified_purchase'])\n",
        "vine_df.show(20)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LG_CLySydMP6",
        "outputId": "fd8a2372-94c4-428d-f1bf-01ac5636c44f"
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3I2DHQBR577SS|          5|            0|          0|   N|                Y|\n",
            "|R1QNE9NQFJC2Y4|          5|            0|          0|   N|                Y|\n",
            "|R3LIDG2Q4LJBAO|          5|            0|          0|   N|                Y|\n",
            "|R3KSZHPAEVPEAL|          5|            0|          0|   N|                Y|\n",
            "| RAI2OIG50KZ43|          5|            0|          0|   N|                Y|\n",
            "|R1R30FA4RB5P54|          4|            0|          0|   N|                Y|\n",
            "|R30IJKCGJBGPJH|          5|            0|          0|   N|                Y|\n",
            "|R18GLJJPVQ1OVH|          5|            0|          0|   N|                Y|\n",
            "| R8TVYIJXLYJT0|          5|            0|          0|   N|                Y|\n",
            "|R1CJGF6M3PVHEZ|          1|            0|          2|   N|                Y|\n",
            "|R2A30ALEGLMCGN|          4|            1|          1|   N|                Y|\n",
            "|R134GP1Y3EQZ1L|          5|            0|          0|   N|                Y|\n",
            "| R6CE3SOIUJGP4|          4|            1|          1|   N|                N|\n",
            "|R37CDLQZUPDZVM|          5|            0|          0|   N|                Y|\n",
            "|R1KH9XCK6GYT26|          5|            0|          0|   N|                Y|\n",
            "|R3I4BQ6654MQNA|          5|            1|          1|   N|                Y|\n",
            "|R3LUPG356F1D40|          5|            3|          3|   N|                N|\n",
            "|R3HNVRTHL9S2ZB|          5|            0|          0|   N|                N|\n",
            "| R5HKL5RD4IUT8|          5|            0|          0|   N|                Y|\n",
            "|R37Z8NBTHPR853|          5|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "vine_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mFF3tqNodMVx",
        "outputId": "64abc6d5-9051-45fc-fe2c-0748bd5593c8"
      },
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5115666"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Drop Nans\n",
        "cleaned_df = vine_df.dropna()\n",
        "cleaned_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "U6h1cgkLdMTA",
        "outputId": "cab9d615-9209-4ab9-b913-528fdf59d41b"
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5115452"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import col\n",
        "\n",
        "# retrieve all the rows where the total_votes count is equal to or greater than 20\n",
        "total_votes_filter_df = cleaned_df.filter(col(\"total_votes\") >= 20)\n",
        "total_votes_filter_df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8M_tVN6UATxD",
        "outputId": "0658202a-0932-43ee-965e-901b88a84140"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2QRUE9REK8OUC|          5|           18|         23|   N|                Y|\n",
            "|R2POXRW6PXHXZG|          5|           25|         30|   N|                Y|\n",
            "| RZN43XRZ89IIJ|          3|           10|         27|   N|                N|\n",
            "|R29Q748WSHZ3SN|          5|           23|         24|   N|                Y|\n",
            "| R46UL5G5HEPRZ|          5|           18|         20|   N|                N|\n",
            "|R2UQHSNWU6WTZX|          5|           74|         76|   N|                N|\n",
            "|R2HSRI3D6E2M9Y|          5|           69|         71|   N|                Y|\n",
            "|R17ARFSDV555EW|          1|           29|         29|   N|                Y|\n",
            "|R3GL5156FFEDQA|          5|           20|         21|   N|                Y|\n",
            "|R29KQJC9PIYWQS|          5|           25|         26|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "total_votes_filter_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_nDjaYI7CQ9x",
        "outputId": "026c1a0e-f777-49a4-a53d-ba43025f4bf8"
      },
      "execution_count": 29,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "79227"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# retrieve all the rows where the number of helpful_votes divided by total_votes is equal to or greater than 50%.\n",
        "total_votes_divided_df = total_votes_filter_df.filter(col(\"helpful_votes\")/col(\"total_votes\") >= .50)\n",
        "total_votes_divided_df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MzxUJZ-7BUQ-",
        "outputId": "2bea34f2-a2ae-4dcd-aab3-fc91fef4b69d"
      },
      "execution_count": 30,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2QRUE9REK8OUC|          5|           18|         23|   N|                Y|\n",
            "|R2POXRW6PXHXZG|          5|           25|         30|   N|                Y|\n",
            "|R29Q748WSHZ3SN|          5|           23|         24|   N|                Y|\n",
            "| R46UL5G5HEPRZ|          5|           18|         20|   N|                N|\n",
            "|R2UQHSNWU6WTZX|          5|           74|         76|   N|                N|\n",
            "|R2HSRI3D6E2M9Y|          5|           69|         71|   N|                Y|\n",
            "|R17ARFSDV555EW|          1|           29|         29|   N|                Y|\n",
            "|R3GL5156FFEDQA|          5|           20|         21|   N|                Y|\n",
            "|R29KQJC9PIYWQS|          5|           25|         26|   N|                Y|\n",
            "| RYWUJT2A33VJH|          5|           20|         22|   N|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "total_votes_divided_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_QnPEpbIATzK",
        "outputId": "cc1c996a-02f8-4bd8-c210-686894ba9130"
      },
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "74760"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#retrieve all the rows where a review was written as part of the Vine program (paid), vine == 'Y'.\n",
        "total_votes_vine_df = total_votes_divided_df.filter(col(\"vine\") == 'Y')\n",
        "total_votes_vine_df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fih2G8MbAT1k",
        "outputId": "4c753c3f-231c-4664-b72f-7b59657fced2"
      },
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R106V6GUNWRSSS|          5|          857|        889|   Y|                N|\n",
            "|R37N8KZS48W36A|          5|           36|         37|   Y|                N|\n",
            "|R1LNEQ2MQE03PD|          5|           49|         66|   Y|                N|\n",
            "|R1ECPWPUNK36ES|          5|           29|         31|   Y|                N|\n",
            "|R16BUM7UQZLOM6|          3|          107|        112|   Y|                N|\n",
            "| R7NWIOCA5RVPR|          5|           19|         26|   Y|                N|\n",
            "|R3NTGOCUZB33JK|          5|           40|         47|   Y|                Y|\n",
            "| RQX3RHGMG7UBN|          5|          186|        200|   Y|                N|\n",
            "|R3E6QYKZDST042|          4|           33|         39|   Y|                N|\n",
            "|R2GR106PWUN85O|          4|           36|         39|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "total_votes_vine_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cKlKIQuDAT3k",
        "outputId": "f9907b3c-a95b-47db-8921-0cd6f5e719af"
      },
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "647"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#retrieve all the rows where the review was not part of the Vine program (unpaid), vine == 'N'.\n",
        "total_votes_unpaid_df = total_votes_divided_df.filter(col(\"vine\") == 'N')\n",
        "total_votes_unpaid_df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "X_fmrUTlAT7-",
        "outputId": "8d9cd4f0-328c-4290-f5d8-e88d4ea3558d"
      },
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R2QRUE9REK8OUC|          5|           18|         23|   N|                Y|\n",
            "|R2POXRW6PXHXZG|          5|           25|         30|   N|                Y|\n",
            "|R29Q748WSHZ3SN|          5|           23|         24|   N|                Y|\n",
            "| R46UL5G5HEPRZ|          5|           18|         20|   N|                N|\n",
            "|R2UQHSNWU6WTZX|          5|           74|         76|   N|                N|\n",
            "|R2HSRI3D6E2M9Y|          5|           69|         71|   N|                Y|\n",
            "|R17ARFSDV555EW|          1|           29|         29|   N|                Y|\n",
            "|R3GL5156FFEDQA|          5|           20|         21|   N|                Y|\n",
            "|R29KQJC9PIYWQS|          5|           25|         26|   N|                Y|\n",
            "| RYWUJT2A33VJH|          5|           20|         22|   N|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "total_votes_unpaid_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "W9iiSc9fAT-j",
        "outputId": "86909ba4-fe21-419d-d493-74a348ab21ac"
      },
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "74113"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Determine the total number of reviews\n",
        "total_votes_divided_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2LNm9UsaAUBT",
        "outputId": "586c0357-b495-4cf0-9096-8e4487395cef"
      },
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "74760"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Determine the number of 5-star IN PAID reviews\n",
        "paid_star_rating_df = total_votes_vine_df.filter(col(\"star_rating\") == 5)\n",
        "paid_star_rating_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ugp3_jgfAUDi",
        "outputId": "d1a2f34e-7ec5-4caa-98a5-3344b546bb33"
      },
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "229"
            ]
          },
          "metadata": {},
          "execution_count": 37
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Determine the number of 5-star IN UNPAID reviews\n",
        "unpaid_star_rating_df = total_votes_unpaid_df.filter(col(\"star_rating\") == 5)\n",
        "unpaid_star_rating_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0SXM4mY7V0KH",
        "outputId": "1d0b7989-d4a2-4313-fc5e-e0b77dc4d12e"
      },
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "43217"
            ]
          },
          "metadata": {},
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import col,when,count,lit,round\n",
        "results_df = total_votes_divided_df.groupBy(\"Vine\").agg(\n",
        "    count(col(\"vine\")).alias(\"Total_Reviews\"),\n",
        "    count(when(col(\"star_rating\") == 5, True)).alias(\"5_Star_Reviews\"),\n",
        "    (count(when(col(\"star_rating\") == 5, True))/count(col(\"vine\"))*100).alias(\"5_Star_Percentage\")).show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IVh-62ZiAUM-",
        "outputId": "05665412-d00a-4f0d-c6e1-a4aa932d5580"
      },
      "execution_count": 61,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-------------+--------------+------------------+\n",
            "|Vine|Total_Reviews|5_Star_Reviews| 5_Star_Percentage|\n",
            "+----+-------------+--------------+------------------+\n",
            "|   Y|          647|           229|35.394126738794434|\n",
            "|   N|        74113|         43217|58.312306882733125|\n",
            "+----+-------------+--------------+------------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "F-pejuDpAUR4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "2F64eyQ8AUUo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "r6c1QJhYAUXK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "qicc96Y2AUaH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "Flx8lQ5eAUcW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "qAKI_14TAUe2"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "2e5IjYKUAUhN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "naMlAAU-AUjk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "2mF1GIhXAUl-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "nuS4HM0UAUpB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "pvlivmUNAUrT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "ffadNmvxAUuW"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}