spark_aws_amazon_review_analysis.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cOCs8Im2tm4D",
        "outputId": "228d543d-b7a2-4a6a-90c4-ff96eca1c2cf"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Get: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",
            "Get:3 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Hit:4 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Hit:5 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:6 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:7 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:8 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease [15.9 kB]\n",
            "Ign:9 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:10 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Hit:11 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:12 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Get:13 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,992 kB]\n",
            "Get:14 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,799 kB]\n",
            "Get:15 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [1,021 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,512 kB]\n",
            "Get:17 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,286 kB]\n",
            "Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,231 kB]\n",
            "Get:19 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic/main amd64 Packages [45.3 kB]\n",
            "Fetched 13.2 MB in 7s (1,948 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ],
      "source": [
        "import os\n",
        "spark_version = 'spark-3.2.1'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# 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",
        "# 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",
        "# SparkSession\n",
        "import findspark\n",
        "findspark.init()\n",
        "\n",
        "from pyspark.sql import SparkSession\n",
        "from pyspark import SparkFiles\n",
        "from pyspark.sql.functions import to_date\n",
        "\n",
        "# JDBC\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "d44KtVMrtsN2",
        "outputId": "dca50b6e-131c-4309-cc20-6a36816e42e9"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|         US|   41409413|R2MTG1GCZLR2DK|B00428R89M|     112201306|yoomall 5M Antenn...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|       As described.| 2015-08-31|\n",
            "|         US|   49668221|R2HBOEM8LE9928|B000068O48|     734576678|Hosa GPM-103 3.5m...|     Electronics|          5|            0|          0|   N|                Y|It works as adver...|It works as adver...| 2015-08-31|\n",
            "|         US|   12338275|R1P4RW1R9FDPEE|B000GGKOG8|     614448099|Channel Master Ti...|     Electronics|          5|            1|          1|   N|                Y|          Five Stars|         Works pissa| 2015-08-31|\n",
            "|         US|   38487968|R1EBPM82ENI67M|B000NU4OTA|      72265257|LIMTECH Wall char...|     Electronics|          1|            0|          0|   N|                Y|            One Star|Did not work at all.| 2015-08-31|\n",
            "|         US|   23732619|R372S58V6D11AT|B00JOQIO6S|     308169188|Skullcandy Air Ra...|     Electronics|          5|            1|          1|   N|                Y|Overall pleased w...|Works well. Bass ...| 2015-08-31|\n",
            "|         US|   21257820|R1A4514XOYI1PD|B008NCD2LG|     976385982|Pioneer SP-BS22-L...|     Electronics|          5|            1|          1|   N|                Y|          Five Stars|The quality on th...| 2015-08-31|\n",
            "|         US|    3084991|R20D9EHB7N20V6|B00007FGUF|     670878953|C2G/Cables to Go ...|     Electronics|          5|            0|          0|   N|                Y|           Lifesaver|Wish I could give...| 2015-08-31|\n",
            "|         US|    8153674|R1WUTD8MVSROJU|B00M9V2RMM|     508452933|COOLEAD-HDMI Swit...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|         works great| 2015-08-31|\n",
            "|         US|   52246189|R1QCYLT25812DM|B00J3O9DYI|     766372886|Philips Wireless ...|     Electronics|          4|            0|          0|   N|                Y|          Four Stars|Great sound and c...| 2015-08-31|\n",
            "|         US|   41463864| R904DQPBCEM7A|B00NS1A0E4|     458130381|PlayStation 3 3D ...|     Electronics|          4|            0|          0|   N|                Y|          Four Stars|    It works well~~~| 2015-08-31|\n",
            "|         US|    2781942|R1DGA6UQIVLKZ7|B007B5V092|     152891509|JVC HAFR201A Xtre...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|           Alll good| 2015-08-31|\n",
            "|         US|     707292| RLQT3V8SMNIBH|B00IODHGVG|     717335277|Sylvania Alarm Cl...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|Love clock radio ...| 2015-08-31|\n",
            "|         US|   31463514|R3T9GZS2TMXZGM|B0035PBHX6|     249533961|Coby 8 GB 1.8-Inc...|     Electronics|          1|            0|          0|   N|                Y|            One Star|Breaks very easil...| 2015-08-31|\n",
            "|         US|   33475055|R24HVAEYP5PLDN|B00K1JJWFO|     635791633|Diamond (Original...|     Electronics|          5|            0|          1|   N|                Y|          Five Stars|Excellent gain in...| 2015-08-31|\n",
            "|         US|   16543871|R32KMAPNV5NJPJ|B00S3LJ5EA|     659360184|Kingvom 8gb 50 Ho...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|everything I expe...| 2015-08-31|\n",
            "|         US|   38472651| RC7VLPHUT6UAF|B00B5QNGN6|     564421014|JBL Ultra-Portabl...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|Love this small s...| 2015-08-31|\n",
            "|         US|   26946211|R3G1II8P4KGUAR|B00PLE8QF6|     685446417|YIPBOWPT Surface ...|     Electronics|          5|            0|          0|   N|                Y|       great product|works as advertis...| 2015-08-31|\n",
            "|         US|   10195727|R1UBFCBUALL6S5|B00GHUSIU6|     595255086|StarTech.com Mini...|     Electronics|          5|            0|          0|   N|                Y|          Five Stars|very good especia...| 2015-08-31|\n",
            "|         US|   47386264|R1WI5NISM6GAUG|B0045EJY90|     892920832|TEAC CD-P650-B Co...|     Electronics|          2|            4|          5|   N|                Y|It does not copy ...|It does not copy ...| 2015-08-31|\n",
            "|         US|   13000908|R27F4OF4BIA4LU|B003BT6BM8|     631236454|Philips SHS8100/2...|     Electronics|          2|            1|          1|   N|                Y|Did not last long...|Did not last long...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Spark\n",
        "spark = SparkSession.builder.appName(\"AWS-Amazon-Reviews\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()\n",
        "\n",
        "# Get files\n",
        "url = \"https://s3://amazongamereview/amazon_reviews_us_Digital_Video_Games_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.csv(SparkFiles.get(\"amazon_reviews_us_Digital_Video_Games_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fm3G0GPRt6Iq",
        "outputId": "a36fb5a7-b55d-449c-85f7-ec433b132591"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-----------+-------------+-----------+----+-----------------+\n",
            "|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+-----------+-------------+-----------+----+-----------------+\n",
            "|          5|            0|          0|   N|                Y|\n",
            "|          5|            0|          0|   N|                Y|\n",
            "|          5|            1|          1|   N|                Y|\n",
            "|          1|            0|          0|   N|                Y|\n",
            "|          5|            1|          1|   N|                Y|\n",
            "|          5|            1|          1|   N|                Y|\n",
            "|          5|            0|          0|   N|                Y|\n",
            "|          5|            0|          0|   N|                Y|\n",
            "|          4|            0|          0|   N|                Y|\n",
            "|          4|            0|          0|   N|                Y|\n",
            "+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Setting up the dataframe\n",
        "vine_df = df.select([\"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\", \"verified_purchase\"])\n",
        "vine_df.show(10)\n",
        "\n",
        "# Finding totals and helpful totals\n",
        "vine_total_votes = vine_df.filter(vine_df[\"total_votes\"] >= 20)\n",
        "vine_helpful = vine_total_votes.filter(vine_total_votes[\"helpful_votes\"]/vine_total_votes[\"total_votes\"] >= 0.5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9hfNpI30uDtu",
        "outputId": "c2ffcf33-02b3-4d14-98ac-8788650eae94"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-------+------------------+------------------+-----------------+----+-----------------+\n",
            "|summary|       star_rating|     helpful_votes|      total_votes|vine|verified_purchase|\n",
            "+-------+------------------+------------------+-----------------+----+-----------------+\n",
            "|  count|              1080|              1080|             1080|1080|             1080|\n",
            "|   mean| 4.093518518518518| 69.19259259259259|77.24259259259259|null|             null|\n",
            "| stddev|0.9834377437155114|140.20986501388205|148.5781746930746|null|             null|\n",
            "|    min|                 1|                11|               20|   Y|                N|\n",
            "|    max|                 5|              2561|             2688|   Y|                Y|\n",
            "+-------+------------------+------------------+-----------------+----+-----------------+\n",
            "\n",
            "+-------+------------------+------------------+------------------+-----+-----------------+\n",
            "|summary|       star_rating|     helpful_votes|       total_votes| vine|verified_purchase|\n",
            "+-------+------------------+------------------+------------------+-----+-----------------+\n",
            "|  count|             49673|             49673|             49673|49673|            49673|\n",
            "|   mean|3.6498500191250782| 60.68725464538079| 66.70503090209974| null|             null|\n",
            "| stddev|1.5646972640859573|154.93894290580138|161.00053469383883| null|             null|\n",
            "|    min|                 1|                10|                20|    N|                N|\n",
            "|    max|                 5|             12786|             12944|    N|                Y|\n",
            "+-------+------------------+------------------+------------------+-----+-----------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Filter paid and unpaid\n",
        "paid_vine = vine_helpful.filter(vine_helpful['vine']== 'Y')\n",
        "unpaid_vine = vine_helpful.filter(vine_helpful['vine']== 'N')\n",
        "\n",
        "paid_vine.describe().show()\n",
        "unpaid_vine.describe().show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KBYhosXnub2P",
        "outputId": "43794fe4-a31d-42a0-d1f0-edbd5bc1c9b7"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "1080\n",
            "454\n",
            "0.4203703703703704\n"
          ]
        }
      ],
      "source": [
        "# Analyzing paid vine\n",
        "paid_five_star = paid_vine[paid_vine['star_rating']== 5].count()\n",
        "paid_count = paid_vine.count()\n",
        "five_star_vine = float(paid_five_star) / float(paid_count)\n",
        "print(paid_count)\n",
        "print(paid_five_star)\n",
        "print(five_star_vine)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "t44dZ1tgverw",
        "outputId": "1facb267-d950-4df7-aa60-341b54178012"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "49673\n",
            "23043\n",
            "0.463893865882874\n"
          ]
        }
      ],
      "source": [
        "# Analyzing unpaid vine\n",
        "unpaid_five_star = unpaid_vine[unpaid_vine['star_rating']== 5].count()\n",
        "unpaid_count = unpaid_vine.count()\n",
        "five_star_non_vine = float(unpaid_five_star) / float(unpaid_count)\n",
        "print(unpaid_count)\n",
        "print(unpaid_five_star)\n",
        "print(float(unpaid_five_star) / float(unpaid_count))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "WPjmd59FzeT5"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "name": "spark_aws_amazon_reviews.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3.8.13 ('base')",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.8.13"
    },
    "vscode": {
      "interpreter": {
        "hash": "65a8de41a5ee1eeefd8cde7b1d120363c7d2ff8e30a40d43c0887b207b60c148"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}