Vine_Review_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "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": "r0v4EOoHVhAf",
        "outputId": "75f66708-1eb3-4d27-e331-f192efc405a3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rGet:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Get:2 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Ign:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:4 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "Hit:5 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:6 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Hit:7 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Get:8 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:9 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Hit:10 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:11 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [83.3 kB]\n",
            "Get:12 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:13 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [913 kB]\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,546 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,990 kB]\n",
            "Get:17 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,323 kB]\n",
            "Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,422 kB]\n",
            "Get:19 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [48.3 kB]\n",
            "Fetched 11.5 MB in 6s (1,796 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.3.0'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "!wget -q http://www.apache.org/dist/spark/spark-3.3.0/spark-3.3.0-bin-hadoop3.tgz\n",
        "!tar xf spark-3.3.0-bin-hadoop3.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-hadoop3\"\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": "s61yKIpmWM5_",
        "outputId": "7101318c-c96c-4265-eba4-60206e959392"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-10-02 20:17:37--  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",
            "\rpostgresql-42.2.16.   0%[                    ]       0  --.-KB/s               \rpostgresql-42.2.16. 100%[===================>] 979.38K  --.-KB/s    in 0.08s   \n",
            "\n",
            "2022-10-02 20:17:37 (11.7 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"M16-Review-Analysis\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "K-_sYWyiWNhC"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://amazon-officereviews.s3.amazonaws.com/amazon_reviews_us_Office_Products_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Office_Products_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mQmBcmA7WibH",
        "outputId": "9e47ab02-986a-4fa5-db5a-3d5703c542d3"
      },
      "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|   43081963|R18RVCKGH1SSI9|B001BM2MAC|     307809868|Scotch Cushion Wr...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|      Great product.|2015-08-31 00:00:00|\n",
            "|         US|   10951564|R3L4L6LW1PUOFY|B00DZYEXPQ|      75004341|Dust-Off Compress...| Office Products|          5|            0|          1|   N|                Y|Phffffffft, Phfff...|What's to say abo...|2015-08-31 00:00:00|\n",
            "|         US|   21143145|R2J8AWXWTDX2TF|B00RTMUHDW|     529689027|Amram Tagger Stan...| Office Products|          5|            0|          0|   N|                Y|but I am sure I w...|Haven't used yet,...|2015-08-31 00:00:00|\n",
            "|         US|   52782374|R1PR37BR7G3M6A|B00D7H8XB6|     868449945|AmazonBasics 12-S...| Office Products|          1|            2|          3|   N|                Y|and the shredder ...|Although this was...|2015-08-31 00:00:00|\n",
            "|         US|   24045652|R3BDDDZMZBZDPU|B001XCWP34|      33521401|Derwent Colored P...| Office Products|          4|            0|          0|   N|                Y|          Four Stars|Gorgeous colors a...|2015-08-31 00:00:00|\n",
            "|         US|   21751234| R8T6MO75ND212|B004J2NBCO|     214932869|Quartet Magnetic ...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Perfect for plann...|2015-08-31 00:00:00|\n",
            "|         US|    9109358|R2YWMQT2V11XYZ|B00MOPAG8K|     863351797|KITLEX40X2592UNV2...| Office Products|          5|            0|          0|   N|                N|          Five Stars|Gold plated fuser...|2015-08-31 00:00:00|\n",
            "|         US|    9967215|R1V2HYL6OI9V39|B003AHIK7U|     383470576|Bible Dry Highlig...| Office Products|          5|            6|          6|   N|                Y|Won't bleed throu...|I have used these...|2015-08-31 00:00:00|\n",
            "|         US|   11234247|R3BLQBKUNXGFS4|B006TKH2RO|     999128878|Parker Ingenuity ...| Office Products|          5|            0|          0|   N|                Y|           Nice pen!|Heavy pen that wr...|2015-08-31 00:00:00|\n",
            "|         US|   12731488|R17MOWJCAR9Y8Q|B00W61M9K0|     622066861| RFID Card Protector| Office Products|          5|            0|          0|   N|                Y| Credit card sleeves|Not sure if they ...|2015-08-31 00:00:00|\n",
            "|         US|   49861762|R11EPG1GHOXMGB|B00BXOGI3A|     688569009|The Original Stan...| Office Products|          4|            0|          0|   N|                N|          Four Stars|Great product for...|2015-08-31 00:00:00|\n",
            "|         US|    1541556|R2797NKTEX5THN|B0018RHWGE|     513803406|Zebra Sparky-1 WK...| Office Products|          5|            0|          0|   N|                Y|Love these highli...|Love these highli...|2015-08-31 00:00:00|\n",
            "|         US|   10579374|R2RUTMJS50E4WF|B00IA8H2OG|     201212366|E-Z Ink (TM) Comp...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Received on time,...|2015-08-31 00:00:00|\n",
            "|         US|   43622307|R1FZYL1HX7O4RM|B007TOIA66|     954138793|Eric 11\" Giant Si...| Office Products|          1|            0|          0|   N|                Y|            One Star|worked about a mo...|2015-08-31 00:00:00|\n",
            "|         US|   50773856|R22ENT0KU5CHPX|B00EANURCE|      43216076|Brother MFCJ450 W...| Office Products|          5|            0|          0|   N|                Y|Brothers Wireless...|I purchased this ...|2015-08-31 00:00:00|\n",
            "|         US|   10165223|R2E5M7FWJC9JLE|B00T3Q0EOO|     637096152|Clarity D613 DECT...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Everything works ...|2015-08-31 00:00:00|\n",
            "|         US|   14744748|R31BNW3O2Q5J3E|B00WN0N818|     710040412|Permo 50 Feet Whi...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|          Thank you!|2015-08-31 00:00:00|\n",
            "|         US|   37536631|R1JMQF1OOMNF49|B001GAOTSW|     696023182|Pilot G2 Retracta...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|     Love these pens|2015-08-31 00:00:00|\n",
            "|         US|     126687| RJH6WQDOA6CZP|B003BNY7PW|     474277918|AT&T DECT 6.0 Acc...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|The product is ev...|2015-08-31 00:00:00|\n",
            "|         US|   31130095|R2EVL0Z5JVV86X|B00092RJX0|     887474295|Pilot G2 Retracta...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|           Great buy|2015-08-31 00:00:00|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.types import IntegerType\n",
        "from pyspark.sql.functions import col\n",
        "\n",
        "vine1_df = df.withColumn(\"star_rating\",col(\"star_rating\").cast(\"Integer\"))\n",
        "vine1_df.dtypes"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RORbYeKitESF",
        "outputId": "3092b3d4-f37c-4ff5-d3cc-47a8bb52eba3"
      },
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('marketplace', 'string'),\n",
              " ('customer_id', 'int'),\n",
              " ('review_id', 'string'),\n",
              " ('product_id', 'string'),\n",
              " ('product_parent', 'int'),\n",
              " ('product_title', 'string'),\n",
              " ('product_category', 'string'),\n",
              " ('star_rating', 'int'),\n",
              " ('helpful_votes', 'int'),\n",
              " ('total_votes', 'int'),\n",
              " ('vine', 'string'),\n",
              " ('verified_purchase', 'string'),\n",
              " ('review_headline', 'string'),\n",
              " ('review_body', 'string'),\n",
              " ('review_date', 'timestamp')]"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the vine_table DataFrame\n",
        "vine_df = vine1_df.select([\"review_id\", \"star_rating\",\"helpful_votes\",\"total_votes\",\"vine\",\"verified_purchase\"])\n",
        "vine_df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KU78xDLytMnO",
        "outputId": "efb9680c-20b9-4ede-efcf-21af8d089559"
      },
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R18RVCKGH1SSI9|          5|            0|          0|   N|                Y|\n",
            "|R3L4L6LW1PUOFY|          5|            0|          1|   N|                Y|\n",
            "|R2J8AWXWTDX2TF|          5|            0|          0|   N|                Y|\n",
            "|R1PR37BR7G3M6A|          1|            2|          3|   N|                Y|\n",
            "|R3BDDDZMZBZDPU|          4|            0|          0|   N|                Y|\n",
            "| R8T6MO75ND212|          5|            0|          0|   N|                Y|\n",
            "|R2YWMQT2V11XYZ|          5|            0|          0|   N|                N|\n",
            "|R1V2HYL6OI9V39|          5|            6|          6|   N|                Y|\n",
            "|R3BLQBKUNXGFS4|          5|            0|          0|   N|                Y|\n",
            "|R17MOWJCAR9Y8Q|          5|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create new table with total votes equal or greater than 20\n",
        "total_votes20_df = vine_df.filter('total_votes>=20')\n",
        "total_votes20_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZeGL71fbZgKA",
        "outputId": "dcf8ca0e-00b1-4746-fcf5-854a990750ab"
      },
      "execution_count": 52,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R243UXMMSSZVS6|          2|          254|        254|   N|                Y|\n",
            "|R2P92EHR0S5GBB|          1|          110|        159|   N|                Y|\n",
            "| RN93Z4XPNAANY|          5|           31|         31|   N|                Y|\n",
            "|R1O74T9XO3OPQ4|          5|           38|         38|   N|                Y|\n",
            "|R3DMIHE5BMUT0R|          3|           21|         21|   N|                Y|\n",
            "|R1QWY17NLHQSB5|          5|           36|         36|   N|                Y|\n",
            "|R3LANI678T2YQS|          5|           39|         47|   N|                N|\n",
            "| RLV4XQ49EBYYU|          1|           56|         65|   N|                Y|\n",
            "| RPBPAJKFD0T26|          5|           63|         63|   N|                Y|\n",
            "|R1IHO8C56KBN6C|          1|           61|         64|   N|                Y|\n",
            "|R3HZT0MTHYQBU7|          1|          221|        221|   N|                Y|\n",
            "|R2ETWIKUXWV2FA|          1|           15|         32|   N|                Y|\n",
            "|R1M9644S4471JU|          5|           20|         20|   N|                Y|\n",
            "| R6FPJVAWQ472P|          5|           33|         34|   N|                Y|\n",
            "| RYQ3KOWJLYPWW|          4|           19|         20|   N|                Y|\n",
            "|R2J6UYRU7AX8NS|          5|           23|         24|   N|                Y|\n",
            "|R32Y1KGXWPHEVP|          5|           44|         44|   N|                N|\n",
            "|R3M14RO30UBS32|          2|           22|         30|   N|                Y|\n",
            "|R1FJ1LLYMAIINJ|          2|           27|         28|   N|                Y|\n",
            "| RB32OZDWJ1MXB|          1|          161|        170|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create DF of table where the # of helpful_votes/total_votes >= 50%\n",
        "helpful_votes_df = total_votes20_df.filter(\"helpful_votes/total_votes>=.50\")\n",
        "helpful_votes_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Gf0ppjEya5RM",
        "outputId": "2a6b76cf-ef2d-42df-f09e-bc3d72642f53"
      },
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R243UXMMSSZVS6|          2|          254|        254|   N|                Y|\n",
            "|R2P92EHR0S5GBB|          1|          110|        159|   N|                Y|\n",
            "| RN93Z4XPNAANY|          5|           31|         31|   N|                Y|\n",
            "|R1O74T9XO3OPQ4|          5|           38|         38|   N|                Y|\n",
            "|R3DMIHE5BMUT0R|          3|           21|         21|   N|                Y|\n",
            "|R1QWY17NLHQSB5|          5|           36|         36|   N|                Y|\n",
            "|R3LANI678T2YQS|          5|           39|         47|   N|                N|\n",
            "| RLV4XQ49EBYYU|          1|           56|         65|   N|                Y|\n",
            "| RPBPAJKFD0T26|          5|           63|         63|   N|                Y|\n",
            "|R1IHO8C56KBN6C|          1|           61|         64|   N|                Y|\n",
            "|R3HZT0MTHYQBU7|          1|          221|        221|   N|                Y|\n",
            "|R1M9644S4471JU|          5|           20|         20|   N|                Y|\n",
            "| R6FPJVAWQ472P|          5|           33|         34|   N|                Y|\n",
            "| RYQ3KOWJLYPWW|          4|           19|         20|   N|                Y|\n",
            "|R2J6UYRU7AX8NS|          5|           23|         24|   N|                Y|\n",
            "|R32Y1KGXWPHEVP|          5|           44|         44|   N|                N|\n",
            "|R3M14RO30UBS32|          2|           22|         30|   N|                Y|\n",
            "|R1FJ1LLYMAIINJ|          2|           27|         28|   N|                Y|\n",
            "| RB32OZDWJ1MXB|          1|          161|        170|   N|                Y|\n",
            "|R1O5FVFCO6MEWX|          1|           41|         42|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create table where the review is part of the Vine program (paid)\n",
        "vine_reviews_df = helpful_votes_df.filter(helpful_votes_df[\"vine\"] == 'Y')\n",
        "vine_reviews_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0S-LREdbbotE",
        "outputId": "f3cbe919-b026-4664-acd6-b51ef44dbe3f"
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3JIGR122X50ZV|          5|         2280|       2314|   Y|                N|\n",
            "|R3KV8P8WLD2KKC|          5|          263|        286|   Y|                N|\n",
            "|R2LFY2ZPJ3J7RH|          4|           36|         38|   Y|                N|\n",
            "|R3P01OXMA0U0CA|          5|          114|        117|   Y|                N|\n",
            "|R2SCMXBD9OQP8Z|          2|           22|         26|   Y|                N|\n",
            "|R2FGS9ECOK5IQN|          5|           22|         23|   Y|                N|\n",
            "| R1V9CO41XS6QY|          2|           56|         58|   Y|                N|\n",
            "|R1LEP1JGXRY2YT|          2|           18|         20|   Y|                N|\n",
            "|R1ZWQJ3ZBSIZ1K|          4|           19|         21|   Y|                Y|\n",
            "| RX1G4FUE5HZGN|          3|           52|         58|   Y|                N|\n",
            "|R2V4NC4FH8IF1Y|          3|           58|         63|   Y|                N|\n",
            "|R1F1PGV3MEL818|          5|          142|        148|   Y|                N|\n",
            "| RFLM1DZ2O0ZE3|          4|           39|         39|   Y|                N|\n",
            "|R2BYBP0R21WYYC|          5|           30|         33|   Y|                N|\n",
            "|R28T0ZQLIYREBI|          5|           45|         47|   Y|                N|\n",
            "|R1EEZAMB8BAB4I|          5|           45|         49|   Y|                N|\n",
            "|R1MT9QOVCW2NSC|          5|           21|         24|   Y|                N|\n",
            "| R3W9PAL4OVHKD|          5|           35|         41|   Y|                N|\n",
            "| R1NMLNZD7JKLY|          5|           40|         44|   Y|                N|\n",
            "| RZ2TREG2ME3K7|          5|           26|         28|   Y|                N|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create table where the review is NOT part of the Vine program (Nonpaid)\n",
        "non_vine_reviews_df = helpful_votes_df.filter(helpful_votes_df[\"vine\"] == 'N')\n",
        "non_vine_reviews_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "alXbRHpNcjUX",
        "outputId": "85a5a692-b284-4374-95d7-f37e7b83cde1"
      },
      "execution_count": 42,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R243UXMMSSZVS6|          2|          254|        254|   N|                Y|\n",
            "|R2P92EHR0S5GBB|          1|          110|        159|   N|                Y|\n",
            "| RN93Z4XPNAANY|          5|           31|         31|   N|                Y|\n",
            "|R1O74T9XO3OPQ4|          5|           38|         38|   N|                Y|\n",
            "|R3DMIHE5BMUT0R|          3|           21|         21|   N|                Y|\n",
            "|R1QWY17NLHQSB5|          5|           36|         36|   N|                Y|\n",
            "|R3LANI678T2YQS|          5|           39|         47|   N|                N|\n",
            "| RLV4XQ49EBYYU|          1|           56|         65|   N|                Y|\n",
            "| RPBPAJKFD0T26|          5|           63|         63|   N|                Y|\n",
            "|R1IHO8C56KBN6C|          1|           61|         64|   N|                Y|\n",
            "|R3HZT0MTHYQBU7|          1|          221|        221|   N|                Y|\n",
            "|R1M9644S4471JU|          5|           20|         20|   N|                Y|\n",
            "| R6FPJVAWQ472P|          5|           33|         34|   N|                Y|\n",
            "| RYQ3KOWJLYPWW|          4|           19|         20|   N|                Y|\n",
            "|R2J6UYRU7AX8NS|          5|           23|         24|   N|                Y|\n",
            "|R32Y1KGXWPHEVP|          5|           44|         44|   N|                N|\n",
            "|R3M14RO30UBS32|          2|           22|         30|   N|                Y|\n",
            "|R1FJ1LLYMAIINJ|          2|           27|         28|   N|                Y|\n",
            "| RB32OZDWJ1MXB|          1|          161|        170|   N|                Y|\n",
            "|R1O5FVFCO6MEWX|          1|           41|         42|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import count"
      ],
      "metadata": {
        "id": "VQrepKfIdaRc"
      },
      "execution_count": 43,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Total # of paid reviews:\n",
        "vine_reviews_count = vine_reviews_df.count()\n",
        "vine_reviews_count"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "heSm4O5Xmkz-",
        "outputId": "b2d189cc-fa2f-4d6d-c2bc-c445989595ac"
      },
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "969"
            ]
          },
          "metadata": {},
          "execution_count": 44
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total # of NON paid reviews:\n",
        "nonvine_reviews_count = non_vine_reviews_df.count()\n",
        "nonvine_reviews_count"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UixybxG6qhjN",
        "outputId": "baf1aa70-d0f4-4307-a1e5-3e2dfd84ef8f"
      },
      "execution_count": 45,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "43745"
            ]
          },
          "metadata": {},
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total Number of 5-star reviews:\n",
        "reviews_5stars = vine_df.filter(\"star_rating == 5\").count()\n",
        "reviews_5stars"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jamiZQptnJ9J",
        "outputId": "afe2a460-5708-404a-9950-975b2c97e484"
      },
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1584177"
            ]
          },
          "metadata": {},
          "execution_count": 46
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Count Helpful votes\n",
        "helpful_review_count = helpful_votes_df.count()\n",
        "helpful_review_count"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CC5CBQMSq5h7",
        "outputId": "8f8cffaf-61b9-4ea5-e0c6-0959080c74ec"
      },
      "execution_count": 47,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "44714"
            ]
          },
          "metadata": {},
          "execution_count": 47
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Number of paid 5-star reviews:\n",
        "paid_reviews_5stars = vine_reviews_df.filter(\"star_rating == 5\").count()\n",
        "paid_reviews_5stars"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2jWAsvHUnkoz",
        "outputId": "27c9a4db-ab4f-4cfe-efc4-c4ac74dd745c"
      },
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "430"
            ]
          },
          "metadata": {},
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Number of Nonpaid 5-star reviews:\n",
        "nonpaid_reviews_5stars = non_vine_reviews_df.filter(\"star_rating == 5\").count()\n",
        "nonpaid_reviews_5stars"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cmFUz5SUoclK",
        "outputId": "8b6940f4-27b2-473e-b143-30a8ed74e120"
      },
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "19233"
            ]
          },
          "metadata": {},
          "execution_count": 49
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# % of paid 5star reviews:\n",
        "perc_paid_5stars = (paid_reviews_5stars/vine_reviews_count)*100\n",
        "perc_paid_5stars"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1-abrJM1onOg",
        "outputId": "dd9595d5-6b13-4bce-89d2-fc7a41c01714"
      },
      "execution_count": 50,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "44.375644994840044"
            ]
          },
          "metadata": {},
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# % of nonpaid 5star reviews:\n",
        "perc_nonpaid_5stars = (nonpaid_reviews_5stars/nonvine_reviews_count)*100\n",
        "perc_nonpaid_5stars"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QcSZehyepFz9",
        "outputId": "769fb775-3e21-459c-bc16-cbc5a78bff3b"
      },
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "43.96616756200709"
            ]
          },
          "metadata": {},
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Total for overall reviews:\n",
        "overall_reviews_count = vine_df.count()\n",
        "overall_reviews_count"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gKCPrq1Ur8Um",
        "outputId": "4d1881ed-15b4-4fdb-d2ac-820044ff7ca1"
      },
      "execution_count": 53,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2642434"
            ]
          },
          "metadata": {},
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Percentage of paid 5-stars reviews vs. Overall reviews\n",
        "overall_perc_paid_reviews = round((paid_reviews_5stars/overall_reviews_count)*100,2)\n",
        "overall_perc_paid_reviews"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fpPRK03F7zz-",
        "outputId": "44a88d5a-7946-460e-bc1c-190acd82e8b8"
      },
      "execution_count": 56,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.02"
            ]
          },
          "metadata": {},
          "execution_count": 56
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Percentage of paid 5-stars reviews vs. Overall reviews\n",
        "overall_perc_nonpaid_reviews = round((nonpaid_reviews_5stars/overall_reviews_count)*100,2)\n",
        "overall_perc_nonpaid_reviews"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GTFpu1Bx8QJ6",
        "outputId": "9775d547-19a8-4511-dcfb-7f02e185e806"
      },
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.73"
            ]
          },
          "metadata": {},
          "execution_count": 57
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create a new DataFrame to summarize all information:\n",
        "\n",
        "from pyspark.sql.types import StructType, StructField, StringType, IntegerType, FloatType\n",
        "\n",
        "finaldf = [\n",
        "    (\"Overall Reviews\", overall_reviews_count),\n",
        "    (\"Helpful Reviews\", helpful_review_count),\n",
        "    (\"Total Paid Reviews\", vine_reviews_count),\n",
        "    (\"Total Unpaid Reviews\", nonvine_reviews_count),\n",
        "    (\"Paid Five Star Reviews\", paid_reviews_5stars),\n",
        "    (\"Unpaid Five Star Reviews\", nonpaid_reviews_5stars),\n",
        "    (\"Percentage of Paid 5-star Reviews\", round(perc_paid_5stars, 2)),\n",
        "    (\"Percentage of Unpaid 5-star Reviews\", round(perc_nonpaid_5stars, 2)),\n",
        "    (\"Paid reviews Overall percentage\", overall_perc_paid_reviews),\n",
        "    (\"NOT Paid reviews Overall percentage\", overall_perc_nonpaid_reviews)\n",
        "]\n",
        "\n",
        "schema = StructType([\n",
        "    StructField(\"Description\", StringType(), True),\\\n",
        "    StructField(\"Count/Percent\", StringType(), True),\\\n",
        "])\n",
        "\n",
        "Summary_df = spark.createDataFrame(data=finaldf, schema=schema) \n",
        "Summary_df.printSchema()\n",
        "Summary_df.show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sK1tjvqi8jSi",
        "outputId": "e2767cc4-365f-4f46-8459-5038ad3281e5"
      },
      "execution_count": 61,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Description: string (nullable = true)\n",
            " |-- Count/Percent: string (nullable = true)\n",
            "\n",
            "+-----------------------------------+-------------+\n",
            "|Description                        |Count/Percent|\n",
            "+-----------------------------------+-------------+\n",
            "|Overall Reviews                    |2642434      |\n",
            "|Helpful Reviews                    |44714        |\n",
            "|Total Paid Reviews                 |969          |\n",
            "|Total Unpaid Reviews               |43745        |\n",
            "|Paid Five Star Reviews             |430          |\n",
            "|Unpaid Five Star Reviews           |19233        |\n",
            "|Percentage of Paid 5-star Reviews  |44.38        |\n",
            "|Percentage of Unpaid 5-star Reviews|43.97        |\n",
            "|Paid reviews Overall percentage    |0.02         |\n",
            "|NOT Paid reviews Overall percentage|0.73         |\n",
            "+-----------------------------------+-------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "79GnEW12--ZO"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}