VideoGames.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "VideoGames.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyOjQ2psWx6bEPHiyRCONm2b",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Niraj-Khatri/Pyspark-AWS/blob/master/VideoGames.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q4deaPOvKnNv"
      },
      "source": [
        "# Setup Spark and Import Dataset"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B5zcNgYgKsAc",
        "outputId": "93a58fd6-4a91-4ee9-975f-857a04091e37"
      },
      "source": [
        "import os\n",
        "# Find the latest version of spark 3.0  from http://www-us.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.0.2'\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",
        "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": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rGet: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 [696 B]\n",
            "Hit:8 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:9 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Get:10 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release.gpg [836 B]\n",
            "Hit:11 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:12 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,295 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",
            "Hit:15 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Get:16 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,796 kB]\n",
            "Get:18 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [715 kB]\n",
            "Get:19 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [919 kB]\n",
            "Get:20 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,202 kB]\n",
            "Get:21 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [594 kB]\n",
            "Get:22 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [2,755 kB]\n",
            "Fetched 11.5 MB in 3s (3,350 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rXdJzVLRKX23",
        "outputId": "48877ce8-ff38-4825-cb54-0a7c460dd5a9"
      },
      "source": [
        "# Connect to Postgres\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.9.jar"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2021-09-07 19:56:34--  https://jdbc.postgresql.org/download/postgresql-42.2.9.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: 914037 (893K) [application/java-archive]\n",
            "Saving to: ‘postgresql-42.2.9.jar’\n",
            "\n",
            "postgresql-42.2.9.j 100%[===================>] 892.61K  5.04MB/s    in 0.2s    \n",
            "\n",
            "2021-09-07 19:56:35 (5.04 MB/s) - ‘postgresql-42.2.9.jar’ saved [914037/914037]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8e4SRZ_WKYUQ"
      },
      "source": [
        "# Start Spark session\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"AmazonReviews\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.9.jar\").getOrCreate()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2DKh2N3zKyZ4"
      },
      "source": [
        "# Import Amazon Video Games Data File From AWS\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_KijPoN5Kauo"
      },
      "source": [
        "# Import struct fields\n",
        "from pyspark.sql.types import StructField, StringType, IntegerType, StructType, TimestampType"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fijVNz0_K3cv",
        "outputId": "163f9a1e-5bc4-4d51-96fe-56f7baec97cc"
      },
      "source": [
        "# Read in data from S3 Buckets\n",
        "from pyspark import SparkFiles\n",
        "url =\"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Video_Games_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "data = spark.read.csv(SparkFiles.get(\"amazon_reviews_us_Video_Games_v1_00.tsv.gz\"), sep='\\t', header=True)\n",
        "\n",
        "# Clean the data\n",
        "data = data.withColumn('review_date', data['review_date'].cast(TimestampType()))\n",
        "data = data.withColumn('customer_id', data['customer_id'].cast(IntegerType()))\n",
        "data = data.withColumn('product_parent', data['product_parent'].cast(IntegerType()))\n",
        "data = data.withColumn('star_rating', data['star_rating'].cast(IntegerType()))\n",
        "data = data.withColumn('helpful_votes', data['helpful_votes'].cast(IntegerType()))\n",
        "data = data.withColumn('total_votes', data['total_votes'].cast(IntegerType()))\n",
        "df = data.dropna()\n",
        "\n",
        "# Show DataFrame\n",
        "df.show()"
      ],
      "execution_count": null,
      "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|   12039526| RTIS3L2M1F5SM|B001CXYMFS|     737716809|Thrustmaster T-Fl...|     Video Games|          5|            0|          0|   N|                Y|an amazing joysti...|Used this for Eli...|2015-08-31 00:00:00|\n",
            "|         US|    9636577| R1ZV7R40OLHKD|B00M920ND6|     569686175|Tonsee 6 buttons ...|     Video Games|          5|            0|          0|   N|                Y|Definitely a sile...|Loved it,  I didn...|2015-08-31 00:00:00|\n",
            "|         US|    2331478|R3BH071QLH8QMC|B0029CSOD2|      98937668|Hidden Mysteries:...|     Video Games|          1|            0|          1|   N|                Y|            One Star|poor quality work...|2015-08-31 00:00:00|\n",
            "|         US|   52495923|R127K9NTSXA2YH|B00GOOSV98|      23143350|GelTabz Performan...|     Video Games|          3|            0|          0|   N|                Y|good, but could b...|nice, but tend to...|2015-08-31 00:00:00|\n",
            "|         US|   14533949|R32ZWUXDJPW27Q|B00Y074JOM|     821342511|Zero Suit Samus a...|     Video Games|          4|            0|          0|   N|                Y|   Great but flawed.|Great amiibo, gre...|2015-08-31 00:00:00|\n",
            "|         US|    2377552|R3AQQ4YUKJWBA6|B002UBI6W6|     328764615|Psyclone Recharge...|     Video Games|          1|            0|          0|   N|                Y|            One Star|The remote consta...|2015-08-31 00:00:00|\n",
            "|         US|   17521011|R2F0POU5K6F73F|B008XHCLFO|      24234603|Protection for yo...|     Video Games|          5|            0|          0|   N|                Y|              A Must|I have a 2012-201...|2015-08-31 00:00:00|\n",
            "|         US|   19676307|R3VNR804HYSMR6|B00BRA9R6A|     682267517|   Nerf 3DS XL Armor|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Perfect, kids lov...|2015-08-31 00:00:00|\n",
            "|         US|     224068| R3GZTM72WA2QH|B009EPWJLA|     435241890|One Piece: Pirate...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|            Excelent|2015-08-31 00:00:00|\n",
            "|         US|   48467989| RNQOY62705W1K|B0000AV7GB|     256572651|Playstation 2 Dan...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Slippery but expe...|2015-08-31 00:00:00|\n",
            "|         US|     106569|R1VTIA3JTYBY02|B00008KTNN|     384411423|Metal Arms: Glitc...|     Video Games|          5|            0|          0|   N|                N|          Five Stars|Love the game. Se...|2015-08-31 00:00:00|\n",
            "|         US|   48269642|R29DOU8791QZL8|B000A3IA0Y|     472622859|72 Pin Connector ...|     Video Games|          1|            0|          0|   N|                Y| Game will get stuck|Does not fit prop...|2015-08-31 00:00:00|\n",
            "|         US|   52738710|R15DUT1VIJ9RJZ|B0053BQN34|     577628462|uDraw Gametablet ...|     Video Games|          2|            0|          0|   N|                Y|We have tried it ...|This was way too ...|2015-08-31 00:00:00|\n",
            "|         US|   10556786|R3IMF2MQ3OU9ZM|B002I0HIMI|     988218515|NBA 2K12(Covers M...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Works great good ...|2015-08-31 00:00:00|\n",
            "|         US|    2963837|R23H79DHOZTYAU|B0081EH12M|     770100932|New Trigger Grips...|     Video Games|          1|            1|          1|   N|                Y|Now i have to buy...|It did not fit th...|2015-08-31 00:00:00|\n",
            "|         US|   23092109| RIV24EQAIXA4O|B005FMLZQQ|      24647669|Xbox 360 Media Re...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|perfect lightweig...|2015-08-31 00:00:00|\n",
            "|         US|   23091728|R3UCNGYDVN24YB|B002BSA388|      33706205|Super Mario Galaxy 2|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|               great|2015-08-31 00:00:00|\n",
            "|         US|   10712640| RUL4H4XTTN2DY|B00BUSLSAC|     829667834|Nintendo 3DS XL -...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Works beautifully...|2015-08-31 00:00:00|\n",
            "|         US|   17455376|R20JF7Z4DHTNX5|B00KWF38AW|     110680188|Captain Toad:  Tr...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Kids loved the ga...|2015-08-31 00:00:00|\n",
            "|         US|   14754850|R2T1AJ5MFI2260|B00BRQJYA8|     616463426|Lego Batman 2: DC...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|           Goodngame|2015-08-31 00:00:00|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fYzLRFunLKIS"
      },
      "source": [
        "# Create Tables"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uz7OE78CLKao"
      },
      "source": [
        "## Customers Table"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tqsbfFXeLHxw"
      },
      "source": [
        "# Customer ID with customer view count\n",
        "customers = df.groupBy(\"customer_id\").count()\n",
        "customers = customers.withColumnRenamed('count','customer_count')\n",
        "customers = customers.withColumn('customer_count', customers['customer_count'].cast(IntegerType()))\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-ZnqwdT1LRxB"
      },
      "source": [
        "# Products Table"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KU2ACoElLQqP"
      },
      "source": [
        "# Product ID and product title\n",
        "products = df.groupby(\"product_id\", \"product_title\").count().select(\"product_id\", \"product_title\")\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e5bl1iDpLVyw"
      },
      "source": [
        "## Reviews Table"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Tg0ChohTLU54"
      },
      "source": [
        "# The review number, who reviewed it, what he/she reviewed, the parent of the reviewed product, and the date of the review\n",
        "reviews = df.select(\"review_id\", \"customer_id\", \"product_id\", \"product_parent\", \"review_date\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rKN-dt-eLbkl"
      },
      "source": [
        "## Vines Table"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D-so0-C5LaGI"
      },
      "source": [
        "# The review number, rating, helpful votes, total votes, and vine \n",
        "vines = df.select(\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SuhJWQUULfvs"
      },
      "source": [
        "# Connect To RDS"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1YB2ptAaLexY"
      },
      "source": [
        "# Configure settings for RDS\n",
        "mode = \"overwrite\"\n",
        "jdbc_url=\"jdbc:postgresql://amazonreviews.c5bki1uyc90h.us-east-2.rds.amazonaws.com:5432/AmazonReviews\"\n",
        "config = {\"user\":\"postgres\", \n",
        "          \"password\": \"password\", #password has been removed\n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "M66G-jd6LkU3"
      },
      "source": [
        "# Write tables to postgres in RDS\n",
        "customers.write.jdbc(url=jdbc_url, table = \"customers\", mode=mode, properties=config)\n",
        "products.write.jdbc(url=jdbc_url, table = \"products\", mode=mode, properties=config)\n",
        "reviews.write.jdbc(url=jdbc_url, table = \"review_id_table\", mode=mode, properties=config)\n",
        "vines.write.jdbc(url=jdbc_url, table = \"vine_table\", mode=mode, properties=config)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HPzaNzTjno3h"
      },
      "source": [
        "# Data Analysis\n",
        "#### In this section, I will analyze the Amazon video game data to determine if vine reviewers provide more helpful reviews"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0RuBkLXVozSO",
        "outputId": "bba4e380-7524-489c-ae52-43b00ca1435a"
      },
      "source": [
        "# Filter reviews to only include reviews that had greater than 50% of helpful votes\n",
        "# Filter reviews with more than 20 total votes\n",
        "filtered_df = vines.filter((vines.helpful_votes/vines.total_votes >= .5) & (vines.total_votes >= 20))\n",
        "filtered_df.show()"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|\n",
            "+--------------+-----------+-------------+-----------+----+\n",
            "| R4PKAZRQJJX14|          1|           21|         34|   N|\n",
            "|R2CI0Y288CC7E2|          1|           21|         35|   N|\n",
            "|R127WEQY2FM1T3|          1|          147|        175|   N|\n",
            "|R2FJ94555FZH32|          2|           55|         60|   N|\n",
            "|R1U3AR67RE273L|          1|           51|         65|   N|\n",
            "|R3PZOXA5X1U8KW|          4|           31|         36|   N|\n",
            "| R6KTC1OPIOIIG|          2|           19|         34|   N|\n",
            "|R36O341WWXXKNP|          5|           28|         31|   N|\n",
            "|R10LZVBLQHBVJ0|          2|          151|        198|   N|\n",
            "|R1VR5GLGY1GE7N|          1|           49|         51|   N|\n",
            "|R2OWDAR0EDEPTF|          1|           23|         28|   N|\n",
            "|R29BOS5HMAY1LO|          5|           88|        110|   N|\n",
            "| R8WD0136CETA7|          2|           99|        119|   N|\n",
            "|R16621F16PQN5A|          3|           24|         31|   N|\n",
            "|R1LXBPG1M9DDOC|          1|          102|        106|   N|\n",
            "| RNEMGSUKJ4535|          2|           21|         25|   N|\n",
            "|R3R2UKBFKP1IQ4|          1|           19|         23|   N|\n",
            "|R2T90XM5G2DBZA|          3|           15|         22|   N|\n",
            "| R5ZMK0Y0KRBRZ|          1|           79|         91|   N|\n",
            "|R12E2W0XOV3I2S|          1|           24|         30|   N|\n",
            "+--------------+-----------+-------------+-----------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-i_CCENjp7eg",
        "outputId": "99a0bb40-098e-42e5-e7f8-953be80348ab"
      },
      "source": [
        "# Calculate number of vine reviews\n",
        "vine_reviews = filtered_df.filter(filtered_df.vine == \"Y\")\n",
        "vine_count = vine_reviews.count()\n",
        "print(f'There were {vine_count} vine reviews')\n",
        "vine_reviews.describe().show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "There were 94 vine reviews\n",
            "+-------+--------------+------------------+-----------------+------------------+----+\n",
            "|summary|     review_id|       star_rating|    helpful_votes|       total_votes|vine|\n",
            "+-------+--------------+------------------+-----------------+------------------+----+\n",
            "|  count|            94|                94|               94|                94|  94|\n",
            "|   mean|          null| 4.202127659574468|54.59574468085106|61.787234042553195|null|\n",
            "| stddev|          null|0.9791348741656414|65.26098459822538| 68.90976994895392|null|\n",
            "|    min|R10FO5UKKVZBK2|                 1|               14|                20|   Y|\n",
            "|    max| RYBLWBAL5M1AY|                 5|              347|               362|   Y|\n",
            "+-------+--------------+------------------+-----------------+------------------+----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oGmiHKgZtGNj",
        "outputId": "e71e967c-75e7-40d7-f7f9-7908d062fa96"
      },
      "source": [
        "# Calculate number of non-vine reviews\n",
        "nonvine_reviews = filtered_df.filter(filtered_df.vine == \"N\")\n",
        "nonvine_count = nonvine_reviews.count()\n",
        "print(f'There were {nonvine_count} non-vine reviews')\n",
        "nonvine_reviews.describe().show()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "There were 40471 non-vine reviews\n",
            "+-------+--------------+------------------+------------------+------------------+-----+\n",
            "|summary|     review_id|       star_rating|     helpful_votes|       total_votes| vine|\n",
            "+-------+--------------+------------------+------------------+------------------+-----+\n",
            "|  count|         40471|             40471|             40471|             40471|40471|\n",
            "|   mean|          null|  3.34765634651973|47.428405524943784|55.891057794470115| null|\n",
            "| stddev|          null|1.6418850112078023|117.53763370687005|127.40280622961905| null|\n",
            "|    min|R1005PFAPGMYQS|                 1|                10|                20|    N|\n",
            "|    max| RZZTUIX1574RC|                 5|             10498|             10780|    N|\n",
            "+-------+--------------+------------------+------------------+------------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EpgN96Jo-daV"
      },
      "source": [
        "# Determine the percentage of five-star review among vine and non-vine reviews"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QYACYVrKBsOo",
        "outputId": "a759e988-c9c9-405b-ba74-4766fb4ec07e"
      },
      "source": [
        "vine_five_star_reviews = vine_reviews.filter(vine_reviews.star_rating == 5).count()\n",
        "percentage_five_star_vine = round(vine_five_star_reviews/vine_count,4)\n",
        "\n",
        "print(f'There were {vine_five_star_reviews} five star vine reviews')\n",
        "print(f'The percentage of vine reviews that were five stars was {percentage_five_star_vine*100}%')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "There were 48 five star vine review\n",
            "The percentage of vine reviews that were five stars was 51.06%\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "y2uTpLOaB9np",
        "outputId": "7cfd3b3f-0ce1-4774-d304-bc4a427edd48"
      },
      "source": [
        "nonvine_five_star_reviews = nonvine_reviews.filter(nonvine_reviews.star_rating == 5).count()\n",
        "percentage_five_star_nonvine = round(nonvine_five_star_reviews/nonvine_count,4)\n",
        "\n",
        "print(f'There were {nonvine_five_star_reviews} five star non-vine reviews')\n",
        "print(f'The percentage of non-vine reviews that were five stars was {percentage_five_star_nonvine*100}%')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "There were 15663 five star non-vine review\n",
            "The percentage of non-vine reviews that were five stars was 38.7%\n"
          ]
        }
      ]
    }
  ]
}