script_progetto.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PROGETTO 2021 - COMPUTER SCIENCE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importo la libreria pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Per prima cosa, leggo tutti i files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "book_ratings = pd.read_csv(\"C:/Users/HP/F_COMPUTER_SCIENCE/progetto/book-ratings.csv\", encoding = \"latin-1\", delimiter = \";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149775</th>\n",
       "      <td>276704</td>\n",
       "      <td>1563526298</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149776</th>\n",
       "      <td>276706</td>\n",
       "      <td>0679447156</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149777</th>\n",
       "      <td>276709</td>\n",
       "      <td>0515107662</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149778</th>\n",
       "      <td>276721</td>\n",
       "      <td>0590442449</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1149779</th>\n",
       "      <td>276723</td>\n",
       "      <td>05162443314</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1149780 rows × 3 columns</p>\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\n",
       "...          ...          ...          ...\n",
       "1149775   276704   1563526298            9\n",
       "1149776   276706   0679447156            0\n",
       "1149777   276709   0515107662           10\n",
       "1149778   276721   0590442449           10\n",
       "1149779   276723  05162443314            8\n",
       "\n",
       "[1149780 rows x 3 columns]"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "book_ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "books = pd.read_csv(\"C:/Users/HP/F_COMPUTER_SCIENCE/progetto/Books.csv\", encoding = 'latin-1', delimiter = \";\",  \n",
    "                    low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "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",
       "    <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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271354</th>\n",
       "      <td>0440400988</td>\n",
       "      <td>There's a Bat in Bunk Five</td>\n",
       "      <td>Paula Danziger</td>\n",
       "      <td>1988</td>\n",
       "      <td>Random House Childrens Pub (Mm)</td>\n",
       "      <td>http://images.amazon.com/images/P/0440400988.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0440400988.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0440400988.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271355</th>\n",
       "      <td>0525447644</td>\n",
       "      <td>From One to One Hundred</td>\n",
       "      <td>Teri Sloat</td>\n",
       "      <td>1991</td>\n",
       "      <td>Dutton Books</td>\n",
       "      <td>http://images.amazon.com/images/P/0525447644.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0525447644.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0525447644.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271356</th>\n",
       "      <td>006008667X</td>\n",
       "      <td>Lily Dale : The True Story of the Town that Ta...</td>\n",
       "      <td>Christine Wicker</td>\n",
       "      <td>2004</td>\n",
       "      <td>HarperSanFrancisco</td>\n",
       "      <td>http://images.amazon.com/images/P/006008667X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/006008667X.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/006008667X.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271357</th>\n",
       "      <td>0192126040</td>\n",
       "      <td>Republic (World's Classics)</td>\n",
       "      <td>Plato</td>\n",
       "      <td>1996</td>\n",
       "      <td>Oxford University Press</td>\n",
       "      <td>http://images.amazon.com/images/P/0192126040.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0192126040.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0192126040.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>271358</th>\n",
       "      <td>0767409752</td>\n",
       "      <td>A Guided Tour of Rene Descartes' Meditations o...</td>\n",
       "      <td>Christopher  Biffle</td>\n",
       "      <td>2000</td>\n",
       "      <td>McGraw-Hill Humanities/Social Sciences/Languages</td>\n",
       "      <td>http://images.amazon.com/images/P/0767409752.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0767409752.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0767409752.0...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>271359 rows × 8 columns</p>\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",
       "271354  0440400988                         There's a Bat in Bunk Five   \n",
       "271355  0525447644                            From One to One Hundred   \n",
       "271356  006008667X  Lily Dale : The True Story of the Town that Ta...   \n",
       "271357  0192126040                        Republic (World's Classics)   \n",
       "271358  0767409752  A Guided Tour of Rene Descartes' Meditations o...   \n",
       "\n",
       "                 Book-Author Year-Of-Publication  \\\n",
       "0         Mark P. O. Morford                2002   \n",
       "1       Richard Bruce Wright                2001   \n",
       "2               Carlo D'Este                1991   \n",
       "3           Gina Bari Kolata                1999   \n",
       "4            E. J. W. Barber                1999   \n",
       "...                      ...                 ...   \n",
       "271354        Paula Danziger                1988   \n",
       "271355            Teri Sloat                1991   \n",
       "271356      Christine Wicker                2004   \n",
       "271357                 Plato                1996   \n",
       "271358   Christopher  Biffle                2000   \n",
       "\n",
       "                                               Publisher  \\\n",
       "0                                Oxford University Press   \n",
       "1                                  HarperFlamingo Canada   \n",
       "2                                        HarperPerennial   \n",
       "3                                   Farrar Straus Giroux   \n",
       "4                             W. W. Norton &amp; Company   \n",
       "...                                                  ...   \n",
       "271354                   Random House Childrens Pub (Mm)   \n",
       "271355                                      Dutton Books   \n",
       "271356                                HarperSanFrancisco   \n",
       "271357                           Oxford University Press   \n",
       "271358  McGraw-Hill Humanities/Social Sciences/Languages   \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",
       "271354  http://images.amazon.com/images/P/0440400988.0...   \n",
       "271355  http://images.amazon.com/images/P/0525447644.0...   \n",
       "271356  http://images.amazon.com/images/P/006008667X.0...   \n",
       "271357  http://images.amazon.com/images/P/0192126040.0...   \n",
       "271358  http://images.amazon.com/images/P/0767409752.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",
       "271354  http://images.amazon.com/images/P/0440400988.0...   \n",
       "271355  http://images.amazon.com/images/P/0525447644.0...   \n",
       "271356  http://images.amazon.com/images/P/006008667X.0...   \n",
       "271357  http://images.amazon.com/images/P/0192126040.0...   \n",
       "271358  http://images.amazon.com/images/P/0767409752.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...  \n",
       "...                                                   ...  \n",
       "271354  http://images.amazon.com/images/P/0440400988.0...  \n",
       "271355  http://images.amazon.com/images/P/0525447644.0...  \n",
       "271356  http://images.amazon.com/images/P/006008667X.0...  \n",
       "271357  http://images.amazon.com/images/P/0192126040.0...  \n",
       "271358  http://images.amazon.com/images/P/0767409752.0...  \n",
       "\n",
       "[271359 rows x 8 columns]"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodbooks_ratings = pd.read_csv(\"C:/Users/HP/F_COMPUTER_SCIENCE/progetto/goodbooks-ratings.csv\", encoding = 'latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "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>book_id</th>\n",
       "      <th>rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>258</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>4081</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>260</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>9296</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2318</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>4</td>\n",
       "      <td>184</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>4</td>\n",
       "      <td>495</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>4</td>\n",
       "      <td>350</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>4</td>\n",
       "      <td>492</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>4</td>\n",
       "      <td>219</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id  book_id  rating\n",
       "0         1      258       5\n",
       "1         2     4081       4\n",
       "2         2      260       5\n",
       "3         2     9296       5\n",
       "4         2     2318       3\n",
       "..      ...      ...     ...\n",
       "94        4      184       4\n",
       "95        4      495       4\n",
       "96        4      350       3\n",
       "97        4      492       4\n",
       "98        4      219       4\n",
       "\n",
       "[99 rows x 3 columns]"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodbooks_ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodbooks = pd.read_csv(\"C:/Users/HP/F_COMPUTER_SCIENCE/progetto/goodbooks.csv\", encoding = 'latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book_id</th>\n",
       "      <th>goodreads_book_id</th>\n",
       "      <th>best_book_id</th>\n",
       "      <th>work_id</th>\n",
       "      <th>books_count</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>authors</th>\n",
       "      <th>original_publication_year</th>\n",
       "      <th>original_title</th>\n",
       "      <th>...</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>work_ratings_count</th>\n",
       "      <th>work_text_reviews_count</th>\n",
       "      <th>ratings_1</th>\n",
       "      <th>ratings_2</th>\n",
       "      <th>ratings_3</th>\n",
       "      <th>ratings_4</th>\n",
       "      <th>ratings_5</th>\n",
       "      <th>image_url</th>\n",
       "      <th>small_image_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2767052</td>\n",
       "      <td>2767052</td>\n",
       "      <td>2792775</td>\n",
       "      <td>272</td>\n",
       "      <td>439023483</td>\n",
       "      <td>9.780439e+12</td>\n",
       "      <td>Suzanne Collins</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>The Hunger Games</td>\n",
       "      <td>...</td>\n",
       "      <td>4780653</td>\n",
       "      <td>4942365</td>\n",
       "      <td>155254</td>\n",
       "      <td>66715</td>\n",
       "      <td>127936</td>\n",
       "      <td>560092</td>\n",
       "      <td>1481305</td>\n",
       "      <td>2706317</td>\n",
       "      <td>https://images.gr-assets.com/books/1447303603m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1447303603s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4640799</td>\n",
       "      <td>491</td>\n",
       "      <td>439554934</td>\n",
       "      <td>9.780440e+12</td>\n",
       "      <td>J.K. Rowling, Mary GrandPrÃ©</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>Harry Potter and the Philosopher's Stone</td>\n",
       "      <td>...</td>\n",
       "      <td>4602479</td>\n",
       "      <td>4800065</td>\n",
       "      <td>75867</td>\n",
       "      <td>75504</td>\n",
       "      <td>101676</td>\n",
       "      <td>455024</td>\n",
       "      <td>1156318</td>\n",
       "      <td>3011543</td>\n",
       "      <td>https://images.gr-assets.com/books/1474154022m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1474154022s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>41865</td>\n",
       "      <td>41865</td>\n",
       "      <td>3212258</td>\n",
       "      <td>226</td>\n",
       "      <td>316015849</td>\n",
       "      <td>9.780316e+12</td>\n",
       "      <td>Stephenie Meyer</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>Twilight</td>\n",
       "      <td>...</td>\n",
       "      <td>3866839</td>\n",
       "      <td>3916824</td>\n",
       "      <td>95009</td>\n",
       "      <td>456191</td>\n",
       "      <td>436802</td>\n",
       "      <td>793319</td>\n",
       "      <td>875073</td>\n",
       "      <td>1355439</td>\n",
       "      <td>https://images.gr-assets.com/books/1361039443m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1361039443s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2657</td>\n",
       "      <td>2657</td>\n",
       "      <td>3275794</td>\n",
       "      <td>487</td>\n",
       "      <td>61120081</td>\n",
       "      <td>9.780061e+12</td>\n",
       "      <td>Harper Lee</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>To Kill a Mockingbird</td>\n",
       "      <td>...</td>\n",
       "      <td>3198671</td>\n",
       "      <td>3340896</td>\n",
       "      <td>72586</td>\n",
       "      <td>60427</td>\n",
       "      <td>117415</td>\n",
       "      <td>446835</td>\n",
       "      <td>1001952</td>\n",
       "      <td>1714267</td>\n",
       "      <td>https://images.gr-assets.com/books/1361975680m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1361975680s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4671</td>\n",
       "      <td>4671</td>\n",
       "      <td>245494</td>\n",
       "      <td>1356</td>\n",
       "      <td>743273567</td>\n",
       "      <td>9.780743e+12</td>\n",
       "      <td>F. Scott Fitzgerald</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>The Great Gatsby</td>\n",
       "      <td>...</td>\n",
       "      <td>2683664</td>\n",
       "      <td>2773745</td>\n",
       "      <td>51992</td>\n",
       "      <td>86236</td>\n",
       "      <td>197621</td>\n",
       "      <td>606158</td>\n",
       "      <td>936012</td>\n",
       "      <td>947718</td>\n",
       "      <td>https://images.gr-assets.com/books/1490528560m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1490528560s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>95</td>\n",
       "      <td>5297</td>\n",
       "      <td>5297</td>\n",
       "      <td>1858012</td>\n",
       "      <td>2303</td>\n",
       "      <td>375751513</td>\n",
       "      <td>9.780376e+12</td>\n",
       "      <td>Oscar Wilde, Jeffrey Eugenides</td>\n",
       "      <td>1891.0</td>\n",
       "      <td>The Picture of Dorian Gray</td>\n",
       "      <td>...</td>\n",
       "      <td>590014</td>\n",
       "      <td>674551</td>\n",
       "      <td>19647</td>\n",
       "      <td>9825</td>\n",
       "      <td>30735</td>\n",
       "      <td>129420</td>\n",
       "      <td>246892</td>\n",
       "      <td>257679</td>\n",
       "      <td>https://images.gr-assets.com/books/1424596966m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1424596966s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>13536860</td>\n",
       "      <td>13536860</td>\n",
       "      <td>18034963</td>\n",
       "      <td>133</td>\n",
       "      <td>345803507</td>\n",
       "      <td>9.780346e+12</td>\n",
       "      <td>E.L. James</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "      <td>...</td>\n",
       "      <td>387290</td>\n",
       "      <td>610116</td>\n",
       "      <td>25287</td>\n",
       "      <td>39079</td>\n",
       "      <td>55691</td>\n",
       "      <td>109963</td>\n",
       "      <td>140304</td>\n",
       "      <td>265079</td>\n",
       "      <td>https://images.gr-assets.com/books/1336418837m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1336418837s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>17245</td>\n",
       "      <td>17245</td>\n",
       "      <td>3165724</td>\n",
       "      <td>2207</td>\n",
       "      <td>393970124</td>\n",
       "      <td>9.780394e+12</td>\n",
       "      <td>Bram Stoker, Nina Auerbach, David J. Skal</td>\n",
       "      <td>1897.0</td>\n",
       "      <td>Dracula</td>\n",
       "      <td>...</td>\n",
       "      <td>618973</td>\n",
       "      <td>689739</td>\n",
       "      <td>17263</td>\n",
       "      <td>11756</td>\n",
       "      <td>36719</td>\n",
       "      <td>151396</td>\n",
       "      <td>246043</td>\n",
       "      <td>243825</td>\n",
       "      <td>https://images.gr-assets.com/books/1387151694m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1387151694s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>5060378</td>\n",
       "      <td>5060378</td>\n",
       "      <td>6976108</td>\n",
       "      <td>209</td>\n",
       "      <td>307269981</td>\n",
       "      <td>9.780307e+12</td>\n",
       "      <td>Stieg Larsson, Reg Keeland</td>\n",
       "      <td>2006.0</td>\n",
       "      <td>Flickan som lekte med elden</td>\n",
       "      <td>...</td>\n",
       "      <td>563994</td>\n",
       "      <td>649253</td>\n",
       "      <td>31423</td>\n",
       "      <td>4623</td>\n",
       "      <td>15781</td>\n",
       "      <td>91021</td>\n",
       "      <td>258236</td>\n",
       "      <td>279592</td>\n",
       "      <td>https://images.gr-assets.com/books/1351778881m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1351778881s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>11857408</td>\n",
       "      <td>11857408</td>\n",
       "      <td>16813814</td>\n",
       "      <td>147</td>\n",
       "      <td>1612130585</td>\n",
       "      <td>9.781612e+12</td>\n",
       "      <td>E.L. James</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>Fifty Shades Darker</td>\n",
       "      <td>...</td>\n",
       "      <td>552059</td>\n",
       "      <td>623340</td>\n",
       "      <td>28052</td>\n",
       "      <td>37245</td>\n",
       "      <td>58935</td>\n",
       "      <td>114203</td>\n",
       "      <td>150906</td>\n",
       "      <td>262051</td>\n",
       "      <td>https://images.gr-assets.com/books/1358266080m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1358266080s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    book_id  goodreads_book_id  best_book_id   work_id  books_count  \\\n",
       "0         1            2767052       2767052   2792775          272   \n",
       "1         2                  3             3   4640799          491   \n",
       "2         3              41865         41865   3212258          226   \n",
       "3         4               2657          2657   3275794          487   \n",
       "4         5               4671          4671    245494         1356   \n",
       "..      ...                ...           ...       ...          ...   \n",
       "94       95               5297          5297   1858012         2303   \n",
       "95       96           13536860      13536860  18034963          133   \n",
       "96       97              17245         17245   3165724         2207   \n",
       "97       98            5060378       5060378   6976108          209   \n",
       "98       99           11857408      11857408  16813814          147   \n",
       "\n",
       "          isbn        isbn13                                    authors  \\\n",
       "0    439023483  9.780439e+12                            Suzanne Collins   \n",
       "1    439554934  9.780440e+12               J.K. Rowling, Mary GrandPrÃ©   \n",
       "2    316015849  9.780316e+12                            Stephenie Meyer   \n",
       "3     61120081  9.780061e+12                                 Harper Lee   \n",
       "4    743273567  9.780743e+12                        F. Scott Fitzgerald   \n",
       "..         ...           ...                                        ...   \n",
       "94   375751513  9.780376e+12             Oscar Wilde, Jeffrey Eugenides   \n",
       "95   345803507  9.780346e+12                                 E.L. James   \n",
       "96   393970124  9.780394e+12  Bram Stoker, Nina Auerbach, David J. Skal   \n",
       "97   307269981  9.780307e+12                 Stieg Larsson, Reg Keeland   \n",
       "98  1612130585  9.781612e+12                                 E.L. James   \n",
       "\n",
       "    original_publication_year                            original_title  ...  \\\n",
       "0                      2008.0                          The Hunger Games  ...   \n",
       "1                      1997.0  Harry Potter and the Philosopher's Stone  ...   \n",
       "2                      2005.0                                  Twilight  ...   \n",
       "3                      1960.0                     To Kill a Mockingbird  ...   \n",
       "4                      1925.0                          The Great Gatsby  ...   \n",
       "..                        ...                                       ...  ...   \n",
       "94                     1891.0                The Picture of Dorian Gray  ...   \n",
       "95                     2012.0                        Fifty Shades Freed  ...   \n",
       "96                     1897.0                                   Dracula  ...   \n",
       "97                     2006.0               Flickan som lekte med elden  ...   \n",
       "98                     2011.0                       Fifty Shades Darker  ...   \n",
       "\n",
       "   ratings_count work_ratings_count  work_text_reviews_count  ratings_1  \\\n",
       "0        4780653            4942365                   155254      66715   \n",
       "1        4602479            4800065                    75867      75504   \n",
       "2        3866839            3916824                    95009     456191   \n",
       "3        3198671            3340896                    72586      60427   \n",
       "4        2683664            2773745                    51992      86236   \n",
       "..           ...                ...                      ...        ...   \n",
       "94        590014             674551                    19647       9825   \n",
       "95        387290             610116                    25287      39079   \n",
       "96        618973             689739                    17263      11756   \n",
       "97        563994             649253                    31423       4623   \n",
       "98        552059             623340                    28052      37245   \n",
       "\n",
       "    ratings_2  ratings_3  ratings_4  ratings_5  \\\n",
       "0      127936     560092    1481305    2706317   \n",
       "1      101676     455024    1156318    3011543   \n",
       "2      436802     793319     875073    1355439   \n",
       "3      117415     446835    1001952    1714267   \n",
       "4      197621     606158     936012     947718   \n",
       "..        ...        ...        ...        ...   \n",
       "94      30735     129420     246892     257679   \n",
       "95      55691     109963     140304     265079   \n",
       "96      36719     151396     246043     243825   \n",
       "97      15781      91021     258236     279592   \n",
       "98      58935     114203     150906     262051   \n",
       "\n",
       "                                            image_url  \\\n",
       "0   https://images.gr-assets.com/books/1447303603m...   \n",
       "1   https://images.gr-assets.com/books/1474154022m...   \n",
       "2   https://images.gr-assets.com/books/1361039443m...   \n",
       "3   https://images.gr-assets.com/books/1361975680m...   \n",
       "4   https://images.gr-assets.com/books/1490528560m...   \n",
       "..                                                ...   \n",
       "94  https://images.gr-assets.com/books/1424596966m...   \n",
       "95  https://images.gr-assets.com/books/1336418837m...   \n",
       "96  https://images.gr-assets.com/books/1387151694m...   \n",
       "97  https://images.gr-assets.com/books/1351778881m...   \n",
       "98  https://images.gr-assets.com/books/1358266080m...   \n",
       "\n",
       "                                      small_image_url  \n",
       "0   https://images.gr-assets.com/books/1447303603s...  \n",
       "1   https://images.gr-assets.com/books/1474154022s...  \n",
       "2   https://images.gr-assets.com/books/1361039443s...  \n",
       "3   https://images.gr-assets.com/books/1361975680s...  \n",
       "4   https://images.gr-assets.com/books/1490528560s...  \n",
       "..                                                ...  \n",
       "94  https://images.gr-assets.com/books/1424596966s...  \n",
       "95  https://images.gr-assets.com/books/1336418837s...  \n",
       "96  https://images.gr-assets.com/books/1387151694s...  \n",
       "97  https://images.gr-assets.com/books/1351778881s...  \n",
       "98  https://images.gr-assets.com/books/1358266080s...  \n",
       "\n",
       "[99 rows x 23 columns]"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodbooks "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = pd.read_csv(\"C:/Users/HP/F_COMPUTER_SCIENCE/progetto/users.csv\", encoding = 'latin-1', delimiter = \";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "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",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278853</th>\n",
       "      <td>278854</td>\n",
       "      <td>portland, oregon, usa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278854</th>\n",
       "      <td>278855</td>\n",
       "      <td>tacoma, washington, united kingdom</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278855</th>\n",
       "      <td>278856</td>\n",
       "      <td>brampton, ontario, canada</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278856</th>\n",
       "      <td>278857</td>\n",
       "      <td>knoxville, tennessee, usa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278857</th>\n",
       "      <td>278858</td>\n",
       "      <td>dublin, n/a, ireland</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>278858 rows × 3 columns</p>\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\n",
       "...         ...                                 ...   ...\n",
       "278853   278854               portland, oregon, usa   NaN\n",
       "278854   278855  tacoma, washington, united kingdom  50.0\n",
       "278855   278856           brampton, ontario, canada   NaN\n",
       "278856   278857           knoxville, tennessee, usa   NaN\n",
       "278857   278858                dublin, n/a, ireland   NaN\n",
       "\n",
       "[278858 rows x 3 columns]"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Normalize the location field of Users dataset, splitting into city, region, country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,3):\n",
    "    users[i]= users['Location'].str.split(',',expand=True)[i]\n",
    "    \n",
    "users = users.rename(columns={0: 'city', 1: 'region', 2: 'country' })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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>city</th>\n",
       "      <th>region</th>\n",
       "      <th>country</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",
       "      <td>nyc</td>\n",
       "      <td>new york</td>\n",
       "      <td>usa</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",
       "      <td>stockton</td>\n",
       "      <td>california</td>\n",
       "      <td>usa</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",
       "      <td>moscow</td>\n",
       "      <td>yukon territory</td>\n",
       "      <td>russia</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",
       "      <td>porto</td>\n",
       "      <td>v.n.gaia</td>\n",
       "      <td>portugal</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",
       "      <td>farnborough</td>\n",
       "      <td>hants</td>\n",
       "      <td>united kingdom</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278853</th>\n",
       "      <td>278854</td>\n",
       "      <td>portland, oregon, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>portland</td>\n",
       "      <td>oregon</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278854</th>\n",
       "      <td>278855</td>\n",
       "      <td>tacoma, washington, united kingdom</td>\n",
       "      <td>50.0</td>\n",
       "      <td>tacoma</td>\n",
       "      <td>washington</td>\n",
       "      <td>united kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278855</th>\n",
       "      <td>278856</td>\n",
       "      <td>brampton, ontario, canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>brampton</td>\n",
       "      <td>ontario</td>\n",
       "      <td>canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278856</th>\n",
       "      <td>278857</td>\n",
       "      <td>knoxville, tennessee, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>knoxville</td>\n",
       "      <td>tennessee</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278857</th>\n",
       "      <td>278858</td>\n",
       "      <td>dublin, n/a, ireland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>dublin</td>\n",
       "      <td>n/a</td>\n",
       "      <td>ireland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>278858 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        User-ID                            Location   Age         city  \\\n",
       "0             1                  nyc, new york, usa   NaN          nyc   \n",
       "1             2           stockton, california, usa  18.0     stockton   \n",
       "2             3     moscow, yukon territory, russia   NaN       moscow   \n",
       "3             4           porto, v.n.gaia, portugal  17.0        porto   \n",
       "4             5  farnborough, hants, united kingdom   NaN  farnborough   \n",
       "...         ...                                 ...   ...          ...   \n",
       "278853   278854               portland, oregon, usa   NaN     portland   \n",
       "278854   278855  tacoma, washington, united kingdom  50.0       tacoma   \n",
       "278855   278856           brampton, ontario, canada   NaN     brampton   \n",
       "278856   278857           knoxville, tennessee, usa   NaN    knoxville   \n",
       "278857   278858                dublin, n/a, ireland   NaN       dublin   \n",
       "\n",
       "                  region          country  \n",
       "0               new york              usa  \n",
       "1             california              usa  \n",
       "2        yukon territory           russia  \n",
       "3               v.n.gaia         portugal  \n",
       "4                  hants   united kingdom  \n",
       "...                  ...              ...  \n",
       "278853            oregon              usa  \n",
       "278854        washington   united kingdom  \n",
       "278855           ontario           canada  \n",
       "278856         tennessee              usa  \n",
       "278857               n/a          ireland  \n",
       "\n",
       "[278858 rows x 6 columns]"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### un'altra possibile soluzione, un poco più macchinosa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "##users['city']=users['Location'].str.split(',',expand=True)[0]\n",
    "##users['region']=users['Location'].str.split(',',expand=True)[1]\n",
    "##users['country']=users['Location'].str.split(',',expand=True)[2]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. For each book in the Books dataset, compute its average rating."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "books_ratings = pd.merge(books, book_ratings, on = 'ISBN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_rating_of_book = books_ratings.groupby(['ISBN', 'Book-Title', 'Book-Author'], as_index=False)['Book-Rating'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "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>Book-Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000913154</td>\n",
       "      <td>The Way Things Work: An Illustrated Encycloped...</td>\n",
       "      <td>C. van Amerongen (translator)</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0001010565</td>\n",
       "      <td>Mog's Christmas</td>\n",
       "      <td>Judith Kerr</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0001046438</td>\n",
       "      <td>Liar</td>\n",
       "      <td>Stephen Fry</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0001046713</td>\n",
       "      <td>Twopence to Cross the Mersey</td>\n",
       "      <td>Helen Forrester</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000104687X</td>\n",
       "      <td>T.S. Eliot Reading \\The Wasteland\\\" and Other ...</td>\n",
       "      <td>T.S. Eliot</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0001046934</td>\n",
       "      <td>The Prime of Miss Jean Brodie</td>\n",
       "      <td>Muriel Spark</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0001047213</td>\n",
       "      <td>The Fighting Man</td>\n",
       "      <td>Gerald Seymour</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0001047647</td>\n",
       "      <td>First Among Equals</td>\n",
       "      <td>Jeffrey Archer</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0001047663</td>\n",
       "      <td>Matter Of Honour</td>\n",
       "      <td>Jeffrey Archer</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0001047868</td>\n",
       "      <td>Kidnapped</td>\n",
       "      <td>Robert Louis Stevenson</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0001047973</td>\n",
       "      <td>Brave New World</td>\n",
       "      <td>Aldous Huxley</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>000104799X</td>\n",
       "      <td>Monk's-hood</td>\n",
       "      <td>Ellis Peters</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0001048082</td>\n",
       "      <td>Made in America</td>\n",
       "      <td>Bill Bryson</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0001048473</td>\n",
       "      <td>Nothing Can Be Better</td>\n",
       "      <td>Barns &amp;amp; Budd</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0001049879</td>\n",
       "      <td>Daughters of the Storm</td>\n",
       "      <td>Elizabeth Buchan</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ISBN                                         Book-Title  \\\n",
       "0   0000913154  The Way Things Work: An Illustrated Encycloped...   \n",
       "1   0001010565                                    Mog's Christmas   \n",
       "2   0001046438                                               Liar   \n",
       "3   0001046713                       Twopence to Cross the Mersey   \n",
       "4   000104687X  T.S. Eliot Reading \\The Wasteland\\\" and Other ...   \n",
       "5   0001046934                      The Prime of Miss Jean Brodie   \n",
       "6   0001047213                                   The Fighting Man   \n",
       "7   0001047647                                 First Among Equals   \n",
       "8   0001047663                                   Matter Of Honour   \n",
       "9   0001047868                                          Kidnapped   \n",
       "10  0001047973                                    Brave New World   \n",
       "11  000104799X                                        Monk's-hood   \n",
       "12  0001048082                                    Made in America   \n",
       "13  0001048473                              Nothing Can Be Better   \n",
       "14  0001049879                             Daughters of the Storm   \n",
       "\n",
       "                      Book-Author  Book-Rating  \n",
       "0   C. van Amerongen (translator)          8.0  \n",
       "1                     Judith Kerr          0.0  \n",
       "2                     Stephen Fry          9.0  \n",
       "3                 Helen Forrester          0.0  \n",
       "4                      T.S. Eliot          6.0  \n",
       "5                    Muriel Spark          0.0  \n",
       "6                  Gerald Seymour          9.0  \n",
       "7                  Jeffrey Archer          0.0  \n",
       "8                  Jeffrey Archer          0.0  \n",
       "9          Robert Louis Stevenson          0.0  \n",
       "10                  Aldous Huxley          9.0  \n",
       "11                   Ellis Peters          7.5  \n",
       "12                    Bill Bryson          8.0  \n",
       "13               Barns &amp; Budd          0.0  \n",
       "14               Elizabeth Buchan          0.0  "
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg_rating_of_book.head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. For each book in the GoodBooks dataset, compute its average rating."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book_id</th>\n",
       "      <th>isbn</th>\n",
       "      <th>authors</th>\n",
       "      <th>original_title</th>\n",
       "      <th>title</th>\n",
       "      <th>average_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>439023483</td>\n",
       "      <td>Suzanne Collins</td>\n",
       "      <td>The Hunger Games</td>\n",
       "      <td>The Hunger Games (The Hunger Games, #1)</td>\n",
       "      <td>4.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>439554934</td>\n",
       "      <td>J.K. Rowling, Mary GrandPrÃ©</td>\n",
       "      <td>Harry Potter and the Philosopher's Stone</td>\n",
       "      <td>Harry Potter and the Sorcerer's Stone (Harry P...</td>\n",
       "      <td>4.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>316015849</td>\n",
       "      <td>Stephenie Meyer</td>\n",
       "      <td>Twilight</td>\n",
       "      <td>Twilight (Twilight, #1)</td>\n",
       "      <td>3.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>61120081</td>\n",
       "      <td>Harper Lee</td>\n",
       "      <td>To Kill a Mockingbird</td>\n",
       "      <td>To Kill a Mockingbird</td>\n",
       "      <td>4.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>743273567</td>\n",
       "      <td>F. Scott Fitzgerald</td>\n",
       "      <td>The Great Gatsby</td>\n",
       "      <td>The Great Gatsby</td>\n",
       "      <td>3.89</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>95</td>\n",
       "      <td>375751513</td>\n",
       "      <td>Oscar Wilde, Jeffrey Eugenides</td>\n",
       "      <td>The Picture of Dorian Gray</td>\n",
       "      <td>The Picture of Dorian Gray</td>\n",
       "      <td>4.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>345803507</td>\n",
       "      <td>E.L. James</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "      <td>Fifty Shades Freed (Fifty Shades, #3)</td>\n",
       "      <td>3.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>393970124</td>\n",
       "      <td>Bram Stoker, Nina Auerbach, David J. Skal</td>\n",
       "      <td>Dracula</td>\n",
       "      <td>Dracula</td>\n",
       "      <td>3.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>307269981</td>\n",
       "      <td>Stieg Larsson, Reg Keeland</td>\n",
       "      <td>Flickan som lekte med elden</td>\n",
       "      <td>The Girl Who Played with Fire (Millennium, #2)</td>\n",
       "      <td>4.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>1612130585</td>\n",
       "      <td>E.L. James</td>\n",
       "      <td>Fifty Shades Darker</td>\n",
       "      <td>Fifty Shades Darker (Fifty Shades, #2)</td>\n",
       "      <td>3.87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>99 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    book_id        isbn                                    authors  \\\n",
       "0         1   439023483                            Suzanne Collins   \n",
       "1         2   439554934               J.K. Rowling, Mary GrandPrÃ©   \n",
       "2         3   316015849                            Stephenie Meyer   \n",
       "3         4    61120081                                 Harper Lee   \n",
       "4         5   743273567                        F. Scott Fitzgerald   \n",
       "..      ...         ...                                        ...   \n",
       "94       95   375751513             Oscar Wilde, Jeffrey Eugenides   \n",
       "95       96   345803507                                 E.L. James   \n",
       "96       97   393970124  Bram Stoker, Nina Auerbach, David J. Skal   \n",
       "97       98   307269981                 Stieg Larsson, Reg Keeland   \n",
       "98       99  1612130585                                 E.L. James   \n",
       "\n",
       "                              original_title  \\\n",
       "0                           The Hunger Games   \n",
       "1   Harry Potter and the Philosopher's Stone   \n",
       "2                                   Twilight   \n",
       "3                      To Kill a Mockingbird   \n",
       "4                           The Great Gatsby   \n",
       "..                                       ...   \n",
       "94                The Picture of Dorian Gray   \n",
       "95                        Fifty Shades Freed   \n",
       "96                                   Dracula   \n",
       "97               Flickan som lekte med elden   \n",
       "98                       Fifty Shades Darker   \n",
       "\n",
       "                                                title  average_rating  \n",
       "0             The Hunger Games (The Hunger Games, #1)            4.34  \n",
       "1   Harry Potter and the Sorcerer's Stone (Harry P...            4.44  \n",
       "2                             Twilight (Twilight, #1)            3.57  \n",
       "3                               To Kill a Mockingbird            4.25  \n",
       "4                                    The Great Gatsby            3.89  \n",
       "..                                                ...             ...  \n",
       "94                         The Picture of Dorian Gray            4.06  \n",
       "95              Fifty Shades Freed (Fifty Shades, #3)            3.88  \n",
       "96                                            Dracula            3.98  \n",
       "97     The Girl Who Played with Fire (Millennium, #2)            4.22  \n",
       "98             Fifty Shades Darker (Fifty Shades, #2)            3.87  \n",
       "\n",
       "[99 rows x 6 columns]"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodbooks[['book_id', 'isbn', 'authors', 'original_title', 'title', 'average_rating']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Merge together all rows sharing the same book title, author and publisher. We will call the resulting datset merged books. The books that have not been merged together will not appear in merged books."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_books = books.groupby(['Book-Title', 'Book-Author', 'Publisher'], as_index=False)['ISBN'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_books = merged_books[merged_books['ISBN']!=1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_books= merged_books[['Book-Title', 'Book-Author', 'Publisher']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>Publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>!%@ (A Nutshell handbook)</td>\n",
       "      <td>Donnalyn Frey</td>\n",
       "      <td>O'Reilly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>'A Hell of a Place to Lose a Cow': An American...</td>\n",
       "      <td>Tim Brookes</td>\n",
       "      <td>National Geographic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>10,000 dreams interpreted: A dictionary of dreams</td>\n",
       "      <td>Gustavus Hindman Miller</td>\n",
       "      <td>Barnes &amp;amp; Nobles Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>101 Famous Poems</td>\n",
       "      <td>Roy J. Cook</td>\n",
       "      <td>McGraw-Hill/Contemporary Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>713</th>\n",
       "      <td>15 Houseplants Even You Can't Kill</td>\n",
       "      <td>Joe Elder</td>\n",
       "      <td>Berkley Pub Group</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265525</th>\n",
       "      <td>Zia</td>\n",
       "      <td>Scott O'Dell</td>\n",
       "      <td>Laurel-Leaf Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265526</th>\n",
       "      <td>Zia</td>\n",
       "      <td>Scott O'Dell</td>\n",
       "      <td>Yearling Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265562</th>\n",
       "      <td>Zimmermann Telegram</td>\n",
       "      <td>Barbara Tuchman</td>\n",
       "      <td>Ballantine Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265625</th>\n",
       "      <td>Zoids Chaotic Century (Zoids: Chaotic Century ...</td>\n",
       "      <td>Michiro Ueyama</td>\n",
       "      <td>Viz Comics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266117</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4725 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Book-Title  \\\n",
       "41                              !%@ (A Nutshell handbook)   \n",
       "59      'A Hell of a Place to Lose a Cow': An American...   \n",
       "255     10,000 dreams interpreted: A dictionary of dreams   \n",
       "465                                      101 Famous Poems   \n",
       "713                    15 Houseplants Even You Can't Kill   \n",
       "...                                                   ...   \n",
       "265525                                                Zia   \n",
       "265526                                                Zia   \n",
       "265562                                Zimmermann Telegram   \n",
       "265625  Zoids Chaotic Century (Zoids: Chaotic Century ...   \n",
       "266117         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "\n",
       "                    Book-Author                       Publisher  \n",
       "41                Donnalyn Frey                        O'Reilly  \n",
       "59                  Tim Brookes             National Geographic  \n",
       "255     Gustavus Hindman Miller       Barnes &amp; Nobles Books  \n",
       "465                 Roy J. Cook  McGraw-Hill/Contemporary Books  \n",
       "713                   Joe Elder               Berkley Pub Group  \n",
       "...                         ...                             ...  \n",
       "265525             Scott O'Dell               Laurel-Leaf Books  \n",
       "265526             Scott O'Dell                  Yearling Books  \n",
       "265562          Barbara Tuchman                Ballantine Books  \n",
       "265625           Michiro Ueyama                      Viz Comics  \n",
       "266117    RÃ?ÃÂ¼diger Nehberg                           Piper  \n",
       "\n",
       "[4725 rows x 3 columns]"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_books"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. For each book in merged books compute its average rating."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>ISBN</th>\n",
       "      <th>Year-Of-Publication</th>\n",
       "      <th>Image-URL-S</th>\n",
       "      <th>Image-URL-M</th>\n",
       "      <th>Image-URL-L</th>\n",
       "      <th>User-ID</th>\n",
       "      <th>Book-Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>!%@ (A Nutshell handbook)</td>\n",
       "      <td>Donnalyn Frey</td>\n",
       "      <td>O'Reilly</td>\n",
       "      <td>1565920465</td>\n",
       "      <td>1994</td>\n",
       "      <td>http://images.amazon.com/images/P/1565920465.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1565920465.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1565920465.0...</td>\n",
       "      <td>271245</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>!%@ (A Nutshell handbook)</td>\n",
       "      <td>Donnalyn Frey</td>\n",
       "      <td>O'Reilly</td>\n",
       "      <td>1565920317</td>\n",
       "      <td>1993</td>\n",
       "      <td>http://images.amazon.com/images/P/1565920317.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1565920317.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1565920317.0...</td>\n",
       "      <td>87141</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>'A Hell of a Place to Lose a Cow': An American...</td>\n",
       "      <td>Tim Brookes</td>\n",
       "      <td>National Geographic</td>\n",
       "      <td>0792276833</td>\n",
       "      <td>2000</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>79186</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>'A Hell of a Place to Lose a Cow': An American...</td>\n",
       "      <td>Tim Brookes</td>\n",
       "      <td>National Geographic</td>\n",
       "      <td>0792276833</td>\n",
       "      <td>2000</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>114216</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>'A Hell of a Place to Lose a Cow': An American...</td>\n",
       "      <td>Tim Brookes</td>\n",
       "      <td>National Geographic</td>\n",
       "      <td>0792276833</td>\n",
       "      <td>2000</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/0792276833.0...</td>\n",
       "      <td>201058</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92851</th>\n",
       "      <td>Zoids Chaotic Century (Zoids: Chaotic Century ...</td>\n",
       "      <td>Michiro Ueyama</td>\n",
       "      <td>Viz Comics</td>\n",
       "      <td>1569317658</td>\n",
       "      <td>2002</td>\n",
       "      <td>http://images.amazon.com/images/P/1569317658.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1569317658.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/1569317658.0...</td>\n",
       "      <td>63714</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92852</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "      <td>3492218091</td>\n",
       "      <td>1994</td>\n",
       "      <td>http://images.amazon.com/images/P/3492218091.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492218091.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492218091.0...</td>\n",
       "      <td>167847</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92853</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "      <td>3492233376</td>\n",
       "      <td>2001</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>174480</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92854</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "      <td>3492233376</td>\n",
       "      <td>2001</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>229149</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92855</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "      <td>3492233376</td>\n",
       "      <td>2001</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>http://images.amazon.com/images/P/3492233376.0...</td>\n",
       "      <td>231891</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92856 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Book-Title  \\\n",
       "0                              !%@ (A Nutshell handbook)   \n",
       "1                              !%@ (A Nutshell handbook)   \n",
       "2      'A Hell of a Place to Lose a Cow': An American...   \n",
       "3      'A Hell of a Place to Lose a Cow': An American...   \n",
       "4      'A Hell of a Place to Lose a Cow': An American...   \n",
       "...                                                  ...   \n",
       "92851  Zoids Chaotic Century (Zoids: Chaotic Century ...   \n",
       "92852         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "92853         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "92854         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "92855         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "\n",
       "                 Book-Author            Publisher        ISBN  \\\n",
       "0              Donnalyn Frey             O'Reilly  1565920465   \n",
       "1              Donnalyn Frey             O'Reilly  1565920317   \n",
       "2                Tim Brookes  National Geographic  0792276833   \n",
       "3                Tim Brookes  National Geographic  0792276833   \n",
       "4                Tim Brookes  National Geographic  0792276833   \n",
       "...                      ...                  ...         ...   \n",
       "92851         Michiro Ueyama           Viz Comics  1569317658   \n",
       "92852  RÃ?ÃÂ¼diger Nehberg                Piper  3492218091   \n",
       "92853  RÃ?ÃÂ¼diger Nehberg                Piper  3492233376   \n",
       "92854  RÃ?ÃÂ¼diger Nehberg                Piper  3492233376   \n",
       "92855  RÃ?ÃÂ¼diger Nehberg                Piper  3492233376   \n",
       "\n",
       "      Year-Of-Publication                                        Image-URL-S  \\\n",
       "0                    1994  http://images.amazon.com/images/P/1565920465.0...   \n",
       "1                    1993  http://images.amazon.com/images/P/1565920317.0...   \n",
       "2                    2000  http://images.amazon.com/images/P/0792276833.0...   \n",
       "3                    2000  http://images.amazon.com/images/P/0792276833.0...   \n",
       "4                    2000  http://images.amazon.com/images/P/0792276833.0...   \n",
       "...                   ...                                                ...   \n",
       "92851                2002  http://images.amazon.com/images/P/1569317658.0...   \n",
       "92852                1994  http://images.amazon.com/images/P/3492218091.0...   \n",
       "92853                2001  http://images.amazon.com/images/P/3492233376.0...   \n",
       "92854                2001  http://images.amazon.com/images/P/3492233376.0...   \n",
       "92855                2001  http://images.amazon.com/images/P/3492233376.0...   \n",
       "\n",
       "                                             Image-URL-M  \\\n",
       "0      http://images.amazon.com/images/P/1565920465.0...   \n",
       "1      http://images.amazon.com/images/P/1565920317.0...   \n",
       "2      http://images.amazon.com/images/P/0792276833.0...   \n",
       "3      http://images.amazon.com/images/P/0792276833.0...   \n",
       "4      http://images.amazon.com/images/P/0792276833.0...   \n",
       "...                                                  ...   \n",
       "92851  http://images.amazon.com/images/P/1569317658.0...   \n",
       "92852  http://images.amazon.com/images/P/3492218091.0...   \n",
       "92853  http://images.amazon.com/images/P/3492233376.0...   \n",
       "92854  http://images.amazon.com/images/P/3492233376.0...   \n",
       "92855  http://images.amazon.com/images/P/3492233376.0...   \n",
       "\n",
       "                                             Image-URL-L  User-ID  Book-Rating  \n",
       "0      http://images.amazon.com/images/P/1565920465.0...   271245            0  \n",
       "1      http://images.amazon.com/images/P/1565920317.0...    87141            6  \n",
       "2      http://images.amazon.com/images/P/0792276833.0...    79186            0  \n",
       "3      http://images.amazon.com/images/P/0792276833.0...   114216            0  \n",
       "4      http://images.amazon.com/images/P/0792276833.0...   201058            0  \n",
       "...                                                  ...      ...          ...  \n",
       "92851  http://images.amazon.com/images/P/1569317658.0...    63714           10  \n",
       "92852  http://images.amazon.com/images/P/3492218091.0...   167847            5  \n",
       "92853  http://images.amazon.com/images/P/3492233376.0...   174480            0  \n",
       "92854  http://images.amazon.com/images/P/3492233376.0...   229149            8  \n",
       "92855  http://images.amazon.com/images/P/3492233376.0...   231891            0  \n",
       "\n",
       "[92856 rows x 10 columns]"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_books_ratings  = pd.merge(merged_books, books_ratings, on = ('Book-Title', 'Book-Author', 'Publisher'), how='left')\n",
    "merged_books_ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_books_ratings = merged_books_ratings.groupby(['Book-Title', 'Book-Author', 'Publisher', 'ISBN'], \n",
    "                                                    as_index=False)['Book-Rating'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_books_ratings.rename(columns={'Book-Rating': 'Book-Rating-Avg'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>ISBN</th>\n",
       "      <th>Book-Rating-Avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>!%@ (A Nutshell handbook)</td>\n",
       "      <td>Donnalyn Frey</td>\n",
       "      <td>O'Reilly</td>\n",
       "      <td>1565920317</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>!%@ (A Nutshell handbook)</td>\n",
       "      <td>Donnalyn Frey</td>\n",
       "      <td>O'Reilly</td>\n",
       "      <td>1565920465</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>'A Hell of a Place to Lose a Cow': An American...</td>\n",
       "      <td>Tim Brookes</td>\n",
       "      <td>National Geographic</td>\n",
       "      <td>0792276833</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>'A Hell of a Place to Lose a Cow': An American...</td>\n",
       "      <td>Tim Brookes</td>\n",
       "      <td>National Geographic</td>\n",
       "      <td>0792277295</td>\n",
       "      <td>3.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10,000 dreams interpreted: A dictionary of dreams</td>\n",
       "      <td>Gustavus Hindman Miller</td>\n",
       "      <td>Barnes &amp;amp; Nobles Books</td>\n",
       "      <td>1566196256</td>\n",
       "      <td>7.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9906</th>\n",
       "      <td>Zoids Chaotic Century (Zoids: Chaotic Century ...</td>\n",
       "      <td>Michiro Ueyama</td>\n",
       "      <td>Viz Comics</td>\n",
       "      <td>1569317666</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9907</th>\n",
       "      <td>Zoids Chaotic Century (Zoids: Chaotic Century ...</td>\n",
       "      <td>Michiro Ueyama</td>\n",
       "      <td>Viz Comics</td>\n",
       "      <td>1569317674</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9908</th>\n",
       "      <td>Zoids Chaotic Century (Zoids: Chaotic Century ...</td>\n",
       "      <td>Michiro Ueyama</td>\n",
       "      <td>Viz Comics</td>\n",
       "      <td>1569317682</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9909</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "      <td>3492218091</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9910</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "      <td>3492233376</td>\n",
       "      <td>2.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9911 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Book-Title  \\\n",
       "0                             !%@ (A Nutshell handbook)   \n",
       "1                             !%@ (A Nutshell handbook)   \n",
       "2     'A Hell of a Place to Lose a Cow': An American...   \n",
       "3     'A Hell of a Place to Lose a Cow': An American...   \n",
       "4     10,000 dreams interpreted: A dictionary of dreams   \n",
       "...                                                 ...   \n",
       "9906  Zoids Chaotic Century (Zoids: Chaotic Century ...   \n",
       "9907  Zoids Chaotic Century (Zoids: Chaotic Century ...   \n",
       "9908  Zoids Chaotic Century (Zoids: Chaotic Century ...   \n",
       "9909         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "9910         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "\n",
       "                  Book-Author                  Publisher        ISBN  \\\n",
       "0               Donnalyn Frey                   O'Reilly  1565920317   \n",
       "1               Donnalyn Frey                   O'Reilly  1565920465   \n",
       "2                 Tim Brookes        National Geographic  0792276833   \n",
       "3                 Tim Brookes        National Geographic  0792277295   \n",
       "4     Gustavus Hindman Miller  Barnes &amp; Nobles Books  1566196256   \n",
       "...                       ...                        ...         ...   \n",
       "9906           Michiro Ueyama                 Viz Comics  1569317666   \n",
       "9907           Michiro Ueyama                 Viz Comics  1569317674   \n",
       "9908           Michiro Ueyama                 Viz Comics  1569317682   \n",
       "9909    RÃ?ÃÂ¼diger Nehberg                      Piper  3492218091   \n",
       "9910    RÃ?ÃÂ¼diger Nehberg                      Piper  3492233376   \n",
       "\n",
       "      Book-Rating-Avg  \n",
       "0            6.000000  \n",
       "1            0.000000  \n",
       "2            0.000000  \n",
       "3            3.400000  \n",
       "4            7.250000  \n",
       "...               ...  \n",
       "9906        10.000000  \n",
       "9907        10.000000  \n",
       "9908        10.000000  \n",
       "9909         5.000000  \n",
       "9910         2.666667  \n",
       "\n",
       "[9911 rows x 5 columns]"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_books_ratings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. For each book in merged books compute the minimum and maximum of the average ratings over all corresponding books in the books dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>Publisher</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Book-Rating-Avg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>!%@ (A Nutshell handbook)</td>\n",
       "      <td>Donnalyn Frey</td>\n",
       "      <td>O'Reilly</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>'A Hell of a Place to Lose a Cow': An American...</td>\n",
       "      <td>Tim Brookes</td>\n",
       "      <td>National Geographic</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10,000 dreams interpreted: A dictionary of dreams</td>\n",
       "      <td>Gustavus Hindman Miller</td>\n",
       "      <td>Barnes &amp;amp; Nobles Books</td>\n",
       "      <td>6.666667</td>\n",
       "      <td>7.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101 Famous Poems</td>\n",
       "      <td>Roy J. Cook</td>\n",
       "      <td>McGraw-Hill/Contemporary Books</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15 Houseplants Even You Can't Kill</td>\n",
       "      <td>Joe Elder</td>\n",
       "      <td>Berkley Pub Group</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4720</th>\n",
       "      <td>Zia</td>\n",
       "      <td>Scott O'Dell</td>\n",
       "      <td>Laurel-Leaf Books</td>\n",
       "      <td>1.666667</td>\n",
       "      <td>3.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4721</th>\n",
       "      <td>Zia</td>\n",
       "      <td>Scott O'Dell</td>\n",
       "      <td>Yearling Books</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4722</th>\n",
       "      <td>Zimmermann Telegram</td>\n",
       "      <td>Barbara Tuchman</td>\n",
       "      <td>Ballantine Books</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4723</th>\n",
       "      <td>Zoids Chaotic Century (Zoids: Chaotic Century ...</td>\n",
       "      <td>Michiro Ueyama</td>\n",
       "      <td>Viz Comics</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4724</th>\n",
       "      <td>Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.</td>\n",
       "      <td>RÃ?ÃÂ¼diger Nehberg</td>\n",
       "      <td>Piper</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4725 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Book-Title  \\\n",
       "                                                          \n",
       "0                             !%@ (A Nutshell handbook)   \n",
       "1     'A Hell of a Place to Lose a Cow': An American...   \n",
       "2     10,000 dreams interpreted: A dictionary of dreams   \n",
       "3                                      101 Famous Poems   \n",
       "4                    15 Houseplants Even You Can't Kill   \n",
       "...                                                 ...   \n",
       "4720                                                Zia   \n",
       "4721                                                Zia   \n",
       "4722                                Zimmermann Telegram   \n",
       "4723  Zoids Chaotic Century (Zoids: Chaotic Century ...   \n",
       "4724         Ã?Ã?berleben in der WÃ?ÃÂ¼ste Danakil.   \n",
       "\n",
       "                  Book-Author                       Publisher Book-Rating-Avg  \\\n",
       "                                                                          min   \n",
       "0               Donnalyn Frey                        O'Reilly        0.000000   \n",
       "1                 Tim Brookes             National Geographic        0.000000   \n",
       "2     Gustavus Hindman Miller       Barnes &amp; Nobles Books        6.666667   \n",
       "3                 Roy J. Cook  McGraw-Hill/Contemporary Books        0.000000   \n",
       "4                   Joe Elder               Berkley Pub Group        0.000000   \n",
       "...                       ...                             ...             ...   \n",
       "4720             Scott O'Dell               Laurel-Leaf Books        1.666667   \n",
       "4721             Scott O'Dell                  Yearling Books        0.000000   \n",
       "4722          Barbara Tuchman                Ballantine Books        0.000000   \n",
       "4723           Michiro Ueyama                      Viz Comics       10.000000   \n",
       "4724    RÃ?ÃÂ¼diger Nehberg                           Piper        2.666667   \n",
       "\n",
       "                 \n",
       "            max  \n",
       "0      6.000000  \n",
       "1      3.400000  \n",
       "2      7.250000  \n",
       "3      5.000000  \n",
       "4      0.000000  \n",
       "...         ...  \n",
       "4720   3.428571  \n",
       "4721   2.000000  \n",
       "4722   4.000000  \n",
       "4723  10.000000  \n",
       "4724   5.000000  \n",
       "\n",
       "[4725 rows x 5 columns]"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_books_ratings[['Book-Title', 'Book-Author', 'Publisher', 'Book-Rating-Avg']]\\\n",
    ".groupby(['Book-Title', 'Book-Author', 'Publisher'], as_index=False).agg({ 'Book-Rating-Avg': ['min', 'max']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. For each book in goodbooks, compute the list of its authors. Assuming that the number of reviews with a text (column work_text_reviews_count) is split equally among all authors, find for each authors the total number of reviews with a text. We will call this quantity the shared number of reviews with a text."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### prima soluzione pensata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "##def list_authors(row):\n",
    "    ##return row['authors'].split(',')\n",
    "\n",
    "##goodbooks['authors']=goodbooks.apply(list_authors, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "##def shared_reviews(row):\n",
    "    ##return row['work_text_reviews_count']/len(row['authors'])\n",
    "\n",
    "#goodbooks['shared_number_of_reviews_with_a_text']=goodbooks.apply(shared_reviews, axis=1)\n",
    "#goodbooks['shared_number_of_reviews_with_a_text']                                                                                           \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "##authors_reviews=goodbooks[['authors','shared_number_of_reviews_with_a_text']]\n",
    "##authors_reviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "##authors_reviews = authors_reviews.explode('authors')\n",
    "\n",
    "##authors_reviews.groupby('authors', as_index=False)['shared_number_of_reviews_with_a_text'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### seconda soluzione proposta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in goodbooks.iterrows():\n",
    "    goodbooks.at[i,'authors'] = row[\"authors\"].split(',')\n",
    "    num_of_authors = len([\"authors\"])\n",
    "    goodbooks.at[i,'work_text_reviews_count'] = row['work_text_reviews_count'] / num_of_authors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodbooks_exploded = goodbooks.explode('authors')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "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>authors</th>\n",
       "      <th>work_text_reviews_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alan R. Clarke</td>\n",
       "      <td>55781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B.M. Mooyaart-Doubleday</td>\n",
       "      <td>20825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bernard Knox</td>\n",
       "      <td>8101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CelÃ¢l Ãster</td>\n",
       "      <td>45518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>David J. Skal</td>\n",
       "      <td>17263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Veronica Roth</td>\n",
       "      <td>156896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>William Golding</td>\n",
       "      <td>26886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>William Goldman</td>\n",
       "      <td>15630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>William Shakespeare</td>\n",
       "      <td>14778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Yann Martel</td>\n",
       "      <td>42962</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      authors  work_text_reviews_count\n",
       "0              Alan R. Clarke                    55781\n",
       "1     B.M. Mooyaart-Doubleday                    20825\n",
       "2                Bernard Knox                     8101\n",
       "3               CelÃ¢l Ãster                    45518\n",
       "4               David J. Skal                    17263\n",
       "..                        ...                      ...\n",
       "105             Veronica Roth                   156896\n",
       "106           William Golding                    26886\n",
       "107           William Goldman                    15630\n",
       "108       William Shakespeare                    14778\n",
       "109               Yann Martel                    42962\n",
       "\n",
       "[110 rows x 2 columns]"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors_review= goodbooks_exploded.groupby('authors', as_index=False)['work_text_reviews_count'].sum()\n",
    "authors_review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_review.rename(columns={'work_text_reviews_count': 'shared_number_of_reviews_with_a_text'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "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>authors</th>\n",
       "      <th>shared_number_of_reviews_with_a_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alan R. Clarke</td>\n",
       "      <td>55781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B.M. Mooyaart-Doubleday</td>\n",
       "      <td>20825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bernard Knox</td>\n",
       "      <td>8101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CelÃ¢l Ãster</td>\n",
       "      <td>45518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>David J. Skal</td>\n",
       "      <td>17263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Veronica Roth</td>\n",
       "      <td>156896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>William Golding</td>\n",
       "      <td>26886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>William Goldman</td>\n",
       "      <td>15630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>William Shakespeare</td>\n",
       "      <td>14778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Yann Martel</td>\n",
       "      <td>42962</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      authors  shared_number_of_reviews_with_a_text\n",
       "0              Alan R. Clarke                                 55781\n",
       "1     B.M. Mooyaart-Doubleday                                 20825\n",
       "2                Bernard Knox                                  8101\n",
       "3               CelÃ¢l Ãster                                 45518\n",
       "4               David J. Skal                                 17263\n",
       "..                        ...                                   ...\n",
       "105             Veronica Roth                                156896\n",
       "106           William Golding                                 26886\n",
       "107           William Goldman                                 15630\n",
       "108       William Shakespeare                                 14778\n",
       "109               Yann Martel                                 42962\n",
       "\n",
       "[110 rows x 2 columns]"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors_review"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "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>authors</th>\n",
       "      <th>shared_number_of_reviews_with_a_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Suzanne Collins</td>\n",
       "      <td>340066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>285369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Mary GrandPrÃ©</td>\n",
       "      <td>285369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Stephenie Meyer</td>\n",
       "      <td>258573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>John Green</td>\n",
       "      <td>230584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Homer</td>\n",
       "      <td>8101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bernard Knox</td>\n",
       "      <td>8101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Robert Fagles</td>\n",
       "      <td>8101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Lauren Weisberger</td>\n",
       "      <td>8024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>John Grisham</td>\n",
       "      <td>4239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               authors  shared_number_of_reviews_with_a_text\n",
       "104    Suzanne Collins                                340066\n",
       "64        J.K. Rowling                                285369\n",
       "18      Mary GrandPrÃ©                                285369\n",
       "100    Stephenie Meyer                                258573\n",
       "70          John Green                                230584\n",
       "..                 ...                                   ...\n",
       "62               Homer                                  8101\n",
       "2         Bernard Knox                                  8101\n",
       "29       Robert Fagles                                  8101\n",
       "77   Lauren Weisberger                                  8024\n",
       "71        John Grisham                                  4239\n",
       "\n",
       "[110 rows x 2 columns]"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors_review.sort_values(by='shared_number_of_reviews_with_a_text', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. For each year of publication, determine the author that has the largest value of the shared number of reviews with a text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodbooks_exploded.rename(columns={'work_text_reviews_count': 'shared_number_of_reviews_with_a_text'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "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>original_publication_year</th>\n",
       "      <th>shared_number_of_reviews_with_a_text</th>\n",
       "      <th>authors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-720.0</td>\n",
       "      <td>8101</td>\n",
       "      <td>Homer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1595.0</td>\n",
       "      <td>14778</td>\n",
       "      <td>William Shakespeare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1811.0</td>\n",
       "      <td>11526</td>\n",
       "      <td>Jane Austen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1813.0</td>\n",
       "      <td>49152</td>\n",
       "      <td>Jane Austen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1818.0</td>\n",
       "      <td>19993</td>\n",
       "      <td>Mary Wollstonecraft Shelley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1847.0</td>\n",
       "      <td>31212</td>\n",
       "      <td>Emily BrontÃ«</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1859.0</td>\n",
       "      <td>13093</td>\n",
       "      <td>Charles Dickens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1868.0</td>\n",
       "      <td>17090</td>\n",
       "      <td>Louisa May Alcott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1884.0</td>\n",
       "      <td>12448</td>\n",
       "      <td>Mark Twain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1891.0</td>\n",
       "      <td>19647</td>\n",
       "      <td>Oscar Wilde</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1897.0</td>\n",
       "      <td>17263</td>\n",
       "      <td>Bram Stoker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1911.0</td>\n",
       "      <td>13054</td>\n",
       "      <td>Frances Hodgson Burnett</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1925.0</td>\n",
       "      <td>51992</td>\n",
       "      <td>F. Scott Fitzgerald</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1932.0</td>\n",
       "      <td>20095</td>\n",
       "      <td>Aldous Huxley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1936.0</td>\n",
       "      <td>16050</td>\n",
       "      <td>Margaret Mitchell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1937.0</td>\n",
       "      <td>37653</td>\n",
       "      <td>John Steinbeck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1945.0</td>\n",
       "      <td>35472</td>\n",
       "      <td>George Orwell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1946.0</td>\n",
       "      <td>24537</td>\n",
       "      <td>Antoine de Saint-ExupÃ©ry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1947.0</td>\n",
       "      <td>20825</td>\n",
       "      <td>Anne Frank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1949.0</td>\n",
       "      <td>45518</td>\n",
       "      <td>George Orwell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1950.0</td>\n",
       "      <td>15186</td>\n",
       "      <td>C.S. Lewis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1951.0</td>\n",
       "      <td>44920</td>\n",
       "      <td>J.D. Salinger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1952.0</td>\n",
       "      <td>13044</td>\n",
       "      <td>E.B. White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1953.0</td>\n",
       "      <td>30694</td>\n",
       "      <td>Ray Bradbury</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1954.0</td>\n",
       "      <td>26886</td>\n",
       "      <td>William Golding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1958.0</td>\n",
       "      <td>22001</td>\n",
       "      <td>Elie Wiesel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1960.0</td>\n",
       "      <td>72586</td>\n",
       "      <td>Harper Lee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1964.0</td>\n",
       "      <td>14368</td>\n",
       "      <td>Shel Silverstein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1967.0</td>\n",
       "      <td>22662</td>\n",
       "      <td>S.E. Hinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1969.0</td>\n",
       "      <td>19646</td>\n",
       "      <td>Kurt Vonnegut Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1973.0</td>\n",
       "      <td>15630</td>\n",
       "      <td>William Goldman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1974.0</td>\n",
       "      <td>9234</td>\n",
       "      <td>Shel Silverstein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1977.0</td>\n",
       "      <td>14936</td>\n",
       "      <td>Stephen King</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1979.0</td>\n",
       "      <td>20345</td>\n",
       "      <td>Douglas Adams</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1985.0</td>\n",
       "      <td>38054</td>\n",
       "      <td>Orson Scott Card</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1988.0</td>\n",
       "      <td>55781</td>\n",
       "      <td>Paulo Coelho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1989.0</td>\n",
       "      <td>4239</td>\n",
       "      <td>John Grisham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1990.0</td>\n",
       "      <td>8143</td>\n",
       "      <td>Michael Crichton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1993.0</td>\n",
       "      <td>54084</td>\n",
       "      <td>Lois Lowry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1995.0</td>\n",
       "      <td>14915</td>\n",
       "      <td>Philip Pullman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1996.0</td>\n",
       "      <td>46205</td>\n",
       "      <td>Nicholas Sparks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1997.0</td>\n",
       "      <td>75867</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1998.0</td>\n",
       "      <td>34172</td>\n",
       "      <td>Louis Sachar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1999.0</td>\n",
       "      <td>47116</td>\n",
       "      <td>Stephen Chbosky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2000.0</td>\n",
       "      <td>31084</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2001.0</td>\n",
       "      <td>42962</td>\n",
       "      <td>Yann Martel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2002.0</td>\n",
       "      <td>36642</td>\n",
       "      <td>Christopher Paolini</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>59730</td>\n",
       "      <td>Mark Haddon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>30719</td>\n",
       "      <td>Jodi Picoult</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>95009</td>\n",
       "      <td>Stieg Larsson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>2006.0</td>\n",
       "      <td>55732</td>\n",
       "      <td>Stieg Larsson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>2007.0</td>\n",
       "      <td>51942</td>\n",
       "      <td>Stephenie Meyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2008.0</td>\n",
       "      <td>155254</td>\n",
       "      <td>Suzanne Collins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>2009.0</td>\n",
       "      <td>88538</td>\n",
       "      <td>Suzanne Collins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>2010.0</td>\n",
       "      <td>96274</td>\n",
       "      <td>Suzanne Collins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>2011.0</td>\n",
       "      <td>101023</td>\n",
       "      <td>Veronica Roth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>2012.0</td>\n",
       "      <td>140739</td>\n",
       "      <td>Veronica Roth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>2015.0</td>\n",
       "      <td>93600</td>\n",
       "      <td>Paula Hawkins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    original_publication_year  shared_number_of_reviews_with_a_text  \\\n",
       "0                      -720.0                                  8101   \n",
       "1                      1595.0                                 14778   \n",
       "2                      1811.0                                 11526   \n",
       "3                      1813.0                                 49152   \n",
       "4                      1818.0                                 19993   \n",
       "5                      1847.0                                 31212   \n",
       "6                      1859.0                                 13093   \n",
       "7                      1868.0                                 17090   \n",
       "8                      1884.0                                 12448   \n",
       "9                      1891.0                                 19647   \n",
       "10                     1897.0                                 17263   \n",
       "11                     1911.0                                 13054   \n",
       "12                     1925.0                                 51992   \n",
       "13                     1932.0                                 20095   \n",
       "14                     1936.0                                 16050   \n",
       "15                     1937.0                                 37653   \n",
       "16                     1945.0                                 35472   \n",
       "17                     1946.0                                 24537   \n",
       "18                     1947.0                                 20825   \n",
       "19                     1949.0                                 45518   \n",
       "20                     1950.0                                 15186   \n",
       "21                     1951.0                                 44920   \n",
       "22                     1952.0                                 13044   \n",
       "23                     1953.0                                 30694   \n",
       "24                     1954.0                                 26886   \n",
       "25                     1958.0                                 22001   \n",
       "26                     1960.0                                 72586   \n",
       "27                     1964.0                                 14368   \n",
       "28                     1967.0                                 22662   \n",
       "29                     1969.0                                 19646   \n",
       "30                     1973.0                                 15630   \n",
       "31                     1974.0                                  9234   \n",
       "32                     1977.0                                 14936   \n",
       "33                     1979.0                                 20345   \n",
       "34                     1985.0                                 38054   \n",
       "35                     1988.0                                 55781   \n",
       "36                     1989.0                                  4239   \n",
       "37                     1990.0                                  8143   \n",
       "38                     1993.0                                 54084   \n",
       "39                     1995.0                                 14915   \n",
       "40                     1996.0                                 46205   \n",
       "41                     1997.0                                 75867   \n",
       "42                     1998.0                                 34172   \n",
       "43                     1999.0                                 47116   \n",
       "44                     2000.0                                 31084   \n",
       "45                     2001.0                                 42962   \n",
       "46                     2002.0                                 36642   \n",
       "47                     2003.0                                 59730   \n",
       "48                     2004.0                                 30719   \n",
       "49                     2005.0                                 95009   \n",
       "50                     2006.0                                 55732   \n",
       "51                     2007.0                                 51942   \n",
       "52                     2008.0                                155254   \n",
       "53                     2009.0                                 88538   \n",
       "54                     2010.0                                 96274   \n",
       "55                     2011.0                                101023   \n",
       "56                     2012.0                                140739   \n",
       "57                     2015.0                                 93600   \n",
       "\n",
       "                        authors  \n",
       "0                         Homer  \n",
       "1           William Shakespeare  \n",
       "2                   Jane Austen  \n",
       "3                   Jane Austen  \n",
       "4   Mary Wollstonecraft Shelley  \n",
       "5                 Emily BrontÃ«  \n",
       "6               Charles Dickens  \n",
       "7             Louisa May Alcott  \n",
       "8                    Mark Twain  \n",
       "9                   Oscar Wilde  \n",
       "10                  Bram Stoker  \n",
       "11      Frances Hodgson Burnett  \n",
       "12          F. Scott Fitzgerald  \n",
       "13                Aldous Huxley  \n",
       "14            Margaret Mitchell  \n",
       "15               John Steinbeck  \n",
       "16                George Orwell  \n",
       "17    Antoine de Saint-ExupÃ©ry  \n",
       "18                   Anne Frank  \n",
       "19                George Orwell  \n",
       "20                   C.S. Lewis  \n",
       "21                J.D. Salinger  \n",
       "22                   E.B. White  \n",
       "23                 Ray Bradbury  \n",
       "24              William Golding  \n",
       "25                  Elie Wiesel  \n",
       "26                   Harper Lee  \n",
       "27             Shel Silverstein  \n",
       "28                  S.E. Hinton  \n",
       "29            Kurt Vonnegut Jr.  \n",
       "30              William Goldman  \n",
       "31             Shel Silverstein  \n",
       "32                 Stephen King  \n",
       "33                Douglas Adams  \n",
       "34             Orson Scott Card  \n",
       "35                 Paulo Coelho  \n",
       "36                 John Grisham  \n",
       "37             Michael Crichton  \n",
       "38                   Lois Lowry  \n",
       "39               Philip Pullman  \n",
       "40              Nicholas Sparks  \n",
       "41                 J.K. Rowling  \n",
       "42                 Louis Sachar  \n",
       "43              Stephen Chbosky  \n",
       "44                 J.K. Rowling  \n",
       "45                  Yann Martel  \n",
       "46          Christopher Paolini  \n",
       "47                  Mark Haddon  \n",
       "48                 Jodi Picoult  \n",
       "49                Stieg Larsson  \n",
       "50                Stieg Larsson  \n",
       "51              Stephenie Meyer  \n",
       "52              Suzanne Collins  \n",
       "53              Suzanne Collins  \n",
       "54              Suzanne Collins  \n",
       "55                Veronica Roth  \n",
       "56                Veronica Roth  \n",
       "57                Paula Hawkins  "
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodbooks_exploded.groupby('original_publication_year', as_index=False)\\\n",
    "[['shared_number_of_reviews_with_a_text', 'authors']].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Assuming that there are no errors in the ISBN fields, find the books in both datasets, and compute the difference of average rating according to the ratings and the goodratings datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "intersected_books = pd.merge(avg_rating_of_book, goodbooks, how ='inner', left_on ='ISBN', right_on= 'isbn')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ISBN', 'Book-Title', 'Book-Author', 'Book-Rating', 'book_id',\n",
       "       'goodreads_book_id', 'best_book_id', 'work_id', 'books_count', 'isbn',\n",
       "       'isbn13', 'authors', 'original_publication_year', 'original_title',\n",
       "       'title', 'language_code', 'average_rating', 'ratings_count',\n",
       "       'work_ratings_count', 'work_text_reviews_count', 'ratings_1',\n",
       "       'ratings_2', 'ratings_3', 'ratings_4', 'ratings_5', 'image_url',\n",
       "       'small_image_url'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 190,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intersected_books.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "intersected_books['ratings_difference'] = intersected_books['Book-Rating'] - intersected_books['average_rating']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "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>ratings_difference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>014028009X</td>\n",
       "      <td>Bridget Jones's Diary</td>\n",
       "      <td>0.001852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>014038572X</td>\n",
       "      <td>The Outsiders (Now in Speak!)</td>\n",
       "      <td>0.400674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>043965548X</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>-0.996667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1400032717</td>\n",
       "      <td>The Curious Incident of the Dog in the Night-T...</td>\n",
       "      <td>0.963187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1594480001</td>\n",
       "      <td>The Kite Runner</td>\n",
       "      <td>-1.860000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ISBN                                         Book-Title  \\\n",
       "0  014028009X                              Bridget Jones's Diary   \n",
       "1  014038572X                      The Outsiders (Now in Speak!)   \n",
       "2  043965548X  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "3  1400032717  The Curious Incident of the Dog in the Night-T...   \n",
       "4  1594480001                                    The Kite Runner   \n",
       "\n",
       "   ratings_difference  \n",
       "0            0.001852  \n",
       "1            0.400674  \n",
       "2           -0.996667  \n",
       "3            0.963187  \n",
       "4           -1.860000  "
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intersected_books[['ISBN','Book-Title', 'ratings_difference']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10. Split the users dataset according to the age. One dataset contains the users with unknown age, one with age 0-14, one with age 15-24, one with age 25-34, and so on"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_unknown = users[users['Age'].isnull() == True]\n",
    "users_0_14 = users[(users['Age']>=0) & (users['Age']<15)]\n",
    "\n",
    "max_value = users['Age'].max()\n",
    "multiplier = 10\n",
    "\n",
    "for i in range(15, int(max_value)+1, multiplier):\n",
    "    name=\"users_\" + str(i) + \"_\" + str(i+multiplier-1)\n",
    "    name = users[(users['Age']>=i) & (users['Age']<i+multiplier)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "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>city</th>\n",
       "      <th>region</th>\n",
       "      <th>country</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",
       "      <td>nyc</td>\n",
       "      <td>new york</td>\n",
       "      <td>usa</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",
       "      <td>moscow</td>\n",
       "      <td>yukon territory</td>\n",
       "      <td>russia</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",
       "      <td>farnborough</td>\n",
       "      <td>hants</td>\n",
       "      <td>united kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>washington, dc, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>washington</td>\n",
       "      <td>dc</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>timmins, ontario, canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>timmins</td>\n",
       "      <td>ontario</td>\n",
       "      <td>canada</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278849</th>\n",
       "      <td>278850</td>\n",
       "      <td>sergnano, lombardia, italy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sergnano</td>\n",
       "      <td>lombardia</td>\n",
       "      <td>italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278853</th>\n",
       "      <td>278854</td>\n",
       "      <td>portland, oregon, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>portland</td>\n",
       "      <td>oregon</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278855</th>\n",
       "      <td>278856</td>\n",
       "      <td>brampton, ontario, canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>brampton</td>\n",
       "      <td>ontario</td>\n",
       "      <td>canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278856</th>\n",
       "      <td>278857</td>\n",
       "      <td>knoxville, tennessee, usa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>knoxville</td>\n",
       "      <td>tennessee</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278857</th>\n",
       "      <td>278858</td>\n",
       "      <td>dublin, n/a, ireland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>dublin</td>\n",
       "      <td>n/a</td>\n",
       "      <td>ireland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110762 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        User-ID                            Location  Age         city  \\\n",
       "0             1                  nyc, new york, usa  NaN          nyc   \n",
       "2             3     moscow, yukon territory, russia  NaN       moscow   \n",
       "4             5  farnborough, hants, united kingdom  NaN  farnborough   \n",
       "6             7                 washington, dc, usa  NaN   washington   \n",
       "7             8            timmins, ontario, canada  NaN      timmins   \n",
       "...         ...                                 ...  ...          ...   \n",
       "278849   278850          sergnano, lombardia, italy  NaN     sergnano   \n",
       "278853   278854               portland, oregon, usa  NaN     portland   \n",
       "278855   278856           brampton, ontario, canada  NaN     brampton   \n",
       "278856   278857           knoxville, tennessee, usa  NaN    knoxville   \n",
       "278857   278858                dublin, n/a, ireland  NaN       dublin   \n",
       "\n",
       "                  region          country  \n",
       "0               new york              usa  \n",
       "2        yukon territory           russia  \n",
       "4                  hants   united kingdom  \n",
       "6                     dc              usa  \n",
       "7                ontario           canada  \n",
       "...                  ...              ...  \n",
       "278849         lombardia            italy  \n",
       "278853            oregon              usa  \n",
       "278855           ontario           canada  \n",
       "278856         tennessee              usa  \n",
       "278857               n/a          ireland  \n",
       "\n",
       "[110762 rows x 6 columns]"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_unknown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "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>city</th>\n",
       "      <th>region</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>melbourne, victoria, australia</td>\n",
       "      <td>14.0</td>\n",
       "      <td>melbourne</td>\n",
       "      <td>victoria</td>\n",
       "      <td>australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>weston, ,</td>\n",
       "      <td>14.0</td>\n",
       "      <td>weston</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>41</td>\n",
       "      <td>santee, california, usa</td>\n",
       "      <td>14.0</td>\n",
       "      <td>santee</td>\n",
       "      <td>california</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>120</td>\n",
       "      <td>villeneuve d`ascq, nord, france</td>\n",
       "      <td>13.0</td>\n",
       "      <td>villeneuve d`ascq</td>\n",
       "      <td>nord</td>\n",
       "      <td>france</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>220</td>\n",
       "      <td>bogota, bogota, colombia</td>\n",
       "      <td>0.0</td>\n",
       "      <td>bogota</td>\n",
       "      <td>bogota</td>\n",
       "      <td>colombia</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278574</th>\n",
       "      <td>278575</td>\n",
       "      <td>burnaby, british columbia, canada</td>\n",
       "      <td>14.0</td>\n",
       "      <td>burnaby</td>\n",
       "      <td>british columbia</td>\n",
       "      <td>canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278582</th>\n",
       "      <td>278583</td>\n",
       "      <td>steele, missouri, usa</td>\n",
       "      <td>13.0</td>\n",
       "      <td>steele</td>\n",
       "      <td>missouri</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278668</th>\n",
       "      <td>278669</td>\n",
       "      <td>spring hill, fl, hernando</td>\n",
       "      <td>13.0</td>\n",
       "      <td>spring hill</td>\n",
       "      <td>fl</td>\n",
       "      <td>hernando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278751</th>\n",
       "      <td>278752</td>\n",
       "      <td>london, nova scotia, canada</td>\n",
       "      <td>9.0</td>\n",
       "      <td>london</td>\n",
       "      <td>nova scotia</td>\n",
       "      <td>canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278793</th>\n",
       "      <td>278794</td>\n",
       "      <td>st. paul, minnesota, usa</td>\n",
       "      <td>14.0</td>\n",
       "      <td>st. paul</td>\n",
       "      <td>minnesota</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4313 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        User-ID                           Location   Age               city  \\\n",
       "10           11     melbourne, victoria, australia  14.0          melbourne   \n",
       "18           19                          weston, ,  14.0             weston   \n",
       "40           41            santee, california, usa  14.0             santee   \n",
       "119         120    villeneuve d`ascq, nord, france  13.0  villeneuve d`ascq   \n",
       "219         220           bogota, bogota, colombia   0.0             bogota   \n",
       "...         ...                                ...   ...                ...   \n",
       "278574   278575  burnaby, british columbia, canada  14.0            burnaby   \n",
       "278582   278583              steele, missouri, usa  13.0             steele   \n",
       "278668   278669          spring hill, fl, hernando  13.0        spring hill   \n",
       "278751   278752        london, nova scotia, canada   9.0             london   \n",
       "278793   278794           st. paul, minnesota, usa  14.0           st. paul   \n",
       "\n",
       "                   region     country  \n",
       "10               victoria   australia  \n",
       "18                                     \n",
       "40             california         usa  \n",
       "119                  nord      france  \n",
       "219                bogota    colombia  \n",
       "...                   ...         ...  \n",
       "278574   british columbia      canada  \n",
       "278582           missouri         usa  \n",
       "278668                 fl    hernando  \n",
       "278751        nova scotia      canada  \n",
       "278793          minnesota         usa  \n",
       "\n",
       "[4313 rows x 6 columns]"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_0_14"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "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>city</th>\n",
       "      <th>region</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>54</td>\n",
       "      <td>eubank, kentucky, usa</td>\n",
       "      <td>44.0</td>\n",
       "      <td>eubank</td>\n",
       "      <td>kentucky</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>67</td>\n",
       "      <td>framingham, massachusetts, usa</td>\n",
       "      <td>43.0</td>\n",
       "      <td>framingham</td>\n",
       "      <td>massachusetts</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>70</td>\n",
       "      <td>rochester, new york, usa</td>\n",
       "      <td>44.0</td>\n",
       "      <td>rochester</td>\n",
       "      <td>new york</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>75</td>\n",
       "      <td>long beach, california, usa</td>\n",
       "      <td>37.0</td>\n",
       "      <td>long beach</td>\n",
       "      <td>california</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>85</td>\n",
       "      <td>london, england, united kingdom</td>\n",
       "      <td>41.0</td>\n",
       "      <td>london</td>\n",
       "      <td>england</td>\n",
       "      <td>united kingdom</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278794</th>\n",
       "      <td>278795</td>\n",
       "      <td>santa pola, alicante, spain</td>\n",
       "      <td>35.0</td>\n",
       "      <td>santa pola</td>\n",
       "      <td>alicante</td>\n",
       "      <td>spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278797</th>\n",
       "      <td>278798</td>\n",
       "      <td>stayton, oregon, usa</td>\n",
       "      <td>39.0</td>\n",
       "      <td>stayton</td>\n",
       "      <td>oregon</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278807</th>\n",
       "      <td>278808</td>\n",
       "      <td>esplugas de llobregat, n/a, spain</td>\n",
       "      <td>43.0</td>\n",
       "      <td>esplugas de llobregat</td>\n",
       "      <td>n/a</td>\n",
       "      <td>spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278813</th>\n",
       "      <td>278814</td>\n",
       "      <td>san jose, california, usa</td>\n",
       "      <td>35.0</td>\n",
       "      <td>san jose</td>\n",
       "      <td>california</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278823</th>\n",
       "      <td>278824</td>\n",
       "      <td>madison, new jersey, usa</td>\n",
       "      <td>41.0</td>\n",
       "      <td>madison</td>\n",
       "      <td>new jersey</td>\n",
       "      <td>usa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32690 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        User-ID                           Location   Age  \\\n",
       "53           54              eubank, kentucky, usa  44.0   \n",
       "66           67     framingham, massachusetts, usa  43.0   \n",
       "69           70           rochester, new york, usa  44.0   \n",
       "74           75        long beach, california, usa  37.0   \n",
       "84           85    london, england, united kingdom  41.0   \n",
       "...         ...                                ...   ...   \n",
       "278794   278795        santa pola, alicante, spain  35.0   \n",
       "278797   278798               stayton, oregon, usa  39.0   \n",
       "278807   278808  esplugas de llobregat, n/a, spain  43.0   \n",
       "278813   278814          san jose, california, usa  35.0   \n",
       "278823   278824           madison, new jersey, usa  41.0   \n",
       "\n",
       "                         city          region          country  \n",
       "53                     eubank        kentucky              usa  \n",
       "66                 framingham   massachusetts              usa  \n",
       "69                  rochester        new york              usa  \n",
       "74                 long beach      california              usa  \n",
       "84                     london         england   united kingdom  \n",
       "...                       ...             ...              ...  \n",
       "278794             santa pola        alicante            spain  \n",
       "278797                stayton          oregon              usa  \n",
       "278807  esplugas de llobregat             n/a            spain  \n",
       "278813               san jose      california              usa  \n",
       "278823                madison      new jersey              usa  \n",
       "\n",
       "[32690 rows x 6 columns]"
      ]
     },
     "execution_count": 196,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_35_44"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11. Find the books that appear only in the goodbooks datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['439023483',\n",
       " '439554934',\n",
       " '316015849',\n",
       " '61120081',\n",
       " '743273567',\n",
       " '525478817',\n",
       " '618260307',\n",
       " '316769177',\n",
       " '1416524797',\n",
       " '679783261',\n",
       " '62024035',\n",
       " '451524934',\n",
       " '452284244',\n",
       " '553296981',\n",
       " '307269752',\n",
       " '439023491',\n",
       " '618346252',\n",
       " '439023513',\n",
       " '439358078',\n",
       " '316166685',\n",
       " '439064864',\n",
       " '439139600',\n",
       " '545010225',\n",
       " '307277674',\n",
       " '439785960',\n",
       " '140283331',\n",
       " '743477111',\n",
       " '297859382',\n",
       " '399155341',\n",
       " '142000671',\n",
       " '739326228',\n",
       " '1612130291',\n",
       " '61122416',\n",
       " '385732554',\n",
       " '60764899',\n",
       " '965818675',\n",
       " '553588486',\n",
       " '143038419',\n",
       " '786838655',\n",
       " '451529308',\n",
       " '142437204',\n",
       " '553816713',\n",
       " '770430074',\n",
       " '1565125606',\n",
       " '375831002',\n",
       " '307347974',\n",
       " '316160199',\n",
       " '60513039',\n",
       " '1416914285',\n",
       " '316160202',\n",
       " '375826696',\n",
       " '345391802',\n",
       " '60929871',\n",
       " '031606792X',\n",
       " '142001740',\n",
       " '142437174',\n",
       " '64410935',\n",
       " '1594633665',\n",
       " '679879242',\n",
       " '393978893',\n",
       " '743454537',\n",
       " '385333846',\n",
       " '446675539',\n",
       " '1594489505',\n",
       " '671027344',\n",
       " '7442912',\n",
       " '812550706',\n",
       " '141439475',\n",
       " '450040186',\n",
       " '316068047',\n",
       " '142402516',\n",
       " '141439661',\n",
       " '439244196',\n",
       " '307275558',\n",
       " '143039954',\n",
       " '156012197',\n",
       " '074324754X',\n",
       " '385486804',\n",
       " '141439602',\n",
       " '030734813X',\n",
       " '60256656',\n",
       " '385338600',\n",
       " '374500010',\n",
       " '014241493X',\n",
       " '345418263',\n",
       " '385737947',\n",
       " '61234001',\n",
       " '517189607',\n",
       " '60531045',\n",
       " '375751513',\n",
       " '345803507',\n",
       " '393970124',\n",
       " '307269981',\n",
       " '1612130585']"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lista= []\n",
    "books_ISBN = books['ISBN'].tolist()\n",
    "for codice in goodbooks['isbn']:\n",
    "    if codice not in books_ISBN:\n",
    "        lista.append(codice)\n",
    "lista"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book_id</th>\n",
       "      <th>goodreads_book_id</th>\n",
       "      <th>best_book_id</th>\n",
       "      <th>work_id</th>\n",
       "      <th>books_count</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>authors</th>\n",
       "      <th>original_publication_year</th>\n",
       "      <th>original_title</th>\n",
       "      <th>...</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>work_ratings_count</th>\n",
       "      <th>work_text_reviews_count</th>\n",
       "      <th>ratings_1</th>\n",
       "      <th>ratings_2</th>\n",
       "      <th>ratings_3</th>\n",
       "      <th>ratings_4</th>\n",
       "      <th>ratings_5</th>\n",
       "      <th>image_url</th>\n",
       "      <th>small_image_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2767052</td>\n",
       "      <td>2767052</td>\n",
       "      <td>2792775</td>\n",
       "      <td>272</td>\n",
       "      <td>439023483</td>\n",
       "      <td>9.780439e+12</td>\n",
       "      <td>[Suzanne Collins]</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>The Hunger Games</td>\n",
       "      <td>...</td>\n",
       "      <td>4780653</td>\n",
       "      <td>4942365</td>\n",
       "      <td>155254</td>\n",
       "      <td>66715</td>\n",
       "      <td>127936</td>\n",
       "      <td>560092</td>\n",
       "      <td>1481305</td>\n",
       "      <td>2706317</td>\n",
       "      <td>https://images.gr-assets.com/books/1447303603m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1447303603s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4640799</td>\n",
       "      <td>491</td>\n",
       "      <td>439554934</td>\n",
       "      <td>9.780440e+12</td>\n",
       "      <td>[J.K. Rowling,  Mary GrandPrÃ©]</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>Harry Potter and the Philosopher's Stone</td>\n",
       "      <td>...</td>\n",
       "      <td>4602479</td>\n",
       "      <td>4800065</td>\n",
       "      <td>75867</td>\n",
       "      <td>75504</td>\n",
       "      <td>101676</td>\n",
       "      <td>455024</td>\n",
       "      <td>1156318</td>\n",
       "      <td>3011543</td>\n",
       "      <td>https://images.gr-assets.com/books/1474154022m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1474154022s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>41865</td>\n",
       "      <td>41865</td>\n",
       "      <td>3212258</td>\n",
       "      <td>226</td>\n",
       "      <td>316015849</td>\n",
       "      <td>9.780316e+12</td>\n",
       "      <td>[Stephenie Meyer]</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>Twilight</td>\n",
       "      <td>...</td>\n",
       "      <td>3866839</td>\n",
       "      <td>3916824</td>\n",
       "      <td>95009</td>\n",
       "      <td>456191</td>\n",
       "      <td>436802</td>\n",
       "      <td>793319</td>\n",
       "      <td>875073</td>\n",
       "      <td>1355439</td>\n",
       "      <td>https://images.gr-assets.com/books/1361039443m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1361039443s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2657</td>\n",
       "      <td>2657</td>\n",
       "      <td>3275794</td>\n",
       "      <td>487</td>\n",
       "      <td>61120081</td>\n",
       "      <td>9.780061e+12</td>\n",
       "      <td>[Harper Lee]</td>\n",
       "      <td>1960.0</td>\n",
       "      <td>To Kill a Mockingbird</td>\n",
       "      <td>...</td>\n",
       "      <td>3198671</td>\n",
       "      <td>3340896</td>\n",
       "      <td>72586</td>\n",
       "      <td>60427</td>\n",
       "      <td>117415</td>\n",
       "      <td>446835</td>\n",
       "      <td>1001952</td>\n",
       "      <td>1714267</td>\n",
       "      <td>https://images.gr-assets.com/books/1361975680m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1361975680s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>4671</td>\n",
       "      <td>4671</td>\n",
       "      <td>245494</td>\n",
       "      <td>1356</td>\n",
       "      <td>743273567</td>\n",
       "      <td>9.780743e+12</td>\n",
       "      <td>[F. Scott Fitzgerald]</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>The Great Gatsby</td>\n",
       "      <td>...</td>\n",
       "      <td>2683664</td>\n",
       "      <td>2773745</td>\n",
       "      <td>51992</td>\n",
       "      <td>86236</td>\n",
       "      <td>197621</td>\n",
       "      <td>606158</td>\n",
       "      <td>936012</td>\n",
       "      <td>947718</td>\n",
       "      <td>https://images.gr-assets.com/books/1490528560m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1490528560s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>95</td>\n",
       "      <td>5297</td>\n",
       "      <td>5297</td>\n",
       "      <td>1858012</td>\n",
       "      <td>2303</td>\n",
       "      <td>375751513</td>\n",
       "      <td>9.780376e+12</td>\n",
       "      <td>[Oscar Wilde,  Jeffrey Eugenides]</td>\n",
       "      <td>1891.0</td>\n",
       "      <td>The Picture of Dorian Gray</td>\n",
       "      <td>...</td>\n",
       "      <td>590014</td>\n",
       "      <td>674551</td>\n",
       "      <td>19647</td>\n",
       "      <td>9825</td>\n",
       "      <td>30735</td>\n",
       "      <td>129420</td>\n",
       "      <td>246892</td>\n",
       "      <td>257679</td>\n",
       "      <td>https://images.gr-assets.com/books/1424596966m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1424596966s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>13536860</td>\n",
       "      <td>13536860</td>\n",
       "      <td>18034963</td>\n",
       "      <td>133</td>\n",
       "      <td>345803507</td>\n",
       "      <td>9.780346e+12</td>\n",
       "      <td>[E.L. James]</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>Fifty Shades Freed</td>\n",
       "      <td>...</td>\n",
       "      <td>387290</td>\n",
       "      <td>610116</td>\n",
       "      <td>25287</td>\n",
       "      <td>39079</td>\n",
       "      <td>55691</td>\n",
       "      <td>109963</td>\n",
       "      <td>140304</td>\n",
       "      <td>265079</td>\n",
       "      <td>https://images.gr-assets.com/books/1336418837m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1336418837s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>17245</td>\n",
       "      <td>17245</td>\n",
       "      <td>3165724</td>\n",
       "      <td>2207</td>\n",
       "      <td>393970124</td>\n",
       "      <td>9.780394e+12</td>\n",
       "      <td>[Bram Stoker,  Nina Auerbach,  David J. Skal]</td>\n",
       "      <td>1897.0</td>\n",
       "      <td>Dracula</td>\n",
       "      <td>...</td>\n",
       "      <td>618973</td>\n",
       "      <td>689739</td>\n",
       "      <td>17263</td>\n",
       "      <td>11756</td>\n",
       "      <td>36719</td>\n",
       "      <td>151396</td>\n",
       "      <td>246043</td>\n",
       "      <td>243825</td>\n",
       "      <td>https://images.gr-assets.com/books/1387151694m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1387151694s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>5060378</td>\n",
       "      <td>5060378</td>\n",
       "      <td>6976108</td>\n",
       "      <td>209</td>\n",
       "      <td>307269981</td>\n",
       "      <td>9.780307e+12</td>\n",
       "      <td>[Stieg Larsson,  Reg Keeland]</td>\n",
       "      <td>2006.0</td>\n",
       "      <td>Flickan som lekte med elden</td>\n",
       "      <td>...</td>\n",
       "      <td>563994</td>\n",
       "      <td>649253</td>\n",
       "      <td>31423</td>\n",
       "      <td>4623</td>\n",
       "      <td>15781</td>\n",
       "      <td>91021</td>\n",
       "      <td>258236</td>\n",
       "      <td>279592</td>\n",
       "      <td>https://images.gr-assets.com/books/1351778881m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1351778881s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>11857408</td>\n",
       "      <td>11857408</td>\n",
       "      <td>16813814</td>\n",
       "      <td>147</td>\n",
       "      <td>1612130585</td>\n",
       "      <td>9.781612e+12</td>\n",
       "      <td>[E.L. James]</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>Fifty Shades Darker</td>\n",
       "      <td>...</td>\n",
       "      <td>552059</td>\n",
       "      <td>623340</td>\n",
       "      <td>28052</td>\n",
       "      <td>37245</td>\n",
       "      <td>58935</td>\n",
       "      <td>114203</td>\n",
       "      <td>150906</td>\n",
       "      <td>262051</td>\n",
       "      <td>https://images.gr-assets.com/books/1358266080m...</td>\n",
       "      <td>https://images.gr-assets.com/books/1358266080s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>94 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    book_id  goodreads_book_id  best_book_id   work_id  books_count  \\\n",
       "0         1            2767052       2767052   2792775          272   \n",
       "1         2                  3             3   4640799          491   \n",
       "2         3              41865         41865   3212258          226   \n",
       "3         4               2657          2657   3275794          487   \n",
       "4         5               4671          4671    245494         1356   \n",
       "..      ...                ...           ...       ...          ...   \n",
       "94       95               5297          5297   1858012         2303   \n",
       "95       96           13536860      13536860  18034963          133   \n",
       "96       97              17245         17245   3165724         2207   \n",
       "97       98            5060378       5060378   6976108          209   \n",
       "98       99           11857408      11857408  16813814          147   \n",
       "\n",
       "          isbn        isbn13                                        authors  \\\n",
       "0    439023483  9.780439e+12                              [Suzanne Collins]   \n",
       "1    439554934  9.780440e+12                [J.K. Rowling,  Mary GrandPrÃ©]   \n",
       "2    316015849  9.780316e+12                              [Stephenie Meyer]   \n",
       "3     61120081  9.780061e+12                                   [Harper Lee]   \n",
       "4    743273567  9.780743e+12                          [F. Scott Fitzgerald]   \n",
       "..         ...           ...                                            ...   \n",
       "94   375751513  9.780376e+12              [Oscar Wilde,  Jeffrey Eugenides]   \n",
       "95   345803507  9.780346e+12                                   [E.L. James]   \n",
       "96   393970124  9.780394e+12  [Bram Stoker,  Nina Auerbach,  David J. Skal]   \n",
       "97   307269981  9.780307e+12                  [Stieg Larsson,  Reg Keeland]   \n",
       "98  1612130585  9.781612e+12                                   [E.L. James]   \n",
       "\n",
       "    original_publication_year                            original_title  ...  \\\n",
       "0                      2008.0                          The Hunger Games  ...   \n",
       "1                      1997.0  Harry Potter and the Philosopher's Stone  ...   \n",
       "2                      2005.0                                  Twilight  ...   \n",
       "3                      1960.0                     To Kill a Mockingbird  ...   \n",
       "4                      1925.0                          The Great Gatsby  ...   \n",
       "..                        ...                                       ...  ...   \n",
       "94                     1891.0                The Picture of Dorian Gray  ...   \n",
       "95                     2012.0                        Fifty Shades Freed  ...   \n",
       "96                     1897.0                                   Dracula  ...   \n",
       "97                     2006.0               Flickan som lekte med elden  ...   \n",
       "98                     2011.0                       Fifty Shades Darker  ...   \n",
       "\n",
       "   ratings_count work_ratings_count  work_text_reviews_count  ratings_1  \\\n",
       "0        4780653            4942365                   155254      66715   \n",
       "1        4602479            4800065                    75867      75504   \n",
       "2        3866839            3916824                    95009     456191   \n",
       "3        3198671            3340896                    72586      60427   \n",
       "4        2683664            2773745                    51992      86236   \n",
       "..           ...                ...                      ...        ...   \n",
       "94        590014             674551                    19647       9825   \n",
       "95        387290             610116                    25287      39079   \n",
       "96        618973             689739                    17263      11756   \n",
       "97        563994             649253                    31423       4623   \n",
       "98        552059             623340                    28052      37245   \n",
       "\n",
       "    ratings_2  ratings_3  ratings_4  ratings_5  \\\n",
       "0      127936     560092    1481305    2706317   \n",
       "1      101676     455024    1156318    3011543   \n",
       "2      436802     793319     875073    1355439   \n",
       "3      117415     446835    1001952    1714267   \n",
       "4      197621     606158     936012     947718   \n",
       "..        ...        ...        ...        ...   \n",
       "94      30735     129420     246892     257679   \n",
       "95      55691     109963     140304     265079   \n",
       "96      36719     151396     246043     243825   \n",
       "97      15781      91021     258236     279592   \n",
       "98      58935     114203     150906     262051   \n",
       "\n",
       "                                            image_url  \\\n",
       "0   https://images.gr-assets.com/books/1447303603m...   \n",
       "1   https://images.gr-assets.com/books/1474154022m...   \n",
       "2   https://images.gr-assets.com/books/1361039443m...   \n",
       "3   https://images.gr-assets.com/books/1361975680m...   \n",
       "4   https://images.gr-assets.com/books/1490528560m...   \n",
       "..                                                ...   \n",
       "94  https://images.gr-assets.com/books/1424596966m...   \n",
       "95  https://images.gr-assets.com/books/1336418837m...   \n",
       "96  https://images.gr-assets.com/books/1387151694m...   \n",
       "97  https://images.gr-assets.com/books/1351778881m...   \n",
       "98  https://images.gr-assets.com/books/1358266080m...   \n",
       "\n",
       "                                      small_image_url  \n",
       "0   https://images.gr-assets.com/books/1447303603s...  \n",
       "1   https://images.gr-assets.com/books/1474154022s...  \n",
       "2   https://images.gr-assets.com/books/1361039443s...  \n",
       "3   https://images.gr-assets.com/books/1361975680s...  \n",
       "4   https://images.gr-assets.com/books/1490528560s...  \n",
       "..                                                ...  \n",
       "94  https://images.gr-assets.com/books/1424596966s...  \n",
       "95  https://images.gr-assets.com/books/1336418837s...  \n",
       "96  https://images.gr-assets.com/books/1387151694s...  \n",
       "97  https://images.gr-assets.com/books/1351778881s...  \n",
       "98  https://images.gr-assets.com/books/1358266080s...  \n",
       "\n",
       "[94 rows x 23 columns]"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "only_goodbooks = goodbooks[goodbooks['isbn'].isin(lista)]\n",
    "only_goodbooks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12. Assuming that each pair (author, title) identifies a book, for each book find the number of times it appears in the books dataset. Which books appear the most times?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [],
   "source": [
    "books_times = books.groupby(['Book-Title', 'Book-Author'], as_index=False).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Book-Title          Little Women\n",
       "Book-Author    Louisa May Alcott\n",
       "size                          21\n",
       "Name: 113850, dtype: object"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_times.iloc[books_times['size'].idxmax()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Book-Title</th>\n",
       "      <th>Book-Author</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>113850</th>\n",
       "      <td>Little Women</td>\n",
       "      <td>Louisa May Alcott</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Book-Title        Book-Author  size\n",
       "113850  Little Women  Louisa May Alcott    21"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "books_times[books_times['size']>=books_times['size'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 13. Find the author with the highest average rating according to the goodbooks datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [],
   "source": [
    "authors_max_average_rating = goodbooks_exploded.groupby('authors', as_index=False)['average_rating'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "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>authors</th>\n",
       "      <th>average_rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Rufus Beck</td>\n",
       "      <td>4.53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        authors  average_rating\n",
       "32   Rufus Beck            4.53"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors_max_average_rating[authors_max_average_rating['average_rating']>=\n",
    "                               authors_max_average_rating['average_rating'].max()]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}