book-recommender-system.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cf518e5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d73fe6ac",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas in d:\\python_anaconda\\lib\\site-packages (1.3.4)\n",
      "Requirement already satisfied: pytz>=2017.3 in d:\\python_anaconda\\lib\\site-packages (from pandas) (2021.3)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in d:\\python_anaconda\\lib\\site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: numpy>=1.17.3 in d:\\python_anaconda\\lib\\site-packages (from pandas) (1.20.3)\n",
      "Requirement already satisfied: six>=1.5 in d:\\python_anaconda\\lib\\site-packages (from python-dateutil>=2.7.3->pandas) (1.16.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c0b135f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fabbffb1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\python_anaconda\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3444: DtypeWarning: Columns (3) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "books = pd.read_csv('books.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b291d1dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings = pd.read_csv('ratings.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c0328f7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "users = pd.read_csv('users.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b4b1b6ff",
   "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>ISBN</th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>Year-Of-Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Image-URL-S</th>\n",
       "      <th>Image-URL-M</th>\n",
       "      <th>Image-URL-L</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0195153448</td>\n",
       "      <td>Classical Mythology</td>\n",
       "      <td>Mark P. O. Morford</td>\n",
       "      <td>2002</td>\n",
       "      <td>Oxford University Press</td>\n",
       "      <td>http://images.amazon.com/images/P/0195153448.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0195153448.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0195153448.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0002005018</td>\n",
       "      <td>Clara Callan</td>\n",
       "      <td>Richard Bruce Wright</td>\n",
       "      <td>2001</td>\n",
       "      <td>HarperFlamingo Canada</td>\n",
       "      <td>http://images.amazon.com/images/P/0002005018.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0002005018.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0002005018.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0060973129</td>\n",
       "      <td>Decision in Normandy</td>\n",
       "      <td>Carlo D'Este</td>\n",
       "      <td>1991</td>\n",
       "      <td>HarperPerennial</td>\n",
       "      <td>http://images.amazon.com/images/P/0060973129.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0060973129.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0060973129.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0374157065</td>\n",
       "      <td>Flu: The Story of the Great Influenza Pandemic...</td>\n",
       "      <td>Gina Bari Kolata</td>\n",
       "      <td>1999</td>\n",
       "      <td>Farrar Straus Giroux</td>\n",
       "      <td>http://images.amazon.com/images/P/0374157065.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0374157065.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0374157065.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0393045218</td>\n",
       "      <td>The Mummies of Urumchi</td>\n",
       "      <td>E. J. W. Barber</td>\n",
       "      <td>1999</td>\n",
       "      <td>W. W. Norton &amp;amp; Company</td>\n",
       "      <td>http://images.amazon.com/images/P/0393045218.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0393045218.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0393045218.0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ISBN                                         Book-Title  \\\n",
       "0  0195153448                                Classical Mythology   \n",
       "1  0002005018                                       Clara Callan   \n",
       "2  0060973129                               Decision in Normandy   \n",
       "3  0374157065  Flu: The Story of the Great Influenza Pandemic...   \n",
       "4  0393045218                             The Mummies of Urumchi   \n",
       "\n",
       "            Book-Author Year-Of-Publication                   Publisher  \\\n",
       "0    Mark P. O. Morford                2002     Oxford University Press   \n",
       "1  Richard Bruce Wright                2001       HarperFlamingo Canada   \n",
       "2          Carlo D'Este                1991             HarperPerennial   \n",
       "3      Gina Bari Kolata                1999        Farrar Straus Giroux   \n",
       "4       E. J. W. Barber                1999  W. W. Norton &amp; Company   \n",
       "\n",
       "                                         Image-URL-S  \\\n",
       "0  http://images.amazon.com/images/P/0195153448.0...   \n",
       "1  http://images.amazon.com/images/P/0002005018.0...   \n",
       "2  http://images.amazon.com/images/P/0060973129.0...   \n",
       "3  http://images.amazon.com/images/P/0374157065.0...   \n",
       "4  http://images.amazon.com/images/P/0393045218.0...   \n",
       "\n",
       "                                         Image-URL-M  \\\n",
       "0  http://images.amazon.com/images/P/0195153448.0...   \n",
       "1  http://images.amazon.com/images/P/0002005018.0...   \n",
       "2  http://images.amazon.com/images/P/0060973129.0...   \n",
       "3  http://images.amazon.com/images/P/0374157065.0...   \n",
       "4  http://images.amazon.com/images/P/0393045218.0...   \n",
       "\n",
       "                                         Image-URL-L  \n",
       "0  http://images.amazon.com/images/P/0195153448.0...  \n",
       "1  http://images.amazon.com/images/P/0002005018.0...  \n",
       "2  http://images.amazon.com/images/P/0060973129.0...  \n",
       "3  http://images.amazon.com/images/P/0374157065.0...  \n",
       "4  http://images.amazon.com/images/P/0393045218.0...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6b2420d4",
   "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>User-ID</th>\n",
       "      <th>ISBN</th>\n",
       "      <th>Book-Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>276725</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>276726</td>\n",
       "      <td>0155061224</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>276727</td>\n",
       "      <td>0446520802</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>276729</td>\n",
       "      <td>052165615X</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>276729</td>\n",
       "      <td>0521795028</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User-ID        ISBN  Book-Rating\n",
       "0   276725  034545104X            0\n",
       "1   276726  0155061224            5\n",
       "2   276727  0446520802            0\n",
       "3   276729  052165615X            3\n",
       "4   276729  0521795028            6"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b273bd2d",
   "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>User-ID</th>\n",
       "      <th>Location</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>nyc, new york, usa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>stockton, california, usa</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>moscow, yukon territory, russia</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>porto, v.n.gaia, portugal</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>farnborough, hants, united kingdom</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User-ID                            Location   Age\n",
       "0        1                  nyc, new york, usa   NaN\n",
       "1        2           stockton, california, usa  18.0\n",
       "2        3     moscow, yukon territory, russia   NaN\n",
       "3        4           porto, v.n.gaia, portugal  17.0\n",
       "4        5  farnborough, hants, united kingdom   NaN"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0171ca7d",
   "metadata": {},
   "source": [
    "# Popularity based recommender\n",
    "#### Books over rating of 250 votes and Top 50 Books, merging books and ratings on column \"ISBN\" because common attribite in both"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73a15613",
   "metadata": {},
   "source": [
    "#    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6575a0cc",
   "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>User-ID</th>\n",
       "      <th>ISBN</th>\n",
       "      <th>Book-Rating</th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>Year-Of-Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Image-URL-S</th>\n",
       "      <th>Image-URL-M</th>\n",
       "      <th>Image-URL-L</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>276725</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>0</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2313</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>5</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6543</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>0</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8680</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>5</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10314</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>9</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031131</th>\n",
       "      <td>276688</td>\n",
       "      <td>0517145553</td>\n",
       "      <td>0</td>\n",
       "      <td>Mostly Harmless</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1995</td>\n",
       "      <td>Random House Value Pub</td>\n",
       "      <td>http://images.amazon.com/images/P/0517145553.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0517145553.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0517145553.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031132</th>\n",
       "      <td>276688</td>\n",
       "      <td>1575660792</td>\n",
       "      <td>7</td>\n",
       "      <td>Gray Matter</td>\n",
       "      <td>Shirley Kennett</td>\n",
       "      <td>1996</td>\n",
       "      <td>Kensington Publishing Corporation</td>\n",
       "      <td>http://images.amazon.com/images/P/1575660792.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1575660792.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1575660792.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031133</th>\n",
       "      <td>276690</td>\n",
       "      <td>0590907301</td>\n",
       "      <td>0</td>\n",
       "      <td>Triplet Trouble and the Class Trip (Triplet Tr...</td>\n",
       "      <td>Debbie Dadey</td>\n",
       "      <td>1997</td>\n",
       "      <td>Apple</td>\n",
       "      <td>http://images.amazon.com/images/P/0590907301.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0590907301.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0590907301.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031134</th>\n",
       "      <td>276704</td>\n",
       "      <td>0679752714</td>\n",
       "      <td>0</td>\n",
       "      <td>A Desert of Pure Feeling (Vintage Contemporaries)</td>\n",
       "      <td>Judith Freeman</td>\n",
       "      <td>1997</td>\n",
       "      <td>Vintage Books USA</td>\n",
       "      <td>http://images.amazon.com/images/P/0679752714.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0679752714.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0679752714.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031135</th>\n",
       "      <td>276704</td>\n",
       "      <td>0806917695</td>\n",
       "      <td>5</td>\n",
       "      <td>Perplexing Lateral Thinking Puzzles: Scholasti...</td>\n",
       "      <td>Paul Sloane</td>\n",
       "      <td>1997</td>\n",
       "      <td>Sterling Publishing</td>\n",
       "      <td>http://images.amazon.com/images/P/0806917695.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0806917695.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0806917695.0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1031136 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         User-ID        ISBN  Book-Rating  \\\n",
       "0         276725  034545104X            0   \n",
       "1           2313  034545104X            5   \n",
       "2           6543  034545104X            0   \n",
       "3           8680  034545104X            5   \n",
       "4          10314  034545104X            9   \n",
       "...          ...         ...          ...   \n",
       "1031131   276688  0517145553            0   \n",
       "1031132   276688  1575660792            7   \n",
       "1031133   276690  0590907301            0   \n",
       "1031134   276704  0679752714            0   \n",
       "1031135   276704  0806917695            5   \n",
       "\n",
       "                                                Book-Title      Book-Author  \\\n",
       "0                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "1                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "2                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "3                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "4                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "...                                                    ...              ...   \n",
       "1031131                                    Mostly Harmless    Douglas Adams   \n",
       "1031132                                        Gray Matter  Shirley Kennett   \n",
       "1031133  Triplet Trouble and the Class Trip (Triplet Tr...     Debbie Dadey   \n",
       "1031134  A Desert of Pure Feeling (Vintage Contemporaries)   Judith Freeman   \n",
       "1031135  Perplexing Lateral Thinking Puzzles: Scholasti...      Paul Sloane   \n",
       "\n",
       "        Year-Of-Publication                          Publisher  \\\n",
       "0                      2002                   Ballantine Books   \n",
       "1                      2002                   Ballantine Books   \n",
       "2                      2002                   Ballantine Books   \n",
       "3                      2002                   Ballantine Books   \n",
       "4                      2002                   Ballantine Books   \n",
       "...                     ...                                ...   \n",
       "1031131                1995             Random House Value Pub   \n",
       "1031132                1996  Kensington Publishing Corporation   \n",
       "1031133                1997                              Apple   \n",
       "1031134                1997                  Vintage Books USA   \n",
       "1031135                1997                Sterling Publishing   \n",
       "\n",
       "                                               Image-URL-S  \\\n",
       "0        http://images.amazon.com/images/P/034545104X.0...   \n",
       "1        http://images.amazon.com/images/P/034545104X.0...   \n",
       "2        http://images.amazon.com/images/P/034545104X.0...   \n",
       "3        http://images.amazon.com/images/P/034545104X.0...   \n",
       "4        http://images.amazon.com/images/P/034545104X.0...   \n",
       "...                                                    ...   \n",
       "1031131  http://images.amazon.com/images/P/0517145553.0...   \n",
       "1031132  http://images.amazon.com/images/P/1575660792.0...   \n",
       "1031133  http://images.amazon.com/images/P/0590907301.0...   \n",
       "1031134  http://images.amazon.com/images/P/0679752714.0...   \n",
       "1031135  http://images.amazon.com/images/P/0806917695.0...   \n",
       "\n",
       "                                               Image-URL-M  \\\n",
       "0        http://images.amazon.com/images/P/034545104X.0...   \n",
       "1        http://images.amazon.com/images/P/034545104X.0...   \n",
       "2        http://images.amazon.com/images/P/034545104X.0...   \n",
       "3        http://images.amazon.com/images/P/034545104X.0...   \n",
       "4        http://images.amazon.com/images/P/034545104X.0...   \n",
       "...                                                    ...   \n",
       "1031131  http://images.amazon.com/images/P/0517145553.0...   \n",
       "1031132  http://images.amazon.com/images/P/1575660792.0...   \n",
       "1031133  http://images.amazon.com/images/P/0590907301.0...   \n",
       "1031134  http://images.amazon.com/images/P/0679752714.0...   \n",
       "1031135  http://images.amazon.com/images/P/0806917695.0...   \n",
       "\n",
       "                                               Image-URL-L  \n",
       "0        http://images.amazon.com/images/P/034545104X.0...  \n",
       "1        http://images.amazon.com/images/P/034545104X.0...  \n",
       "2        http://images.amazon.com/images/P/034545104X.0...  \n",
       "3        http://images.amazon.com/images/P/034545104X.0...  \n",
       "4        http://images.amazon.com/images/P/034545104X.0...  \n",
       "...                                                    ...  \n",
       "1031131  http://images.amazon.com/images/P/0517145553.0...  \n",
       "1031132  http://images.amazon.com/images/P/1575660792.0...  \n",
       "1031133  http://images.amazon.com/images/P/0590907301.0...  \n",
       "1031134  http://images.amazon.com/images/P/0679752714.0...  \n",
       "1031135  http://images.amazon.com/images/P/0806917695.0...  \n",
       "\n",
       "[1031136 rows x 10 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings.merge(books,on='ISBN')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ea81136d",
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings_with_name = ratings.merge(books,on='ISBN')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b29faef4",
   "metadata": {},
   "source": [
    "#### less data than expected beacuse there are some books which are not in ratings so there is slighlty less number of rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3d753b4a",
   "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>User-ID</th>\n",
       "      <th>ISBN</th>\n",
       "      <th>Book-Rating</th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>Year-Of-Publication</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Image-URL-S</th>\n",
       "      <th>Image-URL-M</th>\n",
       "      <th>Image-URL-L</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>276725</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>0</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2313</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>5</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6543</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>0</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8680</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>5</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10314</td>\n",
       "      <td>034545104X</td>\n",
       "      <td>9</td>\n",
       "      <td>Flesh Tones: A Novel</td>\n",
       "      <td>M. J. Rose</td>\n",
       "      <td>2002</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/034545104X.0...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031131</th>\n",
       "      <td>276688</td>\n",
       "      <td>0517145553</td>\n",
       "      <td>0</td>\n",
       "      <td>Mostly Harmless</td>\n",
       "      <td>Douglas Adams</td>\n",
       "      <td>1995</td>\n",
       "      <td>Random House Value Pub</td>\n",
       "      <td>http://images.amazon.com/images/P/0517145553.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0517145553.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0517145553.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031132</th>\n",
       "      <td>276688</td>\n",
       "      <td>1575660792</td>\n",
       "      <td>7</td>\n",
       "      <td>Gray Matter</td>\n",
       "      <td>Shirley Kennett</td>\n",
       "      <td>1996</td>\n",
       "      <td>Kensington Publishing Corporation</td>\n",
       "      <td>http://images.amazon.com/images/P/1575660792.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1575660792.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1575660792.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031133</th>\n",
       "      <td>276690</td>\n",
       "      <td>0590907301</td>\n",
       "      <td>0</td>\n",
       "      <td>Triplet Trouble and the Class Trip (Triplet Tr...</td>\n",
       "      <td>Debbie Dadey</td>\n",
       "      <td>1997</td>\n",
       "      <td>Apple</td>\n",
       "      <td>http://images.amazon.com/images/P/0590907301.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0590907301.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0590907301.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031134</th>\n",
       "      <td>276704</td>\n",
       "      <td>0679752714</td>\n",
       "      <td>0</td>\n",
       "      <td>A Desert of Pure Feeling (Vintage Contemporaries)</td>\n",
       "      <td>Judith Freeman</td>\n",
       "      <td>1997</td>\n",
       "      <td>Vintage Books USA</td>\n",
       "      <td>http://images.amazon.com/images/P/0679752714.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0679752714.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0679752714.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031135</th>\n",
       "      <td>276704</td>\n",
       "      <td>0806917695</td>\n",
       "      <td>5</td>\n",
       "      <td>Perplexing Lateral Thinking Puzzles: Scholasti...</td>\n",
       "      <td>Paul Sloane</td>\n",
       "      <td>1997</td>\n",
       "      <td>Sterling Publishing</td>\n",
       "      <td>http://images.amazon.com/images/P/0806917695.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0806917695.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0806917695.0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1031136 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         User-ID        ISBN  Book-Rating  \\\n",
       "0         276725  034545104X            0   \n",
       "1           2313  034545104X            5   \n",
       "2           6543  034545104X            0   \n",
       "3           8680  034545104X            5   \n",
       "4          10314  034545104X            9   \n",
       "...          ...         ...          ...   \n",
       "1031131   276688  0517145553            0   \n",
       "1031132   276688  1575660792            7   \n",
       "1031133   276690  0590907301            0   \n",
       "1031134   276704  0679752714            0   \n",
       "1031135   276704  0806917695            5   \n",
       "\n",
       "                                                Book-Title      Book-Author  \\\n",
       "0                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "1                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "2                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "3                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "4                                     Flesh Tones: A Novel       M. J. Rose   \n",
       "...                                                    ...              ...   \n",
       "1031131                                    Mostly Harmless    Douglas Adams   \n",
       "1031132                                        Gray Matter  Shirley Kennett   \n",
       "1031133  Triplet Trouble and the Class Trip (Triplet Tr...     Debbie Dadey   \n",
       "1031134  A Desert of Pure Feeling (Vintage Contemporaries)   Judith Freeman   \n",
       "1031135  Perplexing Lateral Thinking Puzzles: Scholasti...      Paul Sloane   \n",
       "\n",
       "        Year-Of-Publication                          Publisher  \\\n",
       "0                      2002                   Ballantine Books   \n",
       "1                      2002                   Ballantine Books   \n",
       "2                      2002                   Ballantine Books   \n",
       "3                      2002                   Ballantine Books   \n",
       "4                      2002                   Ballantine Books   \n",
       "...                     ...                                ...   \n",
       "1031131                1995             Random House Value Pub   \n",
       "1031132                1996  Kensington Publishing Corporation   \n",
       "1031133                1997                              Apple   \n",
       "1031134                1997                  Vintage Books USA   \n",
       "1031135                1997                Sterling Publishing   \n",
       "\n",
       "                                               Image-URL-S  \\\n",
       "0        http://images.amazon.com/images/P/034545104X.0...   \n",
       "1        http://images.amazon.com/images/P/034545104X.0...   \n",
       "2        http://images.amazon.com/images/P/034545104X.0...   \n",
       "3        http://images.amazon.com/images/P/034545104X.0...   \n",
       "4        http://images.amazon.com/images/P/034545104X.0...   \n",
       "...                                                    ...   \n",
       "1031131  http://images.amazon.com/images/P/0517145553.0...   \n",
       "1031132  http://images.amazon.com/images/P/1575660792.0...   \n",
       "1031133  http://images.amazon.com/images/P/0590907301.0...   \n",
       "1031134  http://images.amazon.com/images/P/0679752714.0...   \n",
       "1031135  http://images.amazon.com/images/P/0806917695.0...   \n",
       "\n",
       "                                               Image-URL-M  \\\n",
       "0        http://images.amazon.com/images/P/034545104X.0...   \n",
       "1        http://images.amazon.com/images/P/034545104X.0...   \n",
       "2        http://images.amazon.com/images/P/034545104X.0...   \n",
       "3        http://images.amazon.com/images/P/034545104X.0...   \n",
       "4        http://images.amazon.com/images/P/034545104X.0...   \n",
       "...                                                    ...   \n",
       "1031131  http://images.amazon.com/images/P/0517145553.0...   \n",
       "1031132  http://images.amazon.com/images/P/1575660792.0...   \n",
       "1031133  http://images.amazon.com/images/P/0590907301.0...   \n",
       "1031134  http://images.amazon.com/images/P/0679752714.0...   \n",
       "1031135  http://images.amazon.com/images/P/0806917695.0...   \n",
       "\n",
       "                                               Image-URL-L  \n",
       "0        http://images.amazon.com/images/P/034545104X.0...  \n",
       "1        http://images.amazon.com/images/P/034545104X.0...  \n",
       "2        http://images.amazon.com/images/P/034545104X.0...  \n",
       "3        http://images.amazon.com/images/P/034545104X.0...  \n",
       "4        http://images.amazon.com/images/P/034545104X.0...  \n",
       "...                                                    ...  \n",
       "1031131  http://images.amazon.com/images/P/0517145553.0...  \n",
       "1031132  http://images.amazon.com/images/P/1575660792.0...  \n",
       "1031133  http://images.amazon.com/images/P/0590907301.0...  \n",
       "1031134  http://images.amazon.com/images/P/0679752714.0...  \n",
       "1031135  http://images.amazon.com/images/P/0806917695.0...  \n",
       "\n",
       "[1031136 rows x 10 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_with_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c7c5ead5",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_rating_df = ratings_with_name.groupby('Book-Title').count()['Book-Rating'].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7e9c9f1d",
   "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>Book-Title</th>\n",
       "      <th>Book-Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Light in the Storm: The Civil War Diary of ...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Always Have Popsicles</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple Magic (The Collector's series)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ask Lily (Young Women of Faith: Lily Series, ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Beyond IBM: Leadership Marketing and Finance ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241066</th>\n",
       "      <td>Ã?Â?lpiraten.</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241067</th>\n",
       "      <td>Ã?Â?rger mit Produkt X. Roman.</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241068</th>\n",
       "      <td>Ã?Â?sterlich leben.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241069</th>\n",
       "      <td>Ã?Â?stlich der Berge.</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241070</th>\n",
       "      <td>Ã?Â?thique en toc</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>241071 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  Book-Rating\n",
       "0        A Light in the Storm: The Civil War Diary of ...            4\n",
       "1                                   Always Have Popsicles            1\n",
       "2                    Apple Magic (The Collector's series)            1\n",
       "3        Ask Lily (Young Women of Faith: Lily Series, ...            1\n",
       "4        Beyond IBM: Leadership Marketing and Finance ...            1\n",
       "...                                                   ...          ...\n",
       "241066                                      Ã?Â?lpiraten.            2\n",
       "241067                     Ã?Â?rger mit Produkt X. Roman.            4\n",
       "241068                                Ã?Â?sterlich leben.            1\n",
       "241069                              Ã?Â?stlich der Berge.            3\n",
       "241070                                  Ã?Â?thique en toc            2\n",
       "\n",
       "[241071 rows x 2 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_rating_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5c3fb7f",
   "metadata": {},
   "source": [
    "#### here the book rating is showing that how many ratings are given and to whom"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d0cdb7d",
   "metadata": {},
   "source": [
    "#### now changing the title of book rating to number of ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e96470a8",
   "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>Book-Title</th>\n",
       "      <th>num_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Light in the Storm: The Civil War Diary of ...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Always Have Popsicles</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple Magic (The Collector's series)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ask Lily (Young Women of Faith: Lily Series, ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Beyond IBM: Leadership Marketing and Finance ...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241066</th>\n",
       "      <td>Ã?Â?lpiraten.</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241067</th>\n",
       "      <td>Ã?Â?rger mit Produkt X. Roman.</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241068</th>\n",
       "      <td>Ã?Â?sterlich leben.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241069</th>\n",
       "      <td>Ã?Â?stlich der Berge.</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241070</th>\n",
       "      <td>Ã?Â?thique en toc</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>241071 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  num_ratings\n",
       "0        A Light in the Storm: The Civil War Diary of ...            4\n",
       "1                                   Always Have Popsicles            1\n",
       "2                    Apple Magic (The Collector's series)            1\n",
       "3        Ask Lily (Young Women of Faith: Lily Series, ...            1\n",
       "4        Beyond IBM: Leadership Marketing and Finance ...            1\n",
       "...                                                   ...          ...\n",
       "241066                                      Ã?Â?lpiraten.            2\n",
       "241067                     Ã?Â?rger mit Produkt X. Roman.            4\n",
       "241068                                Ã?Â?sterlich leben.            1\n",
       "241069                              Ã?Â?stlich der Berge.            3\n",
       "241070                                  Ã?Â?thique en toc            2\n",
       "\n",
       "[241071 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_rating_df.rename(columns={'Book-Rating':'num_ratings'}, inplace=True)\n",
    "num_rating_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "080268e4",
   "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>Book-Title</th>\n",
       "      <th>avg_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Light in the Storm: The Civil War Diary of ...</td>\n",
       "      <td>2.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Always Have Popsicles</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple Magic (The Collector's series)</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ask Lily (Young Women of Faith: Lily Series, ...</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Beyond IBM: Leadership Marketing and Finance ...</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241066</th>\n",
       "      <td>Ã?Â?lpiraten.</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241067</th>\n",
       "      <td>Ã?Â?rger mit Produkt X. Roman.</td>\n",
       "      <td>5.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241068</th>\n",
       "      <td>Ã?Â?sterlich leben.</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241069</th>\n",
       "      <td>Ã?Â?stlich der Berge.</td>\n",
       "      <td>2.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241070</th>\n",
       "      <td>Ã?Â?thique en toc</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>241071 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  avg_ratings\n",
       "0        A Light in the Storm: The Civil War Diary of ...     2.250000\n",
       "1                                   Always Have Popsicles     0.000000\n",
       "2                    Apple Magic (The Collector's series)     0.000000\n",
       "3        Ask Lily (Young Women of Faith: Lily Series, ...     8.000000\n",
       "4        Beyond IBM: Leadership Marketing and Finance ...     0.000000\n",
       "...                                                   ...          ...\n",
       "241066                                      Ã?Â?lpiraten.     0.000000\n",
       "241067                     Ã?Â?rger mit Produkt X. Roman.     5.250000\n",
       "241068                                Ã?Â?sterlich leben.     7.000000\n",
       "241069                              Ã?Â?stlich der Berge.     2.666667\n",
       "241070                                  Ã?Â?thique en toc     4.000000\n",
       "\n",
       "[241071 rows x 2 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg_rating_df = ratings_with_name.groupby('Book-Title').mean()['Book-Rating'].reset_index()\n",
    "avg_rating_df.rename(columns={'Book-Rating':'avg_ratings'}, inplace=True)\n",
    "avg_rating_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef3df793",
   "metadata": {},
   "source": [
    "#### Done the calculation of avg (addition of all the ratings / total number of ratings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7ea425da",
   "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>Book-Title</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>avg_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Light in the Storm: The Civil War Diary of ...</td>\n",
       "      <td>4</td>\n",
       "      <td>2.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Always Have Popsicles</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple Magic (The Collector's series)</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ask Lily (Young Women of Faith: Lily Series, ...</td>\n",
       "      <td>1</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Beyond IBM: Leadership Marketing and Finance ...</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241066</th>\n",
       "      <td>Ã?Â?lpiraten.</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241067</th>\n",
       "      <td>Ã?Â?rger mit Produkt X. Roman.</td>\n",
       "      <td>4</td>\n",
       "      <td>5.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241068</th>\n",
       "      <td>Ã?Â?sterlich leben.</td>\n",
       "      <td>1</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241069</th>\n",
       "      <td>Ã?Â?stlich der Berge.</td>\n",
       "      <td>3</td>\n",
       "      <td>2.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241070</th>\n",
       "      <td>Ã?Â?thique en toc</td>\n",
       "      <td>2</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>241071 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  num_ratings  \\\n",
       "0        A Light in the Storm: The Civil War Diary of ...            4   \n",
       "1                                   Always Have Popsicles            1   \n",
       "2                    Apple Magic (The Collector's series)            1   \n",
       "3        Ask Lily (Young Women of Faith: Lily Series, ...            1   \n",
       "4        Beyond IBM: Leadership Marketing and Finance ...            1   \n",
       "...                                                   ...          ...   \n",
       "241066                                      Ã?Â?lpiraten.            2   \n",
       "241067                     Ã?Â?rger mit Produkt X. Roman.            4   \n",
       "241068                                Ã?Â?sterlich leben.            1   \n",
       "241069                              Ã?Â?stlich der Berge.            3   \n",
       "241070                                  Ã?Â?thique en toc            2   \n",
       "\n",
       "        avg_ratings  \n",
       "0          2.250000  \n",
       "1          0.000000  \n",
       "2          0.000000  \n",
       "3          8.000000  \n",
       "4          0.000000  \n",
       "...             ...  \n",
       "241066     0.000000  \n",
       "241067     5.250000  \n",
       "241068     7.000000  \n",
       "241069     2.666667  \n",
       "241070     4.000000  \n",
       "\n",
       "[241071 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popularity_df = num_rating_df.merge(avg_rating_df, on='Book-Title')\n",
    "popularity_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "bd155a00",
   "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>Book-Title</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>avg_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>764</th>\n",
       "      <td>1984</td>\n",
       "      <td>284</td>\n",
       "      <td>4.454225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>818</th>\n",
       "      <td>1st to Die: A Novel</td>\n",
       "      <td>509</td>\n",
       "      <td>3.575639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1048</th>\n",
       "      <td>2nd Chance</td>\n",
       "      <td>356</td>\n",
       "      <td>3.269663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1760</th>\n",
       "      <td>A Bend in the Road</td>\n",
       "      <td>346</td>\n",
       "      <td>3.364162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2281</th>\n",
       "      <td>A Child Called \\It\\\": One Child's Courage to S...</td>\n",
       "      <td>265</td>\n",
       "      <td>4.086792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233850</th>\n",
       "      <td>White Oleander : A Novel</td>\n",
       "      <td>387</td>\n",
       "      <td>3.506460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233851</th>\n",
       "      <td>White Oleander : A Novel (Oprah's Book Club)</td>\n",
       "      <td>356</td>\n",
       "      <td>3.772472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234740</th>\n",
       "      <td>Wicked: The Life and Times of the Wicked Witch...</td>\n",
       "      <td>326</td>\n",
       "      <td>3.766871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234951</th>\n",
       "      <td>Wild Animus</td>\n",
       "      <td>2502</td>\n",
       "      <td>1.019584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240860</th>\n",
       "      <td>\\O\\\" Is for Outlaw\"</td>\n",
       "      <td>269</td>\n",
       "      <td>2.988848</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>186 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  num_ratings  \\\n",
       "764                                                  1984          284   \n",
       "818                                   1st to Die: A Novel          509   \n",
       "1048                                           2nd Chance          356   \n",
       "1760                                   A Bend in the Road          346   \n",
       "2281    A Child Called \\It\\\": One Child's Courage to S...          265   \n",
       "...                                                   ...          ...   \n",
       "233850                           White Oleander : A Novel          387   \n",
       "233851       White Oleander : A Novel (Oprah's Book Club)          356   \n",
       "234740  Wicked: The Life and Times of the Wicked Witch...          326   \n",
       "234951                                        Wild Animus         2502   \n",
       "240860                                \\O\\\" Is for Outlaw\"          269   \n",
       "\n",
       "        avg_ratings  \n",
       "764        4.454225  \n",
       "818        3.575639  \n",
       "1048       3.269663  \n",
       "1760       3.364162  \n",
       "2281       4.086792  \n",
       "...             ...  \n",
       "233850     3.506460  \n",
       "233851     3.772472  \n",
       "234740     3.766871  \n",
       "234951     1.019584  \n",
       "240860     2.988848  \n",
       "\n",
       "[186 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popularity_df[popularity_df['num_ratings']>=250]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "111b23f5",
   "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>Book-Title</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>avg_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80434</th>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Book 3)</td>\n",
       "      <td>428</td>\n",
       "      <td>5.852804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80422</th>\n",
       "      <td>Harry Potter and the Goblet of Fire (Book 4)</td>\n",
       "      <td>387</td>\n",
       "      <td>5.824289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80441</th>\n",
       "      <td>Harry Potter and the Sorcerer's Stone (Book 1)</td>\n",
       "      <td>278</td>\n",
       "      <td>5.737410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80426</th>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Boo...</td>\n",
       "      <td>347</td>\n",
       "      <td>5.501441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80414</th>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Book 2)</td>\n",
       "      <td>556</td>\n",
       "      <td>5.183453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227701</th>\n",
       "      <td>Vinegar Hill (Oprah's Book Club (Paperback))</td>\n",
       "      <td>265</td>\n",
       "      <td>2.245283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233635</th>\n",
       "      <td>Whispers</td>\n",
       "      <td>286</td>\n",
       "      <td>2.199301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143377</th>\n",
       "      <td>Presumed Innocent</td>\n",
       "      <td>294</td>\n",
       "      <td>2.139456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94382</th>\n",
       "      <td>Isle of Dogs</td>\n",
       "      <td>288</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234951</th>\n",
       "      <td>Wild Animus</td>\n",
       "      <td>2502</td>\n",
       "      <td>1.019584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>186 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  num_ratings  \\\n",
       "80434   Harry Potter and the Prisoner of Azkaban (Book 3)          428   \n",
       "80422        Harry Potter and the Goblet of Fire (Book 4)          387   \n",
       "80441      Harry Potter and the Sorcerer's Stone (Book 1)          278   \n",
       "80426   Harry Potter and the Order of the Phoenix (Boo...          347   \n",
       "80414    Harry Potter and the Chamber of Secrets (Book 2)          556   \n",
       "...                                                   ...          ...   \n",
       "227701       Vinegar Hill (Oprah's Book Club (Paperback))          265   \n",
       "233635                                           Whispers          286   \n",
       "143377                                  Presumed Innocent          294   \n",
       "94382                                        Isle of Dogs          288   \n",
       "234951                                        Wild Animus         2502   \n",
       "\n",
       "        avg_ratings  \n",
       "80434      5.852804  \n",
       "80422      5.824289  \n",
       "80441      5.737410  \n",
       "80426      5.501441  \n",
       "80414      5.183453  \n",
       "...             ...  \n",
       "227701     2.245283  \n",
       "233635     2.199301  \n",
       "143377     2.139456  \n",
       "94382      2.000000  \n",
       "234951     1.019584  \n",
       "\n",
       "[186 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popularity_df[popularity_df['num_ratings']>=250].sort_values('avg_ratings',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "761e6985",
   "metadata": {},
   "outputs": [],
   "source": [
    "popular_df = popularity_df[popularity_df['num_ratings']>=250].sort_values('avg_ratings',ascending=False).head(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3988b799",
   "metadata": {},
   "source": [
    "#### these are the top 50 books "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "8d1f3a97",
   "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>Book-Title</th>\n",
       "      <th>num_ratings</th>\n",
       "      <th>avg_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80434</th>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Book 3)</td>\n",
       "      <td>428</td>\n",
       "      <td>5.852804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80422</th>\n",
       "      <td>Harry Potter and the Goblet of Fire (Book 4)</td>\n",
       "      <td>387</td>\n",
       "      <td>5.824289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80441</th>\n",
       "      <td>Harry Potter and the Sorcerer's Stone (Book 1)</td>\n",
       "      <td>278</td>\n",
       "      <td>5.737410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80426</th>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Boo...</td>\n",
       "      <td>347</td>\n",
       "      <td>5.501441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80414</th>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Book 2)</td>\n",
       "      <td>556</td>\n",
       "      <td>5.183453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191612</th>\n",
       "      <td>The Hobbit : The Enchanting Prelude to The Lor...</td>\n",
       "      <td>281</td>\n",
       "      <td>5.007117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187377</th>\n",
       "      <td>The Fellowship of the Ring (The Lord of the Ri...</td>\n",
       "      <td>368</td>\n",
       "      <td>4.948370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80445</th>\n",
       "      <td>Harry Potter and the Sorcerer's Stone (Harry P...</td>\n",
       "      <td>575</td>\n",
       "      <td>4.895652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211384</th>\n",
       "      <td>The Two Towers (The Lord of the Rings, Part 2)</td>\n",
       "      <td>260</td>\n",
       "      <td>4.880769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219741</th>\n",
       "      <td>To Kill a Mockingbird</td>\n",
       "      <td>510</td>\n",
       "      <td>4.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183573</th>\n",
       "      <td>The Da Vinci Code</td>\n",
       "      <td>898</td>\n",
       "      <td>4.642539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187880</th>\n",
       "      <td>The Five People You Meet in Heaven</td>\n",
       "      <td>430</td>\n",
       "      <td>4.551163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180556</th>\n",
       "      <td>The Catcher in the Rye</td>\n",
       "      <td>449</td>\n",
       "      <td>4.545657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196326</th>\n",
       "      <td>The Lovely Bones: A Novel</td>\n",
       "      <td>1295</td>\n",
       "      <td>4.468726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>764</th>\n",
       "      <td>1984</td>\n",
       "      <td>284</td>\n",
       "      <td>4.454225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144165</th>\n",
       "      <td>Prodigal Summer: A Novel</td>\n",
       "      <td>253</td>\n",
       "      <td>4.450593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128670</th>\n",
       "      <td>Neverwhere</td>\n",
       "      <td>265</td>\n",
       "      <td>4.449057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206502</th>\n",
       "      <td>The Secret Life of Bees</td>\n",
       "      <td>774</td>\n",
       "      <td>4.447028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168719</th>\n",
       "      <td>Stupid White Men ...and Other Sorry Excuses fo...</td>\n",
       "      <td>283</td>\n",
       "      <td>4.356890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223135</th>\n",
       "      <td>Tuesdays with Morrie: An Old Man, a Young Man,...</td>\n",
       "      <td>493</td>\n",
       "      <td>4.354970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204387</th>\n",
       "      <td>The Red Tent (Bestselling Backlist)</td>\n",
       "      <td>723</td>\n",
       "      <td>4.334716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191589</th>\n",
       "      <td>The Hitchhiker's Guide to the Galaxy</td>\n",
       "      <td>268</td>\n",
       "      <td>4.328358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129379</th>\n",
       "      <td>Nickel and Dimed: On (Not) Getting By in America</td>\n",
       "      <td>335</td>\n",
       "      <td>4.289552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93381</th>\n",
       "      <td>Into the Wild</td>\n",
       "      <td>252</td>\n",
       "      <td>4.273810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63867</th>\n",
       "      <td>Fahrenheit 451</td>\n",
       "      <td>409</td>\n",
       "      <td>4.264059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74750</th>\n",
       "      <td>Girl with a Pearl Earring</td>\n",
       "      <td>526</td>\n",
       "      <td>4.218631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136145</th>\n",
       "      <td>Outlander</td>\n",
       "      <td>283</td>\n",
       "      <td>4.173145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233370</th>\n",
       "      <td>Where the Heart Is (Oprah's Book Club (Paperba...</td>\n",
       "      <td>585</td>\n",
       "      <td>4.105983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156102</th>\n",
       "      <td>Seabiscuit: An American Legend</td>\n",
       "      <td>275</td>\n",
       "      <td>4.098182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107962</th>\n",
       "      <td>Life of Pi</td>\n",
       "      <td>664</td>\n",
       "      <td>4.088855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176845</th>\n",
       "      <td>The Bean Trees</td>\n",
       "      <td>389</td>\n",
       "      <td>4.087404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2281</th>\n",
       "      <td>A Child Called \\It\\\": One Child's Courage to S...</td>\n",
       "      <td>265</td>\n",
       "      <td>4.086792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8434</th>\n",
       "      <td>ANGELA'S ASHES</td>\n",
       "      <td>279</td>\n",
       "      <td>4.075269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76343</th>\n",
       "      <td>Good in Bed</td>\n",
       "      <td>490</td>\n",
       "      <td>4.055102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64931</th>\n",
       "      <td>Fast Food Nation: The Dark Side of the All-Ame...</td>\n",
       "      <td>321</td>\n",
       "      <td>4.037383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12700</th>\n",
       "      <td>American Gods</td>\n",
       "      <td>302</td>\n",
       "      <td>4.006623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161645</th>\n",
       "      <td>Skipping Christmas</td>\n",
       "      <td>322</td>\n",
       "      <td>4.006211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105777</th>\n",
       "      <td>Left Behind: A Novel of the Earth's Last Days ...</td>\n",
       "      <td>318</td>\n",
       "      <td>4.003145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189551</th>\n",
       "      <td>The Golden Compass (His Dark Materials, Book 1)</td>\n",
       "      <td>336</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181679</th>\n",
       "      <td>The Color Purple</td>\n",
       "      <td>314</td>\n",
       "      <td>3.964968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160336</th>\n",
       "      <td>Silence of the Lambs</td>\n",
       "      <td>256</td>\n",
       "      <td>3.960938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8752</th>\n",
       "      <td>About a Boy</td>\n",
       "      <td>262</td>\n",
       "      <td>3.900763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158138</th>\n",
       "      <td>Seven Up (A Stephanie Plum Novel)</td>\n",
       "      <td>278</td>\n",
       "      <td>3.888489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175097</th>\n",
       "      <td>The Alchemist: A Fable About Following Your Dream</td>\n",
       "      <td>266</td>\n",
       "      <td>3.875940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80069</th>\n",
       "      <td>Hard Eight : A Stephanie Plum Novel (A Stephan...</td>\n",
       "      <td>269</td>\n",
       "      <td>3.825279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170101</th>\n",
       "      <td>Suzanne's Diary for Nicholas</td>\n",
       "      <td>457</td>\n",
       "      <td>3.820569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111073</th>\n",
       "      <td>Lord of the Flies</td>\n",
       "      <td>259</td>\n",
       "      <td>3.818533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5664</th>\n",
       "      <td>A Prayer for Owen Meany</td>\n",
       "      <td>413</td>\n",
       "      <td>3.796610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212070</th>\n",
       "      <td>The Vampire Lestat (Vampire Chronicles, Book II)</td>\n",
       "      <td>301</td>\n",
       "      <td>3.777409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233851</th>\n",
       "      <td>White Oleander : A Novel (Oprah's Book Club)</td>\n",
       "      <td>356</td>\n",
       "      <td>3.772472</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  num_ratings  \\\n",
       "80434   Harry Potter and the Prisoner of Azkaban (Book 3)          428   \n",
       "80422        Harry Potter and the Goblet of Fire (Book 4)          387   \n",
       "80441      Harry Potter and the Sorcerer's Stone (Book 1)          278   \n",
       "80426   Harry Potter and the Order of the Phoenix (Boo...          347   \n",
       "80414    Harry Potter and the Chamber of Secrets (Book 2)          556   \n",
       "191612  The Hobbit : The Enchanting Prelude to The Lor...          281   \n",
       "187377  The Fellowship of the Ring (The Lord of the Ri...          368   \n",
       "80445   Harry Potter and the Sorcerer's Stone (Harry P...          575   \n",
       "211384     The Two Towers (The Lord of the Rings, Part 2)          260   \n",
       "219741                              To Kill a Mockingbird          510   \n",
       "183573                                  The Da Vinci Code          898   \n",
       "187880                 The Five People You Meet in Heaven          430   \n",
       "180556                             The Catcher in the Rye          449   \n",
       "196326                          The Lovely Bones: A Novel         1295   \n",
       "764                                                  1984          284   \n",
       "144165                           Prodigal Summer: A Novel          253   \n",
       "128670                                         Neverwhere          265   \n",
       "206502                            The Secret Life of Bees          774   \n",
       "168719  Stupid White Men ...and Other Sorry Excuses fo...          283   \n",
       "223135  Tuesdays with Morrie: An Old Man, a Young Man,...          493   \n",
       "204387                The Red Tent (Bestselling Backlist)          723   \n",
       "191589               The Hitchhiker's Guide to the Galaxy          268   \n",
       "129379   Nickel and Dimed: On (Not) Getting By in America          335   \n",
       "93381                                       Into the Wild          252   \n",
       "63867                                      Fahrenheit 451          409   \n",
       "74750                           Girl with a Pearl Earring          526   \n",
       "136145                                          Outlander          283   \n",
       "233370  Where the Heart Is (Oprah's Book Club (Paperba...          585   \n",
       "156102                     Seabiscuit: An American Legend          275   \n",
       "107962                                         Life of Pi          664   \n",
       "176845                                     The Bean Trees          389   \n",
       "2281    A Child Called \\It\\\": One Child's Courage to S...          265   \n",
       "8434                                       ANGELA'S ASHES          279   \n",
       "76343                                         Good in Bed          490   \n",
       "64931   Fast Food Nation: The Dark Side of the All-Ame...          321   \n",
       "12700                                       American Gods          302   \n",
       "161645                                 Skipping Christmas          322   \n",
       "105777  Left Behind: A Novel of the Earth's Last Days ...          318   \n",
       "189551    The Golden Compass (His Dark Materials, Book 1)          336   \n",
       "181679                                   The Color Purple          314   \n",
       "160336                               Silence of the Lambs          256   \n",
       "8752                                          About a Boy          262   \n",
       "158138                  Seven Up (A Stephanie Plum Novel)          278   \n",
       "175097  The Alchemist: A Fable About Following Your Dream          266   \n",
       "80069   Hard Eight : A Stephanie Plum Novel (A Stephan...          269   \n",
       "170101                       Suzanne's Diary for Nicholas          457   \n",
       "111073                                  Lord of the Flies          259   \n",
       "5664                              A Prayer for Owen Meany          413   \n",
       "212070   The Vampire Lestat (Vampire Chronicles, Book II)          301   \n",
       "233851       White Oleander : A Novel (Oprah's Book Club)          356   \n",
       "\n",
       "        avg_ratings  \n",
       "80434      5.852804  \n",
       "80422      5.824289  \n",
       "80441      5.737410  \n",
       "80426      5.501441  \n",
       "80414      5.183453  \n",
       "191612     5.007117  \n",
       "187377     4.948370  \n",
       "80445      4.895652  \n",
       "211384     4.880769  \n",
       "219741     4.700000  \n",
       "183573     4.642539  \n",
       "187880     4.551163  \n",
       "180556     4.545657  \n",
       "196326     4.468726  \n",
       "764        4.454225  \n",
       "144165     4.450593  \n",
       "128670     4.449057  \n",
       "206502     4.447028  \n",
       "168719     4.356890  \n",
       "223135     4.354970  \n",
       "204387     4.334716  \n",
       "191589     4.328358  \n",
       "129379     4.289552  \n",
       "93381      4.273810  \n",
       "63867      4.264059  \n",
       "74750      4.218631  \n",
       "136145     4.173145  \n",
       "233370     4.105983  \n",
       "156102     4.098182  \n",
       "107962     4.088855  \n",
       "176845     4.087404  \n",
       "2281       4.086792  \n",
       "8434       4.075269  \n",
       "76343      4.055102  \n",
       "64931      4.037383  \n",
       "12700      4.006623  \n",
       "161645     4.006211  \n",
       "105777     4.003145  \n",
       "189551     4.000000  \n",
       "181679     3.964968  \n",
       "160336     3.960938  \n",
       "8752       3.900763  \n",
       "158138     3.888489  \n",
       "175097     3.875940  \n",
       "80069      3.825279  \n",
       "170101     3.820569  \n",
       "111073     3.818533  \n",
       "5664       3.796610  \n",
       "212070     3.777409  \n",
       "233851     3.772472  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popular_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "1e7741d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "popular_df=popular_df.merge(books,on='Book-Title').drop_duplicates('Book-Title')[['Book-Title','Book-Author','Image-URL-M','num_ratings','avg_ratings']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "1f09eb00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://images.amazon.com/images/P/0439136350.01.MZZZZZZZ.jpg'"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popular_df['Image-URL-M'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85e423ce",
   "metadata": {},
   "source": [
    "# Colaborative Filtering based recommender system\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e6b2b895",
   "metadata": {},
   "outputs": [],
   "source": [
    "x=ratings_with_name.groupby('User-ID').count()['Book-Rating']>200\n",
    "top_users=x[x].index\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bd47803",
   "metadata": {},
   "source": [
    "### users who have rated more than 200 books"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "94917d88",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_rating=ratings_with_name[ratings_with_name['User-ID'].isin(top_users)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "503f6fc1",
   "metadata": {},
   "source": [
    "### users from the ratings_with_name table who have rated more than 200 books"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "6e2efa1a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['1984', '1st to Die: A Novel', '2nd Chance', '4 Blondes',\n",
       "       'A Bend in the Road', 'A Case of Need',\n",
       "       'A Child Called \\It\\\": One Child's Courage to Survive\"',\n",
       "       'A Civil Action', 'A Day Late and a Dollar Short', 'A Fine Balance',\n",
       "       ...\n",
       "       'Winter Solstice', 'Wish You Well', 'Without Remorse',\n",
       "       'Wizard and Glass (The Dark Tower, Book 4)', 'Wuthering Heights',\n",
       "       'Year of Wonders', 'You Belong To Me',\n",
       "       'Zen and the Art of Motorcycle Maintenance: An Inquiry into Values',\n",
       "       'Zoya', '\\O\\\" Is for Outlaw\"'],\n",
       "      dtype='object', name='Book-Title', length=706)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y=filtered_rating.groupby('Book-Title').count()['Book-Rating']>=50\n",
    "famous_books=y[y].index\n",
    "famous_books"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e07176d3",
   "metadata": {},
   "source": [
    "### these are the list of top 50 books suggested by raters of top 200 books"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "feea6168",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_ratings=filtered_rating[filtered_rating['Book-Title'].isin(famous_books)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "4298f00f",
   "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>User-ID</th>\n",
       "      <th>254</th>\n",
       "      <th>2276</th>\n",
       "      <th>2766</th>\n",
       "      <th>2977</th>\n",
       "      <th>3363</th>\n",
       "      <th>4017</th>\n",
       "      <th>4385</th>\n",
       "      <th>6251</th>\n",
       "      <th>6323</th>\n",
       "      <th>6543</th>\n",
       "      <th>...</th>\n",
       "      <th>271705</th>\n",
       "      <th>273979</th>\n",
       "      <th>274004</th>\n",
       "      <th>274061</th>\n",
       "      <th>274301</th>\n",
       "      <th>274308</th>\n",
       "      <th>275970</th>\n",
       "      <th>277427</th>\n",
       "      <th>277639</th>\n",
       "      <th>278418</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Book-Title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1984</th>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1st to Die: A Novel</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2nd Chance</th>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4 Blondes</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A Bend in the Road</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</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>Year of Wonders</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>You Belong To Me</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zen and the Art of Motorcycle Maintenance: An Inquiry into Values</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zoya</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\\O\\\" Is for Outlaw\"</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>706 rows × 810 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "User-ID                                             254     2276    2766    \\\n",
       "Book-Title                                                                   \n",
       "1984                                                   9.0     0.0     0.0   \n",
       "1st to Die: A Novel                                    0.0     0.0     0.0   \n",
       "2nd Chance                                             0.0    10.0     0.0   \n",
       "4 Blondes                                              0.0     0.0     0.0   \n",
       "A Bend in the Road                                     0.0     0.0     7.0   \n",
       "...                                                    ...     ...     ...   \n",
       "Year of Wonders                                        0.0     0.0     0.0   \n",
       "You Belong To Me                                       0.0     0.0     0.0   \n",
       "Zen and the Art of Motorcycle Maintenance: An I...     0.0     0.0     0.0   \n",
       "Zoya                                                   0.0     0.0     0.0   \n",
       "\\O\\\" Is for Outlaw\"                                    0.0     0.0     0.0   \n",
       "\n",
       "User-ID                                             2977    3363    4017    \\\n",
       "Book-Title                                                                   \n",
       "1984                                                   0.0     0.0     0.0   \n",
       "1st to Die: A Novel                                    0.0     0.0     0.0   \n",
       "2nd Chance                                             0.0     0.0     0.0   \n",
       "4 Blondes                                              0.0     0.0     0.0   \n",
       "A Bend in the Road                                     0.0     0.0     0.0   \n",
       "...                                                    ...     ...     ...   \n",
       "Year of Wonders                                        7.0     0.0     0.0   \n",
       "You Belong To Me                                       0.0     0.0     0.0   \n",
       "Zen and the Art of Motorcycle Maintenance: An I...     0.0     0.0     0.0   \n",
       "Zoya                                                   0.0     0.0     0.0   \n",
       "\\O\\\" Is for Outlaw\"                                    0.0     0.0     0.0   \n",
       "\n",
       "User-ID                                             4385    6251    6323    \\\n",
       "Book-Title                                                                   \n",
       "1984                                                   0.0     0.0     0.0   \n",
       "1st to Die: A Novel                                    0.0     0.0     0.0   \n",
       "2nd Chance                                             0.0     0.0     0.0   \n",
       "4 Blondes                                              0.0     0.0     0.0   \n",
       "A Bend in the Road                                     0.0     0.0     0.0   \n",
       "...                                                    ...     ...     ...   \n",
       "Year of Wonders                                        0.0     0.0     0.0   \n",
       "You Belong To Me                                       0.0     0.0     0.0   \n",
       "Zen and the Art of Motorcycle Maintenance: An I...     0.0     0.0     0.0   \n",
       "Zoya                                                   0.0     0.0     0.0   \n",
       "\\O\\\" Is for Outlaw\"                                    0.0     0.0     0.0   \n",
       "\n",
       "User-ID                                             6543    ...  271705  \\\n",
       "Book-Title                                                  ...           \n",
       "1984                                                   0.0  ...    10.0   \n",
       "1st to Die: A Novel                                    9.0  ...     0.0   \n",
       "2nd Chance                                             0.0  ...     0.0   \n",
       "4 Blondes                                              0.0  ...     0.0   \n",
       "A Bend in the Road                                     0.0  ...     0.0   \n",
       "...                                                    ...  ...     ...   \n",
       "Year of Wonders                                        0.0  ...     0.0   \n",
       "You Belong To Me                                       0.0  ...     0.0   \n",
       "Zen and the Art of Motorcycle Maintenance: An I...     0.0  ...     0.0   \n",
       "Zoya                                                   0.0  ...     0.0   \n",
       "\\O\\\" Is for Outlaw\"                                    0.0  ...     0.0   \n",
       "\n",
       "User-ID                                             273979  274004  274061  \\\n",
       "Book-Title                                                                   \n",
       "1984                                                   0.0     0.0     0.0   \n",
       "1st to Die: A Novel                                    0.0     0.0     0.0   \n",
       "2nd Chance                                             0.0     0.0     0.0   \n",
       "4 Blondes                                              0.0     0.0     0.0   \n",
       "A Bend in the Road                                     0.0     0.0     0.0   \n",
       "...                                                    ...     ...     ...   \n",
       "Year of Wonders                                        9.0     0.0     0.0   \n",
       "You Belong To Me                                       0.0     0.0     0.0   \n",
       "Zen and the Art of Motorcycle Maintenance: An I...     0.0     0.0     0.0   \n",
       "Zoya                                                   0.0     0.0     0.0   \n",
       "\\O\\\" Is for Outlaw\"                                    0.0     0.0     0.0   \n",
       "\n",
       "User-ID                                             274301  274308  275970  \\\n",
       "Book-Title                                                                   \n",
       "1984                                                   0.0     0.0     0.0   \n",
       "1st to Die: A Novel                                    0.0     0.0     0.0   \n",
       "2nd Chance                                             0.0     0.0     0.0   \n",
       "4 Blondes                                              0.0     0.0     0.0   \n",
       "A Bend in the Road                                     0.0     0.0     0.0   \n",
       "...                                                    ...     ...     ...   \n",
       "Year of Wonders                                        0.0     0.0     0.0   \n",
       "You Belong To Me                                       0.0     0.0     0.0   \n",
       "Zen and the Art of Motorcycle Maintenance: An I...     0.0     0.0     0.0   \n",
       "Zoya                                                   0.0     0.0     0.0   \n",
       "\\O\\\" Is for Outlaw\"                                    8.0     0.0     0.0   \n",
       "\n",
       "User-ID                                             277427  277639  278418  \n",
       "Book-Title                                                                  \n",
       "1984                                                   0.0     0.0     0.0  \n",
       "1st to Die: A Novel                                    0.0     0.0     0.0  \n",
       "2nd Chance                                             0.0     0.0     0.0  \n",
       "4 Blondes                                              0.0     0.0     0.0  \n",
       "A Bend in the Road                                     0.0     0.0     0.0  \n",
       "...                                                    ...     ...     ...  \n",
       "Year of Wonders                                        0.0     0.0     0.0  \n",
       "You Belong To Me                                       0.0     0.0     0.0  \n",
       "Zen and the Art of Motorcycle Maintenance: An I...     0.0     0.0     0.0  \n",
       "Zoya                                                   0.0     0.0     0.0  \n",
       "\\O\\\" Is for Outlaw\"                                    0.0     0.0     0.0  \n",
       "\n",
       "[706 rows x 810 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt=final_ratings.pivot_table(index='Book-Title',columns='User-ID',values='Book-Rating')\n",
    "pt.fillna(0,inplace=True)\n",
    "pt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acc8a8fe",
   "metadata": {},
   "source": [
    "### this is the desired frame having only those users who rated >200 books and only those books having 50+ ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "0aab1d6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "pickle.dump(popular_df,open('popular.pkl','wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "90c88be4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics.pairwise import cosine_similarity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "2e274b62",
   "metadata": {},
   "outputs": [],
   "source": [
    "similarity_scores=cosine_similarity(pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "666e5f36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(706, 706)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "similarity_scores.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8586ee4",
   "metadata": {},
   "source": [
    "### similarity_score contains the similarity between all 706 books of pt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "957f4bb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def recommend(book_name):\n",
    "    #index fetch for that book\n",
    "    index=np.where(pt.index==book_name)[0][0]\n",
    "    #get the most similar book to bookname in sorted way\n",
    "    similar_items=sorted(list(enumerate(similarity_scores[index])),key=lambda x:x[1],reverse=True)[1:6]\n",
    "    data=[]\n",
    "    for i in similar_items:\n",
    "        item=[]\n",
    "        temp_df=books[books['Book-Title'] == pt.index[i[0]]]\n",
    "        item.extend(list(temp_df.drop_duplicates('Book-Title')['Book-Title'].values))\n",
    "        item.extend(list(temp_df.drop_duplicates('Book-Title')['Book-Author'].values))\n",
    "        item.extend(list(temp_df.drop_duplicates('Book-Title')['Image-URL-M'].values))\n",
    "        data.append(item)\n",
    "    return data  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "a0a8e171",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['Nights in Rodanthe',\n",
       "  'Nicholas Sparks',\n",
       "  'http://images.amazon.com/images/P/0446531332.01.MZZZZZZZ.jpg'],\n",
       " ['The Mulberry Tree',\n",
       "  'Jude Deveraux',\n",
       "  'http://images.amazon.com/images/P/0743437640.01.MZZZZZZZ.jpg'],\n",
       " ['A Walk to Remember',\n",
       "  'Nicholas Sparks',\n",
       "  'http://images.amazon.com/images/P/0446608955.01.MZZZZZZZ.jpg'],\n",
       " [\"River's End\",\n",
       "  'Nora Roberts',\n",
       "  'http://images.amazon.com/images/P/0515127833.01.MZZZZZZZ.jpg'],\n",
       " ['Nightmares &amp; Dreamscapes',\n",
       "  'Stephen King',\n",
       "  'http://images.amazon.com/images/P/0451180232.01.MZZZZZZZ.jpg']]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recommend('Message in a Bottle')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55ed4c8b",
   "metadata": {},
   "source": [
    "### getting the 5 most similar books recommendation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "5fa215fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(pt,open('pt.pkl','wb'))\n",
    "pickle.dump(books,open('books.pkl','wb'))\n",
    "pickle.dump(similarity_scores,open('similarity_scores.pkl','wb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bb8aadd",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.2 64-bit",
   "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.10.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "fb4569285eef3a3450cb62085a5b1e0da4bce0af555edc33dcf29baf3acc1368"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}