Publisher.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "549db2b1",
   "metadata": {},
   "source": [
    "# What kind of book should we publish to get into the Amazon best sellers list?\n",
    "\n",
    "We are a new publisher looking to make our first published book a huge success. We focus on our first book because resources are limited and we can't afford to test the market by publishing multiple books and see what would sell the most. Another important detail is we're limited to publishing our e-books; hence, we won't be considering other medium such as physical books or audiobook.\n",
    "\n",
    "Our aim is somewhat modest and we are only looking to be included in the Amazon best sellers list. If we can be included in that list, we will be more likely to be included in other best sellers list such as the New York Times. \n",
    "\n",
    "In relation to the Amazon Book Best Sellers list, we want to answer the following questions:\n",
    "1. Which genre is the most popular?\n",
    "2. What is the average price of books?\n",
    "3. How many reviews does a book receive on average? Does the average number of reviews change significantly depending on the genre?\n",
    "4. How high is the rating a book receives on average? Does the average rating change significantly depending on the genre?\n",
    "5. Are there any other factors aside from genre that makes a book popular?\n",
    "\n",
    "We'll start by answering the above plus some incidental questions found along the way; we will the follow up by formulating one or two book profiles that could potentially become best sellers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07a2f089",
   "metadata": {},
   "source": [
    "## Loading, Cleaning and Exploring the Data\n",
    "\n",
    "### Load Data Set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c6559f7d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2269, 40)\n"
     ]
    },
    {
     "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>asin</th>\n",
       "      <th>ISBN10</th>\n",
       "      <th>answered_questions</th>\n",
       "      <th>availability</th>\n",
       "      <th>brand</th>\n",
       "      <th>currency</th>\n",
       "      <th>date_first_available</th>\n",
       "      <th>delivery</th>\n",
       "      <th>department</th>\n",
       "      <th>description</th>\n",
       "      <th>...</th>\n",
       "      <th>upc</th>\n",
       "      <th>url</th>\n",
       "      <th>video</th>\n",
       "      <th>video_count</th>\n",
       "      <th>categories</th>\n",
       "      <th>best_sellers_rank</th>\n",
       "      <th>buybox_seller</th>\n",
       "      <th>image</th>\n",
       "      <th>number_of_sellers</th>\n",
       "      <th>colors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0007350813</td>\n",
       "      <td>0007350813</td>\n",
       "      <td>0</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>Emily Brontë</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[\"FREE delivery Tuesday, December 28 if you sp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0007350813</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{\"category\":\"Books / Literature &amp; Fiction / H...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0007513763</td>\n",
       "      <td>9780007513765</td>\n",
       "      <td>0</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>Drew Daywalt</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[\"FREE delivery Tuesday, December 28 if you sp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0007513763</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>[{\"category\":\"Books / Children's Books / Liter...</td>\n",
       "      <td>VMG Books &amp; Media</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0008183988</td>\n",
       "      <td>0008183988</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bernard Cornwell</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[\"FREE delivery January 4 - 10 if you spend $2...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0008183988</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{\"category\":\"Books / Literature &amp; Fiction / H...</td>\n",
       "      <td>Reuseaworld</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0008305838</td>\n",
       "      <td>0008305838</td>\n",
       "      <td>0</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>David Walliams</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[\"FREE delivery Tuesday, December 28 if you sp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0008305838</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>[{\"category\":\"Books / Children's Books / Liter...</td>\n",
       "      <td>Bahamut Media</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0008375526</td>\n",
       "      <td>0008375526</td>\n",
       "      <td>0</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>Caroline Hirons</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[\"FREE delivery Tuesday, December 28\",\"Or fast...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0008375526</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Crafts, Hobbies &amp; Home\",\"Home Improv...</td>\n",
       "      <td>[{\"category\":\"Books / Health, Fitness &amp; Dietin...</td>\n",
       "      <td>KathrynAshleyGallery</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <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>2264</th>\n",
       "      <td>B07P5BPVGM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Jess Lourey</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/B07P5BPVGM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Mystery, Thriller &amp; Suspense\",\"Thril...</td>\n",
       "      <td>[{\"category\":\"Books / Mystery, Thriller &amp; Susp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2265</th>\n",
       "      <td>B07P5JBCFL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Heidi Murkoff (Author, Narrator), Meeghan Hola...</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/B07P5JBCFL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Health, Fitness &amp; Dieting\",\"Women's ...</td>\n",
       "      <td>[{\"category\":\"Books / Health, Fitness &amp; Dietin...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2266</th>\n",
       "      <td>B07NF7DFS2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Clea Shearer (Author, Narrator), Joanna Teplin...</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/B07NF7DFS2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Crafts, Hobbies &amp; Home\",\"Home Improv...</td>\n",
       "      <td>[{\"category\":\"Audible Books &amp; Originals / Home...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2267</th>\n",
       "      <td>B07P67N918</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lisa Jewell (Author), Tamaryn Payne (Narrator)...</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/B07P67N918</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Mystery, Thriller &amp; Suspense\",\"Thril...</td>\n",
       "      <td>[{\"category\":\"Audible Books &amp; Originals / Myst...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2268</th>\n",
       "      <td>B07PFY8K2N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Elton John (Author, Narrator), Taron Egerton (...</td>\n",
       "      <td>USD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/B07PFY8K2N</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>[\"Books\",\"Arts &amp; Photography\",\"Music\",\"Musical...</td>\n",
       "      <td>[{\"category\":\"Audible Books &amp; Originals / Arts...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2269 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            asin         ISBN10  answered_questions availability  \\\n",
       "0     0007350813     0007350813                   0    In Stock.   \n",
       "1     0007513763  9780007513765                   0    In Stock.   \n",
       "2     0008183988     0008183988                   0          NaN   \n",
       "3     0008305838     0008305838                   0    In Stock.   \n",
       "4     0008375526     0008375526                   0    In Stock.   \n",
       "...          ...            ...                 ...          ...   \n",
       "2264  B07P5BPVGM            NaN                   0          NaN   \n",
       "2265  B07P5JBCFL            NaN                   0          NaN   \n",
       "2266  B07NF7DFS2            NaN                   0          NaN   \n",
       "2267  B07P67N918            NaN                   0          NaN   \n",
       "2268  B07PFY8K2N            NaN                   0          NaN   \n",
       "\n",
       "                                                  brand currency  \\\n",
       "0                                          Emily Brontë      USD   \n",
       "1                                          Drew Daywalt      USD   \n",
       "2                                      Bernard Cornwell      USD   \n",
       "3                                        David Walliams      USD   \n",
       "4                                       Caroline Hirons      USD   \n",
       "...                                                 ...      ...   \n",
       "2264                                        Jess Lourey      USD   \n",
       "2265  Heidi Murkoff (Author, Narrator), Meeghan Hola...      USD   \n",
       "2266  Clea Shearer (Author, Narrator), Joanna Teplin...      USD   \n",
       "2267  Lisa Jewell (Author), Tamaryn Payne (Narrator)...      USD   \n",
       "2268  Elton John (Author, Narrator), Taron Egerton (...      USD   \n",
       "\n",
       "     date_first_available                                           delivery  \\\n",
       "0                     NaN  [\"FREE delivery Tuesday, December 28 if you sp...   \n",
       "1                     NaN  [\"FREE delivery Tuesday, December 28 if you sp...   \n",
       "2                     NaN  [\"FREE delivery January 4 - 10 if you spend $2...   \n",
       "3                     NaN  [\"FREE delivery Tuesday, December 28 if you sp...   \n",
       "4                     NaN  [\"FREE delivery Tuesday, December 28\",\"Or fast...   \n",
       "...                   ...                                                ...   \n",
       "2264                  NaN                                                 []   \n",
       "2265                  NaN                                                 []   \n",
       "2266                  NaN                                                 []   \n",
       "2267                  NaN                                                 []   \n",
       "2268                  NaN                                                 []   \n",
       "\n",
       "      department description  ...  upc                                   url  \\\n",
       "0            NaN         NaN  ...  NaN  https://www.amazon.com/dp/0007350813   \n",
       "1            NaN         NaN  ...  NaN  https://www.amazon.com/dp/0007513763   \n",
       "2            NaN         NaN  ...  NaN  https://www.amazon.com/dp/0008183988   \n",
       "3            NaN         NaN  ...  NaN  https://www.amazon.com/dp/0008305838   \n",
       "4            NaN         NaN  ...  NaN  https://www.amazon.com/dp/0008375526   \n",
       "...          ...         ...  ...  ...                                   ...   \n",
       "2264         NaN         NaN  ...  NaN  https://www.amazon.com/dp/B07P5BPVGM   \n",
       "2265         NaN         NaN  ...  NaN  https://www.amazon.com/dp/B07P5JBCFL   \n",
       "2266         NaN         NaN  ...  NaN  https://www.amazon.com/dp/B07NF7DFS2   \n",
       "2267         NaN         NaN  ...  NaN  https://www.amazon.com/dp/B07P67N918   \n",
       "2268         NaN         NaN  ...  NaN  https://www.amazon.com/dp/B07PFY8K2N   \n",
       "\n",
       "     video  video_count                                         categories  \\\n",
       "0      NaN            0   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "1      NaN            0  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "2      NaN            0   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "3      NaN            0  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "4      NaN            0  [\"Books\",\"Crafts, Hobbies & Home\",\"Home Improv...   \n",
       "...    ...          ...                                                ...   \n",
       "2264   NaN            0  [\"Books\",\"Mystery, Thriller & Suspense\",\"Thril...   \n",
       "2265   NaN            0  [\"Books\",\"Health, Fitness & Dieting\",\"Women's ...   \n",
       "2266   NaN            0  [\"Books\",\"Crafts, Hobbies & Home\",\"Home Improv...   \n",
       "2267   NaN            0  [\"Books\",\"Mystery, Thriller & Suspense\",\"Thril...   \n",
       "2268   NaN            0  [\"Books\",\"Arts & Photography\",\"Music\",\"Musical...   \n",
       "\n",
       "                                      best_sellers_rank         buybox_seller  \\\n",
       "0     [{\"category\":\"Books / Literature & Fiction / H...                   NaN   \n",
       "1     [{\"category\":\"Books / Children's Books / Liter...     VMG Books & Media   \n",
       "2     [{\"category\":\"Books / Literature & Fiction / H...           Reuseaworld   \n",
       "3     [{\"category\":\"Books / Children's Books / Liter...         Bahamut Media   \n",
       "4     [{\"category\":\"Books / Health, Fitness & Dietin...  KathrynAshleyGallery   \n",
       "...                                                 ...                   ...   \n",
       "2264  [{\"category\":\"Books / Mystery, Thriller & Susp...                   NaN   \n",
       "2265  [{\"category\":\"Books / Health, Fitness & Dietin...                   NaN   \n",
       "2266  [{\"category\":\"Audible Books & Originals / Home...                   NaN   \n",
       "2267  [{\"category\":\"Audible Books & Originals / Myst...                   NaN   \n",
       "2268  [{\"category\":\"Audible Books & Originals / Arts...                   NaN   \n",
       "\n",
       "      image number_of_sellers colors  \n",
       "0       NaN               NaN    NaN  \n",
       "1       NaN               NaN    NaN  \n",
       "2       NaN               NaN    NaN  \n",
       "3       NaN               NaN    NaN  \n",
       "4       NaN               NaN    NaN  \n",
       "...     ...               ...    ...  \n",
       "2264    NaN               NaN    NaN  \n",
       "2265    NaN               NaN    NaN  \n",
       "2266    NaN               NaN    NaN  \n",
       "2267    NaN               NaN    NaN  \n",
       "2268    NaN               NaN    NaN  \n",
       "\n",
       "[2269 rows x 40 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "amazon_books = pd.read_csv(\"Amazon_popular_books_dataset.csv\")\n",
    "print(amazon_books.shape)\n",
    "\n",
    "# Display both head and tail of data frame\n",
    "amazon_books"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d50c9b7",
   "metadata": {},
   "source": [
    "2269 rows of the data frame matched the data set [documentation](https://github.com/luminati-io/Amazon-popular-books-dataset#amazon-popular-books-dataset):\n",
    "\n",
    "> This Amazon dataset contains 2269 best-selling books.\n",
    "\n",
    "On the other hand, the documentation does not mention the number of columns. We should investigate the column names to make sure nothing odd happened when we loaded the data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "145f9da4",
   "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>columns</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>asin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ISBN10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>answered_questions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>availability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>brand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>currency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>date_first_available</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>delivery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>department</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>description</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>discount</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>domain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>features</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>final_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>format</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>image_url</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>images_count</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>initial_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>item_weight</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>manufacturer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>model_number</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>plus_content</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>product_dimensions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>reviews_count</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>root_bs_rank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>seller_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>seller_name</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>timestamp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>title</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>upc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>url</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>video_count</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>categories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>best_sellers_rank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>buybox_seller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>image</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>number_of_sellers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>colors</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 columns\n",
       "0                   asin\n",
       "1                 ISBN10\n",
       "2     answered_questions\n",
       "3           availability\n",
       "4                  brand\n",
       "5               currency\n",
       "6   date_first_available\n",
       "7               delivery\n",
       "8             department\n",
       "9            description\n",
       "10              discount\n",
       "11                domain\n",
       "12              features\n",
       "13           final_price\n",
       "14                format\n",
       "15             image_url\n",
       "16          images_count\n",
       "17         initial_price\n",
       "18           item_weight\n",
       "19          manufacturer\n",
       "20          model_number\n",
       "21          plus_content\n",
       "22    product_dimensions\n",
       "23                rating\n",
       "24         reviews_count\n",
       "25          root_bs_rank\n",
       "26             seller_id\n",
       "27           seller_name\n",
       "28             timestamp\n",
       "29                 title\n",
       "30                   upc\n",
       "31                   url\n",
       "32                 video\n",
       "33           video_count\n",
       "34            categories\n",
       "35     best_sellers_rank\n",
       "36         buybox_seller\n",
       "37                 image\n",
       "38     number_of_sellers\n",
       "39                colors"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show all columns\n",
    "pd.DataFrame({'columns': amazon_books.columns})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc70f435",
   "metadata": {},
   "source": [
    "### Column Selection\n",
    "\n",
    "Observing the above results, the column names seems reasonable. Now, we'll need to select the relevant columns because even if keeping irrelevant columns don't have any impact in the results of our analysis, it does clutter the display and also slows down data processing and calculations. \n",
    "\n",
    "The documentation mentions that the following are the key columns:\n",
    "\n",
    "> Key data points included in this free dataset:\n",
    "> - ASIN\n",
    "> - ISBN10\n",
    "> - Categories\n",
    "> - Reviews count\n",
    "> - Avg. rating\n",
    "> - Number of sellers\n",
    "> - URL\n",
    "> - Image\n",
    "> - Final price\n",
    "> - Title\n",
    "> - Description\n",
    "> - Availability\n",
    "\n",
    "The above columns should be enough for the purpose of this project thus we will restrict our data frame to only those columns for now; we can always add the removed columns later if there's a need for them.\n",
    "\n",
    "| Full Name         | Column Name       |\n",
    "|:------------------|:------------------|\n",
    "| ASIN              | asin              |\n",
    "| ISBN10            | ISBN10            |\n",
    "| Categories        | categories        |\n",
    "| Reviews count     | reviews_count     |\n",
    "| Average rating    | rating            |\n",
    "| Number of sellers | number_of_sellers |\n",
    "| URL               | url               |\n",
    "| Image             | image             |\n",
    "| Final price       | final_price       |\n",
    "| Title             | title             |\n",
    "| Description       | description       |\n",
    "| Availability      | availability      |\n",
    "| Format            | format            |\n",
    "| Author            | brand             |\n",
    "\n",
    "We've also added the columns for Format and Author (which is called `brand` - odd, but makes sense because sometimes the book is authored collectively as an institution or publisher; and yes, authors are themselves brands in a way)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2df75add",
   "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>asin</th>\n",
       "      <th>ISBN10</th>\n",
       "      <th>categories</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>rating</th>\n",
       "      <th>number_of_sellers</th>\n",
       "      <th>url</th>\n",
       "      <th>image</th>\n",
       "      <th>final_price</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "      <th>availability</th>\n",
       "      <th>format</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0007350813</td>\n",
       "      <td>0007350813</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>13451</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0007350813</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.99</td>\n",
       "      <td>Wuthering Heights (Collins Classics)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$0.99\",\"url\":\"/Wuth...</td>\n",
       "      <td>Emily Brontë</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0007513763</td>\n",
       "      <td>9780007513765</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>16628</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0007513763</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.08</td>\n",
       "      <td>THE DAYS THE CRAYONS QUIT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$10.99\",\"url\":\"/Day...</td>\n",
       "      <td>Drew Daywalt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0008183988</td>\n",
       "      <td>0008183988</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>11275</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0008183988</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>War Lord: Book 13 (The Last Kingdom Series)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$11.99\",\"url\":\"/War...</td>\n",
       "      <td>Bernard Cornwell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0008305838</td>\n",
       "      <td>0008305838</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>15520</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0008305838</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.43</td>\n",
       "      <td>Code Name Bananas: The hilarious and epic new ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>[{\"name\":\"Paperback\",\"price\":\"$24.78\",\"url\":\"/...</td>\n",
       "      <td>David Walliams</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0008375526</td>\n",
       "      <td>0008375526</td>\n",
       "      <td>[\"Books\",\"Crafts, Hobbies &amp; Home\",\"Home Improv...</td>\n",
       "      <td>10884</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.amazon.com/dp/0008375526</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.89</td>\n",
       "      <td>Skincare: The award-winning ultimate no-nonsen...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>In Stock.</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$16.99\",\"url\":\"/Ski...</td>\n",
       "      <td>Caroline Hirons</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         asin         ISBN10  \\\n",
       "0  0007350813     0007350813   \n",
       "1  0007513763  9780007513765   \n",
       "2  0008183988     0008183988   \n",
       "3  0008305838     0008305838   \n",
       "4  0008375526     0008375526   \n",
       "\n",
       "                                          categories  reviews_count  \\\n",
       "0   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]          13451   \n",
       "1  [\"Books\",\"Children's Books\",\"Literature & Fict...          16628   \n",
       "2   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]          11275   \n",
       "3  [\"Books\",\"Children's Books\",\"Literature & Fict...          15520   \n",
       "4  [\"Books\",\"Crafts, Hobbies & Home\",\"Home Improv...          10884   \n",
       "\n",
       "               rating  number_of_sellers  \\\n",
       "0  4.6 out of 5 stars                NaN   \n",
       "1  4.8 out of 5 stars                NaN   \n",
       "2  4.8 out of 5 stars                NaN   \n",
       "3  4.8 out of 5 stars                NaN   \n",
       "4  4.8 out of 5 stars                NaN   \n",
       "\n",
       "                                    url image  final_price  \\\n",
       "0  https://www.amazon.com/dp/0007350813   NaN         3.99   \n",
       "1  https://www.amazon.com/dp/0007513763   NaN        12.08   \n",
       "2  https://www.amazon.com/dp/0008183988   NaN          NaN   \n",
       "3  https://www.amazon.com/dp/0008305838   NaN        20.43   \n",
       "4  https://www.amazon.com/dp/0008375526   NaN        28.89   \n",
       "\n",
       "                                               title description availability  \\\n",
       "0               Wuthering Heights (Collins Classics)         NaN    In Stock.   \n",
       "1                          THE DAYS THE CRAYONS QUIT         NaN    In Stock.   \n",
       "2        War Lord: Book 13 (The Last Kingdom Series)         NaN          NaN   \n",
       "3  Code Name Bananas: The hilarious and epic new ...         NaN    In Stock.   \n",
       "4  Skincare: The award-winning ultimate no-nonsen...         NaN    In Stock.   \n",
       "\n",
       "                                              format             brand  \n",
       "0  [{\"name\":\"Kindle\",\"price\":\"$0.99\",\"url\":\"/Wuth...      Emily Brontë  \n",
       "1  [{\"name\":\"Kindle\",\"price\":\"$10.99\",\"url\":\"/Day...      Drew Daywalt  \n",
       "2  [{\"name\":\"Kindle\",\"price\":\"$11.99\",\"url\":\"/War...  Bernard Cornwell  \n",
       "3  [{\"name\":\"Paperback\",\"price\":\"$24.78\",\"url\":\"/...    David Walliams  \n",
       "4  [{\"name\":\"Kindle\",\"price\":\"$16.99\",\"url\":\"/Ski...   Caroline Hirons  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define the columns to keep\n",
    "keep_cols = ['asin', \n",
    "             'ISBN10', \n",
    "             'categories', \n",
    "             'reviews_count', \n",
    "             'rating', \n",
    "             'number_of_sellers', \n",
    "             'url', \n",
    "             'image', \n",
    "             'final_price',\n",
    "             'title',\n",
    "             'description',\n",
    "             'availability',\n",
    "             'format',\n",
    "             'brand'\n",
    "            ]\n",
    "\n",
    "# Review\n",
    "amazon_books_updated = amazon_books[keep_cols]\n",
    "amazon_books_updated.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49d361cc",
   "metadata": {},
   "source": [
    "### Another Round of Column Selection\n",
    "\n",
    "The previous column selection is mostly done to retrieve plausible columns for the analysis. Upon closer inspection, there are some columns I would like to remove:\n",
    "\n",
    "| Full Name    | Column Name  | Reason for Removal                                                                                                                                                                                                                                                                                                                                                                                         |\n",
    "|:-------------|:-------------|:-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n",
    "| ASIN         | asin         | The [Amazon Standard Identification Number](https://en.wikipedia.org/wiki/Amazon_Standard_Identification_Number) (ASIN) is not required. It's sufficient to identify a book by its title.                                                                                                                                                                                                                  |\n",
    "| ISBN         | ISBN10       | Similar to the reason for ASIN removal, the [International Standard Book Number](https://en.wikipedia.org/wiki/ISBN) (ISBN) is not required.                                                                                                                                                                                                                                                               |\n",
    "| URL          | url          | The url or link to the Amazon page for the book is not necessary. Our analysis doesn't cover anything that looks into the exact url or the page the url links to.                                                                                                                                                                                                                                          |\n",
    "| Image        | image        | The image of the book is not needed. It could be useful, but it requires different (and potentially more complicated) data analysis methods which are beyond the scope of this project. A worthy candidate for investigation in our next project.                                                                                                                                                          |\n",
    "| Description  | description  | Similar to book image, this could be useful but can be too complicated to analyse for the purpose of this project.                                                                                                                                                                                                                                                                                         |\n",
    "| Availability | availability | We are not concerned with a book's stock availability on Amazon; if the book is included in the best seller list, it's good enough whether it's available or not. Other than that, stock availability data is dynamic and the data set is just a snapshot of a book's availability when the data was collected; we'll need a time series data set of availability if we want to use the data for analysis. |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "31de0323",
   "metadata": {
    "scrolled": true
   },
   "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>categories</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>rating</th>\n",
       "      <th>number_of_sellers</th>\n",
       "      <th>final_price</th>\n",
       "      <th>title</th>\n",
       "      <th>format</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>13451</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.99</td>\n",
       "      <td>Wuthering Heights (Collins Classics)</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$0.99\",\"url\":\"/Wuth...</td>\n",
       "      <td>Emily Brontë</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>16628</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.08</td>\n",
       "      <td>THE DAYS THE CRAYONS QUIT</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$10.99\",\"url\":\"/Day...</td>\n",
       "      <td>Drew Daywalt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>11275</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>War Lord: Book 13 (The Last Kingdom Series)</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$11.99\",\"url\":\"/War...</td>\n",
       "      <td>Bernard Cornwell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>15520</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.43</td>\n",
       "      <td>Code Name Bananas: The hilarious and epic new ...</td>\n",
       "      <td>[{\"name\":\"Paperback\",\"price\":\"$24.78\",\"url\":\"/...</td>\n",
       "      <td>David Walliams</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[\"Books\",\"Crafts, Hobbies &amp; Home\",\"Home Improv...</td>\n",
       "      <td>10884</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.89</td>\n",
       "      <td>Skincare: The award-winning ultimate no-nonsen...</td>\n",
       "      <td>[{\"name\":\"Kindle\",\"price\":\"$16.99\",\"url\":\"/Ski...</td>\n",
       "      <td>Caroline Hirons</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          categories  reviews_count  \\\n",
       "0   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]          13451   \n",
       "1  [\"Books\",\"Children's Books\",\"Literature & Fict...          16628   \n",
       "2   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]          11275   \n",
       "3  [\"Books\",\"Children's Books\",\"Literature & Fict...          15520   \n",
       "4  [\"Books\",\"Crafts, Hobbies & Home\",\"Home Improv...          10884   \n",
       "\n",
       "               rating  number_of_sellers  final_price  \\\n",
       "0  4.6 out of 5 stars                NaN         3.99   \n",
       "1  4.8 out of 5 stars                NaN        12.08   \n",
       "2  4.8 out of 5 stars                NaN          NaN   \n",
       "3  4.8 out of 5 stars                NaN        20.43   \n",
       "4  4.8 out of 5 stars                NaN        28.89   \n",
       "\n",
       "                                               title  \\\n",
       "0               Wuthering Heights (Collins Classics)   \n",
       "1                          THE DAYS THE CRAYONS QUIT   \n",
       "2        War Lord: Book 13 (The Last Kingdom Series)   \n",
       "3  Code Name Bananas: The hilarious and epic new ...   \n",
       "4  Skincare: The award-winning ultimate no-nonsen...   \n",
       "\n",
       "                                              format             brand  \n",
       "0  [{\"name\":\"Kindle\",\"price\":\"$0.99\",\"url\":\"/Wuth...      Emily Brontë  \n",
       "1  [{\"name\":\"Kindle\",\"price\":\"$10.99\",\"url\":\"/Day...      Drew Daywalt  \n",
       "2  [{\"name\":\"Kindle\",\"price\":\"$11.99\",\"url\":\"/War...  Bernard Cornwell  \n",
       "3  [{\"name\":\"Paperback\",\"price\":\"$24.78\",\"url\":\"/...    David Walliams  \n",
       "4  [{\"name\":\"Kindle\",\"price\":\"$16.99\",\"url\":\"/Ski...   Caroline Hirons  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define the columns to remove\n",
    "remove_cols =   [\n",
    "                 'asin', \n",
    "                 'ISBN10', \n",
    "                 'url', \n",
    "                 'image', \n",
    "                 'description',\n",
    "                 'availability',\n",
    "                 ]\n",
    "\n",
    "# Remove the columns\n",
    "amazon_books_updated = amazon_books_updated.drop(columns=remove_cols)\n",
    "\n",
    "# Review\n",
    "amazon_books_updated.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4600218d",
   "metadata": {},
   "source": [
    "The final list of columns will be the following:\n",
    "\n",
    "| Full Name         | Column Name       |\n",
    "|:------------------|:------------------|\n",
    "| Categories        | categories        |\n",
    "| Reviews count     | reviews_count     |\n",
    "| Average rating    | rating            |\n",
    "| Number of sellers | number_of_sellers |\n",
    "| Final price       | final_price       |\n",
    "| Title             | title             |\n",
    "| Format            | format            |\n",
    "| Author            | brand             |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3b17b7e",
   "metadata": {},
   "source": [
    "### Handling missing values\n",
    "\n",
    "Let's check for missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3722800f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "categories              0\n",
       "reviews_count           0\n",
       "rating                  0\n",
       "number_of_sellers    2265\n",
       "final_price           877\n",
       "title                   0\n",
       "format                 96\n",
       "brand                   1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the number of missing values per column\n",
    "amazon_books_updated.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25f143a9",
   "metadata": {},
   "source": [
    "It seems that the number of sellers, final price, format, and brand columns have missing values. The first two, especially  number of sellers, have startling number of missing values. Next, I'll go through each column one by one to evaluate how to handle them.\n",
    "\n",
    "**Missing values in the number of sellers column**\n",
    "\n",
    "There's a significant number of missing values in this column. Interesting enough, there are 3 rows with a value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7779afe4",
   "metadata": {
    "scrolled": true
   },
   "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>categories</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>rating</th>\n",
       "      <th>number_of_sellers</th>\n",
       "      <th>final_price</th>\n",
       "      <th>title</th>\n",
       "      <th>format</th>\n",
       "      <th>brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>664</th>\n",
       "      <td>[\"Books\",\"Humor &amp; Entertainment\",\"Puzzles &amp; Ga...</td>\n",
       "      <td>37782</td>\n",
       "      <td>4.9 out of 5 stars</td>\n",
       "      <td>94.0</td>\n",
       "      <td>27.93</td>\n",
       "      <td>Player's Handbook (Dungeons &amp; Dragons)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Wizards of the Coast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1194</th>\n",
       "      <td>[\"Books\",\"Self-Help\",\"Relationships\",\"Love &amp; R...</td>\n",
       "      <td>15377</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>19.0</td>\n",
       "      <td>8.01</td>\n",
       "      <td>Knock Knock What I Love about You Fill in the ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Knock Knock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2029</th>\n",
       "      <td>[\"Toys &amp; Games\",\"Learning &amp; Education\",\"Electr...</td>\n",
       "      <td>18477</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>4.0</td>\n",
       "      <td>17.99</td>\n",
       "      <td>VTech Musical Rhymes Book, Pink</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VTech</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2177</th>\n",
       "      <td>[\"Books\",\"Calendars\",\"Animals\",\"Dogs\"]</td>\n",
       "      <td>11249</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>2.0</td>\n",
       "      <td>16.99</td>\n",
       "      <td>Pooping Pooches White Elephant Gag Gift Calendar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pooping Pooches</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             categories  reviews_count  \\\n",
       "664   [\"Books\",\"Humor & Entertainment\",\"Puzzles & Ga...          37782   \n",
       "1194  [\"Books\",\"Self-Help\",\"Relationships\",\"Love & R...          15377   \n",
       "2029  [\"Toys & Games\",\"Learning & Education\",\"Electr...          18477   \n",
       "2177             [\"Books\",\"Calendars\",\"Animals\",\"Dogs\"]          11249   \n",
       "\n",
       "                  rating  number_of_sellers  final_price  \\\n",
       "664   4.9 out of 5 stars               94.0        27.93   \n",
       "1194  4.7 out of 5 stars               19.0         8.01   \n",
       "2029  4.8 out of 5 stars                4.0        17.99   \n",
       "2177  4.8 out of 5 stars                2.0        16.99   \n",
       "\n",
       "                                                  title format  \\\n",
       "664              Player's Handbook (Dungeons & Dragons)    NaN   \n",
       "1194  Knock Knock What I Love about You Fill in the ...    NaN   \n",
       "2029                    VTech Musical Rhymes Book, Pink    NaN   \n",
       "2177   Pooping Pooches White Elephant Gag Gift Calendar    NaN   \n",
       "\n",
       "                     brand  \n",
       "664   Wizards of the Coast  \n",
       "1194           Knock Knock  \n",
       "2029                 VTech  \n",
       "2177       Pooping Pooches  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show frequency of values in number_of_sellers\n",
    "amazon_books_updated['number_of_sellers'].value_counts()\n",
    "\n",
    "# Show rows which has no missing values in number_of_sellers\n",
    "amazon_books_updated[amazon_books_updated['number_of_sellers'].notnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b9f8cb6",
   "metadata": {},
   "source": [
    "It doesn't seem that we can glean anything important from the available values for number of sellers. Doing the work ourself by filling in the number of sellers is not an option because we do not know exactly when the data was collected; even if we do know, it's unclear whether it's possible to find past data for the known date. Our only option is to drop the number of sellers column though not reluctantly because it is not too important for our analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "642f218f",
   "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>columns</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>categories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>reviews_count</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rating</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>final_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>title</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>format</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>brand</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         columns\n",
       "0     categories\n",
       "1  reviews_count\n",
       "2         rating\n",
       "3    final_price\n",
       "4          title\n",
       "5         format\n",
       "6          brand"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "amazon_books_updated.drop(columns=['number_of_sellers'], inplace=True)\n",
    "# Verify column has been dropped\n",
    "pd.DataFrame({\"columns\": amazon_books_updated.columns})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0454712d",
   "metadata": {},
   "source": [
    "**Missing values in the final price and format columns**\n",
    "\n",
    "We put both the final price and format columns together in this section because they are actually related. Let's see what we mean by that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ce082c43",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NaN      877\n",
      "9.99      25\n",
      "6.99      24\n",
      "8.99      23\n",
      "15.99     21\n",
      "        ... \n",
      "17.26      1\n",
      "25.01      1\n",
      "21.06      1\n",
      "58.88      1\n",
      "9.48       1\n",
      "Name: final_price, Length: 733, dtype: int64\n",
      "NaN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                96\n",
      "[{\"name\":\"Kindle\",\"price\":\"$0.00\",\"url\":\"/Picture-Dorian-Gray-AmazonClassics-ebook/dp/B071HDXV91\"},{\"name\":\"Hardcover\",\"price\":\"\",\"url\":\"/Picture-Dorian-Penguin-Clothbound-Classics/dp/0141442468\"},{\"name\":\"Paperback\",\"price\":\"$3.88\",\"url\":\"/Picture-Dorian-Gray-Oscar-Wilde/dp/1548296740\"},{\"name\":\"Mass Market Paperback\",\"price\":\"$6.46\",\"url\":\"/Picture-Dorian-Gray-Tor-Classics/dp/0812567110\"},{\"name\":\"MP3 CD\",\"price\":\"\",\"url\":\"/Picture-Dorian-Gray-Classic-Collection/dp/1501288202\"},{\"name\":\"Digital\",\"price\":\"\",\"url\":\"/The-Picture-of-Dorian-Gray/dp/0007219008\"}]                                                                                                                                               4\n",
      "[{\"name\":\"Kindle\",\"price\":\"$0.49\",\"url\":\"/Art-War-Sun-Tzu-ebook/dp/B096G2Q2T6\"},{\"name\":\"Hardcover\",\"price\":\"$9.44\",\"url\":\"/Art-War-Chump-Change/dp/1640320164\"},{\"name\":\"Paperback\",\"price\":\"$3.55\",\"url\":\"/Art-War-Sun-Tzu/dp/3903352608\"},{\"name\":\"Mass Market Paperback\",\"price\":\"$7.68\",\"url\":\"/Art-War-Sun-Tzu/dp/1590302257\"},{\"name\":\"Preloaded Digital Audio Player\",\"price\":\"$39.99\",\"url\":\"/Art-War-Library-Sun-tzu/dp/1598958445\"}]                                                                                                                                                                                                                                                                                     3\n",
      "[{\"name\":\"Kindle\",\"price\":\"$12.99\",\"url\":\"/Immortal-Life-Henrietta-Lacks-ebook/dp/B00338QENI\"},{\"name\":\"Audiobook\",\"price\":\"Audiobook $0.00\",\"url\":\"/Immortal-Life-Henrietta-Lacks/dp/B0036UZCRM\"},{\"name\":\"Hardcover\",\"price\":\"$14.32\",\"url\":\"/Immortal-Life-Henrietta-Lacks/dp/1400052173\"},{\"name\":\"Paperback\",\"price\":\"$12.10\",\"url\":\"/Immortal-Life-Henrietta-Lacks/dp/1400052181\"},{\"name\":\"Audio CD\",\"price\":\"$23.74\",\"url\":\"/Immortal-Life-Henrietta-Lacks/dp/0451486315\"}]                                                                                                                                                                                                                                                 3\n",
      "[{\"name\":\"Kindle\",\"price\":\"$0.00\",\"url\":\"/Christmas-Carol-AmazonClassics-Charles-Dickens-ebook/dp/B071ZKZBWR\"},{\"name\":\"Audiobook\",\"price\":\"Audiobook $0.00\",\"url\":\"/A-Christmas-Carol-Charles-Dickens-audiobook/dp/B07JQ28G6X\"},{\"name\":\"Paperback\",\"price\":\"$2.99\",\"url\":\"/Christmas-Carol-Dover-Thrift-Editions/dp/0486268659\"},{\"name\":\"Loose Leaf\",\"price\":\"$22.49\",\"url\":\"/Christmas-carol-Charles-Dickens/dp/0717246108\"},{\"name\":\"Mass Market Paperback\",\"price\":\"$3.95\",\"url\":\"/Christmas-Carol-Bantam-Classics/dp/0553212443\"},{\"name\":\"Audio CD\",\"price\":\"$39.61\",\"url\":\"/Christmas-Carol-Charles-Dickens/dp/1400086035\"},{\"name\":\"Multimedia CD\",\"price\":\"$51.95\",\"url\":\"/Christmas-Carol-Audio-CD/dp/1413010970\"}]     3\n",
      "Name: format, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Print the frequency of values in both the final price and format columns\n",
    "print(amazon_books_updated['final_price'].value_counts(dropna=False))\n",
    "\n",
    "# Be ready for an eyesore\n",
    "print(amazon_books_updated['format'].value_counts(dropna=False).head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11f2e367",
   "metadata": {},
   "source": [
    "Note that the format column also has prices in them but each price is linked to a certain format. This means that a book might have multiple prices depending on its format.\n",
    "\n",
    "Other than that, the values in the final price column is as expected at least on a cursory look, but the values in the format column needs further inspection. Before that, we need to do some data conversion for the format column because even though the above looks like a list, it is actually a string. Converting all the values into a list is our next step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ac430cf9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Formats are either list or nulls: True\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "96"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import ast\n",
    "import numpy as np\n",
    "\n",
    "'''\n",
    "**Convert each value that is not null into a list**\n",
    "\n",
    "`ast.literal_eval` evaluates and converts the literal string into the appropriate Python object. \n",
    "\n",
    "The [documentation](https://docs.python.org/3/library/ast.html#ast.literal_eval) mentioned that it is safe-ish; it prevents Python code from being called but depending on the input, it's possible to trigger memory or C-stack exhaustion.\n",
    "\n",
    "We trust the collector of the data set enough to use `ast.literal_eval` in spite of the potential attack.\n",
    "'''\n",
    "amazon_books_updated['format'] = amazon_books_updated['format'].apply(lambda x: ast.literal_eval(x) if not pd.isna(x) else np.nan)\n",
    "\n",
    "# Making sure that all values in format is either a list or a null value\n",
    "print(\"Formats are either list or nulls: {}\".format(amazon_books_updated['format'].apply(lambda x: type(x) is list or x is np.nan).all()))\n",
    "\n",
    "# Verify if the number of nulls is still the same\n",
    "amazon_books_updated['format'].isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c771c399",
   "metadata": {},
   "source": [
    "Next, let's explore the possible values for the format name and price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "27f25289",
   "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>format</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Comics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Board book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Book Supplement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cards</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MP3 CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Textbook Binding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Perfect Paperback</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Spiral-bound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>School &amp; Library Binding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Plastic Comb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Ring-bound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sheet music</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Library Binding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mass Market Paperback</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Kindle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Digital</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Single Issue Magazine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Audio CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Vinyl Bound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Preloaded Digital Audio Player</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Map</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Audio CD Library Binding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Kindle Edition with Audio/Video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Accessory with book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Audio, Cassette</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Rag Book</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Flexibound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Product Bundle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Audiobook</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Multimedia CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Leather Bound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Paperback</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Loose Leaf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Hardcover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Kindle &amp; comiXology</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             format\n",
       "0                            Comics\n",
       "1                        Board book\n",
       "2                   Book Supplement\n",
       "3                             Cards\n",
       "4                            MP3 CD\n",
       "5                  Textbook Binding\n",
       "6                 Perfect Paperback\n",
       "7                      Spiral-bound\n",
       "8          School & Library Binding\n",
       "9                      Plastic Comb\n",
       "10                       Ring-bound\n",
       "11                      Sheet music\n",
       "12                  Library Binding\n",
       "13            Mass Market Paperback\n",
       "14                           Kindle\n",
       "15                          Digital\n",
       "16                              Toy\n",
       "17            Single Issue Magazine\n",
       "18                         Audio CD\n",
       "19                      Vinyl Bound\n",
       "20   Preloaded Digital Audio Player\n",
       "21                              Map\n",
       "22         Audio CD Library Binding\n",
       "23  Kindle Edition with Audio/Video\n",
       "24              Accessory with book\n",
       "25                  Audio, Cassette\n",
       "26                         Rag Book\n",
       "27                       Flexibound\n",
       "28                   Product Bundle\n",
       "29                        Audiobook\n",
       "30                    Multimedia CD\n",
       "31                    Leather Bound\n",
       "32                        Paperback\n",
       "33                       Loose Leaf\n",
       "34                        Hardcover\n",
       "35              Kindle & comiXology"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>$15.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>$25.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>$44.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>$10.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1398</th>\n",
       "      <td>$16.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1399</th>\n",
       "      <td>$2.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1400</th>\n",
       "      <td>$19.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1401</th>\n",
       "      <td>$23.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1402</th>\n",
       "      <td>$10.57</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1403 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       price\n",
       "0           \n",
       "1     $15.49\n",
       "2     $25.92\n",
       "3     $44.99\n",
       "4     $10.97\n",
       "...      ...\n",
       "1398  $16.22\n",
       "1399   $2.72\n",
       "1400  $19.98\n",
       "1401  $23.63\n",
       "1402  $10.57\n",
       "\n",
       "[1403 rows x 1 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "The value in the format column is a list containing dictionaries. \n",
    "\n",
    "In each list is a dictionary that follows the following structure:\n",
    "{\n",
    "    \"name\": \"Format\",\n",
    "    \"price\": \"$0.00\",\n",
    "    \"url\": \"/url/to/the/book/page/on/Amazon\"\n",
    "}\n",
    "\n",
    "We would like to extract the name and price for each book. \n",
    "\n",
    "''' \n",
    "\n",
    "price_set = set()\n",
    "format_set = set()\n",
    "\n",
    "not_null_format = amazon_books_updated[amazon_books_updated['format'].notnull()]['format']\n",
    "\n",
    "# Loop through the rows with no missing formats and add to the corresponding sets\n",
    "for formats in not_null_format:\n",
    "    for book_format in formats:\n",
    "        format_name = book_format['name']\n",
    "        price = book_format['price']\n",
    "        \n",
    "        format_set.add(format_name)\n",
    "        price_set.add(price)\n",
    "\n",
    "        \n",
    "# Prepare for display\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"last_expr\"\n",
    "\n",
    "format_df = pd.DataFrame({\"format\": list(format_set)})\n",
    "display(format_df)\n",
    "\n",
    "price_df = pd.DataFrame({\"price\": list(price_set)})\n",
    "price_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adb68fc5",
   "metadata": {},
   "source": [
    "One thing for certain is we didn't expect the variety of book formats (which must be obvious to everyone else but not us). This introduces slight complexity on our part because we need to select the format that best represents our more general book format: physical book and e-book.\n",
    "\n",
    "On the other hand, the values for price looks reasonable except for the empty value.\n",
    "\n",
    "Let's identify which book format to keep.\n",
    "\n",
    "**Identifying relevant formats**\n",
    "\n",
    "Physical books on Amazon can come in many formats. After reviewing the books we intend to publish, we decided to focus on the following physical formats: paperback and hardcover. For e-books, the obvious choice would be Kindle and we ignore its derivatives which are Kindle & comixology and Kindle Edition with Audio/Video.\n",
    "\n",
    "We've pruned the format list to only represent what we intend to publish:\n",
    "\n",
    "| Format Name           | Definition                                                                                                                                                                                                                                                                                                            |\n",
    "|:----------------------|:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n",
    "| Paperback             | A book with thick paper or paperboard cover. To contrast with the mass market paperback, a paperback is also called \"trade paperback\" and is regarded as being higher in quality.                                                                                                                                     |\n",
    "| Perfect Paperback     | \"Perfect\" might be a misnomer here depending on your knowledge of book publication. A perfect paperback is not about the quality of the paperback but the kind of binding used. Perfect binding relies solely on adhesive and does not use any stitches.                                                                         |\n",
    "| Mass Market Paperback | A mass market paperback is similar to a \"trade\" paperback, but it tends to be smaller and less durable. It's also generally cheaper since it's intended to be produced \"in mass\". This format is generally used when publishing very popular books in order to make it accessible, mostly price-wise, to most people. |\n",
    "| Hardcover             | A book with rigid/hard protective covers. A book will typically have a hardcover format which are published early in the publication cycle and only for a limited amount of time. After sales are slower, publisher of the hardcover book normally transition to the cheaper paperback format to reinvigorate sales.  |\n",
    "| Kindle                | An e-book meant for reading with an Amazon Kindle.                                                                                                                                                                                                                                                                    |\n",
    "\n",
    "You can find some references on book formats in the <a href=\"#References\">References</a> section.\n",
    "\n",
    "**Keep only the relevant book formats in the data set**\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "051b992a",
   "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>brand</th>\n",
       "      <th>categories</th>\n",
       "      <th>final_price</th>\n",
       "      <th>format</th>\n",
       "      <th>hardcover_price</th>\n",
       "      <th>kindle_price</th>\n",
       "      <th>mass_market_paperback_price</th>\n",
       "      <th>paperback_price</th>\n",
       "      <th>perfect_paperback_price</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Emily Brontë</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>3.99</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.99', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$0.99</td>\n",
       "      <td>$4.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>13451</td>\n",
       "      <td>Wuthering Heights (Collins Classics)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Drew Daywalt</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>12.08</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$10.99', 'url': ...</td>\n",
       "      <td>$9.19</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>16628</td>\n",
       "      <td>THE DAYS THE CRAYONS QUIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bernard Cornwell</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$11.99', 'url': ...</td>\n",
       "      <td>$18.29</td>\n",
       "      <td>$11.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>11275</td>\n",
       "      <td>War Lord: Book 13 (The Last Kingdom Series)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>David Walliams</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>20.43</td>\n",
       "      <td>[{'name': 'Paperback', 'price': '$24.78', 'url...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$24.78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>15520</td>\n",
       "      <td>Code Name Bananas: The hilarious and epic new ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Caroline Hirons</td>\n",
       "      <td>[\"Books\",\"Crafts, Hobbies &amp; Home\",\"Home Improv...</td>\n",
       "      <td>28.89</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$16.99', 'url': ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$16.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>10884</td>\n",
       "      <td>Skincare: The award-winning ultimate no-nonsen...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              brand                                         categories  \\\n",
       "0      Emily Brontë   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "1      Drew Daywalt  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "2  Bernard Cornwell   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "3    David Walliams  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "4   Caroline Hirons  [\"Books\",\"Crafts, Hobbies & Home\",\"Home Improv...   \n",
       "\n",
       "   final_price                                             format  \\\n",
       "0         3.99  [{'name': 'Kindle', 'price': '$0.99', 'url': '...   \n",
       "1        12.08  [{'name': 'Kindle', 'price': '$10.99', 'url': ...   \n",
       "2          NaN  [{'name': 'Kindle', 'price': '$11.99', 'url': ...   \n",
       "3        20.43  [{'name': 'Paperback', 'price': '$24.78', 'url...   \n",
       "4        28.89  [{'name': 'Kindle', 'price': '$16.99', 'url': ...   \n",
       "\n",
       "  hardcover_price kindle_price mass_market_paperback_price paperback_price  \\\n",
       "0          $10.99        $0.99                       $4.95             NaN   \n",
       "1           $9.19       $10.99                         NaN             NaN   \n",
       "2          $18.29       $11.99                         NaN             NaN   \n",
       "3             NaN          NaN                         NaN          $24.78   \n",
       "4             NaN       $16.99                         NaN             NaN   \n",
       "\n",
       "  perfect_paperback_price              rating  reviews_count  \\\n",
       "0                     NaN  4.6 out of 5 stars          13451   \n",
       "1                     NaN  4.8 out of 5 stars          16628   \n",
       "2                     NaN  4.8 out of 5 stars          11275   \n",
       "3                     NaN  4.8 out of 5 stars          15520   \n",
       "4                     NaN  4.8 out of 5 stars          10884   \n",
       "\n",
       "                                               title  \n",
       "0               Wuthering Heights (Collins Classics)  \n",
       "1                          THE DAYS THE CRAYONS QUIT  \n",
       "2        War Lord: Book 13 (The Last Kingdom Series)  \n",
       "3  Code Name Bananas: The hilarious and epic new ...  \n",
       "4  Skincare: The award-winning ultimate no-nonsen...  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "keep_format = ['Paperback', 'Kindle', 'Perfect Paperback', 'Mass Market Paperback', 'Hardcover']\n",
    "\n",
    "def keep_specific_format(format_list, format_to_keep):\n",
    "    if format_list == np.nan:\n",
    "        return format_list\n",
    "    for f_dict in format_list:\n",
    "        if f_dict['name'] == format_to_keep:\n",
    "            return f_dict['price']\n",
    "\n",
    "def keep_paperback_hardcover_kindle(row):\n",
    "#   print(row['format'])\n",
    "    book_formats = row['format']\n",
    "    if book_formats is np.nan:\n",
    "         return row\n",
    "    for format in book_formats:\n",
    "        name = format['name']\n",
    "        price = format['price']\n",
    "        if name in keep_format:\n",
    "            split_name = name.split(' ')\n",
    "            snakecase_name = \"_\".join(split_name).lower()\n",
    "            row[\"{}_price\".format(snakecase_name)] = price\n",
    "    return row\n",
    "\n",
    "amazon_books_updated = amazon_books_updated.apply(keep_paperback_hardcover_kindle, axis=1)\n",
    "amazon_books_updated.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5702bef",
   "metadata": {},
   "source": [
    "It seems that the information from the format column is much richer than the final price column. Another thing we recently noticed is we don't have an idea which format the final price is referring to. This highlights another problem: formats are needed because we're only interested in books that were published at least as a hardcover, paperback, or an e-book; without the format, we risk addding non-representative books into our analysis. \n",
    "\n",
    "Because of the above stated reasons, we are considering removing the final price column and also any books with missing formats. Let's investigate those books with no formats first before removing anything."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "365ebb98",
   "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>brand</th>\n",
       "      <th>categories</th>\n",
       "      <th>format</th>\n",
       "      <th>hardcover_price</th>\n",
       "      <th>kindle_price</th>\n",
       "      <th>mass_market_paperback_price</th>\n",
       "      <th>paperback_price</th>\n",
       "      <th>perfect_paperback_price</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [brand, categories, format, hardcover_price, kindle_price, mass_market_paperback_price, paperback_price, perfect_paperback_price, rating, reviews_count, title]\n",
       "Index: []"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of books with missing formats: 0\n",
      "Percentage of total data set: 0.00%\n"
     ]
    }
   ],
   "source": [
    "# Define a simple function that prints the length of two data frames.\n",
    "# It also prints the percentage of one length in relation to another length\n",
    "def print_length_and_percentage(subset, df, noun=\"rows\"):\n",
    "    subset_nrows = subset.shape[0]\n",
    "    df_nrows     = df.shape[0]\n",
    "    print(\"Number of rows in data set: {}\".format(df_nrows))\n",
    "    print(\"Number of {}: {}\".format(noun, subset_nrows))\n",
    "    print(\"Percentage of total data set: {:.2%}\".format(subset_nrows/df_nrows))\n",
    "\n",
    "# Get books with missing formats\n",
    "no_formats = amazon_books_updated[amazon_books_updated['format'].isnull()]\n",
    "\n",
    "display(no_formats)\n",
    "\n",
    "# Get the percentage of books with missing formats; needed to decide whether to drop them\n",
    "print(\"Number of books with missing formats: {}\".format(no_formats.shape[0]))\n",
    "print(\"Percentage of total data set: {:.2%}\".format(no_formats.shape[0]/amazon_books_updated.shape[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3987d455",
   "metadata": {},
   "source": [
    "After further consideration, we decided to remove books with no formats for the following reasons:\n",
    "1. We could use the book prices in the current year (2022) but that still requires resources (mostly time) we can't afford to expend for this mostly exploratory project. We might consider doing so if there are only a few books, but 96 books are relatively numerous considering our limited resources.\n",
    "2. We gave a cursory look at the details of some of the books and we realised that some of them are not representative of what we want to publish. As seen in the table above, at least 5 of the 10 books are not hardcover, paperback, nor e-books. It seems that a signficant portion of them are books for children which come in child-friendly formats such as board book, musical rhyme book, and activity workbook.\n",
    "3. Although removing around 4 per cent of all books in the data set is not ideal, we're confident that the impact is potentially not significant considering that not all 96 books are representative of the books we're looking for."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a983df39",
   "metadata": {
    "scrolled": true
   },
   "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>brand</th>\n",
       "      <th>categories</th>\n",
       "      <th>format</th>\n",
       "      <th>hardcover_price</th>\n",
       "      <th>kindle_price</th>\n",
       "      <th>mass_market_paperback_price</th>\n",
       "      <th>paperback_price</th>\n",
       "      <th>perfect_paperback_price</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Emily Brontë</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.99', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$0.99</td>\n",
       "      <td>$4.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>13451</td>\n",
       "      <td>Wuthering Heights (Collins Classics)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Drew Daywalt</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$10.99', 'url': ...</td>\n",
       "      <td>$9.19</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>16628</td>\n",
       "      <td>THE DAYS THE CRAYONS QUIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bernard Cornwell</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$11.99', 'url': ...</td>\n",
       "      <td>$18.29</td>\n",
       "      <td>$11.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>11275</td>\n",
       "      <td>War Lord: Book 13 (The Last Kingdom Series)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>David Walliams</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>[{'name': 'Paperback', 'price': '$24.78', 'url...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$24.78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>15520</td>\n",
       "      <td>Code Name Bananas: The hilarious and epic new ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Caroline Hirons</td>\n",
       "      <td>[\"Books\",\"Crafts, Hobbies &amp; Home\",\"Home Improv...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$16.99', 'url': ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$16.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>10884</td>\n",
       "      <td>Skincare: The award-winning ultimate no-nonsen...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              brand                                         categories  \\\n",
       "0      Emily Brontë   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "1      Drew Daywalt  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "2  Bernard Cornwell   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "3    David Walliams  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "4   Caroline Hirons  [\"Books\",\"Crafts, Hobbies & Home\",\"Home Improv...   \n",
       "\n",
       "                                              format hardcover_price  \\\n",
       "0  [{'name': 'Kindle', 'price': '$0.99', 'url': '...          $10.99   \n",
       "1  [{'name': 'Kindle', 'price': '$10.99', 'url': ...           $9.19   \n",
       "2  [{'name': 'Kindle', 'price': '$11.99', 'url': ...          $18.29   \n",
       "3  [{'name': 'Paperback', 'price': '$24.78', 'url...             NaN   \n",
       "4  [{'name': 'Kindle', 'price': '$16.99', 'url': ...             NaN   \n",
       "\n",
       "  kindle_price mass_market_paperback_price paperback_price  \\\n",
       "0        $0.99                       $4.95             NaN   \n",
       "1       $10.99                         NaN             NaN   \n",
       "2       $11.99                         NaN             NaN   \n",
       "3          NaN                         NaN          $24.78   \n",
       "4       $16.99                         NaN             NaN   \n",
       "\n",
       "  perfect_paperback_price              rating  reviews_count  \\\n",
       "0                     NaN  4.6 out of 5 stars          13451   \n",
       "1                     NaN  4.8 out of 5 stars          16628   \n",
       "2                     NaN  4.8 out of 5 stars          11275   \n",
       "3                     NaN  4.8 out of 5 stars          15520   \n",
       "4                     NaN  4.8 out of 5 stars          10884   \n",
       "\n",
       "                                               title  \n",
       "0               Wuthering Heights (Collins Classics)  \n",
       "1                          THE DAYS THE CRAYONS QUIT  \n",
       "2        War Lord: Book 13 (The Last Kingdom Series)  \n",
       "3  Code Name Bananas: The hilarious and epic new ...  \n",
       "4  Skincare: The award-winning ultimate no-nonsen...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove books with no formats \n",
    "amazon_books_updated.drop(index=no_formats.index, inplace=True)\n",
    "\n",
    "# At the same time, drop the final_price\n",
    "amazon_books_updated.drop(columns=['final_price'], inplace=True)\n",
    "\n",
    "amazon_books_updated.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1a83a0c",
   "metadata": {},
   "source": [
    "With the removal finished, let's complete the handling of missing values for the final price and format columns by removing books which are neither hardcover, paperback, nor e-book. There is around 30 books that has that criteria which accounts for approximately 1.5% of the whole data set. We're fine with removing them because they're certainly not representative of the books we want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "033d6833",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Books with no hardcover, paperback, or e-book: 32\n",
      "Percentage of total data set: 1.47%\n"
     ]
    },
    {
     "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>brand</th>\n",
       "      <th>categories</th>\n",
       "      <th>format</th>\n",
       "      <th>hardcover_price</th>\n",
       "      <th>kindle_price</th>\n",
       "      <th>mass_market_paperback_price</th>\n",
       "      <th>paperback_price</th>\n",
       "      <th>perfect_paperback_price</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Emily Brontë</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.99', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$0.99</td>\n",
       "      <td>$4.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>13451</td>\n",
       "      <td>Wuthering Heights (Collins Classics)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Drew Daywalt</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$10.99', 'url': ...</td>\n",
       "      <td>$9.19</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>16628</td>\n",
       "      <td>THE DAYS THE CRAYONS QUIT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bernard Cornwell</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$11.99', 'url': ...</td>\n",
       "      <td>$18.29</td>\n",
       "      <td>$11.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>11275</td>\n",
       "      <td>War Lord: Book 13 (The Last Kingdom Series)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>David Walliams</td>\n",
       "      <td>[\"Books\",\"Children's Books\",\"Literature &amp; Fict...</td>\n",
       "      <td>[{'name': 'Paperback', 'price': '$24.78', 'url...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$24.78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>15520</td>\n",
       "      <td>Code Name Bananas: The hilarious and epic new ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Caroline Hirons</td>\n",
       "      <td>[\"Books\",\"Crafts, Hobbies &amp; Home\",\"Home Improv...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$16.99', 'url': ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$16.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>10884</td>\n",
       "      <td>Skincare: The award-winning ultimate no-nonsen...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              brand                                         categories  \\\n",
       "0      Emily Brontë   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "1      Drew Daywalt  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "2  Bernard Cornwell   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "3    David Walliams  [\"Books\",\"Children's Books\",\"Literature & Fict...   \n",
       "4   Caroline Hirons  [\"Books\",\"Crafts, Hobbies & Home\",\"Home Improv...   \n",
       "\n",
       "                                              format hardcover_price  \\\n",
       "0  [{'name': 'Kindle', 'price': '$0.99', 'url': '...          $10.99   \n",
       "1  [{'name': 'Kindle', 'price': '$10.99', 'url': ...           $9.19   \n",
       "2  [{'name': 'Kindle', 'price': '$11.99', 'url': ...          $18.29   \n",
       "3  [{'name': 'Paperback', 'price': '$24.78', 'url...             NaN   \n",
       "4  [{'name': 'Kindle', 'price': '$16.99', 'url': ...             NaN   \n",
       "\n",
       "  kindle_price mass_market_paperback_price paperback_price  \\\n",
       "0        $0.99                       $4.95             NaN   \n",
       "1       $10.99                         NaN             NaN   \n",
       "2       $11.99                         NaN             NaN   \n",
       "3          NaN                         NaN          $24.78   \n",
       "4       $16.99                         NaN             NaN   \n",
       "\n",
       "  perfect_paperback_price              rating  reviews_count  \\\n",
       "0                     NaN  4.6 out of 5 stars          13451   \n",
       "1                     NaN  4.8 out of 5 stars          16628   \n",
       "2                     NaN  4.8 out of 5 stars          11275   \n",
       "3                     NaN  4.8 out of 5 stars          15520   \n",
       "4                     NaN  4.8 out of 5 stars          10884   \n",
       "\n",
       "                                               title  \n",
       "0               Wuthering Heights (Collins Classics)  \n",
       "1                          THE DAYS THE CRAYONS QUIT  \n",
       "2        War Lord: Book 13 (The Last Kingdom Series)  \n",
       "3  Code Name Bananas: The hilarious and epic new ...  \n",
       "4  Skincare: The award-winning ultimate no-nonsen...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a boolean mask to filter only those books with no hardcover, paperback, or e-book format\n",
    "# hpe - hardcover, paperback, e-book\n",
    "mask_not_hpe = amazon_books_updated[['hardcover_price', 'mass_market_paperback_price', 'paperback_price', 'perfect_paperback_price', 'kindle_price']].isnull().all(axis=1)\n",
    "books_no_hpe = amazon_books_updated[mask_not_hpe]\n",
    "\n",
    "print(\"Books with no hardcover, paperback, or e-book: {}\".format(books_no_hpe.shape[0]))\n",
    "print(\"Percentage of total data set: {:.2%}\".format(books_no_hpe.shape[0]/amazon_books_updated.shape[0]))\n",
    "\n",
    "# Remove the books\n",
    "amazon_books_updated.drop(index=books_no_hpe.index, inplace=True)\n",
    "\n",
    "amazon_books_updated.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3c993dc",
   "metadata": {},
   "source": [
    "**Checkpoint**\n",
    "\n",
    "We added a lot of changes to the data set so it's a good idea to review those before continuing further.\n",
    "\n",
    "We initially set out to handle missing values. The first thing we did was removing the number of sellers column. Next, we looked into the missing values for the final price and format columns; we noticed that we did not need the final price column thus we removed it. We also decided to remove books with missing format values and our reasons are partly resource limitations and partly non-representativeness.\n",
    "\n",
    "Let us review the current missing values for all columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "50cc4505",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "brand                             0\n",
       "categories                        0\n",
       "format                            0\n",
       "hardcover_price                 762\n",
       "kindle_price                    256\n",
       "mass_market_paperback_price    1413\n",
       "paperback_price                 740\n",
       "perfect_paperback_price        2138\n",
       "rating                            0\n",
       "reviews_count                     0\n",
       "title                             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the number of missing values per column\n",
    "amazon_books_updated.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d14dff81",
   "metadata": {},
   "source": [
    "Aside from the prices columns, all columns do not have missing values. The missing values for the prices columns are expected because not all books will have all five formats. This means that when we analyse price, depending on the number of missing values, the findings might be more limited compared to the findings for the other columns i.e. the more data points the better. Nonetheless, we're comfortable with that caveat and we will still keep the prices columns.\n",
    "\n",
    "### Handling Duplicate Books\n",
    "\n",
    "Our next task is to look for duplicate books. We can simply look if there are books with the same title."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "e6985959",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in data set: 2141\n",
      "Number of duplicate books: 661\n",
      "Percentage of total data set: 30.87%\n"
     ]
    }
   ],
   "source": [
    "duplicate_books = amazon_books_updated[amazon_books_updated.duplicated(subset=['title'])]\n",
    "                                       \n",
    "print_length_and_percentage(duplicate_books, amazon_books_updated, \"duplicate books\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad1b2fa0",
   "metadata": {},
   "source": [
    "There are 661 duplicate books which amounts to around 31% of the whole data set. That's quite a lot.\n",
    "\n",
    "Let's zoom in a bit to inspect the duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "e4c2ae7d",
   "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>brand</th>\n",
       "      <th>categories</th>\n",
       "      <th>format</th>\n",
       "      <th>hardcover_price</th>\n",
       "      <th>kindle_price</th>\n",
       "      <th>mass_market_paperback_price</th>\n",
       "      <th>paperback_price</th>\n",
       "      <th>perfect_paperback_price</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Jordan B. Peterson</td>\n",
       "      <td>[\"Books\",\"Business &amp; Money\",\"Job Hunting &amp; Car...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$13.99', 'url': ...</td>\n",
       "      <td>$14.99</td>\n",
       "      <td>$13.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>43801</td>\n",
       "      <td>12 Rules for Life: An Antidote to Chaos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>Jordan B. Peterson</td>\n",
       "      <td>[\"Books\",\"Health, Fitness &amp; Dieting\",\"Psycholo...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$13.99', 'url': ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$13.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$17.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>43801</td>\n",
       "      <td>12 Rules for Life: An Antidote to Chaos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2198</th>\n",
       "      <td>Jordan B. Peterson (Author, Narrator), Norman ...</td>\n",
       "      <td>[\"Books\",\"Health, Fitness &amp; Dieting\",\"Psycholo...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$13.99', 'url': ...</td>\n",
       "      <td>$14.99</td>\n",
       "      <td>$13.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$17.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>43801</td>\n",
       "      <td>12 Rules for Life: An Antidote to Chaos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>416</th>\n",
       "      <td>George Orwell</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$11.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>55581</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>418</th>\n",
       "      <td>George Orwell</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$7.48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>55581</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>786</th>\n",
       "      <td>George Orwell</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$7.48</td>\n",
       "      <td>$11.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>55581</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1760</th>\n",
       "      <td>George Orwell</td>\n",
       "      <td>[\"Books\",\"Politics &amp; Social Sciences\",\"Social ...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$7.48</td>\n",
       "      <td>$11.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>55581</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1694</th>\n",
       "      <td>George Orwell (Author), Charlton Griffin (Narr...</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.90', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$0.90</td>\n",
       "      <td>$7.48</td>\n",
       "      <td>$8.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>55581</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1749</th>\n",
       "      <td>George Orwell (Author), Benjamin May (Narrator...</td>\n",
       "      <td>[\"Books\",\"Science Fiction &amp; Fantasy\",\"Science ...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$10.99</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$7.48</td>\n",
       "      <td>$11.63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>55582</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>Matthew R. Kratter</td>\n",
       "      <td>[\"Books\",\"Business &amp; Money\",\"Investing\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.00', 'url': '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5 out of 5 stars</td>\n",
       "      <td>13067</td>\n",
       "      <td>A Beginner's Guide to the Stock Market: Everyt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1466</th>\n",
       "      <td>Matthew R. Kratter (Author), Mike Norgaard (Na...</td>\n",
       "      <td>[\"Books\",\"Business &amp; Money\",\"Investing\",\"Stocks\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.00', 'url': '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$6.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.5 out of 5 stars</td>\n",
       "      <td>13067</td>\n",
       "      <td>A Beginner's Guide to the Stock Market: Everyt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>Diana Gabaldon</td>\n",
       "      <td>[\"Books\",\"Science Fiction &amp; Fantasy\",\"Fantasy\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$8.49</td>\n",
       "      <td>$15.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>13908</td>\n",
       "      <td>A Breath of Snow and Ashes (Outlander)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>Diana Gabaldon</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$28.69</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$15.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.8 out of 5 stars</td>\n",
       "      <td>13908</td>\n",
       "      <td>A Breath of Snow and Ashes (Outlander)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2241</th>\n",
       "      <td>Charles Dickens (Author), Derek Miller (Narrat...</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.49', 'url': '...</td>\n",
       "      <td>$9.95</td>\n",
       "      <td>$0.49</td>\n",
       "      <td>$3.95</td>\n",
       "      <td>$3.58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>12865</td>\n",
       "      <td>A Christmas Carol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1274</th>\n",
       "      <td>Charles Dickens</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$0.00', 'url': '...</td>\n",
       "      <td>$18.61</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>$3.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>12865</td>\n",
       "      <td>A Christmas Carol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2196</th>\n",
       "      <td>Sarah J. Maas (Author), Jennifer Ikeda (Narrat...</td>\n",
       "      <td>[\"Books\",\"Teen &amp; Young Adult\",\"Literature &amp; Fi...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$6.65', 'url': '...</td>\n",
       "      <td>$19.59</td>\n",
       "      <td>$6.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$10.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>35943</td>\n",
       "      <td>A Court of Thorns and Roses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1248</th>\n",
       "      <td>Sarah J. Maas</td>\n",
       "      <td>[\"Books\",\"Science Fiction &amp; Fantasy\",\"Fantasy\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$6.65', 'url': '...</td>\n",
       "      <td>$19.59</td>\n",
       "      <td>$6.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>35941</td>\n",
       "      <td>A Court of Thorns and Roses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>Sarah J. Maas (Author), Jennifer Ikeda (Narrat...</td>\n",
       "      <td>[\"Books\",\"Teen &amp; Young Adult\",\"Literature &amp; Fi...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$6.65', 'url': '...</td>\n",
       "      <td>$19.59</td>\n",
       "      <td>$6.65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$10.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.7 out of 5 stars</td>\n",
       "      <td>35943</td>\n",
       "      <td>A Court of Thorns and Roses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1099</th>\n",
       "      <td>David Baldacci</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Action &amp; Adve...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$11.99', 'url': ...</td>\n",
       "      <td>$13.99</td>\n",
       "      <td>$11.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.4 out of 5 stars</td>\n",
       "      <td>21250</td>\n",
       "      <td>A Gambling Man (An Archer Novel)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1098</th>\n",
       "      <td>David Baldacci</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Action &amp; Adve...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$11.99', 'url': ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$11.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$11.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.4 out of 5 stars</td>\n",
       "      <td>21250</td>\n",
       "      <td>A Gambling Man (An Archer Novel)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2072</th>\n",
       "      <td>Amor Towles</td>\n",
       "      <td>[\"Books\",\"Mystery, Thriller &amp; Suspense\",\"Thril...</td>\n",
       "      <td>[{'name': 'Audiobook', 'price': 'Audiobook $0....</td>\n",
       "      <td>$14.76</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$9.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>37254</td>\n",
       "      <td>A Gentleman in Moscow: A Novel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Amor Towles</td>\n",
       "      <td>[\"Books\",\"Mystery, Thriller &amp; Suspense\",\"Thril...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$14.76</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>37254</td>\n",
       "      <td>A Gentleman in Moscow: A Novel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>Amor Towles</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction\"]</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$9.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>37254</td>\n",
       "      <td>A Gentleman in Moscow: A Novel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2078</th>\n",
       "      <td>Amor Towles (Author), Nicholas Guy Smith (Narr...</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$14.76</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$9.34</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6 out of 5 stars</td>\n",
       "      <td>37254</td>\n",
       "      <td>A Gentleman in Moscow: A Novel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1514</th>\n",
       "      <td>Isabel Allende (Author), Edoardo Ballerini (Na...</td>\n",
       "      <td>[\"Books\",\"Literature &amp; Fiction\",\"Genre Fiction...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$14.04</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$17.11</td>\n",
       "      <td>$14.49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.4 out of 5 stars</td>\n",
       "      <td>10398</td>\n",
       "      <td>A Long Petal of the Sea: A Novel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  brand  \\\n",
       "101                                  Jordan B. Peterson   \n",
       "269                                  Jordan B. Peterson   \n",
       "2198  Jordan B. Peterson (Author, Narrator), Norman ...   \n",
       "416                                       George Orwell   \n",
       "418                                       George Orwell   \n",
       "786                                       George Orwell   \n",
       "1760                                      George Orwell   \n",
       "1694  George Orwell (Author), Charlton Griffin (Narr...   \n",
       "1749  George Orwell (Author), Benjamin May (Narrator...   \n",
       "747                                  Matthew R. Kratter   \n",
       "1466  Matthew R. Kratter (Author), Mike Norgaard (Na...   \n",
       "299                                      Diana Gabaldon   \n",
       "397                                      Diana Gabaldon   \n",
       "2241  Charles Dickens (Author), Derek Miller (Narrat...   \n",
       "1274                                    Charles Dickens   \n",
       "2196  Sarah J. Maas (Author), Jennifer Ikeda (Narrat...   \n",
       "1248                                      Sarah J. Maas   \n",
       "2016  Sarah J. Maas (Author), Jennifer Ikeda (Narrat...   \n",
       "1099                                     David Baldacci   \n",
       "1098                                     David Baldacci   \n",
       "2072                                        Amor Towles   \n",
       "123                                         Amor Towles   \n",
       "565                                         Amor Towles   \n",
       "2078  Amor Towles (Author), Nicholas Guy Smith (Narr...   \n",
       "1514  Isabel Allende (Author), Edoardo Ballerini (Na...   \n",
       "\n",
       "                                             categories  \\\n",
       "101   [\"Books\",\"Business & Money\",\"Job Hunting & Car...   \n",
       "269   [\"Books\",\"Health, Fitness & Dieting\",\"Psycholo...   \n",
       "2198  [\"Books\",\"Health, Fitness & Dieting\",\"Psycholo...   \n",
       "416    [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "418    [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "786    [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "1760  [\"Books\",\"Politics & Social Sciences\",\"Social ...   \n",
       "1694  [\"Books\",\"Literature & Fiction\",\"Genre Fiction...   \n",
       "1749  [\"Books\",\"Science Fiction & Fantasy\",\"Science ...   \n",
       "747            [\"Books\",\"Business & Money\",\"Investing\"]   \n",
       "1466  [\"Books\",\"Business & Money\",\"Investing\",\"Stocks\"]   \n",
       "299     [\"Books\",\"Science Fiction & Fantasy\",\"Fantasy\"]   \n",
       "397    [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "2241  [\"Books\",\"Literature & Fiction\",\"Genre Fiction...   \n",
       "1274   [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "2196  [\"Books\",\"Teen & Young Adult\",\"Literature & Fi...   \n",
       "1248    [\"Books\",\"Science Fiction & Fantasy\",\"Fantasy\"]   \n",
       "2016  [\"Books\",\"Teen & Young Adult\",\"Literature & Fi...   \n",
       "1099  [\"Books\",\"Literature & Fiction\",\"Action & Adve...   \n",
       "1098  [\"Books\",\"Literature & Fiction\",\"Action & Adve...   \n",
       "2072  [\"Books\",\"Mystery, Thriller & Suspense\",\"Thril...   \n",
       "123   [\"Books\",\"Mystery, Thriller & Suspense\",\"Thril...   \n",
       "565    [\"Books\",\"Literature & Fiction\",\"Genre Fiction\"]   \n",
       "2078  [\"Books\",\"Literature & Fiction\",\"Genre Fiction...   \n",
       "1514  [\"Books\",\"Literature & Fiction\",\"Genre Fiction...   \n",
       "\n",
       "                                                 format hardcover_price  \\\n",
       "101   [{'name': 'Kindle', 'price': '$13.99', 'url': ...          $14.99   \n",
       "269   [{'name': 'Kindle', 'price': '$13.99', 'url': ...             NaN   \n",
       "2198  [{'name': 'Kindle', 'price': '$13.99', 'url': ...          $14.99   \n",
       "416   [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $10.99   \n",
       "418   [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $10.99   \n",
       "786   [{'name': 'Kindle', 'price': '$9.99', 'url': '...             NaN   \n",
       "1760  [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $10.99   \n",
       "1694  [{'name': 'Kindle', 'price': '$0.90', 'url': '...          $10.99   \n",
       "1749  [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $10.99   \n",
       "747   [{'name': 'Kindle', 'price': '$0.00', 'url': '...             NaN   \n",
       "1466  [{'name': 'Kindle', 'price': '$0.00', 'url': '...             NaN   \n",
       "299   [{'name': 'Kindle', 'price': '$9.99', 'url': '...             NaN   \n",
       "397   [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $28.69   \n",
       "2241  [{'name': 'Kindle', 'price': '$0.49', 'url': '...           $9.95   \n",
       "1274  [{'name': 'Kindle', 'price': '$0.00', 'url': '...          $18.61   \n",
       "2196  [{'name': 'Kindle', 'price': '$6.65', 'url': '...          $19.59   \n",
       "1248  [{'name': 'Kindle', 'price': '$6.65', 'url': '...          $19.59   \n",
       "2016  [{'name': 'Kindle', 'price': '$6.65', 'url': '...          $19.59   \n",
       "1099  [{'name': 'Kindle', 'price': '$11.99', 'url': ...          $13.99   \n",
       "1098  [{'name': 'Kindle', 'price': '$11.99', 'url': ...             NaN   \n",
       "2072  [{'name': 'Audiobook', 'price': 'Audiobook $0....          $14.76   \n",
       "123   [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $14.76   \n",
       "565   [{'name': 'Kindle', 'price': '$9.99', 'url': '...             NaN   \n",
       "2078  [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $14.76   \n",
       "1514  [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $14.04   \n",
       "\n",
       "     kindle_price mass_market_paperback_price paperback_price  \\\n",
       "101        $13.99                         NaN             NaN   \n",
       "269        $13.99                         NaN          $17.63   \n",
       "2198       $13.99                         NaN          $17.63   \n",
       "416         $9.99                         NaN          $11.63   \n",
       "418         $9.99                       $7.48             NaN   \n",
       "786         $9.99                       $7.48          $11.63   \n",
       "1760        $9.99                       $7.48          $11.63   \n",
       "1694        $0.90                       $7.48           $8.75   \n",
       "1749        $9.99                       $7.48          $11.63   \n",
       "747         $0.00                         NaN             NaN   \n",
       "1466        $0.00                         NaN           $6.99   \n",
       "299         $9.99                       $8.49          $15.99   \n",
       "397         $9.99                         NaN          $15.99   \n",
       "2241        $0.49                       $3.95           $3.58   \n",
       "1274        $0.00                       $3.95             NaN   \n",
       "2196        $6.65                         NaN          $10.80   \n",
       "1248        $6.65                         NaN             NaN   \n",
       "2016        $6.65                         NaN          $10.80   \n",
       "1099       $11.99                         NaN             NaN   \n",
       "1098       $11.99                         NaN          $11.99   \n",
       "2072          NaN                         NaN           $9.34   \n",
       "123         $9.99                         NaN             NaN   \n",
       "565         $9.99                         NaN           $9.34   \n",
       "2078        $9.99                         NaN           $9.34   \n",
       "1514        $9.99                      $17.11          $14.49   \n",
       "\n",
       "     perfect_paperback_price              rating  reviews_count  \\\n",
       "101                      NaN  4.8 out of 5 stars          43801   \n",
       "269                      NaN  4.8 out of 5 stars          43801   \n",
       "2198                     NaN  4.8 out of 5 stars          43801   \n",
       "416                      NaN  4.7 out of 5 stars          55581   \n",
       "418                      NaN  4.7 out of 5 stars          55581   \n",
       "786                      NaN  4.7 out of 5 stars          55581   \n",
       "1760                     NaN  4.7 out of 5 stars          55581   \n",
       "1694                     NaN  4.7 out of 5 stars          55581   \n",
       "1749                     NaN  4.7 out of 5 stars          55582   \n",
       "747                      NaN  4.5 out of 5 stars          13067   \n",
       "1466                     NaN  4.5 out of 5 stars          13067   \n",
       "299                      NaN  4.8 out of 5 stars          13908   \n",
       "397                      NaN  4.8 out of 5 stars          13908   \n",
       "2241                     NaN  4.7 out of 5 stars          12865   \n",
       "1274                     NaN  4.7 out of 5 stars          12865   \n",
       "2196                     NaN  4.7 out of 5 stars          35943   \n",
       "1248                     NaN  4.7 out of 5 stars          35941   \n",
       "2016                     NaN  4.7 out of 5 stars          35943   \n",
       "1099                     NaN  4.4 out of 5 stars          21250   \n",
       "1098                     NaN  4.4 out of 5 stars          21250   \n",
       "2072                     NaN  4.6 out of 5 stars          37254   \n",
       "123                      NaN  4.6 out of 5 stars          37254   \n",
       "565                      NaN  4.6 out of 5 stars          37254   \n",
       "2078                     NaN  4.6 out of 5 stars          37254   \n",
       "1514                     NaN  4.4 out of 5 stars          10398   \n",
       "\n",
       "                                                  title  \n",
       "101             12 Rules for Life: An Antidote to Chaos  \n",
       "269             12 Rules for Life: An Antidote to Chaos  \n",
       "2198            12 Rules for Life: An Antidote to Chaos  \n",
       "416                                                1984  \n",
       "418                                                1984  \n",
       "786                                                1984  \n",
       "1760                                               1984  \n",
       "1694                                               1984  \n",
       "1749                                               1984  \n",
       "747   A Beginner's Guide to the Stock Market: Everyt...  \n",
       "1466  A Beginner's Guide to the Stock Market: Everyt...  \n",
       "299              A Breath of Snow and Ashes (Outlander)  \n",
       "397              A Breath of Snow and Ashes (Outlander)  \n",
       "2241                                  A Christmas Carol  \n",
       "1274                                  A Christmas Carol  \n",
       "2196                        A Court of Thorns and Roses  \n",
       "1248                        A Court of Thorns and Roses  \n",
       "2016                        A Court of Thorns and Roses  \n",
       "1099                   A Gambling Man (An Archer Novel)  \n",
       "1098                   A Gambling Man (An Archer Novel)  \n",
       "2072                     A Gentleman in Moscow: A Novel  \n",
       "123                      A Gentleman in Moscow: A Novel  \n",
       "565                      A Gentleman in Moscow: A Novel  \n",
       "2078                     A Gentleman in Moscow: A Novel  \n",
       "1514                   A Long Petal of the Sea: A Novel  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_duplicated_books = amazon_books_updated[amazon_books_updated.duplicated(subset=['title'], keep=False)].sort_values(by=['title'])\n",
    "all_duplicated_books.head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "826a4acd",
   "metadata": {},
   "source": [
    "It seems that the duplicates have quite similar values for all columns. What we found noteworthy:\n",
    "1. The prices with missing values differ between duplicates.\n",
    "2. Categories are not equal between duplicates.\n",
    "\n",
    "We are curious why those duplicates happened and our first hunch on what could be useful is looking at the url of each duplicate. The cleaned data set doesn't have the url values but we can still inspect the original data set to inspect those urls.\n",
    "\n",
    "Let's use one of the books with duplicates, Jordan B. Peterson's 12 Rules for Life: An Antidote to Chaos, as a starting point."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "dbdca317",
   "metadata": {
    "scrolled": true
   },
   "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>title</th>\n",
       "      <th>brand</th>\n",
       "      <th>url</th>\n",
       "      <th>categories</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>12 Rules for Life: An Antidote to Chaos</td>\n",
       "      <td>Jordan B. Peterson</td>\n",
       "      <td>https://www.amazon.com/dp/0141988517</td>\n",
       "      <td>[\"Books\",\"Business &amp; Money\",\"Job Hunting &amp; Careers\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>12 Rules for Life: An Antidote to Chaos</td>\n",
       "      <td>Jordan B. Peterson</td>\n",
       "      <td>https://www.amazon.com/dp/0345816021</td>\n",
       "      <td>[\"Books\",\"Health, Fitness &amp; Dieting\",\"Psychology &amp; Counseling\"]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2198</th>\n",
       "      <td>12 Rules for Life: An Antidote to Chaos</td>\n",
       "      <td>Jordan B. Peterson (Author, Narrator), Norman Doidge MD - foreword (Author), Random House Canada...</td>\n",
       "      <td>https://www.amazon.com/dp/B0797Y87JC</td>\n",
       "      <td>[\"Books\",\"Health, Fitness &amp; Dieting\",\"Psychology &amp; Counseling\",\"Applied Psychology\"]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        title  \\\n",
       "101   12 Rules for Life: An Antidote to Chaos   \n",
       "269   12 Rules for Life: An Antidote to Chaos   \n",
       "2198  12 Rules for Life: An Antidote to Chaos   \n",
       "\n",
       "                                                                                                    brand  \\\n",
       "101                                                                                    Jordan B. Peterson   \n",
       "269                                                                                    Jordan B. Peterson   \n",
       "2198  Jordan B. Peterson (Author, Narrator), Norman Doidge MD - foreword (Author), Random House Canada...   \n",
       "\n",
       "                                       url  \\\n",
       "101   https://www.amazon.com/dp/0141988517   \n",
       "269   https://www.amazon.com/dp/0345816021   \n",
       "2198  https://www.amazon.com/dp/B0797Y87JC   \n",
       "\n",
       "                                                                                categories  \n",
       "101                                   [\"Books\",\"Business & Money\",\"Job Hunting & Careers\"]  \n",
       "269                        [\"Books\",\"Health, Fitness & Dieting\",\"Psychology & Counseling\"]  \n",
       "2198  [\"Books\",\"Health, Fitness & Dieting\",\"Psychology & Counseling\",\"Applied Psychology\"]  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change the column width so it can accommodate all the text in categories\n",
    "pd.set_option(\n",
    "    'display.max_colwidth', 100\n",
    ")\n",
    "\n",
    "# Show all books with the author/brand name containing Jordan\n",
    "amazon_books[amazon_books[\"brand\"].str.match(\"Jordan\").fillna(False)][[\"title\", \"brand\", \"url\", \"categories\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b94bdaf",
   "metadata": {},
   "source": [
    "There are three separate urls for the book. When we visited each url, we found that all pointed to the same book but the format is different: 12 Rules for Life:\n",
    "\n",
    "| URL                                                               | Format    |\n",
    "|:------------------------------------------------------------------|:----------|\n",
    "| https://www.amazon.com/dp/0141988517 | Paperback |\n",
    "| https://www.amazon.com/dp/B0797Y87JC                              | Audiobook |\n",
    "| https://www.amazon.com/dp/0345816021                              | Hardcover |\n",
    "\n",
    "The patterns seem to be the same for the other books we inspected.\n",
    "\n",
    "We can't afford to do simple duplicate reconciliation, e.g. keep first or last duplicates, because each duplicate represents different format of the book and we might miss some nuanced information available only in that format. We can illustrate this potential missing information by referring to the above table again. The first row which is for the paperback format has \"Job Hunting & Careers\" as a category while that category is missing for the other two urls; likewise, the third row has the \"Applied Psychology\" category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "6d42f9b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\"Books\",\"Business & Money\",\"Job Hunting & Careers\"]\n",
      "[\"Books\",\"Health, Fitness & Dieting\",\"Psychology & Counseling\"]\n",
      "[\"Books\",\"Health, Fitness & Dieting\",\"Psychology & Counseling\",\"Applied Psychology\"]\n"
     ]
    }
   ],
   "source": [
    "jordan_books = amazon_books[amazon_books[\"brand\"].str.match(\"Jordan\").fillna(False)][[\"title\", \"brand\", \"categories\", \"url\"]]\n",
    "\n",
    "for _, listing in jordan_books.iterrows():\n",
    "    print(listing[\"categories\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5d2951c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "426c05a0",
   "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>brand</th>\n",
       "      <th>categories</th>\n",
       "      <th>format</th>\n",
       "      <th>hardcover_price</th>\n",
       "      <th>kindle_price</th>\n",
       "      <th>mass_market_paperback_price</th>\n",
       "      <th>paperback_price</th>\n",
       "      <th>perfect_paperback_price</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews_count</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Karin Slaughter</td>\n",
       "      <td>[\"Books\",\"Mystery, Thriller &amp; Suspense\",\"Thril...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$37.70</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$7.98</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.3 out of 5 stars</td>\n",
       "      <td>10050</td>\n",
       "      <td>Pretty Girls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2036</th>\n",
       "      <td>Karin Slaughter (Author), Kathleen Early (Narr...</td>\n",
       "      <td>[\"Books\",\"Mystery, Thriller &amp; Suspense\",\"Myste...</td>\n",
       "      <td>[{'name': 'Kindle', 'price': '$9.99', 'url': '...</td>\n",
       "      <td>$37.68</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$9.99</td>\n",
       "      <td>$7.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.3 out of 5 stars</td>\n",
       "      <td>10050</td>\n",
       "      <td>Pretty Girls</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  brand  \\\n",
       "11                                      Karin Slaughter   \n",
       "2036  Karin Slaughter (Author), Kathleen Early (Narr...   \n",
       "\n",
       "                                             categories  \\\n",
       "11    [\"Books\",\"Mystery, Thriller & Suspense\",\"Thril...   \n",
       "2036  [\"Books\",\"Mystery, Thriller & Suspense\",\"Myste...   \n",
       "\n",
       "                                                 format hardcover_price  \\\n",
       "11    [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $37.70   \n",
       "2036  [{'name': 'Kindle', 'price': '$9.99', 'url': '...          $37.68   \n",
       "\n",
       "     kindle_price mass_market_paperback_price paperback_price  \\\n",
       "11          $9.99                         NaN           $7.98   \n",
       "2036        $9.99                       $9.99           $7.99   \n",
       "\n",
       "     perfect_paperback_price              rating  reviews_count         title  \n",
       "11                       NaN  4.3 out of 5 stars          10050  Pretty Girls  \n",
       "2036                     NaN  4.3 out of 5 stars          10050  Pretty Girls  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "amazon_books_updated[amazon_books_updated.duplicated(subset=['title'], keep=False)][\"title\"]\n",
    "amazon_books_updated[amazon_books_updated[\"title\"] == \"Pretty Girls\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67f5af87",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "9ec4f30b",
   "metadata": {},
   "source": [
    "## References\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e8e7eed",
   "metadata": {},
   "source": [
    "https://en.wikipedia.org/wiki/Hardcover\n",
    "\n",
    "https://www.julesbuono.com/paperback-vs-mass-market-paperback/\n",
    "\n",
    "https://en.wikipedia.org/wiki/Paperback#Mass-market\n",
    "\n",
    "https://en.wikipedia.org/wiki/Amazon_Kindle\n",
    "\n",
    "https://sellercentral.amazon.com/forums/t/what-is-a-perfect-paperback-in-listing-a-book/236479\n",
    "\n",
    "https://en.wikibooks.org/wiki/Bookbinding/Perfect_binding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "680bff1d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}