Big_Data_Digital_Books12_7.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Big_Data_Digital_Books12.7.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyPbvL0R6FDwvIVw4SwUjoek",
      "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/abednarz210/Big_Data_Challenge/blob/main/Big_Data_Digital_Books12_7.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_gNvq7nAeSJo",
        "outputId": "28d99615-3fee-4d7b-cce4-6dec2e09b8d0"
      },
      "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",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Waiting for headers] [1 \r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Waiting for headers] [Wa\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.142)\r                                                                               \rIgn:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:3 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Get:4 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Ign:5 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:6 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release\n",
            "Hit:7 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:8 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:9 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:10 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Hit:11 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:12 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Hit:13 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,819 kB]\n",
            "Get:17 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [691 kB]\n",
            "Get:18 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,446 kB]\n",
            "Get:19 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,461 kB]\n",
            "Get:20 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [933 kB]\n",
            "Get:21 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [2,898 kB]\n",
            "Get:22 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,228 kB]\n",
            "Fetched 12.7 MB in 5s (2,723 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.2.0'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-8-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-8-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()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.9.jar"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yUI2pw63TN20",
        "outputId": "ed689972-5cc9-4965-b031-6878af25384f"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2021-12-08 04:28:38--  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  --.-KB/s    in 0.1s    \n",
            "\n",
            "2021-12-08 04:28:39 (6.06 MB/s) - ‘postgresql-42.2.9.jar’ saved [914037/914037]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        " from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"big_data_hw\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.9.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "h6AgM33lexBS"
      },
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Read in data from S3 Buckets\n",
        "from pyspark import SparkFiles\n",
        "url=\"https://db-6c14c855de480dadeb3a60a9f2e52024-s3-root-bucket.s3.us-west-2.amazonaws.com/amazon_reviews_us_Digital_Ebook_Purchase_v1_01.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "book_df = spark.read.csv(SparkFiles.get(\"amazon_reviews_us_Digital_Ebook_Purchase_v1_01.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "\n",
        "# Show DataFrame\n",
        "book_df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sSMppaRGTWV-",
        "outputId": "aca97cfd-5ee0-46a5-f526-e0f2583ba329"
      },
      "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|   33605939| RGYFDX8QXKEIR|B007KO2MLO|     328837464|           Big Maria|Digital_Ebook_Pur...|          4|            0|          0|   N|                N|              Quirky|Elmore Leonard me...| 2013-09-09|\n",
            "|         US|   34058393|R13CBGTMNV9R8Z|B005FLODDE|     764276359|The Woman Who Was...|Digital_Ebook_Pur...|          4|            1|          2|   N|                Y|The Woman Who Was...|This book was ver...| 2013-09-09|\n",
            "|         US|   39601147| R7DRFHC0F71O0|B00EA3L35O|     535606445|Mary had A Sleepy...|Digital_Ebook_Pur...|          5|            0|          0|   N|                N|This Sleepy Sheep...|I had the opportu...| 2013-09-09|\n",
            "|         US|   17351407|R27LUKEXU3KBXQ|B00BL3JV50|     240053004|          Starstruck|Digital_Ebook_Pur...|          5|            1|          1|   N|                Y|Steamy and suspen...|What a great read...| 2013-09-09|\n",
            "|         US|   10463387|R1VXTPUYMNU687|B00CXU7U80|     931529805|The Complete Cona...|Digital_Ebook_Pur...|          5|            1|          2|   N|                N|          Barbarians|Barbarians need l...| 2013-09-09|\n",
            "|         US|   50484904|R30DKW1GJWLPZC|B004EWGS5G|     442453110|The Middle Passag...|Digital_Ebook_Pur...|          3|            1|          2|   N|                Y|            Menu....|It was okay obvio...| 2013-09-09|\n",
            "|         US|    7145636|R18DPFG2FALJI9|B00BNRJAT6|     856774152|Hide in Plain Sig...|Digital_Ebook_Pur...|          5|            0|          0|   N|                Y|        Awesome book|Very interesting ...| 2013-09-09|\n",
            "|         US|    6285538|R24D677N5WBW5Q|B007FZOXJM|       5589837|Face of Betrayal ...|Digital_Ebook_Pur...|          5|            0|          0|   N|                Y|    Face of betrayal|Really enjoyed th...| 2013-09-09|\n",
            "|         US|   10278048|R2FCJ9BQLSIOR3|B00B6AK7LU|     362701357|Final Justice (A ...|Digital_Ebook_Pur...|          5|            0|          0|   N|                Y|           Very good|I really liked th...| 2013-09-09|\n",
            "|         US|   16568972|R1R6K4MAKDWTXI|B00EVMMLU0|     342745087|Falling For My Hu...|Digital_Ebook_Pur...|          4|            0|          0|   N|                Y|Ouch! My neck is ...|Reading Callum an...| 2013-09-09|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+--------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#number of rows \n",
        "book_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H14N8LQoTWhA",
        "outputId": "073e6d81-8c6e-475c-c829-784d35b212ee"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5101693"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#drop any null values\n",
        "dropna_df = book_df.dropna()\n",
        "dropna_df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TLkJsxpCTWpA",
        "outputId": "d7eba4d9-ffea-4fa0-ae58-9b4a36d254fb"
      },
      "execution_count": 6,
      "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|   33605939| RGYFDX8QXKEIR|B007KO2MLO|     328837464|           Big Maria|Digital_Ebook_Pur...|          4|            0|          0|   N|                N|              Quirky|Elmore Leonard me...| 2013-09-09|\n",
            "|         US|   34058393|R13CBGTMNV9R8Z|B005FLODDE|     764276359|The Woman Who Was...|Digital_Ebook_Pur...|          4|            1|          2|   N|                Y|The Woman Who Was...|This book was ver...| 2013-09-09|\n",
            "|         US|   39601147| R7DRFHC0F71O0|B00EA3L35O|     535606445|Mary had A Sleepy...|Digital_Ebook_Pur...|          5|            0|          0|   N|                N|This Sleepy Sheep...|I had the opportu...| 2013-09-09|\n",
            "|         US|   17351407|R27LUKEXU3KBXQ|B00BL3JV50|     240053004|          Starstruck|Digital_Ebook_Pur...|          5|            1|          1|   N|                Y|Steamy and suspen...|What a great read...| 2013-09-09|\n",
            "|         US|   10463387|R1VXTPUYMNU687|B00CXU7U80|     931529805|The Complete Cona...|Digital_Ebook_Pur...|          5|            1|          2|   N|                N|          Barbarians|Barbarians need l...| 2013-09-09|\n",
            "|         US|   50484904|R30DKW1GJWLPZC|B004EWGS5G|     442453110|The Middle Passag...|Digital_Ebook_Pur...|          3|            1|          2|   N|                Y|            Menu....|It was okay obvio...| 2013-09-09|\n",
            "|         US|    7145636|R18DPFG2FALJI9|B00BNRJAT6|     856774152|Hide in Plain Sig...|Digital_Ebook_Pur...|          5|            0|          0|   N|                Y|        Awesome book|Very interesting ...| 2013-09-09|\n",
            "|         US|    6285538|R24D677N5WBW5Q|B007FZOXJM|       5589837|Face of Betrayal ...|Digital_Ebook_Pur...|          5|            0|          0|   N|                Y|    Face of betrayal|Really enjoyed th...| 2013-09-09|\n",
            "|         US|   10278048|R2FCJ9BQLSIOR3|B00B6AK7LU|     362701357|Final Justice (A ...|Digital_Ebook_Pur...|          5|            0|          0|   N|                Y|           Very good|I really liked th...| 2013-09-09|\n",
            "|         US|   16568972|R1R6K4MAKDWTXI|B00EVMMLU0|     342745087|Falling For My Hu...|Digital_Ebook_Pur...|          4|            0|          0|   N|                Y|Ouch! My neck is ...|Reading Callum an...| 2013-09-09|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+--------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "book_df.head(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AecvLPVmTWtw",
        "outputId": "430ec837-e159-47fc-b779-d5af7b1f6b88"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(marketplace='US', customer_id=33605939, review_id='RGYFDX8QXKEIR', product_id='B007KO2MLO', product_parent=328837464, product_title='Big Maria', product_category='Digital_Ebook_Purchase', star_rating=4, helpful_votes=0, total_votes=0, vine='N', verified_purchase='N', review_headline='Quirky', review_body='Elmore Leonard meets the cast of Sierra Madre. Just a quirky read that will make you want to keep trying no matter what happens.', review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=34058393, review_id='R13CBGTMNV9R8Z', product_id='B005FLODDE', product_parent=764276359, product_title=\"The Woman Who Wasn't There: The True Story of an Incredible Deception\", product_category='Digital_Ebook_Purchase', star_rating=4, helpful_votes=1, total_votes=2, vine='N', verified_purchase='Y', review_headline=\"The Woman Who Wasn't There\", review_body='This book was very interesting. It is a true story about a woman who perpetrated a lie about her survival of the attack on 9/11. It was so amazing to me that she could/would do such a thing. It had such a devastating effect on so many.', review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=39601147, review_id='R7DRFHC0F71O0', product_id='B00EA3L35O', product_parent=535606445, product_title='Mary had A Sleepy Sheep', product_category='Digital_Ebook_Purchase', star_rating=5, helpful_votes=0, total_votes=0, vine='N', verified_purchase='N', review_headline='This Sleepy Sheep rocks!', review_body=\"I had the opportunity to review Mary had a Sleepy Sheep by Julia Dweck with my kindergarten class and they thought it was hysterical! We have read this story multiple times and it's just as funny every time. It's about a little girl and her sheep who is so sleepy no matter what she tries to do to wake him up. And she tries some pretty crazy things! You'll have to purchase it and see for yourself how she finally keeps her poor sheep awake :)\", review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=17351407, review_id='R27LUKEXU3KBXQ', product_id='B00BL3JV50', product_parent=240053004, product_title='Starstruck', product_category='Digital_Ebook_Purchase', star_rating=5, helpful_votes=1, total_votes=1, vine='N', verified_purchase='Y', review_headline='Steamy and suspenseful!!!!!', review_body=\"What a great read!  I really couldn't put this book down!  A suspenseful twist in this steamy love story will keep you reading!!  Where can I find a Jesse?!?!  Just loved his relationship with Sam. You've gotta read this story!  It won't disappoint!\", review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=10463387, review_id='R1VXTPUYMNU687', product_id='B00CXU7U80', product_parent=931529805, product_title='The Complete Conan Saga', product_category='Digital_Ebook_Purchase', star_rating=5, helpful_votes=1, total_votes=2, vine='N', verified_purchase='N', review_headline='Barbarians', review_body='Barbarians need love too !  Short stories work well with ebooks. The texts can be printed and read later.  They can be read on a laptop.  Bravo !', review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=50484904, review_id='R30DKW1GJWLPZC', product_id='B004EWGS5G', product_parent=442453110, product_title='The Middle Passage (A Cat Royal Adventure)', product_category='Digital_Ebook_Purchase', star_rating=3, helpful_votes=1, total_votes=2, vine='N', verified_purchase='Y', review_headline='Menu....', review_body=\"It was okay obviously, but if you hadn't read the other beginning ' books,' well it would be confusing. Plus, there's the fact that it did not tell me that it was a script for a play, though the plot is sort of good once you get kind of the idea of what happened.\", review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=7145636, review_id='R18DPFG2FALJI9', product_id='B00BNRJAT6', product_parent=856774152, product_title='Hide in Plain Sight (The Three Sisters Inn Book 1)', product_category='Digital_Ebook_Purchase', star_rating=5, helpful_votes=0, total_votes=0, vine='N', verified_purchase='Y', review_headline='Awesome book', review_body=\"Very interesting and kept me reading. Read it in one day. Didn't get much of anything else done. Very good ..\", review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=6285538, review_id='R24D677N5WBW5Q', product_id='B007FZOXJM', product_parent=5589837, product_title='Face of Betrayal (A Triple Threat Novel)', product_category='Digital_Ebook_Purchase', star_rating=5, helpful_votes=0, total_votes=0, vine='N', verified_purchase='Y', review_headline='Face of betrayal', review_body='Really enjoyed this book and the author! Ready to read the next one!  Thanks for keeping me intrigued and unable to put my book down.', review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=10278048, review_id='R2FCJ9BQLSIOR3', product_id='B00B6AK7LU', product_parent=362701357, product_title='Final Justice (A Romantic Suspense)', product_category='Digital_Ebook_Purchase', star_rating=5, helpful_votes=0, total_votes=0, vine='N', verified_purchase='Y', review_headline='Very good', review_body='I really liked this book. I felt like I got to know the characters. It was a really good read.', review_date='2013-09-09'),\n",
              " Row(marketplace='US', customer_id=16568972, review_id='R1R6K4MAKDWTXI', product_id='B00EVMMLU0', product_parent=342745087, product_title='Falling For My Husband (British Billionaires)', product_category='Digital_Ebook_Purchase', star_rating=4, helpful_votes=0, total_votes=0, vine='N', verified_purchase='Y', review_headline='Ouch! My neck is sore!!!', review_body='Reading Callum and Stella\\'s story was a bit like watching a tennis match. They bounced like a tennis ball back and forth from his side of the court to her side of the court. They were together then not together, together and then not, over and over again. Like a tennis match, it was entertaining at times, but was nerve racking and tiring to watch. Yet, I couldn\\'t stop reading. Although it was painful to witness, I did like the dilemma Callum found himself in when the woman who had shattered his heart suddenly came back to him and  wanted him back.  He was caught between that proverbial rock  and  a hard place and was forced to make an impossibly tough choice. But, just like in a tennis match, in order for Callum to win in Love, he had to put himself out there, face his opponent head on with everything he had, and fight to win. I get why Stella\\'s brother did what he did. He gave his \\\\\\\\\"brother\\\\\\\\\" and sister the ultimate gift.....LOVE and family!', review_date='2013-09-09')]"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#credit https://sparkbyexamples.com/pyspark/pyspark-distinct-to-drop-duplicates/\n",
        "\n",
        "distinctDF = book_df.distinct()\n",
        "print(\"Distinct count: \"+str(distinctDF.count()))\n",
        "distinctDF.show(truncate=False)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YD2DTvCzTWzQ",
        "outputId": "03aa03f7-d0b9-459e-8f45-18776074e82c"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Distinct count: 5101693\n",
            "+-----------+-----------+--------------+----------+--------------+---------------------------------------------------------------------------------------------+----------------------+-----------+-------------+-----------+----+-----------------+----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+\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         |19528321   |R100242LN81O1I|B003H06BRG|890128726     |Jigsaw                                                                                       |Digital_Ebook_Purchase|5          |1            |1          |N   |Y                |Mystery                                             |Well written and real,a compulsively readable story.....Your book club will talk and talk...an mark this one a winning novel. You will be be captivated by the characters and the mystery as it unfolds. The story is riveting and has all the anticipation you want throughout .  The perfect book that is being made into a movie One of those books that sucks you in and you can't put it down.  You will see this writer in the future.This wonderful story is a stunning debut for Mr. Brogden.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |2011-01-16 |\n",
            "|US         |16197253   |R1005WDNA9MEEQ|B000FC24S4|214005239     |The Marrow of Tradition [with Biographical Introduction]                                     |Digital_Ebook_Purchase|2          |1            |2          |N   |N                |It's A Pretty Boring Book                           |This book was required, and it's very boring. It starts slow, and the ending is completely unrealistic. This is something to read only if you have to.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |2010-03-01 |\n",
            "|US         |17370927   |R100ACJRDE3COO|B007JBUSKU|847963746     |Death's Door (Billy Boyle World War II Mystery Book 7)                                       |Digital_Ebook_Purchase|4          |0            |0          |N   |Y                |Death's Door (Billy Boyle World War II Mystery)     |A good read with history as to the how the Vatican worked during the World War II German occupation of Italy.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |2012-12-25 |\n",
            "|US         |31874025   |R100Q807IMB8DK|B002HMJZ8M|920255819     |Gears of War: Jacinto's Remnant                                                              |Digital_Ebook_Purchase|5          |0            |1          |N   |Y                |Great!                                              |I just noticed there is a 3* rating on this book. Are you insane woman??? This book presents Col. Hoffman in a new light as well as other key characters such as Cpl. Baird, Sgt. Fenix and Mataki, and especially Lt. Stroud. Awesome read. Love it totally and cannot wait for the fourth presumable after the release of the third game. Was originally unable to percieve this as a good book with the abscence of the Locust horde but the random Lambent element was masterfully put together. Bravo Mrs. Traviss.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |2010-12-08 |\n",
            "|US         |47827305   |R100QMGKKMQDAI|B00AQ3K8IU|907407538     |Hopeless                                                                                     |Digital_Ebook_Purchase|5          |0            |0          |N   |Y                |Loved it                                            |Such a good story, and I couldn't put it down!! It was heart wrenching what happened but cool to see how everyone was connected. Fate brought Dean and sky back togethet                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |2013-01-23 |\n",
            "|US         |24569945   |R100UEENUKYVBE|B007J4T2G8|600633062     |Fifty Shades of Grey (Fifty Shades, Book 1)                                                  |Digital_Ebook_Purchase|5          |0            |2          |N   |Y                |Book one                                            |I had to see what everyone was talking about so bought it out of curiousity. I could not stop reading it. Awesome. Now I need to buy the next one.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |2013-08-06 |\n",
            "|US         |36251024   |R101PQMD4W12YZ|B007JCTYE0|356596139     |Dana The Dancing Dinosaur - A Funny Rhyming Children's Picture Book (A Funny Rhyming Story 3)|Digital_Ebook_Purchase|5          |0            |0          |N   |Y                |Dana The Dancing Dinosaur                           |All the children I am related to or know, adore this book.  You should see them smile, laugh, dance ... and the ones who can read love reading this book to an adult.  I have got to admit, even I love the book and I'm over 60 !! hahaha                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |2012-05-02 |\n",
            "|US         |13989696   |R102BQEGS1J3S2|B007MB5IRA|819537366     |One Shot at Forever: A Small Town, an Unlikely Coach, and a Magical Baseball Season          |Digital_Ebook_Purchase|5          |0            |0          |N   |Y                |one shot at forever                                 |I loved this story about an amazing group of kids with alot of heart.  It was well written and fun to read.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |2012-10-08 |\n",
            "|US         |20128975   |R102ENNYYV747C|B003NX7OBY|562207142     |Shoot to Thrill: A Monkeewrench Novel (Monkeewrench Mysteries Book 5)                        |Digital_Ebook_Purchase|4          |0            |0          |N   |Y                |Monkeywrench series                                 |The first book was good, but alot of it was starting to learn and know all the characters.  After I knew each character the remainder of that book and all 5 following them were soooo good!  I just looked for time throughout the day to read some.  I really hated to see the series come to an end....hopefully there will be another one.  Would highly recommend this series.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |2013-01-05 |\n",
            "|US         |10156490   |R102FQZJONSQMO|B004FGMDOQ|333044434     |Miss Peregrine's Home for Peculiar Children (Miss Peregrine's Peculiar Children Book 1)      |Digital_Ebook_Purchase|5          |0            |0          |N   |Y                |can't wait to read the next one!                    |Good read..I wonder what the next book will hold. Such a page turner, always wanting to know what's next. :)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |2013-07-13 |\n",
            "|US         |33766825   |R102N83Q8BLGOE|B00BE6UPU4|424661103     |Wags To Riches                                                                               |Digital_Ebook_Purchase|3          |1            |1          |N   |Y                |It's ok                                             |I really think this book has potiental with a geat story line but that I have big issues with the fact the author makes all about money...and that just kinda loses me...it should be about love not what he can and does buy for her...Like I said it has potiential but it does need work.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |2013-03-28 |\n",
            "|US         |35449061   |R102RFJK3X650S|B001ODEPEE|881447504     |Psych: A Mind is a Terrible Thing to Read                                                    |Digital_Ebook_Purchase|5          |0            |0          |N   |Y                |Great!                                              |Such an entertaining read,I love Psych and have seen every single episode I was thrilled to find books based on the series, it's like a extra long episode with all the same wit and charm I plan on purchasing another book to read now.<br />A must read for any fan of the show!!!!!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |2013-05-01 |\n",
            "|US         |13121951   |R102YQ9DPPDMLL|B007EDOKZW|24032186      |Brain on Fire: My Month of Madness                                                           |Digital_Ebook_Purchase|4          |0            |0          |N   |Y                |Fascinating read......                              |And look into the way the brain works and processes illness. Caught myself rooting for her recovery and clarity. Inspiring story.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |2013-07-29 |\n",
            "|US         |50217259   |R1030MH3AC8GZU|B0071NPT4Q|132194583     |Most Talkative: Stories from the Front Lines of Pop Culture                                  |Digital_Ebook_Purchase|4          |0            |0          |N   |Y                |A Fun Read                                          |A must read for fans of AMC, WWHL, or RHW of anywhere. If you love Andy already, you will love him even more. Perfect summer read!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |2013-07-23 |\n",
            "|US         |40028342   |R1039A7RABJB6R|B000FC1IXQ|859372921     |Kick Me: Adventures in Adolescence                                                           |Digital_Ebook_Purchase|4          |0            |0          |N   |N                |Hilariously Harrowing Memoir                        |I was drawn to this book because the author, Paul Feig, was the creator of the excellent and lamentablt short-lived TV series, Freaks and Geeks. And while reading it, one can certainly see the genesis of the show in his hilariously awkward childhood. Since Mr. Feig and I must be about the same age, this was a trip down memory lane, both in terms of the childhood experiences and the pop cultural references.  This entertaining and neurotic memoir could probably be read in a single evening, but why rush it?  I found myself delayed by frequent laughing jags followed by moments of reverie as I daydreamed of incidents from my own long ago youth.  I found the stories from the junior high years to be absolutely hysterical while those from the high school years were a bit more ponderous. But overall, well done and great fun!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |2012-05-01 |\n",
            "|US         |12330998   |R103C5S05Y65A1|B00DLATS18|432915499     |What Wendy Wants                                                                             |Digital_Ebook_Purchase|4          |0            |0          |N   |Y                |What Wendy Wants - don't we all!?                   |This book was an eye opener for me.  It is a quick read but you definitely have to be open minded to read this.  I enjoyed it and thought it was a unique storyline.<br /><br />I could relate to the characters especially at the beginning of the book and right from the start I was hooked.  I loved how the story progressed and thought it was a fun read.  We all want someone in our lives who wants to provide us with what we want (whatever that may be!! ;-)).<br /><br />It is very much a fantasy romance book without being paranormal.  It takes us on a journey from normality to something a lot more exciting with all Wendy's desires being fulfilled.  This isn't the type book I would character cast because I didn't get a feel for what the characters looked like....to me they could be any average married couple.  I think that is one the reasons the book was so appealing to me.<br /><br />I am rating this book 4 stars because it had me hooked from the start and I didn't want to put it down.  I wasn't, however, invested nearly as much with these characters as I have been with other books I've read recently.<br /><br />I thought it was interesting, enjoyable and a fun read.  I would recommend this book to people who want some happy excitement in their reading without it being too heavy!<br /><br />You can see my full blog entry about this book at Bec's Book Blog.|2013-07-22 |\n",
            "|US         |47736620   |R103HU3W0JALJ9|B007AD3F5W|158661198     |Firefly                                                                                      |Digital_Ebook_Purchase|5          |3            |3          |N   |Y                |Great story from a first time author                |I purchased this book, because once upon a time, I knew the author.  I was skeptical at first, but within the first 3 chapters, I couldn't put it down.  I fell in love with the characters and was sucked into the story.  I did notice a few grammatical errors and what I would call \\\\\"typos\\\\\", but I could always figure out what was meant to be said.  I thought it was better than the Twilight series and am letting my 11-year-old daughter read it now.  Jennifer Kilgore wrote a great book and I look very forward to whatever she has coming next.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |2012-02-27 |\n",
            "|US         |33853208   |R103Q8RLWCXLSO|B004J8HR5K|46936823      |Husk                                                                                         |Digital_Ebook_Purchase|5          |1            |2          |N   |N                |Suspense followed by MORE suspense                  |Just to set the record straight, I got Husk free from the publisher because I posted a review of [[ASIN:B004H1T9H8 Terror Town]] by James Roy Daley (who is the publisher as well). Husk is horror along the lines of What the Night Knows: A Novel by Dean Koontz. I enjoyed this terror novel very much. Yes, the entity is supernatural, and \\\\\"it\\\\\" makes for one VERY weird sick puppy of a killer. Maybe it is not fair to compare Hults to Koontz or early King, but he writes along those lines with his own take on horror/terror. The second part of this thriller just seemed to fly by.  One suspenseful moment was followed by another and then another an so on.  I could not find a safe place to take a break. I was planning on buying Husk anyways, but it was a bonus to get it for free.  Speaking of free, I also downloaded [[ASIN:B004NIFOEM Anything Can Be Dangerous]] by Matt Hults, and will be looking for more titles by him (and his publisher as well).                                                                                                                                                                                                                                                                                                                                                                                                                                      |2011-05-26 |\n",
            "|US         |16819930   |R103TF5SXSGZ7R|B007JCTY40|40952012      |Snow Dance: A Dance 'n' Luv prequel                                                          |Digital_Ebook_Purchase|4          |2            |2          |N   |Y                |Sweet and fun to read                               |This story is very sweet and I had fun reading it. The characters are just different enough to make you want to see them together. I liked the dialogue and thought it was well written and this story was very readable. If you are looking for something short and enjoyable to read, this is a good place to start.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |2013-01-14 |\n",
            "|US         |43169792   |R103WVMVWRY12 |B006FOI1KG|877002723     |I Suck at Girls                                                                              |Digital_Ebook_Purchase|4          |0            |0          |N   |Y                |Sweet, witty...but not QUITE as funny as I had hoped|This was a very quick and easy read that I chose to for a few laughs. Very poignant and made me look back at my own life and its misadventures. I enjoyed it overall but was just expecting more funnies from 'Dad.'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |2012-12-26 |\n",
            "+-----------+-----------+--------------+----------+--------------+---------------------------------------------------------------------------------------------+----------------------+-----------+-------------+-----------+----+-----------------+----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#drop columns to match schema\n",
        "\n",
        "distinctDF.drop(\"marketplace\",\"review_id \",\"lastname\", \"product_parent\", \"verified_purchase\", \"review_headline\", \"review_body\") \\\n",
        "    .printSchema()\n",
        "\n",
        "cols = (\"marketplace\",\"review_id \",\"lastname\", \"product_parent\", \"verified_purchase\", \"review_headline\", \"review_body\")\n",
        "\n",
        "distinctDF.drop(*cols) \\\n",
        "   .printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1DIBA_p2TW8r",
        "outputId": "b6b1f038-5ea4-41c4-c00e-d2445f03ee6d"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- customer_id: integer (nullable = true)\n",
            " |-- review_id: string (nullable = true)\n",
            " |-- product_id: string (nullable = true)\n",
            " |-- product_title: string (nullable = true)\n",
            " |-- product_category: string (nullable = true)\n",
            " |-- star_rating: integer (nullable = true)\n",
            " |-- helpful_votes: integer (nullable = true)\n",
            " |-- total_votes: integer (nullable = true)\n",
            " |-- vine: string (nullable = true)\n",
            " |-- review_date: string (nullable = true)\n",
            "\n",
            "root\n",
            " |-- customer_id: integer (nullable = true)\n",
            " |-- review_id: string (nullable = true)\n",
            " |-- product_id: string (nullable = true)\n",
            " |-- product_title: string (nullable = true)\n",
            " |-- product_category: string (nullable = true)\n",
            " |-- star_rating: integer (nullable = true)\n",
            " |-- helpful_votes: integer (nullable = true)\n",
            " |-- total_votes: integer (nullable = true)\n",
            " |-- vine: string (nullable = true)\n",
            " |-- review_date: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    }
  ]
}