PySpark and SparkSQL Complete Guide.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PySpark and SparkSQL Complete Guides"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apache Spark is a cluster computing system that offers comprehensive libraries and APIs for developers, and\n",
    "SparkSQL can be represented as the module in Apache Spark for processing unstructured data with the help of DataFrame API."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, we will cover the basics how to run Spark Jobs with PySpark (Python API) and execute useful functions insdie. If followed, you should be able to grasp a basic understadning of PySparks and its common functions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we use the findspark library to locate spark on our local machine\n",
    "import findspark\n",
    "findspark.init('C:/Users/bokhy/spark/spark-2.4.6-bin-hadoop2.7')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date, timedelta, datetime\n",
    "import time\n",
    "\n",
    "import pyspark # only run this after findspark.init()\n",
    "from pyspark.sql import SparkSession, SQLContext\n",
    "from pyspark.context import SparkContext\n",
    "from pyspark.sql.functions import * \n",
    "from pyspark.sql.types import * "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Initialize the Spark Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to begin with initilize the Spark Session. DataFrame can be created and registered as tables. Moreover, SQL tables be executed, tables can be cached, and parquet/json/csv/avro data formatted files can be read."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc = SparkSession.builder.appName(\"PysparkExample\").config (\"spark.sql.shuffle.partitions\", \"50\").config(\"spark.driver.maxResultSize\",\"5g\").config (\"spark.sql.execution.arrow.enabled\", \"true\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://DESKTOP-L9PS4IG.home:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v2.4.6</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>PysparkExample</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x1e0ceb4bf08>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Load Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can download the Kaggle dataset, which includes the book title, author, the date of the best seller list, the published date of the list, the book description, the rank (this week and last week), the publisher, number of weeks on the list, and the price [Link](https://www.kaggle.com/cmenca/new-york-times-hardcover-fiction-best-sellers)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Spark is so Awesome that it supports all different types of data to be read.\n",
    "\n",
    "DataFrames can be created by reading txt, csv, json and parquet file formats. In our example, we will be using .json formatted file. You can also find and read text, csv and parquet file formats by using the related read functions as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#JSON\n",
    "dataframe = sc.read.json('./data/nyt2.json')\n",
    "\n",
    "#TXT files\n",
    "# dataframe_txt = sc.read.text('./data/text_data.txt')\n",
    "\n",
    "#CSV files\n",
    "# dataframe_csv = sc.read.csv('./data/csv_data.csv')\n",
    "\n",
    "#PARQUET files\n",
    "# dataframe_parquet = sc.read.load('./data/parquet_data.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Look at the data with show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+\n",
      "|                 _id|  amazon_product_url|              author| bestsellers_date|         description|   price|   published_date|    publisher|rank|rank_last_week|               title|weeks_on_list|\n",
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       Dean R Koontz|[[1211587200000]]|Odd Thomas, who c...|  [, 27]|[[1212883200000]]|       Bantam| [1]|           [0]|           ODD HOURS|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Stephenie Meyer|[[1211587200000]]|Aliens have taken...|[25.99,]|[[1212883200000]]|Little, Brown| [2]|           [1]|            THE HOST|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|        Emily Giffin|[[1211587200000]]|A woman's happy m...|[24.95,]|[[1212883200000]]| St. Martin's| [3]|           [2]|LOVE THE ONE YOU'...|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|   Patricia Cornwell|[[1211587200000]]|A Massachusetts s...|[22.95,]|[[1212883200000]]|       Putnam| [4]|           [0]|           THE FRONT|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Chuck Palahniuk|[[1211587200000]]|An aging porn que...|[24.95,]|[[1212883200000]]|    Doubleday| [5]|           [0]|               SNUFF|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|James Patterson a...|[[1211587200000]]|A woman finds an ...|[24.99,]|[[1212883200000]]|Little, Brown| [6]|           [3]|SUNDAYS AT TIFFANY’S|          [4]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       John Sandford|[[1211587200000]]|The Minneapolis d...|[26.95,]|[[1212883200000]]|       Putnam| [7]|           [4]|        PHANTOM PREY|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       Jimmy Buffett|[[1211587200000]]|A Southern family...|[21.99,]|[[1212883200000]]|Little, Brown| [8]|           [6]|          SWINE NOT?|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|    Elizabeth George|[[1211587200000]]|In Cornwall, tryi...|[27.95,]|[[1212883200000]]|       Harper| [9]|           [8]|     CARELESS IN RED|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|      David Baldacci|[[1211587200000]]|An intelligence a...|[26.99,]|[[1212883200000]]|Grand Central|[10]|           [7]|     THE WHOLE TRUTH|          [5]|\n",
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dataframe.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Simple Data Inspection "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We usually want to simply skim through the dataframe before going deeper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+--------------------+----+--------------+--------------------+-------------+\n",
      "|                 _id|  amazon_product_url|              author| bestsellers_date|         description|   price|   published_date|           publisher|rank|rank_last_week|               title|weeks_on_list|\n",
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+--------------------+----+--------------+--------------------+-------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       Dean R Koontz|[[1211587200000]]|Odd Thomas, who c...|  [, 27]|[[1212883200000]]|              Bantam| [1]|           [0]|           ODD HOURS|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Stephenie Meyer|[[1211587200000]]|Aliens have taken...|[25.99,]|[[1212883200000]]|       Little, Brown| [2]|           [1]|            THE HOST|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|        Emily Giffin|[[1211587200000]]|A woman's happy m...|[24.95,]|[[1212883200000]]|        St. Martin's| [3]|           [2]|LOVE THE ONE YOU'...|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|   Patricia Cornwell|[[1211587200000]]|A Massachusetts s...|[22.95,]|[[1212883200000]]|              Putnam| [4]|           [0]|           THE FRONT|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Chuck Palahniuk|[[1211587200000]]|An aging porn que...|[24.95,]|[[1212883200000]]|           Doubleday| [5]|           [0]|               SNUFF|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|James Patterson a...|[[1211587200000]]|A woman finds an ...|[24.99,]|[[1212883200000]]|       Little, Brown| [6]|           [3]|SUNDAYS AT TIFFANY’S|          [4]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       John Sandford|[[1211587200000]]|The Minneapolis d...|[26.95,]|[[1212883200000]]|              Putnam| [7]|           [4]|        PHANTOM PREY|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       Jimmy Buffett|[[1211587200000]]|A Southern family...|[21.99,]|[[1212883200000]]|       Little, Brown| [8]|           [6]|          SWINE NOT?|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|    Elizabeth George|[[1211587200000]]|In Cornwall, tryi...|[27.95,]|[[1212883200000]]|              Harper| [9]|           [8]|     CARELESS IN RED|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|      David Baldacci|[[1211587200000]]|An intelligence a...|[26.99,]|[[1212883200000]]|       Grand Central|[10]|           [7]|     THE WHOLE TRUTH|          [5]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|        Troy Denning|[[1211587200000]]|The New Jedi orde...|  [, 27]|[[1212883200000]]|  Del Rey/Ballantine|[11]|           [5]|          INVINCIBLE|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|          James Frey|[[1211587200000]]|A novel, set in L...|[26.95,]|[[1212883200000]]|              Harper|[12]|           [9]|BRIGHT SHINY MORNING|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|         Garth Stein|[[1211587200000]]|A Lab-terrier mix...|[23.95,]|[[1212883200000]]|              Harper|[13]|           [0]|THE ART OF RACING...|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Debbie Macomber|[[1211587200000]]|A widow who owns ...|[24.95,]|[[1212883200000]]|                Mira|[14]|          [10]|       TWENTY WISHES|          [4]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|         Jeff Shaara|[[1211587200000]]|A novel about the...|  [, 28]|[[1212883200000]]|          Ballantine|[15]|          [11]|      THE STEEL WAVE|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|    Phillip Margolin|[[1211587200000]]|                    |   [, 0]|[[1212883200000]]|HarperCollins Pub...|[16]|           [0]| EXECUTIVE PRIVILEGE|          [0]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       Jhumpa Lahiri|[[1211587200000]]|Stories of the an...|   [, 0]|[[1212883200000]]|               Knopf|[17]|           [0]|  UNACCUSTOMED EARTH|          [0]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|      Joseph O'Neill|[[1211587200000]]|A Dutchman desert...|   [, 0]|[[1212883200000]]|Knopf Publishing ...|[18]|           [0]|          NETHERLAND|          [0]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|        John Grisham|[[1211587200000]]|Political and leg...|   [, 0]|[[1212883200000]]|Doubleday Publishing|[19]|           [0]|          THE APPEAL|          [0]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       James Rollins|[[1211587200000]]|                    |   [, 0]|[[1212883200000]]|Random House Publ...|[20]|           [0]|INDIANA JONES AND...|          [0]|\n",
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+--------------------+----+--------------+--------------------+-------------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+-------+--------------------+---------------+--------------------+---------+------------------+\n",
      "|summary|  amazon_product_url|         author|         description|publisher|             title|\n",
      "+-------+--------------------+---------------+--------------------+---------+------------------+\n",
      "|  count|               10195|          10195|               10195|    10195|             10195|\n",
      "|   mean|                null|           null|                null|     null|1877.7142857142858|\n",
      "| stddev|                null|           null|                null|     null| 370.9760613506458|\n",
      "|    min|http://www.amazon...|        AJ Finn|                    |      ACE|  10TH ANNIVERSARY|\n",
      "|    max|https://www.amazo...|various authors|’Tis for the Rebe...|allantine|               ZOO|\n",
      "+-------+--------------------+---------------+--------------------+---------+------------------+\n",
      "\n",
      "== Parsed Logical Plan ==\n",
      "Relation[_id#6,amazon_product_url#7,author#8,bestsellers_date#9,description#10,price#11,published_date#12,publisher#13,rank#14,rank_last_week#15,title#16,weeks_on_list#17] json\n",
      "\n",
      "== Analyzed Logical Plan ==\n",
      "_id: struct<$oid:string>, amazon_product_url: string, author: string, bestsellers_date: struct<$date:struct<$numberLong:string>>, description: string, price: struct<$numberDouble:string,$numberInt:string>, published_date: struct<$date:struct<$numberLong:string>>, publisher: string, rank: struct<$numberInt:string>, rank_last_week: struct<$numberInt:string>, title: string, weeks_on_list: struct<$numberInt:string>\n",
      "Relation[_id#6,amazon_product_url#7,author#8,bestsellers_date#9,description#10,price#11,published_date#12,publisher#13,rank#14,rank_last_week#15,title#16,weeks_on_list#17] json\n",
      "\n",
      "== Optimized Logical Plan ==\n",
      "Relation[_id#6,amazon_product_url#7,author#8,bestsellers_date#9,description#10,price#11,published_date#12,publisher#13,rank#14,rank_last_week#15,title#16,weeks_on_list#17] json\n",
      "\n",
      "== Physical Plan ==\n",
      "*(1) FileScan json [_id#6,amazon_product_url#7,author#8,bestsellers_date#9,description#10,price#11,published_date#12,publisher#13,rank#14,rank_last_week#15,title#16,weeks_on_list#17] Batched: false, Format: JSON, Location: InMemoryFileIndex[file:/C:/Users/bokhy/Desktop/Python/github/PySpark/data/nyt2.json], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<_id:struct<$oid:string>,amazon_product_url:string,author:string,bestsellers_date:struct<$d...\n"
     ]
    }
   ],
   "source": [
    "# Returns dataframe column names and data types\n",
    "dataframe.dtypes\n",
    "\n",
    "# Displays the content of dataframe\n",
    "dataframe.show()\n",
    "\n",
    "# Return first n rows\n",
    "dataframe.head()\n",
    "\n",
    "# Returns first row\n",
    "dataframe.first()\n",
    "\n",
    "# Return first n rows\n",
    "dataframe.take(5)\n",
    "\n",
    "# Computes summary statistics\n",
    "dataframe.describe().show()\n",
    "\n",
    "# Returns columns of dataframe\n",
    "dataframe.columns\n",
    "\n",
    "# Counts the number of rows in dataframe\n",
    "dataframe.count()\n",
    "\n",
    "# Counts the number of distinct rows in dataframe\n",
    "dataframe.distinct().count()\n",
    "\n",
    "# Prints plans including physical and logical\n",
    "dataframe.explain(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Useful common functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [1] Remove Duplicate Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Duplicate values in a table can be eliminated by using dropDuplicates() function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+----------------+----+--------------+--------------------+-------------+\n",
      "|                 _id|  amazon_product_url|              author| bestsellers_date|         description|   price|   published_date|       publisher|rank|rank_last_week|               title|weeks_on_list|\n",
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+----------------+----+--------------+--------------------+-------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Clive Cussler wit...|[[1213401600000]]|Juan Cabrillo and...|[26.95,]|[[1214697600000]]|          Putnam| [4]|           [3]|         PLAGUE SHIP|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|      Jeffery Deaver|[[1215820800000]]|Detectives Lincol...|   [, 0]|[[1217116800000]]|Simon & Schuster|[20]|           [0]|   THE BROKEN WINDOW|          [0]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|        Daniel Silva|[[1217030400000]]|Gabriel Allon, an...|[26.95,]|[[1218326400000]]|          Putnam| [1]|           [0]|  THE SECRET SERVANT|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Janet Evanovich|[[1217030400000]]|Stephanie Plum an...|[27.95,]|[[1218326400000]]|    St. Martin’s| [9]|           [7]|   FEARLESS FOURTEEN|          [6]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|          Jane Green|[[1218240000000]]|A woman’s life ch...|   [, 0]|[[1219536000000]]|          Viking|[18]|           [0]|     THE BEACH HOUSE|          [0]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|      Brunonia Barry|[[1220054400000]]|Secrets of a fami...|[24.95,]|[[1221350400000]]|          Morrow|[13]|          [10]|     THE LACE READER|          [5]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|    David Wroblewski|[[1221264000000]]|A mute takes refu...|[25.95,]|[[1222560000000]]|            Ecco| [9]|           [9]|THE STORY OF EDGA...|         [14]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       John Sandford|[[1225497600000]]|Virgil Flowers in...|   [, 0]|[[1226793600000]]|          Putnam|[20]|           [0]|      HEAT LIGHTNING|          [0]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|            J D Robb|[[1226102400000]]|Lt. Eve Dallas in...|[25.95,]|[[1227398400000]]|          Putnam| [2]|           [0]|  SALVATION IN DEATH|          [1]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|       Toni Morrison|[[1226707200000]]|In 17th-century A...|[23.95,]|[[1228003200000]]|           Knopf| [5]|           [0]|             A MERCY|          [1]|\n",
      "+--------------------+--------------------+--------------------+-----------------+--------------------+--------+-----------------+----------------+----+--------------+--------------------+-------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dataframe_dropdup = dataframe.dropDuplicates() \n",
    "dataframe_dropdup.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [2] 'Select' Operation "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to obtain columns by column or by indexing (i.e. dataframe[‘author’])."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|              author|\n",
      "+--------------------+\n",
      "|       Dean R Koontz|\n",
      "|     Stephenie Meyer|\n",
      "|        Emily Giffin|\n",
      "|   Patricia Cornwell|\n",
      "|     Chuck Palahniuk|\n",
      "|James Patterson a...|\n",
      "|       John Sandford|\n",
      "|       Jimmy Buffett|\n",
      "|    Elizabeth George|\n",
      "|      David Baldacci|\n",
      "+--------------------+\n",
      "only showing top 10 rows\n",
      "\n",
      "+--------------------+--------------------+----+--------+\n",
      "|              author|               title|rank|   price|\n",
      "+--------------------+--------------------+----+--------+\n",
      "|       Dean R Koontz|           ODD HOURS| [1]|  [, 27]|\n",
      "|     Stephenie Meyer|            THE HOST| [2]|[25.99,]|\n",
      "|        Emily Giffin|LOVE THE ONE YOU'...| [3]|[24.95,]|\n",
      "|   Patricia Cornwell|           THE FRONT| [4]|[22.95,]|\n",
      "|     Chuck Palahniuk|               SNUFF| [5]|[24.95,]|\n",
      "|James Patterson a...|SUNDAYS AT TIFFANY’S| [6]|[24.99,]|\n",
      "|       John Sandford|        PHANTOM PREY| [7]|[26.95,]|\n",
      "|       Jimmy Buffett|          SWINE NOT?| [8]|[21.99,]|\n",
      "|    Elizabeth George|     CARELESS IN RED| [9]|[27.95,]|\n",
      "|      David Baldacci|     THE WHOLE TRUTH|[10]|[26.99,]|\n",
      "+--------------------+--------------------+----+--------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Show all entries in title column\n",
    "dataframe.select(\"author\").show(10)\n",
    "\n",
    "#Show all entries in title, author, rank, price columns\n",
    "dataframe.select(\"author\", \"title\", \"rank\", \"price\").show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [3] 'When' Operation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----------------------------------------------------+\n",
      "|               title|CASE WHEN (NOT (title = ODD HOURS)) THEN 1 ELSE 0 END|\n",
      "+--------------------+-----------------------------------------------------+\n",
      "|           ODD HOURS|                                                    0|\n",
      "|            THE HOST|                                                    1|\n",
      "|LOVE THE ONE YOU'...|                                                    1|\n",
      "|           THE FRONT|                                                    1|\n",
      "|               SNUFF|                                                    1|\n",
      "|SUNDAYS AT TIFFANY’S|                                                    1|\n",
      "|        PHANTOM PREY|                                                    1|\n",
      "|          SWINE NOT?|                                                    1|\n",
      "|     CARELESS IN RED|                                                    1|\n",
      "|     THE WHOLE TRUTH|                                                    1|\n",
      "+--------------------+-----------------------------------------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Show title and assign 0 or 1 depending on title\n",
    "dataframe.select(\"title\", when(dataframe.title != 'ODD HOURS', 1).otherwise(0)).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [4] 'isin' Operation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-------------+-----------------+--------------------+--------+-----------------+------------+----+--------------+--------------------+-------------+\n",
      "|                 _id|  amazon_product_url|       author| bestsellers_date|         description|   price|   published_date|   publisher|rank|rank_last_week|               title|weeks_on_list|\n",
      "+--------------------+--------------------+-------------+-----------------+--------------------+--------+-----------------+------------+----+--------------+--------------------+-------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...| Emily Giffin|[[1211587200000]]|A woman's happy m...|[24.95,]|[[1212883200000]]|St. Martin's| [3]|           [2]|LOVE THE ONE YOU'...|          [2]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|John Sandford|[[1211587200000]]|The Minneapolis d...|[26.95,]|[[1212883200000]]|      Putnam| [7]|           [4]|        PHANTOM PREY|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...| Emily Giffin|[[1212192000000]]|A woman’s happy m...|[24.95,]|[[1213488000000]]|St. Martin’s| [4]|           [3]|LOVE THE ONE YOU'...|          [3]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|John Sandford|[[1212192000000]]|The Minneapolis d...|[26.95,]|[[1213488000000]]|      Putnam| [9]|           [7]|        PHANTOM PREY|          [4]|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...| Emily Giffin|[[1212796800000]]|A woman’s happy m...|[24.95,]|[[1214092800000]]|St. Martin’s| [4]|           [4]|LOVE THE ONE YOU'...|          [4]|\n",
      "+--------------------+--------------------+-------------+-----------------+--------------------+--------+-----------------+------------+----+--------------+--------------------+-------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Show rows with specified authors if in the given options\n",
    "dataframe [dataframe.author.isin(\"John Sandford\", \"Emily Giffin\")].show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [5] 'Like' Operation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+------------------+\n",
      "|              author|               title|title LIKE % THE %|\n",
      "+--------------------+--------------------+------------------+\n",
      "|       Dean R Koontz|           ODD HOURS|             false|\n",
      "|     Stephenie Meyer|            THE HOST|             false|\n",
      "|        Emily Giffin|LOVE THE ONE YOU'...|              true|\n",
      "|   Patricia Cornwell|           THE FRONT|             false|\n",
      "|     Chuck Palahniuk|               SNUFF|             false|\n",
      "|James Patterson a...|SUNDAYS AT TIFFANY’S|             false|\n",
      "|       John Sandford|        PHANTOM PREY|             false|\n",
      "|       Jimmy Buffett|          SWINE NOT?|             false|\n",
      "|    Elizabeth George|     CARELESS IN RED|             false|\n",
      "|      David Baldacci|     THE WHOLE TRUTH|             false|\n",
      "|        Troy Denning|          INVINCIBLE|             false|\n",
      "|          James Frey|BRIGHT SHINY MORNING|             false|\n",
      "|         Garth Stein|THE ART OF RACING...|              true|\n",
      "|     Debbie Macomber|       TWENTY WISHES|             false|\n",
      "|         Jeff Shaara|      THE STEEL WAVE|             false|\n",
      "+--------------------+--------------------+------------------+\n",
      "only showing top 15 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Show author and title is TRUE if title has \" THE \" word in titles\n",
    "dataframe.select(\"author\", \"title\", dataframe.title.like(\"% THE %\")).show(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [6] 'Startswith' — 'Endswith' Operation "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "StartsWith scans from the beginning of word/content with specified criteria in the brackets. In parallel, EndsWith processes the word/content starting from the end. Both of the functions are case sensitive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+--------------------+----------------------+\n",
      "|           author|               title|startswith(title, THE)|\n",
      "+-----------------+--------------------+----------------------+\n",
      "|    Dean R Koontz|           ODD HOURS|                 false|\n",
      "|  Stephenie Meyer|            THE HOST|                  true|\n",
      "|     Emily Giffin|LOVE THE ONE YOU'...|                 false|\n",
      "|Patricia Cornwell|           THE FRONT|                  true|\n",
      "|  Chuck Palahniuk|               SNUFF|                 false|\n",
      "+-----------------+--------------------+----------------------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+-----------------+--------------------+-------------------+\n",
      "|           author|               title|endswith(title, NT)|\n",
      "+-----------------+--------------------+-------------------+\n",
      "|    Dean R Koontz|           ODD HOURS|              false|\n",
      "|  Stephenie Meyer|            THE HOST|              false|\n",
      "|     Emily Giffin|LOVE THE ONE YOU'...|              false|\n",
      "|Patricia Cornwell|           THE FRONT|               true|\n",
      "|  Chuck Palahniuk|               SNUFF|              false|\n",
      "+-----------------+--------------------+-------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dataframe.select(\"author\", \"title\", dataframe.title.startswith(\"THE\")).show(5)\n",
    "dataframe.select(\"author\", \"title\", dataframe.title.endswith(\"NT\")).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [7] 'Substring' Operation "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the following examples, texts are extracted from the index numbers (1, 3), (3, 6) and (1, 6)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+\n",
      "|title|\n",
      "+-----+\n",
      "|  Dea|\n",
      "|  Ste|\n",
      "|  Emi|\n",
      "|  Pat|\n",
      "|  Chu|\n",
      "+-----+\n",
      "only showing top 5 rows\n",
      "\n",
      "+------+\n",
      "| title|\n",
      "+------+\n",
      "|an R K|\n",
      "|epheni|\n",
      "|ily Gi|\n",
      "|tricia|\n",
      "|uck Pa|\n",
      "+------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+------+\n",
      "| title|\n",
      "+------+\n",
      "|Dean R|\n",
      "|Stephe|\n",
      "|Emily |\n",
      "|Patric|\n",
      "|Chuck |\n",
      "+------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dataframe.select(dataframe.author.substr(1, 3).alias(\"title\")).show(5)\n",
    "dataframe.select(dataframe.author.substr(3, 6).alias(\"title\")).show(5)\n",
    "dataframe.select(dataframe.author.substr(1, 6).alias(\"title\")).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [8] Adding Columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[_id: struct<$oid:string>, amazon_product_url: string, author: string, bestsellers_date: struct<$date:struct<$numberLong:string>>, description: string, price: struct<$numberDouble:string,$numberInt:string>, published_date: struct<$date:struct<$numberLong:string>>, publisher: string, rank: struct<$numberInt:string>, rank_last_week: struct<$numberInt:string>, title: string, weeks_on_list: struct<$numberInt:string>, new_column: string]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from pyspark.sql import functions as F\n",
    "\n",
    "# Lit() is required while we are creating columns with exact values.\n",
    "dataframe = dataframe.withColumn('new_column', F.lit('This is a new column'))\n",
    "\n",
    "display(dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [9] Updating Columns "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For updated operations of DataFrame API, withColumnRenamed() function is used with two parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+--------------------+\n",
      "|                 _id|                 URL|           author| bestsellers_date|         description|   price|   published_date|    publisher|rank|rank_last_week|               title|weeks_on_list|          new_column|\n",
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+--------------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|    Dean R Koontz|[[1211587200000]]|Odd Thomas, who c...|  [, 27]|[[1212883200000]]|       Bantam| [1]|           [0]|           ODD HOURS|          [1]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|  Stephenie Meyer|[[1211587200000]]|Aliens have taken...|[25.99,]|[[1212883200000]]|Little, Brown| [2]|           [1]|            THE HOST|          [3]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Emily Giffin|[[1211587200000]]|A woman's happy m...|[24.95,]|[[1212883200000]]| St. Martin's| [3]|           [2]|LOVE THE ONE YOU'...|          [2]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Patricia Cornwell|[[1211587200000]]|A Massachusetts s...|[22.95,]|[[1212883200000]]|       Putnam| [4]|           [0]|           THE FRONT|          [1]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|  Chuck Palahniuk|[[1211587200000]]|An aging porn que...|[24.95,]|[[1212883200000]]|    Doubleday| [5]|           [0]|               SNUFF|          [1]|This is a new column|\n",
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Update column 'amazon_product_url' with 'URL'\n",
    "dataframe = dataframe.withColumnRenamed('amazon_product_url', 'URL')\n",
    "\n",
    "dataframe.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [10] Removing Columns "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Removal of a column can be achieved in two ways: \\\n",
    "1. Adding the list of column names in the drop() function \n",
    "2. Specifying columns by pointing in the drop function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+----+--------------+--------------------+-------------+--------------------+\n",
      "|                 _id|                 URL|           author| bestsellers_date|         description|   price|rank|rank_last_week|               title|weeks_on_list|          new_column|\n",
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+----+--------------+--------------------+-------------+--------------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|    Dean R Koontz|[[1211587200000]]|Odd Thomas, who c...|  [, 27]| [1]|           [0]|           ODD HOURS|          [1]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|  Stephenie Meyer|[[1211587200000]]|Aliens have taken...|[25.99,]| [2]|           [1]|            THE HOST|          [3]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Emily Giffin|[[1211587200000]]|A woman's happy m...|[24.95,]| [3]|           [2]|LOVE THE ONE YOU'...|          [2]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Patricia Cornwell|[[1211587200000]]|A Massachusetts s...|[22.95,]| [4]|           [0]|           THE FRONT|          [1]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|  Chuck Palahniuk|[[1211587200000]]|An aging porn que...|[24.95,]| [5]|           [0]|               SNUFF|          [1]|This is a new column|\n",
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+----+--------------+--------------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+----+--------------+--------------------+-------------+--------------------+\n",
      "|                 _id|                 URL|           author| bestsellers_date|         description|   price|rank|rank_last_week|               title|weeks_on_list|          new_column|\n",
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+----+--------------+--------------------+-------------+--------------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|    Dean R Koontz|[[1211587200000]]|Odd Thomas, who c...|  [, 27]| [1]|           [0]|           ODD HOURS|          [1]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|  Stephenie Meyer|[[1211587200000]]|Aliens have taken...|[25.99,]| [2]|           [1]|            THE HOST|          [3]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|     Emily Giffin|[[1211587200000]]|A woman's happy m...|[24.95,]| [3]|           [2]|LOVE THE ONE YOU'...|          [2]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Patricia Cornwell|[[1211587200000]]|A Massachusetts s...|[22.95,]| [4]|           [0]|           THE FRONT|          [1]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|  Chuck Palahniuk|[[1211587200000]]|An aging porn que...|[24.95,]| [5]|           [0]|               SNUFF|          [1]|This is a new column|\n",
      "+--------------------+--------------------+-----------------+-----------------+--------------------+--------+----+--------------+--------------------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 1.\n",
    "dataframe_remove = dataframe.drop(\"publisher\", \"published_date\").show(5)\n",
    "\n",
    "# 2.\n",
    "dataframe_remove2 = dataframe.drop(dataframe.publisher).drop(dataframe.published_date).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [11] 'GroupBy' Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-----+\n",
      "|              author|count|\n",
      "+--------------------+-----+\n",
      "|          James Frey|    2|\n",
      "|    Elin Hilderbrand|   58|\n",
      "|   Sharon Kay Penman|    2|\n",
      "|         Kate Jacobs|    3|\n",
      "|       Karen Robards|    6|\n",
      "|     Gary Shteyngart|    3|\n",
      "|         Lisa Genova|    7|\n",
      "|James Patterson a...|   30|\n",
      "|         Ruth Reichl|    3|\n",
      "|         JRR Tolkien|    2|\n",
      "+--------------------+-----+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Group by author, count the books of the authors in the groups\n",
    "\n",
    "dataframe.groupBy(\"author\").count().show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [12] 'Filter' Operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+---------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------+-------------+--------------------+\n",
      "|                 _id|                 URL|         author| bestsellers_date|         description|   price|   published_date|    publisher|rank|rank_last_week|   title|weeks_on_list|          new_column|\n",
      "+--------------------+--------------------+---------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------+-------------+--------------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Stephenie Meyer|[[1211587200000]]|Aliens have taken...|[25.99,]|[[1212883200000]]|Little, Brown| [2]|           [1]|THE HOST|          [3]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Stephenie Meyer|[[1212192000000]]|Aliens have taken...|[25.99,]|[[1213488000000]]|Little, Brown| [2]|           [2]|THE HOST|          [4]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Stephenie Meyer|[[1212796800000]]|Aliens have taken...|[25.99,]|[[1214092800000]]|Little, Brown| [2]|           [2]|THE HOST|          [5]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Stephenie Meyer|[[1213401600000]]|Aliens have taken...|[25.99,]|[[1214697600000]]|Little, Brown| [3]|           [2]|THE HOST|          [6]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Stephenie Meyer|[[1214006400000]]|Aliens have taken...|[25.99,]|[[1215302400000]]|Little, Brown| [3]|           [3]|THE HOST|          [7]|This is a new column|\n",
      "+--------------------+--------------------+---------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------+-------------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Filtering entries of title\n",
    "# Only keeps records having value 'THE HOST'\n",
    "\n",
    "dataframe.filter(dataframe[\"title\"] == 'THE HOST').show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [13] Handling Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replacing null values\n",
    "dataframe.na.fill()\n",
    "dataFrame.fillna()\n",
    "dataFrameNaFunctions.fill()\n",
    "\n",
    "# Returning new dataframe restricting rows with null valuesdataframe.na.drop()\n",
    "dataFrame.dropna()\n",
    "dataFrameNaFunctions.drop()\n",
    "\n",
    "# Return new dataframe replacing one value with another\n",
    "dataframe.na.replace(5, 15)\n",
    "dataFrame.replace()\n",
    "dataFrameNaFunctions.replace()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [14] Repartitioning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to increase or decrease the existing level of partitioning in RDD. \\\n",
    "\n",
    "Increasing can be actualized by using **repartition(self, numPartitions)** function which results in a new RDD that obtains same/higher number of partitions. \\\n",
    "\n",
    "Decreasing can be processed with **coalesce(self, numPartitions, shuffle=False)** function that results in new RDD with a reduced number of partitions to a specified number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dataframe with 10 partitions\n",
    "dataframe.repartition(10).rdd.getNumPartitions()\n",
    "\n",
    "# Dataframe with 1 partition\n",
    "dataframe.coalesce(1).rdd.getNumPartitions()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [15] Running SQL Commnads In Spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+---------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+--------------------+\n",
      "|                 _id|                 URL|         author| bestsellers_date|         description|   price|   published_date|    publisher|rank|rank_last_week|               title|weeks_on_list|          new_column|\n",
      "+--------------------+--------------------+---------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+--------------------+\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|  Dean R Koontz|[[1211587200000]]|Odd Thomas, who c...|  [, 27]|[[1212883200000]]|       Bantam| [1]|           [0]|           ODD HOURS|          [1]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|Stephenie Meyer|[[1211587200000]]|Aliens have taken...|[25.99,]|[[1212883200000]]|Little, Brown| [2]|           [1]|            THE HOST|          [3]|This is a new column|\n",
      "|[5b4aa4ead3089013...|http://www.amazon...|   Emily Giffin|[[1211587200000]]|A woman's happy m...|[24.95,]|[[1212883200000]]| St. Martin's| [3]|           [2]|LOVE THE ONE YOU'...|          [2]|This is a new column|\n",
      "+--------------------+--------------------+---------------+-----------------+--------------------+--------+-----------------+-------------+----+--------------+--------------------+-------------+--------------------+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Registering a table\n",
    "dataframe.registerTempTable(\"df\")\n",
    "\n",
    "sc.sql(\"select * from df\").show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.sql(\"SELECT CASE WHEN description LIKE '%love%' THEN 'Love_Theme'             \n",
    "           WHEN description LIKE '%hate%' THEN 'Hate_Theme'            \n",
    "           WHEN description LIKE '%happy%' THEN 'Happiness_Theme' \\            \n",
    "           WHEN description LIKE '%anger%' THEN 'Anger_Theme' \\             \n",
    "           WHEN description LIKE '%horror%' THEN 'Horror_Theme' \\              \n",
    "           WHEN description LIKE '%death%' THEN 'Criminal_Theme' \\               \n",
    "           WHEN description LIKE '%detective%' THEN 'Mystery_Theme' ELSE 'Other_Themes' END Themes from df\").groupBy('Themes').count().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [16] Output in Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting dataframe into an RDD\n",
    "rdd_convert = dataframe.rdd\n",
    "\n",
    "# Converting dataframe into a RDD of string \n",
    "dataframe.toJSON().first()\n",
    "\n",
    "# Obtaining contents of df as Pandas \n",
    "dataFramedataframe.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [17] Write & Save to Files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Any data source type that is loaded to our code as data frames can easily be converted and saved into other types including .parquet and .json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write & Save File in .parquet format\n",
    "dataframe.select(\"author\", \"title\", \"rank\", \"description\") \\\n",
    "        .write \\\n",
    "        .save(\"Rankings_Descriptions.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write & Save File in .json format\n",
    "dataframe.select(\"author\", \"title\") \\\n",
    "        .write \\\n",
    "        .save(\"Authors_Titles.json\",format=\"json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [18] End Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}