Clustering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a47fdb78",
   "metadata": {},
   "source": [
    "## Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "612a112e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.cluster import KMeans"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "596aef58",
   "metadata": {},
   "source": [
    "## Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8d58a9c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "movies = pd.read_csv('movies.csv',\n",
    "            sep=',',\n",
    "            names=['MovieID','Title','Genres'],\n",
    "            engine='python',\n",
    "            header=0\n",
    "            )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d49c1e3d",
   "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>MovieID</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>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children|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|Romance</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": [
       "   MovieID                               Title  \\\n",
       "0        1                    Toy Story (1995)   \n",
       "1        2                      Jumanji (1995)   \n",
       "2        3             Grumpier Old Men (1995)   \n",
       "3        4            Waiting to Exhale (1995)   \n",
       "4        5  Father of the Bride Part II (1995)   \n",
       "\n",
       "                                        Genres  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  \n",
       "1                   Adventure|Children|Fantasy  \n",
       "2                               Comedy|Romance  \n",
       "3                         Comedy|Drama|Romance  \n",
       "4                                       Comedy  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6a0ee99e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings = pd.read_csv('ratings.csv',\n",
    "            sep=',',\n",
    "            names=['UserID','MovieID','Rating', 'Timestamp'],\n",
    "            parse_dates=['Timestamp'],\n",
    "            engine='python',\n",
    "            header=0\n",
    "            )\n",
    "ratings = ratings.drop(\"Timestamp\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1e36394a",
   "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>UserID</th>\n",
       "      <th>MovieID</th>\n",
       "      <th>Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1029</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1061</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1129</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1172</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserID  MovieID  Rating\n",
       "0       1       31     2.5\n",
       "1       1     1029     3.0\n",
       "2       1     1061     3.0\n",
       "3       1     1129     2.0\n",
       "4       1     1172     4.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "01c29ec6",
   "metadata": {},
   "outputs": [],
   "source": [
    "movie_ratings = pd.merge(movies, ratings, on='MovieID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "49a791b3",
   "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>MovieID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Genres</th>\n",
       "      <th>UserID</th>\n",
       "      <th>Rating</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>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "      <td>9</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "      <td>13</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "      <td>19</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MovieID             Title                                       Genres  \\\n",
       "0        1  Toy Story (1995)  Adventure|Animation|Children|Comedy|Fantasy   \n",
       "1        1  Toy Story (1995)  Adventure|Animation|Children|Comedy|Fantasy   \n",
       "2        1  Toy Story (1995)  Adventure|Animation|Children|Comedy|Fantasy   \n",
       "3        1  Toy Story (1995)  Adventure|Animation|Children|Comedy|Fantasy   \n",
       "4        1  Toy Story (1995)  Adventure|Animation|Children|Comedy|Fantasy   \n",
       "\n",
       "   UserID  Rating  \n",
       "0       7     3.0  \n",
       "1       9     4.0  \n",
       "2      13     5.0  \n",
       "3      15     2.0  \n",
       "4      19     3.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ee84feba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100004, 5)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_ratings.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "1c50c1a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "genre = movie_ratings['Genres']\n",
    "genre = genre.str.get_dummies()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "3c886e16",
   "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>(no genres listed)</th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>Documentary</th>\n",
       "      <th>Drama</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>IMAX</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   (no genres listed)  Action  Adventure  Animation  Children  Comedy  Crime  \\\n",
       "0                   0       0          1          1         1       1      0   \n",
       "1                   0       0          1          1         1       1      0   \n",
       "2                   0       0          1          1         1       1      0   \n",
       "3                   0       0          1          1         1       1      0   \n",
       "4                   0       0          1          1         1       1      0   \n",
       "\n",
       "   Documentary  Drama  Fantasy  Film-Noir  Horror  IMAX  Musical  Mystery  \\\n",
       "0            0      0        1          0       0     0        0        0   \n",
       "1            0      0        1          0       0     0        0        0   \n",
       "2            0      0        1          0       0     0        0        0   \n",
       "3            0      0        1          0       0     0        0        0   \n",
       "4            0      0        1          0       0     0        0        0   \n",
       "\n",
       "   Romance  Sci-Fi  Thriller  War  Western  \n",
       "0        0       0         0    0        0  \n",
       "1        0       0         0    0        0  \n",
       "2        0       0         0    0        0  \n",
       "3        0       0         0    0        0  \n",
       "4        0       0         0    0        0  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "c979174e",
   "metadata": {},
   "outputs": [],
   "source": [
    "movie_ratings_genres = pd.concat([movie_ratings.drop(['Genres'], axis=1), genre], axis=1,)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "725c8bc0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "79d13139",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "movie_ratings_genres = movie_ratings_genres.drop(['(no genres listed)'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "414745e8",
   "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>MovieID</th>\n",
       "      <th>Title</th>\n",
       "      <th>UserID</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>...</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>IMAX</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>57266</th>\n",
       "      <td>2953</td>\n",
       "      <td>Home Alone 2: Lost in New York (1992)</td>\n",
       "      <td>150</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36532</th>\n",
       "      <td>1414</td>\n",
       "      <td>Mother (1996)</td>\n",
       "      <td>537</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4588</th>\n",
       "      <td>150</td>\n",
       "      <td>Apollo 13 (1995)</td>\n",
       "      <td>194</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34096</th>\n",
       "      <td>1300</td>\n",
       "      <td>My Life as a Dog (Mitt liv som hund) (1985)</td>\n",
       "      <td>232</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32670</th>\n",
       "      <td>1266</td>\n",
       "      <td>Unforgiven (1992)</td>\n",
       "      <td>433</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       MovieID                                        Title  UserID  Rating  \\\n",
       "57266     2953        Home Alone 2: Lost in New York (1992)     150     1.5   \n",
       "36532     1414                                Mother (1996)     537     4.0   \n",
       "4588       150                             Apollo 13 (1995)     194     4.0   \n",
       "34096     1300  My Life as a Dog (Mitt liv som hund) (1985)     232     5.0   \n",
       "32670     1266                            Unforgiven (1992)     433     4.0   \n",
       "\n",
       "       Action  Adventure  Animation  Children  Comedy  Crime  ...  Film-Noir  \\\n",
       "57266       0          0          0         1       1      0  ...          0   \n",
       "36532       0          0          0         0       1      0  ...          0   \n",
       "4588        0          1          0         0       0      0  ...          0   \n",
       "34096       0          0          0         0       1      0  ...          0   \n",
       "32670       0          0          0         0       0      0  ...          0   \n",
       "\n",
       "       Horror  IMAX  Musical  Mystery  Romance  Sci-Fi  Thriller  War  Western  \n",
       "57266       0     0        0        0        0       0         0    0        0  \n",
       "36532       0     0        0        0        0       0         0    0        0  \n",
       "4588        0     1        0        0        0       0         0    0        0  \n",
       "34096       0     0        0        0        0       0         0    0        0  \n",
       "32670       0     0        0        0        0       0         0    0        1  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_ratings_genres.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "25ef0f98",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Adventure',\n",
       " 'Animation',\n",
       " 'Children',\n",
       " 'Comedy',\n",
       " 'Fantasy',\n",
       " 'Romance',\n",
       " 'Drama',\n",
       " 'Action',\n",
       " 'Crime',\n",
       " 'Thriller',\n",
       " 'Horror',\n",
       " 'Mystery',\n",
       " 'Sci-Fi',\n",
       " 'Documentary',\n",
       " 'IMAX',\n",
       " 'War',\n",
       " 'Musical',\n",
       " 'Western',\n",
       " 'Film-Noir']"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genres = list(pd.unique(movies[\"Genres\"].str.split(\"|\", expand=True).stack()))\n",
    "genres.remove('(no genres listed)')\n",
    "genres"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25b45e95",
   "metadata": {},
   "source": [
    "# Melt and pivot with one hot encoding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "9b4e175e",
   "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>UserID</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Genre</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900071</th>\n",
       "      <td>287</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Film-Noir</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900072</th>\n",
       "      <td>73</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Film-Noir</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900073</th>\n",
       "      <td>611</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Film-Noir</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900074</th>\n",
       "      <td>611</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Film-Noir</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900075</th>\n",
       "      <td>547</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Film-Noir</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1900076 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         UserID  Rating      Genre  value\n",
       "0             7     3.0  Adventure      1\n",
       "1             9     4.0  Adventure      1\n",
       "2            13     5.0  Adventure      1\n",
       "3            15     2.0  Adventure      1\n",
       "4            19     3.0  Adventure      1\n",
       "...         ...     ...        ...    ...\n",
       "1900071     287     5.0  Film-Noir      0\n",
       "1900072      73     4.5  Film-Noir      0\n",
       "1900073     611     5.0  Film-Noir      0\n",
       "1900074     611     3.0  Film-Noir      0\n",
       "1900075     547     5.0  Film-Noir      0\n",
       "\n",
       "[1900076 rows x 4 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#melting dataframe to form one hot encoding dataframe\n",
    "mdf = pd.melt(movie_ratings_genres[['UserID', 'Rating'] + genres], id_vars=['UserID', 'Rating'], var_name='Genre')\n",
    "mdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "8f994621",
   "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>UserID</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1896118</th>\n",
       "      <td>628</td>\n",
       "      <td>2.5</td>\n",
       "      <td>Film-Noir</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1896119</th>\n",
       "      <td>664</td>\n",
       "      <td>3.5</td>\n",
       "      <td>Film-Noir</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1896336</th>\n",
       "      <td>199</td>\n",
       "      <td>3.5</td>\n",
       "      <td>Film-Noir</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1896502</th>\n",
       "      <td>380</td>\n",
       "      <td>2.5</td>\n",
       "      <td>Film-Noir</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1896503</th>\n",
       "      <td>522</td>\n",
       "      <td>1.5</td>\n",
       "      <td>Film-Noir</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>265499 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         UserID  Rating      Genre\n",
       "0             7     3.0  Adventure\n",
       "1             9     4.0  Adventure\n",
       "2            13     5.0  Adventure\n",
       "3            15     2.0  Adventure\n",
       "4            19     3.0  Adventure\n",
       "...         ...     ...        ...\n",
       "1896118     628     2.5  Film-Noir\n",
       "1896119     664     3.5  Film-Noir\n",
       "1896336     199     3.5  Film-Noir\n",
       "1896502     380     2.5  Film-Noir\n",
       "1896503     522     1.5  Film-Noir\n",
       "\n",
       "[265499 rows x 3 columns]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#keeping only 1 values for every user at every genre\n",
    "mdf = mdf[mdf['value']==1][['UserID', 'Rating', 'Genre']]\n",
    "mdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "b17555ea",
   "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",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"19\" halign=\"left\">Rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Genre</th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>Documentary</th>\n",
       "      <th>Drama</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>IMAX</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UserID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.80</td>\n",
       "      <td>2.17</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.57</td>\n",
       "      <td>2.12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.50</td>\n",
       "      <td>2.40</td>\n",
       "      <td>2.83</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.32</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.09</td>\n",
       "      <td>3.67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.62</td>\n",
       "      <td>3.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.50</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.59</td>\n",
       "      <td>3.80</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3.80</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.47</td>\n",
       "      <td>3.30</td>\n",
       "      <td>2.88</td>\n",
       "      <td>2.70</td>\n",
       "      <td>3.61</td>\n",
       "      <td>3.70</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.92</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.38</td>\n",
       "      <td>2.88</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.65</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3.55</td>\n",
       "      <td>4.17</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.27</td>\n",
       "      <td>4.34</td>\n",
       "      <td>4.71</td>\n",
       "      <td>4.63</td>\n",
       "      <td>4.33</td>\n",
       "      <td>4.16</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.46</td>\n",
       "      <td>4.57</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.94</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.83</td>\n",
       "      <td>4.09</td>\n",
       "      <td>4.50</td>\n",
       "      <td>4.26</td>\n",
       "      <td>4.02</td>\n",
       "      <td>4.75</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4.00</td>\n",
       "      <td>3.82</td>\n",
       "      <td>3.90</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.98</td>\n",
       "      <td>3.45</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.81</td>\n",
       "      <td>3.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.88</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.25</td>\n",
       "      <td>3.33</td>\n",
       "      <td>4.08</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.62</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>667</th>\n",
       "      <td>3.27</td>\n",
       "      <td>3.57</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.88</td>\n",
       "      <td>3.67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.71</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.67</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.26</td>\n",
       "      <td>4.25</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.83</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>668</th>\n",
       "      <td>3.50</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.44</td>\n",
       "      <td>4.57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.31</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.50</td>\n",
       "      <td>4.50</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>669</th>\n",
       "      <td>3.78</td>\n",
       "      <td>3.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.40</td>\n",
       "      <td>3.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.42</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.25</td>\n",
       "      <td>2.88</td>\n",
       "      <td>3.08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>3.60</td>\n",
       "      <td>3.40</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.86</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.76</td>\n",
       "      <td>3.33</td>\n",
       "      <td>1.5</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.71</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.90</td>\n",
       "      <td>4.33</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>4.00</td>\n",
       "      <td>4.09</td>\n",
       "      <td>4.25</td>\n",
       "      <td>4.33</td>\n",
       "      <td>3.81</td>\n",
       "      <td>4.05</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.78</td>\n",
       "      <td>4.41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.25</td>\n",
       "      <td>4.25</td>\n",
       "      <td>3.87</td>\n",
       "      <td>4.04</td>\n",
       "      <td>4.05</td>\n",
       "      <td>3.86</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>671 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Rating                                                              \\\n",
       "Genre  Action Adventure Animation Children Comedy Crime Documentary Drama   \n",
       "UserID                                                                      \n",
       "1        2.80      2.17      2.00     2.50   2.00  2.50         NaN  2.57   \n",
       "2        3.32      3.75      3.67     3.50   3.09  3.67         NaN  3.62   \n",
       "3        3.47      3.30      2.88     2.70   3.61  3.70        3.67  3.92   \n",
       "4        4.27      4.34      4.71     4.63   4.33  4.16        4.00  4.46   \n",
       "5        4.00      3.82      3.90     3.89   3.98  3.45        3.67  3.81   \n",
       "...       ...       ...       ...      ...    ...   ...         ...   ...   \n",
       "667      3.27      3.57      4.00     4.00   3.88  3.67         NaN  3.71   \n",
       "668      3.50      3.00      3.00     3.00   3.44  4.57         NaN  4.31   \n",
       "669      3.78      3.80       NaN      NaN   3.40  3.75         NaN  3.42   \n",
       "670      3.60      3.40      4.00     4.00   3.86  4.00         NaN  3.76   \n",
       "671      4.00      4.09      4.25     4.33   3.81  4.05        4.00  3.78   \n",
       "\n",
       "                                                                               \\\n",
       "Genre  Fantasy Film-Noir Horror  IMAX Musical Mystery Romance Sci-Fi Thriller   \n",
       "UserID                                                                          \n",
       "1         2.12       NaN   3.00   NaN    3.00     NaN    3.50   2.40     2.83   \n",
       "2         3.40       NaN   3.50  4.00    3.75    4.00    3.59   3.80     3.33   \n",
       "3         3.00       NaN   3.38  2.88    2.50    3.75    3.65   3.14     3.55   \n",
       "4         4.57       5.0   3.94  5.00    4.83    4.09    4.50   4.26     4.02   \n",
       "5         3.75       NaN   3.88  4.00    4.25    3.33    4.08   4.00     3.62   \n",
       "...        ...       ...    ...   ...     ...     ...     ...    ...      ...   \n",
       "667       4.00       NaN    NaN  4.00    3.67    4.00    3.26   4.25     3.50   \n",
       "668       4.00       NaN   5.00   NaN     NaN    3.00    5.00    NaN     4.50   \n",
       "669       3.00       5.0   2.57   NaN     NaN    3.67    3.25   2.88     3.08   \n",
       "670       3.33       1.5   5.00  3.00     NaN    3.75    3.71   3.75     3.90   \n",
       "671       4.41       NaN   4.00  4.00    4.25    4.25    3.87   4.04     4.05   \n",
       "\n",
       "                      \n",
       "Genre    War Western  \n",
       "UserID                \n",
       "1       2.00     3.0  \n",
       "2       3.80     5.0  \n",
       "3       4.17     4.0  \n",
       "4       4.75     3.0  \n",
       "5       4.00     NaN  \n",
       "...      ...     ...  \n",
       "667     3.83     3.0  \n",
       "668     4.50     NaN  \n",
       "669      NaN     5.0  \n",
       "670     4.33     1.0  \n",
       "671     3.86     3.5  \n",
       "\n",
       "[671 rows x 19 columns]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pivoting the dataframe and calculating avarage rating for every genre by every user\n",
    "movie_genres_avg_rating = pd.pivot_table(mdf, columns = ['Genre'], index = ['UserID'], values = ['Rating'], aggfunc = np.mean)\n",
    "movie_genres_avg_rating = movie_genres_avg_rating.round(decimals=2)\n",
    "movie_genres_avg_rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "f8e302a6",
   "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",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"19\" halign=\"left\">Rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Genre</th>\n",
       "      <th>Action</th>\n",
       "      <th>Adventure</th>\n",
       "      <th>Animation</th>\n",
       "      <th>Children</th>\n",
       "      <th>Comedy</th>\n",
       "      <th>Crime</th>\n",
       "      <th>Documentary</th>\n",
       "      <th>Drama</th>\n",
       "      <th>Fantasy</th>\n",
       "      <th>Film-Noir</th>\n",
       "      <th>Horror</th>\n",
       "      <th>IMAX</th>\n",
       "      <th>Musical</th>\n",
       "      <th>Mystery</th>\n",
       "      <th>Romance</th>\n",
       "      <th>Sci-Fi</th>\n",
       "      <th>Thriller</th>\n",
       "      <th>War</th>\n",
       "      <th>Western</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UserID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.80</td>\n",
       "      <td>2.17</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.50</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.84</td>\n",
       "      <td>2.57</td>\n",
       "      <td>2.12</td>\n",
       "      <td>3.91</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.76</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.50</td>\n",
       "      <td>2.40</td>\n",
       "      <td>2.83</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.32</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.09</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.84</td>\n",
       "      <td>3.62</td>\n",
       "      <td>3.40</td>\n",
       "      <td>3.91</td>\n",
       "      <td>3.50</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.59</td>\n",
       "      <td>3.80</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3.80</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.47</td>\n",
       "      <td>3.30</td>\n",
       "      <td>2.88</td>\n",
       "      <td>2.70</td>\n",
       "      <td>3.61</td>\n",
       "      <td>3.70</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.92</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.91</td>\n",
       "      <td>3.38</td>\n",
       "      <td>2.88</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.65</td>\n",
       "      <td>3.14</td>\n",
       "      <td>3.55</td>\n",
       "      <td>4.17</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.27</td>\n",
       "      <td>4.34</td>\n",
       "      <td>4.71</td>\n",
       "      <td>4.63</td>\n",
       "      <td>4.33</td>\n",
       "      <td>4.16</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.46</td>\n",
       "      <td>4.57</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.94</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.83</td>\n",
       "      <td>4.09</td>\n",
       "      <td>4.50</td>\n",
       "      <td>4.26</td>\n",
       "      <td>4.02</td>\n",
       "      <td>4.75</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4.00</td>\n",
       "      <td>3.82</td>\n",
       "      <td>3.90</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.98</td>\n",
       "      <td>3.45</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.81</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.91</td>\n",
       "      <td>3.88</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.25</td>\n",
       "      <td>3.33</td>\n",
       "      <td>4.08</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.62</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.61</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>667</th>\n",
       "      <td>3.27</td>\n",
       "      <td>3.57</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.88</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.84</td>\n",
       "      <td>3.71</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.91</td>\n",
       "      <td>3.44</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.67</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.26</td>\n",
       "      <td>4.25</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.83</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>668</th>\n",
       "      <td>3.50</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.44</td>\n",
       "      <td>4.57</td>\n",
       "      <td>3.84</td>\n",
       "      <td>4.31</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.91</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.76</td>\n",
       "      <td>3.59</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.52</td>\n",
       "      <td>4.50</td>\n",
       "      <td>4.50</td>\n",
       "      <td>3.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>669</th>\n",
       "      <td>3.78</td>\n",
       "      <td>3.80</td>\n",
       "      <td>3.64</td>\n",
       "      <td>3.51</td>\n",
       "      <td>3.40</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.84</td>\n",
       "      <td>3.42</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.57</td>\n",
       "      <td>3.76</td>\n",
       "      <td>3.59</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.25</td>\n",
       "      <td>2.88</td>\n",
       "      <td>3.08</td>\n",
       "      <td>3.89</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>3.60</td>\n",
       "      <td>3.40</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.86</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.84</td>\n",
       "      <td>3.76</td>\n",
       "      <td>3.33</td>\n",
       "      <td>1.50</td>\n",
       "      <td>5.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.59</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.71</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.90</td>\n",
       "      <td>4.33</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>4.00</td>\n",
       "      <td>4.09</td>\n",
       "      <td>4.25</td>\n",
       "      <td>4.33</td>\n",
       "      <td>3.81</td>\n",
       "      <td>4.05</td>\n",
       "      <td>4.00</td>\n",
       "      <td>3.78</td>\n",
       "      <td>4.41</td>\n",
       "      <td>3.91</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.25</td>\n",
       "      <td>4.25</td>\n",
       "      <td>3.87</td>\n",
       "      <td>4.04</td>\n",
       "      <td>4.05</td>\n",
       "      <td>3.86</td>\n",
       "      <td>3.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>671 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Rating                                                              \\\n",
       "Genre  Action Adventure Animation Children Comedy Crime Documentary Drama   \n",
       "UserID                                                                      \n",
       "1        2.80      2.17      2.00     2.50   2.00  2.50        3.84  2.57   \n",
       "2        3.32      3.75      3.67     3.50   3.09  3.67        3.84  3.62   \n",
       "3        3.47      3.30      2.88     2.70   3.61  3.70        3.67  3.92   \n",
       "4        4.27      4.34      4.71     4.63   4.33  4.16        4.00  4.46   \n",
       "5        4.00      3.82      3.90     3.89   3.98  3.45        3.67  3.81   \n",
       "...       ...       ...       ...      ...    ...   ...         ...   ...   \n",
       "667      3.27      3.57      4.00     4.00   3.88  3.67        3.84  3.71   \n",
       "668      3.50      3.00      3.00     3.00   3.44  4.57        3.84  4.31   \n",
       "669      3.78      3.80      3.64     3.51   3.40  3.75        3.84  3.42   \n",
       "670      3.60      3.40      4.00     4.00   3.86  4.00        3.84  3.76   \n",
       "671      4.00      4.09      4.25     4.33   3.81  4.05        4.00  3.78   \n",
       "\n",
       "                                                                               \\\n",
       "Genre  Fantasy Film-Noir Horror  IMAX Musical Mystery Romance Sci-Fi Thriller   \n",
       "UserID                                                                          \n",
       "1         2.12      3.91   3.00  3.76    3.00    3.75    3.50   2.40     2.83   \n",
       "2         3.40      3.91   3.50  4.00    3.75    4.00    3.59   3.80     3.33   \n",
       "3         3.00      3.91   3.38  2.88    2.50    3.75    3.65   3.14     3.55   \n",
       "4         4.57      5.00   3.94  5.00    4.83    4.09    4.50   4.26     4.02   \n",
       "5         3.75      3.91   3.88  4.00    4.25    3.33    4.08   4.00     3.62   \n",
       "...        ...       ...    ...   ...     ...     ...     ...    ...      ...   \n",
       "667       4.00      3.91   3.44  4.00    3.67    4.00    3.26   4.25     3.50   \n",
       "668       4.00      3.91   5.00  3.76    3.59    3.00    5.00   3.52     4.50   \n",
       "669       3.00      5.00   2.57  3.76    3.59    3.67    3.25   2.88     3.08   \n",
       "670       3.33      1.50   5.00  3.00    3.59    3.75    3.71   3.75     3.90   \n",
       "671       4.41      3.91   4.00  4.00    4.25    4.25    3.87   4.04     4.05   \n",
       "\n",
       "                      \n",
       "Genre    War Western  \n",
       "UserID                \n",
       "1       2.00    3.00  \n",
       "2       3.80    5.00  \n",
       "3       4.17    4.00  \n",
       "4       4.75    3.00  \n",
       "5       4.00    3.61  \n",
       "...      ...     ...  \n",
       "667     3.83    3.00  \n",
       "668     4.50    3.61  \n",
       "669     3.89    5.00  \n",
       "670     4.33    1.00  \n",
       "671     3.86    3.50  \n",
       "\n",
       "[671 rows x 19 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_genres_avg_rating = movie_genres_avg_rating.fillna(movie_genres_avg_rating.mean()).round(decimals=2)\n",
    "movie_genres_avg_rating"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78426485",
   "metadata": {},
   "source": [
    "# K-means clustering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "fc93ab75",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\30694\\appdata\\local\\programs\\python\\python38\\lib\\site-packages\\sklearn\\utils\\validation.py:1675: FutureWarning: Feature names only support names that are all strings. Got feature names with dtypes: ['tuple']. An error will be raised in 1.2.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Cluster</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UserID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>667</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>668</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>669</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>670</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>671 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Cluster\n",
       "UserID         \n",
       "1             8\n",
       "2            15\n",
       "3             3\n",
       "4             7\n",
       "5            17\n",
       "...         ...\n",
       "667          12\n",
       "668          13\n",
       "669          15\n",
       "670          18\n",
       "671          17\n",
       "\n",
       "[671 rows x 1 columns]"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kmeans = KMeans(n_clusters=20, init='k-means++', max_iter=300, n_init=10, random_state=0)\n",
    "predictions = kmeans.fit_predict(movie_genres_avg_rating)\n",
    "user_cluster = pd.DataFrame(index=movie_genres_avg_rating.index)\n",
    "user_cluster['Cluster'] = predictions\n",
    "user_cluster.to_csv('user_cluster.csv')\n",
    "user_cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "9d454727",
   "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>MovieID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Genres</th>\n",
       "      <th>UserID</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Cluster</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>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>GoldenEye (1995)</td>\n",
       "      <td>Action|Adventure|Thriller</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>Get Shorty (1995)</td>\n",
       "      <td>Comedy|Crime|Thriller</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31</td>\n",
       "      <td>Dangerous Minds (1995)</td>\n",
       "      <td>Drama</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34</td>\n",
       "      <td>Babe (1995)</td>\n",
       "      <td>Children|Drama</td>\n",
       "      <td>7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18</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>99999</th>\n",
       "      <td>2385</td>\n",
       "      <td>Home Fries (1998)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "      <td>227</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000</th>\n",
       "      <td>2386</td>\n",
       "      <td>Jerry Springer: Ringmaster (1998)</td>\n",
       "      <td>Comedy|Drama</td>\n",
       "      <td>227</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>2387</td>\n",
       "      <td>Very Bad Things (1998)</td>\n",
       "      <td>Comedy|Crime</td>\n",
       "      <td>227</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100002</th>\n",
       "      <td>2389</td>\n",
       "      <td>Psycho (1998)</td>\n",
       "      <td>Crime|Horror|Thriller</td>\n",
       "      <td>227</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>2392</td>\n",
       "      <td>Jack Frost (1998)</td>\n",
       "      <td>Children|Comedy|Drama</td>\n",
       "      <td>227</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100004 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        MovieID                              Title  \\\n",
       "0             1                   Toy Story (1995)   \n",
       "1            10                   GoldenEye (1995)   \n",
       "2            21                  Get Shorty (1995)   \n",
       "3            31             Dangerous Minds (1995)   \n",
       "4            34                        Babe (1995)   \n",
       "...         ...                                ...   \n",
       "99999      2385                  Home Fries (1998)   \n",
       "100000     2386  Jerry Springer: Ringmaster (1998)   \n",
       "100001     2387             Very Bad Things (1998)   \n",
       "100002     2389                      Psycho (1998)   \n",
       "100003     2392                  Jack Frost (1998)   \n",
       "\n",
       "                                             Genres  UserID  Rating  Cluster  \n",
       "0       Adventure|Animation|Children|Comedy|Fantasy       7     3.0       18  \n",
       "1                         Action|Adventure|Thriller       7     3.0       18  \n",
       "2                             Comedy|Crime|Thriller       7     3.0       18  \n",
       "3                                             Drama       7     3.0       18  \n",
       "4                                    Children|Drama       7     4.0       18  \n",
       "...                                             ...     ...     ...      ...  \n",
       "99999                                Comedy|Romance     227     2.0       13  \n",
       "100000                                 Comedy|Drama     227     5.0       13  \n",
       "100001                                 Comedy|Crime     227     5.0       13  \n",
       "100002                        Crime|Horror|Thriller     227     5.0       13  \n",
       "100003                        Children|Comedy|Drama     227     4.0       13  \n",
       "\n",
       "[100004 rows x 6 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_ratings_cluster = pd.merge(movie_ratings, user_cluster, on='UserID')\n",
    "movie_ratings_cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "73f1e726",
   "metadata": {
    "scrolled": false
   },
   "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>MovieID</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Cluster</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31</td>\n",
       "      <td>3.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>2385</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000</th>\n",
       "      <td>2386</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>2387</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100002</th>\n",
       "      <td>2389</td>\n",
       "      <td>5.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>2392</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100004 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        MovieID  Rating  Cluster\n",
       "0             1     3.0       18\n",
       "1            10     3.0       18\n",
       "2            21     3.0       18\n",
       "3            31     3.0       18\n",
       "4            34     4.0       18\n",
       "...         ...     ...      ...\n",
       "99999      2385     2.0       13\n",
       "100000     2386     5.0       13\n",
       "100001     2387     5.0       13\n",
       "100002     2389     5.0       13\n",
       "100003     2392     4.0       13\n",
       "\n",
       "[100004 rows x 3 columns]"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_ratings_cluster = movie_ratings_cluster.drop(columns=['Genres', 'Title', 'UserID'], axis=1)\n",
    "movie_ratings_cluster.\n",
    "movie_ratings_cluster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "3613901e",
   "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",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"20\" halign=\"left\">Rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cluster</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MovieID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.29</td>\n",
       "      <td>4.07</td>\n",
       "      <td>3.88</td>\n",
       "      <td>3.58</td>\n",
       "      <td>1.83</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.64</td>\n",
       "      <td>4.79</td>\n",
       "      <td>2.38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.95</td>\n",
       "      <td>2.08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.03</td>\n",
       "      <td>4.25</td>\n",
       "      <td>4.56</td>\n",
       "      <td>3.86</td>\n",
       "      <td>4.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.83</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.38</td>\n",
       "      <td>3.11</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.75</td>\n",
       "      <td>3.46</td>\n",
       "      <td>3.38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.58</td>\n",
       "      <td>3.93</td>\n",
       "      <td>3.80</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.33</td>\n",
       "      <td>4.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.04</td>\n",
       "      <td>2.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.50</td>\n",
       "      <td>4.40</td>\n",
       "      <td>1.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.38</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.25</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2.70</td>\n",
       "      <td>3.40</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2.80</td>\n",
       "      <td>3.17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.33</td>\n",
       "      <td>2.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.50</td>\n",
       "      <td>4.12</td>\n",
       "      <td>3.50</td>\n",
       "      <td>3.12</td>\n",
       "      <td>3.25</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161944</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162376</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162542</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162672</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163949</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9066 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Rating                                                              \\\n",
       "Cluster     0     1     2     3     4   5     6     7     8   9   10    11   \n",
       "MovieID                                                                      \n",
       "1         3.29  4.07  3.88  3.58  1.83 NaN  3.64  4.79  2.38 NaN NaN  2.50   \n",
       "2         2.83  3.50  3.38  3.11  4.00 NaN  2.00  5.00  2.00 NaN NaN  2.75   \n",
       "3         2.33  4.00   NaN  3.04  2.50 NaN  4.50  4.40  1.50 NaN NaN  3.00   \n",
       "4         1.50   NaN  1.00  2.00  2.00 NaN   NaN  3.00   NaN NaN NaN   NaN   \n",
       "5         2.70  3.40  3.00  2.80  3.17 NaN   NaN  4.33  2.75 NaN NaN  3.00   \n",
       "...        ...   ...   ...   ...   ...  ..   ...   ...   ...  ..  ..   ...   \n",
       "161944     NaN   NaN   NaN   NaN   NaN NaN   NaN  5.00   NaN NaN NaN   NaN   \n",
       "162376     NaN   NaN   NaN  4.50   NaN NaN   NaN   NaN   NaN NaN NaN   NaN   \n",
       "162542     NaN   NaN   NaN   NaN   NaN NaN  5.00   NaN   NaN NaN NaN   NaN   \n",
       "162672     NaN   NaN   NaN   NaN   NaN NaN  3.00   NaN   NaN NaN NaN   NaN   \n",
       "163949     NaN   NaN   NaN  5.00   NaN NaN   NaN   NaN   NaN NaN NaN   NaN   \n",
       "\n",
       "                                                       \n",
       "Cluster    12    13  14    15    16    17    18    19  \n",
       "MovieID                                                \n",
       "1        3.95  2.08 NaN  4.03  4.25  4.56  3.86  4.45  \n",
       "2        3.46  3.38 NaN  3.58  3.93  3.80  3.00  3.67  \n",
       "3        3.00  2.00 NaN  3.75  3.00  3.38  2.00  3.50  \n",
       "4        3.25  1.00 NaN  3.00   NaN  3.00  3.00  3.00  \n",
       "5        3.40   NaN NaN  3.50  4.12  3.50  3.12  3.25  \n",
       "...       ...   ...  ..   ...   ...   ...   ...   ...  \n",
       "161944    NaN   NaN NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "162376    NaN   NaN NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "162542    NaN   NaN NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "162672    NaN   NaN NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "163949    NaN   NaN NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "\n",
       "[9066 rows x 20 columns]"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pivot to calculate the average rating for every movie by every cluster\n",
    "movies_cluster_avg = pd.pivot_table(udf, columns='Cluster', index='MovieID', values=['Rating'], aggfunc=np.mean)\n",
    "movies_cluster_avg = movies_cluster_avg.round(decimals=2)\n",
    "movies_cluster_avg.to_csv('movies_cluster_avg.csv')\n",
    "movies_cluster_avg"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}