YooChoose/01-Data-Preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download data\n",
    "\n",
    "Run the following code:\n",
    "\n",
    "```\n",
    "$ bash\n",
    "$ pip install git+https://github.com/maciejkula/spotlight.git\n",
    "$ apt install p7zip\n",
    "$ cd /tf/notebooks/data\n",
    "$ mkdir yoochoose\n",
    "$ curl -Lo yoochoose-data.7z https://s3-eu-west-1.amazonaws.com/yc-rdata/yoochoose-data.7z\n",
    "$ 7z x yoochoose-data.7z\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clicks dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 19.4 s, sys: 2.98 s, total: 22.4 s\n",
      "Wall time: 23 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_clicks = pd.read_csv(\"/tf/notebooks/data/yoochoose/\" + 'yoochoose-clicks.dat', sep=',', header=None,\n",
    "                  dtype={0:np.int32, 1:str, 2:np.int64, 3:str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:51:09.277Z</td>\n",
       "      <td>214536502</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:54:09.868Z</td>\n",
       "      <td>214536500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:54:46.998Z</td>\n",
       "      <td>214536506</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:57:00.306Z</td>\n",
       "      <td>214577561</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-07T13:56:37.614Z</td>\n",
       "      <td>214662742</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0                         1          2  3\n",
       "0  1  2014-04-07T10:51:09.277Z  214536502  0\n",
       "1  1  2014-04-07T10:54:09.868Z  214536500  0\n",
       "2  1  2014-04-07T10:54:46.998Z  214536506  0\n",
       "3  1  2014-04-07T10:57:00.306Z  214577561  0\n",
       "4  2  2014-04-07T13:56:37.614Z  214662742  0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clicks.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Managing the category type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The categories can be S (for promotion), 0 (when unknown), \n",
    "# a number between 1-12 when it came from a category on the page\n",
    "# or a 8-10 digit number that represents a brand\n",
    "\n",
    "def assign_cat(x):\n",
    "    if x == \"S\":\n",
    "        return \"PROMOTION\"\n",
    "    elif np.int(x) == 0:\n",
    "        return \"NONE\"\n",
    "    elif np.int(x) < 13:\n",
    "        return \"CATEGORY\"\n",
    "    else:\n",
    "        return \"BRAND\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assign the caegory to the clicks dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 8.46 s, sys: 111 ms, total: 8.57 s\n",
      "Wall time: 8.57 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_clicks[\"Item_Type\"] = df_clicks.iloc[:,3].map(assign_cat)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assign column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clicks.columns =  [\"SessionId\", \"TimeStr\", \"ItemId\", \"Item_Type_Code\", \"ItemType\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can drop item type code now\n",
    "df_clicks.drop([\"Item_Type_Code\"], inplace=True, axis=1)"
   ]
  },
  {
   "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>SessionId</th>\n",
       "      <th>TimeStr</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:51:09.277Z</td>\n",
       "      <td>214536502</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:54:09.868Z</td>\n",
       "      <td>214536500</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:54:46.998Z</td>\n",
       "      <td>214536506</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-07T10:57:00.306Z</td>\n",
       "      <td>214577561</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2014-04-07T13:56:37.614Z</td>\n",
       "      <td>214662742</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SessionId                   TimeStr     ItemId ItemType\n",
       "0          1  2014-04-07T10:51:09.277Z  214536502     NONE\n",
       "1          1  2014-04-07T10:54:09.868Z  214536500     NONE\n",
       "2          1  2014-04-07T10:54:46.998Z  214536506     NONE\n",
       "3          1  2014-04-07T10:57:00.306Z  214577561     NONE\n",
       "4          2  2014-04-07T13:56:37.614Z  214662742     NONE"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clicks.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploring the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique sessions: 9249729\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of unique sessions:\", len(df_clicks.SessionId.unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7fc59b928208>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZAAAAEGCAYAAABLgMOSAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8QZhcZAAASuElEQVR4nO3dfZBddX3H8fenSbFq1SBkGJpAE2u0TTtjxZXQ8WEc6UCg1tDWcRCmpJaRWtFqH6bFOlOdtn9AH6QyQ+lQoYIjokUdMq02UnT68AeRDc8BkRUVkgmQEh5s7aDRb/+4v9DLzi7Z/W323o15v2bu7Dnf8zv399tz797PnnPPPTdVhSRJ8/Uj4x6AJOnQZIBIkroYIJKkLgaIJKmLASJJ6rJ83AMYlaOPPrrWrFkz7mFI0iFl+/bt/1VVK2dadtgEyJo1a5icnBz3MCTpkJLkW7Mt8xCWJKmLASJJ6mKASJK6GCCSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqcth80n0hbhm2wMLWv+sDccfpJFI0tLhHogkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6GCCSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqYsBIknqYoBIkroYIJKkLnMKkCS/m2RHkruSfDLJjyVZm2Rbkqkkn0pyRGv7nDY/1ZavGbqf97f6vUlOHapvbLWpJBcM1efdhyRpNA4YIElWAb8DTFTVzwHLgDOBi4CLq+qlwGPAuW2Vc4HHWv3i1o4k69t6PwtsBP42ybIky4BLgdOA9cDbWlvm24ckaXTmeghrOfDcJMuB5wG7gTcC17XlVwFntOlNbZ62/OQkafVrq+qpqvoGMAWc2G5TVXV/VX0XuBbY1NaZbx+SpBE5YIBU1S7gr4AHGATHE8B24PGq2tea7QRWtelVwINt3X2t/VHD9WnrzFY/qqOPZ0hyXpLJJJN79uw50K8qSZqHuRzCOpLBf/xrgZ8Ans/gENSSV1WXV9VEVU2sXLly3MORpB8qczmE9YvAN6pqT1V9D/gs8BpgRTukBbAa2NWmdwHHAbTlLwIeHa5PW2e2+qMdfUiSRmQuAfIAcFKS57X3GU4G7ga+DLyltdkMXN+mt7R52vIvVVW1+pntDKq1wDrgK8DNwLp2xtURDN5o39LWmW8fkqQRWX6gBlW1Lcl1wC3APuBW4HLgn4Frk/x5q13RVrkC+HiSKWAvg0CgqnYk+TSD8NkHnF9V3wdI8m5gK4MzvK6sqh3tvv5oPn1IkkYnh8s/7hMTEzU5Odm17jXbHlhQ32dtOH5B60vSuCTZXlUTMy3zk+iSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6GCCSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6GCCSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6GCCSpC4GiCSpy5wCJMmKJNcl+WqSe5L8QpIXJ7khyX3t55GtbZJckmQqyR1JThi6n82t/X1JNg/VX5XkzrbOJUnS6vPuQ5I0GnPdA/kI8C9V9dPAK4B7gAuAG6tqHXBjmwc4DVjXbucBl8EgDIAPAhuAE4EP7g+E1uYdQ+ttbPV59SFJGp0DBkiSFwGvB64AqKrvVtXjwCbgqtbsKuCMNr0JuLoGbgJWJDkWOBW4oar2VtVjwA3AxrbshVV1U1UVcPW0+5pPH5KkEZnLHshaYA/wD0luTfLRJM8Hjqmq3a3NQ8AxbXoV8ODQ+jtb7dnqO2eo09HHMyQ5L8lkksk9e/bM4VeVJM3VXAJkOXACcFlVvRL4H/7/UBIAbc+hDv7wFtZHVV1eVRNVNbFy5cpFGpkkHZ7mEiA7gZ1Vta3NX8cgUB7ef9io/XykLd8FHDe0/upWe7b66hnqdPQhSRqRAwZIVT0EPJjk5a10MnA3sAXYfybVZuD6Nr0FOKedKXUS8EQ7DLUVOCXJke3N81OArW3Zk0lOamdfnTPtvubThyRpRJbPsd17gE8kOQK4H3g7g/D5dJJzgW8Bb21tPw+cDkwB32ltqaq9Sf4MuLm1+9Oq2tum3wV8DHgu8IV2A7hwPn1IkkYng7cWfvhNTEzU5ORk17rXbHtgQX2fteH4Ba0vSeOSZHtVTcy0zE+iS5K6GCCSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6GCCSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6GCCSpC4GiCSpiwEiSepigEiSuhggkqQuBogkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6zDlAkixLcmuSf2rza5NsSzKV5FNJjmj157T5qbZ8zdB9vL/V701y6lB9Y6tNJblgqD7vPiRJozGfPZD3AvcMzV8EXFxVLwUeA85t9XOBx1r94taOJOuBM4GfBTYCf9tCaRlwKXAasB54W2s77z4kSaMzpwBJshr4JeCjbT7AG4HrWpOrgDPa9KY2T1t+cmu/Cbi2qp6qqm8AU8CJ7TZVVfdX1XeBa4FNnX1IkkZkrnsgfwP8IfCDNn8U8HhV7WvzO4FVbXoV8CBAW/5Ea/90fdo6s9V7+pAkjcgBAyTJm4BHqmr7CMZzUCU5L8lkksk9e/aMeziS9ENlLnsgrwHenOSbDA4vvRH4CLAiyfLWZjWwq03vAo4DaMtfBDw6XJ+2zmz1Rzv6eIaquryqJqpqYuXKlXP4VSVJc3XAAKmq91fV6qpaw+BN8C9V1dnAl4G3tGabgevb9JY2T1v+paqqVj+znUG1FlgHfAW4GVjXzrg6ovWxpa0z3z4kSSOy/MBNZvVHwLVJ/hy4Fbii1a8APp5kCtjLIBCoqh1JPg3cDewDzq+q7wMkeTewFVgGXFlVO3r6kCSNTg6Xf9wnJiZqcnKya91rtj2woL7P2nD8gtaXpHFJsr2qJmZa5ifRJUldDBBJUhcDRJLUxQCRJHUxQCRJXQwQSVIXA0SS1MUAkSR1MUAkSV0MEElSFwNEktTFAJEkdTFAJEldDBBJUhcDRJLUxQCRJHUxQCRJXQwQSVIXA0SS1MUAkSR1MUAkSV0MEElSFwNEktTFAJEkdTFAJEldDBBJUhcDRJLUxQCRJHUxQCRJXQwQSVIXA0SS1MUAkSR1MUAkSV0MEElSFwNEktTFAJEkdTFAJEldDBBJUpcDBkiS45J8OcndSXYkeW+rvzjJDUnuaz+PbPUkuSTJVJI7kpwwdF+bW/v7kmweqr8qyZ1tnUuSpLcPSdJozGUPZB/w+1W1HjgJOD/JeuAC4MaqWgfc2OYBTgPWtdt5wGUwCAPgg8AG4ETgg/sDobV5x9B6G1t9Xn1IkkbngAFSVbur6pY2/W3gHmAVsAm4qjW7CjijTW8Crq6Bm4AVSY4FTgVuqKq9VfUYcAOwsS17YVXdVFUFXD3tvubThyRpROb1HkiSNcArgW3AMVW1uy16CDimTa8CHhxabWerPVt95wx1OvqYPt7zkkwmmdyzZ8/cfklJ0pzMOUCS/DjwGeB9VfXk8LK251AHeWzP0NNHVV1eVRNVNbFy5cpFGpkkHZ7mFCBJfpRBeHyiqj7byg/vP2zUfj7S6ruA44ZWX91qz1ZfPUO9pw9J0ojM5SysAFcA91TVh4cWbQH2n0m1Gbh+qH5OO1PqJOCJdhhqK3BKkiPbm+enAFvbsieTnNT6Omfafc2nD0nSiCyfQ5vXAL8O3Jnktlb7Y+BC4NNJzgW+Bby1Lfs8cDowBXwHeDtAVe1N8mfAza3dn1bV3jb9LuBjwHOBL7Qb8+1DkjQ6BwyQqvpPILMsPnmG9gWcP8t9XQlcOUN9Evi5GeqPzrcPSdJo+El0SVIXA0SS1MUAkSR1MUAkSV0MEElSFwNEktTFAJEkdTFAJEldDBBJUhcDRJLUxQCRJHUxQCRJXQwQSVIXA0SS1MUAkSR1MUAkSV0MEElSFwNEktTFAJEkdTFAJEldDBBJUhcDRJLUxQCRJHUxQCRJXQwQSVIXA0SS1MUAkSR1MUAkSV0MEElSFwNEktTFAJEkdTFAJEldDBBJUhcDRJLUZfm4B3A4uGbbA93rnrXh+IM4Ekk6eNwDkSR1MUAkSV0MEElSl0M2QJJsTHJvkqkkF4x7PJJ0uDkkAyTJMuBS4DRgPfC2JOvHOypJOrwcqmdhnQhMVdX9AEmuBTYBd491VItgIWdwLYRnf0k6kEM1QFYBDw7N7wQ2TG+U5DzgvDb730nu7ejraOC/OtZbbIs6rrP7Vz0st9cCLdWxOa75WarjgoWN7SdnW3CoBsicVNXlwOULuY8kk1U1cZCGdNA4rvlZquOCpTs2xzU/S3VcsHhjOyTfAwF2AccNza9uNUnSiByqAXIzsC7J2iRHAGcCW8Y8Jkk6rBySh7Cqal+SdwNbgWXAlVW1Y5G6W9AhsEXkuOZnqY4Llu7YHNf8LNVxwSKNLVW1GPcrSfohd6gewpIkjZkBIknqYoDMYqlcKiXJcUm+nOTuJDuSvLfVP5RkV5Lb2u30MY3vm0nubGOYbLUXJ7khyX3t55EjHtPLh7bLbUmeTPK+cWyzJFcmeSTJXUO1GbdPBi5pz7k7kpwwhrH9ZZKvtv4/l2RFq69J8r9D2+7vRjyuWR+7JO9v2+zeJKeOeFyfGhrTN5Pc1uqj3F6zvUYs/vOsqrxNuzF4Y/7rwEuAI4DbgfVjGsuxwAlt+gXA1xhcvuVDwB8sgW31TeDoabW/AC5o0xcAF435sXyIwYehRr7NgNcDJwB3HWj7AKcDXwACnARsG8PYTgGWt+mLhsa2ZrjdGMY142PX/hZuB54DrG1/t8tGNa5py/8a+JMxbK/ZXiMW/XnmHsjMnr5USlV9F9h/qZSRq6rdVXVLm/42cA+DT+IvZZuAq9r0VcAZYxzLycDXq+pb4+i8qv4d2DutPNv22QRcXQM3ASuSHDvKsVXVF6tqX5u9icFnrEZqlm02m03AtVX1VFV9A5hi8Pc70nElCfBW4JOL0fezeZbXiEV/nhkgM5vpUiljf9FOsgZ4JbCtld7ddkGvHPVhoiEFfDHJ9gwuHQNwTFXtbtMPAceMZ2jA4DNCw3/US2GbzbZ9ltrz7jcZ/Ke639oktyb5tySvG8N4Znrslso2ex3wcFXdN1Qb+faa9hqx6M8zA+QQkeTHgc8A76uqJ4HLgJ8Cfh7YzWD3eRxeW1UnMLgy8vlJXj+8sAb7zGM5VzyDD5m+GfjHVloq2+xp49w+zybJB4B9wCdaaTdwfFW9Evg94JokLxzhkJbcYzfN23jmPyoj314zvEY8bbGeZwbIzJbUpVKS/CiDJ8YnquqzAFX1cFV9v6p+APw9i7TbfiBVtav9fAT4XBvHw/t3idvPR8YxNgahdktVPdzGuCS2GbNvnyXxvEvyG8CbgLPbCw/tENGjbXo7g/caXjaqMT3LYzf2bZZkOfCrwKf210a9vWZ6jWAEzzMDZGZL5lIp7djqFcA9VfXhofrwMctfAe6avu4Ixvb8JC/YP83gDdi7GGyrza3ZZuD6UY+tecZ/hUthmzWzbZ8twDntLJmTgCeGDkGMRJKNwB8Cb66q7wzVV2bwPTwkeQmwDrh/hOOa7bHbApyZ5DlJ1rZxfWVU42p+EfhqVe3cXxjl9prtNYJRPM9GcZbAoXhjcKbC1xj85/CBMY7jtQx2Pe8Abmu304GPA3e2+hbg2DGM7SUMzoC5HdixfzsBRwE3AvcB/wq8eAxjez7wKPCiodrItxmDANsNfI/BseZzZ9s+DM6KubQ95+4EJsYwtikGx8f3P9f+rrX9tfYY3wbcAvzyiMc162MHfKBts3uB00Y5rlb/GPDOaW1Hub1me41Y9OeZlzKRJHXxEJYkqYsBIknqYoBIkroYIJKkLgaIJKmLASIdJEn+u/1ck+Ssg3i/H0ryBzPU1wxfGVYaNQNEOvjWAActQKSlygCRDr4Lgde174H43STLMviejZvbxQB/CyDJG9qF9q5Pcn+SC5OcneQrGXzHyk9Nv+Mkr0pye5LbgfNH/YtJwwwQ6eC7APiPqvr5qrqYwSepn6iqVwOvBt7RLrsB8ArgncDPAL8OvKyqTgQ+Crxnhvv+B+A9VfWKxf4lpAMxQKTFdwqDaw/dxuAy20cxuDYSwM01+D6HpxhcWuKLrX4ng0NhT8vg2wFX1OB7KWBweQ9pbJaPewDSYSAM9hq2PqOYvAF4aqj0g6H5H+Dfp5Y490Ckg+/bDL5adL+twG+3S26T5GXt6sXzUlWPA48neW0rnb3gkUoL4H840sF3B/D99kb3x4CPMDgcdUu79PYe+r/m9+3AlUmK/z/cJY2FV+OVJHXxEJYkqYsBIknqYoBIkroYIJKkLgaIJKmLASJJ6mKASJK6/B+Japq1vxRaJQAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_clicks_by_session = df_clicks.groupby([\"SessionId\"])\n",
    "df_clicks_sessionCount = df_clicks_by_session[\"ItemId\"].count()\n",
    "sns.distplot(df_clicks_sessionCount, kde=False, bins=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Buy dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 650 ms, sys: 100 ms, total: 750 ms\n",
      "Wall time: 776 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_buys = pd.read_csv(\"/tf/notebooks/data/yoochoose/\" + 'yoochoose-buys.dat', sep=',', header=None,\n",
    "                  dtype={0:np.int32, 1:str, 2:np.int64, 3:np.int64, 4:np.int64})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>SessionId</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Action</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>420374</td>\n",
       "      <td>214537888</td>\n",
       "      <td>12462</td>\n",
       "      <td>1</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>420374</td>\n",
       "      <td>214537850</td>\n",
       "      <td>10471</td>\n",
       "      <td>1</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>281626</td>\n",
       "      <td>214535653</td>\n",
       "      <td>1883</td>\n",
       "      <td>1</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>420368</td>\n",
       "      <td>214530572</td>\n",
       "      <td>6073</td>\n",
       "      <td>1</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>420368</td>\n",
       "      <td>214835025</td>\n",
       "      <td>2617</td>\n",
       "      <td>1</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SessionId     ItemId  Price  Quantity Action\n",
       "0     420374  214537888  12462         1    BUY\n",
       "1     420374  214537850  10471         1    BUY\n",
       "2     281626  214535653   1883         1    BUY\n",
       "3     420368  214530572   6073         1    BUY\n",
       "4     420368  214835025   2617         1    BUY"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_buys.columns = [\"SessionId\", \"TimeStr\", \"ItemId\", \"Price\", \"Quantity\"]\n",
    "df_buys.drop([\"TimeStr\"], inplace=True, axis=1)\n",
    "df_buys[\"Action\"] = \"BUY\"\n",
    "df_buys.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 12.5 s, sys: 4.7 s, total: 17.2 s\n",
      "Wall time: 17.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df = pd.merge(left=df_clicks, right=df_buys, how=\"left\", on=[\"SessionId\", \"ItemId\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"ItemId==@ItemId & SessionId==@SessionId\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "ItemId = 214821371\n",
    "SessionId = 11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>SessionId</th>\n",
       "      <th>TimeStr</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:45:29.873Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:46:12.162Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:46:57.355Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SessionId                   TimeStr     ItemId ItemType\n",
       "26         11  2014-04-03T10:45:29.873Z  214821371     NONE\n",
       "27         11  2014-04-03T10:46:12.162Z  214821371     NONE\n",
       "28         11  2014-04-03T10:46:57.355Z  214821371     NONE"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clicks.query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>SessionId</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Action</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>214821371</td>\n",
       "      <td>1046</td>\n",
       "      <td>1</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>214821371</td>\n",
       "      <td>1046</td>\n",
       "      <td>1</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SessionId     ItemId  Price  Quantity Action\n",
       "10         11  214821371   1046         1    BUY\n",
       "11         11  214821371   1046         1    BUY"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_buys.query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>SessionId</th>\n",
       "      <th>TimeStr</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemType</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Action</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:45:29.873Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1046.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:45:29.873Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1046.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:46:12.162Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1046.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:46:12.162Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1046.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:46:57.355Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1046.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-04-03T10:46:57.355Z</td>\n",
       "      <td>214821371</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1046.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SessionId                   TimeStr     ItemId ItemType   Price  Quantity  \\\n",
       "26         11  2014-04-03T10:45:29.873Z  214821371     NONE  1046.0       1.0   \n",
       "27         11  2014-04-03T10:45:29.873Z  214821371     NONE  1046.0       1.0   \n",
       "28         11  2014-04-03T10:46:12.162Z  214821371     NONE  1046.0       1.0   \n",
       "29         11  2014-04-03T10:46:12.162Z  214821371     NONE  1046.0       1.0   \n",
       "30         11  2014-04-03T10:46:57.355Z  214821371     NONE  1046.0       1.0   \n",
       "31         11  2014-04-03T10:46:57.355Z  214821371     NONE  1046.0       1.0   \n",
       "\n",
       "   Action  \n",
       "26    BUY  \n",
       "27    BUY  \n",
       "28    BUY  \n",
       "29    BUY  \n",
       "30    BUY  \n",
       "31    BUY  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop duplicates\n",
    "df.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sub-select data - based on threshold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "SESSION_THRESHOLD = 20\n",
    "ITEM_THRESHOLD = 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "session_lengths = df.groupby([\"SessionId\"]).size()\n",
    "session_lengths_w_threshold = (session_lengths[session_lengths>SESSION_THRESHOLD]).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_session_threshold = df[df.SessionId.isin(session_lengths_w_threshold.SessionId)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5131"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_lengths = df.groupby([\"ItemId\"]).size()\n",
    "item_lengths_w_threshold = item_lengths[item_lengths>ITEM_THRESHOLD]\n",
    "len(item_lengths_w_threshold)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "item_lengths_w_threshold = item_lengths_w_threshold.reset_index()\n",
    "df_with_session_item_threshold = df_with_session_threshold[df_with_session_threshold.ItemId.isin(item_lengths_w_threshold.ItemId)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "session_lengths_2 = df_with_session_item_threshold.groupby([\"SessionId\"]).size()\n",
    "session_lengths_2_w_threshold = (session_lengths_2[session_lengths_2 > SESSION_THRESHOLD]).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_with_session_item_threshold[df_with_session_item_threshold.SessionId.isin(session_lengths_2_w_threshold.SessionId)]"
   ]
  },
  {
   "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>SessionId</th>\n",
       "      <th>TimeStr</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemType</th>\n",
       "      <th>Price</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Action</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>282</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:19:20.979Z</td>\n",
       "      <td>214840483</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1674.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>283</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:19:28.762Z</td>\n",
       "      <td>214840483</td>\n",
       "      <td>NONE</td>\n",
       "      <td>1674.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>284</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:26:01.516Z</td>\n",
       "      <td>214717286</td>\n",
       "      <td>NONE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>285</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:26:15.176Z</td>\n",
       "      <td>214558807</td>\n",
       "      <td>NONE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>286</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:26:32.159Z</td>\n",
       "      <td>214821300</td>\n",
       "      <td>NONE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SessionId                   TimeStr     ItemId ItemType   Price  \\\n",
       "282         87  2014-04-07T06:19:20.979Z  214840483     NONE  1674.0   \n",
       "283         87  2014-04-07T06:19:28.762Z  214840483     NONE  1674.0   \n",
       "284         87  2014-04-07T06:26:01.516Z  214717286     NONE     NaN   \n",
       "285         87  2014-04-07T06:26:15.176Z  214558807     NONE     NaN   \n",
       "286         87  2014-04-07T06:26:32.159Z  214821300     NONE     NaN   \n",
       "\n",
       "     Quantity Action  \n",
       "282       1.0    BUY  \n",
       "283       1.0    BUY  \n",
       "284       NaN    NaN  \n",
       "285       NaN    NaN  \n",
       "286       NaN    NaN  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py:6287: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._update_inplace(new_data)\n"
     ]
    }
   ],
   "source": [
    "df_final.Action.fillna(value=\"CLICK\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/pandas/core/frame.py:4102: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  errors=errors,\n"
     ]
    }
   ],
   "source": [
    "df_final.drop([\"Price\", \"Quantity\"], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SessionId</th>\n",
       "      <th>TimeStr</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemType</th>\n",
       "      <th>Action</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>282</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:19:20.979Z</td>\n",
       "      <td>214840483</td>\n",
       "      <td>NONE</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>283</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:19:28.762Z</td>\n",
       "      <td>214840483</td>\n",
       "      <td>NONE</td>\n",
       "      <td>BUY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>284</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:26:01.516Z</td>\n",
       "      <td>214717286</td>\n",
       "      <td>NONE</td>\n",
       "      <td>CLICK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>285</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:26:15.176Z</td>\n",
       "      <td>214558807</td>\n",
       "      <td>NONE</td>\n",
       "      <td>CLICK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>286</td>\n",
       "      <td>87</td>\n",
       "      <td>2014-04-07T06:26:32.159Z</td>\n",
       "      <td>214821300</td>\n",
       "      <td>NONE</td>\n",
       "      <td>CLICK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SessionId                   TimeStr     ItemId ItemType Action\n",
       "282         87  2014-04-07T06:19:20.979Z  214840483     NONE    BUY\n",
       "283         87  2014-04-07T06:19:28.762Z  214840483     NONE    BUY\n",
       "284         87  2014-04-07T06:26:01.516Z  214717286     NONE  CLICK\n",
       "285         87  2014-04-07T06:26:15.176Z  214558807     NONE  CLICK\n",
       "286         87  2014-04-07T06:26:32.159Z  214821300     NONE  CLICK"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fixing Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 11.3 s, sys: 28.3 ms, total: 11.3 s\n",
      "Wall time: 11.3 s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_final['Time'] = df_final.TimeStr.apply(lambda x: dt.datetime.strptime(x, '%Y-%m-%dT%H:%M:%S.%fZ').timestamp()) #This is not UTC. It does not really matter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "del(df_final[\"TimeStr\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 519 ms, sys: 79.1 ms, total: 598 ms\n",
      "Wall time: 597 ms\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_final.sort_values(by=[\"SessionId\", \"Time\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "df_final[\"Rating\"] = df_final.Action.apply(lambda x: 1 if (x == \"CLICK\") else 5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>SessionId</th>\n",
       "      <th>ItemId</th>\n",
       "      <th>ItemType</th>\n",
       "      <th>Action</th>\n",
       "      <th>Time</th>\n",
       "      <th>Rating</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>282</td>\n",
       "      <td>87</td>\n",
       "      <td>214840483</td>\n",
       "      <td>NONE</td>\n",
       "      <td>BUY</td>\n",
       "      <td>1.396852e+09</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>283</td>\n",
       "      <td>87</td>\n",
       "      <td>214840483</td>\n",
       "      <td>NONE</td>\n",
       "      <td>BUY</td>\n",
       "      <td>1.396852e+09</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>284</td>\n",
       "      <td>87</td>\n",
       "      <td>214717286</td>\n",
       "      <td>NONE</td>\n",
       "      <td>CLICK</td>\n",
       "      <td>1.396852e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>285</td>\n",
       "      <td>87</td>\n",
       "      <td>214558807</td>\n",
       "      <td>NONE</td>\n",
       "      <td>CLICK</td>\n",
       "      <td>1.396852e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>286</td>\n",
       "      <td>87</td>\n",
       "      <td>214821300</td>\n",
       "      <td>NONE</td>\n",
       "      <td>CLICK</td>\n",
       "      <td>1.396852e+09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     SessionId     ItemId ItemType Action          Time  Rating\n",
       "282         87  214840483     NONE    BUY  1.396852e+09       5\n",
       "283         87  214840483     NONE    BUY  1.396852e+09       5\n",
       "284         87  214717286     NONE  CLICK  1.396852e+09       1\n",
       "285         87  214558807     NONE  CLICK  1.396852e+09       1\n",
       "286         87  214821300     NONE  CLICK  1.396852e+09       1"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.to_csv(\"/tf/notebooks/data/yoochoose/\"+\"rating.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}