2. Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fc75304c",
   "metadata": {},
   "source": [
    "# DATA CLEANING"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f88e548",
   "metadata": {},
   "source": [
    "Adjust column names, handle duplicates and missing values, standardize text fields, merge datasets, and save the new dataframes.\n",
    "\n",
    "⚠️ **NOTE:** As I said in the previous notebook, in this notebook I share the data cleaning process I conducted. Due to potential future issues and time constraints, I've decided to focus primarily on utilizing only the \"books_df\" dataset and the one I personally scraped from the Goodreads website, while many other datasets included in this notebook may not be used."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcc9828b",
   "metadata": {},
   "source": [
    "## Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7796691e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "from IPython.display import Image, display\n",
    "from IPython.display import HTML\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87bf3d5a",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "317c2a81",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Skipping line 3350: expected 12 fields, saw 13\n",
      "Skipping line 4704: expected 12 fields, saw 13\n",
      "Skipping line 5879: expected 12 fields, saw 13\n",
      "Skipping line 8981: expected 12 fields, saw 13\n",
      "\n"
     ]
    }
   ],
   "source": [
    "books_df = pd.read_csv('GoodReads_100k_books.csv')\n",
    "ratings_df = pd.read_csv('Ratings.csv')\n",
    "books_df_2 = pd.read_csv('Books.csv')\n",
    "users_df = pd.read_csv('Users.csv')\n",
    "top100_df = pd.read_csv('Top-100 Trending Books.csv')\n",
    "customers_df = pd.read_csv('customer reviews.csv')\n",
    "goodreads_df = pd.read_csv('goodreads_webscrap.csv')\n",
    "\n",
    "# Attempt to read a CSV file named 'gr_books.csv' into a Pandas DataFrame.\n",
    "try:\n",
    "    gr_books_df = pd.read_csv('gr_books.csv', error_bad_lines=False)\n",
    "    \n",
    "# If there is an exception (specifically, a ParserError) while parsing the CSV file,\n",
    "# print an error message that includes the details of the exception.\n",
    "except pd.errors.ParserError as e:\n",
    "    print(f\"Error parsing 'gr_books.csv': {e}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83a5c893",
   "metadata": {},
   "source": [
    "## Check the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "60f4154d",
   "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>author</th>\n",
       "      <th>bookformat</th>\n",
       "      <th>desc</th>\n",
       "      <th>genre</th>\n",
       "      <th>img</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>link</th>\n",
       "      <th>pages</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews</th>\n",
       "      <th>title</th>\n",
       "      <th>totalratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Laurence M. Hauptman</td>\n",
       "      <td>Hardcover</td>\n",
       "      <td>Reveals that several hundred thousand Indians ...</td>\n",
       "      <td>History,Military History,Civil War,American Hi...</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>002914180X</td>\n",
       "      <td>9.78E+12</td>\n",
       "      <td>https://goodreads.com/book/show/1001053.Betwee...</td>\n",
       "      <td>0</td>\n",
       "      <td>3.52</td>\n",
       "      <td>5</td>\n",
       "      <td>Between Two Fires: American Indians in the Civ...</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Charlotte Fiell,Emmanuelle Dirix</td>\n",
       "      <td>Paperback</td>\n",
       "      <td>Fashion Sourcebook - 1920s is the first book i...</td>\n",
       "      <td>Couture,Fashion,Historical,Art,Nonfiction</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>1906863482</td>\n",
       "      <td>9.78E+12</td>\n",
       "      <td>https://goodreads.com/book/show/10010552-fashi...</td>\n",
       "      <td>576</td>\n",
       "      <td>4.51</td>\n",
       "      <td>6</td>\n",
       "      <td>Fashion Sourcebook 1920s</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andy Anderson</td>\n",
       "      <td>Paperback</td>\n",
       "      <td>The seminal history and analysis of the Hungar...</td>\n",
       "      <td>Politics,History</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>948984147</td>\n",
       "      <td>9.78E+12</td>\n",
       "      <td>https://goodreads.com/book/show/1001077.Hungar...</td>\n",
       "      <td>124</td>\n",
       "      <td>4.15</td>\n",
       "      <td>2</td>\n",
       "      <td>Hungary 56</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carlotta R. Anderson</td>\n",
       "      <td>Hardcover</td>\n",
       "      <td>\"All-American Anarchist\" chronicles the life a...</td>\n",
       "      <td>Labor,History</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>814327079</td>\n",
       "      <td>9.78E+12</td>\n",
       "      <td>https://goodreads.com/book/show/1001079.All_Am...</td>\n",
       "      <td>324</td>\n",
       "      <td>3.83</td>\n",
       "      <td>1</td>\n",
       "      <td>All-American Anarchist: Joseph A. Labadie and ...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jean Leveille</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Aujourdâ€™hui, lâ€™oiseau nous invite Ã  sa ta...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>2761920813</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://goodreads.com/book/show/10010880-les-o...</td>\n",
       "      <td>177</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Les oiseaux gourmands</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             author bookformat  \\\n",
       "0              Laurence M. Hauptman  Hardcover   \n",
       "1  Charlotte Fiell,Emmanuelle Dirix  Paperback   \n",
       "2                     Andy Anderson  Paperback   \n",
       "3              Carlotta R. Anderson  Hardcover   \n",
       "4                     Jean Leveille        NaN   \n",
       "\n",
       "                                                desc  \\\n",
       "0  Reveals that several hundred thousand Indians ...   \n",
       "1  Fashion Sourcebook - 1920s is the first book i...   \n",
       "2  The seminal history and analysis of the Hungar...   \n",
       "3  \"All-American Anarchist\" chronicles the life a...   \n",
       "4  Aujourdâ€™hui, lâ€™oiseau nous invite Ã  sa ta...   \n",
       "\n",
       "                                               genre  \\\n",
       "0  History,Military History,Civil War,American Hi...   \n",
       "1          Couture,Fashion,Historical,Art,Nonfiction   \n",
       "2                                   Politics,History   \n",
       "3                                      Labor,History   \n",
       "4                                                NaN   \n",
       "\n",
       "                                                 img        isbn    isbn13  \\\n",
       "0  https://i.gr-assets.com/images/S/compressed.ph...  002914180X  9.78E+12   \n",
       "1  https://i.gr-assets.com/images/S/compressed.ph...  1906863482  9.78E+12   \n",
       "2  https://i.gr-assets.com/images/S/compressed.ph...   948984147  9.78E+12   \n",
       "3  https://i.gr-assets.com/images/S/compressed.ph...   814327079  9.78E+12   \n",
       "4  https://i.gr-assets.com/images/S/compressed.ph...  2761920813       NaN   \n",
       "\n",
       "                                                link  pages  rating  reviews  \\\n",
       "0  https://goodreads.com/book/show/1001053.Betwee...      0    3.52        5   \n",
       "1  https://goodreads.com/book/show/10010552-fashi...    576    4.51        6   \n",
       "2  https://goodreads.com/book/show/1001077.Hungar...    124    4.15        2   \n",
       "3  https://goodreads.com/book/show/1001079.All_Am...    324    3.83        1   \n",
       "4  https://goodreads.com/book/show/10010880-les-o...    177    4.00        1   \n",
       "\n",
       "                                               title  totalratings  \n",
       "0  Between Two Fires: American Indians in the Civ...            33  \n",
       "1                           Fashion Sourcebook 1920s            41  \n",
       "2                                         Hungary 56            26  \n",
       "3  All-American Anarchist: Joseph A. Labadie and ...             6  \n",
       "4                              Les oiseaux gourmands             1  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6021691a",
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "44f0009e",
   "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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "05ce43be",
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "afa33c75",
   "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>Rank</th>\n",
       "      <th>book title</th>\n",
       "      <th>book price</th>\n",
       "      <th>rating</th>\n",
       "      <th>author</th>\n",
       "      <th>year of publication</th>\n",
       "      <th>genre</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Iron Flame (The Empyrean, 2)</td>\n",
       "      <td>18.42</td>\n",
       "      <td>4.1</td>\n",
       "      <td>Rebecca Yarros</td>\n",
       "      <td>2023</td>\n",
       "      <td>Fantasy Romance</td>\n",
       "      <td>amazon.com/Iron-Flame-Empyrean-Rebecca-Yarros/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>20.93</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Britney Spears</td>\n",
       "      <td>2023</td>\n",
       "      <td>Memoir</td>\n",
       "      <td>amazon.com/Woman-Me-Britney-Spears/dp/16680090...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>My Name Is Barbra</td>\n",
       "      <td>31.50</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Barbra Streisand</td>\n",
       "      <td>2023</td>\n",
       "      <td>Autobiography</td>\n",
       "      <td>amazon.com/My-Name-Barbra-Streisand/dp/0525429...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Friends, Lovers, and the Big Terrible Thing: A...</td>\n",
       "      <td>23.99</td>\n",
       "      <td>4.4</td>\n",
       "      <td>Matthew Perry</td>\n",
       "      <td>2023</td>\n",
       "      <td>Memoir</td>\n",
       "      <td>amazon.com/Friends-Lovers-Big-Terrible-Thing/d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>How to Catch a Turkey</td>\n",
       "      <td>5.65</td>\n",
       "      <td>4.8</td>\n",
       "      <td>Adam Wallace</td>\n",
       "      <td>2018</td>\n",
       "      <td>Childrens, Fiction</td>\n",
       "      <td>amazon.com/How-Catch-Turkey-Adam-Wallace/dp/14...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                                         book title  book price  \\\n",
       "0     1                       Iron Flame (The Empyrean, 2)       18.42   \n",
       "1     2                                    The Woman in Me       20.93   \n",
       "2     3                                  My Name Is Barbra       31.50   \n",
       "3     4  Friends, Lovers, and the Big Terrible Thing: A...       23.99   \n",
       "4     5                              How to Catch a Turkey        5.65   \n",
       "\n",
       "   rating            author  year of publication               genre  \\\n",
       "0     4.1    Rebecca Yarros                 2023     Fantasy Romance   \n",
       "1     4.5    Britney Spears                 2023              Memoir   \n",
       "2     4.5  Barbra Streisand                 2023       Autobiography   \n",
       "3     4.4     Matthew Perry                 2023              Memoir   \n",
       "4     4.8      Adam Wallace                 2018  Childrens, Fiction   \n",
       "\n",
       "                                                 url  \n",
       "0  amazon.com/Iron-Flame-Empyrean-Rebecca-Yarros/...  \n",
       "1  amazon.com/Woman-Me-Britney-Spears/dp/16680090...  \n",
       "2  amazon.com/My-Name-Barbra-Streisand/dp/0525429...  \n",
       "3  amazon.com/Friends-Lovers-Big-Terrible-Thing/d...  \n",
       "4  amazon.com/How-Catch-Turkey-Adam-Wallace/dp/14...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top100_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "43be8fa2",
   "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>Sno</th>\n",
       "      <th>book name</th>\n",
       "      <th>review title</th>\n",
       "      <th>reviewer</th>\n",
       "      <th>reviewer rating</th>\n",
       "      <th>review description</th>\n",
       "      <th>is_verified</th>\n",
       "      <th>date</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>ASIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>Unbelievably impressive. Her torn life on paper.</td>\n",
       "      <td>Murderess Marbie</td>\n",
       "      <td>4</td>\n",
       "      <td>I'm only a third way in. Shipped lightening fa...</td>\n",
       "      <td>True</td>\n",
       "      <td>26-10-2023</td>\n",
       "      <td>Reviewed in the United States October 26, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>What a heartbreaking story</td>\n",
       "      <td>L J</td>\n",
       "      <td>5</td>\n",
       "      <td>\"There have been so many times when I was scar...</td>\n",
       "      <td>True</td>\n",
       "      <td>06-11-2023</td>\n",
       "      <td>Reviewed in the United States November 6, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>Britney you are so invincible! You are an insp...</td>\n",
       "      <td>Jamie</td>\n",
       "      <td>5</td>\n",
       "      <td>The media could not be loaded. I personally ha...</td>\n",
       "      <td>True</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>Reviewed in the United States November 1, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>Fast Read, Sad Story</td>\n",
       "      <td>KMG</td>\n",
       "      <td>5</td>\n",
       "      <td>I have been a fan of Britney's music since the...</td>\n",
       "      <td>True</td>\n",
       "      <td>25-10-2023</td>\n",
       "      <td>Reviewed in the United States October 25, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>Buy it, it’s worth the read!</td>\n",
       "      <td>Stephanie Brown</td>\n",
       "      <td>5</td>\n",
       "      <td>Whether or not you’re a fan, it’s a great read...</td>\n",
       "      <td>True</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>Reviewed in the United States November 1, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Sno        book name                                       review title  \\\n",
       "0    0  The Woman in Me   Unbelievably impressive. Her torn life on paper.   \n",
       "1    1  The Woman in Me                         What a heartbreaking story   \n",
       "2    2  The Woman in Me  Britney you are so invincible! You are an insp...   \n",
       "3    3  The Woman in Me                               Fast Read, Sad Story   \n",
       "4    4  The Woman in Me                       Buy it, it’s worth the read!   \n",
       "\n",
       "           reviewer  reviewer rating  \\\n",
       "0  Murderess Marbie                4   \n",
       "1               L J                5   \n",
       "2             Jamie                5   \n",
       "3               KMG                5   \n",
       "4   Stephanie Brown                5   \n",
       "\n",
       "                                  review description  is_verified        date  \\\n",
       "0  I'm only a third way in. Shipped lightening fa...         True  26-10-2023   \n",
       "1  \"There have been so many times when I was scar...         True  06-11-2023   \n",
       "2  The media could not be loaded. I personally ha...         True  01-11-2023   \n",
       "3  I have been a fan of Britney's music since the...         True  25-10-2023   \n",
       "4  Whether or not you’re a fan, it’s a great read...         True  01-11-2023   \n",
       "\n",
       "                                        timestamp        ASIN  \n",
       "0  Reviewed in the United States October 26, 2023  1668009048  \n",
       "1  Reviewed in the United States November 6, 2023  1668009048  \n",
       "2  Reviewed in the United States November 1, 2023  1668009048  \n",
       "3  Reviewed in the United States October 25, 2023  1668009048  \n",
       "4  Reviewed in the United States November 1, 2023  1668009048  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5e1f960d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bookID</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>language_code</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harry Potter and the Half-Blood Prince (Harry ...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.57</td>\n",
       "      <td>0439785960</td>\n",
       "      <td>9780439785969</td>\n",
       "      <td>eng</td>\n",
       "      <td>652</td>\n",
       "      <td>2095690</td>\n",
       "      <td>27591</td>\n",
       "      <td>9/16/2006</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.49</td>\n",
       "      <td>0439358078</td>\n",
       "      <td>9780439358071</td>\n",
       "      <td>eng</td>\n",
       "      <td>870</td>\n",
       "      <td>2153167</td>\n",
       "      <td>29221</td>\n",
       "      <td>9/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Harry...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>4.42</td>\n",
       "      <td>0439554896</td>\n",
       "      <td>9780439554893</td>\n",
       "      <td>eng</td>\n",
       "      <td>352</td>\n",
       "      <td>6333</td>\n",
       "      <td>244</td>\n",
       "      <td>11/1/2003</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.56</td>\n",
       "      <td>043965548X</td>\n",
       "      <td>9780439655484</td>\n",
       "      <td>eng</td>\n",
       "      <td>435</td>\n",
       "      <td>2339585</td>\n",
       "      <td>36325</td>\n",
       "      <td>5/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Harry Potter Boxed Set  Books 1-5 (Harry Potte...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.78</td>\n",
       "      <td>0439682584</td>\n",
       "      <td>9780439682589</td>\n",
       "      <td>eng</td>\n",
       "      <td>2690</td>\n",
       "      <td>41428</td>\n",
       "      <td>164</td>\n",
       "      <td>9/13/2004</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   bookID                                              title  \\\n",
       "0       1  Harry Potter and the Half-Blood Prince (Harry ...   \n",
       "1       2  Harry Potter and the Order of the Phoenix (Har...   \n",
       "2       4  Harry Potter and the Chamber of Secrets (Harry...   \n",
       "3       5  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4       8  Harry Potter Boxed Set  Books 1-5 (Harry Potte...   \n",
       "\n",
       "                      authors  average_rating        isbn         isbn13  \\\n",
       "0  J.K. Rowling/Mary GrandPré            4.57  0439785960  9780439785969   \n",
       "1  J.K. Rowling/Mary GrandPré            4.49  0439358078  9780439358071   \n",
       "2                J.K. Rowling            4.42  0439554896  9780439554893   \n",
       "3  J.K. Rowling/Mary GrandPré            4.56  043965548X  9780439655484   \n",
       "4  J.K. Rowling/Mary GrandPré            4.78  0439682584  9780439682589   \n",
       "\n",
       "  language_code    num_pages  ratings_count  text_reviews_count  \\\n",
       "0           eng          652        2095690               27591   \n",
       "1           eng          870        2153167               29221   \n",
       "2           eng          352           6333                 244   \n",
       "3           eng          435        2339585               36325   \n",
       "4           eng         2690          41428                 164   \n",
       "\n",
       "  publication_date        publisher  \n",
       "0        9/16/2006  Scholastic Inc.  \n",
       "1         9/1/2004  Scholastic Inc.  \n",
       "2        11/1/2003       Scholastic  \n",
       "3         5/1/2004  Scholastic Inc.  \n",
       "4        9/13/2004       Scholastic  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_books_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4ceb99b4",
   "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>Title</th>\n",
       "      <th>Author</th>\n",
       "      <th>Image URL</th>\n",
       "      <th>Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fourth Wing (The Empyrean, #1)</td>\n",
       "      <td>Rebecca Yarros</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.63 avg rating — 844,282 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Happy Place</td>\n",
       "      <td>Emily Henry</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.06 avg rating — 574,386 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Yellowface</td>\n",
       "      <td>R.F. Kuang</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>3.87 avg rating — 227,094 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Love, Theoretically</td>\n",
       "      <td>Ali Hazelwood</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.17 avg rating — 239,576 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Divine Rivals (Letters of Enchantment, #1)</td>\n",
       "      <td>Rebecca   Ross</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.26 avg rating — 161,237 ratings</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Title          Author  \\\n",
       "0              Fourth Wing (The Empyrean, #1)  Rebecca Yarros   \n",
       "1                                 Happy Place     Emily Henry   \n",
       "2                                  Yellowface      R.F. Kuang   \n",
       "3                         Love, Theoretically   Ali Hazelwood   \n",
       "4  Divine Rivals (Letters of Enchantment, #1)  Rebecca   Ross   \n",
       "\n",
       "                                           Image URL  \\\n",
       "0  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "1  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "2  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "3  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "4  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "\n",
       "                              Rating  \n",
       "0  4.63 avg rating — 844,282 ratings  \n",
       "1  4.06 avg rating — 574,386 ratings  \n",
       "2  3.87 avg rating — 227,094 ratings  \n",
       "3  4.17 avg rating — 239,576 ratings  \n",
       "4  4.26 avg rating — 161,237 ratings  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodreads_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89e87a38",
   "metadata": {},
   "source": [
    "## Standardize column names to snake_case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cdced4db",
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_snake_case(df):\n",
    "    df.columns = df.columns.str.lower().str.replace(' ', '_').str.replace('-', '_')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "324fc9f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply to books_df\n",
    "books_df = to_snake_case(books_df)\n",
    "\n",
    "# Apply to ratings_df\n",
    "ratings_df = to_snake_case(ratings_df)\n",
    "\n",
    "# Apply to books_df_2\n",
    "books_df_2 = to_snake_case(books_df_2)\n",
    "\n",
    "# Apply to users_df\n",
    "users_df = to_snake_case(users_df)\n",
    "\n",
    "# Apply to top100_df\n",
    "top100_df = to_snake_case(top100_df)\n",
    "\n",
    "# Apply to customers_df\n",
    "customers_df = to_snake_case(customers_df)\n",
    "\n",
    "# Apply to goodreads_df\n",
    "goodreads_df = to_snake_case(goodreads_df)\n",
    "\n",
    "# Apply to gr_books_df\n",
    "gr_books_df = to_snake_case(gr_books_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b09dbaee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['author', 'bookformat', 'desc', 'genre', 'img', 'isbn', 'isbn13',\n",
       "       'link', 'pages', 'rating', 'reviews', 'title', 'totalratings'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check column names\n",
    "books_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdf0be2a",
   "metadata": {},
   "source": [
    "I will change:\n",
    "\n",
    "- 'bookformat' to book_format\n",
    "- 'desc' to description\n",
    "- 'img' to image_url\n",
    "- 'link' to url\n",
    "- 'totalratings' to total_ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e87040dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['author', 'book_format', 'description', 'genre', 'image_url', 'isbn',\n",
       "       'isbn13', 'url', 'pages', 'rating', 'reviews', 'title',\n",
       "       'total_ratings'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns in books_df\n",
    "books_df.rename(columns={\n",
    "    'bookformat': 'book_format',\n",
    "    'desc': 'description',\n",
    "    'img': 'image_url',\n",
    "    'link': 'url',\n",
    "    'totalratings': 'total_ratings'\n",
    "}, inplace=True)\n",
    "\n",
    "# Check\n",
    "books_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3a93bd8a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'isbn', 'book_rating'], dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03305206",
   "metadata": {},
   "source": [
    "I will change:\n",
    "\n",
    "- 'book_rating' to rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ddf2fef3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'isbn', 'rating'], dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename the 'book_rating' column to 'rating' in ratings_df\n",
    "ratings_df.rename(columns={'book_rating': 'rating'}, inplace=True)\n",
    "\n",
    "ratings_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c9f67862",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['isbn', 'book_title', 'book_author', 'year_of_publication', 'publisher',\n",
       "       'image_url_s', 'image_url_m', 'image_url_l'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df_2.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd3bdf14",
   "metadata": {},
   "source": [
    "I will change:\n",
    "\n",
    "- 'book_title' to title\n",
    "- 'book_author' to author\n",
    "- 'year_of_publication' to year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a1dae185",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['isbn', 'title', 'author', 'year', 'publisher', 'image_url_s',\n",
       "       'image_url_m', 'image_url_l'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns in books_df_2\n",
    "books_df_2.rename(columns={'book_title': 'title', 'book_author': 'author', 'year_of_publication': 'year'}, inplace=True)\n",
    "\n",
    "books_df_2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3c926e07",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'location', 'age'], dtype='object')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "212ec66f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['rank', 'book_title', 'book_price', 'rating', 'author',\n",
       "       'year_of_publication', 'genre', 'url'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top100_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ad5b112",
   "metadata": {},
   "source": [
    "I will change:\n",
    "\n",
    "- 'book_title' to title\n",
    "- 'book_price' to price\n",
    "- 'year_of_publication' to year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "b02409c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['rank', 'title', 'price', 'rating', 'author', 'year', 'genre', 'url'], dtype='object')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns in top100_df\n",
    "top100_df.rename(columns={'book_title': 'title', 'book_price': 'price', 'year_of_publication': 'year'}, inplace=True)\n",
    "\n",
    "top100_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "bd619f27",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['sno', 'book_name', 'review_title', 'reviewer', 'reviewer_rating',\n",
       "       'review_description', 'is_verified', 'date', 'timestamp', 'asin'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44f23aad",
   "metadata": {},
   "source": [
    "I will change:\n",
    "\n",
    "- 'sno' to serial_number\n",
    "- 'book_name' to title\n",
    "- 'date' to year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f231fe22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['serial_number', 'title', 'review_title', 'reviewer', 'reviewer_rating',\n",
       "       'review_description', 'is_verified', 'year', 'timestamp', 'asin'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns in customers_df\n",
    "customers_df.rename(columns={'sno': 'serial_number', 'book_name': 'title', 'date': 'year'}, inplace=True)\n",
    "\n",
    "customers_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c37dceff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['title', 'author', 'image_url', 'rating'], dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodreads_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "c7ed3d1b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['bookid', 'title', 'authors', 'average_rating', 'isbn', 'isbn13',\n",
       "       'language_code', '__num_pages', 'ratings_count', 'text_reviews_count',\n",
       "       'publication_date', 'publisher'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_books_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "982313a8",
   "metadata": {},
   "source": [
    "I will change:\n",
    "\n",
    "- 'bookid' to book_id\n",
    "- 'authors' to author\n",
    "- 'average_rating' to rating, 'isbn', 'isbn13'\n",
    "- 'language_code' to language\n",
    "- '__num_pages' to pages\n",
    "- 'text_reviews_count' to reviews_count\n",
    "- 'publication_date' to year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "ebb4c4f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['book_id', 'title', 'author', 'rating', 'isbn', 'isbn13', 'language',\n",
       "       'pages', 'ratings_count', 'reviews_count', 'year', 'publisher'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns in gr_books_df\n",
    "gr_books_df.rename(columns={\n",
    "    'bookid': 'book_id',\n",
    "    'authors': 'author',\n",
    "    'average_rating': 'rating',\n",
    "    'isbn': 'isbn',\n",
    "    'isbn13': 'isbn13',\n",
    "    'language_code': 'language',\n",
    "    '__num_pages': 'pages',\n",
    "    'text_reviews_count': 'reviews_count',\n",
    "    'publication_date': 'year'\n",
    "}, inplace=True)\n",
    "\n",
    "gr_books_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "129c3776",
   "metadata": {},
   "source": [
    "## Merge some dataframes on common columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e8ef6b7",
   "metadata": {},
   "source": [
    "### Merge books_df_2, ratings_df and users_df\n",
    "\n",
    "They are from the same dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ade1aab6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge users_df and ratings_df on 'user_id'\n",
    "u_r_df = users_df.merge(ratings_df, on='user_id', how='inner')\n",
    "\n",
    "# Merge u_r_df and books_df_2 on 'isbn'\n",
    "users_books_df = u_r_df.merge(books_df_2, on='isbn', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "d13fb33b",
   "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",
       "      <th>isbn</th>\n",
       "      <th>rating</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</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>2</td>\n",
       "      <td>stockton, california, usa</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0195153448</td>\n",
       "      <td>0</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>8</td>\n",
       "      <td>timmins, ontario, canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0002005018</td>\n",
       "      <td>5</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>11400</td>\n",
       "      <td>ottawa, ontario, canada</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0002005018</td>\n",
       "      <td>0</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>3</th>\n",
       "      <td>11676</td>\n",
       "      <td>n/a, n/a, n/a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0002005018</td>\n",
       "      <td>8</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>4</th>\n",
       "      <td>41385</td>\n",
       "      <td>sudbury, ontario, canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0002005018</td>\n",
       "      <td>0</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id                   location   age        isbn  rating  \\\n",
       "0        2  stockton, california, usa  18.0  0195153448       0   \n",
       "1        8   timmins, ontario, canada   NaN  0002005018       5   \n",
       "2    11400    ottawa, ontario, canada  49.0  0002005018       0   \n",
       "3    11676              n/a, n/a, n/a   NaN  0002005018       8   \n",
       "4    41385   sudbury, ontario, canada   NaN  0002005018       0   \n",
       "\n",
       "                 title                author  year                publisher  \\\n",
       "0  Classical Mythology    Mark P. O. Morford  2002  Oxford University Press   \n",
       "1         Clara Callan  Richard Bruce Wright  2001    HarperFlamingo Canada   \n",
       "2         Clara Callan  Richard Bruce Wright  2001    HarperFlamingo Canada   \n",
       "3         Clara Callan  Richard Bruce Wright  2001    HarperFlamingo Canada   \n",
       "4         Clara Callan  Richard Bruce Wright  2001    HarperFlamingo Canada   \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/0002005018.0...   \n",
       "3  http://images.amazon.com/images/P/0002005018.0...   \n",
       "4  http://images.amazon.com/images/P/0002005018.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/0002005018.0...   \n",
       "3  http://images.amazon.com/images/P/0002005018.0...   \n",
       "4  http://images.amazon.com/images/P/0002005018.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/0002005018.0...  \n",
       "3  http://images.amazon.com/images/P/0002005018.0...  \n",
       "4  http://images.amazon.com/images/P/0002005018.0...  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef21ec17",
   "metadata": {},
   "source": [
    "### Merge top100_df and customers_df\n",
    "\n",
    "Both are from the same dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9dfc6856",
   "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>rank</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>rating</th>\n",
       "      <th>author</th>\n",
       "      <th>year_x</th>\n",
       "      <th>genre</th>\n",
       "      <th>url</th>\n",
       "      <th>serial_number</th>\n",
       "      <th>review_title</th>\n",
       "      <th>reviewer</th>\n",
       "      <th>reviewer_rating</th>\n",
       "      <th>review_description</th>\n",
       "      <th>is_verified</th>\n",
       "      <th>year_y</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>asin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>20.93</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Britney Spears</td>\n",
       "      <td>2023</td>\n",
       "      <td>Memoir</td>\n",
       "      <td>amazon.com/Woman-Me-Britney-Spears/dp/16680090...</td>\n",
       "      <td>0</td>\n",
       "      <td>Unbelievably impressive. Her torn life on paper.</td>\n",
       "      <td>Murderess Marbie</td>\n",
       "      <td>4</td>\n",
       "      <td>I'm only a third way in. Shipped lightening fa...</td>\n",
       "      <td>True</td>\n",
       "      <td>26-10-2023</td>\n",
       "      <td>Reviewed in the United States October 26, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>20.93</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Britney Spears</td>\n",
       "      <td>2023</td>\n",
       "      <td>Memoir</td>\n",
       "      <td>amazon.com/Woman-Me-Britney-Spears/dp/16680090...</td>\n",
       "      <td>1</td>\n",
       "      <td>What a heartbreaking story</td>\n",
       "      <td>L J</td>\n",
       "      <td>5</td>\n",
       "      <td>\"There have been so many times when I was scar...</td>\n",
       "      <td>True</td>\n",
       "      <td>06-11-2023</td>\n",
       "      <td>Reviewed in the United States November 6, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>20.93</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Britney Spears</td>\n",
       "      <td>2023</td>\n",
       "      <td>Memoir</td>\n",
       "      <td>amazon.com/Woman-Me-Britney-Spears/dp/16680090...</td>\n",
       "      <td>2</td>\n",
       "      <td>Britney you are so invincible! You are an insp...</td>\n",
       "      <td>Jamie</td>\n",
       "      <td>5</td>\n",
       "      <td>The media could not be loaded. I personally ha...</td>\n",
       "      <td>True</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>Reviewed in the United States November 1, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>20.93</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Britney Spears</td>\n",
       "      <td>2023</td>\n",
       "      <td>Memoir</td>\n",
       "      <td>amazon.com/Woman-Me-Britney-Spears/dp/16680090...</td>\n",
       "      <td>3</td>\n",
       "      <td>Fast Read, Sad Story</td>\n",
       "      <td>KMG</td>\n",
       "      <td>5</td>\n",
       "      <td>I have been a fan of Britney's music since the...</td>\n",
       "      <td>True</td>\n",
       "      <td>25-10-2023</td>\n",
       "      <td>Reviewed in the United States October 25, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>The Woman in Me</td>\n",
       "      <td>20.93</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Britney Spears</td>\n",
       "      <td>2023</td>\n",
       "      <td>Memoir</td>\n",
       "      <td>amazon.com/Woman-Me-Britney-Spears/dp/16680090...</td>\n",
       "      <td>4</td>\n",
       "      <td>Buy it, it’s worth the read!</td>\n",
       "      <td>Stephanie Brown</td>\n",
       "      <td>5</td>\n",
       "      <td>Whether or not you’re a fan, it’s a great read...</td>\n",
       "      <td>True</td>\n",
       "      <td>01-11-2023</td>\n",
       "      <td>Reviewed in the United States November 1, 2023</td>\n",
       "      <td>1668009048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rank            title  price  rating          author  year_x   genre  \\\n",
       "0     2  The Woman in Me  20.93     4.5  Britney Spears    2023  Memoir   \n",
       "1     2  The Woman in Me  20.93     4.5  Britney Spears    2023  Memoir   \n",
       "2     2  The Woman in Me  20.93     4.5  Britney Spears    2023  Memoir   \n",
       "3     2  The Woman in Me  20.93     4.5  Britney Spears    2023  Memoir   \n",
       "4     2  The Woman in Me  20.93     4.5  Britney Spears    2023  Memoir   \n",
       "\n",
       "                                                 url  serial_number  \\\n",
       "0  amazon.com/Woman-Me-Britney-Spears/dp/16680090...              0   \n",
       "1  amazon.com/Woman-Me-Britney-Spears/dp/16680090...              1   \n",
       "2  amazon.com/Woman-Me-Britney-Spears/dp/16680090...              2   \n",
       "3  amazon.com/Woman-Me-Britney-Spears/dp/16680090...              3   \n",
       "4  amazon.com/Woman-Me-Britney-Spears/dp/16680090...              4   \n",
       "\n",
       "                                        review_title          reviewer  \\\n",
       "0   Unbelievably impressive. Her torn life on paper.  Murderess Marbie   \n",
       "1                         What a heartbreaking story               L J   \n",
       "2  Britney you are so invincible! You are an insp...             Jamie   \n",
       "3                               Fast Read, Sad Story               KMG   \n",
       "4                       Buy it, it’s worth the read!   Stephanie Brown   \n",
       "\n",
       "   reviewer_rating                                 review_description  \\\n",
       "0                4  I'm only a third way in. Shipped lightening fa...   \n",
       "1                5  \"There have been so many times when I was scar...   \n",
       "2                5  The media could not be loaded. I personally ha...   \n",
       "3                5  I have been a fan of Britney's music since the...   \n",
       "4                5  Whether or not you’re a fan, it’s a great read...   \n",
       "\n",
       "   is_verified      year_y                                       timestamp  \\\n",
       "0         True  26-10-2023  Reviewed in the United States October 26, 2023   \n",
       "1         True  06-11-2023  Reviewed in the United States November 6, 2023   \n",
       "2         True  01-11-2023  Reviewed in the United States November 1, 2023   \n",
       "3         True  25-10-2023  Reviewed in the United States October 25, 2023   \n",
       "4         True  01-11-2023  Reviewed in the United States November 1, 2023   \n",
       "\n",
       "         asin  \n",
       "0  1668009048  \n",
       "1  1668009048  \n",
       "2  1668009048  \n",
       "3  1668009048  \n",
       "4  1668009048  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge the DataFrames using the \"title\" column\n",
    "best_selling_df = pd.merge(top100_df, customers_df, on='title', how='inner')\n",
    "\n",
    "best_selling_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93c6c198",
   "metadata": {},
   "source": [
    "### Find common columns between books_df and gr_books_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64c81172",
   "metadata": {},
   "source": [
    "This two DataFrames are the ones that I will use for my model. So:\n",
    "\n",
    "1. **Common Columns**: Identifying common columns is essential for merging, analyzing, or combining data from both DataFrames. It helps ensure that I have consistent information across the two DataFrames. In my case, it helps identify which columns I can use as features for clustering.\n",
    "\n",
    "2. **Columns Unique to books_df**: These columns are present in `books_df` but not in `gr_books_df`. Knowing these unique columns allows to understand what additional information is available in `books_df`. I may consider using these columns as additional features in my book recommender system.\n",
    "\n",
    "3. **Columns Unique to gr_books_df**: Similarly, these columns are present in `gr_books_df` but not in `books_df`. Understanding these unique columns helps me to assess what additional data might be available in `gr_books_df`. So I can decide whether to incorporate this information into my recommender system.\n",
    "\n",
    "By analyzing common and unique columns, I can make informed decisions about which features to include in my book recommender model, ensuring it utilizes all relevant information for effective clustering and recommendations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "ebcf75ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Common Columns:\n",
      "{'rating', 'isbn', 'author', 'isbn13', 'pages', 'title'}\n",
      "\n",
      "Columns Unique to books_df:\n",
      "{'reviews', 'description', 'image_url', 'url', 'total_ratings', 'genre', 'book_format'}\n",
      "\n",
      "Columns Unique to gr_books_df:\n",
      "{'ratings_count', 'book_id', 'language', 'year', 'publisher', 'reviews_count'}\n"
     ]
    }
   ],
   "source": [
    "# Find common columns between books_df and gr_books_df\n",
    "common_columns = set(books_df.columns) & set(gr_books_df.columns)\n",
    "\n",
    "# Find columns unique to books_df\n",
    "unique_to_books_df = set(books_df.columns) - set(gr_books_df.columns)\n",
    "\n",
    "# Find columns unique to gr_books_df\n",
    "unique_to_gr_books_df = set(gr_books_df.columns) - set(books_df.columns)\n",
    "\n",
    "print(\"Common Columns:\")\n",
    "print(common_columns)\n",
    "print(\"\\nColumns Unique to books_df:\")\n",
    "print(unique_to_books_df)\n",
    "print(\"\\nColumns Unique to gr_books_df:\")\n",
    "print(unique_to_gr_books_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dc19945",
   "metadata": {},
   "source": [
    "#### My selected features for clustering are:\n",
    "\n",
    "1. Description (I will make a sentiment analysis to group them)\n",
    "2. Genre\n",
    "3. Pages\n",
    "4. Rating\n",
    "5. Year of Publication\n",
    "6. Language\n",
    "\n",
    "#### So here's what I should consider doing to gather the missing information for these features:\n",
    "\n",
    "1. **Description**: The `description` is missing in `gr_books_df`, so I can try to obtain book descriptions from various sources, such as Google Books API, Amazon, or other book-related APIs. I can use the ISBN or book title to fetch descriptions for books missing this information.\n",
    "\n",
    "2. **Genre**: I can manually categorize books into genres because this information is missing in `gr_books_df`. Alternatively, I can use machine learning techniques, such as natural language processing (NLP), to classify books into genres based on their descriptions or other available information.\n",
    "\n",
    "3. **Pages**: I have the `pages` information in both dataframes.\n",
    "\n",
    "4. **Rating**: Ratings are available in both DataFrames, so there's no need to gather additional information for this feature.\n",
    "\n",
    "5. **Year of Publication**: Missing in `books_df` so I can attempt to fill in missing publication years by searching for the books on platforms like Google Books or Amazon. These platforms often provide publication years for books.\n",
    "\n",
    "6. **Language**: Missing in `books_df`. The `language` feature may require web scraping or API calls to obtain language information for books. I can use the ISBN or book title to search for language details on book-related websites or language-specific APIs.\n",
    "\n",
    "In summary, for the missing information, I may need to resort to external sources such as online bookstores, libraries, or book-related APIs to fill in the gaps. Web scraping and data enrichment techniques can be valuable tools in this process, helping me gather comprehensive data for my clustering and recommendation model.\n",
    "\n",
    "For now I will continue with the data cleaning before filling this missing information and merging both dataframes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa8f6714",
   "metadata": {},
   "source": [
    "## Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "09d6596c",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null values in Books:\n",
      " author               0\n",
      "book_format       3228\n",
      "description       6772\n",
      "genre            10467\n",
      "image_url         3045\n",
      "isbn             14482\n",
      "isbn13           11435\n",
      "url                  0\n",
      "pages                0\n",
      "rating               0\n",
      "reviews              0\n",
      "title                1\n",
      "total_ratings        0\n",
      "dtype: int64\n",
      "\n",
      "Null values in Users Books:\n",
      " user_id             0\n",
      "location            0\n",
      "age            277835\n",
      "isbn                0\n",
      "rating              0\n",
      "title               0\n",
      "author              1\n",
      "year                0\n",
      "publisher           2\n",
      "image_url_s         0\n",
      "image_url_m         0\n",
      "image_url_l         4\n",
      "dtype: int64\n",
      "\n",
      "Null values in Best Selling:\n",
      " rank                  0\n",
      "title                 0\n",
      "price                 0\n",
      "rating                0\n",
      "author                0\n",
      "year_x                0\n",
      "genre                 0\n",
      "url                   0\n",
      "serial_number         0\n",
      "review_title          0\n",
      "reviewer              0\n",
      "reviewer_rating       0\n",
      "review_description    0\n",
      "is_verified           0\n",
      "year_y                0\n",
      "timestamp             0\n",
      "asin                  0\n",
      "dtype: int64\n",
      "\n",
      "Null values in Goodreads:\n",
      " title        0\n",
      "author       0\n",
      "image_url    0\n",
      "rating       0\n",
      "dtype: int64\n",
      "\n",
      "Null values in Goodreads 2:\n",
      " book_id          0\n",
      "title            0\n",
      "author           0\n",
      "rating           0\n",
      "isbn             0\n",
      "isbn13           0\n",
      "language         0\n",
      "pages            0\n",
      "ratings_count    0\n",
      "reviews_count    0\n",
      "year             0\n",
      "publisher        0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(\"Null values in Books:\\n\" ,books_df.isnull().sum())\n",
    "print(\"\\nNull values in Users Books:\\n\" ,users_books_df.isnull().sum())\n",
    "print(\"\\nNull values in Best Selling:\\n\" ,best_selling_df.isnull().sum())\n",
    "print(\"\\nNull values in Goodreads:\\n\" ,goodreads_df.isnull().sum())\n",
    "print(\"\\nNull values in Goodreads 2:\\n\" ,gr_books_df.isnull().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "993dc522",
   "metadata": {},
   "source": [
    "This provides information about the presence of null (missing) values in various columns of different dataframes. \n",
    "\n",
    "#### Here's a summary of the null values in each dataframe:\n",
    "\n",
    "**1. Books DataFrame:**\n",
    "- `book_format`: 3,228 missing values.\n",
    "- `description`: 6,772 missing values.\n",
    "- `genre`: 10,467 missing values.\n",
    "- `image_url`: 3,045 missing values.\n",
    "- `isbn`: 14,482 missing values.\n",
    "- `isbn13`: 11,435 missing values.\n",
    "- `title`: 1 missing value.\n",
    "\n",
    "**2. Users Books DataFrame:**\n",
    "- `age`: 277,835 missing values.\n",
    "- `author`: 1 missing value.\n",
    "- `publisher`: 2 missing values.\n",
    "- `image_url_l`: 4 missing values.\n",
    "\n",
    "**3. Best Selling DataFrame:**\n",
    "No missing values.\n",
    "\n",
    "**4. Goodreads DataFrame:**\n",
    "No missing values.\n",
    "\n",
    "**5. Goodreads 2 DataFrame:**\n",
    "No missing values.\n",
    "\n",
    "This helps me to identify columns with missing values in each dataframe. I may need to handle these missing values through techniques like imputation, removal, or data enrichment."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99bc1f2f",
   "metadata": {},
   "source": [
    "## Identifying and Removing Unnecessary Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea5f94b6",
   "metadata": {},
   "source": [
    "#### Books: I will drop `book_format` and `isbn13`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "044c85b2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['author', 'book_format', 'description', 'genre', 'image_url', 'isbn',\n",
       "       'isbn13', 'url', 'pages', 'rating', 'reviews', 'title',\n",
       "       'total_ratings'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59792cd5",
   "metadata": {},
   "source": [
    "In the context of my dataset and the specific objectives of my analysis, I have decided to retain the `isbn` column while removing the `isbn13` one. The reason for this choice is primarily based on the practicality and common usage of the ISBN (International Standard Book Number) as the primary unique identifier for books. The ISBN serves as a widely recognized and effective means of book identification, facilitating searches and categorization. While ISBN13 provides a longer and more detailed identification number, it is not commonly required for my current analytical goals, and the ISBN alone is sufficient for my purposes. By removing the `isbn13` column, I aim to streamline the dataset and reduce redundancy, focusing on the most essential information for my book-related analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "88d2a270",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['author', 'description', 'genre', 'image_url', 'isbn', 'url', 'pages',\n",
       "       'rating', 'reviews', 'title', 'total_ratings'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.drop(columns=[\"book_format\", 'isbn13'], inplace=True)\n",
    "\n",
    "books_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a3a97b5",
   "metadata": {},
   "source": [
    "#### Users Books: I will drop `user_id`, `image_url_s` and `image_url_l`\n",
    "\n",
    "User_id is unique for each user so I don't need it for my analysis. Also from the images I will just keep `image_url_m` that contains the same information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "21b206a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['user_id', 'location', 'age', 'isbn', 'rating', 'title', 'author',\n",
       "       'year', 'publisher', 'image_url_s', 'image_url_m', 'image_url_l'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "17d4e163",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['location', 'age', 'isbn', 'rating', 'title', 'author', 'year',\n",
       "       'publisher', 'image_url_m'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.drop(columns=[\"user_id\", 'image_url_s', 'image_url_l'], inplace=True)\n",
    "\n",
    "users_books_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "155991c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['location', 'age', 'isbn', 'rating', 'title', 'author', 'year',\n",
       "       'publisher', 'image_url'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.rename(columns={'image_url_m': 'image_url'}, inplace=True)\n",
    "\n",
    "users_books_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5492cf1",
   "metadata": {},
   "source": [
    "**Best Selling: I will drop `rank`, `serial_number`, `review_title`, `reviewer`, `reviewer_rating`, `is_verified`, `year_y`, `review_description`, `timestamp` and `asin`**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "d3e0303c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['rank', 'title', 'price', 'rating', 'author', 'year_x', 'genre', 'url',\n",
       "       'serial_number', 'review_title', 'reviewer', 'reviewer_rating',\n",
       "       'review_description', 'is_verified', 'year_y', 'timestamp', 'asin'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "best_selling_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ebfc3cf",
   "metadata": {},
   "source": [
    "I will drop them because:\n",
    "\n",
    "- `serial number`: I don't need this one for my analysis.\n",
    "- `review_title`: Is redundant with the information contained in `review_description`.\n",
    "- `reviewer`: I don't need the name of each person who has written a review for the book.\n",
    "- `reviewer_rating`: Is redundant with the average rating in the `rating`column.\n",
    "- `is_verified`: also don't need this one.\n",
    "- `year_y`: because it indicates the date when the review was posted and I don't need it for my analysis.\n",
    "- `rank`, `review_description`, `timestamp` and `asin`: all of them are unique for each book and I don't need them for the recommender."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "6bea642f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of columns to drop\n",
    "columns_to_drop = [\"rank\", \"review_description\", \"timestamp\", \"asin\", 'serial_number', 'review_title', 'reviewer', 'reviewer_rating', 'is_verified', 'year_y']\n",
    "\n",
    "# Drop columns in the list\n",
    "best_selling_df.drop(columns=columns_to_drop, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "f3dae349",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['title', 'price', 'rating', 'author', 'year', 'genre', 'url'], dtype='object')"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename the 'year_x' column to 'year' \n",
    "best_selling_df.rename(columns={'year_x': 'year'}, inplace=True)\n",
    "\n",
    "best_selling_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ceaafe19",
   "metadata": {},
   "source": [
    "#### Goodreads: I keep them all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "c51dd775",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['title', 'author', 'image_url', 'rating'], dtype='object')"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodreads_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99b1ce6d",
   "metadata": {},
   "source": [
    "#### Goodreads 2: I will drop `book_id` and `isbn13`\n",
    "\n",
    "I don't need them since I already have the `isbn` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "632d8f81",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['book_id', 'title', 'author', 'rating', 'isbn', 'isbn13', 'language',\n",
       "       'pages', 'ratings_count', 'reviews_count', 'year', 'publisher'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_books_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "cb802460",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['title', 'author', 'rating', 'isbn', 'language', 'pages',\n",
       "       'ratings_count', 'reviews_count', 'year', 'publisher'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_to_drop = ['book_id', 'isbn13']\n",
    "gr_books_df.drop(columns=columns_to_drop, inplace=True)\n",
    "\n",
    "gr_books_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea44988a",
   "metadata": {},
   "source": [
    "## Fill missing information using APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c94758e1",
   "metadata": {},
   "source": [
    "**Missing info in `books_df`:**\n",
    "\n",
    "- description: 6,772 missing values.\n",
    "- genre: 10,467 missing values.\n",
    "- image_url: 3,045 missing values.\n",
    "- isbn: 14,482 missing values.\n",
    "- title: 1 missing value.\n",
    "- year\n",
    "- language\n",
    "\n",
    "\n",
    "**Missing info in `users_books_df`:**\n",
    "\n",
    "- age: 277,835 missing values.\n",
    "- author: 1 missing value.\n",
    "- publisher: 2 missing values.\n",
    "\n",
    "I will fill the age missing values lately in the EDA part. And since I only have 1 missing value for author and 2 for publisher I will fill them manually checking in the web using the ISBN of the book.\n",
    "\n",
    "\n",
    "**Missing info in `gr_books_df`:**\n",
    "\n",
    "- description\n",
    "- genre"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffab338e",
   "metadata": {},
   "source": [
    "### 1. books_df\n",
    "\n",
    "I use the Google Books API to fetch missing information based on the 'isbn', 'title', and 'author' from the books_df DataFrame. I will attempt to fetch the missing information using the ISBN. If the ISBN is not available, I will construct a query using both the title and the author."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b1dcca2",
   "metadata": {},
   "source": [
    "### Fill missing information using Google Books API\n",
    "\n",
    "\n",
    "It is a time-consuming process (approximately 4 hours) and doesn't provide comprehensive information for each book, which is why I ultimately choose to skip this step.\n",
    "\n",
    "But here I share the code that I was trying."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "d497ed82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100000, 11)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "f7092f67",
   "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>author</th>\n",
       "      <th>description</th>\n",
       "      <th>genre</th>\n",
       "      <th>image_url</th>\n",
       "      <th>isbn</th>\n",
       "      <th>url</th>\n",
       "      <th>pages</th>\n",
       "      <th>rating</th>\n",
       "      <th>reviews</th>\n",
       "      <th>title</th>\n",
       "      <th>total_ratings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jean Leveille</td>\n",
       "      <td>Aujourdâ€™hui, lâ€™oiseau nous invite Ã  sa ta...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>2761920813</td>\n",
       "      <td>https://goodreads.com/book/show/10010880-les-o...</td>\n",
       "      <td>177</td>\n",
       "      <td>4.00</td>\n",
       "      <td>1</td>\n",
       "      <td>Les oiseaux gourmands</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Nick Le Neve Walmsley</td>\n",
       "      <td>At the time of her construction in the late 19...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>752456830</td>\n",
       "      <td>https://goodreads.com/book/show/10011431-r101</td>\n",
       "      <td>160</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2</td>\n",
       "      <td>R101: A Pictorial History</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Mark Verman</td>\n",
       "      <td>The earliest medieval Jewish mystical writings...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>791407209</td>\n",
       "      <td>https://goodreads.com/book/show/10011932-the-b...</td>\n",
       "      <td>282</td>\n",
       "      <td>4.75</td>\n",
       "      <td>1</td>\n",
       "      <td>The Books of Contemplation: Medieval Jewish My...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Leo Bagrow,Raleigh Ashlin Skelton</td>\n",
       "      <td>This illustrated work is intended to acquaint ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>913750336</td>\n",
       "      <td>https://goodreads.com/book/show/10013570-histo...</td>\n",
       "      <td>312</td>\n",
       "      <td>5.00</td>\n",
       "      <td>0</td>\n",
       "      <td>History of Cartography: Enlarged Second Edition</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Luc Milne</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Erotica,Gay Erotica</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>943595614</td>\n",
       "      <td>https://goodreads.com/book/show/1001377.The_Mi...</td>\n",
       "      <td>160</td>\n",
       "      <td>3.46</td>\n",
       "      <td>1</td>\n",
       "      <td>The Milk Farm: An Erotic Novel</td>\n",
       "      <td>13</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99988</th>\n",
       "      <td>C.T. Khanolkar</td>\n",
       "      <td>A Novella that brings out a story of a person ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://goodreads.com/book/show/17319699</td>\n",
       "      <td>90</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3</td>\n",
       "      <td>à¤…à¤œà¤—à¤°</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99989</th>\n",
       "      <td>Claire Farrell</td>\n",
       "      <td>Perditaâ€™s life has gone back to normal in al...</td>\n",
       "      <td>Fantasy,Paranormal,Young Adult,Fantasy,Shapesh...</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://goodreads.com/book/show/17319882-purity</td>\n",
       "      <td>0</td>\n",
       "      <td>4.05</td>\n",
       "      <td>64</td>\n",
       "      <td>Purity</td>\n",
       "      <td>1244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99990</th>\n",
       "      <td>Ilja Gort</td>\n",
       "      <td>Ilja Gort is een Nederlandse wijnboer in Frank...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://goodreads.com/book/show/17319892-slurp</td>\n",
       "      <td>246</td>\n",
       "      <td>3.64</td>\n",
       "      <td>1</td>\n",
       "      <td>Slurp!</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>Simon Monk</td>\n",
       "      <td>Design custom printed circuit boards with EAGL...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>71819266</td>\n",
       "      <td>https://goodreads.com/book/show/17320181-make-...</td>\n",
       "      <td>304</td>\n",
       "      <td>4.07</td>\n",
       "      <td>7</td>\n",
       "      <td>Make Your Own PCBs with Eagle: From Schematic ...</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>Tracie L. Miller-Nobles,Brenda L. Mattison,Ell...</td>\n",
       "      <td>Redefining tradition in learning accounting. ,...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>133251241</td>\n",
       "      <td>https://goodreads.com/book/show/17320260-horng...</td>\n",
       "      <td>1645</td>\n",
       "      <td>4.05</td>\n",
       "      <td>1</td>\n",
       "      <td>Horngren's Financial &amp; Managerial Accounting</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26281 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  author  \\\n",
       "4                                          Jean Leveille   \n",
       "8                                  Nick Le Neve Walmsley   \n",
       "10                                           Mark Verman   \n",
       "27                     Leo Bagrow,Raleigh Ashlin Skelton   \n",
       "30                                             Luc Milne   \n",
       "...                                                  ...   \n",
       "99988                                     C.T. Khanolkar   \n",
       "99989                                     Claire Farrell   \n",
       "99990                                          Ilja Gort   \n",
       "99995                                         Simon Monk   \n",
       "99996  Tracie L. Miller-Nobles,Brenda L. Mattison,Ell...   \n",
       "\n",
       "                                             description  \\\n",
       "4      Aujourdâ€™hui, lâ€™oiseau nous invite Ã  sa ta...   \n",
       "8      At the time of her construction in the late 19...   \n",
       "10     The earliest medieval Jewish mystical writings...   \n",
       "27     This illustrated work is intended to acquaint ...   \n",
       "30                                                   NaN   \n",
       "...                                                  ...   \n",
       "99988  A Novella that brings out a story of a person ...   \n",
       "99989  Perditaâ€™s life has gone back to normal in al...   \n",
       "99990  Ilja Gort is een Nederlandse wijnboer in Frank...   \n",
       "99995  Design custom printed circuit boards with EAGL...   \n",
       "99996  Redefining tradition in learning accounting. ,...   \n",
       "\n",
       "                                                   genre  \\\n",
       "4                                                    NaN   \n",
       "8                                                    NaN   \n",
       "10                                                   NaN   \n",
       "27                                                   NaN   \n",
       "30                                   Erotica,Gay Erotica   \n",
       "...                                                  ...   \n",
       "99988                                                NaN   \n",
       "99989  Fantasy,Paranormal,Young Adult,Fantasy,Shapesh...   \n",
       "99990                                                NaN   \n",
       "99995                                                NaN   \n",
       "99996                                                NaN   \n",
       "\n",
       "                                               image_url        isbn  \\\n",
       "4      https://i.gr-assets.com/images/S/compressed.ph...  2761920813   \n",
       "8      https://i.gr-assets.com/images/S/compressed.ph...   752456830   \n",
       "10     https://i.gr-assets.com/images/S/compressed.ph...   791407209   \n",
       "27                                                   NaN   913750336   \n",
       "30     https://i.gr-assets.com/images/S/compressed.ph...   943595614   \n",
       "...                                                  ...         ...   \n",
       "99988  https://i.gr-assets.com/images/S/compressed.ph...         NaN   \n",
       "99989  https://i.gr-assets.com/images/S/compressed.ph...         NaN   \n",
       "99990  https://i.gr-assets.com/images/S/compressed.ph...         NaN   \n",
       "99995  https://i.gr-assets.com/images/S/compressed.ph...    71819266   \n",
       "99996  https://i.gr-assets.com/images/S/compressed.ph...   133251241   \n",
       "\n",
       "                                                     url  pages  rating  \\\n",
       "4      https://goodreads.com/book/show/10010880-les-o...    177    4.00   \n",
       "8          https://goodreads.com/book/show/10011431-r101    160    5.00   \n",
       "10     https://goodreads.com/book/show/10011932-the-b...    282    4.75   \n",
       "27     https://goodreads.com/book/show/10013570-histo...    312    5.00   \n",
       "30     https://goodreads.com/book/show/1001377.The_Mi...    160    3.46   \n",
       "...                                                  ...    ...     ...   \n",
       "99988           https://goodreads.com/book/show/17319699     90    3.14   \n",
       "99989    https://goodreads.com/book/show/17319882-purity      0    4.05   \n",
       "99990     https://goodreads.com/book/show/17319892-slurp    246    3.64   \n",
       "99995  https://goodreads.com/book/show/17320181-make-...    304    4.07   \n",
       "99996  https://goodreads.com/book/show/17320260-horng...   1645    4.05   \n",
       "\n",
       "       reviews                                              title  \\\n",
       "4            1                              Les oiseaux gourmands   \n",
       "8            2                          R101: A Pictorial History   \n",
       "10           1  The Books of Contemplation: Medieval Jewish My...   \n",
       "27           0    History of Cartography: Enlarged Second Edition   \n",
       "30           1                     The Milk Farm: An Erotic Novel   \n",
       "...        ...                                                ...   \n",
       "99988        3                                       à¤…à¤œà¤—à¤°   \n",
       "99989       64                                             Purity   \n",
       "99990        1                                             Slurp!   \n",
       "99995        7  Make Your Own PCBs with Eagle: From Schematic ...   \n",
       "99996        1       Horngren's Financial & Managerial Accounting   \n",
       "\n",
       "       total_ratings  \n",
       "4                  1  \n",
       "8                  3  \n",
       "10                 4  \n",
       "27                 1  \n",
       "30                13  \n",
       "...              ...  \n",
       "99988             14  \n",
       "99989           1244  \n",
       "99990             14  \n",
       "99995             56  \n",
       "99996             38  \n",
       "\n",
       "[26281 rows x 11 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a DataFrame with rows containing null values in any column\n",
    "null_rows_df = books_df[books_df.isnull().any(axis=1)]\n",
    "null_rows_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "21e35629",
   "metadata": {},
   "outputs": [],
   "source": [
    "from concurrent.futures import ThreadPoolExecutor, as_completed\n",
    "\n",
    "# Function to fetch data from Google Books API\n",
    "def fetch_book_data(isbn, title, author):\n",
    "    # Construct the query\n",
    "    query = isbn if pd.notna(isbn) else f'intitle:{title}+inauthor:{author}'\n",
    "    url = f\"https://www.googleapis.com/books/v1/volumes?q={query}\"\n",
    "    \n",
    "    # Initialize empty data dictionary\n",
    "    book_data = {\n",
    "        'description': None,\n",
    "        'genres': None,\n",
    "        'year_of_publication': None,\n",
    "        'language': None\n",
    "    }\n",
    "    \n",
    "    try:\n",
    "        response = requests.get(url)\n",
    "        response.raise_for_status()  # Will raise an HTTPError if the HTTP request returned an unsuccessful status code\n",
    "        \n",
    "        # Parse response if items are present\n",
    "        if 'items' in response.json():\n",
    "            book_info = response.json()['items'][0]['volumeInfo']\n",
    "            \n",
    "            # Get the necessary information if available\n",
    "            book_data['description'] = book_info.get('description')\n",
    "            book_data['genres'] = ', '.join(book_info.get('categories', []))\n",
    "            book_data['year_of_publication'] = book_info.get('publishedDate', '').split('-')[0]  # Just get the year part\n",
    "            book_data['language'] = book_info.get('language')\n",
    "            \n",
    "    except requests.exceptions.HTTPError as e:\n",
    "        # Add more error handling here depending on the requirements\n",
    "        print(f\"HTTPError for ISBN/Title '{isbn or title}': {e}\")\n",
    "    \n",
    "    except requests.exceptions.RequestException as e:\n",
    "        # Handle any other exceptions\n",
    "        print(f\"RequestException for ISBN/Title '{isbn or title}': {e}\")\n",
    "    \n",
    "    return book_data\n",
    "\n",
    "# Function to update the DataFrame\n",
    "def update_books_df(df):\n",
    "    with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "        future_to_row = {executor.submit(fetch_book_data, row['isbn'], row['title'], row['author']): index for index, row in df.iterrows()}\n",
    "        \n",
    "        for future in as_completed(future_to_row):\n",
    "            index = future_to_row[future]\n",
    "            try:\n",
    "                result = future.result()\n",
    "                # Update the DataFrame if data is found\n",
    "                if result['description']:\n",
    "                    df.at[index, 'Description'] = result['description']\n",
    "                if result['genres']:\n",
    "                    df.at[index, 'Genre'] = result['genres']\n",
    "                if result['year_of_publication']:\n",
    "                    df.at[index, 'YearOfPublication'] = result['year_of_publication']\n",
    "                if result['language']:\n",
    "                    df.at[index, 'Language'] = result['language']\n",
    "                    \n",
    "            except Exception as e:\n",
    "                print(f\"Exception for row {index}: {e}\")\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "14adaec9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# It is a time-consuming process (approximately 4 hours) \n",
    "# and doesn't provide comprehensive information for each book, \n",
    "# which is why I ultimately choose to skip this step.\n",
    "\n",
    "# Call the function\n",
    "# null_rows_df = update_books_df(null_rows_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "7dba72b1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of data to drop: 26.28%\n"
     ]
    }
   ],
   "source": [
    "# Calculate the percentage of data represented by null_values_df\n",
    "percentage_to_drop = (len(null_rows_df) / len(books_df)) * 100\n",
    "\n",
    "# Print the percentage\n",
    "print(f\"Percentage of data to drop: {percentage_to_drop:.2f}%\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "b857fdc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop rows from books_df where any column has null values\n",
    "books_df.dropna(axis=0, how='any', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef4ab6e6",
   "metadata": {},
   "source": [
    "**The decision to remove rows with any null values from the \"books_df\" DataFrame is primarily driven by time constraints and the need to expedite the project's progress. While it is acknowledged that this action may lead to a reduction in the overall data quality and potentially affect the accuracy of the book recommender, it is a pragmatic choice made to maintain workflow efficiency. The time-consuming nature of making requests to various APIs has prompted this decision, as it allows for faster development and prevents undue delays in the project timeline.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "cc02e21d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73719, 11)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "cc05598a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "author           0\n",
       "description      0\n",
       "genre            0\n",
       "image_url        0\n",
       "isbn             0\n",
       "url              0\n",
       "pages            0\n",
       "rating           0\n",
       "reviews          0\n",
       "title            0\n",
       "total_ratings    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cfa8581",
   "metadata": {},
   "source": [
    "### 2. users_books_df\n",
    "\n",
    "I will fill the age missing values lately in the EDA part. And since I only have 1 missing value for author and 2 for publisher I will fill them manually checking in the web using the ISBN of the book.\n",
    "\n",
    "(Finally I won't use this df so it's not in the EDA notebook)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "81897da5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Identify rows with missing values in the 'author' and 'publisher' columns\n",
    "\n",
    "missing_author_publisher = users_books_df[users_books_df['author'].isnull() | users_books_df['publisher'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "3483b778",
   "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>location</th>\n",
       "      <th>age</th>\n",
       "      <th>isbn</th>\n",
       "      <th>rating</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "      <th>image_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>857163</th>\n",
       "      <td>morrow, georgia, usa</td>\n",
       "      <td>52.0</td>\n",
       "      <td>193169656X</td>\n",
       "      <td>9</td>\n",
       "      <td>Tyrant Moon</td>\n",
       "      <td>Elaine Corvidae</td>\n",
       "      <td>2002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://images.amazon.com/images/P/193169656X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857174</th>\n",
       "      <td>morrow, georgia, usa</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1931696993</td>\n",
       "      <td>9</td>\n",
       "      <td>Finders Keepers</td>\n",
       "      <td>Linnea Sinclair</td>\n",
       "      <td>2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://images.amazon.com/images/P/1931696993.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>857604</th>\n",
       "      <td>selayang, selangor, malaysia</td>\n",
       "      <td>26.0</td>\n",
       "      <td>9627982032</td>\n",
       "      <td>8</td>\n",
       "      <td>The Credit Suisse Guide to Managing Your Perso...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995</td>\n",
       "      <td>Edinburgh Financial Publishing</td>\n",
       "      <td>http://images.amazon.com/images/P/9627982032.0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            location   age        isbn  rating  \\\n",
       "857163          morrow, georgia, usa  52.0  193169656X       9   \n",
       "857174          morrow, georgia, usa  52.0  1931696993       9   \n",
       "857604  selayang, selangor, malaysia  26.0  9627982032       8   \n",
       "\n",
       "                                                    title           author  \\\n",
       "857163                                        Tyrant Moon  Elaine Corvidae   \n",
       "857174                                    Finders Keepers  Linnea Sinclair   \n",
       "857604  The Credit Suisse Guide to Managing Your Perso...              NaN   \n",
       "\n",
       "        year                       publisher  \\\n",
       "857163  2002                             NaN   \n",
       "857174  2001                             NaN   \n",
       "857604  1995  Edinburgh Financial Publishing   \n",
       "\n",
       "                                                image_url  \n",
       "857163  http://images.amazon.com/images/P/193169656X.0...  \n",
       "857174  http://images.amazon.com/images/P/1931696993.0...  \n",
       "857604  http://images.amazon.com/images/P/9627982032.0...  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_author_publisher"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "726b748d",
   "metadata": {},
   "source": [
    "I manually searched for missing information on the web using the ISBN and filled in the missing data for specific ISBN values in the `users_books_df`.\n",
    "\n",
    "- ISBN 193169656X: Filled 'publisher' with 'NovelBooks, Inc.'\n",
    "- ISBN 1931696993: Filled 'publisher' with 'CreateSpace Independent Publishing Platform'\n",
    "- ISBN 9627982032: Filled 'author' with 'n/a'\n",
    "\n",
    "\"By n/a (Author)\" typically indicates that the author's name for a particular book or publication is not available or not provided. \"n/a\" stands for \"not applicable\" or \"not available,\" and it is used as a placeholder when there is no specific author mentioned for the book. This can occur for various reasons, such as when the book is a compilation of works from multiple authors, when the author's name is unknown, or when the information is missing or not provided in the source data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "7b3412e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill 'publisher' column for specific ISBN values\n",
    "users_books_df.loc[users_books_df['isbn'] == '193169656X', 'publisher'] = 'NovelBooks, Inc.'\n",
    "users_books_df.loc[users_books_df['isbn'] == '1931696993', 'publisher'] = 'CreateSpace Independent Publishing Platform'\n",
    "\n",
    "# Fill 'author' column for a specific ISBN value\n",
    "users_books_df.loc[users_books_df['isbn'] == '9627982032', 'author'] = 'n/a'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "ae40468f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "location          0\n",
       "age          277835\n",
       "isbn              0\n",
       "rating            0\n",
       "title             0\n",
       "author            0\n",
       "year              0\n",
       "publisher         0\n",
       "image_url         0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "befb9cf8",
   "metadata": {},
   "source": [
    "### 3. gr_books_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51993043",
   "metadata": {},
   "source": [
    "### Fill missing information using Google Books API\n",
    "\n",
    "Same issue. As I previously said."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f7b4b11b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import concurrent.futures\n",
    "from concurrent.futures import ThreadPoolExecutor\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "def fetch_book_data(isbn):\n",
    "    url = f\"https://www.googleapis.com/books/v1/volumes?q=isbn:{isbn}\"\n",
    "    response = requests.get(url)\n",
    "    if response.status_code == 200:\n",
    "        data = response.json()\n",
    "        if 'items' in data and data['totalItems'] > 0:\n",
    "            book_info = data['items'][0]['volumeInfo']\n",
    "            return book_info.get('description', ''), book_info.get('categories', [])\n",
    "    return '', []\n",
    "\n",
    "# Function to run fetch_book_data in parallel\n",
    "def fetch_descriptions_and_genres(isbns):\n",
    "    book_data = {}\n",
    "    with ThreadPoolExecutor() as executor:\n",
    "        future_to_isbn = {executor.submit(fetch_book_data, isbn): isbn for isbn in isbns}\n",
    "        for future in as_completed(future_to_isbn):\n",
    "            isbn = future_to_isbn[future]\n",
    "            try:\n",
    "                book_data[isbn] = future.result()\n",
    "            except Exception as e:\n",
    "                book_data[isbn] = ('', [])\n",
    "    return book_data\n",
    "\n",
    "# Function to update the DataFrame with descriptions and genres\n",
    "def update_dataframe(df):\n",
    "    isbns = df['isbn'].tolist()\n",
    "    book_data = fetch_descriptions_and_genres(isbns)\n",
    "    \n",
    "    # Update DataFrame with the fetched data\n",
    "    for isbn, (description, genres) in book_data.items():\n",
    "        df.loc[df['isbn'] == isbn, 'Description'] = description\n",
    "        df.loc[df['isbn'] == isbn, 'Genre'] = ', '.join(genres)  # Join genres list into a string\n",
    "    \n",
    "    return df\n",
    "\n",
    "# Same issue. Takes too long.\n",
    "# gr_books_df = update_dataframe(gr_books_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "52b861fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11123, 10)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_books_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c673006f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title            0\n",
       "author           0\n",
       "rating           0\n",
       "isbn             0\n",
       "language         0\n",
       "pages            0\n",
       "ratings_count    0\n",
       "reviews_count    0\n",
       "year             0\n",
       "publisher        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_books_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bdfae10",
   "metadata": {},
   "source": [
    "Finally, due time constrictions I will only use the books_df for clustering."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18fd14e4",
   "metadata": {},
   "source": [
    "## Check Duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "e8adf3d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "3824\n",
      "803\n",
      "0\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "print(books_df.duplicated().sum())\n",
    "print(users_books_df.duplicated().sum())\n",
    "print(best_selling_df.duplicated().sum())\n",
    "print(goodreads_df.duplicated().sum())\n",
    "print(gr_books_df.duplicated().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "ea077b08",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop duplicates from the users_books_df\n",
    "users_books_df = users_books_df.drop_duplicates()\n",
    "\n",
    "users_books_df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdc682f9",
   "metadata": {},
   "source": [
    "## Check % of null values, Data types and Unique Values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef96f88c",
   "metadata": {},
   "source": [
    "### 1. books_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "4d01f8ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "author           0.0\n",
       "description      0.0\n",
       "genre            0.0\n",
       "image_url        0.0\n",
       "isbn             0.0\n",
       "url              0.0\n",
       "pages            0.0\n",
       "rating           0.0\n",
       "reviews          0.0\n",
       "title            0.0\n",
       "total_ratings    0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# % of null values\n",
    "missing_percentage = (books_df.isnull().sum() / len(books_df)) * 100\n",
    "missing_percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "92edba81",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "author            object\n",
       "description       object\n",
       "genre             object\n",
       "image_url         object\n",
       "isbn              object\n",
       "url               object\n",
       "pages              int64\n",
       "rating           float64\n",
       "reviews            int64\n",
       "title             object\n",
       "total_ratings      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check data types\n",
    "books_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "a4d5a4d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "author           51979\n",
       "description      73336\n",
       "genre            61272\n",
       "image_url        73719\n",
       "isbn             73719\n",
       "url              73719\n",
       "pages             1248\n",
       "rating             267\n",
       "reviews           2734\n",
       "title            72368\n",
       "total_ratings     9716\n",
       "dtype: int64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# See unique values in each column\n",
    "books_df.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aee76450",
   "metadata": {},
   "source": [
    "It seems that I have 73719 different isbn so **73.719 different books**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fced56b5",
   "metadata": {},
   "source": [
    "### 2. users_books_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "bc69787d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "location      0.000000\n",
       "age          26.783587\n",
       "isbn          0.000000\n",
       "rating        0.000000\n",
       "title         0.000000\n",
       "author        0.000000\n",
       "year          0.000000\n",
       "publisher     0.000000\n",
       "image_url     0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_percentage_users_books = (users_books_df.isnull().sum() / len(users_books_df)) * 100\n",
    "missing_percentage_users_books"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "aea953b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "location      object\n",
       "age          float64\n",
       "isbn          object\n",
       "rating         int64\n",
       "title         object\n",
       "author        object\n",
       "year          object\n",
       "publisher     object\n",
       "image_url     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "418e5c4a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2002, 2001, 1991, 1999, 2000, 1993, 1996, 1988, 2004, 1998, 1994,\n",
       "       2003, 1997, 1987, 1989, 1992, 1983, 1979, 1995, 1982, 1985, 1986,\n",
       "       1978, 1980, 1952, 1990, 1981, 0, 1984, 1968, 1961, 1959, 1946,\n",
       "       1974, 1958, 1976, 1971, '2002', 1977, 1975, 1965, 1941, 1970,\n",
       "       '1996', '1999', 1972, '1989', '1992', '2001', 1953, 1962, 1973,\n",
       "       '2004', 1960, '1997', '2000', '0', 1954, 1966, 1920, '1998',\n",
       "       '2003', 1967, 1956, 1942, 1955, 1951, 1969, 1948, '1977', '1990',\n",
       "       '1986', '1994', '1982', 1963, 1964, '1995', '1959', '1988', 1950,\n",
       "       '1991', '1984', 2005, 1943, '1993', '1987', '1983', '1981', 1957,\n",
       "       '1972', '1980', 1937, 1940, '1965', '1970', '1979', 1930, 1923,\n",
       "       1945, 1947, '1976', 1936, '1985', '1978', 1925, '1966', '1958',\n",
       "       '1975', '1973', 1927, '1974', '1967', '1971', '1969', 2030, '1968',\n",
       "       2011, '1960', '1964', 2020, 1939, 1926, 1938, '1961', 1911, 1904,\n",
       "       1949, 1932, 1929, '1953', '1963', '1955', '1944', '1920', '1956',\n",
       "       '1957', '1942', '1933', '1962', '1952', '1922', '1897', '1954',\n",
       "       '1941', '1949', '1939', 2050, '1947', 1902, 1924, 1921, 1900,\n",
       "       '1945', '2005', 2038, 2010, 1928, '1932', '1911', '2006', '1948',\n",
       "       '1923', '1943', 1901, 2026, '1900', 2021, '1951', 1931, '1940',\n",
       "       '1378', '2030', '1946', '1950', 1908, '1938', 'DK Publishing Inc',\n",
       "       'Gallimard', '1909', '1924', '2012', '2008', '1936', 1935, '1376',\n",
       "       '1926', '2037', '1931', '1927', 1906, 1806, 1933, 1944, 1917,\n",
       "       '2020', '1930', '2011', '1919', 1914, 1934, 1910, '2024'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Changing year to date type\n",
    "\n",
    "unique_years = users_books_df['year'].unique()\n",
    "unique_years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "4f978b4e",
   "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>location</th>\n",
       "      <th>age</th>\n",
       "      <th>isbn</th>\n",
       "      <th>rating</th>\n",
       "      <th>title</th>\n",
       "      <th>author</th>\n",
       "      <th>year</th>\n",
       "      <th>publisher</th>\n",
       "      <th>image_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82630</th>\n",
       "      <td>bremen, bremen, germany</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3453137205</td>\n",
       "      <td>7</td>\n",
       "      <td>Marchenmond</td>\n",
       "      <td>Wolfgang Hohlbein</td>\n",
       "      <td>0</td>\n",
       "      <td>Wilhelm Heyne Verlag GmbH &amp;amp; Co KG</td>\n",
       "      <td>http://images.amazon.com/images/P/3453137205.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180775</th>\n",
       "      <td>milan, lombardia, italy</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8804382449</td>\n",
       "      <td>7</td>\n",
       "      <td>Fiori Nella Pioggia</td>\n",
       "      <td>Rosamunde Pilcher</td>\n",
       "      <td>0</td>\n",
       "      <td>Transglobal Books Inc</td>\n",
       "      <td>http://images.amazon.com/images/P/8804382449.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180776</th>\n",
       "      <td>monza, lombardia, italy</td>\n",
       "      <td>26.0</td>\n",
       "      <td>8804382449</td>\n",
       "      <td>0</td>\n",
       "      <td>Fiori Nella Pioggia</td>\n",
       "      <td>Rosamunde Pilcher</td>\n",
       "      <td>0</td>\n",
       "      <td>Transglobal Books Inc</td>\n",
       "      <td>http://images.amazon.com/images/P/8804382449.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207865</th>\n",
       "      <td>madrid, madrid, spain</td>\n",
       "      <td>31.0</td>\n",
       "      <td>840142285X</td>\n",
       "      <td>8</td>\n",
       "      <td>Un Mundo Feliz (Fiction, Poetry and Drama)</td>\n",
       "      <td>Huxley</td>\n",
       "      <td>0</td>\n",
       "      <td>Plaza &amp;amp; Janes Editor</td>\n",
       "      <td>http://images.amazon.com/images/P/840142285X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213598</th>\n",
       "      <td>roma, lazio, italy</td>\n",
       "      <td>29.0</td>\n",
       "      <td>8804401680</td>\n",
       "      <td>7</td>\n",
       "      <td>Ti Con Zero</td>\n",
       "      <td>Italo Calvino</td>\n",
       "      <td>0</td>\n",
       "      <td>Mondadori Italian</td>\n",
       "      <td>http://images.amazon.com/images/P/8804401680.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1030520</th>\n",
       "      <td>omaha, nebraska, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0529044633</td>\n",
       "      <td>0</td>\n",
       "      <td>Charlie Brown Dictionary Volume 1</td>\n",
       "      <td>Charles Schulz</td>\n",
       "      <td>0</td>\n",
       "      <td>Riverside Dist@world Bible</td>\n",
       "      <td>http://images.amazon.com/images/P/0529044633.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1030533</th>\n",
       "      <td>omaha, nebraska, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0553120166</td>\n",
       "      <td>0</td>\n",
       "      <td>Shroud</td>\n",
       "      <td>Robert K Wilcox</td>\n",
       "      <td>0</td>\n",
       "      <td>Bantam Doubleday Dell</td>\n",
       "      <td>http://images.amazon.com/images/P/0553120166.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1030756</th>\n",
       "      <td>omaha, nebraska, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0716620073</td>\n",
       "      <td>0</td>\n",
       "      <td>Christmas In Britain</td>\n",
       "      <td>Edited</td>\n",
       "      <td>0</td>\n",
       "      <td>Random House~childrens</td>\n",
       "      <td>http://images.amazon.com/images/P/0716620073.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1030777</th>\n",
       "      <td>omaha, nebraska, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0785307494</td>\n",
       "      <td>0</td>\n",
       "      <td>Card Games for One Or Two</td>\n",
       "      <td>David Galt</td>\n",
       "      <td>0</td>\n",
       "      <td>Publications International Ltd</td>\n",
       "      <td>http://images.amazon.com/images/P/0785307494.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031070</th>\n",
       "      <td>new york, new york, usa</td>\n",
       "      <td>32.0</td>\n",
       "      <td>8817853712</td>\n",
       "      <td>10</td>\n",
       "      <td>Lettera a Un Bambino Mai Mato</td>\n",
       "      <td>Oriana Fallaci</td>\n",
       "      <td>0</td>\n",
       "      <td>Rizzoli - RCS Libri</td>\n",
       "      <td>http://images.amazon.com/images/P/8817853712.0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1268 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        location   age        isbn  rating  \\\n",
       "82630    bremen, bremen, germany  22.0  3453137205       7   \n",
       "180775   milan, lombardia, italy  21.0  8804382449       7   \n",
       "180776   monza, lombardia, italy  26.0  8804382449       0   \n",
       "207865     madrid, madrid, spain  31.0  840142285X       8   \n",
       "213598        roma, lazio, italy  29.0  8804401680       7   \n",
       "...                          ...   ...         ...     ...   \n",
       "1030520     omaha, nebraska, usa   NaN  0529044633       0   \n",
       "1030533     omaha, nebraska, usa   NaN  0553120166       0   \n",
       "1030756     omaha, nebraska, usa   NaN  0716620073       0   \n",
       "1030777     omaha, nebraska, usa   NaN  0785307494       0   \n",
       "1031070  new york, new york, usa  32.0  8817853712      10   \n",
       "\n",
       "                                              title             author year  \\\n",
       "82630                                   Marchenmond  Wolfgang Hohlbein    0   \n",
       "180775                          Fiori Nella Pioggia  Rosamunde Pilcher    0   \n",
       "180776                          Fiori Nella Pioggia  Rosamunde Pilcher    0   \n",
       "207865   Un Mundo Feliz (Fiction, Poetry and Drama)             Huxley    0   \n",
       "213598                                  Ti Con Zero      Italo Calvino    0   \n",
       "...                                             ...                ...  ...   \n",
       "1030520           Charlie Brown Dictionary Volume 1     Charles Schulz    0   \n",
       "1030533                                      Shroud    Robert K Wilcox    0   \n",
       "1030756                        Christmas In Britain             Edited    0   \n",
       "1030777                   Card Games for One Or Two         David Galt    0   \n",
       "1031070               Lettera a Un Bambino Mai Mato     Oriana Fallaci    0   \n",
       "\n",
       "                                     publisher  \\\n",
       "82630    Wilhelm Heyne Verlag GmbH &amp; Co KG   \n",
       "180775                   Transglobal Books Inc   \n",
       "180776                   Transglobal Books Inc   \n",
       "207865                Plaza &amp; Janes Editor   \n",
       "213598                       Mondadori Italian   \n",
       "...                                        ...   \n",
       "1030520             Riverside Dist@world Bible   \n",
       "1030533                  Bantam Doubleday Dell   \n",
       "1030756                 Random House~childrens   \n",
       "1030777         Publications International Ltd   \n",
       "1031070                    Rizzoli - RCS Libri   \n",
       "\n",
       "                                                 image_url  \n",
       "82630    http://images.amazon.com/images/P/3453137205.0...  \n",
       "180775   http://images.amazon.com/images/P/8804382449.0...  \n",
       "180776   http://images.amazon.com/images/P/8804382449.0...  \n",
       "207865   http://images.amazon.com/images/P/840142285X.0...  \n",
       "213598   http://images.amazon.com/images/P/8804401680.0...  \n",
       "...                                                    ...  \n",
       "1030520  http://images.amazon.com/images/P/0529044633.0...  \n",
       "1030533  http://images.amazon.com/images/P/0553120166.0...  \n",
       "1030756  http://images.amazon.com/images/P/0716620073.0...  \n",
       "1030777  http://images.amazon.com/images/P/0785307494.0...  \n",
       "1031070  http://images.amazon.com/images/P/8817853712.0...  \n",
       "\n",
       "[1268 rows x 9 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Locate the values that contain letters or less than 3 digits\n",
    "filtered_df = users_books_df[users_books_df['year'].str.contains('[A-Za-z]', na=False) | (users_books_df['year'].str.len() < 3)]\n",
    "\n",
    "# Display the filtered DataFrame\n",
    "filtered_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "d7eebbc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "filt_years = filtered_df['year'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "110d0d12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['0', 'DK Publishing Inc', 'Gallimard'], dtype=object)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filt_years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "070b2d88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1027312, 9)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "393b3b69",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of values in filtered_df relative to users_books_df: 0.12%\n"
     ]
    }
   ],
   "source": [
    "percentage = (len(filtered_df) / len(users_books_df)) * 100\n",
    "print(f\"Percentage of values in filtered_df relative to users_books_df: {percentage:.2f}%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a014d0a2",
   "metadata": {},
   "source": [
    "Given that the percentage of values in `filtered_df` relative to the total DataFrame `users_books_df` is only 0.12%, which is an extremely small and insignificant portion of the dataset, it is justifiable to proceed with the decision to remove these rows. Removing such a small fraction of the data will have negligible impact on the overall analysis and modeling while simplifying the dataset by eliminating rows with non-standard or irrelevant values. This action ensures that the dataset remains cleaner and more focused on the relevant data for the analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "34b09827",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1026044, 9)"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Define the values to be removed\n",
    "values_to_remove = ['0', 'DK Publishing Inc', 'Gallimard']\n",
    "\n",
    "# Remove rows with specified values in the 'year' column\n",
    "users_books_df = users_books_df[~users_books_df['year'].isin(values_to_remove)]\n",
    "users_books_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "39a5a12d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2002, 2001, 1991, 1999, 2000, 1993, 1996, 1988, 2004, 1998, 1994,\n",
       "       2003, 1997, 1987, 1989, 1992, 1983, 1979, 1995, 1982, 1985, 1986,\n",
       "       1978, 1980, 1952, 1990, 1981, 0, 1984, 1968, 1961, 1959, 1946,\n",
       "       1974, 1958, 1976, 1971, '2002', 1977, 1975, 1965, 1941, 1970,\n",
       "       '1996', '1999', 1972, '1989', '1992', '2001', 1953, 1962, 1973,\n",
       "       '2004', 1960, '1997', '2000', 1954, 1966, 1920, '1998', '2003',\n",
       "       1967, 1956, 1942, 1955, 1951, 1969, 1948, '1977', '1990', '1986',\n",
       "       '1994', '1982', 1963, 1964, '1995', '1959', '1988', 1950, '1991',\n",
       "       '1984', 2005, 1943, '1993', '1987', '1983', '1981', 1957, '1972',\n",
       "       '1980', 1937, 1940, '1965', '1970', '1979', 1930, 1923, 1945, 1947,\n",
       "       '1976', 1936, '1985', '1978', 1925, '1966', '1958', '1975', '1973',\n",
       "       1927, '1974', '1967', '1971', '1969', 2030, '1968', 2011, '1960',\n",
       "       '1964', 2020, 1939, 1926, 1938, '1961', 1911, 1904, 1949, 1932,\n",
       "       1929, '1953', '1963', '1955', '1944', '1920', '1956', '1957',\n",
       "       '1942', '1933', '1962', '1952', '1922', '1897', '1954', '1941',\n",
       "       '1949', '1939', 2050, '1947', 1902, 1924, 1921, 1900, '1945',\n",
       "       '2005', 2038, 2010, 1928, '1932', '1911', '2006', '1948', '1923',\n",
       "       '1943', 1901, 2026, '1900', 2021, '1951', 1931, '1940', '1378',\n",
       "       '2030', '1946', '1950', 1908, '1938', '1909', '1924', '2012',\n",
       "       '2008', '1936', 1935, '1376', '1926', '2037', '1931', '1927', 1906,\n",
       "       1806, 1933, 1944, 1917, '2020', '1930', '2011', '1919', 1914, 1934,\n",
       "       1910, '2024'], dtype=object)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_years = users_books_df['year'].unique()\n",
    "unique_years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "61197ea5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the 'year' column to datetime\n",
    "users_books_df['year'] = pd.to_datetime(users_books_df['year'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "f35a0e68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "location             object\n",
       "age                 float64\n",
       "isbn                 object\n",
       "rating                int64\n",
       "title                object\n",
       "author               object\n",
       "year         datetime64[ns]\n",
       "publisher            object\n",
       "image_url            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "62424268",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "location      22464\n",
       "age             141\n",
       "isbn         269119\n",
       "rating           11\n",
       "title        240222\n",
       "author       101246\n",
       "year            196\n",
       "publisher     16635\n",
       "image_url    268814\n",
       "dtype: int64"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_books_df.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09d59297",
   "metadata": {},
   "source": [
    "### 3. best_selling_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "8f58b309",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title     0.0\n",
       "price     0.0\n",
       "rating    0.0\n",
       "author    0.0\n",
       "year      0.0\n",
       "genre     0.0\n",
       "url       0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_percentage_best_selling = (best_selling_df.isnull().sum() / len(best_selling_df)) * 100\n",
    "missing_percentage_best_selling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "d9476fb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title      object\n",
       "price     float64\n",
       "rating    float64\n",
       "author     object\n",
       "year        int64\n",
       "genre      object\n",
       "url        object\n",
       "dtype: object"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "best_selling_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "040645da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2023, 2018, 2017, 2014, 2020, 1960, 1998, 2022, 2009, 2015, 2013,\n",
       "       2011, 2021, 1967, 1997, 1969, 2019, 1947, 1989, 2016, 1980, 2024,\n",
       "       2008, 2005, 1999, 1982, 1987])"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_years = best_selling_df['year'].unique()\n",
    "unique_years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "c981ada2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the 'year' column to datetime\n",
    "best_selling_df['year'] = pd.to_datetime(best_selling_df['year'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "649bb476",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title             object\n",
       "price            float64\n",
       "rating           float64\n",
       "author            object\n",
       "year      datetime64[ns]\n",
       "genre             object\n",
       "url               object\n",
       "dtype: object"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "best_selling_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "85c8e22e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title     86\n",
       "price     83\n",
       "rating     9\n",
       "author    75\n",
       "year      27\n",
       "genre     70\n",
       "url       87\n",
       "dtype: int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "best_selling_df.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef3886fb",
   "metadata": {},
   "source": [
    "### 4. goodreads_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "e0e66d6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title        0.0\n",
       "author       0.0\n",
       "image_url    0.0\n",
       "rating       0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_percentage_goodreads = (goodreads_df.isnull().sum() / len(goodreads_df)) * 100\n",
    "missing_percentage_goodreads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "f9a01985",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title        object\n",
       "author       object\n",
       "image_url    object\n",
       "rating       object\n",
       "dtype: object"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodreads_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "2a0d7c00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title        100\n",
       "author        91\n",
       "image_url    100\n",
       "rating       100\n",
       "dtype: int64"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodreads_df.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "21d95a5d",
   "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>title</th>\n",
       "      <th>author</th>\n",
       "      <th>image_url</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fourth Wing (The Empyrean, #1)</td>\n",
       "      <td>Rebecca Yarros</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.63 avg rating — 844,282 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Happy Place</td>\n",
       "      <td>Emily Henry</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.06 avg rating — 574,386 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Yellowface</td>\n",
       "      <td>R.F. Kuang</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>3.87 avg rating — 227,094 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Love, Theoretically</td>\n",
       "      <td>Ali Hazelwood</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.17 avg rating — 239,576 ratings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Divine Rivals (Letters of Enchantment, #1)</td>\n",
       "      <td>Rebecca   Ross</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.26 avg rating — 161,237 ratings</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        title          author  \\\n",
       "0              Fourth Wing (The Empyrean, #1)  Rebecca Yarros   \n",
       "1                                 Happy Place     Emily Henry   \n",
       "2                                  Yellowface      R.F. Kuang   \n",
       "3                         Love, Theoretically   Ali Hazelwood   \n",
       "4  Divine Rivals (Letters of Enchantment, #1)  Rebecca   Ross   \n",
       "\n",
       "                                           image_url  \\\n",
       "0  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "1  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "2  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "3  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "4  https://i.gr-assets.com/images/S/compressed.ph...   \n",
       "\n",
       "                              rating  \n",
       "0  4.63 avg rating — 844,282 ratings  \n",
       "1  4.06 avg rating — 574,386 ratings  \n",
       "2  3.87 avg rating — 227,094 ratings  \n",
       "3  4.17 avg rating — 239,576 ratings  \n",
       "4  4.26 avg rating — 161,237 ratings  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodreads_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcad6c8e",
   "metadata": {},
   "source": [
    "I will split the \"rating\" column into two separate columns \"rating\" and \"rating_count\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "a0c0deef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split the \"rating\" column into \"rating\" and \"rating_count\" columns\n",
    "goodreads_df[['rating', 'rating_count']] = goodreads_df['rating'].str.extract(r'([\\d.]+) avg rating — ([\\d,]+) ratings')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "857080d7",
   "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>title</th>\n",
       "      <th>author</th>\n",
       "      <th>image_url</th>\n",
       "      <th>rating</th>\n",
       "      <th>rating_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fourth Wing (The Empyrean, #1)</td>\n",
       "      <td>Rebecca Yarros</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.63</td>\n",
       "      <td>844,282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Happy Place</td>\n",
       "      <td>Emily Henry</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.06</td>\n",
       "      <td>574,386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Yellowface</td>\n",
       "      <td>R.F. Kuang</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>3.87</td>\n",
       "      <td>227,094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Love, Theoretically</td>\n",
       "      <td>Ali Hazelwood</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.17</td>\n",
       "      <td>239,576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Divine Rivals (Letters of Enchantment, #1)</td>\n",
       "      <td>Rebecca   Ross</td>\n",
       "      <td>https://i.gr-assets.com/images/S/compressed.ph...</td>\n",
       "      <td>4.26</td>\n",
       "      <td>161,237</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        title          author  \\\n",
       "0              Fourth Wing (The Empyrean, #1)  Rebecca Yarros   \n",
       "1                                 Happy Place     Emily Henry   \n",
       "2                                  Yellowface      R.F. Kuang   \n",
       "3                         Love, Theoretically   Ali Hazelwood   \n",
       "4  Divine Rivals (Letters of Enchantment, #1)  Rebecca   Ross   \n",
       "\n",
       "                                           image_url rating rating_count  \n",
       "0  https://i.gr-assets.com/images/S/compressed.ph...   4.63      844,282  \n",
       "1  https://i.gr-assets.com/images/S/compressed.ph...   4.06      574,386  \n",
       "2  https://i.gr-assets.com/images/S/compressed.ph...   3.87      227,094  \n",
       "3  https://i.gr-assets.com/images/S/compressed.ph...   4.17      239,576  \n",
       "4  https://i.gr-assets.com/images/S/compressed.ph...   4.26      161,237  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodreads_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac8bf73a",
   "metadata": {},
   "source": [
    "### 5. gr_books_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "78b2aa64",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title            0.0\n",
       "author           0.0\n",
       "rating           0.0\n",
       "isbn             0.0\n",
       "language         0.0\n",
       "pages            0.0\n",
       "ratings_count    0.0\n",
       "reviews_count    0.0\n",
       "year             0.0\n",
       "publisher        0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_percentage_gr_books = (gr_books_df.isnull().sum() / len(gr_books_df)) * 100\n",
    "missing_percentage_gr_books"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "6fb83eeb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title             object\n",
       "author            object\n",
       "rating           float64\n",
       "isbn              object\n",
       "language          object\n",
       "pages              int64\n",
       "ratings_count      int64\n",
       "reviews_count      int64\n",
       "year              object\n",
       "publisher         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_books_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "7d106251",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title            10348\n",
       "author            6639\n",
       "rating             209\n",
       "isbn             11123\n",
       "language            27\n",
       "pages              997\n",
       "ratings_count     5294\n",
       "reviews_count     1822\n",
       "year              3679\n",
       "publisher         2290\n",
       "dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_books_df.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83c92d12",
   "metadata": {},
   "source": [
    "Due time constrictions I will finally not use this df."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "822c4209",
   "metadata": {},
   "source": [
    "## Saving"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "106e7795",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop \"isbn\" before saving\n",
    "books_df.drop(columns=['isbn'], inplace=True)\n",
    "# Save books_df to CSV\n",
    "books_df.to_csv('books_df_cleaned.csv', index=False)\n",
    "\n",
    "# Save users_books_df to CSV\n",
    "users_books_df.to_csv('users_books_df_cleaned.csv', index=False)\n",
    "\n",
    "# Save best_selling_df to CSV\n",
    "best_selling_df.to_csv('best_selling_df_cleaned.csv', index=False)\n",
    "\n",
    "# Save goodreads_df to CSV\n",
    "goodreads_df.to_csv('goodreads_df_cleaned.csv', index=False)\n",
    "\n",
    "# Save gr_books_df to CSV\n",
    "gr_books_df.to_csv('gr_books_df_cleaned.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}