MovieLens 1M Dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MovieLens 1M Dataset\n",
    "\n",
    "GroupLens Research provides a number of collections of movie ratings data collected from users of MovieLens in the late 1990s and early 2000s. The data provide movie ratings, movie metadata (genres and year), and demographic data about the users (age, zip code, gender identification, and occupation). Such data is often of interest in the development of recommendation systems based on machine learning algorithms.\n",
    "\n",
    "While we do not explore machine learning techniques in detail in this notebook, I will show you how to slice and dice datasets like these into the exact form you need. The MovieLens 1M dataset contains <b>1 million ratings</b> collected from 6,000 users on 4,000 movies. It’s spread across three tables: \n",
    "<ul><li>ratings</li><li>user information</li><li>movie information</li></ul>\n",
    "    \n",
    "***\n",
    "\n",
    "You can find this dataset by googling \"MovieLens 1M Datast\" or <a href=\"https://grouplens.org/datasets/movielens/1m/\" target=\"_blank\">here</a>, <br> These three files are zipped, so extract into your working directory.    \n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Let's first read users data</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = pd.read_table('Pandas/users.dat')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>1::F::1::10::48067</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2::M::56::16::70072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3::M::25::15::55117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4::M::45::7::02460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5::M::25::20::55455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6::F::50::9::55117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6034</th>\n",
       "      <td>6036::F::25::15::32603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6035</th>\n",
       "      <td>6037::F::45::1::76006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6036</th>\n",
       "      <td>6038::F::56::1::14706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6037</th>\n",
       "      <td>6039::F::45::0::01060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6038</th>\n",
       "      <td>6040::M::25::6::11106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6039 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          1::F::1::10::48067\n",
       "0        2::M::56::16::70072\n",
       "1        3::M::25::15::55117\n",
       "2         4::M::45::7::02460\n",
       "3        5::M::25::20::55455\n",
       "4         6::F::50::9::55117\n",
       "...                      ...\n",
       "6034  6036::F::25::15::32603\n",
       "6035   6037::F::45::1::76006\n",
       "6036   6038::F::56::1::14706\n",
       "6037   6039::F::45::0::01060\n",
       "6038   6040::M::25::6::11106\n",
       "\n",
       "[6039 rows x 1 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h2>We have a few observations to make here </h2><br>\n",
    "<h3>\n",
    "<ol>\n",
    "    <li>data is all squezed into one column</li>\n",
    "    <li>data elements are seperated by <b><code>\"::\"</code></b> separator</li>\n",
    "    <li>There are no column headers</li>\n",
    "    <li>First row of data is read as headers</li>\n",
    "</ol> \n",
    "</h3>\n",
    "<h4>You can read the documentation of the dataset where you downloaded it from, and find the column headers for each of the table & relavent details. Often you will have to read documentation of the dataset before you start using the dataset</h4><br>\n",
    "<h4>So now that we understand the data better - lets work on the dataset to make it usable - this step is called <code>data cleansing</code></h4>\n",
    "<br><h4>Lets read all three tables</h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\brk3\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "C:\\Users\\brk3\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.\n",
      "  \n",
      "C:\\Users\\brk3\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:9: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.\n",
      "  if __name__ == '__main__':\n"
     ]
    }
   ],
   "source": [
    "unames = ['user_id', 'gender', 'age', 'occupation', 'zip']\n",
    "users = pd.read_table('Pandas/users.dat', sep='::', header=None, names=unames)\n",
    "\n",
    "rnames = ['user_id', 'movie_id', 'rating', 'timestamp']\n",
    "ratings = pd.read_table('Pandas/ratings.dat', sep='::', header=None, names=rnames)\n",
    "\n",
    "mnames = ['movie_id', 'title', 'genres']\n",
    "movies = pd.read_table('Pandas/movies.dat', sep='::', header=None, names=mnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "\n",
    "<h4>You will see we have addressed all our observations by using these :<br>\n",
    "<ul>\n",
    "<li>OBSERVATION 1 & 2 : using <code>sep = '::' </code></li>\n",
    "<li>OBSERVATION 3: by creating our own list with header names </li>\n",
    "    <li>OBSERVATION 4: by using <code>names = 'unames'</code></li></ul></h4>\n",
    "Lets see if these worked\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>occupation</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>M</td>\n",
       "      <td>56</td>\n",
       "      <td>16</td>\n",
       "      <td>70072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>15</td>\n",
       "      <td>55117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>M</td>\n",
       "      <td>45</td>\n",
       "      <td>7</td>\n",
       "      <td>02460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>20</td>\n",
       "      <td>55455</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id gender  age  occupation    zip\n",
       "0        1      F    1          10  48067\n",
       "1        2      M   56          16  70072\n",
       "2        3      M   25          15  55117\n",
       "3        4      M   45           7  02460\n",
       "4        5      M   25          20  55455"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>movie_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978300760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>661</td>\n",
       "      <td>3</td>\n",
       "      <td>978302109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>914</td>\n",
       "      <td>3</td>\n",
       "      <td>978301968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>3408</td>\n",
       "      <td>4</td>\n",
       "      <td>978300275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2355</td>\n",
       "      <td>5</td>\n",
       "      <td>978824291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  movie_id  rating  timestamp\n",
       "0        1      1193       5  978300760\n",
       "1        1       661       3  978302109\n",
       "2        1       914       3  978301968\n",
       "3        1      3408       4  978300275\n",
       "4        1      2355       5  978824291"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>movie_id</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children's|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale (1995)</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   movie_id                               title                        genres\n",
       "0         1                    Toy Story (1995)   Animation|Children's|Comedy\n",
       "1         2                      Jumanji (1995)  Adventure|Children's|Fantasy\n",
       "2         3             Grumpier Old Men (1995)                Comedy|Romance\n",
       "3         4            Waiting to Exhale (1995)                  Comedy|Drama\n",
       "4         5  Father of the Bride Part II (1995)                        Comedy"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "\n",
    "Note that ages and occupations are coded as integers indicating groups described in\n",
    "the dataset’s README file. Analyzing the data spread across three tables is not a simple\n",
    "task; for example, suppose you wanted to compute mean ratings for a particular\n",
    "movie by sex and age. As you will see, this is much easier to do with all of the data\n",
    "merged together into a single table. Using pandas’s <b><code>merge</code></b> function, we first merge\n",
    "ratings with users and then merge that result with the movies data. pandas infers\n",
    "which columns to use as the merge (or join) keys based on overlapping names:\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>movie_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>occupation</th>\n",
       "      <th>zip</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978300760</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest (1975)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978298413</td>\n",
       "      <td>M</td>\n",
       "      <td>56</td>\n",
       "      <td>16</td>\n",
       "      <td>70072</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest (1975)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>1193</td>\n",
       "      <td>4</td>\n",
       "      <td>978220179</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>12</td>\n",
       "      <td>32793</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest (1975)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>1193</td>\n",
       "      <td>4</td>\n",
       "      <td>978199279</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>7</td>\n",
       "      <td>22903</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest (1975)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>1193</td>\n",
       "      <td>5</td>\n",
       "      <td>978158471</td>\n",
       "      <td>M</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>95350</td>\n",
       "      <td>One Flew Over the Cuckoo's Nest (1975)</td>\n",
       "      <td>Drama</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>1000204</th>\n",
       "      <td>5949</td>\n",
       "      <td>2198</td>\n",
       "      <td>5</td>\n",
       "      <td>958846401</td>\n",
       "      <td>M</td>\n",
       "      <td>18</td>\n",
       "      <td>17</td>\n",
       "      <td>47901</td>\n",
       "      <td>Modulations (1998)</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000205</th>\n",
       "      <td>5675</td>\n",
       "      <td>2703</td>\n",
       "      <td>3</td>\n",
       "      <td>976029116</td>\n",
       "      <td>M</td>\n",
       "      <td>35</td>\n",
       "      <td>14</td>\n",
       "      <td>30030</td>\n",
       "      <td>Broken Vessels (1998)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000206</th>\n",
       "      <td>5780</td>\n",
       "      <td>2845</td>\n",
       "      <td>1</td>\n",
       "      <td>958153068</td>\n",
       "      <td>M</td>\n",
       "      <td>18</td>\n",
       "      <td>17</td>\n",
       "      <td>92886</td>\n",
       "      <td>White Boys (1999)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000207</th>\n",
       "      <td>5851</td>\n",
       "      <td>3607</td>\n",
       "      <td>5</td>\n",
       "      <td>957756608</td>\n",
       "      <td>F</td>\n",
       "      <td>18</td>\n",
       "      <td>20</td>\n",
       "      <td>55410</td>\n",
       "      <td>One Little Indian (1973)</td>\n",
       "      <td>Comedy|Drama|Western</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000208</th>\n",
       "      <td>5938</td>\n",
       "      <td>2909</td>\n",
       "      <td>4</td>\n",
       "      <td>957273353</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>35401</td>\n",
       "      <td>Five Wives, Three Secretaries and Me (1998)</td>\n",
       "      <td>Documentary</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000209 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id  movie_id  rating  timestamp gender  age  occupation    zip  \\\n",
       "0              1      1193       5  978300760      F    1          10  48067   \n",
       "1              2      1193       5  978298413      M   56          16  70072   \n",
       "2             12      1193       4  978220179      M   25          12  32793   \n",
       "3             15      1193       4  978199279      M   25           7  22903   \n",
       "4             17      1193       5  978158471      M   50           1  95350   \n",
       "...          ...       ...     ...        ...    ...  ...         ...    ...   \n",
       "1000204     5949      2198       5  958846401      M   18          17  47901   \n",
       "1000205     5675      2703       3  976029116      M   35          14  30030   \n",
       "1000206     5780      2845       1  958153068      M   18          17  92886   \n",
       "1000207     5851      3607       5  957756608      F   18          20  55410   \n",
       "1000208     5938      2909       4  957273353      M   25           1  35401   \n",
       "\n",
       "                                               title                genres  \n",
       "0             One Flew Over the Cuckoo's Nest (1975)                 Drama  \n",
       "1             One Flew Over the Cuckoo's Nest (1975)                 Drama  \n",
       "2             One Flew Over the Cuckoo's Nest (1975)                 Drama  \n",
       "3             One Flew Over the Cuckoo's Nest (1975)                 Drama  \n",
       "4             One Flew Over the Cuckoo's Nest (1975)                 Drama  \n",
       "...                                              ...                   ...  \n",
       "1000204                           Modulations (1998)           Documentary  \n",
       "1000205                        Broken Vessels (1998)                 Drama  \n",
       "1000206                            White Boys (1999)                 Drama  \n",
       "1000207                     One Little Indian (1973)  Comedy|Drama|Western  \n",
       "1000208  Five Wives, Three Secretaries and Me (1998)           Documentary  \n",
       "\n",
       "[1000209 rows x 10 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.merge(pd.merge(ratings, users), movies)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "\n",
    "<h4>We see from ratings table that each user has multiple ratings, where did all these go? Lets check for user with user_id = 1</h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>movie_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>occupation</th>\n",
       "      <th>zip</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12759</th>\n",
       "      <td>1</td>\n",
       "      <td>595</td>\n",
       "      <td>5</td>\n",
       "      <td>978824268</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Beauty and the Beast (1991)</td>\n",
       "      <td>Animation|Children's|Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13819</th>\n",
       "      <td>1</td>\n",
       "      <td>938</td>\n",
       "      <td>4</td>\n",
       "      <td>978301752</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Gigi (1958)</td>\n",
       "      <td>Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14006</th>\n",
       "      <td>1</td>\n",
       "      <td>2398</td>\n",
       "      <td>4</td>\n",
       "      <td>978302281</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Miracle on 34th Street (1947)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14386</th>\n",
       "      <td>1</td>\n",
       "      <td>2918</td>\n",
       "      <td>4</td>\n",
       "      <td>978302124</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Ferris Bueller's Day Off (1986)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15859</th>\n",
       "      <td>1</td>\n",
       "      <td>1035</td>\n",
       "      <td>5</td>\n",
       "      <td>978301753</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Sound of Music, The (1965)</td>\n",
       "      <td>Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16741</th>\n",
       "      <td>1</td>\n",
       "      <td>2791</td>\n",
       "      <td>4</td>\n",
       "      <td>978302188</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Airplane! (1980)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18472</th>\n",
       "      <td>1</td>\n",
       "      <td>2687</td>\n",
       "      <td>3</td>\n",
       "      <td>978824268</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Tarzan (1999)</td>\n",
       "      <td>Animation|Children's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18914</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>978301777</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Bambi (1942)</td>\n",
       "      <td>Animation|Children's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19503</th>\n",
       "      <td>1</td>\n",
       "      <td>3105</td>\n",
       "      <td>5</td>\n",
       "      <td>978301713</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Awakenings (1990)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20183</th>\n",
       "      <td>1</td>\n",
       "      <td>2797</td>\n",
       "      <td>4</td>\n",
       "      <td>978302039</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Big (1988)</td>\n",
       "      <td>Comedy|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21674</th>\n",
       "      <td>1</td>\n",
       "      <td>2321</td>\n",
       "      <td>3</td>\n",
       "      <td>978302205</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Pleasantville (1998)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22832</th>\n",
       "      <td>1</td>\n",
       "      <td>720</td>\n",
       "      <td>3</td>\n",
       "      <td>978300760</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Wallace &amp; Gromit: The Best of Aardman Animatio...</td>\n",
       "      <td>Animation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23270</th>\n",
       "      <td>1</td>\n",
       "      <td>1270</td>\n",
       "      <td>5</td>\n",
       "      <td>978300055</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Back to the Future (1985)</td>\n",
       "      <td>Comedy|Sci-Fi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25853</th>\n",
       "      <td>1</td>\n",
       "      <td>527</td>\n",
       "      <td>5</td>\n",
       "      <td>978824195</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Schindler's List (1993)</td>\n",
       "      <td>Drama|War</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28157</th>\n",
       "      <td>1</td>\n",
       "      <td>2340</td>\n",
       "      <td>3</td>\n",
       "      <td>978300103</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Meet Joe Black (1998)</td>\n",
       "      <td>Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28501</th>\n",
       "      <td>1</td>\n",
       "      <td>48</td>\n",
       "      <td>5</td>\n",
       "      <td>978824351</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Pocahontas (1995)</td>\n",
       "      <td>Animation|Children's|Musical|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28883</th>\n",
       "      <td>1</td>\n",
       "      <td>1097</td>\n",
       "      <td>4</td>\n",
       "      <td>978301953</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>E.T. the Extra-Terrestrial (1982)</td>\n",
       "      <td>Children's|Drama|Fantasy|Sci-Fi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31152</th>\n",
       "      <td>1</td>\n",
       "      <td>1721</td>\n",
       "      <td>4</td>\n",
       "      <td>978300055</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Titanic (1997)</td>\n",
       "      <td>Drama|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32698</th>\n",
       "      <td>1</td>\n",
       "      <td>1545</td>\n",
       "      <td>4</td>\n",
       "      <td>978824139</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Ponette (1996)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32771</th>\n",
       "      <td>1</td>\n",
       "      <td>745</td>\n",
       "      <td>3</td>\n",
       "      <td>978824268</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Close Shave, A (1995)</td>\n",
       "      <td>Animation|Comedy|Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33428</th>\n",
       "      <td>1</td>\n",
       "      <td>2294</td>\n",
       "      <td>4</td>\n",
       "      <td>978824291</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Antz (1998)</td>\n",
       "      <td>Animation|Children's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34073</th>\n",
       "      <td>1</td>\n",
       "      <td>3186</td>\n",
       "      <td>4</td>\n",
       "      <td>978300019</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Girl, Interrupted (1999)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34504</th>\n",
       "      <td>1</td>\n",
       "      <td>1566</td>\n",
       "      <td>4</td>\n",
       "      <td>978824330</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Hercules (1997)</td>\n",
       "      <td>Adventure|Animation|Children's|Comedy|Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34973</th>\n",
       "      <td>1</td>\n",
       "      <td>588</td>\n",
       "      <td>4</td>\n",
       "      <td>978824268</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Aladdin (1992)</td>\n",
       "      <td>Animation|Children's|Comedy|Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36324</th>\n",
       "      <td>1</td>\n",
       "      <td>1907</td>\n",
       "      <td>4</td>\n",
       "      <td>978824330</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Mulan (1998)</td>\n",
       "      <td>Animation|Children's</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36814</th>\n",
       "      <td>1</td>\n",
       "      <td>783</td>\n",
       "      <td>4</td>\n",
       "      <td>978824291</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Hunchback of Notre Dame, The (1996)</td>\n",
       "      <td>Animation|Children's|Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37204</th>\n",
       "      <td>1</td>\n",
       "      <td>1836</td>\n",
       "      <td>5</td>\n",
       "      <td>978300172</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Last Days of Disco, The (1998)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37339</th>\n",
       "      <td>1</td>\n",
       "      <td>1022</td>\n",
       "      <td>5</td>\n",
       "      <td>978300055</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Cinderella (1950)</td>\n",
       "      <td>Animation|Children's|Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37916</th>\n",
       "      <td>1</td>\n",
       "      <td>2762</td>\n",
       "      <td>4</td>\n",
       "      <td>978302091</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Sixth Sense, The (1999)</td>\n",
       "      <td>Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40375</th>\n",
       "      <td>1</td>\n",
       "      <td>150</td>\n",
       "      <td>5</td>\n",
       "      <td>978301777</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Apollo 13 (1995)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41626</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>978824268</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43703</th>\n",
       "      <td>1</td>\n",
       "      <td>1961</td>\n",
       "      <td>5</td>\n",
       "      <td>978301590</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Rain Man (1988)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45033</th>\n",
       "      <td>1</td>\n",
       "      <td>1962</td>\n",
       "      <td>4</td>\n",
       "      <td>978301753</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Driving Miss Daisy (1989)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45685</th>\n",
       "      <td>1</td>\n",
       "      <td>2692</td>\n",
       "      <td>4</td>\n",
       "      <td>978301570</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Run Lola Run (Lola rennt) (1998)</td>\n",
       "      <td>Action|Crime|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46757</th>\n",
       "      <td>1</td>\n",
       "      <td>260</td>\n",
       "      <td>4</td>\n",
       "      <td>978300760</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Star Wars: Episode IV - A New Hope (1977)</td>\n",
       "      <td>Action|Adventure|Fantasy|Sci-Fi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49748</th>\n",
       "      <td>1</td>\n",
       "      <td>1028</td>\n",
       "      <td>5</td>\n",
       "      <td>978301777</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Mary Poppins (1964)</td>\n",
       "      <td>Children's|Comedy|Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50759</th>\n",
       "      <td>1</td>\n",
       "      <td>1029</td>\n",
       "      <td>5</td>\n",
       "      <td>978302205</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Dumbo (1941)</td>\n",
       "      <td>Animation|Children's|Musical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51327</th>\n",
       "      <td>1</td>\n",
       "      <td>1207</td>\n",
       "      <td>4</td>\n",
       "      <td>978300719</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>To Kill a Mockingbird (1962)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52255</th>\n",
       "      <td>1</td>\n",
       "      <td>2028</td>\n",
       "      <td>5</td>\n",
       "      <td>978301619</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Saving Private Ryan (1998)</td>\n",
       "      <td>Action|Drama|War</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54908</th>\n",
       "      <td>1</td>\n",
       "      <td>531</td>\n",
       "      <td>4</td>\n",
       "      <td>978302149</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Secret Garden, The (1993)</td>\n",
       "      <td>Children's|Drama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55246</th>\n",
       "      <td>1</td>\n",
       "      <td>3114</td>\n",
       "      <td>4</td>\n",
       "      <td>978302174</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Toy Story 2 (1999)</td>\n",
       "      <td>Animation|Children's|Comedy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56831</th>\n",
       "      <td>1</td>\n",
       "      <td>608</td>\n",
       "      <td>4</td>\n",
       "      <td>978301398</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Fargo (1996)</td>\n",
       "      <td>Crime|Drama|Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59344</th>\n",
       "      <td>1</td>\n",
       "      <td>1246</td>\n",
       "      <td>4</td>\n",
       "      <td>978302091</td>\n",
       "      <td>F</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>48067</td>\n",
       "      <td>Dead Poets Society (1989)</td>\n",
       "      <td>Drama</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  movie_id  rating  timestamp gender  age  occupation    zip  \\\n",
       "12759        1       595       5  978824268      F    1          10  48067   \n",
       "13819        1       938       4  978301752      F    1          10  48067   \n",
       "14006        1      2398       4  978302281      F    1          10  48067   \n",
       "14386        1      2918       4  978302124      F    1          10  48067   \n",
       "15859        1      1035       5  978301753      F    1          10  48067   \n",
       "16741        1      2791       4  978302188      F    1          10  48067   \n",
       "18472        1      2687       3  978824268      F    1          10  48067   \n",
       "18914        1      2018       4  978301777      F    1          10  48067   \n",
       "19503        1      3105       5  978301713      F    1          10  48067   \n",
       "20183        1      2797       4  978302039      F    1          10  48067   \n",
       "21674        1      2321       3  978302205      F    1          10  48067   \n",
       "22832        1       720       3  978300760      F    1          10  48067   \n",
       "23270        1      1270       5  978300055      F    1          10  48067   \n",
       "25853        1       527       5  978824195      F    1          10  48067   \n",
       "28157        1      2340       3  978300103      F    1          10  48067   \n",
       "28501        1        48       5  978824351      F    1          10  48067   \n",
       "28883        1      1097       4  978301953      F    1          10  48067   \n",
       "31152        1      1721       4  978300055      F    1          10  48067   \n",
       "32698        1      1545       4  978824139      F    1          10  48067   \n",
       "32771        1       745       3  978824268      F    1          10  48067   \n",
       "33428        1      2294       4  978824291      F    1          10  48067   \n",
       "34073        1      3186       4  978300019      F    1          10  48067   \n",
       "34504        1      1566       4  978824330      F    1          10  48067   \n",
       "34973        1       588       4  978824268      F    1          10  48067   \n",
       "36324        1      1907       4  978824330      F    1          10  48067   \n",
       "36814        1       783       4  978824291      F    1          10  48067   \n",
       "37204        1      1836       5  978300172      F    1          10  48067   \n",
       "37339        1      1022       5  978300055      F    1          10  48067   \n",
       "37916        1      2762       4  978302091      F    1          10  48067   \n",
       "40375        1       150       5  978301777      F    1          10  48067   \n",
       "41626        1         1       5  978824268      F    1          10  48067   \n",
       "43703        1      1961       5  978301590      F    1          10  48067   \n",
       "45033        1      1962       4  978301753      F    1          10  48067   \n",
       "45685        1      2692       4  978301570      F    1          10  48067   \n",
       "46757        1       260       4  978300760      F    1          10  48067   \n",
       "49748        1      1028       5  978301777      F    1          10  48067   \n",
       "50759        1      1029       5  978302205      F    1          10  48067   \n",
       "51327        1      1207       4  978300719      F    1          10  48067   \n",
       "52255        1      2028       5  978301619      F    1          10  48067   \n",
       "54908        1       531       4  978302149      F    1          10  48067   \n",
       "55246        1      3114       4  978302174      F    1          10  48067   \n",
       "56831        1       608       4  978301398      F    1          10  48067   \n",
       "59344        1      1246       4  978302091      F    1          10  48067   \n",
       "\n",
       "                                                   title  \\\n",
       "12759                        Beauty and the Beast (1991)   \n",
       "13819                                        Gigi (1958)   \n",
       "14006                      Miracle on 34th Street (1947)   \n",
       "14386                    Ferris Bueller's Day Off (1986)   \n",
       "15859                         Sound of Music, The (1965)   \n",
       "16741                                   Airplane! (1980)   \n",
       "18472                                      Tarzan (1999)   \n",
       "18914                                       Bambi (1942)   \n",
       "19503                                  Awakenings (1990)   \n",
       "20183                                         Big (1988)   \n",
       "21674                               Pleasantville (1998)   \n",
       "22832  Wallace & Gromit: The Best of Aardman Animatio...   \n",
       "23270                          Back to the Future (1985)   \n",
       "25853                            Schindler's List (1993)   \n",
       "28157                              Meet Joe Black (1998)   \n",
       "28501                                  Pocahontas (1995)   \n",
       "28883                  E.T. the Extra-Terrestrial (1982)   \n",
       "31152                                     Titanic (1997)   \n",
       "32698                                     Ponette (1996)   \n",
       "32771                              Close Shave, A (1995)   \n",
       "33428                                        Antz (1998)   \n",
       "34073                           Girl, Interrupted (1999)   \n",
       "34504                                    Hercules (1997)   \n",
       "34973                                     Aladdin (1992)   \n",
       "36324                                       Mulan (1998)   \n",
       "36814                Hunchback of Notre Dame, The (1996)   \n",
       "37204                     Last Days of Disco, The (1998)   \n",
       "37339                                  Cinderella (1950)   \n",
       "37916                            Sixth Sense, The (1999)   \n",
       "40375                                   Apollo 13 (1995)   \n",
       "41626                                   Toy Story (1995)   \n",
       "43703                                    Rain Man (1988)   \n",
       "45033                          Driving Miss Daisy (1989)   \n",
       "45685                   Run Lola Run (Lola rennt) (1998)   \n",
       "46757          Star Wars: Episode IV - A New Hope (1977)   \n",
       "49748                                Mary Poppins (1964)   \n",
       "50759                                       Dumbo (1941)   \n",
       "51327                       To Kill a Mockingbird (1962)   \n",
       "52255                         Saving Private Ryan (1998)   \n",
       "54908                          Secret Garden, The (1993)   \n",
       "55246                                 Toy Story 2 (1999)   \n",
       "56831                                       Fargo (1996)   \n",
       "59344                          Dead Poets Society (1989)   \n",
       "\n",
       "                                              genres  \n",
       "12759                   Animation|Children's|Musical  \n",
       "13819                                        Musical  \n",
       "14006                                          Drama  \n",
       "14386                                         Comedy  \n",
       "15859                                        Musical  \n",
       "16741                                         Comedy  \n",
       "18472                           Animation|Children's  \n",
       "18914                           Animation|Children's  \n",
       "19503                                          Drama  \n",
       "20183                                 Comedy|Fantasy  \n",
       "21674                                         Comedy  \n",
       "22832                                      Animation  \n",
       "23270                                  Comedy|Sci-Fi  \n",
       "25853                                      Drama|War  \n",
       "28157                                        Romance  \n",
       "28501           Animation|Children's|Musical|Romance  \n",
       "28883                Children's|Drama|Fantasy|Sci-Fi  \n",
       "31152                                  Drama|Romance  \n",
       "32698                                          Drama  \n",
       "32771                      Animation|Comedy|Thriller  \n",
       "33428                           Animation|Children's  \n",
       "34073                                          Drama  \n",
       "34504  Adventure|Animation|Children's|Comedy|Musical  \n",
       "34973            Animation|Children's|Comedy|Musical  \n",
       "36324                           Animation|Children's  \n",
       "36814                   Animation|Children's|Musical  \n",
       "37204                                          Drama  \n",
       "37339                   Animation|Children's|Musical  \n",
       "37916                                       Thriller  \n",
       "40375                                          Drama  \n",
       "41626                    Animation|Children's|Comedy  \n",
       "43703                                          Drama  \n",
       "45033                                          Drama  \n",
       "45685                           Action|Crime|Romance  \n",
       "46757                Action|Adventure|Fantasy|Sci-Fi  \n",
       "49748                      Children's|Comedy|Musical  \n",
       "50759                   Animation|Children's|Musical  \n",
       "51327                                          Drama  \n",
       "52255                               Action|Drama|War  \n",
       "54908                               Children's|Drama  \n",
       "55246                    Animation|Children's|Comedy  \n",
       "56831                           Crime|Drama|Thriller  \n",
       "59344                                          Drama  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['user_id']==1][10:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h4>Lesson learnt : Never trust what is displayed, since data is large - python often find ways to display it in most consise way, so allways interogate the data properly and do not rely on what is displayed</h4>\n",
    "\n",
    "\n",
    "Let's get the mean movie ratings for each film grouped by gender, we can use the <code>pivot_table</code> method:\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>$1,000,000 Duck (1971)</th>\n",
       "      <td>3.375000</td>\n",
       "      <td>2.761905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>'Night Mother (1986)</th>\n",
       "      <td>3.388889</td>\n",
       "      <td>3.352941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>'Til There Was You (1997)</th>\n",
       "      <td>2.675676</td>\n",
       "      <td>2.733333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>'burbs, The (1989)</th>\n",
       "      <td>2.793478</td>\n",
       "      <td>2.962085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...And Justice for All (1979)</th>\n",
       "      <td>3.828571</td>\n",
       "      <td>3.689024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "gender                                F         M\n",
       "title                                            \n",
       "$1,000,000 Duck (1971)         3.375000  2.761905\n",
       "'Night Mother (1986)           3.388889  3.352941\n",
       "'Til There Was You (1997)      2.675676  2.733333\n",
       "'burbs, The (1989)             2.793478  2.962085\n",
       "...And Justice for All (1979)  3.828571  3.689024"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_ratings = data.pivot_table('rating', index='title',columns='gender', aggfunc='mean')\n",
    "mean_ratings[:5]   # i have shown only top 5 rows to quickly check if the output was as it was intended to be"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h4>This produced another DataFrame containing mean ratings with movie titles as row\n",
    "labels (the “index”) and gender as column labels. I first filter down to movies that\n",
    "received at least 250 ratings (a completely arbitrary number); to do this, I then group\n",
    "the data by title and use <code><b>size()</b></code> to get a Series of group sizes for each title:</h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title\n",
       "$1,000,000 Duck (1971)                37\n",
       "'Night Mother (1986)                  70\n",
       "'Til There Was You (1997)             52\n",
       "'burbs, The (1989)                   303\n",
       "...And Justice for All (1979)        199\n",
       "1-900 (1994)                           2\n",
       "10 Things I Hate About You (1999)    700\n",
       "101 Dalmatians (1961)                565\n",
       "101 Dalmatians (1996)                364\n",
       "12 Angry Men (1957)                  616\n",
       "dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_by_title = data.groupby('title').size()\n",
    "ratings_by_title[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([''burbs, The (1989)', '10 Things I Hate About You (1999)',\n",
       "       '101 Dalmatians (1961)', '101 Dalmatians (1996)', '12 Angry Men (1957)',\n",
       "       '13th Warrior, The (1999)', '2 Days in the Valley (1996)',\n",
       "       '20,000 Leagues Under the Sea (1954)', '2001: A Space Odyssey (1968)',\n",
       "       '2010 (1984)',\n",
       "       ...\n",
       "       'X-Men (2000)', 'Year of Living Dangerously (1982)',\n",
       "       'Yellow Submarine (1968)', 'You've Got Mail (1998)',\n",
       "       'Young Frankenstein (1974)', 'Young Guns (1988)',\n",
       "       'Young Guns II (1990)', 'Young Sherlock Holmes (1985)',\n",
       "       'Zero Effect (1998)', 'eXistenZ (1999)'],\n",
       "      dtype='object', name='title', length=1216)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "active_titles = ratings_by_title.index[ratings_by_title >= 250]\n",
    "active_titles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h4>The index of titles receiving at least 250 ratings can then be used to select rows from mean_ratings:</h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>'burbs, The (1989)</th>\n",
       "      <td>2.793478</td>\n",
       "      <td>2.962085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10 Things I Hate About You (1999)</th>\n",
       "      <td>3.646552</td>\n",
       "      <td>3.311966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101 Dalmatians (1961)</th>\n",
       "      <td>3.791444</td>\n",
       "      <td>3.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101 Dalmatians (1996)</th>\n",
       "      <td>3.240000</td>\n",
       "      <td>2.911215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 Angry Men (1957)</th>\n",
       "      <td>4.184397</td>\n",
       "      <td>4.328421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Young Guns (1988)</th>\n",
       "      <td>3.371795</td>\n",
       "      <td>3.425620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Young Guns II (1990)</th>\n",
       "      <td>2.934783</td>\n",
       "      <td>2.904025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Young Sherlock Holmes (1985)</th>\n",
       "      <td>3.514706</td>\n",
       "      <td>3.363344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zero Effect (1998)</th>\n",
       "      <td>3.864407</td>\n",
       "      <td>3.723140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eXistenZ (1999)</th>\n",
       "      <td>3.098592</td>\n",
       "      <td>3.289086</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1216 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "gender                                    F         M\n",
       "title                                                \n",
       "'burbs, The (1989)                 2.793478  2.962085\n",
       "10 Things I Hate About You (1999)  3.646552  3.311966\n",
       "101 Dalmatians (1961)              3.791444  3.500000\n",
       "101 Dalmatians (1996)              3.240000  2.911215\n",
       "12 Angry Men (1957)                4.184397  4.328421\n",
       "...                                     ...       ...\n",
       "Young Guns (1988)                  3.371795  3.425620\n",
       "Young Guns II (1990)               2.934783  2.904025\n",
       "Young Sherlock Holmes (1985)       3.514706  3.363344\n",
       "Zero Effect (1998)                 3.864407  3.723140\n",
       "eXistenZ (1999)                    3.098592  3.289086\n",
       "\n",
       "[1216 rows x 2 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_ratings = mean_ratings.loc[active_titles]\n",
    "mean_ratings     # notice the size of table changing from previous mean_rating table "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h4>To see the top films among female viewers, we can sort by the F column in descending order:</h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Close Shave, A (1995)</th>\n",
       "      <td>4.644444</td>\n",
       "      <td>4.473795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wrong Trousers, The (1993)</th>\n",
       "      <td>4.588235</td>\n",
       "      <td>4.478261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sunset Blvd. (a.k.a. Sunset Boulevard) (1950)</th>\n",
       "      <td>4.572650</td>\n",
       "      <td>4.464589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wallace &amp; Gromit: The Best of Aardman Animation (1996)</th>\n",
       "      <td>4.563107</td>\n",
       "      <td>4.385075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Schindler's List (1993)</th>\n",
       "      <td>4.562602</td>\n",
       "      <td>4.491415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Avengers, The (1998)</th>\n",
       "      <td>1.915254</td>\n",
       "      <td>2.017467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Speed 2: Cruise Control (1997)</th>\n",
       "      <td>1.906667</td>\n",
       "      <td>1.863014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rocky V (1990)</th>\n",
       "      <td>1.878788</td>\n",
       "      <td>2.132780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barb Wire (1996)</th>\n",
       "      <td>1.585366</td>\n",
       "      <td>2.100386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Battlefield Earth (2000)</th>\n",
       "      <td>1.574468</td>\n",
       "      <td>1.616949</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1216 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "gender                                                     F         M\n",
       "title                                                                 \n",
       "Close Shave, A (1995)                               4.644444  4.473795\n",
       "Wrong Trousers, The (1993)                          4.588235  4.478261\n",
       "Sunset Blvd. (a.k.a. Sunset Boulevard) (1950)       4.572650  4.464589\n",
       "Wallace & Gromit: The Best of Aardman Animation...  4.563107  4.385075\n",
       "Schindler's List (1993)                             4.562602  4.491415\n",
       "...                                                      ...       ...\n",
       "Avengers, The (1998)                                1.915254  2.017467\n",
       "Speed 2: Cruise Control (1997)                      1.906667  1.863014\n",
       "Rocky V (1990)                                      1.878788  2.132780\n",
       "Barb Wire (1996)                                    1.585366  2.100386\n",
       "Battlefield Earth (2000)                            1.574468  1.616949\n",
       "\n",
       "[1216 rows x 2 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_female_ratings = mean_ratings.sort_values(by='F', ascending=False)\n",
    "top_female_ratings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h2>Measuring Rating Disagreement</h2>\n",
    "Suppose you wanted to find the movies that are most divisive between male and  female viewers. One way is to add a column to mean_ratings containing the difference in means, then sort by that:\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>'burbs, The (1989)</th>\n",
       "      <td>2.793478</td>\n",
       "      <td>2.962085</td>\n",
       "      <td>0.168607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10 Things I Hate About You (1999)</th>\n",
       "      <td>3.646552</td>\n",
       "      <td>3.311966</td>\n",
       "      <td>-0.334586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101 Dalmatians (1961)</th>\n",
       "      <td>3.791444</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>-0.291444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101 Dalmatians (1996)</th>\n",
       "      <td>3.240000</td>\n",
       "      <td>2.911215</td>\n",
       "      <td>-0.328785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 Angry Men (1957)</th>\n",
       "      <td>4.184397</td>\n",
       "      <td>4.328421</td>\n",
       "      <td>0.144024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Young Guns (1988)</th>\n",
       "      <td>3.371795</td>\n",
       "      <td>3.425620</td>\n",
       "      <td>0.053825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Young Guns II (1990)</th>\n",
       "      <td>2.934783</td>\n",
       "      <td>2.904025</td>\n",
       "      <td>-0.030758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Young Sherlock Holmes (1985)</th>\n",
       "      <td>3.514706</td>\n",
       "      <td>3.363344</td>\n",
       "      <td>-0.151362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zero Effect (1998)</th>\n",
       "      <td>3.864407</td>\n",
       "      <td>3.723140</td>\n",
       "      <td>-0.141266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eXistenZ (1999)</th>\n",
       "      <td>3.098592</td>\n",
       "      <td>3.289086</td>\n",
       "      <td>0.190494</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1216 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "gender                                    F         M      diff\n",
       "title                                                          \n",
       "'burbs, The (1989)                 2.793478  2.962085  0.168607\n",
       "10 Things I Hate About You (1999)  3.646552  3.311966 -0.334586\n",
       "101 Dalmatians (1961)              3.791444  3.500000 -0.291444\n",
       "101 Dalmatians (1996)              3.240000  2.911215 -0.328785\n",
       "12 Angry Men (1957)                4.184397  4.328421  0.144024\n",
       "...                                     ...       ...       ...\n",
       "Young Guns (1988)                  3.371795  3.425620  0.053825\n",
       "Young Guns II (1990)               2.934783  2.904025 -0.030758\n",
       "Young Sherlock Holmes (1985)       3.514706  3.363344 -0.151362\n",
       "Zero Effect (1998)                 3.864407  3.723140 -0.141266\n",
       "eXistenZ (1999)                    3.098592  3.289086  0.190494\n",
       "\n",
       "[1216 rows x 3 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_ratings['diff'] = mean_ratings['M'] - mean_ratings['F']\n",
    "mean_ratings   # new column added."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h4>Sorting by 'diff' yields the movies with the greatest rating difference so that we can see which ones were preferred by women:</h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Dirty Dancing (1987)</th>\n",
       "      <td>3.790378</td>\n",
       "      <td>2.959596</td>\n",
       "      <td>-0.830782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jumpin' Jack Flash (1986)</th>\n",
       "      <td>3.254717</td>\n",
       "      <td>2.578358</td>\n",
       "      <td>-0.676359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grease (1978)</th>\n",
       "      <td>3.975265</td>\n",
       "      <td>3.367041</td>\n",
       "      <td>-0.608224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Little Women (1994)</th>\n",
       "      <td>3.870588</td>\n",
       "      <td>3.321739</td>\n",
       "      <td>-0.548849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steel Magnolias (1989)</th>\n",
       "      <td>3.901734</td>\n",
       "      <td>3.365957</td>\n",
       "      <td>-0.535777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Anastasia (1997)</th>\n",
       "      <td>3.800000</td>\n",
       "      <td>3.281609</td>\n",
       "      <td>-0.518391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rocky Horror Picture Show, The (1975)</th>\n",
       "      <td>3.673016</td>\n",
       "      <td>3.160131</td>\n",
       "      <td>-0.512885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Color Purple, The (1985)</th>\n",
       "      <td>4.158192</td>\n",
       "      <td>3.659341</td>\n",
       "      <td>-0.498851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Age of Innocence, The (1993)</th>\n",
       "      <td>3.827068</td>\n",
       "      <td>3.339506</td>\n",
       "      <td>-0.487561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Free Willy (1993)</th>\n",
       "      <td>2.921348</td>\n",
       "      <td>2.438776</td>\n",
       "      <td>-0.482573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "gender                                        F         M      diff\n",
       "title                                                              \n",
       "Dirty Dancing (1987)                   3.790378  2.959596 -0.830782\n",
       "Jumpin' Jack Flash (1986)              3.254717  2.578358 -0.676359\n",
       "Grease (1978)                          3.975265  3.367041 -0.608224\n",
       "Little Women (1994)                    3.870588  3.321739 -0.548849\n",
       "Steel Magnolias (1989)                 3.901734  3.365957 -0.535777\n",
       "Anastasia (1997)                       3.800000  3.281609 -0.518391\n",
       "Rocky Horror Picture Show, The (1975)  3.673016  3.160131 -0.512885\n",
       "Color Purple, The (1985)               4.158192  3.659341 -0.498851\n",
       "Age of Innocence, The (1993)           3.827068  3.339506 -0.487561\n",
       "Free Willy (1993)                      2.921348  2.438776 -0.482573"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted_by_diff = mean_ratings.sort_values(by='diff')\n",
    "sorted_by_diff[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h4>Reversing the order of the rows and again slicing off the top 10 rows, we get the movies preferred by men that women didn’t rate as highly: </h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>gender</th>\n",
       "      <th>F</th>\n",
       "      <th>M</th>\n",
       "      <th>diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Good, The Bad and The Ugly, The (1966)</th>\n",
       "      <td>3.494949</td>\n",
       "      <td>4.221300</td>\n",
       "      <td>0.726351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky Fried Movie, The (1977)</th>\n",
       "      <td>2.878788</td>\n",
       "      <td>3.555147</td>\n",
       "      <td>0.676359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dumb &amp; Dumber (1994)</th>\n",
       "      <td>2.697987</td>\n",
       "      <td>3.336595</td>\n",
       "      <td>0.638608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Longest Day, The (1962)</th>\n",
       "      <td>3.411765</td>\n",
       "      <td>4.031447</td>\n",
       "      <td>0.619682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cable Guy, The (1996)</th>\n",
       "      <td>2.250000</td>\n",
       "      <td>2.863787</td>\n",
       "      <td>0.613787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Evil Dead II (Dead By Dawn) (1987)</th>\n",
       "      <td>3.297297</td>\n",
       "      <td>3.909283</td>\n",
       "      <td>0.611985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hidden, The (1987)</th>\n",
       "      <td>3.137931</td>\n",
       "      <td>3.745098</td>\n",
       "      <td>0.607167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rocky III (1982)</th>\n",
       "      <td>2.361702</td>\n",
       "      <td>2.943503</td>\n",
       "      <td>0.581801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Caddyshack (1980)</th>\n",
       "      <td>3.396135</td>\n",
       "      <td>3.969737</td>\n",
       "      <td>0.573602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>For a Few Dollars More (1965)</th>\n",
       "      <td>3.409091</td>\n",
       "      <td>3.953795</td>\n",
       "      <td>0.544704</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "gender                                         F         M      diff\n",
       "title                                                               \n",
       "Good, The Bad and The Ugly, The (1966)  3.494949  4.221300  0.726351\n",
       "Kentucky Fried Movie, The (1977)        2.878788  3.555147  0.676359\n",
       "Dumb & Dumber (1994)                    2.697987  3.336595  0.638608\n",
       "Longest Day, The (1962)                 3.411765  4.031447  0.619682\n",
       "Cable Guy, The (1996)                   2.250000  2.863787  0.613787\n",
       "Evil Dead II (Dead By Dawn) (1987)      3.297297  3.909283  0.611985\n",
       "Hidden, The (1987)                      3.137931  3.745098  0.607167\n",
       "Rocky III (1982)                        2.361702  2.943503  0.581801\n",
       "Caddyshack (1980)                       3.396135  3.969737  0.573602\n",
       "For a Few Dollars More (1965)           3.409091  3.953795  0.544704"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted_by_diff[::-1][:10]  # note the slice [::-1] reverses the order and [:10] slices top 10 records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "<h4>Suppose instead you wanted the movies that elicited the most disagreement among\n",
    "viewers, independent of gender identification. Disagreement can be measured by the\n",
    "variance or standard deviation of the ratings: </h4>\n",
    "\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title\n",
       "Dumb & Dumber (1994)                     1.321333\n",
       "Blair Witch Project, The (1999)          1.316368\n",
       "Natural Born Killers (1994)              1.307198\n",
       "Tank Girl (1995)                         1.277695\n",
       "Rocky Horror Picture Show, The (1975)    1.260177\n",
       "Eyes Wide Shut (1999)                    1.259624\n",
       "Evita (1996)                             1.253631\n",
       "Billy Madison (1995)                     1.249970\n",
       "Fear and Loathing in Las Vegas (1998)    1.246408\n",
       "Bicentennial Man (1999)                  1.245533\n",
       "Name: rating, dtype: float64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rating_std_by_title = data.groupby('title')['rating'].std()  # create group by title and calculate sd for ratings\n",
    "rating_std_by_title = rating_std_by_title.loc[active_titles] # ilter the resulting set for active titles\n",
    "rating_std_by_title.sort_values(ascending=False)[:10]    #Order Series by value in descending order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1> END OF SECTION </h1>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}