examples/movielens/ml-1m/preprocess.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Script to create train/test dataset.\n",
    "### Please download movielens-1M data to script folder.\n",
    "\n",
    "https://grouplens.org/datasets/movielens/1m/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/hwzhang/.pyenv/versions/3.6.6/envs/test_prediction_flow/lib/python3.6/site-packages/ipykernel_launcher.py:1: 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",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "users_df = pd.read_csv('./users.dat', sep='::', names=['userId', 'gender', 'age', 'occupation', 'zipCode'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_df['userId'] = users_df['userId'].astype(np.int)\n",
    "users_df['age'] = users_df['age'].astype(np.int)\n",
    "users_df['occupation'] = users_df['occupation'].astype(np.int)\n",
    "users_df['zipCode'] = users_df['zipCode'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/hwzhang/.pyenv/versions/3.6.6/envs/test_prediction_flow/lib/python3.6/site-packages/ipykernel_launcher.py:1: 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",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "movies_df = pd.read_csv('./movies.dat', sep='::', names=['movieId', 'title', 'genres'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies_df['movieId'] = movies_df['movieId'].astype(np.int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/hwzhang/.pyenv/versions/3.6.6/envs/test_prediction_flow/lib/python3.6/site-packages/ipykernel_launcher.py:1: 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",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "ratings_df = pd.read_csv('./ratings.dat', sep='::', names=['userId', 'movieId', 'ratting', 'timestamp'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings_df['userId'] = ratings_df['userId'].astype(np.int)\n",
    "ratings_df['movieId'] = ratings_df['movieId'].astype(np.int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>occupation</th>\n",
       "      <th>zipCode</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": [
       "   userId gender  age  occupation zipCode\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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>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": [
       "   movieId                               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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>ratting</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": [
       "   userId  movieId  ratting  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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6040"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# there are 6040 users\n",
    "\n",
    "users_df.userId.unique().shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3883"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# there are 3883 movies\n",
    "\n",
    "movies_df.movieId.unique().shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Users have rated at least 20 movies.\n",
    "\n",
    "ratings_df.groupby(by='userId', as_index=False).movieId.count().movieId.min()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# seperate train/test by userIds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "trainUserIds, testUserIds = train_test_split(\n",
    "    users_df.userId.unique(), test_size=0.2, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratings_df = ratings_df[ratings_df.userId.isin(trainUserIds)]\n",
    "test_ratings_df = ratings_df[ratings_df.userId.isin(testUserIds)]\n",
    "\n",
    "train_ratings_df = train_ratings_df.sort_values(\n",
    "    by=['userId', 'timestamp'], ascending=[True, True])\n",
    "test_ratings_df = test_ratings_df.sort_values(\n",
    "    by=['userId', 'timestamp'], ascending=[True, True])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# prepare history movieIds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def mark_last_timestamp(df):\n",
    "    last = df[['userId', 'movieId']].groupby(\n",
    "        by='userId', as_index=False).tail(1).copy()\n",
    "    last['last'] = 1\n",
    "    df = pd.merge(\n",
    "        df, last, how='left',\n",
    "        on=['userId', 'movieId'])\n",
    "    df.loc[~df['last'].isnull(), 'last'] = 1\n",
    "    df.loc[df['last'].isnull(), 'last'] = 0\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratings_df = mark_last_timestamp(train_ratings_df)\n",
    "test_ratings_df = mark_last_timestamp(test_ratings_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "candidate_movie_ids = movies_df.movieId.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# slow implement\n",
    "def neg_sampling(candidates, filters, length):\n",
    "    max_len = len(candidates)\n",
    "    \n",
    "    res = []\n",
    "    for i in range(length):\n",
    "        while(1):\n",
    "            c = candidates[np.random.randint(0, max_len)]\n",
    "            if c not in filters:\n",
    "                res.append(str(c))\n",
    "                filters.add(c)\n",
    "                break\n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_hist_movie_ids(df, max_len=10):\n",
    "    hist_movie_ids = list()\n",
    "    neg_hist_movie_ids = list()\n",
    "    for _, group in df.groupby(by='userId'):\n",
    "        tmp_hist_movie_ids = list()\n",
    "        for _, row in group.iterrows():\n",
    "            # keep high rated movies\n",
    "            if row['ratting'] >= 4 and row['last'] == 0:\n",
    "                tmp_hist_movie_ids.append(str(int(row['movieId'])))\n",
    "        # keep latest high rated movies\n",
    "        tmp_hist_movie_ids.reverse()\n",
    "        tmp_hist_movie_ids = tmp_hist_movie_ids[:max_len]\n",
    "        # revert to timestamp order\n",
    "        tmp_hist_movie_ids.reverse()\n",
    "        tmp_neg_hist_movie_ids = neg_sampling(\n",
    "            candidate_movie_ids, set(hist_movie_ids), len(tmp_hist_movie_ids))\n",
    "        hist_movie_ids.append('|'.join(tmp_hist_movie_ids))\n",
    "        neg_hist_movie_ids.append('|'.join(tmp_neg_hist_movie_ids))\n",
    "    return hist_movie_ids, neg_hist_movie_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_hist_movie_ids, train_neg_hist_movie_ids = get_hist_movie_ids(train_ratings_df)\n",
    "test_hist_movie_ids, test_neg_hist_movie_ids = get_hist_movie_ids(test_ratings_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratings_df = train_ratings_df[train_ratings_df['last'] == 1]\n",
    "train_ratings_df['histHighRatedMovieIds'] = train_hist_movie_ids\n",
    "train_ratings_df['negHistMovieIds'] = train_neg_hist_movie_ids\n",
    "\n",
    "test_ratings_df = test_ratings_df[test_ratings_df['last'] == 1]\n",
    "test_ratings_df['histHighRatedMovieIds'] = test_hist_movie_ids\n",
    "test_ratings_df['negHistMovieIds'] = test_neg_hist_movie_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>ratting</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>last</th>\n",
       "      <th>histHighRatedMovieIds</th>\n",
       "      <th>negHistMovieIds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>1</td>\n",
       "      <td>48</td>\n",
       "      <td>5</td>\n",
       "      <td>978824351</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1545|527|595|588|1|2355|2294|783|1566|1907</td>\n",
       "      <td>393|2562|3289|3442|2808|2259|295|1190|3321|3470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>2</td>\n",
       "      <td>1917</td>\n",
       "      <td>3</td>\n",
       "      <td>978300174</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3418|349|1527|2353|1370|648|368|736|2002|1544</td>\n",
       "      <td>2117|2560|3480|3678|2798|1222|3872|3503|3913|3494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>3</td>\n",
       "      <td>2081</td>\n",
       "      <td>4</td>\n",
       "      <td>978298504</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2735|1136|3421|1394|1304|1079|1259|2355|3552|104</td>\n",
       "      <td>2481|3945|3629|3056|1756|3725|1519|1425|307|37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>4</td>\n",
       "      <td>1954</td>\n",
       "      <td>5</td>\n",
       "      <td>978294282</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2366|1201|2692|2947|1214|3418|3702|1240|2951|1036</td>\n",
       "      <td>1719|2853|3132|1164|1097|931|1558|1363|492|3406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>5</td>\n",
       "      <td>288</td>\n",
       "      <td>2</td>\n",
       "      <td>978246585</td>\n",
       "      <td>1.0</td>\n",
       "      <td>515|1715|506|377|1580|1921|1897|2427|551|2029</td>\n",
       "      <td>1927|940|2153|865|957|3219|1834|2602|3770|3843</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     userId  movieId  ratting  timestamp  last  \\\n",
       "52        1       48        5  978824351   1.0   \n",
       "181       2     1917        3  978300174   1.0   \n",
       "232       3     2081        4  978298504   1.0   \n",
       "253       4     1954        5  978294282   1.0   \n",
       "451       5      288        2  978246585   1.0   \n",
       "\n",
       "                                 histHighRatedMovieIds  \\\n",
       "52          1545|527|595|588|1|2355|2294|783|1566|1907   \n",
       "181      3418|349|1527|2353|1370|648|368|736|2002|1544   \n",
       "232   2735|1136|3421|1394|1304|1079|1259|2355|3552|104   \n",
       "253  2366|1201|2692|2947|1214|3418|3702|1240|2951|1036   \n",
       "451      515|1715|506|377|1580|1921|1897|2427|551|2029   \n",
       "\n",
       "                                       negHistMovieIds  \n",
       "52     393|2562|3289|3442|2808|2259|295|1190|3321|3470  \n",
       "181  2117|2560|3480|3678|2798|1222|3872|3503|3913|3494  \n",
       "232     2481|3945|3629|3056|1756|3725|1519|1425|307|37  \n",
       "253    1719|2853|3132|1164|1097|931|1558|1363|492|3406  \n",
       "451     1927|940|2153|865|957|3219|1834|2602|3770|3843  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_ratings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>ratting</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>last</th>\n",
       "      <th>histHighRatedMovieIds</th>\n",
       "      <th>negHistMovieIds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>9</td>\n",
       "      <td>2294</td>\n",
       "      <td>4</td>\n",
       "      <td>978226678</td>\n",
       "      <td>1.0</td>\n",
       "      <td>590|480|2302|2268|529|349|524|16|1653|2278</td>\n",
       "      <td>876|1953|2644|3540|2794|90|3291|406|2282|2406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>15</td>\n",
       "      <td>3510</td>\n",
       "      <td>5</td>\n",
       "      <td>978361393</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1892|648|1422|1597|3489|1196|260|1210|3461|2115</td>\n",
       "      <td>322|135|1324|3928|2808|3220|2992|3937|652|3671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>16</td>\n",
       "      <td>2701</td>\n",
       "      <td>2</td>\n",
       "      <td>978174795</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2683|2699|2355|2975|2369|3175|2761|2581|2394|2724</td>\n",
       "      <td>3128|1830|3281|163|1334|585|3580|1776|1392|774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>18</td>\n",
       "      <td>1683</td>\n",
       "      <td>5</td>\n",
       "      <td>978157434</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1643|1721|475|2573|249|3723|261|3448|838|26</td>\n",
       "      <td>1986|972|2586|2846|1855|2369|568|2144|3329|2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>782</th>\n",
       "      <td>24</td>\n",
       "      <td>2657</td>\n",
       "      <td>4</td>\n",
       "      <td>986157459</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1073|3699|2640|592|858|1193|1635|425|2757|1959</td>\n",
       "      <td>3917|1764|990|2042|1861|3097|3746|2624|3829|3421</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     userId  movieId  ratting  timestamp  last  \\\n",
       "105       9     2294        4  978226678   1.0   \n",
       "306      15     3510        5  978361393   1.0   \n",
       "341      16     2701        2  978174795   1.0   \n",
       "646      18     1683        5  978157434   1.0   \n",
       "782      24     2657        4  986157459   1.0   \n",
       "\n",
       "                                 histHighRatedMovieIds  \\\n",
       "105         590|480|2302|2268|529|349|524|16|1653|2278   \n",
       "306    1892|648|1422|1597|3489|1196|260|1210|3461|2115   \n",
       "341  2683|2699|2355|2975|2369|3175|2761|2581|2394|2724   \n",
       "646        1643|1721|475|2573|249|3723|261|3448|838|26   \n",
       "782     1073|3699|2640|592|858|1193|1635|425|2757|1959   \n",
       "\n",
       "                                      negHistMovieIds  \n",
       "105     876|1953|2644|3540|2794|90|3291|406|2282|2406  \n",
       "306    322|135|1324|3928|2808|3220|2992|3937|652|3671  \n",
       "341    3128|1830|3281|163|1334|585|3580|1776|1392|774  \n",
       "646   1986|972|2586|2846|1855|2369|568|2144|3329|2000  \n",
       "782  3917|1764|990|2042|1861|3097|3746|2624|3829|3421  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_ratings_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# merge with other features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratings_df = pd.merge(train_ratings_df, users_df, how='inner', on='userId')\n",
    "test_ratings_df = pd.merge(test_ratings_df, users_df, how='inner', on='userId')\n",
    "train_ratings_df = pd.merge(train_ratings_df, movies_df, how='inner', on='movieId')\n",
    "test_ratings_df = pd.merge(test_ratings_df, movies_df, how='inner', on='movieId')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# create label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratings_df['label'] = 0\n",
    "train_ratings_df.loc[train_ratings_df['ratting'] >= 4, 'label'] = 1\n",
    "test_ratings_df['label'] = 0\n",
    "test_ratings_df.loc[test_ratings_df['ratting'] >= 4, 'label'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratings_df.to_csv('./train.csv', index=False)\n",
    "test_ratings_df.to_csv('./test.csv', index=False)"
   ]
  },
  {
   "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>userId</th>\n",
       "      <th>movieId</th>\n",
       "      <th>ratting</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>last</th>\n",
       "      <th>histHighRatedMovieIds</th>\n",
       "      <th>negHistMovieIds</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>occupation</th>\n",
       "      <th>zipCode</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>48</td>\n",
       "      <td>5</td>\n",
       "      <td>978824351</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1545|527|595|588|1|2355|2294|783|1566|1907</td>\n",
       "      <td>393|2562|3289|3442|2808|2259|295|1190|3321|3470</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",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2030</td>\n",
       "      <td>48</td>\n",
       "      <td>3</td>\n",
       "      <td>977809337</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3928|1934|2099|3345|1013|1101|3524|3429|3751|2138</td>\n",
       "      <td>3142|1743|525|1746|3323|935|2183|3556|3343|1542</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>4</td>\n",
       "      <td>77345</td>\n",
       "      <td>Pocahontas (1995)</td>\n",
       "      <td>Animation|Children's|Musical|Romance</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4877</td>\n",
       "      <td>48</td>\n",
       "      <td>2</td>\n",
       "      <td>962767153</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2078|1032|2081|616|364|2033|3034|2394|3745|3615</td>\n",
       "      <td>2354|747|3478|2638|311|1204|3370|807|29|1380</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>4</td>\n",
       "      <td>94703</td>\n",
       "      <td>Pocahontas (1995)</td>\n",
       "      <td>Animation|Children's|Musical|Romance</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>1917</td>\n",
       "      <td>3</td>\n",
       "      <td>978300174</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3418|349|1527|2353|1370|648|368|736|2002|1544</td>\n",
       "      <td>2117|2560|3480|3678|2798|1222|3872|3503|3913|3494</td>\n",
       "      <td>M</td>\n",
       "      <td>56</td>\n",
       "      <td>16</td>\n",
       "      <td>70072</td>\n",
       "      <td>Armageddon (1998)</td>\n",
       "      <td>Action|Adventure|Sci-Fi|Thriller</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5122</td>\n",
       "      <td>1917</td>\n",
       "      <td>5</td>\n",
       "      <td>962213528</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3078|2858|2907|1584|2571|32|788|1573|2428|1580</td>\n",
       "      <td>1860|194|742|2379|1966|3397|3195|678|2240|2196</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>20009</td>\n",
       "      <td>Armageddon (1998)</td>\n",
       "      <td>Action|Adventure|Sci-Fi|Thriller</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  movieId  ratting  timestamp  last  \\\n",
       "0       1       48        5  978824351   1.0   \n",
       "1    2030       48        3  977809337   1.0   \n",
       "2    4877       48        2  962767153   1.0   \n",
       "3       2     1917        3  978300174   1.0   \n",
       "4    5122     1917        5  962213528   1.0   \n",
       "\n",
       "                               histHighRatedMovieIds  \\\n",
       "0         1545|527|595|588|1|2355|2294|783|1566|1907   \n",
       "1  3928|1934|2099|3345|1013|1101|3524|3429|3751|2138   \n",
       "2    2078|1032|2081|616|364|2033|3034|2394|3745|3615   \n",
       "3      3418|349|1527|2353|1370|648|368|736|2002|1544   \n",
       "4     3078|2858|2907|1584|2571|32|788|1573|2428|1580   \n",
       "\n",
       "                                     negHistMovieIds gender  age  occupation  \\\n",
       "0    393|2562|3289|3442|2808|2259|295|1190|3321|3470      F    1          10   \n",
       "1    3142|1743|525|1746|3323|935|2183|3556|3343|1542      M   25           4   \n",
       "2       2354|747|3478|2638|311|1204|3370|807|29|1380      M   25           4   \n",
       "3  2117|2560|3480|3678|2798|1222|3872|3503|3913|3494      M   56          16   \n",
       "4     1860|194|742|2379|1966|3397|3195|678|2240|2196      M   25           0   \n",
       "\n",
       "  zipCode              title                                genres  label  \n",
       "0   48067  Pocahontas (1995)  Animation|Children's|Musical|Romance      1  \n",
       "1   77345  Pocahontas (1995)  Animation|Children's|Musical|Romance      0  \n",
       "2   94703  Pocahontas (1995)  Animation|Children's|Musical|Romance      0  \n",
       "3   70072  Armageddon (1998)      Action|Adventure|Sci-Fi|Thriller      0  \n",
       "4   20009  Armageddon (1998)      Action|Adventure|Sci-Fi|Thriller      1  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_ratings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>ratting</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>last</th>\n",
       "      <th>histHighRatedMovieIds</th>\n",
       "      <th>negHistMovieIds</th>\n",
       "      <th>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>occupation</th>\n",
       "      <th>zipCode</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2294</td>\n",
       "      <td>4</td>\n",
       "      <td>978226678</td>\n",
       "      <td>1.0</td>\n",
       "      <td>590|480|2302|2268|529|349|524|16|1653|2278</td>\n",
       "      <td>876|1953|2644|3540|2794|90|3291|406|2282|2406</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>17</td>\n",
       "      <td>61614</td>\n",
       "      <td>Antz (1998)</td>\n",
       "      <td>Animation|Children's</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "      <td>3510</td>\n",
       "      <td>5</td>\n",
       "      <td>978361393</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1892|648|1422|1597|3489|1196|260|1210|3461|2115</td>\n",
       "      <td>322|135|1324|3928|2808|3220|2992|3937|652|3671</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>7</td>\n",
       "      <td>22903</td>\n",
       "      <td>Frequency (2000)</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2165</td>\n",
       "      <td>3510</td>\n",
       "      <td>5</td>\n",
       "      <td>974982139</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1095|2959|2416|3552|16|1266|3210|104|3147|3148</td>\n",
       "      <td>1936|1248|2241|985|3097|2282|3550|2690|3731|718</td>\n",
       "      <td>M</td>\n",
       "      <td>25</td>\n",
       "      <td>7</td>\n",
       "      <td>32836</td>\n",
       "      <td>Frequency (2000)</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2319</td>\n",
       "      <td>3510</td>\n",
       "      <td>4</td>\n",
       "      <td>974476547</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2987|1240|2243|1302|2716|1961|1096|3408|3481|3148</td>\n",
       "      <td>392|2782|1615|3720|781|1511|3225|2273|2655|3798</td>\n",
       "      <td>M</td>\n",
       "      <td>35</td>\n",
       "      <td>7</td>\n",
       "      <td>48104</td>\n",
       "      <td>Frequency (2000)</td>\n",
       "      <td>Drama|Thriller</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>2701</td>\n",
       "      <td>2</td>\n",
       "      <td>978174795</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2683|2699|2355|2975|2369|3175|2761|2581|2394|2724</td>\n",
       "      <td>3128|1830|3281|163|1334|585|3580|1776|1392|774</td>\n",
       "      <td>F</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>20670</td>\n",
       "      <td>Wild Wild West (1999)</td>\n",
       "      <td>Action|Sci-Fi|Western</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  movieId  ratting  timestamp  last  \\\n",
       "0       9     2294        4  978226678   1.0   \n",
       "1      15     3510        5  978361393   1.0   \n",
       "2    2165     3510        5  974982139   1.0   \n",
       "3    2319     3510        4  974476547   1.0   \n",
       "4      16     2701        2  978174795   1.0   \n",
       "\n",
       "                               histHighRatedMovieIds  \\\n",
       "0         590|480|2302|2268|529|349|524|16|1653|2278   \n",
       "1    1892|648|1422|1597|3489|1196|260|1210|3461|2115   \n",
       "2     1095|2959|2416|3552|16|1266|3210|104|3147|3148   \n",
       "3  2987|1240|2243|1302|2716|1961|1096|3408|3481|3148   \n",
       "4  2683|2699|2355|2975|2369|3175|2761|2581|2394|2724   \n",
       "\n",
       "                                   negHistMovieIds gender  age  occupation  \\\n",
       "0    876|1953|2644|3540|2794|90|3291|406|2282|2406      M   25          17   \n",
       "1   322|135|1324|3928|2808|3220|2992|3937|652|3671      M   25           7   \n",
       "2  1936|1248|2241|985|3097|2282|3550|2690|3731|718      M   25           7   \n",
       "3  392|2782|1615|3720|781|1511|3225|2273|2655|3798      M   35           7   \n",
       "4   3128|1830|3281|163|1334|585|3580|1776|1392|774      F   35           0   \n",
       "\n",
       "  zipCode                  title                 genres  label  \n",
       "0   61614            Antz (1998)   Animation|Children's      1  \n",
       "1   22903       Frequency (2000)         Drama|Thriller      1  \n",
       "2   32836       Frequency (2000)         Drama|Thriller      1  \n",
       "3   48104       Frequency (2000)         Drama|Thriller      1  \n",
       "4   20670  Wild Wild West (1999)  Action|Sci-Fi|Western      0  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_ratings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}