PySpark and SparkSQL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Library Importing\n",
    "\n",
    "import pandas as pd\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.context import SparkContext\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import *\n",
    "from datetime import date, timedelta, datetime\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# INITIALIZING SPARKSESSION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Initialization of a Spark Session\n",
    "sc = SparkSession.builder.appName(\"PySparkExample\")\\\n",
    "    .config (\"spark.sql.shuffle.partitions\", \"50\") \\\n",
    "    .config(\"spark.driver.maxResultSize\",\"5g\") \\\n",
    "    .config (\"spark.sql.execution.arrow.enabled\", \"true\")\\\n",
    "    .getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CREATING DATAFRAMES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read from json, parquet, txt, csv formatted files.\n",
    "#Creates a spark data frame called as raw_data.\n",
    "\n",
    "#JSON\n",
    "dataframe = sc.read.json('dataset/nyt2.json')\n",
    "\n",
    "#TXT FILES\n",
    "# dataframe_txt = sc.read.text('text_data.txt')\n",
    "\n",
    "#CSV FILES\n",
    "# dataframe_csv = sc.read.csv('csv_data.csv')\n",
    "\n",
    "#PARQUET FILES\n",
    "# dataframe_parquet = sc.read.load('parquet_data.parquet')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe = sc.read.json('dataset/nyt2.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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": [
    "# DUPLICATE VALUES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe_dropdup = dataframe.dropDuplicates()"
   ]
  },
  {
   "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...|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.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['_id',\n",
       " 'amazon_product_url',\n",
       " 'author',\n",
       " 'bestsellers_date',\n",
       " 'description',\n",
       " 'price',\n",
       " 'published_date',\n",
       " 'publisher',\n",
       " 'rank',\n",
       " 'rank_last_week',\n",
       " 'title',\n",
       " 'weeks_on_list']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# QUERIES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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": [
    "# Select Operation\n",
    "\n",
    "dataframe.select(\"author\").show(10) #Show all entries in title column\n",
    "\n",
    "dataframe.select(\"author\", \"title\", \"rank\", \"price\").show(10)  #Show all entries in title, author, rank, price columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# When"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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",
    "\n",
    "dataframe.select(\"title\", when(dataframe.title != 'ODD HOURS', 1).otherwise(0)).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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",
    "\n",
    "dataframe [dataframe.author.isin(\"John Sandford\",\"Emily Giffin\")].show(5)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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",
    "\n",
    "dataframe.select(\"author\", \"title\", dataframe.title.like(\"% THE %\")).show(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# StartsWith - EndsWith"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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",
    "\n",
    "dataframe.select(\"author\", \"title\", dataframe.title.endswith(\"NT\")).show(5)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Substring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+\n",
      "| title|\n",
      "+------+\n",
      "|Dean R|\n",
      "|Stephe|\n",
      "|Emily |\n",
      "|Patric|\n",
      "|Chuck |\n",
      "|James |\n",
      "|John S|\n",
      "|Jimmy |\n",
      "|Elizab|\n",
      "|David |\n",
      "|Troy D|\n",
      "|James |\n",
      "|Garth |\n",
      "|Debbie|\n",
      "|Jeff S|\n",
      "|Philli|\n",
      "|Jhumpa|\n",
      "|Joseph|\n",
      "|John G|\n",
      "|James |\n",
      "+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dataframe.select(dataframe.author.substr(1, 6).alias(\"title\")).show()"
   ]
  },
  {
   "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",
    "\n",
    "dataframe.select(dataframe.author.substr(3, 6).alias(\"title\")).show(5)\n",
    "\n",
    "dataframe.select(dataframe.author.substr(1, 6).alias(\"title\")).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# INSPECT DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('_id', 'struct<$oid:string>'),\n",
       " ('amazon_product_url', 'string'),\n",
       " ('author', 'string'),\n",
       " ('bestsellers_date', 'struct<$date:struct<$numberLong:string>>'),\n",
       " ('description', 'string'),\n",
       " ('price', 'struct<$numberDouble:string,$numberInt:string>'),\n",
       " ('published_date', 'struct<$date:struct<$numberLong:string>>'),\n",
       " ('publisher', 'string'),\n",
       " ('rank', 'struct<$numberInt:string>'),\n",
       " ('rank_last_week', 'struct<$numberInt:string>'),\n",
       " ('title', 'string'),\n",
       " ('weeks_on_list', 'struct<$numberInt:string>')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Returns dataframe column names and data types\n",
    "dataframe.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "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"
     ]
    }
   ],
   "source": [
    "# Displays the content of dataframe\n",
    "dataframe.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(_id=Row($oid='5b4aa4ead3089013507db18b'), amazon_product_url='http://www.amazon.com/Odd-Hours-Dean-Koontz/dp/0553807056?tag=NYTBS-20', author='Dean R Koontz', bestsellers_date=Row($date=Row($numberLong='1211587200000')), description='Odd Thomas, who can communicate with the dead, confronts evil forces in a California coastal town.', price=Row($numberDouble=None, $numberInt='27'), published_date=Row($date=Row($numberLong='1212883200000')), publisher='Bantam', rank=Row($numberInt='1'), rank_last_week=Row($numberInt='0'), title='ODD HOURS', weeks_on_list=Row($numberInt='1'))"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Return first n rows\n",
    "dataframe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(_id=Row($oid='5b4aa4ead3089013507db18b'), amazon_product_url='http://www.amazon.com/Odd-Hours-Dean-Koontz/dp/0553807056?tag=NYTBS-20', author='Dean R Koontz', bestsellers_date=Row($date=Row($numberLong='1211587200000')), description='Odd Thomas, who can communicate with the dead, confronts evil forces in a California coastal town.', price=Row($numberDouble=None, $numberInt='27'), published_date=Row($date=Row($numberLong='1212883200000')), publisher='Bantam', rank=Row($numberInt='1'), rank_last_week=Row($numberInt='0'), title='ODD HOURS', weeks_on_list=Row($numberInt='1'))"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Returns first row\n",
    "dataframe.first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(_id=Row($oid='5b4aa4ead3089013507db18b'), amazon_product_url='http://www.amazon.com/Odd-Hours-Dean-Koontz/dp/0553807056?tag=NYTBS-20', author='Dean R Koontz', bestsellers_date=Row($date=Row($numberLong='1211587200000')), description='Odd Thomas, who can communicate with the dead, confronts evil forces in a California coastal town.', price=Row($numberDouble=None, $numberInt='27'), published_date=Row($date=Row($numberLong='1212883200000')), publisher='Bantam', rank=Row($numberInt='1'), rank_last_week=Row($numberInt='0'), title='ODD HOURS', weeks_on_list=Row($numberInt='1')),\n",
       " Row(_id=Row($oid='5b4aa4ead3089013507db18c'), amazon_product_url='http://www.amazon.com/The-Host-Novel-Stephenie-Meyer/dp/0316218502?tag=NYTBS-20', author='Stephenie Meyer', bestsellers_date=Row($date=Row($numberLong='1211587200000')), description='Aliens have taken control of the minds and bodies of most humans, but one woman won’t surrender.', price=Row($numberDouble='25.99', $numberInt=None), published_date=Row($date=Row($numberLong='1212883200000')), publisher='Little, Brown', rank=Row($numberInt='2'), rank_last_week=Row($numberInt='1'), title='THE HOST', weeks_on_list=Row($numberInt='3')),\n",
       " Row(_id=Row($oid='5b4aa4ead3089013507db18d'), amazon_product_url='http://www.amazon.com/Love-Youre-With-Emily-Giffin/dp/0312348665?tag=NYTBS-20', author='Emily Giffin', bestsellers_date=Row($date=Row($numberLong='1211587200000')), description=\"A woman's happy marriage is shaken when she encounters an old boyfriend.\", price=Row($numberDouble='24.95', $numberInt=None), published_date=Row($date=Row($numberLong='1212883200000')), publisher=\"St. Martin's\", rank=Row($numberInt='3'), rank_last_week=Row($numberInt='2'), title=\"LOVE THE ONE YOU'RE WITH\", weeks_on_list=Row($numberInt='2')),\n",
       " Row(_id=Row($oid='5b4aa4ead3089013507db18e'), amazon_product_url='http://www.amazon.com/The-Front-Garano-Patricia-Cornwell-ebook/dp/B0017T0C9M?tag=NYTBS-20', author='Patricia Cornwell', bestsellers_date=Row($date=Row($numberLong='1211587200000')), description='A Massachusetts state investigator and his team from \"At Risk\" confront a rogue association of municipal police departments.', price=Row($numberDouble='22.95', $numberInt=None), published_date=Row($date=Row($numberLong='1212883200000')), publisher='Putnam', rank=Row($numberInt='4'), rank_last_week=Row($numberInt='0'), title='THE FRONT', weeks_on_list=Row($numberInt='1')),\n",
       " Row(_id=Row($oid='5b4aa4ead3089013507db18f'), amazon_product_url='http://www.amazon.com/Snuff-Chuck-Palahniuk/dp/0385517882?tag=NYTBS-20', author='Chuck Palahniuk', bestsellers_date=Row($date=Row($numberLong='1211587200000')), description='An aging porn queens aims to cap her career by having sex on film with 600 men in one day.', price=Row($numberDouble='24.95', $numberInt=None), published_date=Row($date=Row($numberLong='1212883200000')), publisher='Doubleday', rank=Row($numberInt='5'), rank_last_week=Row($numberInt='0'), title='SNUFF', weeks_on_list=Row($numberInt='1'))]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Return first n rows\n",
    "dataframe.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+--------------------+---------------+--------------------+---------+------------------+\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"
     ]
    }
   ],
   "source": [
    "# Computes summary statistics\n",
    "dataframe.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['_id',\n",
       " 'amazon_product_url',\n",
       " 'author',\n",
       " 'bestsellers_date',\n",
       " 'description',\n",
       " 'price',\n",
       " 'published_date',\n",
       " 'publisher',\n",
       " 'rank',\n",
       " 'rank_last_week',\n",
       " 'title',\n",
       " 'weeks_on_list']"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Returns columns of dataframe\n",
    "dataframe.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10195"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Counts the number of rows in dataframe\n",
    "dataframe.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10195"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Counts the number of distinct rows in dataframe\n",
    "dataframe.distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Parsed Logical Plan ==\n",
      "Relation[_id#36,amazon_product_url#37,author#38,bestsellers_date#39,description#40,price#41,published_date#42,publisher#43,rank#44,rank_last_week#45,title#46,weeks_on_list#47] 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#36,amazon_product_url#37,author#38,bestsellers_date#39,description#40,price#41,published_date#42,publisher#43,rank#44,rank_last_week#45,title#46,weeks_on_list#47] json\n",
      "\n",
      "== Optimized Logical Plan ==\n",
      "Relation[_id#36,amazon_product_url#37,author#38,bestsellers_date#39,description#40,price#41,published_date#42,publisher#43,rank#44,rank_last_week#45,title#46,weeks_on_list#47] json\n",
      "\n",
      "== Physical Plan ==\n",
      "*(1) FileScan json [_id#36,amazon_product_url#37,author#38,bestsellers_date#39,description#40,price#41,published_date#42,publisher#43,rank#44,rank_last_week#45,title#46,weeks_on_list#47] Batched: false, Format: JSON, Location: InMemoryFileIndex[file:/C:/Users/ersoyp/Documents/MEDIUM/Code/dataset/nyt2.json], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<_id:struct<$oid:string>,amazon_product_url:string,author:string,bestsellers_date:struct<$d...\n"
     ]
    }
   ],
   "source": [
    "# Prints plans including physical and logical\n",
    "dataframe.explain(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ADD, UPDATE & REMOVE COLUMNS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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|          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": [
    "# Lit() is required while we are creating columns with exact values.\n",
    "\n",
    "dataframe = dataframe.withColumn('new_column', lit('This is a new column'))\n",
    "dataframe.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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",
    "\n",
    "dataframe = dataframe.withColumnRenamed('amazon_product_url', 'URL')\n",
    "dataframe.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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": [
    "dataframe_remove = dataframe.drop(\"publisher\", \"published_date\").show(5)\n",
    "\n",
    "dataframe_remove2 = dataframe.drop(dataframe.publisher).drop(dataframe.published_date).show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GROUPBY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+-----+\n",
      "|rank_last_week|count|\n",
      "+--------------+-----+\n",
      "|           [7]|  470|\n",
      "|          [10]|  363|\n",
      "|          [15]|  123|\n",
      "|          [11]|  327|\n",
      "|           [4]|  524|\n",
      "|           [3]|  520|\n",
      "|          [12]|  289|\n",
      "|           [6]|  497|\n",
      "|          [13]|  246|\n",
      "|           [8]|  460|\n",
      "+--------------+-----+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Group by author, count the books of the authors in the groups\n",
    "dataframe.groupBy(\"rank_last_week\").count().show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MISIING AND REPLACING VALUES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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": [
    "# Replace null values\n",
    "dataframe.na.fill(50).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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": [
    "# Return new dataframe restricting rows with null values\n",
    "dataframe.na.drop().show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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": [
    "# Return new dataframe replacing one value with another\n",
    "dataframe.na.replace(10, 20).show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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": [
    "dataframe.na.replace(['_id', 'Unique_ID'], ['amazon_product_url', 'URL'], 'name').show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# REPARTITIONING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dataframe with 10 partitions\n",
    "\n",
    "dataframe.repartition(10).rdd.getNumPartitions()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dataframe with 1 partition\n",
    "\n",
    "dataframe.coalesce(1).rdd.getNumPartitions()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RUNNING SQL QUERIES PROGRAMMATICALLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Registering DataFrames "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe.createGlobalTempView(\"nyt2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe.createTempView(\"bestsellers\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe.createOrReplaceTempView(\"nyt2\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Registering tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Registering a table\n",
    "dataframe.registerTempTable(\"df\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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",
      "+---------------+-----+\n",
      "|         Themes|count|\n",
      "+---------------+-----+\n",
      "|    Anger_Theme|  203|\n",
      "|   Other_Themes| 8778|\n",
      "|  Mystery_Theme|  454|\n",
      "|     Hate_Theme|   23|\n",
      "| Criminal_Theme|  305|\n",
      "|   Horror_Theme|    6|\n",
      "|Happiness_Theme|   34|\n",
      "|     Love_Theme|  392|\n",
      "+---------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sc.sql(\"select * from df\").show(3)\n",
    "\n",
    "\n",
    "sc.sql(\"select \\\n",
    "               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' \\\n",
    "               ELSE 'Other_Themes' \\\n",
    "               END Themes \\\n",
    "       from df\").groupBy('Themes').count().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OUTPUT\n",
    "\n",
    "# Data Structures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting dataframe into an RDD\n",
    "rdd_convert = dataframe.rdd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"_id\":{\"$oid\":\"5b4aa4ead3089013507db18b\"},\"URL\":\"http://www.amazon.com/Odd-Hours-Dean-Koontz/dp/0553807056?tag=NYTBS-20\",\"author\":\"Dean R Koontz\",\"bestsellers_date\":{\"$date\":{\"$numberLong\":\"1211587200000\"}},\"description\":\"Odd Thomas, who can communicate with the dead, confronts evil forces in a California coastal town.\",\"price\":{\"$numberInt\":\"27\"},\"published_date\":{\"$date\":{\"$numberLong\":\"1212883200000\"}},\"publisher\":\"Bantam\",\"rank\":{\"$numberInt\":\"1\"},\"rank_last_week\":{\"$numberInt\":\"0\"},\"title\":\"ODD HOURS\",\"weeks_on_list\":{\"$numberInt\":\"1\"},\"new_column\":\"This is a new column\"}'"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Converting dataframe into a RDD of string\n",
    "dataframe.toJSON().first()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>URL</th>\n",
       "      <th>author</th>\n",
       "      <th>bestsellers_date</th>\n",
       "      <th>description</th>\n",
       "      <th>price</th>\n",
       "      <th>published_date</th>\n",
       "      <th>publisher</th>\n",
       "      <th>rank</th>\n",
       "      <th>rank_last_week</th>\n",
       "      <th>title</th>\n",
       "      <th>weeks_on_list</th>\n",
       "      <th>new_column</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(5b4aa4ead3089013507db18b,)</td>\n",
       "      <td>http://www.amazon.com/Odd-Hours-Dean-Koontz/dp...</td>\n",
       "      <td>Dean R Koontz</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>Odd Thomas, who can communicate with the dead,...</td>\n",
       "      <td>(None, 27)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Bantam</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>ODD HOURS</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(5b4aa4ead3089013507db18c,)</td>\n",
       "      <td>http://www.amazon.com/The-Host-Novel-Stephenie...</td>\n",
       "      <td>Stephenie Meyer</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>Aliens have taken control of the minds and bod...</td>\n",
       "      <td>(25.99, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Little, Brown</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>THE HOST</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(5b4aa4ead3089013507db18d,)</td>\n",
       "      <td>http://www.amazon.com/Love-Youre-With-Emily-Gi...</td>\n",
       "      <td>Emily Giffin</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A woman's happy marriage is shaken when she en...</td>\n",
       "      <td>(24.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>St. Martin's</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>LOVE THE ONE YOU'RE WITH</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(5b4aa4ead3089013507db18e,)</td>\n",
       "      <td>http://www.amazon.com/The-Front-Garano-Patrici...</td>\n",
       "      <td>Patricia Cornwell</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A Massachusetts state investigator and his tea...</td>\n",
       "      <td>(22.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Putnam</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>THE FRONT</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(5b4aa4ead3089013507db18f,)</td>\n",
       "      <td>http://www.amazon.com/Snuff-Chuck-Palahniuk/dp...</td>\n",
       "      <td>Chuck Palahniuk</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>An aging porn queens aims to cap her career by...</td>\n",
       "      <td>(24.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Doubleday</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>SNUFF</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>(5b4aa4ead3089013507db190,)</td>\n",
       "      <td>http://www.amazon.com/Sundays-at-Tiffanys-Jame...</td>\n",
       "      <td>James Patterson and Gabrielle Charbonnet</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A woman finds an unexpected love</td>\n",
       "      <td>(24.99, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Little, Brown</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>SUNDAYS AT TIFFANY’S</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>(5b4aa4ead3089013507db191,)</td>\n",
       "      <td>http://www.amazon.com/Phantom-Prey-John-Sandfo...</td>\n",
       "      <td>John Sandford</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>The Minneapolis detective Lucas Davenport inve...</td>\n",
       "      <td>(26.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Putnam</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>PHANTOM PREY</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>(5b4aa4ead3089013507db192,)</td>\n",
       "      <td>http://www.amazon.com/From-Worse-Southern-Vamp...</td>\n",
       "      <td>Jimmy Buffett</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A Southern family tries to hide its pet pig at...</td>\n",
       "      <td>(21.99, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Little, Brown</td>\n",
       "      <td>(8,)</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>SWINE NOT?</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>(5b4aa4ead3089013507db193,)</td>\n",
       "      <td>http://www.amazon.com/Where-Are-You-Now-Novel/...</td>\n",
       "      <td>Elizabeth George</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>In Cornwall, trying to recover from his wife's...</td>\n",
       "      <td>(27.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Harper</td>\n",
       "      <td>(9,)</td>\n",
       "      <td>(8,)</td>\n",
       "      <td>CARELESS IN RED</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>(5b4aa4ead3089013507db194,)</td>\n",
       "      <td>http://www.amazon.com/The-Whole-Truth-David-Ba...</td>\n",
       "      <td>David Baldacci</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>An intelligence agent and a journalist team up...</td>\n",
       "      <td>(26.99, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Grand Central</td>\n",
       "      <td>(10,)</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>THE WHOLE TRUTH</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>(5b4aa4ead3089013507db195,)</td>\n",
       "      <td>http://www.amazon.com/Careless-Red-Novel-Eliza...</td>\n",
       "      <td>Troy Denning</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>The New Jedi order battles Darth Caedus, Darth...</td>\n",
       "      <td>(None, 27)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Del Rey/Ballantine</td>\n",
       "      <td>(11,)</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>INVINCIBLE</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>(5b4aa4ead3089013507db196,)</td>\n",
       "      <td>http://www.amazon.com/Unaccustomed-Earth-Jhump...</td>\n",
       "      <td>James Frey</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A novel, set in Los Angeles, by the author of ...</td>\n",
       "      <td>(26.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Harper</td>\n",
       "      <td>(12,)</td>\n",
       "      <td>(9,)</td>\n",
       "      <td>BRIGHT SHINY MORNING</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>(5b4aa4ead3089013507db197,)</td>\n",
       "      <td>http://www.amazon.com/The-Racing-Rain-Garth-St...</td>\n",
       "      <td>Garth Stein</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A Lab-terrier mix with great insight into the ...</td>\n",
       "      <td>(23.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Harper</td>\n",
       "      <td>(13,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>THE ART OF RACING IN THE RAIN</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>(5b4aa4ead3089013507db198,)</td>\n",
       "      <td>http://www.amazon.com/Twenty-Wishes-Blossom-St...</td>\n",
       "      <td>Debbie Macomber</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A widow who owns a bookstore on Blossom Street...</td>\n",
       "      <td>(24.95, None)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Mira</td>\n",
       "      <td>(14,)</td>\n",
       "      <td>(10,)</td>\n",
       "      <td>TWENTY WISHES</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>(5b4aa4ead3089013507db199,)</td>\n",
       "      <td>http://www.amazon.com/Hold-Tight-Harlan-Coben/...</td>\n",
       "      <td>Jeff Shaara</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A novel about the Allied invasion in France in...</td>\n",
       "      <td>(None, 28)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Ballantine</td>\n",
       "      <td>(15,)</td>\n",
       "      <td>(11,)</td>\n",
       "      <td>THE STEEL WAVE</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>(5b4aa4ead3089013507db19a,)</td>\n",
       "      <td>http://www.amazon.com/Executive-Privilege-Nove...</td>\n",
       "      <td>Phillip Margolin</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td></td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>HarperCollins Publishers</td>\n",
       "      <td>(16,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>EXECUTIVE PRIVILEGE</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>(5b4aa4ead3089013507db19b,)</td>\n",
       "      <td>http://www.amazon.com/Unaccustomed-Vintage-Con...</td>\n",
       "      <td>Jhumpa Lahiri</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>Stories of the anxiety and transformation expe...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Knopf</td>\n",
       "      <td>(17,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>UNACCUSTOMED EARTH</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>(5b4aa4ead3089013507db19c,)</td>\n",
       "      <td>http://www.amazon.com/Netherland-A-Novel-Josep...</td>\n",
       "      <td>Joseph O'Neill</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>A Dutchman deserted by his wife after 9/11 fin...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Knopf Publishing Group</td>\n",
       "      <td>(18,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>NETHERLAND</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>(5b4aa4ead3089013507db19d,)</td>\n",
       "      <td>http://www.amazon.com/The-Appeal-John-Grisham/...</td>\n",
       "      <td>John Grisham</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td>Political and legal intrigue ensue when a Miss...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Doubleday Publishing</td>\n",
       "      <td>(19,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>THE APPEAL</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>(5b4aa4ead3089013507db19e,)</td>\n",
       "      <td>http://www.amazon.com/Indiana-Jones-Kingdom-Cr...</td>\n",
       "      <td>James Rollins</td>\n",
       "      <td>((1211587200000,),)</td>\n",
       "      <td></td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1212883200000,),)</td>\n",
       "      <td>Random House Publishing Group</td>\n",
       "      <td>(20,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>INDIANA JONES AND THE KINGDOM OF THE CRYSTAL S...</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>(5b4aa4ead3089013507db19f,)</td>\n",
       "      <td>http://www.amazon.com/Blood-Anita-Blake-Vampir...</td>\n",
       "      <td>Laurell K Hamilton</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>The vampire hunter Anita Blake is involved in ...</td>\n",
       "      <td>(25.95, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Berkley</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>BLOOD NOIR</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>(5b4aa4ead3089013507db1a0,)</td>\n",
       "      <td>http://www.amazon.com/The-Host-Novel-Stephenie...</td>\n",
       "      <td>Stephenie Meyer</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>Aliens have taken control of the minds and bod...</td>\n",
       "      <td>(25.99, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Little, Brown</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>THE HOST</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>(5b4aa4ead3089013507db1a1,)</td>\n",
       "      <td>http://www.amazon.com/Odd-Hours-Dean-Koontz/dp...</td>\n",
       "      <td>Dean R Koontz</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>Odd Thomas, who can communicate with the dead,...</td>\n",
       "      <td>(None, 27)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Bantam</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>ODD HOURS</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>(5b4aa4ead3089013507db1a2,)</td>\n",
       "      <td>http://www.amazon.com/Love-Youre-With-Emily-Gi...</td>\n",
       "      <td>Emily Giffin</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>A woman’s happy marriage is shaken when she en...</td>\n",
       "      <td>(24.95, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>St. Martin’s</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>LOVE THE ONE YOU'RE WITH</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>(5b4aa4ead3089013507db1a3,)</td>\n",
       "      <td>http://www.amazon.com/Sundays-at-Tiffanys-Jame...</td>\n",
       "      <td>James Patterson and Gabrielle Charbonnet</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>A woman finds an unexpected love.</td>\n",
       "      <td>(24.99, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Little, Brown</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>SUNDAYS AT TIFFANY’S</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>(5b4aa4ead3089013507db1a4,)</td>\n",
       "      <td>http://www.amazon.com/Chasing-Harry-Winston-A-...</td>\n",
       "      <td>Lauren Weisberger</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>Three glamorous friends, New York women nearin...</td>\n",
       "      <td>(25.95, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Simon &amp; Schuster</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>CHASING HARRY WINSTON</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>(5b4aa4ead3089013507db1a5,)</td>\n",
       "      <td>http://www.amazon.com/The-Front-Garano-Patrici...</td>\n",
       "      <td>Patricia Cornwell</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>A Massachusetts state investigator and his tea...</td>\n",
       "      <td>(22.95, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Putnam</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>THE FRONT</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>(5b4aa4ead3089013507db1a6,)</td>\n",
       "      <td>http://www.amazon.com/Devil-Care-James-Bond-No...</td>\n",
       "      <td>Sebastian Faulks</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>This continuation of the James Bond franchise ...</td>\n",
       "      <td>(24.95, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Doubleday</td>\n",
       "      <td>(8,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>DEVIL MAY CARE</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>(5b4aa4ead3089013507db1a7,)</td>\n",
       "      <td>http://www.amazon.com/Phantom-Prey-John-Sandfo...</td>\n",
       "      <td>John Sandford</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>The Minneapolis detective Lucas Davenport inve...</td>\n",
       "      <td>(26.95, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Putnam</td>\n",
       "      <td>(9,)</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>PHANTOM PREY</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>(5b4aa4ead3089013507db1a8,)</td>\n",
       "      <td>http://www.amazon.com/Snuff-Chuck-Palahniuk/dp...</td>\n",
       "      <td>Chuck Palahniuk</td>\n",
       "      <td>((1212192000000,),)</td>\n",
       "      <td>An aging porn queen aims to cap her career by ...</td>\n",
       "      <td>(24.95, None)</td>\n",
       "      <td>((1213488000000,),)</td>\n",
       "      <td>Doubleday</td>\n",
       "      <td>(10,)</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>SNUFF</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10165</th>\n",
       "      <td>(5b4aa4ead3089013507dd940,)</td>\n",
       "      <td>https://www.amazon.com/President-Missing-Novel...</td>\n",
       "      <td>Bill Clinton and James Patterson</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>President Jonathan Duncan takes on adversaries...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Little, Brown and Knopf</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>THE PRESIDENT IS MISSING</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10166</th>\n",
       "      <td>(5b4aa4ead3089013507dd941,)</td>\n",
       "      <td>https://www.amazon.com/All-We-Ever-Wanted-Nove...</td>\n",
       "      <td>Emily Giffin</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A scandal sends members of two Nashville famil...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Ballantine</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>ALL WE EVER WANTED</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10167</th>\n",
       "      <td>(5b4aa4ead3089013507dd942,)</td>\n",
       "      <td>https://www.amazon.com/Outsider-Novel-Stephen-...</td>\n",
       "      <td>Stephen King</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A detective investigates a seemingly wholesome...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Scribner</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>THE OUTSIDER</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10168</th>\n",
       "      <td>(5b4aa4ead3089013507dd943,)</td>\n",
       "      <td>https://www.amazon.com/Perfect-Couple-Elin-Hil...</td>\n",
       "      <td>Elin Hilderbrand</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A body is found in Nantucket Harbor hours befo...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Little, Brown</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>THE PERFECT COUPLE</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10169</th>\n",
       "      <td>(5b4aa4ead3089013507dd944,)</td>\n",
       "      <td>https://www.amazon.com/Shelter-Place-Nora-Robe...</td>\n",
       "      <td>Nora Roberts</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>Survivors of a mass shooting outside a mall in...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>St. Martin's</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>SHELTER IN PLACE</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10170</th>\n",
       "      <td>(5b4aa4ead3089013507dd945,)</td>\n",
       "      <td>https://www.amazon.com/Clancy-Line-Sight-Jack-...</td>\n",
       "      <td>Mike Maden</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>Jack Ryan Jr. risks his life to protect a woma...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Putnam</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>TOM CLANCY LINE OF SIGHT</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10171</th>\n",
       "      <td>(5b4aa4ead3089013507dd946,)</td>\n",
       "      <td>https://www.amazon.com/Death-Mrs-Westaway-Ruth...</td>\n",
       "      <td>Ruth Ware</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A tarot card reader mistakenly receives an inh...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Gallery/Scout</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>THE DEATH OF MRS. WESTAWAY</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10172</th>\n",
       "      <td>(5b4aa4ead3089013507dd947,)</td>\n",
       "      <td>https://www.amazon.com/There-novel-Tommy-Orang...</td>\n",
       "      <td>Tommy Orange</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A multigenerational story exploring the plight...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Knopf</td>\n",
       "      <td>(8,)</td>\n",
       "      <td>(8,)</td>\n",
       "      <td>THERE THERE</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10173</th>\n",
       "      <td>(5b4aa4ead3089013507dd948,)</td>\n",
       "      <td>https://www.amazon.com/When-Life-Gives-You-Lul...</td>\n",
       "      <td>Lauren Weisberger</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>Emily Charlton plans a comeback from the suburbs.</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Simon &amp; Schuster</td>\n",
       "      <td>(9,)</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>WHEN LIFE GIVES YOU LULULEMONS</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10174</th>\n",
       "      <td>(5b4aa4ead3089013507dd949,)</td>\n",
       "      <td>https://www.amazon.com/Before-We-Were-Yours-No...</td>\n",
       "      <td>Lisa Wingate</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A South Carolina lawyer learns about the quest...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Ballantine</td>\n",
       "      <td>(10,)</td>\n",
       "      <td>(9,)</td>\n",
       "      <td>BEFORE WE WERE YOURS</td>\n",
       "      <td>(41,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10175</th>\n",
       "      <td>(5b4aa4ead3089013507dd94a,)</td>\n",
       "      <td>https://www.amazon.com/Something-Water-Novel-C...</td>\n",
       "      <td>Catherine Steadman</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A documentary filmmaker and an investment bank...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Ballantine</td>\n",
       "      <td>(11,)</td>\n",
       "      <td>(11,)</td>\n",
       "      <td>SOMETHING IN THE WATER</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10176</th>\n",
       "      <td>(5b4aa4ead3089013507dd94b,)</td>\n",
       "      <td>https://www.amazon.com/Little-Fires-Everywhere...</td>\n",
       "      <td>Celeste Ng</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>An artist upends a quiet town outside Cleveland.</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Penguin Press</td>\n",
       "      <td>(12,)</td>\n",
       "      <td>(14,)</td>\n",
       "      <td>LITTLE FIRES EVERYWHERE</td>\n",
       "      <td>(40,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10177</th>\n",
       "      <td>(5b4aa4ead3089013507dd94c,)</td>\n",
       "      <td>https://www.amazon.com/Fallen-Memory-Man-David...</td>\n",
       "      <td>David Baldacci</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>Amos Decker puts his talents toward solving a ...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Grand Central</td>\n",
       "      <td>(13,)</td>\n",
       "      <td>(10,)</td>\n",
       "      <td>THE FALLEN</td>\n",
       "      <td>(11,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10178</th>\n",
       "      <td>(5b4aa4ead3089013507dd94d,)</td>\n",
       "      <td>https://www.amazon.com/Woman-Window-Novel-J-Fi...</td>\n",
       "      <td>AJ Finn</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A recluse who drinks heavily and takes prescri...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>Morrow</td>\n",
       "      <td>(14,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>THE WOMAN IN THE WINDOW</td>\n",
       "      <td>(23,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10179</th>\n",
       "      <td>(5b4aa4ead3089013507dd94e,)</td>\n",
       "      <td>https://www.amazon.com/Great-Alone-Novel-Krist...</td>\n",
       "      <td>Kristin Hannah</td>\n",
       "      <td>((1530316800000,),)</td>\n",
       "      <td>A former prisoner of war moves his family to A...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1531612800000,),)</td>\n",
       "      <td>St. Martin's</td>\n",
       "      <td>(15,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>THE GREAT ALONE</td>\n",
       "      <td>(18,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10180</th>\n",
       "      <td>(5b4aa4ead3089013507dd94f,)</td>\n",
       "      <td>https://www.amazon.com/President-Missing-Novel...</td>\n",
       "      <td>Bill Clinton and James Patterson</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>President Jonathan Duncan takes on adversaries...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Little, Brown and Knopf</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>THE PRESIDENT IS MISSING</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10181</th>\n",
       "      <td>(5b4aa4ead3089013507dd950,)</td>\n",
       "      <td>https://www.amazon.com/Spymaster-Thriller-Scot...</td>\n",
       "      <td>Brad Thor</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>The 18th book in the Scot Harvath series. As a...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Emily Bestler/Atria</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>SPYMASTER</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10182</th>\n",
       "      <td>(5b4aa4ead3089013507dd951,)</td>\n",
       "      <td>https://www.amazon.com/Perfect-Couple-Elin-Hil...</td>\n",
       "      <td>Elin Hilderbrand</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A body is found in Nantucket Harbor hours befo...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Little, Brown</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>THE PERFECT COUPLE</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10183</th>\n",
       "      <td>(5b4aa4ead3089013507dd952,)</td>\n",
       "      <td>https://www.amazon.com/Outsider-Novel-Stephen-...</td>\n",
       "      <td>Stephen King</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A detective investigates a seemingly wholesome...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Scribner</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>(3,)</td>\n",
       "      <td>THE OUTSIDER</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10184</th>\n",
       "      <td>(5b4aa4ead3089013507dd953,)</td>\n",
       "      <td>https://www.amazon.com/All-We-Ever-Wanted-Nove...</td>\n",
       "      <td>Emily Giffin</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A scandal sends members of two Nashville famil...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Ballantine</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>ALL WE EVER WANTED</td>\n",
       "      <td>(2,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10185</th>\n",
       "      <td>(5b4aa4ead3089013507dd954,)</td>\n",
       "      <td>https://www.amazon.com/There-novel-Tommy-Orang...</td>\n",
       "      <td>Tommy Orange</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A multigenerational story exploring the plight...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Knopf</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>(8,)</td>\n",
       "      <td>THERE THERE</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10186</th>\n",
       "      <td>(5b4aa4ead3089013507dd955,)</td>\n",
       "      <td>https://www.amazon.com/Death-Mrs-Westaway-Ruth...</td>\n",
       "      <td>Ruth Ware</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A tarot card reader mistakenly receives an inh...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Gallery/Scout</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>(7,)</td>\n",
       "      <td>THE DEATH OF MRS. WESTAWAY</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10187</th>\n",
       "      <td>(5b4aa4ead3089013507dd956,)</td>\n",
       "      <td>https://www.amazon.com/When-Life-Gives-You-Lul...</td>\n",
       "      <td>Lauren Weisberger</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>Emily Charlton plans a comeback from the suburbs.</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Simon &amp; Schuster</td>\n",
       "      <td>(8,)</td>\n",
       "      <td>(9,)</td>\n",
       "      <td>WHEN LIFE GIVES YOU LULULEMONS</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10188</th>\n",
       "      <td>(5b4aa4ead3089013507dd957,)</td>\n",
       "      <td>https://www.amazon.com/Fallen-Memory-Man-David...</td>\n",
       "      <td>David Baldacci</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>Amos Decker puts his talents toward solving a ...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Grand Central</td>\n",
       "      <td>(9,)</td>\n",
       "      <td>(13,)</td>\n",
       "      <td>THE FALLEN</td>\n",
       "      <td>(12,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10189</th>\n",
       "      <td>(5b4aa4ead3089013507dd958,)</td>\n",
       "      <td>https://www.amazon.com/Before-We-Were-Yours-No...</td>\n",
       "      <td>Lisa Wingate</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A South Carolina lawyer learns about the quest...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Ballantine</td>\n",
       "      <td>(10,)</td>\n",
       "      <td>(10,)</td>\n",
       "      <td>BEFORE WE WERE YOURS</td>\n",
       "      <td>(42,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10190</th>\n",
       "      <td>(5b4aa4ead3089013507dd959,)</td>\n",
       "      <td>https://www.amazon.com/Clancy-Line-Sight-Jack-...</td>\n",
       "      <td>Mike Maden</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>Jack Ryan Jr. risks his life to protect a woma...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Putnam</td>\n",
       "      <td>(11,)</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>TOM CLANCY LINE OF SIGHT</td>\n",
       "      <td>(4,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10191</th>\n",
       "      <td>(5b4aa4ead3089013507dd95a,)</td>\n",
       "      <td>https://www.amazon.com/Something-Water-Novel-C...</td>\n",
       "      <td>Catherine Steadman</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A documentary filmmaker and an investment bank...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Ballantine</td>\n",
       "      <td>(12,)</td>\n",
       "      <td>(11,)</td>\n",
       "      <td>SOMETHING IN THE WATER</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10192</th>\n",
       "      <td>(5b4aa4ead3089013507dd95b,)</td>\n",
       "      <td>https://www.amazon.com/Little-Fires-Everywhere...</td>\n",
       "      <td>Celeste Ng</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>An artist upends a quiet town outside Cleveland.</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Penguin Press</td>\n",
       "      <td>(13,)</td>\n",
       "      <td>(12,)</td>\n",
       "      <td>LITTLE FIRES EVERYWHERE</td>\n",
       "      <td>(41,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10193</th>\n",
       "      <td>(5b4aa4ead3089013507dd95c,)</td>\n",
       "      <td>https://www.amazon.com/Shelter-Place-Nora-Robe...</td>\n",
       "      <td>Nora Roberts</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>Survivors of a mass shooting outside a mall in...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>St. Martin's</td>\n",
       "      <td>(14,)</td>\n",
       "      <td>(5,)</td>\n",
       "      <td>SHELTER IN PLACE</td>\n",
       "      <td>(6,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10194</th>\n",
       "      <td>(5b4aa4ead3089013507dd95d,)</td>\n",
       "      <td>https://www.amazon.com/Last-Time-Lied-Novel/dp...</td>\n",
       "      <td>Riley Sager</td>\n",
       "      <td>((1530921600000,),)</td>\n",
       "      <td>A painter is in danger when she returns to the...</td>\n",
       "      <td>(None, 0)</td>\n",
       "      <td>((1532217600000,),)</td>\n",
       "      <td>Dutton</td>\n",
       "      <td>(15,)</td>\n",
       "      <td>(0,)</td>\n",
       "      <td>THE LAST TIME I LIED</td>\n",
       "      <td>(1,)</td>\n",
       "      <td>This is a new column</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10195 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               _id  \\\n",
       "0      (5b4aa4ead3089013507db18b,)   \n",
       "1      (5b4aa4ead3089013507db18c,)   \n",
       "2      (5b4aa4ead3089013507db18d,)   \n",
       "3      (5b4aa4ead3089013507db18e,)   \n",
       "4      (5b4aa4ead3089013507db18f,)   \n",
       "5      (5b4aa4ead3089013507db190,)   \n",
       "6      (5b4aa4ead3089013507db191,)   \n",
       "7      (5b4aa4ead3089013507db192,)   \n",
       "8      (5b4aa4ead3089013507db193,)   \n",
       "9      (5b4aa4ead3089013507db194,)   \n",
       "10     (5b4aa4ead3089013507db195,)   \n",
       "11     (5b4aa4ead3089013507db196,)   \n",
       "12     (5b4aa4ead3089013507db197,)   \n",
       "13     (5b4aa4ead3089013507db198,)   \n",
       "14     (5b4aa4ead3089013507db199,)   \n",
       "15     (5b4aa4ead3089013507db19a,)   \n",
       "16     (5b4aa4ead3089013507db19b,)   \n",
       "17     (5b4aa4ead3089013507db19c,)   \n",
       "18     (5b4aa4ead3089013507db19d,)   \n",
       "19     (5b4aa4ead3089013507db19e,)   \n",
       "20     (5b4aa4ead3089013507db19f,)   \n",
       "21     (5b4aa4ead3089013507db1a0,)   \n",
       "22     (5b4aa4ead3089013507db1a1,)   \n",
       "23     (5b4aa4ead3089013507db1a2,)   \n",
       "24     (5b4aa4ead3089013507db1a3,)   \n",
       "25     (5b4aa4ead3089013507db1a4,)   \n",
       "26     (5b4aa4ead3089013507db1a5,)   \n",
       "27     (5b4aa4ead3089013507db1a6,)   \n",
       "28     (5b4aa4ead3089013507db1a7,)   \n",
       "29     (5b4aa4ead3089013507db1a8,)   \n",
       "...                            ...   \n",
       "10165  (5b4aa4ead3089013507dd940,)   \n",
       "10166  (5b4aa4ead3089013507dd941,)   \n",
       "10167  (5b4aa4ead3089013507dd942,)   \n",
       "10168  (5b4aa4ead3089013507dd943,)   \n",
       "10169  (5b4aa4ead3089013507dd944,)   \n",
       "10170  (5b4aa4ead3089013507dd945,)   \n",
       "10171  (5b4aa4ead3089013507dd946,)   \n",
       "10172  (5b4aa4ead3089013507dd947,)   \n",
       "10173  (5b4aa4ead3089013507dd948,)   \n",
       "10174  (5b4aa4ead3089013507dd949,)   \n",
       "10175  (5b4aa4ead3089013507dd94a,)   \n",
       "10176  (5b4aa4ead3089013507dd94b,)   \n",
       "10177  (5b4aa4ead3089013507dd94c,)   \n",
       "10178  (5b4aa4ead3089013507dd94d,)   \n",
       "10179  (5b4aa4ead3089013507dd94e,)   \n",
       "10180  (5b4aa4ead3089013507dd94f,)   \n",
       "10181  (5b4aa4ead3089013507dd950,)   \n",
       "10182  (5b4aa4ead3089013507dd951,)   \n",
       "10183  (5b4aa4ead3089013507dd952,)   \n",
       "10184  (5b4aa4ead3089013507dd953,)   \n",
       "10185  (5b4aa4ead3089013507dd954,)   \n",
       "10186  (5b4aa4ead3089013507dd955,)   \n",
       "10187  (5b4aa4ead3089013507dd956,)   \n",
       "10188  (5b4aa4ead3089013507dd957,)   \n",
       "10189  (5b4aa4ead3089013507dd958,)   \n",
       "10190  (5b4aa4ead3089013507dd959,)   \n",
       "10191  (5b4aa4ead3089013507dd95a,)   \n",
       "10192  (5b4aa4ead3089013507dd95b,)   \n",
       "10193  (5b4aa4ead3089013507dd95c,)   \n",
       "10194  (5b4aa4ead3089013507dd95d,)   \n",
       "\n",
       "                                                     URL  \\\n",
       "0      http://www.amazon.com/Odd-Hours-Dean-Koontz/dp...   \n",
       "1      http://www.amazon.com/The-Host-Novel-Stephenie...   \n",
       "2      http://www.amazon.com/Love-Youre-With-Emily-Gi...   \n",
       "3      http://www.amazon.com/The-Front-Garano-Patrici...   \n",
       "4      http://www.amazon.com/Snuff-Chuck-Palahniuk/dp...   \n",
       "5      http://www.amazon.com/Sundays-at-Tiffanys-Jame...   \n",
       "6      http://www.amazon.com/Phantom-Prey-John-Sandfo...   \n",
       "7      http://www.amazon.com/From-Worse-Southern-Vamp...   \n",
       "8      http://www.amazon.com/Where-Are-You-Now-Novel/...   \n",
       "9      http://www.amazon.com/The-Whole-Truth-David-Ba...   \n",
       "10     http://www.amazon.com/Careless-Red-Novel-Eliza...   \n",
       "11     http://www.amazon.com/Unaccustomed-Earth-Jhump...   \n",
       "12     http://www.amazon.com/The-Racing-Rain-Garth-St...   \n",
       "13     http://www.amazon.com/Twenty-Wishes-Blossom-St...   \n",
       "14     http://www.amazon.com/Hold-Tight-Harlan-Coben/...   \n",
       "15     http://www.amazon.com/Executive-Privilege-Nove...   \n",
       "16     http://www.amazon.com/Unaccustomed-Vintage-Con...   \n",
       "17     http://www.amazon.com/Netherland-A-Novel-Josep...   \n",
       "18     http://www.amazon.com/The-Appeal-John-Grisham/...   \n",
       "19     http://www.amazon.com/Indiana-Jones-Kingdom-Cr...   \n",
       "20     http://www.amazon.com/Blood-Anita-Blake-Vampir...   \n",
       "21     http://www.amazon.com/The-Host-Novel-Stephenie...   \n",
       "22     http://www.amazon.com/Odd-Hours-Dean-Koontz/dp...   \n",
       "23     http://www.amazon.com/Love-Youre-With-Emily-Gi...   \n",
       "24     http://www.amazon.com/Sundays-at-Tiffanys-Jame...   \n",
       "25     http://www.amazon.com/Chasing-Harry-Winston-A-...   \n",
       "26     http://www.amazon.com/The-Front-Garano-Patrici...   \n",
       "27     http://www.amazon.com/Devil-Care-James-Bond-No...   \n",
       "28     http://www.amazon.com/Phantom-Prey-John-Sandfo...   \n",
       "29     http://www.amazon.com/Snuff-Chuck-Palahniuk/dp...   \n",
       "...                                                  ...   \n",
       "10165  https://www.amazon.com/President-Missing-Novel...   \n",
       "10166  https://www.amazon.com/All-We-Ever-Wanted-Nove...   \n",
       "10167  https://www.amazon.com/Outsider-Novel-Stephen-...   \n",
       "10168  https://www.amazon.com/Perfect-Couple-Elin-Hil...   \n",
       "10169  https://www.amazon.com/Shelter-Place-Nora-Robe...   \n",
       "10170  https://www.amazon.com/Clancy-Line-Sight-Jack-...   \n",
       "10171  https://www.amazon.com/Death-Mrs-Westaway-Ruth...   \n",
       "10172  https://www.amazon.com/There-novel-Tommy-Orang...   \n",
       "10173  https://www.amazon.com/When-Life-Gives-You-Lul...   \n",
       "10174  https://www.amazon.com/Before-We-Were-Yours-No...   \n",
       "10175  https://www.amazon.com/Something-Water-Novel-C...   \n",
       "10176  https://www.amazon.com/Little-Fires-Everywhere...   \n",
       "10177  https://www.amazon.com/Fallen-Memory-Man-David...   \n",
       "10178  https://www.amazon.com/Woman-Window-Novel-J-Fi...   \n",
       "10179  https://www.amazon.com/Great-Alone-Novel-Krist...   \n",
       "10180  https://www.amazon.com/President-Missing-Novel...   \n",
       "10181  https://www.amazon.com/Spymaster-Thriller-Scot...   \n",
       "10182  https://www.amazon.com/Perfect-Couple-Elin-Hil...   \n",
       "10183  https://www.amazon.com/Outsider-Novel-Stephen-...   \n",
       "10184  https://www.amazon.com/All-We-Ever-Wanted-Nove...   \n",
       "10185  https://www.amazon.com/There-novel-Tommy-Orang...   \n",
       "10186  https://www.amazon.com/Death-Mrs-Westaway-Ruth...   \n",
       "10187  https://www.amazon.com/When-Life-Gives-You-Lul...   \n",
       "10188  https://www.amazon.com/Fallen-Memory-Man-David...   \n",
       "10189  https://www.amazon.com/Before-We-Were-Yours-No...   \n",
       "10190  https://www.amazon.com/Clancy-Line-Sight-Jack-...   \n",
       "10191  https://www.amazon.com/Something-Water-Novel-C...   \n",
       "10192  https://www.amazon.com/Little-Fires-Everywhere...   \n",
       "10193  https://www.amazon.com/Shelter-Place-Nora-Robe...   \n",
       "10194  https://www.amazon.com/Last-Time-Lied-Novel/dp...   \n",
       "\n",
       "                                         author     bestsellers_date  \\\n",
       "0                                 Dean R Koontz  ((1211587200000,),)   \n",
       "1                               Stephenie Meyer  ((1211587200000,),)   \n",
       "2                                  Emily Giffin  ((1211587200000,),)   \n",
       "3                             Patricia Cornwell  ((1211587200000,),)   \n",
       "4                               Chuck Palahniuk  ((1211587200000,),)   \n",
       "5      James Patterson and Gabrielle Charbonnet  ((1211587200000,),)   \n",
       "6                                 John Sandford  ((1211587200000,),)   \n",
       "7                                 Jimmy Buffett  ((1211587200000,),)   \n",
       "8                              Elizabeth George  ((1211587200000,),)   \n",
       "9                                David Baldacci  ((1211587200000,),)   \n",
       "10                                 Troy Denning  ((1211587200000,),)   \n",
       "11                                   James Frey  ((1211587200000,),)   \n",
       "12                                  Garth Stein  ((1211587200000,),)   \n",
       "13                              Debbie Macomber  ((1211587200000,),)   \n",
       "14                                  Jeff Shaara  ((1211587200000,),)   \n",
       "15                             Phillip Margolin  ((1211587200000,),)   \n",
       "16                                Jhumpa Lahiri  ((1211587200000,),)   \n",
       "17                               Joseph O'Neill  ((1211587200000,),)   \n",
       "18                                 John Grisham  ((1211587200000,),)   \n",
       "19                                James Rollins  ((1211587200000,),)   \n",
       "20                           Laurell K Hamilton  ((1212192000000,),)   \n",
       "21                              Stephenie Meyer  ((1212192000000,),)   \n",
       "22                                Dean R Koontz  ((1212192000000,),)   \n",
       "23                                 Emily Giffin  ((1212192000000,),)   \n",
       "24     James Patterson and Gabrielle Charbonnet  ((1212192000000,),)   \n",
       "25                            Lauren Weisberger  ((1212192000000,),)   \n",
       "26                            Patricia Cornwell  ((1212192000000,),)   \n",
       "27                             Sebastian Faulks  ((1212192000000,),)   \n",
       "28                                John Sandford  ((1212192000000,),)   \n",
       "29                              Chuck Palahniuk  ((1212192000000,),)   \n",
       "...                                         ...                  ...   \n",
       "10165          Bill Clinton and James Patterson  ((1530316800000,),)   \n",
       "10166                              Emily Giffin  ((1530316800000,),)   \n",
       "10167                              Stephen King  ((1530316800000,),)   \n",
       "10168                          Elin Hilderbrand  ((1530316800000,),)   \n",
       "10169                              Nora Roberts  ((1530316800000,),)   \n",
       "10170                                Mike Maden  ((1530316800000,),)   \n",
       "10171                                 Ruth Ware  ((1530316800000,),)   \n",
       "10172                              Tommy Orange  ((1530316800000,),)   \n",
       "10173                         Lauren Weisberger  ((1530316800000,),)   \n",
       "10174                              Lisa Wingate  ((1530316800000,),)   \n",
       "10175                        Catherine Steadman  ((1530316800000,),)   \n",
       "10176                                Celeste Ng  ((1530316800000,),)   \n",
       "10177                            David Baldacci  ((1530316800000,),)   \n",
       "10178                                   AJ Finn  ((1530316800000,),)   \n",
       "10179                            Kristin Hannah  ((1530316800000,),)   \n",
       "10180          Bill Clinton and James Patterson  ((1530921600000,),)   \n",
       "10181                                 Brad Thor  ((1530921600000,),)   \n",
       "10182                          Elin Hilderbrand  ((1530921600000,),)   \n",
       "10183                              Stephen King  ((1530921600000,),)   \n",
       "10184                              Emily Giffin  ((1530921600000,),)   \n",
       "10185                              Tommy Orange  ((1530921600000,),)   \n",
       "10186                                 Ruth Ware  ((1530921600000,),)   \n",
       "10187                         Lauren Weisberger  ((1530921600000,),)   \n",
       "10188                            David Baldacci  ((1530921600000,),)   \n",
       "10189                              Lisa Wingate  ((1530921600000,),)   \n",
       "10190                                Mike Maden  ((1530921600000,),)   \n",
       "10191                        Catherine Steadman  ((1530921600000,),)   \n",
       "10192                                Celeste Ng  ((1530921600000,),)   \n",
       "10193                              Nora Roberts  ((1530921600000,),)   \n",
       "10194                               Riley Sager  ((1530921600000,),)   \n",
       "\n",
       "                                             description          price  \\\n",
       "0      Odd Thomas, who can communicate with the dead,...     (None, 27)   \n",
       "1      Aliens have taken control of the minds and bod...  (25.99, None)   \n",
       "2      A woman's happy marriage is shaken when she en...  (24.95, None)   \n",
       "3      A Massachusetts state investigator and his tea...  (22.95, None)   \n",
       "4      An aging porn queens aims to cap her career by...  (24.95, None)   \n",
       "5                       A woman finds an unexpected love  (24.99, None)   \n",
       "6      The Minneapolis detective Lucas Davenport inve...  (26.95, None)   \n",
       "7      A Southern family tries to hide its pet pig at...  (21.99, None)   \n",
       "8      In Cornwall, trying to recover from his wife's...  (27.95, None)   \n",
       "9      An intelligence agent and a journalist team up...  (26.99, None)   \n",
       "10     The New Jedi order battles Darth Caedus, Darth...     (None, 27)   \n",
       "11     A novel, set in Los Angeles, by the author of ...  (26.95, None)   \n",
       "12     A Lab-terrier mix with great insight into the ...  (23.95, None)   \n",
       "13     A widow who owns a bookstore on Blossom Street...  (24.95, None)   \n",
       "14     A novel about the Allied invasion in France in...     (None, 28)   \n",
       "15                                                            (None, 0)   \n",
       "16     Stories of the anxiety and transformation expe...      (None, 0)   \n",
       "17     A Dutchman deserted by his wife after 9/11 fin...      (None, 0)   \n",
       "18     Political and legal intrigue ensue when a Miss...      (None, 0)   \n",
       "19                                                            (None, 0)   \n",
       "20     The vampire hunter Anita Blake is involved in ...  (25.95, None)   \n",
       "21     Aliens have taken control of the minds and bod...  (25.99, None)   \n",
       "22     Odd Thomas, who can communicate with the dead,...     (None, 27)   \n",
       "23     A woman’s happy marriage is shaken when she en...  (24.95, None)   \n",
       "24                     A woman finds an unexpected love.  (24.99, None)   \n",
       "25     Three glamorous friends, New York women nearin...  (25.95, None)   \n",
       "26     A Massachusetts state investigator and his tea...  (22.95, None)   \n",
       "27     This continuation of the James Bond franchise ...  (24.95, None)   \n",
       "28     The Minneapolis detective Lucas Davenport inve...  (26.95, None)   \n",
       "29     An aging porn queen aims to cap her career by ...  (24.95, None)   \n",
       "...                                                  ...            ...   \n",
       "10165  President Jonathan Duncan takes on adversaries...      (None, 0)   \n",
       "10166  A scandal sends members of two Nashville famil...      (None, 0)   \n",
       "10167  A detective investigates a seemingly wholesome...      (None, 0)   \n",
       "10168  A body is found in Nantucket Harbor hours befo...      (None, 0)   \n",
       "10169  Survivors of a mass shooting outside a mall in...      (None, 0)   \n",
       "10170  Jack Ryan Jr. risks his life to protect a woma...      (None, 0)   \n",
       "10171  A tarot card reader mistakenly receives an inh...      (None, 0)   \n",
       "10172  A multigenerational story exploring the plight...      (None, 0)   \n",
       "10173  Emily Charlton plans a comeback from the suburbs.      (None, 0)   \n",
       "10174  A South Carolina lawyer learns about the quest...      (None, 0)   \n",
       "10175  A documentary filmmaker and an investment bank...      (None, 0)   \n",
       "10176   An artist upends a quiet town outside Cleveland.      (None, 0)   \n",
       "10177  Amos Decker puts his talents toward solving a ...      (None, 0)   \n",
       "10178  A recluse who drinks heavily and takes prescri...      (None, 0)   \n",
       "10179  A former prisoner of war moves his family to A...      (None, 0)   \n",
       "10180  President Jonathan Duncan takes on adversaries...      (None, 0)   \n",
       "10181  The 18th book in the Scot Harvath series. As a...      (None, 0)   \n",
       "10182  A body is found in Nantucket Harbor hours befo...      (None, 0)   \n",
       "10183  A detective investigates a seemingly wholesome...      (None, 0)   \n",
       "10184  A scandal sends members of two Nashville famil...      (None, 0)   \n",
       "10185  A multigenerational story exploring the plight...      (None, 0)   \n",
       "10186  A tarot card reader mistakenly receives an inh...      (None, 0)   \n",
       "10187  Emily Charlton plans a comeback from the suburbs.      (None, 0)   \n",
       "10188  Amos Decker puts his talents toward solving a ...      (None, 0)   \n",
       "10189  A South Carolina lawyer learns about the quest...      (None, 0)   \n",
       "10190  Jack Ryan Jr. risks his life to protect a woma...      (None, 0)   \n",
       "10191  A documentary filmmaker and an investment bank...      (None, 0)   \n",
       "10192   An artist upends a quiet town outside Cleveland.      (None, 0)   \n",
       "10193  Survivors of a mass shooting outside a mall in...      (None, 0)   \n",
       "10194  A painter is in danger when she returns to the...      (None, 0)   \n",
       "\n",
       "            published_date                      publisher   rank  \\\n",
       "0      ((1212883200000,),)                         Bantam   (1,)   \n",
       "1      ((1212883200000,),)                  Little, Brown   (2,)   \n",
       "2      ((1212883200000,),)                   St. Martin's   (3,)   \n",
       "3      ((1212883200000,),)                         Putnam   (4,)   \n",
       "4      ((1212883200000,),)                      Doubleday   (5,)   \n",
       "5      ((1212883200000,),)                  Little, Brown   (6,)   \n",
       "6      ((1212883200000,),)                         Putnam   (7,)   \n",
       "7      ((1212883200000,),)                  Little, Brown   (8,)   \n",
       "8      ((1212883200000,),)                         Harper   (9,)   \n",
       "9      ((1212883200000,),)                  Grand Central  (10,)   \n",
       "10     ((1212883200000,),)             Del Rey/Ballantine  (11,)   \n",
       "11     ((1212883200000,),)                         Harper  (12,)   \n",
       "12     ((1212883200000,),)                         Harper  (13,)   \n",
       "13     ((1212883200000,),)                           Mira  (14,)   \n",
       "14     ((1212883200000,),)                     Ballantine  (15,)   \n",
       "15     ((1212883200000,),)       HarperCollins Publishers  (16,)   \n",
       "16     ((1212883200000,),)                          Knopf  (17,)   \n",
       "17     ((1212883200000,),)         Knopf Publishing Group  (18,)   \n",
       "18     ((1212883200000,),)           Doubleday Publishing  (19,)   \n",
       "19     ((1212883200000,),)  Random House Publishing Group  (20,)   \n",
       "20     ((1213488000000,),)                        Berkley   (1,)   \n",
       "21     ((1213488000000,),)                  Little, Brown   (2,)   \n",
       "22     ((1213488000000,),)                         Bantam   (3,)   \n",
       "23     ((1213488000000,),)                   St. Martin’s   (4,)   \n",
       "24     ((1213488000000,),)                  Little, Brown   (5,)   \n",
       "25     ((1213488000000,),)               Simon & Schuster   (6,)   \n",
       "26     ((1213488000000,),)                         Putnam   (7,)   \n",
       "27     ((1213488000000,),)                      Doubleday   (8,)   \n",
       "28     ((1213488000000,),)                         Putnam   (9,)   \n",
       "29     ((1213488000000,),)                      Doubleday  (10,)   \n",
       "...                    ...                            ...    ...   \n",
       "10165  ((1531612800000,),)        Little, Brown and Knopf   (1,)   \n",
       "10166  ((1531612800000,),)                     Ballantine   (2,)   \n",
       "10167  ((1531612800000,),)                       Scribner   (3,)   \n",
       "10168  ((1531612800000,),)                  Little, Brown   (4,)   \n",
       "10169  ((1531612800000,),)                   St. Martin's   (5,)   \n",
       "10170  ((1531612800000,),)                         Putnam   (6,)   \n",
       "10171  ((1531612800000,),)                  Gallery/Scout   (7,)   \n",
       "10172  ((1531612800000,),)                          Knopf   (8,)   \n",
       "10173  ((1531612800000,),)               Simon & Schuster   (9,)   \n",
       "10174  ((1531612800000,),)                     Ballantine  (10,)   \n",
       "10175  ((1531612800000,),)                     Ballantine  (11,)   \n",
       "10176  ((1531612800000,),)                  Penguin Press  (12,)   \n",
       "10177  ((1531612800000,),)                  Grand Central  (13,)   \n",
       "10178  ((1531612800000,),)                         Morrow  (14,)   \n",
       "10179  ((1531612800000,),)                   St. Martin's  (15,)   \n",
       "10180  ((1532217600000,),)        Little, Brown and Knopf   (1,)   \n",
       "10181  ((1532217600000,),)            Emily Bestler/Atria   (2,)   \n",
       "10182  ((1532217600000,),)                  Little, Brown   (3,)   \n",
       "10183  ((1532217600000,),)                       Scribner   (4,)   \n",
       "10184  ((1532217600000,),)                     Ballantine   (5,)   \n",
       "10185  ((1532217600000,),)                          Knopf   (6,)   \n",
       "10186  ((1532217600000,),)                  Gallery/Scout   (7,)   \n",
       "10187  ((1532217600000,),)               Simon & Schuster   (8,)   \n",
       "10188  ((1532217600000,),)                  Grand Central   (9,)   \n",
       "10189  ((1532217600000,),)                     Ballantine  (10,)   \n",
       "10190  ((1532217600000,),)                         Putnam  (11,)   \n",
       "10191  ((1532217600000,),)                     Ballantine  (12,)   \n",
       "10192  ((1532217600000,),)                  Penguin Press  (13,)   \n",
       "10193  ((1532217600000,),)                   St. Martin's  (14,)   \n",
       "10194  ((1532217600000,),)                         Dutton  (15,)   \n",
       "\n",
       "      rank_last_week                                              title  \\\n",
       "0               (0,)                                          ODD HOURS   \n",
       "1               (1,)                                           THE HOST   \n",
       "2               (2,)                           LOVE THE ONE YOU'RE WITH   \n",
       "3               (0,)                                          THE FRONT   \n",
       "4               (0,)                                              SNUFF   \n",
       "5               (3,)                               SUNDAYS AT TIFFANY’S   \n",
       "6               (4,)                                       PHANTOM PREY   \n",
       "7               (6,)                                         SWINE NOT?   \n",
       "8               (8,)                                    CARELESS IN RED   \n",
       "9               (7,)                                    THE WHOLE TRUTH   \n",
       "10              (5,)                                         INVINCIBLE   \n",
       "11              (9,)                               BRIGHT SHINY MORNING   \n",
       "12              (0,)                      THE ART OF RACING IN THE RAIN   \n",
       "13             (10,)                                      TWENTY WISHES   \n",
       "14             (11,)                                     THE STEEL WAVE   \n",
       "15              (0,)                                EXECUTIVE PRIVILEGE   \n",
       "16              (0,)                                 UNACCUSTOMED EARTH   \n",
       "17              (0,)                                         NETHERLAND   \n",
       "18              (0,)                                         THE APPEAL   \n",
       "19              (0,)  INDIANA JONES AND THE KINGDOM OF THE CRYSTAL S...   \n",
       "20              (0,)                                         BLOOD NOIR   \n",
       "21              (2,)                                           THE HOST   \n",
       "22              (1,)                                          ODD HOURS   \n",
       "23              (3,)                           LOVE THE ONE YOU'RE WITH   \n",
       "24              (6,)                               SUNDAYS AT TIFFANY’S   \n",
       "25              (0,)                              CHASING HARRY WINSTON   \n",
       "26              (4,)                                          THE FRONT   \n",
       "27              (0,)                                     DEVIL MAY CARE   \n",
       "28              (7,)                                       PHANTOM PREY   \n",
       "29              (5,)                                              SNUFF   \n",
       "...              ...                                                ...   \n",
       "10165           (1,)                           THE PRESIDENT IS MISSING   \n",
       "10166           (0,)                                 ALL WE EVER WANTED   \n",
       "10167           (3,)                                       THE OUTSIDER   \n",
       "10168           (2,)                                 THE PERFECT COUPLE   \n",
       "10169           (5,)                                   SHELTER IN PLACE   \n",
       "10170           (4,)                           TOM CLANCY LINE OF SIGHT   \n",
       "10171           (6,)                         THE DEATH OF MRS. WESTAWAY   \n",
       "10172           (8,)                                        THERE THERE   \n",
       "10173           (7,)                     WHEN LIFE GIVES YOU LULULEMONS   \n",
       "10174           (9,)                               BEFORE WE WERE YOURS   \n",
       "10175          (11,)                             SOMETHING IN THE WATER   \n",
       "10176          (14,)                            LITTLE FIRES EVERYWHERE   \n",
       "10177          (10,)                                         THE FALLEN   \n",
       "10178           (0,)                            THE WOMAN IN THE WINDOW   \n",
       "10179           (0,)                                    THE GREAT ALONE   \n",
       "10180           (1,)                           THE PRESIDENT IS MISSING   \n",
       "10181           (0,)                                          SPYMASTER   \n",
       "10182           (4,)                                 THE PERFECT COUPLE   \n",
       "10183           (3,)                                       THE OUTSIDER   \n",
       "10184           (2,)                                 ALL WE EVER WANTED   \n",
       "10185           (8,)                                        THERE THERE   \n",
       "10186           (7,)                         THE DEATH OF MRS. WESTAWAY   \n",
       "10187           (9,)                     WHEN LIFE GIVES YOU LULULEMONS   \n",
       "10188          (13,)                                         THE FALLEN   \n",
       "10189          (10,)                               BEFORE WE WERE YOURS   \n",
       "10190           (6,)                           TOM CLANCY LINE OF SIGHT   \n",
       "10191          (11,)                             SOMETHING IN THE WATER   \n",
       "10192          (12,)                            LITTLE FIRES EVERYWHERE   \n",
       "10193           (5,)                                   SHELTER IN PLACE   \n",
       "10194           (0,)                               THE LAST TIME I LIED   \n",
       "\n",
       "      weeks_on_list            new_column  \n",
       "0              (1,)  This is a new column  \n",
       "1              (3,)  This is a new column  \n",
       "2              (2,)  This is a new column  \n",
       "3              (1,)  This is a new column  \n",
       "4              (1,)  This is a new column  \n",
       "5              (4,)  This is a new column  \n",
       "6              (3,)  This is a new column  \n",
       "7              (2,)  This is a new column  \n",
       "8              (3,)  This is a new column  \n",
       "9              (5,)  This is a new column  \n",
       "10             (2,)  This is a new column  \n",
       "11             (2,)  This is a new column  \n",
       "12             (1,)  This is a new column  \n",
       "13             (4,)  This is a new column  \n",
       "14             (2,)  This is a new column  \n",
       "15             (0,)  This is a new column  \n",
       "16             (0,)  This is a new column  \n",
       "17             (0,)  This is a new column  \n",
       "18             (0,)  This is a new column  \n",
       "19             (0,)  This is a new column  \n",
       "20             (1,)  This is a new column  \n",
       "21             (4,)  This is a new column  \n",
       "22             (2,)  This is a new column  \n",
       "23             (3,)  This is a new column  \n",
       "24             (5,)  This is a new column  \n",
       "25             (1,)  This is a new column  \n",
       "26             (2,)  This is a new column  \n",
       "27             (1,)  This is a new column  \n",
       "28             (4,)  This is a new column  \n",
       "29             (2,)  This is a new column  \n",
       "...             ...                   ...  \n",
       "10165          (4,)  This is a new column  \n",
       "10166          (1,)  This is a new column  \n",
       "10167          (6,)  This is a new column  \n",
       "10168          (2,)  This is a new column  \n",
       "10169          (5,)  This is a new column  \n",
       "10170          (3,)  This is a new column  \n",
       "10171          (5,)  This is a new column  \n",
       "10172          (4,)  This is a new column  \n",
       "10173          (4,)  This is a new column  \n",
       "10174         (41,)  This is a new column  \n",
       "10175          (4,)  This is a new column  \n",
       "10176         (40,)  This is a new column  \n",
       "10177         (11,)  This is a new column  \n",
       "10178         (23,)  This is a new column  \n",
       "10179         (18,)  This is a new column  \n",
       "10180          (5,)  This is a new column  \n",
       "10181          (1,)  This is a new column  \n",
       "10182          (3,)  This is a new column  \n",
       "10183          (7,)  This is a new column  \n",
       "10184          (2,)  This is a new column  \n",
       "10185          (5,)  This is a new column  \n",
       "10186          (6,)  This is a new column  \n",
       "10187          (5,)  This is a new column  \n",
       "10188         (12,)  This is a new column  \n",
       "10189         (42,)  This is a new column  \n",
       "10190          (4,)  This is a new column  \n",
       "10191          (5,)  This is a new column  \n",
       "10192         (41,)  This is a new column  \n",
       "10193          (6,)  This is a new column  \n",
       "10194          (1,)  This is a new column  \n",
       "\n",
       "[10195 rows x 13 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Obtaining contents of df as Pandas dataFrame\n",
    "dataframe.toPandas()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Write & Save Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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": 44,
   "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": [
    "# STOPPING SPARK SESSION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# End Spark Session \n",
    "sc.stop()"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}