data_manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data manipulation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext lab_black"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset 1 shape: (24058263, 2)\n",
      "-Dataset examples-\n",
      "          Cust_Id  Rating\n",
      "0              1:     NaN\n",
      "5000000   2560324     4.0\n",
      "10000000  2271935     2.0\n",
      "15000000  1921803     2.0\n",
      "20000000  1933327     3.0\n"
     ]
    }
   ],
   "source": [
    "# load on one dataset for spead Skip date\n",
    "df = pd.read_csv(\n",
    "    \"Data/combined_data_1.txt\", header=None, names=[\"Cust_Id\", \"Rating\"], usecols=[0, 1]\n",
    ")\n",
    "\n",
    "df[\"Rating\"] = df[\"Rating\"].astype(float)\n",
    "\n",
    "print(\"Dataset 1 shape: {}\".format(df.shape))\n",
    "print(\"-Dataset examples-\")\n",
    "print(df.iloc[::5000000, :])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data cleaning "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Movie numpy: [1.000e+00 1.000e+00 1.000e+00 ... 4.499e+03 4.499e+03 4.499e+03]\n",
      "Length: 24053764\n"
     ]
    }
   ],
   "source": [
    "df_nan = pd.DataFrame(pd.isnull(df.Rating))\n",
    "df_nan = df_nan[df_nan[\"Rating\"] == True]\n",
    "df_nan = df_nan.reset_index()\n",
    "\n",
    "movie_np = []\n",
    "movie_id = 1\n",
    "\n",
    "for i, j in zip(df_nan[\"index\"][1:], df_nan[\"index\"][:-1]):\n",
    "    # numpy approach\n",
    "    temp = np.full((1, i - j - 1), movie_id)\n",
    "    movie_np = np.append(movie_np, temp)\n",
    "    movie_id += 1\n",
    "\n",
    "# Account for last record and corresponding length\n",
    "# numpy approach\n",
    "last_record = np.full((1, len(df) - df_nan.iloc[-1, 0] - 1), movie_id)\n",
    "movie_np = np.append(movie_np, last_record)\n",
    "\n",
    "print(\"Movie numpy: {}\".format(movie_np))\n",
    "print(\"Length: {}\".format(len(movie_np)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-Dataset examples-\n",
      "          Cust_Id  Rating  Movie_Id\n",
      "1         1488844     3.0         1\n",
      "5000996    501954     2.0       996\n",
      "10001962   404654     5.0      1962\n",
      "15002876   886608     2.0      2876\n",
      "20003825  1193835     2.0      3825\n"
     ]
    }
   ],
   "source": [
    "# remove those Movie ID rows\n",
    "df = df[pd.notnull(df[\"Rating\"])]\n",
    "\n",
    "df[\"Movie_Id\"] = movie_np.astype(int)\n",
    "df[\"Cust_Id\"] = df[\"Cust_Id\"].astype(int)\n",
    "print(\"-Dataset examples-\")\n",
    "print(df.iloc[::5000000, :])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data slicing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Movie minimum times of review: 1799.0\n",
      "Customer minimum times of review: 52.0\n"
     ]
    }
   ],
   "source": [
    "f = [\"count\", \"mean\"]\n",
    "\n",
    "df_movie_summary = df.groupby(\"Movie_Id\")[\"Rating\"].agg(f)\n",
    "df_movie_summary.index = df_movie_summary.index.map(int)\n",
    "movie_benchmark = round(df_movie_summary[\"count\"].quantile(0.7), 0)\n",
    "drop_movie_list = df_movie_summary[df_movie_summary[\"count\"] < movie_benchmark].index\n",
    "\n",
    "print(\"Movie minimum times of review: {}\".format(movie_benchmark))\n",
    "\n",
    "df_cust_summary = df.groupby(\"Cust_Id\")[\"Rating\"].agg(f)\n",
    "df_cust_summary.index = df_cust_summary.index.map(int)\n",
    "cust_benchmark = round(df_cust_summary[\"count\"].quantile(0.7), 0)\n",
    "drop_cust_list = df_cust_summary[df_cust_summary[\"count\"] < cust_benchmark].index\n",
    "\n",
    "print(\"Customer minimum times of review: {}\".format(cust_benchmark))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Shape: (24053764, 3)\n",
      "After Trim Shape: (17337458, 3)\n",
      "-Data Examples-\n",
      "          Cust_Id  Rating  Movie_Id\n",
      "696        712664     5.0         3\n",
      "6932490   1299309     5.0      1384\n",
      "13860273   400155     3.0      2660\n",
      "20766530   466962     4.0      3923\n"
     ]
    }
   ],
   "source": [
    "print(\"Original Shape: {}\".format(df.shape))\n",
    "df = df[~df[\"Movie_Id\"].isin(drop_movie_list)]\n",
    "df = df[~df[\"Cust_Id\"].isin(drop_cust_list)]\n",
    "print(\"After Trim Shape: {}\".format(df.shape))\n",
    "print(\"-Data Examples-\")\n",
    "print(df.iloc[::5000000, :])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(143458, 1350)\n"
     ]
    }
   ],
   "source": [
    "df_p = pd.pivot_table(df, values=\"Rating\", index=\"Cust_Id\", columns=\"Movie_Id\")\n",
    "\n",
    "print(df_p.shape)"
   ]
  },
  {
   "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>Movie_Id</th>\n",
       "      <th>3</th>\n",
       "      <th>8</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>26</th>\n",
       "      <th>28</th>\n",
       "      <th>30</th>\n",
       "      <th>32</th>\n",
       "      <th>33</th>\n",
       "      <th>...</th>\n",
       "      <th>4472</th>\n",
       "      <th>4474</th>\n",
       "      <th>4478</th>\n",
       "      <th>4479</th>\n",
       "      <th>4485</th>\n",
       "      <th>4488</th>\n",
       "      <th>4490</th>\n",
       "      <th>4492</th>\n",
       "      <th>4493</th>\n",
       "      <th>4496</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cust_Id</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</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>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</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>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</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>79</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>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>134</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.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>169</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>183</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>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</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>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</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>199</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.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>10 rows × 1350 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Movie_Id  3     8     16    17    18    26    28    30    32    33    ...  \\\n",
       "Cust_Id                                                               ...   \n",
       "6          NaN   NaN   NaN   NaN   NaN   NaN   NaN   3.0   NaN   NaN  ...   \n",
       "7          NaN   5.0   NaN   NaN   NaN   NaN   4.0   5.0   NaN   NaN  ...   \n",
       "79         NaN   NaN   NaN   NaN   NaN   NaN   NaN   3.0   NaN   NaN  ...   \n",
       "97         NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "134        NaN   NaN   NaN   NaN   NaN   NaN   5.0   NaN   NaN   NaN  ...   \n",
       "169        NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "183        NaN   NaN   NaN   NaN   NaN   NaN   NaN   4.0   NaN   NaN  ...   \n",
       "188        NaN   NaN   NaN   NaN   NaN   NaN   NaN   3.0   NaN   NaN  ...   \n",
       "195        NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...   \n",
       "199        NaN   NaN   NaN   NaN   NaN   NaN   NaN   5.0   NaN   NaN  ...   \n",
       "\n",
       "Movie_Id  4472  4474  4478  4479  4485  4488  4490  4492  4493  4496  \n",
       "Cust_Id                                                               \n",
       "6          3.0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "7          3.0   NaN   NaN   5.0   NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "79         4.0   NaN   NaN   NaN   NaN   NaN   4.0   NaN   NaN   NaN  \n",
       "97         NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "134        NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "169        NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "183        NaN   NaN   NaN   NaN   NaN   NaN   3.0   NaN   NaN   NaN  \n",
       "188        NaN   NaN   NaN   NaN   NaN   NaN   3.0   3.0   NaN   NaN  \n",
       "195        4.0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "199        NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  \n",
       "\n",
       "[10 rows x 1350 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_p.head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}